August 8, 2012 at 12:43 pm
Hello,
I need to setup SQL Replication (transactional) on a database thats over 200 GB. Replication is going to be setup over the WAN and so it would take a very long time to send the snapshot and it would saturate the already highly utilised WAN. I've looked at shipping a backup of the database and then initializing the subscriber using the backup rather than a snapshot, however I need to remove some columns before replicating the data, we cant have some of the data at the subscriber.
Is it possible to create snapshot, ship the files off (portable Drive) and then place them somewhere on the subscriber and then initialize the subscription and applying the snapshot from the copied files?
Not sure if that made sense, but any advice would be appreciated.
SQL Server 2008 R2 to SQL Server 2008 / R2 / 2012
Thanks
The Rumble.
August 8, 2012 at 1:15 pm
Yes you can.
1. Run the Create Publication wizard on the publisher. Make sure NOT to create a snapshot.
2. Once the wizard has finished, right-click the new publication, choose Properties
and make sure that the "Allow initialization from backup files" is set to True
3. Disable the "Distribution clean up: distribution" job to make sure that no
commands are deleted from MSrepl_commands before the entire backup/restore operation
is complete
4. Create a full database backup to disk and make sure that the file is available for the
publisher until the entire replication setup is finished.
5. Transfer the backup file to the subscriber server and restore it there. If you do
regular backups to disk make sure to use one that was taken *after* the publication
was created. Also restore trans log backups taken after the full backup. The last backup
should be restored using WITH RECOVERY, and keep in mind that a more recent backup saves
you time in the syncing process when the replication is being initialized.
6. Disable all triggers on the subscriber database:
EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'
7. Disable all constraints on subscriber database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
8. Run the following script ON THE PUBLISHING SERVER IN THE PUBLISHING DATABASE to enable
the replication:
EXEC sp_addsubscription
@publication ='myPublication', --> Name of the publication
@subscriber = 'myserver.myDomain.com', --> Fully qualified name or IP of subscriber server
@destination_db ='MySubscriberDB', --> Name of the database you just restored (doesn't have to be the same as the publisher)
@sync_type = 'initialize with backup', --> no need to change this
@backupdevicetype = 'disk', --> no need to change this
@backupdevicename = 'F:\backupfile.bak' --> Pointer to the last backupfile that was restored, but from the folder on the on the
publishing server. If you restored trans logs also the last translog file is what you
need to put here
9. Enable the "Distribution clean up: distribution" job again
August 8, 2012 at 10:00 pm
sqlrumble (8/8/2012)
Hello,I need to setup SQL Replication (transactional) on a database thats over 200 GB. Replication is going to be setup over the WAN and so it would take a very long time to send the snapshot and it would saturate the already highly utilised WAN. I've looked at shipping a backup of the database and then initializing the subscriber using the backup rather than a snapshot, however I need to remove some columns before replicating the data, we cant have some of the data at the subscriber.
Is it possible to create snapshot, ship the files off (portable Drive) and then place them somewhere on the subscriber and then initialize the subscription and applying the snapshot from the copied files?
Not sure if that made sense, but any advice would be appreciated.
SQL Server 2008 R2 to SQL Server 2008 / R2 / 2012
Thanks
The Rumble.
You can create the snapshot in a location that suits you - you specify the snapshot location in the publication definition (mostly, people leave this as the default location but you can override it). So, you specify a location that is available to both servers but actually on different drives. You would
- create the snapshot
- copy the files to the subscriber (perhaps via the portable drive) if needed
- initialise the subscriber
If the location of the snapshot folder is on the portable drive, the second step becomes transport the drive to the subscriber and plug it in.
You may need to create the scripts to add the publication manually - I don't think the GUI has this option any more.
For more info, have a look in Books Online at sp_addpublication. In particular, info about parameter @snapshot_in_defaultfolder and @alt_snapshot_folder
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply