Forum Discussion

DBATony's avatar
DBATony
Copper Contributor
Jan 02, 2025

Disaster Recovery and with existing replication

Is there any Disaster Recovery plan for SQL Server that can also handle existing replication.

In this case:

I have an on-premise server with 9 TB in 18 user databases and existing replication with publications from 2 user databases to multiple on-premises subscribers.

I have an Azure IaaS server with an identical SQL Server install and local disk allocations.

My questions are:

For a Basic Backup & Restore DR plan:

  1. Can existing replication be handled. The DR server has a new name? 
  2. Can you restore the distribution database to another server. Can replication continue to the existing subscribers?
  3. Or is re-initializing all replication the only option? I never seen existing replication in documented in any DR plan.  Does any DR documentation factor in existing replication.  I know that when I restored user database in the past to another server that had existing replication, is was impossible to clean up that replication. And a full re-initial from scratch was the only option.

For an Availability Group DR plan:

  1. Would Availability Groups, and making these two servers part of a WSFC handle existing replication any better? The distribution database is a system database, and I am not sure it can be handled any better with an AG.  Would a complete re-initial of any existing replication be required after a failover?
  • 1. Can existing replication be handled on a server with a new name?
    No, existing replication cannot be easily transferred to a server with a different name. Replication relies on the publisher, distributor, and subscriber being registered with their specific server names. Restoring the distribution database to a new server will break the replication metadata, as it references the old server name.

    2. Can you restore the distribution database to another server and continue replication?
    The distribution database cannot be restored to a different server without breaking replication. SQL Server replication is tightly coupled to the original server name, and there is no built-in mechanism to seamlessly transfer replication metadata to a new server.

    3. Is re-initializing all replication the only option?
    Yes, in most cases, re-initialization of replication is the only viable option when setting up replication on a new server after a DR event. 

    4. Does any DR documentation address replication?
    DR documentation for SQL Server generally focuses on backup/restore and high availability options like Log Shipping or Availability Groups. Replication is usually considered a separate component, as it is primarily a data distribution mechanism.

  • petevern's avatar
    petevern
    Brass Contributor

    1. Can existing replication be handled on a server with a new name?
    No, existing replication cannot be easily transferred to a server with a different name. Replication relies on the publisher, distributor, and subscriber being registered with their specific server names. Restoring the distribution database to a new server will break the replication metadata, as it references the old server name.

    2. Can you restore the distribution database to another server and continue replication?
    The distribution database cannot be restored to a different server without breaking replication. SQL Server replication is tightly coupled to the original server name, and there is no built-in mechanism to seamlessly transfer replication metadata to a new server.

    3. Is re-initializing all replication the only option?
    Yes, in most cases, re-initialization of replication is the only viable option when setting up replication on a new server after a DR event. 

    4. Does any DR documentation address replication?
    DR documentation for SQL Server generally focuses on backup/restore and high availability options like Log Shipping or Availability Groups. Replication is usually considered a separate component, as it is primarily a data distribution mechanism.

    • DBATony's avatar
      DBATony
      Copper Contributor

      Thank you.  This is what I thought, and I needed to confirm, just in case I was missing something I didn't know.

Resources