A Follow-Up Note on SQL Aliases


Last December I wrote an article titled Working With SQL Aliases where I explained a bit about why I use them.  It’s still a controversial topic with some disagreeing that it provides any benefit at all, at least during SharePoint setup time. The primary argument I’ve heard against it, is that its confusing and someone may not know they’re using an Alias. But, I say if your Farm is undocumented, don’t blame the technology.

I still use SQL Aliases because it makes my life easier. I can script my installs with it more easily and don’t have to remember every scenario where I MUST use an Alias. For example, Spence Harbar’s post on UPS troubleshooting states that it’s required for UPS prior to the June 2011 CU; he still recommends using one anyway, as I do.

In my last post, I showed how you could use “cliconfg.exe” to set up SQL Aliases. Recently, through twitter, a blog post by @AirborneGeek caught my attention. It’s titled A Note About cliconfg.exe on x64 Machines where Kerry Tyler writes about the different versions of cliconfg.exe; I agree its very confusing.

So, let’s ditch that utility and use PowerShell. This is one simple way to setup the alias. Substitute the following for your own values:

  • Alias_SPDB: This will be your alias. I want to start to prepend mine with “Alias” so we know its actually an alias.
  • SQL1\SHAREPOINT: The SQL Server/Instance/Cluster name
  • 1433: The SQL Server port
New-ItemProperty HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo –name Alias_SPDB -propertytype String -value "DBMSSOCN,SQL1\SHAREPOINT,1433"
New-ItemProperty HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo –name Alias_SPDB -propertytype String -value "DBMSSOCN,SQL1\SHAREPOINT,1433"

This is quick and dirty. A better approach may be to use Ingo Karstein’s approach. I believe using WMI is the preferred approach, plus his way allows you to create a PowerShell module where you can use the common verbs, Get and Set.

I won’t say using an Alias is best practice, you should decide what’s best for your organization. I hope I’ve provided some arguments as well as some ways to easily accomplish the task.

0 comments… add one

Leave a Reply