Quick PS script to get SQL Server Configuration Aliases

ALIASES

Aliases!

They can be quite helpful, but they can also be a major pain when setting up new servers or migrating to a new server.

Trust me, we just got burned by this recently. A missed alias resulted in a critical app being down for a while until we figured it out.

If you use aliases and don’t have them all setup on your new machine you can pretty much expect things to break.

It is also a pain to sit and transcribe the various alias settings to be able to rebuild them all on the next machine.

There is an export list option for the aliases on your server, that’s nice and all, but there isn’t a corresponding import option.

Plus you have to deal with 32 and 64 bit lists.

The very simple script below helps since you can use to get the details of both the 32 and 64bit SQL Server aliases you have setup on your system.

Ready for it? It’s a long convoluted one:

That’s it?

Yeah. That’s it.

All the alias information is stored nice and neat in the registry.

This is a very simple PS script to dump the properties of the registry location of the SQL Server configuration manager alias scripts.

Heck, if you know the path to any other property in the registry you can dump that as well using the same Get-ItemProperty.

 

Now if only there was a way script to read that info in on a new server and set the registry value quickly for all the aliases you may need.

Maybe that’ll be next time…

 

[divider][icon_box icon=”users” title=”Who is Chris Bell?”]

Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com. Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services, training and the FREE sp_WOxCompliant compliance check script for your SQL Server environment.

Click here to contact Chris or to follow him on twitter. [/icon_box]

 

 

Comments 8

  1. Pingback: Quick PS script to get SQL Server Configuration Aliases - Syndication – WaterOx Consulting - SQL Server - SQL Server - Toad World

  2. I was going to read this, but the shifting background was no annoying I decided it wasn’t worth it.

    1. Post
      Author

      The shifting background should never have been set on that. Don’t know why it was (I find it annoying too!).
      Hopefully you will find the article more “worth it” now that the anomalous background has been corrected.

  3. I need the actual script so I can replay it on the new server.  Creating them through the UI is annoying as all get out.  Please point me to a script that will actually generate the “Create Alias….blah blah blah” syntax.

     

    Regards

    Bob

  4. I’m getting the following when I try to run the command you suggested:

    Get-ItemProperty : Cannot find path ‘HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo’ because it does not exist.
    At line:1 char:1
    + Get-ItemProperty ‘HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\Connect …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (HKLM:\SOFTWARE\…lient\ConnectTo:String) [Get-ItemProperty], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemPropertyCommand

     

    And I actually let the intellisense guide me.  When it came to the “ConnectTo” path, it only had SNI11.0.  There wasn’t a ConnectTo

    Suggestions?  And thank you for the code tip.

    1. John, that means there are no aliases set on the machine. The first time you create an alias, it creates the ConnectTo key.

    2. You can try “HKLM:\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo”. It worked for me.

  5. The command in the blog post refers to aliases as follows:
    On a 32-bit Windows Operating System, then

    Gets the aliases set up for 32-bit drivers, the only ones available on that computer.

    If you’re using a 64-bit Windows Operating System, the story is more complicated:
    The following command (same as above), gets the aliases that 64-bit drivers use:

    For 32-bit drivers, use this command:

    The Wow6432Node is the registry key area for anything that is 32-bit specific on a 64-bit version of Windows.

    Please note, that this is very important.  Let’s say you are setting up aliases for a SharePoint web front end or application server, a 64-bit application.  You will then need to make sure you have the 64-bit alias setup.  If you are configuring an alias for, say Excel or Access, then you need to match the alias for bit version of MS Office you have installed.  If you are setting up aliases for use in Visual Studio (e.g. SQL Server Management Studio, the SQL BI developer tools), then those are 32-bit, and need a 32-bit alias, but deployed to the corresponding server (e.g. SSRS, SSIS, SSAS server), then you will need 64-bit aliases.  That said, some of the SSIS connection manager drivers may only be 32-bit (possibly Excel/Access or 3rd party), so you’ll need to check.

    TLDR version:  There are two separate locations for aliases for the bit version on 64-bit Windows, one for 32-bit and one for 64-bit data drivers.  Just make sure you have aliases reconfigured exactly the same in each location and remember to check both locations for configurations.

     

     

Leave a Reply

Your email address will not be published. Required fields are marked *

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