Reinitialize subscription
-
Tuesday, November 13, 2012 10:24 AM
Hi,
I have created a transactional replication subscription that was initialized from backup. I have now an error that the subscription must be reinitialized. Will I have downtime if I mark subscription for reinitialization? I do not have a snapshot!
CKotsis
All Replies
-
Tuesday, November 13, 2012 1:32 PMModerator
The best thing to do here is to drop the subscription, backup the publication database, restore it on the subscriber, and then recreate your subscription with the initialize from backup settings as described here.
http://msdn.microsoft.com/en-us/library/ms147834.aspx
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Marked As Answer by ckotsidimos Wednesday, November 14, 2012 9:05 AM
-
Tuesday, November 13, 2012 3:47 PM The other option (Hilary's being the safest) would be to drop the subscription and recreate it without initializing. You would then be required to do a manual sync to ensure that data is truly in sync. If that is a really large table that can be a very painful task, so proceed with caution.
David
- Marked As Answer by ckotsidimos Wednesday, November 14, 2012 9:05 AM
-
Tuesday, November 13, 2012 3:54 PMModerator
Ensure you use the Continue on Data Consistency Error profile as well. Otherwise for every data consistency error (missing rows, PK violations) your distribution agent will fail.
Not using this profile can be of assistance when you want to know about such problems, but on busy systems this will lead to a large number of errors and your subscription will get progressively more and more out of sync.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Wednesday, November 14, 2012 9:03 AM
Thanks for the info. Those were my thoughts also.
@David, I have to do the subscription from backup. I cannot afford the downtime!
CKotsis
- Edited by ckotsidimos Wednesday, November 14, 2012 9:05 AM
-
Wednesday, November 14, 2012 2:12 PM
That was my point in offering the suggestion I did. You can do that method without downtime, the only issue being that your data is out of sync.
By you stating that you cannot afford downtime, I'm assuming that you are looking for the subscription database to stay online and all the articles associated with that publication to stay available to end users. If that is the case, then the only way to do that is to create the subscription without initializing. Again though, that means that your data is out of sync from the start and will have to be synced manually.
How large is the table or tables in the publication?
David
-
Wednesday, November 14, 2012 2:19 PMAbout 300Gb!, I decided to do a backup and restore and initialize the database again. the issue is why this happened.
CKotsis
-
Wednesday, November 14, 2012 2:24 PM
Fair enough. I agree, that is the safest method as you are sure to start at a point where everything is in sync. If no downtime is acceptable, which is the case in my environment, even with tables / databases that large, downtime is not an option and you need to figure out other methods, which is why I suggested what I did.
Glad that you have a plan and are moving forward.
David
.png)
