Introduction to SQL Server 2012 AlwaysOn Availability Groups – Q & A

Last week, I presented my session “Introduction to SQL Server 2012 AlwaysOn Availability Groups” to my largest audience ever at the PASS DBA Fundamentals Virtual Chapter. There were 191 total attendees, and I would like to take a moment to thank all of you for attending, it was truly AWESOME! Also, I would like to take a moment to apologize for the audio issues that occurred throughout the session. This was primarily my fault, as I had joined the webinar twice, once as a presenter by phone with a high-quality headset and good quality audio connection, and another time as an attendee just to keep an eye on what all of you were seeing. Unfortunately, the attendee laptop was somehow selected as the microphone to be used while I presented from my actual presenter laptop, and that is why the audio kept fading in and out and was poor quality. Mark, Mike, and I met to discuss this and how to prevent it in the future, and so this should not happen again.

Anyway, I received several questions during this session that I wanted to address via this blog post, as I think they could benefit everyone. So without further delay, here they are:

  • What would you recommend for the maximum number of (practical) databases per Availability Group?
    • This will depend on the hardware you’re running on (specifically the number of CPU threads that the primary replica can support), and the network bandwidth available between your primary and secondary replicas. Also, the amount of transactions per second occurring in each database will be a factor in this. There are no hard-and-fast rules about how many databases can be in the Availability Group. Please see http://msdn.microsoft.com/en-us/library/ff878487.aspx#RestrictionsAG for Microsoft’s recommendations in this regard.
  • How do Availability Groups work with CDC?
  • If an Availability Group is setup at the SQL Instance level, can you have multiple SQL instances per cluster node and have an Active-Active configuration?
    • First of all, an Availability Groups is not the same as a Failover Cluster Instance. An Availability Group is a group of 1 or more databases that all failover together and share a set of replicas onto which that failover may occur. Each replica is another SQL Server instance that sits on another node of the same Windows Server Failover Cluster that the primary replica does. With that said, an Availability Group can only have replicas that are nodes of the same Windows Server Failover Cluster. Therefore, active/active in an Availability Group would be more a question about which replicas are readable or not and not so much about running multiple Availability Groups. Additionally, an Availability Group is not an instance-level failover (like in a Failover Cluster Instance), so things like the master and MSDB databases, logins, etc. do not failover in an Availability Group. You can have multiple Availability Groups running at the same time, but keep in mind that they would all need to be sitting on nodes of the same Windows Server Failover Cluster, and only one instance of SQL Server per cluster node can participate in Availability Groups due to the coordination between the Availability Groups and their underlying Windows Server Failover Cluster. To clarify that a bit, you cannot install 2 SQL Server instances to the same Windows Server Failover Cluster node and have one instance host a replica for on Availability Group and the other instance host a replica for a different Availability Group. Instead, you would have a single SQL Server Instance on the Windows Server Failover Cluster node that would participate in both of the Availability Groups.
  • Is it possible to set this up with demo licenses? Is there a temp/demo/developer clustering license available from Microsoft? (for those of us on the bench who would like to test this)
    • Absolutely! Microsoft offers an evaluation version of SQL Server 2012, which can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=29066 and used to test AlwaysOn Availability Groups. In addition, if you already have SQL Server Developer Edition licenses, you can use those licenses to test AlwaysOn Availability Groups (you just can’t use them in any production capacity).
  • Can you select which databases are in the Availability Group? Can you have two different databases from two different servers?
    • Yes, you can select which databases are part of the Availability Group. However, any database that is part of the Availability Group will need to be present on the primary replica and then synchronized to all secondary replicas. Therefore, if your primary replica has 10 databases, you could select 5 of those databases to be part of the Availability Group and those would then be synchronized to the other replicas. The 5 databases not included in the Availability Group would remain untouched and only on the server that they were on originally. The same is true of the secondary replicas. They will already contain all of the databases that are part of the Availability Group, but they can also contain a number of local databases that are not part of the Availability Group.
  • Can we use SQL Server Standard edition for only two nodes? (reference: http://msdn.microsoft.com/en-us/library/cc645993.aspx)
    • No, you cannot. What the High Availability matrix is showing is running a two node Failover Cluster Instance on Standard edition. There are only two editions of SQL Server that will support Availability Groups, and those are Enterprise and Developer editions, and both edition support up to 5 replicas in an Availability Group. Remember that AlwaysOn is just a marketing term that Microsoft uses to describe several of their High Availability features, and is not a feature in itself. Don’t let their overuse of this term confuse you.
  • Should the listener be a separate server? Does the listener need to have SQL Server installed on it?
    • The listener name is just a cluster resource name, and is not a separate physical server or cluster node, nor is it a separate SQL Server instance like a Database Mirroring Witness would be. Think of the listener name as just another service that the Windows Server Failover Cluster can host on any of its nodes. The caveat here is that the Availability Group and the Cluster are talking to one another and so the Availability Group makes sure that the listener name is always hosted by the cluster node that is the primary replica of the Availability Group. Therefore it is safe to say that the primary replica (a stand-alone SQL Server Instance installed on a Windows Server Failover Cluster node) is always the host of the listener name (if you created one).

Service Broker Overview

No Junk Mail

No poison messages either, please.

Service Broker has been a core feature of all editions of SQL Server since SQL 2005. It allows for the asynchronous delivery and processing of messages sent within a database or between databases (either on the same instance or in separate instances). In some ways, you can think of Service Broker like the USPS (in fact, SQL Server even uses Service Broker as part of Database Mail to send email messages). This blog post will use the analogy of sending a letter to a friend (yes, a snail-mail letter…yes, I know that nobody does that anymore… :-P) to give a brief overview of the various components of Service Broker and how they work together, allowing you to send a message from one instance to another instance, where the message will be processed.

Encryption & Security

By default, Service Broker will encrypt messages sent between databases (on the same instance, or on separate instances), and the best way to allow this is to use certificates. However, setting up certificate-based encryption can be a major pain in the butt, and order of operations is extremely important. The first thing you want to do is create and backup the database Master Key on each database/instance. Then, you should create a user in each database/instance with or without a login (Ex: create UserA on InstanceA and UserB on InstanceB). Once you create the users, then create a certificate for each user and back it up. Next, create a user on each database/instance that has the same name as the user on the other instance/database (Ex: create UserB on InstanceA and UserA on InstanceB), and then restore the certificate for each of the users you just made (Ex: Restore UserACert on InstanceB and UserBCert on InstanceA). Once you have the users and certificates setup correctly, then you can proceed with setting up the Service Broker infrastructure.

It is recommended to use database users without logins to restrict the users’ access to just the database that is receiving messages, but if you use a Stored Procedure to automatically process Messages received on a Queue, and if that stored procedure needs to interact with any other database(s) than the database that holds the Queue, then you will need to create a user with a login, and map that login to the other database(s) that the Stored Procedure needs to interact with.

It helps to have a central location where you can store the certificate files so that you can get to them later if you need to add more instances to your Service Broker topology.

-- Example script to setup security on AdamServer
USE Demo;
GO;

-- Create the database master key (you should back it up too, but that's not required for this to work)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyReally,Really,Really,ReallyStrongPassword...Really!';

-- You can optionally create a login to use. If you don't need the login, just create the user "WITH NO LOGIN" (instead of "FOR LOGIN AdamUser").
CREATE LOGIN AdamUser WITH PASSWORD = 'SomeOtherReally,Really,ReallyStrongPassword...Really!';

CREATE USER AdamUser FOR LOGIN AdamUser;
EXEC sp_addrolemember @rolename='db_owner', @membername='AdamUser'; -- Add security as you see fit. You probably shouldn't give away the keys to the house like I did here.

-- Create a certificate and bind it to the database user, then back it up (you must do the backup here because you will use it on the remote server to recreate this user and certificate there).
CREATE CERTIFICATE AdamUser_Cert
AUTHORIZATION AdamUser
WITH SUBJECT = 'Certificate for database user AdamUser',
EXPIRY_DATE = '12/31/2099';

BACKUP CERTIFICATE AdamUser_Cert
TO FILE = 'c:\AdamUser_cert.cer' -- It's probably a good idea to use a secure network share here
WITH
PRIVATE KEY (FILE = 'c:\AdamUser_cert.pvk', -- It's probably a good idea to use a secure network share here
ENCRYPTION BY PASSWORD = 'SomeOther,OtherReally,Really,ReallyStrongPassword...Really!');

-- Recreate the remote server's user and certificate on this server (I'm assuming you've already done the steps above on the remote server. The user doesn't need a login.)

CREATE USER FriendUser WITHOUT LOGIN;
EXEC sp_addrolemember @rolename='db_owner', @membername='FriendUser';

CREATE CERTIFICATE FriendUser_Cert
AUTHORIZATION FriendUser
FROM FILE = 'c:\FriendUser_cert.cer' -- It's probably a good idea to use a secure network share here
WITH
PRIVATE KEY (FILE = 'c:\FriendUser_Cert.pvk', -- It's probably a good idea to use a secure network share here
DECRYPTION BY PASSWORD = 'SomeOther,OtherReally,Really,ReallyStrongPassword...Really!');

Message Types & Contracts

In order to hold a conversation using Service Broker, we first have to specify one or more message types. A Message Type is simply a logical way for you to specify what kind of content will be contained in a message. In this analogy, our message type will be called FriendlyLetter (All messages sent with this type will contain letters sent between friends. We could also create another message type called BusinessLetter that would be letters sent between businesses.) and specifies that all messages sent with this Message Type will be validated as WELL_FORMED_XML. Once we have our Message Type(s) created, then we can create a Contract. In this case, our Contract will be called LetterContract, and it specifies that the parties involved in the conversation are allowed to communicate using the FriendlyLetter Message Type only.

CREATE MESSAGE TYPE FriendlyLetter
Authorization dbo
Validation = WELL_FORMED_XML;

CREATE CONTRACT LetterContract
Authorization dbo
(FriendlyLetter SENT BY ANY);

Queues & Services

In order to send and receive our messages, we need a Queue (or mailbox) to send and receive the messages with. When we create our LetterQueue, we can optionally specify an automated process (stored procedure) that will handle the messages received (in this case, dbo.FriendlyMessageHandler). If you specify an automated process to receive the messages, you should also specify how many concurrent copies of that process can be running (in this case, just 1). If you don’t specify an automated process to receive the messages, then you will need to receive them manually (see the Conversations & Messages section).

Just like in real life, our messages won’t get delivered unless there is a Service to deliver them for us (In real life, this would be the USPS. Unlike in real life, the service is only for a single Queue (whereas the USPS delivers to all mailboxes in the US). In this case, we will call our Service the LetterService, and specify that it will be for our LetterQueue, and that it will be owned by the local user we created in the Encryption & Security section.

CREATE QUEUE LetterQueue
WITH
STATUS = ON, -- Is the queue enabled to receive messages?
RETENTION = OFF, -- Does the queue keep messages that have been received off the queue, or delete them?
ACTIVATION ( -- The Activation clause is optional and only needs to be specified when automatic message processing is being used
PROCEDURE_NAME = [dbo].[FriendlyMessageHandler], -- The stored procedure that is run when a message is received
MAX_QUEUE_READERS = 1, -- How many times can we run the proc above concurrently?
EXECUTE AS 'AdamUser', -- What user will the proc be executed as?
STATUS = ON -- Is automatic activation turned on or off for this queue?
);

CREATE SERVICE [//AdamServer/LetterService] -- I recommend naming your services: ///, so that it's easier to tell what server and service you're conversing with
AUTHORIZATION [AdamUser] -- The user that owns this service
ON QUEUE LetterQueue -- What queue is this service for?
([LetterContract]); -- What contract governs conversations over this service?

GRANT SEND ON SERVICE::[//AdamServer/LetterService] to FriendUser; -- Allow the other server's user to send this server messages

Endpoints, Routes, & Remote Service Bindings

Now that we have our Queue and Service created, we need to create a Route and Endpoint so that the Service knows where and how to send messages. We only need one Endpoint per instance, and in this case, our Endpoint will be called SBEndpoint. In order for our Service to know where to send the messages to, we need to specify a Route. In this case, our Route will be called SendLetterRoute, will specify that it is for the receiving server’s LetterService, and will also specify where that service can be found. We also need to create a route in MSDB for receiving messages to our local service, which I’ve called ReceiveLetterRoute.

The last piece of infrastructure we need to create is the Remote Service Binding. This tells SQL Server which user to use when sending messages to the receiving service. Just like with Routes, we need to create one Remote Service Binding for each receiving service we want to send to.

USE MSDB;
GO;

CREATE ENDPOINT SBEndpoint
AUTHORIZATION sa
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS);

CREATE ROUTE ReceiveLetterRoute
AUTHORIZATION dbo
WITH
SERVICE_NAME = N'//AdamServer/LetterService',
ADDRESS = N'LOCAL';

USE Demo;
GO;

CREATE ROUTE SendLetterRoute
AUTHORIZATION dbo
WITH
SERVICE_NAME = N'//FriendServer/LetterService',
ADDRESS = N'TCP://FriendServer:4022';

CREATE REMOTE SERVICE BINDING [SendLetterBinding]
AUTHORIZATION dbo
TO SERVICE N'//FriendServer/LetterService'
WITH USER = [AdamUser]; -- The user to send the message as

Conversations & Messages

Once all of the infrastructure pieces are created on both the sending and receiving sides, we can begin to hold a conversation/Dialog. When we begin our Dialog, we specify which local service is sending the message and to which remote service it is sending the message. We must also specify the Contract that governs how the two sides may communicate on that Dialog. In this case, our Dialog will be sent from //AdamServer/LetterService to //FriendServer/LetterService on Contract LetterContract. Once the Dialog has been started, we can send messages (of any type allowed by the contract), and the messages will be received in the same order in which they are sent. Since it is possible to send more than one message over a single Dialog, it is recommended not to begin new Dialogs unless absolutely necessary (beginning a new Dialog creates overhead as the two servers authenticate and encrypt a new channel of communication, whereas using an existing Dialog does not incur that overhead).

When the receiving side gets a message, the message is placed in the Queue specified by the receiving Service. If there is an automated process specified to process messages on that queue, then that Stored Procedure will be executed and will process the message. In order to process a message (either automatically or manually), you must Receive the message off of the Queue. In most cases, you will receive one message at a time to make sure that the messages are received and processed in the correct order.

-- Execute on sender:
DECLARE @ConversationHandle uniqueidentifier;
DECLARE @XMLMessage XML = 'Hi!';

BEGIN DIALOG @ConversationHandle
FROM SERVICE [//AdamServer/LetterService]
TO SERVICE N'//FriendServer/LetterService'
ON CONTRACT [LetterContract]
WITH
ENCRYPTION = ON;

SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [FriendlyLetter]
(@XMLMessage);
-- Execute on receiver:
declare @ConversationHandle uniqueidentifier, @Service varchar(128), @Contract varchar(128), @MessageType varchar(128), @XMLMessage xml;

WAITFOR (
RECEIVE TOP(1)
@ConversationHandle = conversation_handle,
@Service = service_name,
@Contract = service_contract_name,
@MessageType = message_type_name,
@XMLMessage = CAST(message_body AS XML)
FROM LetterQueue
), TIMEOUT 5000; -- Wait 5 seconds

Bringing It All Together

Service Broker Diagram

Click the image to see it full size in a new window

References