Share via

Is there any better disaster recovery than Transactional Replication?

Jinal Contractor 121 Reputation points
2025-12-10T19:28:35.8233333+00:00

I am trying to implement Transactional replication on SQL Server 2016 from Prod server to Report server. But it is not carried lot of tables that doesn't have Primary keys and those tables are important too.

Is there different disaster recovery tools that I can implement on my server than Transactional replication?

There are only two servers, Prod and Report.

SQL Server Reporting Services
SQL Server Reporting Services

A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.

0 comments

Answer recommended by moderator

  1. Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator
    2025-12-10T21:28:21.9866667+00:00

    Transactional Replication is not the most convenient DR solution, since there are so many moving parts. And limitations like lack of support for tables without primary keys also makes it problematic.

    If it is only a matter of disaster recovery, log shipping fits the bill. And, well, the standby server is accessible for read-only operations, but every time a new transaction log is applied, users are kicked out. Which can be a bit inconvenient.

    Then there are of course availability groups where you can have a readable secondary. But this requires a cluster. And it requires Enterprise Edition. Although, on later versions, starting with SQL 2017, you can have clusterless AGs. They are intended for scaling reporting workloads, but they can also serve for disaster recovery. But you still need Enterprise Edition.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2025-12-10T19:28:55.6+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    If you are looking for alternatives to Transactional Replication for disaster recovery in SQL Server, you might consider the following options:

    1. Log Shipping: This is a disaster recovery solution that allows you to back up the transaction log on the primary server and restore it on a secondary server. It can be used for single primary databases and multiple secondary databases. Log shipping can provide a more straightforward setup compared to transactional replication, especially if you have tables without primary keys.
    2. Database Mirroring: This option provides high availability and disaster recovery by maintaining a mirror copy of the database on a standby server. It can operate in synchronous or asynchronous mode, depending on your needs for data consistency and performance.
    3. Active Geo-Replication: If you are using SQL Server in Azure, this feature allows you to create readable secondary databases in different regions. It provides automatic failover and is suitable for disaster recovery scenarios.
    4. Azure Site Recovery: This service can help protect SQL Server workloads by replicating them to Azure. It is application agnostic and can support various SQL Server versions.

    Each of these options has its own advantages and considerations, so you should evaluate them based on your specific requirements, including the need for primary keys in your tables.

    References:

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.