Managing new publications and subscriptions
In my never ending quest to simplify administration tasks on SQL Server, I recently came up with a method to automate the creation of new publications with a one script fits all piece of SQL Code.
Considering that in most enterprises, the default configuration options for replication will remain the same, the only thing that changes are the destination servers and the publications/articles themselves.
For this reason I set about writing 2 sprocs (one for publication, one for subscription) that hard codes the constant configurations that my client uses for transactional replication, and uses metadata for the variables. In this way, each new request for a new publication or a subscription to an existing publication is easily dealt with by adding a new record the appropriate table and running the script.
I have used three tables to hold the meta data in my ServerControl database:
- dbo.Publication (holds data about the publisher and publication
- dbo.Subscription (holds data about the distribution to the subscriber and the subscriber itself)
- dbo.Article (holds data about each article)
Now it may be the case that the model doesn’t quite fit every scenario (e.g. its possible to publish say 10 articles in a publication and only subscribe to one of them.) however for my client’s requirements, this model fits.
The 2 sprocs then gather the meta data and use them as parameters for the system stored procedures that configure replication.
For the sprocs to work, the assumption has been made that distribution topology has already been configured.
Both sprocs are re-runnable. They test for the existence of each publication/subscription before they attempt to create them. If they find that it is already present they ignore it and move on to the next one, thereby ensuring that any pending distributions are not interfered with.
Of course we can elaborate on the code by adding more metadata (schedules, filters), but for now this allows us to manage the configurations that change on a regular basis accross mulitple servers.
If you make any improvements to the code, then please feel free to post it here.
Cheers
FB