Posted by: Denny Cherry
AlwaysOn, Availability Groups, Database, Microsoft Cluster Service, MSCS, SQL Server, SQL Server 2012, SSMS
By now hopefully everyone knows about AlwaysOn Availability Groups in SQL Server 2012 and the high availability options that they provide to databases. One problem with AlwaysOn Availability Groups when combined with third party applications is that the third party application may configure the connection string and not allow you to change it. When you want to install one of these third party applications within a SQL Server 2012 availability group this can give you some problems as typically you would typically create the database then add the database to the availability group. However this requires that you change the connection string which in this case we are trying to avoid.
There however is some good news to this little problem. The good news in this case is that you can create a SQL Server availability group, which has an availability group listener, without putting any databases in it. This is done by creating the availability group without using wizard that is available within SQL Server Management Studio. Instead of starting the wizard select the “New Availability Group…” option from the Availability Group context menu as shown below.
This will allow you to create an Availability Group without any availability groups and with only a single replica. Once the availability group is created, the listener can be created for the availability group. The 3rd party application can then use the listener to connect to the database engine and create the database. The database once created can be added to the availability group as can the additional replicas.
While using this technique is a lot harder than going through the wizard as it requires that the database backups and restores be done manually and the configurations be all done by hand instead of the handy wizard it’ll meet the requirements of the application which is to not change the connection string.