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 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 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:
    • 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).

Creating a Login with a SID

For those of you who don’t already know, there is a new PASS Virtual Chapter called DBA Fundamentals that has meetings on the first Tuesday of every month. The chapter specializes in providing training and support for new DBAs and also for seasoned DBAs who would just like to brush-up on their skills. I was fortunate enough to be asked to present at their second meeting on 9/4, and I gave my session about SQL Server Service Broker. One of the questions that I was asked during the session was: How do you create a login with a SID, and more specifically, how do you create and find the SID for that login?

In my session on Service Broker and also in my session on AlwaysOn Availability Groups, I mention that if you’re using AlwaysOn Availability Groups or Database Mirroring that you should create your logins with the same SID on each instance/replica so that when a failover occurs, you don’t have to re-map your database users to their appropriate logins. However, I don’t really go into the detail of how to do that in my sessions, so I wanted to take some time to do that here.

use master;

-- Get a new GUID value to use as the SID
declare @SID uniqueidentifier = newid();

-- In order to use the SID in a create login statement, it must be in binary format
declare @SID_Binary varbinary(max) = (select cast(@SID as varbinary(max)));

-- View the SID in GUID and Binary format:
select @SID, @SID_Binary;
-- E72669E3-9FAA-4BCB-8F8F-570EBF114674, 0xE36926E7AA9FCB4B8F8F570EBF114674

-- Here is the statement we really want to run:
--create login SQLDiablo with password='Passw0rd!', sid=0xE36926E7AA9FCB4B8F8F570EBF114674;

-- But that requires us to paste in the SID. There has to be a better way:
declare @UserName nvarchar(128) = 'SQLDiablo', @Password nvarchar(max) = 'Passw0rd!';
declare @Query nvarchar(max) = 'create login ' + @UserName + ' with password=''' + @Password + ''', sid=0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@SID_Binary") )', 'varchar(max)') + ';';

select @Query;
execute sp_executesql @Query;

-- Since varbinary can be a little tricky to work with in dynamic SQL, XPath is our friend.
-- Above we converted the value of @SID_Binary to Hex using XPath's value method (don't forget to add 0x to the beginning of it).

-- Get the SID for the login we just created, as a GUID
select, cast(sp.sid as uniqueidentifier) SID_AS_GUID from sys.server_principals sp where = 'SQLDiablo';

-- SQLDiablo, E72669E3-9FAA-4BCB-8F8F-570EBF114674

set @Query = 'drop login ' + @UserName + ';';
execute sp_executesql @Query;