Is there any better disaster recovery than Transactional Replication?

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:

• Log Shipping and Replication (SQL Server)
• Set up disaster recovery for SQL Server

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.