4

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?

Samantha Branham
  • 221
  • 3
  • 11

2 Answers2

3

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)

adaptr
  • 16,576
  • 23
  • 34
0

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.

Christopher Perrin
  • 4,811
  • 19
  • 33
  • 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... – Samantha Branham Jul 06 '12 at 20:00