Musings of a technophile

I have a table at my company that is replicated from a live database server to a server that is used for reporting. The live server is in one location while the reporting server is located in the building where I work, and they are connected via a point-to-point T1 line. The problem is that the table that is replicated is now over 60GB, so if replication breaks and I have to reinitialize replication, it can take days or maybe even weeks to transfer the snapshot data over.

What I found really annoying is that I have backups of the primary database and I perform log shipping from the publishing server to the secondary server for backup purposes. That means I already had most of the data of that table that was going to be duplicated and sent over in a snapshot. I figured there had to be some way to be able to import the data I already had into the replicated table on the subscriber, and then catch up the records to the replicated data, and then have replication continue to add records after that. I talked to a friend of mine and he had some idea’s on how to “hack” this, however it was more related to a process he was doing and didn’t really fit my goals. After attempting to find a work around for the better part of a day, I wound up finally finding the solution, and figured I would write it down here.

So to begin – you first need to already have a backup or bulk insert file, or log shipped database at the subscribers location.

1] Stop the distribution from occurring. You need to open up the distribution agent and stop the process. This is done by going to Replication—> Local Publications—>Publication–>Subscription
and right clicking and clicking properties in SQL Server Manager. Then click Stop.

2] Re-initialize the subscription. Right click the subscription and click “Reinitialize”. On the screen that pops up, select “Use a new snapshot” and “Generate a new snapshot now”.

3] Monitor the snapshot process. Right click the publication and click “View snapshot agent status”. You are basically going to wait until it says “[100%] A snapshot of x article(s) was generated.”, where x is the number of articles you are replicating. Steps 4 and 5 can be done while waiting for this step to complete.

4] Script out and remove indexes from the subscriber table. I would recommend scripting out all the drop statements and then the create statements for all indexes except the primary key on the subscriber table first, then dropping the indexes. By removing the indexes, you will stop the bulk insert from having to update indexes, which can speed up bulk insert TREMENDOUSLY (a.k.a – hours instead of days).

5] Import your data into the subscriber table. While the snapshot is being done, go ahead and import your data into the subscriber. If you need to make the table first then go ahead and do so but make sure it matches the schema of the table on the publisher. Bulk insert works the best to import the data. If you need to copy data from a log shipped database, use bcp to dump the table first, then all you have to do is change the destination table and change “out” to “in” for the bcp command, easy stuff.

6] “Hack” the snapshot files. Once the snapshot is done, You need to go into the snapshot folder (this is defined in your publication) and find the files that were created by the snapshot. Inside the folder there will be a folder called unc, open that and there will be a list of all the publications you have. Open the folder for the publication you are working on, and there will be a folder name which basically is the time at which the snapshot started. You may have multiple folders here if you have multiple snapshots, but it’s ok – just find the folder that has the highest date time and go into it (you may want to look at the date modified if you are having a hard time figuring the folder out). Inside the folder will be all the snapshot files. They are follow the following file types:

• *.bcp – contains the snapshot data of the table to be replicated
• *.pre – contains a script to execute on the subscriber in order to set up replication
• *.idx – The primary key index creation script
• *.sch – The schema for all the stored procedures, the tables, and any other things that will be needed to continue replication on the subscriber once the snapshot is delivered

what we’re going to do is basically make it so that the snapshot never does anything. To make that happen, we will be backing up all the files, and then replacing the .bcp, .pre, and .idx files with empty files, and then modifying the .sch file. You either do this manually (just make a folder called backup in that directory and do what I have started above), or you can make a batch file the does the following:

mkdir backup

for /f "tokens=*" %%a in ('dir /b *.bcp') do move %%a backup

for /f "tokens=*" %%a in ('dir /b *.pre') do move %%a backup

for /f "tokens=*" %%a in ('dir /b *.idx') do move %%a backup

for /f "tokens=*" %%a in ('dir /b backup') do echo. 2> %%a

for /f "tokens=*" %%a in ('dir /b *.sch') do copy %%a backup

which will basically do all of that for you; just put put it in the subscription directory and run it. Now go into the *.sch file(s) and remove the drop table and create table sections at the very tops of those files. Delete until you get to the “GO” statement right after the create table statement. Save the file.

7] Start replication distribution. Go back into SQL Server Manager and bring up the properties of your subscription again. The process should still be stopped from step 1. Assuming step 5 is done, you can go ahead and start the process now. It should quickly state that the snapshot has been sent, and then proceed on to saying that x amount of commands were transferred.

8] Catch up the difference between the snapshot and the data you imported. The problem now is going to be that you have a gap between whatever data you imported, and the last record of the snapshot. The distribution will only send rows that occurred AFTER the snapshot, so if your backup does not go up to the same point as the snapshot, you’re going to have to catch up these rows manually. This is the reason that this process only works for tables that will not have their rows updated or deleted – because the log reader agent doesn’t know the difference between your backup and where it is at currently. If however your backup was form something like log shipping, and therefore you could have made the backup AFTER the snapshot was done, this should prove not to be an issue.