September 18, 2013 at 6:59 am
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?
Thanks,
Jason
September 18, 2013 at 8:16 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply