Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Init From Backup

By Andy Warren,

One of the biggest hurdles to using replication on large databases is doing the initial snapshot. If you're new to replication (and we're focusing on transactional replication in this article) a snapshot is the process of putting the tables and related data on the subscriber. A vanilla snapshot consists of scripting out the tables and indexes, BCP'ing out the data, then applying the scripts to the subscriber, and finally BCP'ing the data in. It's often faster than expected based on the database size because it's only the data being moved, not the indexes themselves. Speed is a function of available disk IO on both machines as well as the network. Doing a snapshot of a terabyte database across a WAN can be an exercise in slow.

Prior to SQL 2005 there weren't many great alternatives if you were set on using replication. One way was to quiesce the system; deny users the ability to change anything, then run a full backup, copy it to a portable drive, drive or overnight it to the subscriber location, load it, and then start replication by configuring it to know that the subscriber already had the data and finally let the users resume work. Worked okay if you could do this overnight or weekend. SQL 2000 introduced the concept of a concurrent snapshot - basically a non blocking snapshot. This let us run the snapshot with minimal impact on users. It didn't change the time required to post the data to the subscriber though. SQL 2005 now gives us the option to initialize a subscriber from backup without much  pain. It's not supported via the replication dialogs in SSMS, but it's relatively easy to do and I'm going to demonstrate that today. This article assumes you already know how to set up replication and build a publication.

After creating a publication, right click it and select properties. Change the allow initialization from backup files setting to True, then close the dialog.

The next step is to run a full backup. I'm using Adventureworks and I'm replicating just the Person.Address table for this exercise.

backup database AdventureWorks to disk='c:\aworks.bak'

Then, restore the file to the subscriber server (in this case I'm going to restore to the same machine, my laptop):

restore database AdventureWorksSubscriber from disk='c:\aworks.bak' with
move 'AdventureWorks_data' to 'c:\aworkssub.mdf',
move 'AdventureWorks_log' to 'c:\aworkssub.ldf'

Now we need to create the subscription on the publisher and for it to use the restored copy, we have to do this with TSQL:

use adventureworks
go
sp_addsubscription 
	@publication ='Test', --your pub name here
	@subscriber='earlgrey', --subscriber server name
	@destination_db='AdventureWorksSubscriber',
	@sync_type = 'initialize with backup',
	@backupdevicetype = 'disk',
	@backupdevicename = 'c:\aworks.bak'
go

We can see the subscription has been added in SSMS:

To prove it's working we'll update one row on the publisher, wait a few seconds, and then verify on the subscriber:

update person.address set addressline2='TEST UPDATE' where addressid=1
use adventureworkssubscriber
go
select * from person.address where addressid=1

The only downside to this technique is that the subscriber has a complete copy of the database. In our case we only wanted to replicate one table. If we're concerned about security, or the amount of space used on the subscriber, we can just start dropping tables and other objects after we get everything going. It's easy enough to use and maybe they'll support it better in a service pack or SQL 2008.

I blog once a week or so at http://blogs.sqlservercentral.com/blogs/andy_warren/default.aspx about SQLServer, SQL user groups, and related topics. I hope you'll visit and comment occasionally!

Total article views: 3646 | Views in the last 30 days: 10
 
Related Articles
FORUM

Snapshot replication for large database

Snapshot replication for large database

FORUM

Snapshot Replication - Transactions after snapshot replication starts are replicated to subscribers as part of Snapshot

Snapshot Replication - Transactions after snapshot replication starts are replicated to subscribers ...

FORUM

Snapshot replication for large database

Snapshot replication for large database

FORUM

Backups Vs Mirroring VS Replications Vs Snapshot

Backups Vs Mirroring VS Replications Vs Snapshot

FORUM

Merge Replication across internet, "The merge process was unable to deliver the snapshot to the Subscriber. "

Merge Replication across internet, "The merge process was unable to deliver the snapshot to the Subs...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones