When setting up high availability (HA) or HA+disaster recovery (DR) solutions with SQL Server AlwaysOn Availability Groups, it’s critical to remember that the basic unit of failover in these scenarios is the availability group (or the group of user-defined databases). As such, things typically scoped at the server (or instance) level are not made redundant or synchronized via AlwaysOn Availability Groups. And, quite sadly, there’s typically very little about this mentioned in otherwise detailed white papers, Books Online, and other resources provided by Microsoft in regards to setting up AlwaysOn Availability Groups.
Part 1: Introduction
Part 2: Putting AlwaysOn into Context
Part 3: Defining Batch Jobs
Synchronization Concerns
Since the underlying purpose of creating HA and DR solutions is to provide redundancy to pre-emptively address failures (i.e., at the local/system level for HA concerns and at the datacenter level for DR concerns), then it stands to reason that making data redundant is a start, but not enough. To make entire storage solutions redundant, organizations need to make additional server level (or instance level) details redundant, including:
- Logins. For administrators new to SQL Server, the fact that logins are scoped at the server/instance level while users are scoped at the database level is a constant source of confusion. I’ve distilled the root of this problem into a free SQL Server video, but even once this problem is totally understood, it still means that Logins (and their mappings to underlying users) have to be synchronized across all servers that will be hosting an Availability Group. Happily, however, SQL Server 2012 gives us the rudiments of an approach to move authentication down into the database through the idea or notion of Contained Databases. Accordingly, a best practice for dealing with AlwaysOn Availability Groups is to make Users portable by using the notion of a contained user. I’ve outlined the specifics in a previous article on SQL Server 2012 Contained Databases. It is worth mentioning, however, that while containment is a great feature (or will be once/if we get a more fully-developed feature set from Microsoft), that there is the potential for containment to cause SOME issues with interaction between user databases and the tempdb—so make sure you test this functionality in FULL before using contained users in a production setting.
- Linked Servers. In situations where there’s a need to let SQL Server talk to a remote server, the most secure way to do this is force connectivity via Linked Servers (or explicitly defined remote endpoints) as opposed to utterly insane approach of allowing ad-hoc distributed queries. Consequently, if your Availability Group databases need to talk with an older AS/400 or another SQL Server or some Oracle databases, then you’ll need to define these Linked Server details on whichever hosts are participating in your Availability Groups—otherwise failover from the primary replica will bring communications failure.
- Endpoints. If Service Broker or other advanced components are being used, they’ll need to be addressed, as well.
- Resource Pools. I’ve worked with SQL Server’s Resource Governor and SQL Server’s AlwaysOn Availability Groups—but never the two together. If you’re trying to mix and match the two to create HA solutions where SLAs are maintained by throttling some activities, then you’ll need to make sure that the resource pools and definitions you create on your primary server are synchronized to the other servers in your topology as well.
- Alerts. Alerts are, in my experience, a totally underused feature of the SQL Server Agent—but for those organizations that want to pre-emptively be notified of potential problems or issues with their SQL Server, or who wish to know about situations when an Availability Group Failover actually occurs, then Alerts will need to be synchronized across all servers participating in an Availability Group topology, as well. (I’ll actually be covering this in more detail in my next post.)
- SQL Server Agent Jobs. As outlined in my previous post on defining batch jobs, there are a host of different kinds of jobs that will need to be synchronized—and potentially modified—to work correctly once AlwaysOn Availability Groups are thrown into the mix and once user databases can jump from on server to another.
- SQL Server Backups. Similar in concept to the notion of Jobs, backups also require some additional tweaking and extra consideration once databases can move between different hosts.
The Peril of Non-Synchronization
Initial synchronization of most of the details outlined above is borderline trivial in that it just typically requires scripting objects and running those definitions on other servers. Jobs and backups are bit more complex because they end up needing to address the notion of a target.
The rub, of course, is that just because something was scripted and synchronized initially, doesn’t mean it’ll stay that way. And therein lies one of the core problems addressed in this series of posts—because without either some way of enforcing synchronization or being alerted to when things go out of sync, then the reality is that the Failover of an Availability Group from one host to another could be a success at the data level, but might result in a failure or disaster because of missing components or dependencies on the new host.
Next: AlwaysOn Availability Groups and SQL Server Jobs, Part 5: Setting Up Failover Alerts
