How to rename Distribution Job in an automated fashion ?

Technorati Tags: Replication

Situation: Recently I came across a situation wherein I have to rename the distribution jobs that are created when replication is recreated. The need to recreate replication is due to the fact that we do every 3 or 4th week staging refresh in our environment.

Our environment is on Windows server 2008 (64bit) with SQL server 2005 enterprise edition SP3 with Transactional replication involved.

Approach: We have a automated process called a Master Job (I will blog this in my future posts) that does the staging refresh job. This job is named as per what it does, meaning it drops replication, does restores and recreates replication with just one click ! Isn’t that cool !

But this job has some manual steps to rename the distribution jobs that are created when the replication is recreated. So to fully automate the job, I thought of automating some how to rename the distribution jobs.

First I would try to explain how the jobs are created when replication is created or recreated.

When transactional replication is created either using GUI (wizard) or recreated using scripts, the respective agent (snapshot, distribution and log reader) jobs are created with their default naming conventions. For example:

Snapshot Agent:
[PublisherServerName]-PublisherDB-PublisherDB- [# of time replication script/wizard run on this server]

Log reader Agent:
[PublisherServerName]-PublisherDB- [# of time replication script/wizard run on this server]

Distribution Agent:
[publisherserverName]-publisherDB-SubsriberServerName [# of times subscribers created run on this server]

[Ref: http://rdbmsexperts.com/Blogs/?p=40 . Thanks to Vinay for his help !]

This shows that there is no control of creating our own job names (for better management and maintenance).

There are two ways of renaming the distribution jobs:

1. On the distribution server, double click the respective job names and then rename them. Uh! that’s a manual way of doing and it can be tricky or prone to mistakes and being a DBA, its not smart way to do things !
2. The second way is creating a custom script as below:

In the distribution database, there is a system table called dbo.MSdistribution_agents that gives the info about the distribution agent with respect to sysjobs.

Below is the script that I used to rename the distribution jobs ( we have multiple publisher – subscribers.)

declare @jobID1 uniqueidentifier 
select @jobID1=job_id from [dist_db_name].dbo.MSdistribution_agents a,master.sys.servers s
where a.publisher_db='[pub_db_name]'  and
a.subscriber_db='[subscriber_db_name]' and
a.subscriber_id=s.server_id and
s.name='subscriber_server_name'

print @jobID1  -- this will print the jobID of the distribution job 

select job_id, name from msdb.dbo.sysjobs where job_id = @jobID1   -- this will give the jobID as well as job name of  the distribution job that we will be renaming

–-rename the distribution agent job that is created to new job name

exec msdb.dbo.sp_update_job @job_id = @jobID1, @new_name = ‘ New_meaning_ful_name’

To sum up above method:

-- Get the job ID for the distribution job created
declare @jobID1 uniqueidentifier

select @jobID1=job_id from [dist_db_name].dbo.MSdistribution_agents a,master.sys.servers s
where a.publisher_db='[pub_db_name]'  and
a.subscriber_db='[subscriber_db_name]' and
a.subscriber_id=s.server_id and
s.name='subscriber_server_name'

–-rename the distribution agent job that is created to new job name

exec msdb.dbo.sp_update_job @job_id = @jobID1, @new_name = ‘ New_meaning_ful_name’

In the same way we can rename the job for snapshot and logreader agent as well with MSlogreader_agents & MSsnapshot_agents tables respectively.

Hope this helps !

Thanks,

\\K