One of Microsoft’s best practices for SharePoint is to create a SQL alias for your SharePoint farm. This can ease the transition from one SQL server to another, required in some instances to scale your farm out, as well as to aid in recovery from some disasters. Luckily this is one of the easiest best practices to implement!
To create a SQL alias, log in to your SharePoint server (note: needs to be completed on ALL of your SharePoint servers).
Open Windows Explorer, and browse to “C:\Windows\System32 folder”. Open the program “CliConfg.exe”.
Select the second tab “Alias” of the SQL Server Client Network Utility, and click “Add”.
In the “Add Network Library Configuration” dialog type in the desired alias for your SQL server. A good standard for this is A-[SharePoint Farm Name]. For example, if your farm name was “Intranet” your alias would be “A-Intranet”. Type the name of the actual SQL server you’re connecting to in the “Server name” field. The alias you create needs to be the same on ALL SharePoint servers in the farm.
Select “OK”, and your alias is created! You can now create a SharePoint farm using the Alias as the database server name. If you ever have to move your databases to a new server, you can change the alias to point to the new server name, and keep SharePoint pointing at the alias!
Note: another best-practice on 64-bit systems, is to configure the alias in “C:\Windows\System32\CliConfg.exe” and “C:\Windows\SysWOW64\CliConfg.exe”. You can then use this alias with both 64-bit and 32-bit processes, and is REQUIRED for Project Server 2007 to work properly with SQL aliases.
Over the next few weeks I’ll be creating a dozen or so virtual machines (VM). To save time I’m trying to script as much of the installation and configuration as possible, and today the portion I wanted to script was the creation of a SQL Alias. I’m sure everyone has read my wonderful blog post onCreating a SQL Alias for SharePoint, but that’s a manual process ?
I found a few threads that discussed doing this, but none detailed the steps 100 percent. I did a little digging with the SQL Server Client Network Utility and the registry, and came up with the script below. Using this I’m able to create the same alias for both x86 and x64 SQL connections on every machine!
#This is the name of your SQL Alias
#This is the name of your SQL server (the actual name!)
#These are the two Registry locations for the SQL Alias locations
#We're going to see if the ConnectTo key already exists, and create it if it doesn't.
((test-path -path $x86) -ne $True)
"$x86 doesn't exist"
((test-path -path $x64) -ne $True)
"$x64 doesn't exist"
#Adding the extra "fluff" to tell the machine what type of alias it is
+ $ServerName +
#Creating our TCP/IP Aliases
New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAlias
New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAlias
#Creating our Named Pipes Aliases
New-ItemProperty -Path $x86 –Name $AliasName -PropertyType String -Value $NamedPipesAlias
New-ItemProperty -Path $x64 –Name $AliasName -PropertyType String -Value $NamedPipesAlias