I'm playing around with SQL Server Agent alerts, hoping that I can get some reliable error notifications sent out when our transactional replication breaks. To test it however, I need to break it. I have a subscription that's safe to break, but I don't want to have to spend hours reinitializing and delivering snapshots afterward. Is there an easy, reversible way to break replication?
Add a comment
|
2 Answers
ALTER DATABASE foo SET SINGLE_USER
Since the SQL agent jobs that perform replication cannot connect to a database in single-user mode, this has the simultaneous result of stopping replication, and disallowing client connections.
Ergo, replication is broken but it can be resumed without re-initializing (the LSN is still good)
What about testing it to perfection in a VM environment and then duplicating it to the real servers. A VM can easily be rolled back to a previous state so you can always start over again.
answered Jul 5, 2012 at 8:35
-
Effort. In our case it's fine to break replication in production for a minute or two, and far less of a cost than duplicating our environment. I sure wish we had replication in our test environment, though... Jul 6, 2012 at 20:00