July 16, 2015 at 9:50 am
Hi all. I just wanted some feedback regarding how you are handling the replication of the many instance-level objects/items (logins, linked servers, server roles, database mail, operators, on and on) to the replicas in an AlwaysOn topology.
I'm especially curious about DBAs managing larger SQL Server environments. In my current environment, we have approximately 80 production SQL instances containing about 650 databases that require high availability and disaster recovery.
We use mirroring today and have a solid, home-grown solution for replicating the instance-level items from production to disaster recovery. AlwaysOn changes things a bit since we'll have multiple replicas and of course the database could be active on any one of those at any time. So my concern is about instance-level items being created in one instance but never deployed to the other instances participating in the AG group.
Any thoughts or feedback would be appreciated. Take care.
July 17, 2015 at 5:36 am
Hi,
Time ago I made for a customer a series of scripts that basically collected all remarkable system objects storing the results on an accessory DB and then I replicate this DB with other user DBs across the SQL Server instances. In case of DR, they were able to use this accessory DB and generate object creation scripts for the system objects. They could even run it periodically and sync system objects among replicas.
The systems object they replicated were (as far as I remember):
- Logins
- Server roles membership
- Backup devices
- Linked servers
- Maintenance plans
- Jobs, schedules, operators, alerts
- Custom errors
Except credential system objects I think it is possible to replicate and sync almost every server side configuration.
July 17, 2015 at 8:55 am
Thanks. That is exactly what we are doing today for the one to one relationship in mirroring between PRD and DR. It works well and we can "replay" the PRD instance-level items in DR and everything looks the same.
My concern is we are potentially looking at multiple (3 or 4) replicas between PRD and DR, so unless DBAs are disciplined to make changes on all instances involved with an AG group, we risk missing something. And that will of course eventually happen.
Short of creating some kind of master server to handle all of this (which I'm not at all keen on), this presents, to me at least, a challenge to widely adopting AlwaysOn. Just its management seems like it will consume huge amounts of DBA cycles.
July 17, 2015 at 9:15 am
Hi.
Here is a complete series of articles about how to manage and work with AG in case you did not know it.
http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-1-introduction
I hope that it will give some good tips.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply