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
No comments:
Post a Comment