SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Would Always-On Groups be a decent solution for this?


Would Always-On Groups be a decent solution for this?

Author
Message
jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4173 Visits: 11749
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
HowardW
HowardW
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 Visits: 9892
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search