Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Would Always-On Groups be a decent solution for this? Expand / Collapse
Posted Wednesday, September 18, 2013 6:59 AM



Group: General Forum Members
Last Login: Thursday, December 1, 2016 2:17 PM
Points: 1,772, Visits: 10,711
So, my employer (may) be frowning on after-hours work (even though it's a *bad* thing to take down a production SQL during the day to reboot from OS updates that they require to be installed by a certain date) and I'm looking to get some options lined up to resolve this.

While a SQL cluster would be a solution, they started migrating to a VMWare environment before I started, and just recently decomissioned the last physical SQL servers (in a cluster no less) a couple months ago.

So, I thought of AAGs. It sounds like this would let me do the OS updates (which on my SQL servers I am responsible for) by the following method:
1. Update the replica (which would not be configured for read-only access)
2. Reboot the replica
3. Wait for the DBs to re-synch
4. Manually (planned) failover the AAG DBs to the replica, making it the Primary
5. Repeat steps 1-4, replacing "replica" with "primary" (although technically the former primary would now be the replica because of step 4)
6. Go home at my normally scheduled time, and only connect in to work if something breaks...

I've set up a very basic AAG at home in a VM environment without to much hassle, so that part I think I could manage.

I'm also presuming that any SQL Agent jobs would need to be configured on *both* hosts, as you can't make system DBs part of an AAG (one downside compared to a cluster, at least.)

Am I on the right track? Or at least a reasonable one, if this becomes a requirement?

Post #1495909
Posted Wednesday, September 18, 2013 8:16 AM

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
It's definitely do-able and AlwaysOn can be a HA solution as well as a DR.

Remember that switch-over is not completely seamless and users will get disconnected (same with clustering), so you need to make sure your company is OK with the disruption caused by switchover during business hours. If you have a large number of databases, performing a switchover with AlwaysOn (even in synchronous protection) will almost certainly be a lot slower than a clustering failover.

Setting up a seamless HA environment with AlwaysOn is higher maintenance than doing it with clustering as they're independent instances, so you need to configure all your agent jobs to check whether they're on the active node (using the AlwaysOn DMVs/DMFs) and then only run subsequent steps if it's the primary (pretty easy to script a job step into each job to do this).

Other procedures also need setting up to make sure that logins are synced on each side etc.

On the plus-side, since AlwaysOn is shared-nothing, if you can put the second instance in another data centre, you also get a rock-solid DR environment that's frequently tested for free.
Post #1495937
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse