Connection Strings – SQL Server DNS Aliases

The use of DNS aliases to make system changes easier and more resilient

published: 2015-09-14 12:43

In a recent post we looked at how developers can help out DBA’s by adding the “Application Name” parameter to the connection string so that this can be seen in diagnostic tools such as profiler.

However, the addition of the application name is not the only way to improve things. Changing the server attribute can work wonders as well. This type of collaboration helps create resilience in organisations, which is key objective in reducing operational issues and improving user experiences.

This example of how small initiatives can add to a company’s resilience is the use of server names or, more accurately, using an alias rather than an actual server name. These aliases can be easily created and, more importantly, easily changed using our internal or external domain name system, or DNS.

SQL Server DNS Alias Example

Suppose that we had a server named “SQLPRD1”. We could create an alias in our DNS system called “CS-APP-CRM”. Now “CS-APP-CRM” points directly to “SQLPRD1” so when connecting to the default SQL instance on “SQLPRD1” we can simply use “CS-APP-CRM” as the server name.

Name “SQLPRD1” has become “CS-APP-CRM”

SQL Server DNS Alias Benefit

The benefit of the use of the alias comes down the road when changes are occurring within the environment. This because it is extremely straightforward to change where the alias points and the change only has to be made in one place. Let’s face it, our servers can change for a number of reasons such as an upgrade or a hardware fault, so the transitions need to be as efficient as possible.

Using our previous example again, suppose we are configuring a new server to replace “SQLPRD1”. We create a new server “SQLPRD2” and move our databases across to the new server in our maintenance window.

But we have a number of applications that connect to the databases on the old host. If those applications have used the actual server name instead of an alias then we would need to visit all the applications and redirect the connection strings to the new server. Hopefully, the connection strings can be changed easily from a configuration file and are not hard coded – which would be a further complication as the apps may have to be recompiled.

Instead under the proposed method, all of our connection strings use the alias “CS-APP-CRM”. Then all we need to do is change the alias itself – in our DNS system – to point to “SQLPRD2” instead of “SQLPRD1”. We won’t need to visit any applications and even if the apps contain hard-coded connection strings they will now connect to the new server.

So, if you’re not using DNS aliases already, I’d suggest moving towards them. The alias can be used in almost any connecting application, such as via SQL Server management studio, Excel etc.

I hope you enjoyed this tip and it contributes towards the resilience of your team and organisation.

RockSolid Platform Tip

If you are using RockSolid within your environment, but are not using DNS aliases then RockSolid can help you prepare for server changes. RockSolid monitoring collects all connecting users, application and host details over time, so when you need to move one of more databases, you can quickly identify the source systems that are connected. DNS aliases will certainly help, but if you are not using them, having the connecting data on demand from RockSolid makes the migration process a lot more efficient and significantly reduces the risk of application failures post migration.

Martin is a Senior SQL Consultant with RockSolid SQL and has over 10 years’ experience as a TSQL developer and database administrator. He is a current recipient of the Microsoft Most Valuable Professional (MVP) award in SQL Server and is a regional mentor for the Professional Association of SQL Server (PASS) in the Asia Pacific area.