Scripting Replication
All replication components in a topology should be scripted as part of a disaster recovery plan, and scripts can also be used to automate repetitive tasks. A script contains the Transact-SQL system stored procedures necessary to implement the replication component(s) scripted, such as a publication or subscription. Scripts can be created in a wizard (such as the New Publication Wizard) or in Microsoft SQL Server Management Studio after you create a component. You can view, modify, and run the script using SQL Server Management Studio or sqlcmd. Scripts can be stored with backup files to be used in case a replication topology must be reconfigured.
A component should be re-scripted if any property changes are made. If you use custom stored procedures with transactional replication, a copy of each procedure should be stored with the scripts; the copy should be updated if the procedure changes (procedures are typically updated due to schema changes or changing application requirements). For more information about custom procedures, see Specifying How Changes Are Propagated for Transactional Articles.
For merge publications that use parameterized filters, publication scripts contain the stored procedure calls to create data partitions. The script provides a reference for the partitions created and a way in which to re-create one or more partitions if necessary.
Example of Automating a Task with Scripts
Consider Adventure Works Cycles, which implements merge replication to distribute data to its remote sales force. A sales representative downloads all the data that pertains to the customers in her territory using pull subscriptions. When working offline, the sales representative updates data and enters new customers and orders. Because Adventure Works Cycles has more than fifty sales representatives in different territories, it would be time-consuming to create the different subscriptions at each Subscriber with the New Subscription Wizard. Instead, the replication administrator can follow these steps:
- Set up the necessary merge publications with partitions based on the sales representative or their territory.
- Create a pull subscription for one Subscriber.
- Generate a script based on that pull subscription.
- Modify the script, changing such values as the name of the Subscriber.
- Run the script at multiple Subscribers to generate the required pull subscriptions.
To script replication
- SQL Server Management Studio: How to: Script Replication Objects (SQL Server Management Studio)