AlwaysOn Availability Groups and SQL Server Jobs, Part 4: Synchronizing Server-Level Details

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.