This spring at SQL Connections, I presented a pre-conference seminar with Kimberly on the new features in SQL Server 2012, including Availability Groups. While on my flight to Las Vegas, I was working on an application in C# that motivated me to write a new demonstrator application for use with Availability Groups. I spent a little time putting together a small application that I have used for every Availability Group demonstration since, and I’ve been asked a few times if I would make the application publicly available. Paul and I discussed this and agreed that we’d offer the application as a free download.
The application is really simple in its design and usage, so I am going to use this post to show how to configure the application as well as how to use it for demonstrations of Availability Groups, or just to play with all the features provided by Availability Groups during your own testing.
Application Configuration Options
The application uses a basic application configuration file that most .NET applications use. The configuration file is really simple and provides the ability to customize the defaults that are available in the New Connection window to simplify demonstrations based on your own environment setup.
The settings in the configuration file provide the default values for the following options in the New Connection window.
Using the application
I usually start out with a simple Availability Group (hereafter referred to as an AG) configuration where a SQL Server login is used and the AG was created through the UI. I use the application to connect directly to the primary server using a SQL Server login without using the Availability Group Listener to show how you can connect directly to the primary server with its name. Then I perform a failover to the synchronous replica that is configured for high availability, and open a new connection to show that the connectivity will fail since the SQL Server login wasn’t created on the secondary. Then I use DDL to create the login and show that even though I’ve created the login on the secondary, it will fail because the SID for the new login isn’t the same as the SID that existed on the original primary server. At this point I explain that you could use sp_help_revlogin to transfer the login with the SID intact, but then show that Partially Contained Databases with database authentication can prevent having to manually migrate every login, so I failover to the original primary and setup partial containment on all the nodes and migrate the server level login to being a contained database login. Then I close all of the connection windows and create a new connection that uses the Availability Group Listener for the connection so that I can then failover between the primary replica and the high availability synchronous successfully. A single New Connection in the application can support this.
Then I configure ReadOnly routing for the AG and show two connections, one configured for the Read/Write workload, and another configured as a ReadOnly connection to the listener using a ReadOnly workload.
After setting up ReadOnly routing, and then specifying that the connection is for ReadOnly operation in the application, it will automatically redirect the connections to the first readable secondary in the routing configuration as expected. Performing a failover will allow the application to show the expected failover error messages and then redirect the applications connections based on the configuration of the ApplicationConnection windows to the desired hosts in the AG.
An example of the failover messages returned is below:
Once the failover completes, the application will reconnect to the appropriate nodes based on the ReadOnly routing configuration for the AG.
The application requires .NET 4.0 and the SQL Server 2012 Native Client be installed to make use of the new ApplicationIntent connection string option.
You also need to create the following table in whatever database you are using for the demonstration:
CREATE TABLE InsertCount (RowID int identity primary key)
INSERT INTO InsertCount DEFAULT VALUES;
The application can be downloaded from:
If you have any questions, feel free to email me at firstname.lastname@example.org or through the contact form on this blog.