we would like to setup DR nodes for all existing production SQL servers

Question

we would like to setup DR nodes for all existing production SQL servers

DR site would be more than 200 miles , what is the best recommendations in terms of network, servers and SQL server editions etc

Answer 1

Plain old FCIs cannot be an option, because that is a shared disk. Obviously the DR site needs to have its own disks.

This means that you need some form of availability group beyond Basic AG. Thus, you need Enterprise Edition.

There two possibilities: One is to use distributed AGs. This essentially means that you have one AG locally and one AG at the DR site that you tie together.

But if you don't need the HA aspect of AGs, you could use a clusterless AG, then you only need one server at each site.

Keep in mind that for the distance you indicate, synchronous replicas are out of the question; the latency will be too long.

As for the question about the ideal distance between the primary data centre and the DR site, that depends a lot of your location. I would say that where I live, Stockholm, a distance of 320 km, is reasonable. The likelihood for a disaster hitting that wide is not that big. But if you live in an area where earthquakes are prevalent, that distance may be to short, as an earthquake could damage both sites.

Answer 2

To set up disaster recovery (DR) nodes for your existing production SQL servers, especially over a distance of more than 200 miles, consider the following recommendations:

1. Network Considerations:
   • Ensure you have a reliable and high-speed network connection between your primary and DR sites. A dedicated line or VPN with sufficient bandwidth is recommended to minimize latency and ensure data synchronization.
   • Implement network redundancy to avoid single points of failure.
2. SQL Server Editions:
   • Use SQL Server Enterprise Edition if you require advanced features like Always On Availability Groups, which provide high availability and disaster recovery capabilities. If your needs are less demanding, Standard Edition may suffice, but it has limitations in terms of high availability features.
3. Server Configuration:
   • Ensure that the hardware at the DR site meets or exceeds the performance requirements of your production servers. While identical hardware is not strictly necessary, compatibility in terms of Windows and SQL Server versions is crucial.
   • Consider using SQL Server Failover Cluster Instances (FCI) or Always On Availability Groups for high availability and disaster recovery solutions.
4. Data Synchronization:
   • Implement log shipping, database mirroring, or Always On Availability Groups for data synchronization between the primary and DR sites. Each method has its pros and cons, so choose based on your recovery point objectives (RPO) and recovery time objectives (RTO).
5. Testing and Validation:
   • Regularly test your DR setup to ensure that it works as expected. This includes failover testing and ensuring that backups are valid and can be restored in a timely manner.

By following these recommendations, you can establish a robust disaster recovery strategy for your SQL Server environment.