November 10, 2010 at 10:12 am
I'm hoping this is a common issue and there are best practices on how to handle the situation that I just haven't been smart enough to discover. Any help is appreciated.
Here is the scenario:
We have a 100GB database that we want to start replicating to an offsite server.
Our limitations are as follows:
We can't take the publishing database off line
We don't have physical access to the subscriber machine so we have to transfer the database over the network
Regardless of which replication method we use I don't know how to manage the first step and get the database to the new server and start replication. In the past I have always set up replication in early stages or before go-live, I've never tried to cold start it after a database is live and so large.
Other information, it is an OLTP database but with low transaction counts. Most of the large changes happen at night with client imports then small changes are made to the data.
What other information would be helpful?
Thanks
November 11, 2010 at 5:23 am
We do this on a fairly regular basis and can be done using a "nosync" subscription. You will still need to generate a replication snapshot which shouldnt but may involve some table locking for a short period and this will need to be done during a quiet period or maintenance slot. This method should work in both Transactional and Merge replication (doubt you'll be snapshot replication with 100Gb)
Cory Blythe (11/10/2010)
We don't have physical access to the subscriber machine so we have to transfer the database over the network
Overcome this by logshipping the database to the destination server.
-------------------------
These are the steps I would would follow.
1. Create the publication in advance
2. Set up a logshipped database on the destination server
3. Generate all replication scripts in advance
4. Restore tail end transaction log on logshipped db
5. run scripts on publisher and subscriber
/* Run on subscriber db */
exec scriptpublicationcustomprocs 'ReplicatedTablesPublication' --Generate all repl procs on subscriber as these wont exist
--------------------------
/* Run on Publisher */
exec sp_addsubscription
@publication = N'ReplicatedTablesPublication',
@subscriber = N'',
@destination_db = N'ReplicatedTables',
@subscription_type = N'Push', @sync_type = N'none', --this is a no sync sub
@article = N'all', @update_mode = N'read only', @frequency_type = 64, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4,
@frequency_subday_interval = 5, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @active_start_date = 0,
@active_end_date = 0, @offloadagent = 0, @dts_package_location = N'Distributor'
GO
November 11, 2010 at 6:50 am
MysteryJimbo (11/11/2010)
/* Run on subscriber db */
exec scriptpublicationcustomprocs 'ReplicatedTablesPublication' --Generate all repl procs on subscriber as these wont exist
What I actually meant is, run this on the publisher and the output on the subscriber.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply