Using DNS aliases to manage database connections

That (new SQL Server) smell

If you want a lively discussion among DBA’s, ask them their opinions on the following topics: using tbl_ and vw_ when naming objects, if SSMS should be installed on your servers, using mount points vs. drive letters, and in-place vs. new build upgrades. While I’m passionate about some of those, the one that I fall firmly on one side of the fence on is upgrades: I always try to build a new server if I can.

Of course, that’s not always easy: in addition to moving your actual data to a new server, you have to worry about things like user permissions, linked servers, SSIS catalogs, agent jobs… the list goes on and on. A checklist or runbook is almost always need to make sure you got everything, and it behooves you to do this in stages, starting with your test environments and moving up, slowly, towards your production systems.

But we’re looking at the process in terms of DBA work. What about your developers and customers who actually connect to and use the databases? Application developers need to update their connection strings to point to the new server, and your customers need to know the new server address as well. Plenty of times, this is where things fall down: it’s easy to build a server and install SQL Server on it, but once the server’s up, how do you ensure everyone is actually connecting to the right place? And most of all: how can you avoid unexpected downtime when you shut down the old server for the stragglers who didn’t update their connections? The solution is to make sure they don’t have to. Let me explain…

When I move you move (just like that)

When you connect to a SQL Server instance, you’re most likely connecting directly to the host name of the server running that instance. So for example, if the host name of my instance is SQLSERVER-A, then in my SSMS connection (or application) I probably type in a host name of fully qualified domain name (FQDN) of a server. If I want to move a database from one server to another, or stand up a new server and move everything over to it, from now on I’ll need to make sure I type in the new host name. And for a DBA, this is fine. We mostly identify our servers by the hosts they run on.

Developers and users, on the other hand, don’t always think like that. They each probably only care about one or two databases on a given instance, and depending on their release cycle even something as simple as changing a connection string might need to be a scheduled change. So when databases move or you build a new server you not only need to ensure as little downtime as possible from a system perspective but with as little impact to a user’s system too. And you can do that with the help of your network team and your local domain name system: DNS.

What’s my (C)NAME?

In a Windows Active Directory Domain, (almost) every PC on the network gets registered in DNS. As a quick example, let’s look at my lab:

I have a domain controller, a DNS server, and 3 computer objects in the domain: two SQL servers, and a user workstation. I have a copy of AdventureWorks2014 on SQLSERVER-A. My DNS looks like this, which you can view in DNS manager:

To see all the currently registered entries, we need to look at the Forward Lookup Zone. A Forward Lookup Zone is where most name resolution occurs when a computer requests a lookup. What we see here are A records, which is the most basic mapping of a hostname to an IP address. In an Active Directory domain, when a computer joins the domain it (almost always) gets a DNS record registered as well. If type in SQLSERVER-A in, say, SQL Server Management Studio to connect, what really happens behind the scenes is that hostname is checked against DNS for an IP address and then routed to the appropriate server.

But there’s more to DNS than A records. You can also create CNAMES, which are aliases for existing A records. This lets us do some really neat stuff, like creating a domain alias for a particular database! So, if I have a copy of AdventureWorks on SQLSERVER-A, I can create a special CNAME that references just SQLSERVER-A for AdventureWorks. If I right-click on the Forward Lookup Zone for my domain, and click “New Alias (CNAME)…” I get a dialog box that looks like this:

I am providing input in the first and third boxes only. In the first box, I am creating a multi-part alias in the convention of DatabaseName.Environment.SQL. When I click okay and refresh my forward lookup zone, I see a new SQL folder in my zone. That’s because I used .SQL at the end of the alias. It automatically organizes everything with that suffix in a folder. And inside there, the environments, and inside there, the database names. So if I wanted an alias for AdventureWorks in QA, I would make another CNAME record with AdventureWorks.QA.SQL which might point at a different server that has AdventureWorks running on it. At the bottom of the dialog box is he Time to Live (TTL) of the record. We’ll talk more about this in a bit, but for now we’ll set it to 5 minutes.

I want to connect to the instance but instead of typing in “SQLSERVER-A” in my connection box, I could do type this instead to connect:

You’re probably wondering why this all matters. It matters because if you need to make a change to where your databases live (either moving a database from one SQL to another, or retiring an existing SQL server) you need to ensure every user and/or application knows the address of the new server. Example: We want to retire SQL Server A. That means moving the database over (either detaching and moving, log shipping, mirroring… take your pick) AND every application or user needs updated to point to the new location. This might be trivial, or it might not be. Without a domain alias, every application that uses a database on this server would need to be updated as part of your upgrade. That’s more coordination and time spent, but with a little planning you can make this update (almost) seamless to developers and users.

Logging in to WORKSTATION-A, I can ping the existing SQL server using my domain alias for AdventureWorks, and it resolves (correctly) to SQLSERVER-A’s IP address:

So, let’s assume I’ve already completed my move of the AdventureWorks database over to SQLSERVER-B. All I need to do is go back to the DNS manager and update the CNAME record to point to point to SQLSERVER-B by right-clicking the entry and updating the address. If I do that, I should be able to ping the same FQDN and get a different IP:

What, wait… what happened? In this example, I didn’t get the updated address for the server on my workstation. Why?

Time Keeps On Slipping… slipping… slipping

The reason I didn’t get the new address is because DNS records are cached on lookup. This is a good thing; excessive lookups can lead to network latency, and some service providers can even charge you per DNS lookup too. The amount of time a record is cached on a client system is called Time to Live (TTL). In our example above we set that to 5 minutes. If I had had waited roughly that amount of time before doing my ping, it would have resolved correctly.

So the trick then is when you create these aliases the first time, you might want a lower TTL so the clients will get refreshed quickly. Once everything is working you might want to consider upping the TTL to hours, if not days. You can always set it lower again a few days ahead of an upgrade so clients are constantly getting the new address.

If your network’s TTL is controlled by a policy that doesn’t allow you (or want you) to set lower a TTL, you can force a refresh of your DNS cache locally by typing (in an administrator-level command prompt) ipconfig /flushdns . Then you should resolve to the right address. Personally, on production systems (like web servers) I do this anyway. But with a low enough TTL and properly communicated time frames for an upgrade, you might not have to.

And you thought you’d get out of this without writing PowerShell?

You can use CNAMES to be a granular or as broad as you want. Maybe you only ever want an alias for an entire server because all the databases contained in it have to move together or maybe you want the flexibility of having an alias per database. Personally I like the latter solution so I can move things around to different servers if I need to. Thanks to the magic of PowerShell, creating domain aliases is very easy. The script below allows you to create aliases for a database by letting you provide the hostname of the SQL Server where the database lives, your DNS zone where the aliases are going to be created, and a list of other environments you want to create. You need access to the DNS server (most likely domain admin rights, although your environment may vary) and you have to run these cmdlets in an administrator-level PowerShell session.

Note: It always goes without saying, but you shouldn’t just copy and paste this script and run it in production. I highly, highly recommend you set up a lab to play with first, or at the very least share it with your network admins since they’re the ones that probably will administer your DNS servers. Mucking up DNS can cause some MAJOR problems so please use caution. You were warned.

[cmdletbinding()]
param(
    [Parameter(Mandatory=$true)] [string] $SQLServerFQDN,
    [Parameter(Mandatory=$true)] [string] $AliasName,
    [Parameter(Mandatory=$true)] [string] $ZoneName,
    [Parameter(Mandatory=$false)] [int] $TimeToLiveSeconds = 3600,
    [Parameter(Mandatory=$false)] [string[]] $OtherEnvironments
)

begin
{
    $ttl = New-TimeSpan -Seconds $TimeToLiveSeconds
    $namingConvention = '.SQL'
}

process
{
    Add-DnsServerResourceRecordCName -Name ($AliasName + ".PROD" + $namingConvention) -HostNameAlias $SQLServerFQDN -ZoneName $ZoneName -TimeToLive $ttl
    
    if ($OtherEnvironments)
    {
        foreach ($o in $OtherEnvironments)
        {
            Add-DnsServerResourceRecordCName -Name ($AliasName + "." + $o + $namingConvention) -HostNameAlias $SQLServerFQDN -ZoneName $ZoneName -TimeToLive $ttl
        }
    }
}

So if I wanted to quickly spin up a bunch of aliases for different environments for a given database, I could run the script like this:

.\New-SQLServerDomainAlias.ps1 -SQLServerFQDN SQLSERVERA.BOATMURDERED.NET -AliasName DatabaseName -ZoneName BOATMURDERED.NET -OtherEnvironments DEV,INT

It also supports custom TTLs, in seconds

.\New-SQLServerDomainAlias.ps1 -SQLServerFQDN SQLSERVERA.BOATMURDERED.NET -AliasName DatabaseName -ZoneName BOATMURDERED.NET -TimeToLiveSeconds 60

Feel free to grab the code and modify it. I have a built-in naming convention there but you could easily change it (or make it a parameter!) if you’d like.

Hopefully this post has been helpful for you. I know using domain aliases has helped me a lot when planning upgrades. Of course, if you’re not using domain aliases today there’s some planning and cooridnation with your users, developers, and networking teams that has to go into it, but if you can pull it off you can set yourself up for easy future migrations. What do you think? Are you using aliases today? Why or why not? Hit up the comments, and thanks for reading!