While visiting a customer and helping them set up AlwaysOn Availability Groups we came across a surprising "feature". On my laptop I had build a demo lab consisting of one domain controller, three Windows 2008R2 Core member servers with SQL 2012 SP1 installed and a full Windows 2008 R2 installation with SQL Server management studio installed. I had setup the cluster, enabled AlwaysOn and created an empty database to create an availability group. I tested the availability group by manually failing over using management studio and this worked fine.

But that's in the demo world. Usually we have all kind of conditions that cause our demo's not to work with our customers. In this case the customer had set up an availability group as well and the failover was working when initiated from management studio. But stopping the SQL Server service caused the Availability Group to stay in a resolving state and never failover. I tried this on my machine and it failed over perfectly. The only difference between my setup and the customer was that I was using a three node cluster and they were using a two node + file share cluster. To match the customers setup I removed the third replica from the availability group and evicted this node from the cluster. This left me with a two node cluster. What is important to remember is that this is not a recommended setup. In this setup you will never have majority and the cluster will go offline when one of the nodes goes offline. This basically defeats the purpose of a cluster. But to satisfy my curiosity I stopped the SQL Server service. To my surprise the availability group failed over to the other node.

A quick recap:

• Three node cluster with an availability group: Stopping the service fails over the availability group
• Two node cluster (not recommended) with an availability group: Stopping the service fails over the availability group
• Two node with file share witness: Stopping the service leaves the secondary in a resolving state and the listener remains offline

This is a known issue and if you read the signs along the road you would have noticed this. The reason I missed it at first is that in the earlier builds the link in the warning did not work. Below is a screenshot of the warning you will get when you perform a failover in the third scenario.

There is a KB article that describes this behavior (http://support.microsoft.com/kb/2761129) and this references the same KB article (http://support.microsoft.com/kb/2494036) that is mentioned in the link in the warning message. In short it turns out that the nodes do not have a vote. You can see this by querying sys.dm_hadr_cluster_members or clicking the View Cluster Quorum Information link in the AlwaysOn dashboard. This will show NULL or Not Available. This means the nodes do not have a vote and we need the vote to determine if there is quorum.

After applying the hotfix we get the desired behavior and the dmv and dashboard show 1 indicating each node has a vote. Stopping the service results in a failover leaving the new primary in a Not Synchronizing state. This is the expected state but leaves the availability group at risk. Another replica must come online as soon as possible to start synchronizing. In this state the transaction log will not be able to truncate and reuse the space within the log file.

In SQL Server you can use temporary tables to store intermediate results. This is a common used technique to speed up query processing. Recently I came across a problem where the temporary tables were causing the performance degradation. I'm not going into the different temporary tables types, there are good posts on these. I'm going to walk you through a real-life example and show that the most given answer in the SQL Server world holds true; "It Depends".

In this case the desired result was a single stored procedure that would return multiple results based on some or no search criteria. If a user navigated to the page the complete set was returned and only one filter was applied. In the result set there was a row count, a paged result and some distinct fields from the total results to provide additional filtering. Think of these as slicers and dicers you use in Excel. I will demonstrate the general idea using the AdventureWorks database. I'm using the 2008 version here.

DECLARE @t table (id int IDENTITY, salesorderid int)

INSERT INTO @t (salesorderid)

SELECT soh.SalesOrderID FROM Sales.SalesOrderHeader soh

INNER JOIN Purchasing.ShipMethod sm ON soh.ShipMethodID = sm.ShipMethodID

WHERE sm.Name = 'CARGO TRANSPORT 5'

ORDER BY soh.OrderDate DESC;

-- 1 Get Rowcount

SELECT COUNT(*) AS TotalRecords FROM @t;

-- 2 Get Current page

SELECT soh.OrderDate, soh.SubTotal, st.Name, p.LastName, c.AccountNumber FROM Sales.SalesOrderHeader soh

INNER JOIN @t t ON soh.SalesOrderID = t.salesorderid

INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID

INNER JOIN Sales.SalesPerson sp ON soh.SalesPersonID = sp.BusinessEntityID

INNER JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID

INNER JOIN Person.Person p ON sp.BusinessEntityID = p.BusinessEntityID

WHERE t.id BETWEEN 1 AND 20

ORDER BY soh.OrderDate DESC;

-- 3 Get SalesTerritory values

SELECT DISTINCT st.TerritoryID, st.Name FROM Sales.SalesTerritory st

INNER JOIN Sales.SalesOrderHeader soh ON st.TerritoryID = soh.TerritoryID

INNER JOIN @t t ON soh.SalesOrderID = t.salesorderid

ORdER BY st.Name;

-- 4 Get OrderDate Year values

SELECT DISTINCT YEAR(soh.OrderDate) as OrderDateYear FROM Sales.SalesOrderHeader soh

INNER JOIN @t t ON soh.SalesOrderID = t.salesorderid

ORDER BY OrderDateYear;

In preparation a table variable is filled with the filtered set of ID's and is sorted in the desired order. In the first query we retrieve the row count for the set. The second set is the actual result set the user will see in the application. In this case it's the first page with 20 results. In step 3 and 4 additional results are returned that are used in the application to provide quick filters for the user. When I run this query on my database I get 4 sets with a total of 35 rows. For this I used 25628 reads. That seemed a lot to me. The execution plan for this one showed some missing indexes and some clustered index scans here and there. I added a few indexes. If you are running along, here they are:

CREATE NONCLUSTERED INDEX temp_SALESORDERHEADER_ShipMethodId ON Sales.SalesOrderHeader(ShipMethodId) INCLUDE(SalesOrderID, OrderDate);

CREATE NONCLUSTERED INDEX temp_SALESORDERHEADER_Select ON Sales.SalesOrderHeader(SalesOrderId, OrderDate) INCLUDE(CustomerID, SalesPersonID, TerritoryID, SubTotal);

CREATE NONCLUSTERED INDEX temp_SALESORDERHEADER_Territory ON Sales.SalesOrderHeader(SalesOrderID, TerritoryID)

CREATE NONCLUSTERED INDEX temp_TERRITORY_Name ON Sales.SalesTerritory(TerritoryID, Name);

CREATE NONCLUSTERED INDEX temp_PERSON_Lastname ON Person.Person(LastName)

This got my reads down to 900. That's to show you will need a proper indexing strategy. But still, more work could be done. One of the things I tried first was use a temporary table instead of a table variable. But this increased my reads. Although I could add indexes the index creation itself would cause even more reads. And in this example the set is not that big but when I was working with the real set things got ugly pretty quickly.

The last thing I tried was to remove the temporary tables altogether and use a view. My thought was to create a view with the search criteria in the view and maybe index that view. This view could easily be reused by the other sets. The definition of the view and the adjusted queries are below:

CREATE VIEW vSearch AS

SELECT soh.SalesOrderID, soh.TerritoryID, soh.OrderDate, soh.SalesPersonID, soh.CustomerID, soh.SubTotal, sm.Name

FROM Sales.SalesOrderHeader soh

INNER JOIN Purchasing.ShipMethod sm ON soh.ShipMethodID = sm.ShipMethodID

GO

-- 1 Get Rowcount

SELECT COUNT(*) AS TotalRecords FROM vSearch WHERE Name = 'CARGO TRANSPORT 5'

-- 2 Get Current page

SELECT v.OrderDate, v.SubTotal, st.Name, p.LastName, c.AccountNumber FROM

(SELECT ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS row_id, SalesOrderID,

TerritoryID, SalesPersonID, CustomerID, OrderDate, SubTotal

FROM vSearch WHERE Name = 'CARGO TRANSPORT 5') v

INNER JOIN Sales.SalesTerritory st ON v.TerritoryID = st.TerritoryID

INNER JOIN Sales.Customer c ON v.CustomerID = c.CustomerID

INNER JOIN Person.Person p ON v.SalesPersonID = p.BusinessEntityID

WHERE v.row_id BETWEEN 1 AND 20

ORDER BY v.OrderDate DESC;

-- 3 Get SalesTerritory values

SELECT DISTINCT st.TerritoryID, st.Name FROM Sales.SalesTerritory st

INNER JOIN vSearch v ON v.TerritoryID = st.TerritoryID

WHERE v.Name = 'CARGO TRANSPORT 5'

ORdER BY st.Name;

-- 4 Get OrderDate Year values

SELECT DISTINCT YEAR(v.OrderDate) as OrderDateYear FROM vSearch v

WHERE v.Name = 'CARGO TRANSPORT 5'

ORDER BY OrderDateYear;

An additional index was needed:

CREATE NONCLUSTERED INDEX temp_IX_SALESORDERHEADER_ShipMethodID ON Sales.SalesOrderHeader (ShipMethodID) INCLUDE (OrderDate,CustomerID,SalesPersonID,TerritoryID,SubTotal);

This resulted in 466 reads. More optimizations can be done bit a big win was gained by using a view and just retrieving the data directly instead of using temporary sets.

A big lesson lies in the creation of the temporary objects. If you create a simple table with two columns and just 10 records and you select the table you will only get a few reads. If you create a table variable, insert the real table into the variable and select it again you get a couple of hundred reads. On small sets you won't notice a big difference but on larger sets the difference on cpu, reads, writes and duration is really noticeable.

In my opinion SQL or Windows Authentication isn’t a choice. You should always use Windows authentication unless technically impossible. The most common scenario is when the user account is not in the same domain as the SQL Instance and there is no trust relationship. Or when the server is based on an architecture that does not support Windows Authentication like Unix.

In most other scenarios you can use Windows Authentication and you should. In this post I will walk you through a scenario where we change a web application from SQL Authentication to Windows authentication. Eventually we will also use Kerberos and constrained delegation to get the users credentials all the way to SQL Server.

To get things running we use two identical web applications listed below. The only thing the web application does is get information about the connection from SQL Server. To be able to see this information the accounts are granted view server state permissions.

<%@ Page Language=”C#” %> 
 <!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.1//EN” 
 “http://www.w3.org/TR/xhml11/DTD/xhtml11.dtd”> 
 <script runat=”server”> 
 </script> 
 <html xmlns=”http://www.w3.org/1999/xhtml” > 
 <body> 
 <form id=”form1” runat=”sever”> 
 <div> 
 <asp:SqlDataSource ID=”sqlDataSourceMaster” runat=”server” SelectCommand=”select s.session_id, net_transport, auth_scheme, s.host_name, s.login_name, s.program_name from sys.dm_exec_connections c inner join sys.dm_exec_sessions s on c.session_id = s.session_id where s.session_id = @@SPID;” 
 ConnectionString=”<%$ ConnectionStrings:connectionStringMaster %>” 
 DataSourceMode=”DataReader”> 
 </asp:SqlDataSource> 
 <asp:GridView ID=”gridViewMaster” runat=”server” 
 DataSourceID=”sqlDataSourceMaster” 
 DataKeyNames=”session_id” AutoGenerateColumns=”true”> 
 <asp:GridView> 
 </div> 
 </form> 
 </body> 
 </html>

<configuration> 
 <connectionStrings> 
 <add name=”connectionStringMaster” providerName=”System.Data.SqlClient” 
 connectionString=”server=sql-05;database=master;uid=appusr1;pwd-p@ssw0rd;app=site1” /> 
 </connectionStrings> 
 </configuration>

The HTML page includes some C# script to retrieve the current session information from SQL Server. Notice in the connection string the connection is made using SQL Authentication. Also I’m using the app directive to distinguish the connection.

This is nothing different than the ordinary web application using SQL Server. First step is to change from SQL Authentication to Windows Authentication. For this we change the connection string to: server=sql-05;database=master;integrated security=sspi;app=site1.

After a refresh of the browser the application fails. Why? The event log on the webserver and the SQL log provide the information; Login failed for user ‘CONTOSO\IIS-01$’. The dollar signs tells us we are dealing with a computer or managed service account. In this case the computer account. We changed from SQL to Windows Authentication and now instead of authenticating with the SQL login ADO.NET is authenticating with the credentials of the process running the web application. The reason the computer account is used is because the application pool in which the web application is running is running with either the NETWORK SERVICE account or the ApplicationPoolIdentity. The ApplicationPoolIdentity is new for IIS7 and is used to isolate the processes on the machine itself, but when connecting to other machines it will connect in the same way as the NETWORK SERVICE account, using the computer account. To get the application running again we can add the computer account as a login on the SQL box and grant the VIEW SERVER STATE securable.

As you may notice we are connecting with NTLM. NTLM is an older authentication protocol where username and password are exchanged. It’s more secure to use Kerberos where there is only a username password exchange between a Kerberos server, most of the times a domain controller, and the client. The generated ticket will then be used for further authentication. Getting our application to use Kerberos instead of NTLM is a breeze, all you need is a domain admin or delegated permissions and set the proper SPNs for SQL Server using SETSPN.

SETSPN –A MSSQLSvc/sql-05.contoso.com:1433 CONTOSO\sqlsvc

And that’s it. Refreshing the websites gives me Kerberos authentication.

Now we are going to take things one step further. If you have successfully got Kerberos working you can get constrained delegation to work. In this scenario we get the user account requesting the webpage. A few more steps have to be taken to achieve that.

1. The website has to be set to Windows Authentication using the IIS manager.
2. In the web.config we add this section
   <system.web>
<identity impersonate=”true” />
</system.web>
I got an 500.24 error. Apparently because .NET 2.0 needs classic mode for an IIS7 application pool. This error went away after reconfiguring the application pool. More info: http://learn.iis.net/page.aspx/381/aspnet-20-breaking-changes-on-iis-70/
3. Set the computer account in active directory as Trusted for Delegation.

And that’s it.

As you can see in the screenshot above both sites use Kerberos authentication and in both cases the user credentials are send to SQL Server.

This is a quick introduction to show you how easy you can setup Windows authentication in favor of SQL Authentication and additionally use Kerberos as an authentication mechanism. Ultimately you can leverage Kerberos to use constrained delegation and get the user credentials all the way to the SQL Server, often referred to as ‘Double Hop’. As this is a quick way to introduce this it’s by far not the complete list of possibilities. To further investigate the techniques involved I suggest you take a look at the sites listed below:

http://technet.microsoft.com/en-us/library/cc995228.aspx
http://msdn.microsoft.com/en-us/library/ff649317.aspx
http://msdn.microsoft.com/en-us/library/ee191523.aspx

With the freshly released SQL Server 2012 we can put availability groups to live. You have seen all the demo's and now you are ready to get things going in your own datacenter. At this point you might get to a few surprises. One of these might be to get Kerberos going for your availability groups. If we recap the theory behind availability groups we get a more clearer picture.

In SQL Server 2012 we use availability groups as a unit of failover. We have up to five single SQL instances. The underlying nodes participate in the same Windows Failover Cluster. In the cluster configuration you will notice a few resources shared amongst these independent SQL instances:

• Availability Group Resource
• IP-Address
• Virtual Network Name

Now if you want to use Kerberos you must make sure all prerequirements are met. The most important thing being a service principal name, or SPN. We set a SPN using the SETSPN command that's included in the Windows. A good start on more information is Books Online: http://msdn.microsoft.com/en-us/library/ms191153(v=sql.110).aspx

Getting Kerberos to work on just SQL should not be hard. So now we introduce availability groups. This is where it gets harder. To get Kerberos working on availability groups you must also set a SPN on the virtual network name.

So if you have an availability group named AG-SQL-1 in the CONTOSO.COM domain you must set a SPN on ag-sql-1.contoso.com with the correct port and the domain account running the SQL Server process. So here is a catch. What if the availability group fails over to another node. It needs to be registered again unless that node is running with the same domain credentials.

Bottom line: if you want to use Kerberos with availability groups you need to run all nodes with the same domain account. This will save you a lot of headaches.

You can read up on it in Books Online: http://msdn.microsoft.com/en-us/library/ff878487(v=sql.110).aspx#PrerequisitesSI

Last week RC0 for SQL Server 2012 was released. If you missed it you can find it here: http://www.microsoft.com/download/en/details.aspx?id=28145.

One area where you will find improvements or changes is AlwaysOn Availability Groups. In this post I will step through the wizard to show you the improvements that were made. The basis is a 4 node Windows Server 2008 R2 cluster with a stand alone SQL Server 2012 RC0 installation on each of them. All instances use the same domain account. On my first instance, SQL-02, I have two databases; AlwaysOnDb1 and AlwaysOnDb2. I will create an availability group for these databases and sync these to three replica’s.

• In Management Studio connect to the first instance, SQL-02 and navigate to the new AlwaysOn High Availability tree item. Right click it and choose New Availability Group Wizard.
• Skip the Welcome Screen by clicking Next.
• First specify the name for the availability group. I’m using AG-AlwaysOn-1. Click Next to proceed.
• In the next screen you can specify the databases you can use in your availability group. Choose all that apply and click Next to proceed. If for some reason the database cannot be chosen check the status column.
  
• The next screen is where the real magic happens. This screen has changed a lot since CTP3. Let’s go through each tab. The first tab is Replicas. This is where you will specify the replicas. Add an replica by clicking Add Replica and choose your favorite option. I’m using all four instances.
  
• The second tab is Endpoints. This is where you will specify the endpoints used by the mirroring sessions. By default these are encrypted.
• The third tab is Backup Preferences. This is where you specify where the backups are taken. But remember, this is not forced. You have to create the logic yourself. Read more about backups on replicas here: http://msdn.microsoft.com/en-us/library/hh245119(v=sql.110).aspx. To demonstrate this later on I will set the top priority on SQL-04, then SQL-03 and last SQL-02. I will configure SQL-05 as a machine where no backups are allowed.
  
• The final tab on this screen is the Listener tab. The listener is the connection between the availability group and the application. The name specified in this tab, is the name you use for your connection. Provide a name and a port and set the network mode to Static or DHCP according to your network setup.
  
• Click Next to continue and specify how you want the data to be initialized. There are three options here:
  1. Full – If you want the wizard to take care of the synchronization.
  2. Join Only – If you have already placed the databases on the replica’s.
  3. Skip – If you plan to place the databases on another time.
• Clicking Next will validate your configuration and the greener the better.
  
• Next followed by Finish will create the availability group.
  
• The warning is related to the Windows Server cluster configuration. In my case this is not the most desired configuration. The warning provides a link to the KB article that describes the quorum models and what the preferred one is.

Now that the availability group is created we can open up the dashboard to view the health. You can open the dashboard by right clicking the AlwaysOn High Availability item in management studio and choose Show Dashboard. This is what it looks like.

To quickly check if I can perform backups on a replica I connect to SQL-05 using management studio, open a new query window and create a COPY_ONLY backup. This will work, although I specified during the wizard that this replica was not available for backups. So remember, you can always create a COPY_ONLY full or a log backup on the replicas. For log backups remember that these will also be part of the log chain. You WILL need these to restore your database to a point in time. You can create backup logic with the new sys.fn_hadr_backup_is_preferred_replica function. Here is an example.

SQL Server 2012 RC0 provides an even better experience for the availability groups but you still have to be in control. Know what you are doing. Where you want backups to happen. If log backups are made on replicas. With greater powers come greater responsibilities.