Using SQL Aliases with SharePoint by Thomas Vochten

Thomas Vochten, a former colleague of mine, wrote about an interesting way to create and configure your SharePoint farm to take into account any possible changes in your backend SQL infrastructure by using SQL aliases on your MOSS servers and therefore adding the possibility to change your backend SQL server transparently to your SharePoint configuration.

On his blog, he wrote:

“I recently found out about SQL Aliases and how to use them in combination with SharePoint installations. It enables you to define local alias name to connect to with a SQL Client, so you can change the actual connection later on. This may come in handy when switching over to your mirror database server, when moving servers, when virtualizing you database server etc. “

Furthermore he describes the actual steps to configure this. I am not adding those steps to this blog post, to have you go look at the original post instead [8-|]

I also checked with my favorite Premier support Engineer form Microsoft to check if this is supported and he confirms it is. He aslo confirmed seeing this more and more for use with Excel Services and failover scenarios as suggested by Thomas.

**** Update ****
Also worth mentionning is Thomas’s comment on my question:

I wrote: “Just to be clear: this needs to be configured on the MOSS servers locally, right, and not on the SQL box? And if so, then it would need to be configured on all MOSS servers part of the farm, too, right? “

Thomas replied: “Indeed, it needs to be configured on every MOSS server. You could also do this after MOSS is already installed: configure the alias exactly as your existing connection, and change it when needed.”

Thanks Thomas for sharing.

One thought on “Using SQL Aliases with SharePoint by Thomas Vochten

  1. Using SQL Server aliases makes it easy to for us to keep all developer VMs configured identically to our production servers.

    We use a script similar to the following to configure the aliases:

    set DBSERVERALIAS=AliasName
    set DBSERVER=RealServerName

    rem %windir%\system32\cliconfg.exe
    reg add HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo /v %DBSERVERALIAS% /t REG_SZ /d “DBMSSOCN,%DBSERVER%” /f
    reg query HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo

    rem 64-bit support for database alias
    rem %windir%\SysWOW64\cliconfig.exe
    if /i NOT “%PROCESSOR_ARCHITECTURE%” == “X86” (
    reg add HKLM\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo /v %DBSERVERALIAS% /t REG_SZ /d “DBMSSOCN,%DBSERVER%” /f
    reg query HKLM\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo
    )

Leave a Reply

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