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.
5 thoughts on “Announcing the SQLskills Availability Group Demonstrator”
I owe you Jonathan…Thanks a lot! This is something which I was looking for all over the internet. This tiny(very useful) application will really help lot of DBA’s with less Frontend Application development exposure(Like me) to test and understand AG’s. Am going to utilize this awesome tool in my Lab for testing my AG’s:)
Thanks again – Sree!
Hi Jonathan, good work indeed! We were on the search for this as well.
Are we doing something wrong when we also use it for SQL Server Mirroring?
We expected that if we create a "new Connection" then "Start"; following by a Database Mirroring "Failover", that the Connection would disconnect. But although the connect string was pointing to the "Data Source" of the "Mirror DB" it was aware of the new "Principal DB".
And that was from several Clients in less than 2 Seconds (from the GUI perspective)!
For Documentation: What sort of connection provider is used in your Application?
Thanks and bye now, Markus.
Thanks Jonathan for the brilliant App, it works Great.
With this app – it becomes really easy to demonstrate the Readonly Routing.
Do you have any recomendations on a method to find the over head cost of having a database in a SYNC AG group as opposed to an ASYNC AG group? In other words how can the trans latency be measured that is introduced in a SYNC AG group?
I am unable to download this application. do not see any download link , could you please check that