A Fine Slice Of SQL Server
You work with Database Mirroring or AlwaysOn AG, and you want to make sure your end users work only on the secondary server. How should you do that?
This solution feels a little hacky to me. There’s enough value in it that I could see companies doing this, but it’d be nice if there were an easier way.
Comments closedDerik Hammer’s final Availability Group architecture post covers disaster recovery on the cheap:
This architecture can be used when your organization does not value their secondary data center the same as the primary. It is a best practice to have matched or similar hardware between your primary and disaster recovery sites but that is not always possible. When costs need to be reduced it is better to have one failover server that you know can handle the work load rather than two servers which are under powered. Under powered hardware can easily become an effective outage if they cause timeouts as soon as a production work load is placed on them.
For many small to medium-sized enterprises, this might be the easiest route to sell to management—it’s hard to get management to have “redundant” servers which normally don’t get used.
Comments closedDerik Hammer digs into a more complex architecture for HA+DR:
The Availability Group will handle the data synchronization between the data centers. Asynchronous mode is recommend due to potential network latency hindering primary site performance. With the combination of these two features, you meet HA with the FCI’s automatic failover locally and DR with manual failover of the Availability Group between sites.
Derik’s conclusion is important: this isn’t the type of thing a brand new accidental DBA should try to build; there are a lot of moving parts here. But if you want a robust solution and have the support people available to handle the relative complexity, this is a good option.
Comments closedJohn Handra shows how to configure read-only routing on an Availability Group:
NOTE: SQL Server 2016 introduces load-balancing across the secondary replicas in your AlwaysOn Availability Group. To utilize this feature, the above routing list will need to be modified so that SQL Server directs incoming read-only connections across the servers you choose to be load-balanced. You will have to use nested parentheses around the server instances you want to be part of the load-balanced group.
Read-only routing requires some additional setup and possibly load changes, but it can give you a nice performance gain by off-loading some of your read requests.
Comments closed