Question about SQL 2000 TLOG replication

Hi to all,
i have 2 questions about SQL replication :
1- If i configure a trasaction replication between 2 SQL server 2000 (online
and subscriber) and after i schedule the distributor agent for start onlyone
to week
this maybe generated a problem as more incresase of code and so slowing sql
or other proiblem ?
2- Exists any to the tail for replication ?
3- if i manually insert a data only on subscriber database can i generte any
replication problems ?!'! Problems as stop of replica or other problem ?!!?
4- What happened if i do a bckup log with truncate only for online databse
or distributor ?
Thanks in advance.I count 4 questions not 2, but whose counting?
1) This won't work unless you set the history retention to 14 days or so.
Once you do this you will find performance degrades after several days
depending on your load. Ideally you would run the agents continually.
2) What do you mean by the tail of replication?
3) if the data you enter on the subscriber conflicts with the data entered
on the publisher it will cause a problem. Use the continue on data
consistency error profiler to skip this.
4) This should work ok. Replication will work fine after a truncate.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:%23bZSDbI4GHA.1848@.TK2MSFTNGP06.phx.gbl...
> Hi to all,
> i have 2 questions about SQL replication :
> 1- If i configure a trasaction replication between 2 SQL server 2000
> (online and subscriber) and after i schedule the distributor agent for
> start onlyone to week
> this maybe generated a problem as more incresase of code and so slowing
> sql or other proiblem ?
> 2- Exists any to the tail for replication ?
> 3- if i manually insert a data only on subscriber database can i generte
> any replication problems ?!'! Problems as stop of replica or other
> problem ?!!?
> 4- What happened if i do a bckup log with truncate only for online
> databse or distributor ?
>
> Thanks in advance.
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>I count 4 questions not 2, but whose counting?
Sorry the questions are 4 :-)
> 1) This won't work unless you set the history retention to 14 days or so.
> Once you do this you will find performance degrades after several days
> depending on your load. Ideally you would run the agents continually.
No i would schedule the agent for example every week at 02:00
> 2) What do you mean by the tail of replication?
Sorry i would tell : Exists any problem if i schedule the agent for
distribution only every 2 week and the distribution database and the data to
replicate are more grows ?
> 3) if the data you enter on the subscriber conflicts with the data entered
> on the publisher it will cause a problem. Use the continue on data
> consistency error profiler to skip this.
Ok thanks.
> 4) This should work ok. Replication will work fine after a truncate.
Ok thanks.
Thanks in advance.|||answers inline.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<io.com@.newsgroup.nospam> wrote in message
news:eW$E0CJ4GHA.2424@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eoPWojI4GHA.4820@.TK2MSFTNGP06.phx.gbl...
>>I count 4 questions not 2, but whose counting?
> Sorry the questions are 4 :-)
>
;)
>> 1) This won't work unless you set the history retention to 14 days or so.
>> Once you do this you will find performance degrades after several days
>> depending on your load. Ideally you would run the agents continually.
> No i would schedule the agent for example every week at 02:00
>> 2) What do you mean by the tail of replication?
> Sorry i would tell : Exists any problem if i schedule the agent for
> distribution only every 2 week and the distribution database and the data
> to replicate are more grows ?
>
Ideally you would replicate as frequently as possible, having the agent run
once or twice a week will not offer good performance if there is significant
load.
>> 3) if the data you enter on the subscriber conflicts with the data
>> entered on the publisher it will cause a problem. Use the continue on
>> data consistency error profiler to skip this.
> Ok thanks.
>> 4) This should work ok. Replication will work fine after a truncate.
> Ok thanks.
> Thanks in advance.
>

question about retrieving identity value

hi,
according to the SQL Server Books Online, there're 3 system functions return
last-generated identity values: IDENT_CURRENT, @.@.IDENTITY, and
SCOPE_IDENTITY. My colleague wrote a stored procedure like the following:
...
BEGIN TRAN
...
INSERT INTO [TABLE_WITH_IDENTITY_COLUMN] ...
SET @.ID = IDENT_CURRENT('TABLE_WITH_IDENTITY_COLUM
N')
...
COMMIT TRAN
...
I think using IDENT_CURRENT may be a problem in multiuser environment,
because it returns the last identity value generated in any session. But he
said it's ok, since these statements are put in a transaction and SQL Server
will lock the table. Is it true?Hi
He is wrong. SQL Server may not lock the whole table, so it is possible that
2 processes can insert into the same table, at the same time.
SCOPE_IDENTITY is the correct one to use.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:72F8BDEC-FA0F-4880-B1E7-FFBDB8102955@.microsoft.com...
> hi,
> according to the SQL Server Books Online, there're 3 system functions
> return
> last-generated identity values: IDENT_CURRENT, @.@.IDENTITY, and
> SCOPE_IDENTITY. My colleague wrote a stored procedure like the following:
> ...
> BEGIN TRAN
> ...
> INSERT INTO [TABLE_WITH_IDENTITY_COLUMN] ...
> SET @.ID = IDENT_CURRENT('TABLE_WITH_IDENTITY_COLUM
N')
> ...
> COMMIT TRAN
> ...
> I think using IDENT_CURRENT may be a problem in multiuser environment,
> because it returns the last identity value generated in any session. But
> he
> said it's ok, since these statements are put in a transaction and SQL
> Server
> will lock the table. Is it true?

question about job names

When I create a 'simple' transactional push replication 3 jobs are created.
As far as I know from studing books online I can either let SQL Server generate the jobs, and SQL Server determine the jobname (which is different with every time I let generate them), or I can create the jobs manually and use that jobs when I create publication and subscription.
What I like is that SQL Server generate the jobs with a name I define. Why is this not supported? Or do I miss something ?

Regards
Wolfgang KunkWhat is the reason you need a custom job name?|||

This is supported. You can't do this by clicking through the replication wizard. You can do this by executing the stored procedures to create the publication, articles, and subscriptions. I've done this many times.

Why would you want to?

1. Naming conventions

2. Providing better names than what you get by default

3. Someone has handed you a database full of garbage - i.e. table names with spaces, /, -, $, %, and all manner of other special characters that you want to strip out so that the engine doesn't freak out when it tries to create this stuff.

|||

Yes, this makes sense for article names.

But custom names for the agent jobs in MSDB is not supported. The names of these msdb jobs have nothing to do with the names of objects inside of a database. Please let me know if there's a business reason for needing to customize the actual MSDB job name for the agents, we can certainly file a bug/DCR on it.

|||

Well, I have several running in production that are not using the default job names. I routinely change these from the defaults. In fact, it has been several years since I deployed replication in a production environment and kept the default names. None of them ever conform to any of our naming conventions. The ones that always gave me troubles were all of the clean up jobs that I had to go back in and manually rename. Finally wrote a script to automate all of that stuff so now when I setup replication, it also renames all of the clean up jobs as well.

It is a royal pain when any changes have to be made, because SQL Server wants to either revert them back or in most cases will create another job and force fit the jobs names back again. I've always found it to be unreasonable that I can specify the name of absolutely everything across the replication engine, except the clean up jobs. It just has never been high on my priority list to ask for the change since there were a lot more important things to develop within the engine.

|||An example:
I like to implement an alert which stops the distribution agent.
I stop the job with "exec dbo.sp_stop_job <jobname>"
When I now change or recreate the replication the jobname changed automaticly and I need to change every other alert or job which references the jobname of the distribution agent!
That is a high administrative expenditure and it is error-prone.

If I can provide jobnames there is no need to change other jobs or alerts referencing that job!

Wolfgang|||Thanks for your feedback, I'll file a bug and see if we can get something for a future Yukon service pack, if not for next release. We do have some public procs that do as you want, but unfortunately they're not documented, and therefore not supported. I hope to have that changed in the future.|||

In SQL2005, we offer the following system procedures for starting\stopping replication agents:

sp_start|stoppublication_snapshot (at publisher)

sp_start|stop[merge]pushsubscription_agent (at publisher)

sp_start|stop[merge]pullsubscription_agent (at subscriber)

You can also pre-define a job with whatever name you choose and then *attach* a replication agent to it through the *@.job_name parameters of the replication system procedures but we have security reasons for not allowing users to specify any job names they want for replication jobs that we generate. Note that we will not clean up any jobs that you create before attaching a replication agent to it.

Will the new procedures be sufficient for your purposes?

-Raymond

|||Ok, Raymond spilled the beans. These are the procs I'm looking to get doc'd for public use. Right now they're unsupported, so I recommend not using them as they can change between now and the time they go public.|||OK, as soon as the procs are supported they will solve the example.
But it is only an example. In general I don't want to change references to jobs at all.
And I cannot see that providing a job name could be a security issue.

Wolfgang|||

You can use your own job names as long as you create the job beforehand and perform the cleanup afterwards. This was original usage scenario that the @.*job_name parameters were designed to address. Unfortunately, the meaning of those parameters got subverted into "let me choose whatever names I want for the jobs generated by replication because I want to start my jobs through T-SQL without 1) querying the job_id which is stored in replication system tables and 2) writing a program that uses the ActiveX controls". I can agree that this is a worthy scenario to support but unfortunately mixing the two had caused a vulnerability in our code where a mere db_owner of a publisher or subscriber database can choose to drop any jobs on the server (say distribution cleanup). It was very difficult for us to dig ourselves out of this hole while knowing folks like yourself will be upset so we create the procs in SQL2005 to control the agent jobs in a more secured manner. But then again, this seems to be a battle that we can't seem to win.

-Raymond

|||I still cannot see were the problem is. From my point of view it doesn't matter whether the jobname is generated automatically or given by a user. Internally you use the assigned job_id.

Wolfgang|||

I can mail you the exploit code where a mere db_owner of a publisher database can drop the distribution cleanup job on a < SQL2000 sp3 server although I doubt that will convince you.

-Raymond

|||

Kunk wrote:

I still cannot see were the problem is. From my point of view it doesn't matter whether the jobname is generated automatically or given by a user. Internally you use the assigned job_id.
Wolfgang

I have noticed, that when job name is assigned automatically for merge pull subscription, deleting that subscription causes deleting of appropriate job, but when I create subscription using script with custom job name, SQL Server leaves that job after deleting subscription.

I didn't find the reason...

|||Raymond stated exactly this behaviour in one of his former notes:

"You can also pre-define a job with whatever name you choose and then *attach* a replication agent to it through the *@.job_name

parameters of the replication system procedures but we have security

reasons for not allowing users to specify any job names they want for

replication jobs that we generate. Note that we will not clean up any

jobs that you create before attaching a replication agent to it."

As I don't now how the replication system works internally, it is hard to say why it works as it works. I only can say that the handling of replication jobs is not very comfortable. But that is only my personal opinion.

Wolfgang

question about job names

When I create a 'simple' transactional push replication 3 jobs are created.
As far as I know from studing books online I can either let SQL Server generate the jobs, and SQL Server determine the jobname (which is different with every time I let generate them), or I can create the jobs manually and use that jobs when I create publication and subscription.
What I like is that SQL Server generate the jobs with a name I define. Why is this not supported? Or do I miss something ?

Regards
Wolfgang Kunk

What is the reason you need a custom job name?|||

This is supported. You can't do this by clicking through the replication wizard. You can do this by executing the stored procedures to create the publication, articles, and subscriptions. I've done this many times.

Why would you want to?

1. Naming conventions

2. Providing better names than what you get by default

3. Someone has handed you a database full of garbage - i.e. table names with spaces, /, -, $, %, and all manner of other special characters that you want to strip out so that the engine doesn't freak out when it tries to create this stuff.

|||

Yes, this makes sense for article names.

But custom names for the agent jobs in MSDB is not supported. The names of these msdb jobs have nothing to do with the names of objects inside of a database. Please let me know if there's a business reason for needing to customize the actual MSDB job name for the agents, we can certainly file a bug/DCR on it.

|||

Well, I have several running in production that are not using the default job names. I routinely change these from the defaults. In fact, it has been several years since I deployed replication in a production environment and kept the default names. None of them ever conform to any of our naming conventions. The ones that always gave me troubles were all of the clean up jobs that I had to go back in and manually rename. Finally wrote a script to automate all of that stuff so now when I setup replication, it also renames all of the clean up jobs as well.

It is a royal pain when any changes have to be made, because SQL Server wants to either revert them back or in most cases will create another job and force fit the jobs names back again. I've always found it to be unreasonable that I can specify the name of absolutely everything across the replication engine, except the clean up jobs. It just has never been high on my priority list to ask for the change since there were a lot more important things to develop within the engine.

|||An example:
I like to implement an alert which stops the distribution agent.
I stop the job with "exec dbo.sp_stop_job <jobname>"
When I now change or recreate the replication the jobname changed automaticly and I need to change every other alert or job which references the jobname of the distribution agent!
That is a high administrative expenditure and it is error-prone.

If I can provide jobnames there is no need to change other jobs or alerts referencing that job!

Wolfgang

|||Thanks for your feedback, I'll file a bug and see if we can get something for a future Yukon service pack, if not for next release. We do have some public procs that do as you want, but unfortunately they're not documented, and therefore not supported. I hope to have that changed in the future.|||

In SQL2005, we offer the following system procedures for starting\stopping replication agents:

sp_start|stoppublication_snapshot (at publisher)

sp_start|stop[merge]pushsubscription_agent (at publisher)

sp_start|stop[merge]pullsubscription_agent (at subscriber)

You can also pre-define a job with whatever name you choose and then *attach* a replication agent to it through the *@.job_name parameters of the replication system procedures but we have security reasons for not allowing users to specify any job names they want for replication jobs that we generate. Note that we will not clean up any jobs that you create before attaching a replication agent to it.

Will the new procedures be sufficient for your purposes?

-Raymond

|||Ok, Raymond spilled the beans. These are the procs I'm looking to get doc'd for public use. Right now they're unsupported, so I recommend not using them as they can change between now and the time they go public.|||OK, as soon as the procs are supported they will solve the example.
But it is only an example. In general I don't want to change references to jobs at all.
And I cannot see that providing a job name could be a security issue.

Wolfgang

|||

You can use your own job names as long as you create the job beforehand and perform the cleanup afterwards. This was original usage scenario that the @.*job_name parameters were designed to address. Unfortunately, the meaning of those parameters got subverted into "let me choose whatever names I want for the jobs generated by replication because I want to start my jobs through T-SQL without 1) querying the job_id which is stored in replication system tables and 2) writing a program that uses the ActiveX controls". I can agree that this is a worthy scenario to support but unfortunately mixing the two had caused a vulnerability in our code where a mere db_owner of a publisher or subscriber database can choose to drop any jobs on the server (say distribution cleanup). It was very difficult for us to dig ourselves out of this hole while knowing folks like yourself will be upset so we create the procs in SQL2005 to control the agent jobs in a more secured manner. But then again, this seems to be a battle that we can't seem to win.

-Raymond

|||I still cannot see were the problem is. From my point of view it doesn't matter whether the jobname is generated automatically or given by a user. Internally you use the assigned job_id.

Wolfgang

|||

I can mail you the exploit code where a mere db_owner of a publisher database can drop the distribution cleanup job on a < SQL2000 sp3 server although I doubt that will convince you.

-Raymond

|||

Kunk wrote:

I still cannot see were the problem is. From my point of view it doesn't matter whether the jobname is generated automatically or given by a user. Internally you use the assigned job_id.
Wolfgang

I have noticed, that when job name is assigned automatically for merge pull subscription, deleting that subscription causes deleting of appropriate job, but when I create subscription using script with custom job name, SQL Server leaves that job after deleting subscription.

I didn't find the reason...

|||Raymond stated exactly this behaviour in one of his former notes:

"You can also pre-define a job with whatever name you choose and then *attach* a replication agent to it through the *@.job_name parameters of the replication system procedures but we have security reasons for not allowing users to specify any job names they want for replication jobs that we generate.Note that we will not clean up any jobs that you create before attaching a replication agent to it."

As I don't now how the replication system works internally, it is hard to say why it works as it works. I only can say that the handling of replication jobs is not very comfortable. But that is only my personal opinion.

Wolfgang
sql

question about job names

When I create a 'simple' transactional push replication 3 jobs are created.
As far as I know from studing books online I can either let SQL Server generate the jobs, and SQL Server determine the jobname (which is different with every time I let generate them), or I can create the jobs manually and use that jobs when I create publication and subscription.
What I like is that SQL Server generate the jobs with a name I define. Why is this not supported? Or do I miss something ?

Regards
Wolfgang KunkWhat is the reason you need a custom job name?|||

This is supported. You can't do this by clicking through the replication wizard. You can do this by executing the stored procedures to create the publication, articles, and subscriptions. I've done this many times.

Why would you want to?

1. Naming conventions

2. Providing better names than what you get by default

3. Someone has handed you a database full of garbage - i.e. table names with spaces, /, -, $, %, and all manner of other special characters that you want to strip out so that the engine doesn't freak out when it tries to create this stuff.

|||

Yes, this makes sense for article names.

But custom names for the agent jobs in MSDB is not supported. The names of these msdb jobs have nothing to do with the names of objects inside of a database. Please let me know if there's a business reason for needing to customize the actual MSDB job name for the agents, we can certainly file a bug/DCR on it.

|||

Well, I have several running in production that are not using the default job names. I routinely change these from the defaults. In fact, it has been several years since I deployed replication in a production environment and kept the default names. None of them ever conform to any of our naming conventions. The ones that always gave me troubles were all of the clean up jobs that I had to go back in and manually rename. Finally wrote a script to automate all of that stuff so now when I setup replication, it also renames all of the clean up jobs as well.

It is a royal pain when any changes have to be made, because SQL Server wants to either revert them back or in most cases will create another job and force fit the jobs names back again. I've always found it to be unreasonable that I can specify the name of absolutely everything across the replication engine, except the clean up jobs. It just has never been high on my priority list to ask for the change since there were a lot more important things to develop within the engine.

|||An example:
I like to implement an alert which stops the distribution agent.
I stop the job with "exec dbo.sp_stop_job <jobname>"
When I now change or recreate the replication the jobname changed automaticly and I need to change every other alert or job which references the jobname of the distribution agent!
That is a high administrative expenditure and it is error-prone.

If I can provide jobnames there is no need to change other jobs or alerts referencing that job!

Wolfgang|||Thanks for your feedback, I'll file a bug and see if we can get something for a future Yukon service pack, if not for next release. We do have some public procs that do as you want, but unfortunately they're not documented, and therefore not supported. I hope to have that changed in the future.|||

In SQL2005, we offer the following system procedures for starting\stopping replication agents:

sp_start|stoppublication_snapshot (at publisher)

sp_start|stop[merge]pushsubscription_agent (at publisher)

sp_start|stop[merge]pullsubscription_agent (at subscriber)

You can also pre-define a job with whatever name you choose and then *attach* a replication agent to it through the *@.job_name parameters of the replication system procedures but we have security reasons for not allowing users to specify any job names they want for replication jobs that we generate. Note that we will not clean up any jobs that you create before attaching a replication agent to it.

Will the new procedures be sufficient for your purposes?

-Raymond

|||Ok, Raymond spilled the beans. These are the procs I'm looking to get doc'd for public use. Right now they're unsupported, so I recommend not using them as they can change between now and the time they go public.|||OK, as soon as the procs are supported they will solve the example.
But it is only an example. In general I don't want to change references to jobs at all.
And I cannot see that providing a job name could be a security issue.

Wolfgang|||

You can use your own job names as long as you create the job beforehand and perform the cleanup afterwards. This was original usage scenario that the @.*job_name parameters were designed to address. Unfortunately, the meaning of those parameters got subverted into "let me choose whatever names I want for the jobs generated by replication because I want to start my jobs through T-SQL without 1) querying the job_id which is stored in replication system tables and 2) writing a program that uses the ActiveX controls". I can agree that this is a worthy scenario to support but unfortunately mixing the two had caused a vulnerability in our code where a mere db_owner of a publisher or subscriber database can choose to drop any jobs on the server (say distribution cleanup). It was very difficult for us to dig ourselves out of this hole while knowing folks like yourself will be upset so we create the procs in SQL2005 to control the agent jobs in a more secured manner. But then again, this seems to be a battle that we can't seem to win.

-Raymond

|||I still cannot see were the problem is. From my point of view it doesn't matter whether the jobname is generated automatically or given by a user. Internally you use the assigned job_id.

Wolfgang|||

I can mail you the exploit code where a mere db_owner of a publisher database can drop the distribution cleanup job on a < SQL2000 sp3 server although I doubt that will convince you.

-Raymond

|||

Kunk wrote:

I still cannot see were the problem is. From my point of view it doesn't matter whether the jobname is generated automatically or given by a user. Internally you use the assigned job_id.
Wolfgang

I have noticed, that when job name is assigned automatically for merge pull subscription, deleting that subscription causes deleting of appropriate job, but when I create subscription using script with custom job name, SQL Server leaves that job after deleting subscription.

I didn't find the reason...

|||Raymond stated exactly this behaviour in one of his former notes:

"You can also pre-define a job with whatever name you choose and then *attach* a replication agent to it through the *@.job_name

parameters of the replication system procedures but we have security

reasons for not allowing users to specify any job names they want for

replication jobs that we generate. Note that we will not clean up any

jobs that you create before attaching a replication agent to it."

As I don't now how the replication system works internally, it is hard to say why it works as it works. I only can say that the handling of replication jobs is not very comfortable. But that is only my personal opinion.

Wolfgang