Tuesday, 8 January 2013

Create SQL Server Aliases using Powershell

Create SQL Aliases example Powershell
For DR and Moving/splitting up SQL Server load use aliases, costs you nothing and later on you can split the load.  I use 3-4 even on small SP farms.

Tip: SQL 2012 has always on availability clustering, the SQL Server listener (need for Availability Groups (AG)) does the same functions as a SQL Alias.  So my take is if you use a SQL 2012 AG then the listener on an always on availability cluster does the same function as the SQL Alias.  Obviously rather use the listeners DNS name as opposed to the IP adr of the listener but if you are using AG you don't need a SQL Alias.

Thoughts: SQL 2012 brings a new option to the table regarding SQL Aliases for SP2010 & SP2013.  If you are using Always-on Availability Groups (AP) in SQL 2013, you get a SQL listener that does the same function as as the SQL Alias.  AG gives you automatic db fail over for your Sp farm.  Issue is if you use AG with a SQL alias you have a single point of failure so your DB won't automatically fail over.

So the big reason to use SQL Aliases for me in the past was to allow me to split my database servers when 1 became the bottleneck.  The goodness with AG outweighs this option to improve performance especially as if I'm using AG I probably have sufficient resources as this is planned upfront.

Creating Registry keys safely in PowerShell:
    # Check if the key already exists - Example from AutoSPInstaller on creating aliases.   
    $client = Get-Item 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client' -ErrorAction SilentlyContinue
    # Create the key in case it doesn't yet exist
    If (!$client) {$client = New-Item 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client' -Force}

Tip:  Check SQL connections and SQL Aliases using a udl file.  Create a text file on your desktop, rename the .txt extension to .udl.  Open the UDL file and verify the connection string works.  I check the Alias that uses the AOAG listener, if this fails I check the connection using the listener, if this fails I check I can hook to any SQL instance.  This pretty much tells me where I have gone wrong.

Tip:  Review your SQL Alaises and cleintside neworking using the SQL Server Client Network Utility tool.  In the run window type: cliconfg


Post a Comment