Working with SQL Aliases

By now, many SharePoint Administrators have heard this “best practice:” Use SQL Aliases. Recently, I was surprised to hear that this may not be unanimous – Todd Klindt (@toddklindt) in his weekly netcast argues against it. Todd also pointed out Todd Carter’s (@toddca) blog post where he suggests always using SQL Aliases.

In my work, I always employ SQL Aliases, even on single server farms where SQL and SharePoint are on the same server. Seems like a waste of time, doesn’t it? Well, it takes about half a minute to setup but can save hours if you get into trouble.

Let’s look at a common scenario. Your developers need SharePoint setup quickly so they can create some gee-whiz solutions. So, you quickly create a SharePoint farm accepting all defaults (or use a script mentioned in a previous post) and deliver it to your developer buddies.

Little did you know, only 3 months later, you’re running out of disk space or SQL gets corrupted or you finally get your shiny new blade servers and want to build out a new SQL cluster for your development environment. Anything could happen…

Enter: SQL Aliases. Without an alias, you’d be setting up a brand new SQL server and SharePoint from scratch. You’d have to migrate all the developer work, their solutions, their settings and have to deal with whining because someone is “down” until you’re done building the new system.

SQL Aliases are like host names, somewhat. It’s the name everyone knows and remembers (including SharePoint). In DNS (or host files), the name stays the same, though the IP could change. Aliases are similar, you can change your SQL Servers name, but it’s alias will remain. All you’d have to do is update a “pointer.” One BIG difference worth mentioning though: DNS is server-side, meaning you go to your DNS server and update a record; SQL Aliases are client-side, like hosts files. Only the computer on which you created it knows about it and will use it. So yes, you have to create this on any client/computer that you want accessed this way. This usually just means, all your SharePoint servers in the farm.

So now, hopefully, you see the benefits of using SQL Aliases. I’ll show you how to set one up in less than 30 seconds.

These steps are taken from my Wiki http://wiki.wahidsaleemi.com/Pages/SQL_Server_Setting_Alias

1. Click Start –> Run –> cliconfg.exe (notice there is no i in confg)
2. Select the Alias tab and click Add…
3. Specify the Server alias (for example: SharePoint_DB or SQLPoint1 or whatever)
4. Select the TCP/IP Network library.
5. For Server name, enter the SQL Server Name (if using default instance) or use UNC notation if using non-default instance (e.g., \\servername\instance)
6. Uncheck Dynamically determine port and use the default port of 1433.
7. Press OK. Repeat this on any client that is part of the SharePoint farm.

If you want to use Aliases on your SQL Server, or if you have an all-in-one box (SQL + SharePoint), follow this procedure instead:

1. By default, Windows comes with a SQL client called “cliconfig.exe” but as I’ll show you later, this doesn’t always work.

2. Next, run SQL Server Configuration Manager and expand the “SQL Native Client 10.0 Configuration” nodes (both of them).

 

Take note that one has (32bit) and the other doesn’t. Depending on the client, one will be used over the other so it’s best to configure both nodes identically.

3. Right-click on “Aliases” and select “New Alias…” A dialog box will open.

a. Use any name for your Alias, for example “SharePoint_DB”
b. You should specify the port number, SQL’s default is 1433. Leave the protocol at TCP/IP.
c. For Server, enter just the server name if you’re using the default SQL instance (e.g., Server01_Web). If you’re using a named instance, you need to enter the server name and instance using UNC notation (e.g., \\Server01_Web\NewInstance).

d. Click OK and repeat the steps for the other “Aliases” node.

Now that your Alias is created, you can try to connect to it using SQL Server Mangaement Studio or another method. So, instead of using the “Server Name” to connect to SQL, use your new Alias, it should work just fine.

When installing SharePoint, and it asks for your SQL Server name, just specify the SQL Alias. This way, if anything ever goes wrong with SQL, you can just change the Alias to a new server and not have to re-configure SharePoint.