Setting a SQL alias on every SharePoint server is a common task when you are installing SharePoint. You use the SQL Server Client Network Utility (cliconfg.exe) for this. This tool is available on every SharePoint server because it’s part of the SQL Server Native Client prerequisite.
Setting a SQL alias is a best practice because it makes your life a whole lot easier when you want to change the actual database server in some point of time. If you use an alias, the only thing you need to do at that moment, is change the target of your alias and you’re good to go. If you install SharePoint and you reference the database server directly, your only way of pointing SharePoint to the new database server painlessly, is to create an alias at that time, set the name of the alias to the name of the old database server and have it point to the new server. Definately not cool because when someone looks at the Servers in Central Administration, it will list the old name and it’s not clear that this is not a server anymore but an alias.
When you want to set an alias, you run cliconfg.exe on each SharePoint server. In this tool, you have an “Alias” tab, where you can set it. You provide a name, the type of connection (Named Pipes, TCP/IP) and a server name. You can also select a custom port if you use TCP/IP or keep the default.
If you don’t want to do this manually, there’s also a way of doing this with PowerShell. The only thing this tool does, is create a string value in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
So, doing this using PowerShell is easy.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
<# .SYNOPSIS Adds a SQL Server alias to the current server. .DESCRIPTION Adds a SQL Server alias to the current server. .NOTES File Name: Add-SQLServerAlias.ps1 Author : Bart Kuppens Version : 2.0 .PARAMETER Name Specifies the name of the alias. .PARAMETER SQLServerName Specifies the name of the SQL Server. .PARAMETER Port Specifies the port. .PARAMETER Machine Specifies the computer where the registry is located. .EXAMPLE PS > Add-SQLServerAlias -Name "SHPDB" -SQLServerName "SRV-CTG-SQL01" -Port 1433 -Machine SRV-CTG-SHP01 #> [CmdletBinding()] param( [parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true,HelpMessage="Specifies the name of the alias.")] [string]$Name, [parameter(Position=1,Mandatory=$true,ValueFromPipeline=$false,HelpMessage="Specifies the name of the SQL Server.")] [string]$SQLServerName, [parameter(Position=2,Mandatory=$true,ValueFromPipeline=$false,HelpMessage="Specifies the port.")] [string]$Port, [parameter(Position=3,Mandatory=$false,ValueFromPipeline=$false,HelpMessage="Specifies the computer where the registry is located.")] [string]$Machine ) $hive = "localmachine" $parentKey = "SOFTWARE\\Microsoft\\MSSQLServer\\Client\\" $key = "ConnectTo" # If the $Machine parameter was not provided, use the local machine. if ($Machine -eq $null) { $Machine = $ENV:COMPUTERNAME } try { # Connect to the registry (also works for remote machines) $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]$hive, $machine) } catch { Write-Host "Unable to connect to the registry of machine '$machine'. Please verify that the remote registry service is running and that you have administrative access to that machine." break } # Open the key in the registry $subkey = $reg.OpenSubKey($parentKey + $key, $true) if ($subkey -eq $null) { # The key doesn't exist, open the parent key and create the subkey. $parentTemp = $reg.OpenSubKey($parentKey,$true) if ($parentTemp -eq $null) { Write-Host "Parent key not found in the registry of '$machine'. Please verify that the SQL Client Tools are installed." break } else { try { $parentTemp.CreateSubKey($key) >> $null } catch { Write-Host "Unable to create the key '$key' in '$parentKey' on machine '$machine'. Do you have administrative permissions?" break } $subkey = $reg.OpenSubKey($parentKey + $key, $true) } } $res = $subkey.GetValue($Name) if (!$res) { $subkey.SetValue($Name,"DBMSSOCN,$SQLServerName,$Port") Write-Output "Alias $Name created successfully!" } else { Write-Output "Alias $Name already exists" } $reg.Close() |
You can find this script in my PowerShell Repository on GitHub.
