I have a production database i.e. where there are constant updates and I've configured this to be published to another server using Transactional Replication.

When I configure transactional replication I've been doing the following:

  • disable access to the source database
  • backup source DB then restore to subscription server
  • configure replication
  • re-enable DB access to our apps

The problem with this approach is scheduling in downtime, having to suspend all the various timed scheduled tasks we run and shutting down access to our various applications that are dependant on this database.

Can I just configure transactional replication without disabling access to the publishing database and the subscriber database will correctly catch up? i.e. are all the DML statements queued on the publisher and as soon as the subscriber is ready they are picked off and executed?


The simple answer is No... You do not need to disable access to the system etc. Here is why and how it works... When you setup replication, the publisher creates a Snapshot of the data at that very moment you create the publication. There is also a Log Reader that is running at the same time with Transactional Replication that is catching EVERY Transaction since the last snapshot was created. So... when a subscriber is created it is basically a 2 step process:

Step 1: The Snapshot is applied to the Subscriber which creates the schema and data on the SUbscriber database.

Step2: Then the Transactions since the time of Snapshot are applied in order at the Subscriber to get the Subscriber database up to date with the latest data.

So basically, no, you do not need to do all of that other stuf you are doing to create a new subscriber.

Let me know if you need anything else.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.