Setting up replication

Question

Setting up replication

brettwalker42

SSC Enthusiast

Points: 178
More actions
March 9, 2020 at 7:31 pm

#3732602
Hello,
I'm am an experienced DBA and developer but am new to replication. I have to set up replication on a ~100 GB database and have some questions. I am replicating from our production database to a reporting database on a different server.
1. Any estimates on how long it will take to do the initial snapshot and restore on the destination? How much impact will it have on the production server/database?
2. Can this be done during production hours or should it be done off-hours? It's important that production is not affected by this. The reporting database is a little more forgiving if it's offline for a while (an hour or 2).
3. Can the destination database exist or does it have to be created when the initial snapshot runs? We currently have a reporting database. Do I need to drop it and let replication create it?
4. We are currently using log-shipping but it is not working for us. How do I disable/delete log-shipping for the current production database.
Let me know if you have any questions or need clarification on anything.
Thanks,
Brett

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

There are too many variables to guess how long it will take - number of articles and data, metadata needed for those articles, hardware, network latency would all play a part. If you are concerned about it taking too long, you can initialize with a snapshot or you can initialize with a backup.

You would want to do this off hours as it does impact the publisher and if you distributor is also your publisher, there is even more impact. The buffer pool is also impacted so off hours is best.

You can't use the existing database - you want to initialize with a snapshot or backup so that replication agents can start tracking the transactions from that point.

To remove the log shipping, you can so something like the following:

-- On the primary
EXEC MASTER.dbo.sp_delete_log_shipping_primary_secondary
 N'PrimaryDatabaseName',
 N'SecondaryServerName',
 N'SecondarDatabaseName'
GO
EXEC MASTER.dbo.sp_delete_log_shipping_primary_database N'PrimaryDatabaseName'
GO


-- On the secondary
EXEC MASTER.dbo.sp_delete_log_shipping_secondary_database N'SecondaryDatabaseName'

Sue

brettwalker42 SSC Enthusiast Points: 178 More actions · Answer 2

Sue,

Thanks for the response. Lots of good info.

Brett