In my business world we utilize Transactional Replication to copy data from our OLTP to another box for Reporting purposes. For a little background on replication I will quote directly from MSDN what transactional replication is http://msdn.microsoft.com/en-us/library/ms151198.aspx:

“Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise.”

Now that an explanation of what replication is about I can dive further into the steps I’ve taken to automate my own replication. Within replication there are publications that reside on the publishers side (where the data is being copied from) and subscriptions on the receiving side (where the data is being copied to). In my current set up I have 18 publishers. Since this post is regarding Automation I will not go into how to manually start the agent via the Replication Monitor.

I created 18 SQL files that correspond to my 18 Publications. Within my sql files I have the following:

Use [DBName]

Go

Declare @publication VARCHAR(50)

SET @publication = [PublicationName]

EXEC sp_startpublication_snapshot @publication = @publication

GO

This allows me to have 18 seperate start up files; now can I have one file instead of 18. Sure I can; however in some instances I only want to run maybe 1 or 2 so to keep this automated I have seperated mine out. To execute these I have multiple options. I can use my Redgate SQL Multi Script utility that I have at my disposal (which I have done in the past); however I have also created a SSIS package. The SISS package calls a .BAT file with the following contents:

for %%G in (*.sql) do sqlcmd /S (local) -E -i”%%G”

Within my SSIS package I point the running of this command to the directory where I store all my publications; the command executes in this case all sql files (*.sql) to my local environment.

Once the process completes all my agents are started and the snapshot generation is underway.