Create SQL Server Alias using PowerShell
As per SharePoint best practices for SQL Server, SQL Alias needs to be created on all SharePoint Servers in the farm. Creating SQL Alias for SharePoint through UI, is already explained in my another post: Create SQL Server Alias for SharePoint , and here is the PowerShell version of creating SQL Server Alias.
#Name of your SQL Server Alias $AliasName = "SP13_PROD_SQL" # Actual SQL Server Name $SQLServerName = "G1VSP13-SQLC001" #TCP Port $Port = "1433" #These are the two Registry locations for the SQL Alias $x86 = "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo" $x64 = "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo" #if the ConnectTo key doesn't exists, create it. if ((test-path -path $x86) -ne $True) { New-Item $x86 } if ((test-path -path $x64) -ne $True) { New-Item $x64 } #Define SQL Alias $TCPAliasName = "DBMSSOCN,$SQLServerName,$Port" #Create TCP/IP Aliases New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAliasName New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAliasNamefinally, open these two executables to verify:
- C:\Windows\System32\cliconfg.exe
- C:\Windows\SysWOW64\cliconfg.exe
Can you tell me how I can add enabling/disabling the TCP/IP and Pipes protocols in this script?
ReplyDeleteThis way does not properly create the alias using TCP/IP. I tried it and experienced SQL errors when trying to create the farm and configuration database.
ReplyDeleteWhen I manually changed the alias to use TCP/IP, it then succeeded.
I realize this was 3 years ago, but just curious if you tried rebooting or restarting the sql service since this is inherently a registry modification that might not be picked up right away.
Delete