November 3, 2008 at 1:24 pm
Hi,
we would like to implement transactional replication for one of our production databases (size over 250 GB in a 24/7/365 web environment).
During my research for that, I realized that subscribers can be initialized via backup from the publisher which seems the way to go considering the size of the DB but unfortunately it seems I still have to take an initial snapshot of the DB when setting up the publication (which is not an option due to the fact that the tables get locked during that time => killing the front end web application ... not even talking about space constraints on that server).
Since I did not really find anything about this particular problem so far, my questions are now:
1. Is there a way to go completely without an initial snapshot ?!
2. If so, what are the pitfalls to get around ?!
Any input or tips would be appreciated ... thanks in advance !
Kind regards
November 3, 2008 at 1:54 pm
Hi,
Did you confirm that this is happening? The reason I am asking is that in SQL Server 2005 it will use Concurrent snapshot by default, see http://msdn.microsoft.com/en-us/library/ms151706(SQL.90).aspx.
🙂
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 3, 2008 at 2:06 pm
Thanks for the quick response - I was reading this document before but somehow missed that point (it seems locking is not really a problem while creating a snapshot for transactional replication - and to answer your question, no I did not confirm / tried that out since it is a production DB).
Nevertheless I still want to know if there is a way to go around creating that snapshot because
a) of the space constraints we have (this would be a 250+ GB snapshot)
and
b) we would not need the snapshot for initializing if we use a backup as base / source for the subscriber (as far as I understand it)
November 3, 2008 at 10:20 pm
While setting up your replication you surely can use a subscription with no syc......for this typeof setup, only new data will be moved.....
Pitfalls -
1> Sometimes the replication stored procedures are not properly......don't worry they can be created separately
2> Errors might occur......needs monitoring initially.......you can skip those errors or might make proper changes as corrective measures - errors might include : Row not found during deletion or update....
3> If you want a ditto replication, you need to make sure that after you have taken the backup till the replication setup is complete, the primary database is read-only.....
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
November 4, 2008 at 2:09 pm
Thanks for the heads up about the pitfalls - i will for sure have a look at those ... 🙂
November 5, 2008 at 2:47 pm
You can use init with backup option...
* Noel
November 5, 2008 at 9:18 pm
Setting up a subscriber using a backup -
1> Setup the publication, if not already setup, as usual.
2> Restore a latest backup of the publishing database as your subscribing database.
3> Setup subscription with the above database
If using GUI (Management Console) - you just need to uncheck the initialize checkbox
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
November 21, 2008 at 9:43 am
there is also an option to run the snapshot to a DVD and I think an external hard disk is OK then use that to initialize the sub
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply