working with sql client aliases

SQL Client Aliases allow you to connect to a SQL Server instance using another name. This is especially useful during migrations. Want your servers to connect to the new SQL Server without modifying connection strings within your application? Or what if you could use easy-to-remember names for your docker containers? SQL Client Aliases can help.

image

For the longest time, I managed these aliases using cliconfg for 64-bit applications (C:\Windows\System32\cliconfg.exe) or cliconfg for 32-bit applications (C:\Windows\SysWOW64\cliconfg.exe).

When I’d remember and if it was available, I’d also manage SQL Client Aliases using SQL Server Configuration Manager, which surfaces aliases for both 32-bit applications and 64-bit applications in a single pane. Here’s a screenshot of both SQL Server Configuration Manager and cliconfg. Note they both show the same aliases, confirming that you can manage aliases using whichever you prefer.

SQL Native Client provides cliconfg.exe but I think it’s also built into Windows. I’ve yet to find an OS that doesn’t have cliconfg on it – and I tried all the way back to Windows 2003! So if SQL Client Aliases seem useful to you, you’re in luck.

While you can manage SQL Client Aliases using the GUI, I prefer using dbatools which helps me avoid logging into multiple servers at once, and creates both the 32-bit and 64-bit aliases at once.

using dbatools

I find SQL Client Aliases most useful for facilitating easy migrations and using them is even recommended as a best practice in the SharePoint world.

First, I check all of my servers to see which aliases are currently setup.

Get-DbaClientAlias -ComputerName web01, web02, web03

Then, I’ll either remove old ones using something like this, which allows me to select the aliases I want to remove.

Get-DbaClientAlias -ComputerName web01, web02, web03 | Out-GridView -Passthru |  Remove-DbaClientAlias

Then I create my aliases as required. Note that the aliases and server names can be formatted in a number of ways. Both named instances and specifying a port numbers is supported.

New-DbaClientAlias -ComputerName web01, web02, web03 -ServerName sql02 -Alias sql01
New-DbaClientAlias -ComputerName web01, web02, web03 -ServerName sql02\sqlexpress19 -Alias sql01\sqlexpress14
New-DbaClientAlias -ComputerName web01, web02, web03 -ServerName 'sql02,2383' -Alias 'sql01,2383'

By default, connections are created as TCP/IP. Want to use named pipes? That’s also possible.

New-DbaClientAlias -ServerName sqlcluster\sharepoint -Alias sp -Protocol NamedPipes

If you’re familiar with named pipe aliases, you may remember that it creates a funky string. Don’t worry, we take care of that for you. Just pass the ServerName and Alias as you would with TCP/IP and we’ll build the named pipe string for you.

Now that I’ve created all of my required aliases, let’s take a look at them using Get-DbaClientAlias and Out-GridView. Note that when I don’t specify -ComputerName, the command executes against my local machine, in this case, WORKSTATIONX.

try it out

Test this out yourself – create a new alias, then use dbatools, Azure Data Studio or SSMS to connect to the new server using the old name. In the example below, I’ve migrated sql2014 to sql2016 then will use the sql2014 alias to connect.

image

Automating your migrations just got even easier! For more information, check out our built-in help using Get-Help Get-DbaClientAlias -Detailed.

- Chrissy 🚀

5 thoughts on “working with sql client aliases

  1. Greg Tate Reply

    Awesome post on this functionality! You and all those who have worked on dbatools are a treasure to the community!

    Thanks so much for all of your hard work and time!

  2. Gareth Reply

    Woo, this is great. I use aliases sometimes, didn’t realise those commands were in dbatools.

    • Chrissy LeMaire Post authorReply

      It’s true! I use a mix of both. In my last migration, I preferred SQL Client Aliases because it was instant – I didn’t have to wait for DNS replication.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.