Have you ever tried setting up a SQL Server Transactional Replication before? I had a chance to explore this the other day at a customer site. Setting up the publisher and subscriber bit was simple and easy by default. Thanks to the handy Wizard interface. However, we faced a tiny bit of a problem when using the wizard.

Yes, under normal circumstances, setting up a transactional replication with the wizard is really easy and dandy. The thing is, probably only a small percentage of live production databases fit in as normal.

I had a problem with the initial snapshot creation when setting up the transactional replication. This is using SQL Server 2005. The initial snapshot creation took ages to complete. Even after running it overnight, for 8 hours, it did not complete. The thing is, while the snapshot agent was running, the database was not accessible to other users. Is there a way to make the snapshot run under low priority? 

Since I had to allow users access to the database, I had to stop the snapshot job. Will it start from scratch if restarted or will it continue from where it stopped? I tested and it seemed to start from scratch once again. Another 8+ hours? I don’t think so. Thus, I set out to find a way to create a transactional replication without a snapshot.

After googling for “transactional replication without  snapshot” for a bit, I found the perfect solution to my problem. I found that by using the stored procedure called sp_addsubscription(), I can initialize a subscription without the need to create a snapshot of the publishing database. sp_addsubscription() allows me to initiate a subscription with the most recent backup of the publishing database.

No snapshot means no 8+ hours of waiting. I’ve got to try this out, I said to myself. So I did. I first tested it out in my test environment. It worked and thus, without hesitation, I proceeded to put it up into the production environment.

SQL2005 Publication Wizard Snapshot Agent

How did I do it? Following are the steps to create a SQL Server 2005 Transactional Replication without an initial snapshot. 

1. Create a new publication using the New Publication Wizard. Yes, you can still use the wizard to create the publication. Just fill in the fields and settings as required according to your environment. However, when you reach the SnapShot Agent dialog, leave all the checkboxes unchecked. You didn’t want a snapshot remember? That’s the only thing to note while going through the New Publication Wizard dialogs. 

2. Upon completion of the New Publication Wizard, open the property dialog to your publication. You now need to modify a property setting on the publication. You need to set the publication to allow initialization from backup files. Yeah, that’s all there is to it. Just set this setting to True. It really would have been even more dandier if this could be performed within the New Publication Wizard though.

SQL2005 Publication Properties

3. Perform a full backup of your database. You need to run this backup from SQL Server itself, not any other third party backup tools. Backup the database to a directory accessible by the SQL Server, such as “<SQLServer Directory>BackupDatabase.bak”.

4. Restore the backup into the desired subscriber sql server instance. Don’t know how to restore from a SQL backup? Google it up. Really, it’s simple. Just run restore from the SQL Management Studio and point the wizard to the backup file. Of course you should copy the backup file into the server first.

5. Run the following stored procedure in the SQL Server Management Studio Query Window.

sp_addsubscription 
@publication ='YourPublicationName', --your pub name here
@subscriber='SubscriberServerDBInstance', --subscriber server name
@destination_db='SubscriberDatabase',
@sync_type = 'initialize with backup',
@backupdevicetype = 'disk',
@backupdevicename = 'c:<SomeDirectory>Database.bak'
go


When I ran the script above in my production environment, I encountered an error with regards to some Distribution Cleanup Job being active. After stopping and disabling the said Distribution Cleanup Job and rerunning the above stored procedure, I can see the new subscription listed under the publication. 

Hence, my subscription was successfully created and initiated without the need of an initial snapshot that would take 8+ hours to complete.

The entire process from New Publication Wizard to completion of the stored procedure took roughly about 2 hours plus. The bulk of the time was spent waiting for  the following jobs to complete:

1. backup publication database
2. copy backup to subscriber server
3. restoring subscriber database

The transactional replication is now happily running between the two servers. Of course, the above process creates only a basic full database transactional replication. If your requirements are a litte bit more complicated, you might want to take a look at the options you can configure in the sp_addsubscription stored procedure.