Working with SQL Aliases

SharePoint

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.
image

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).

 image

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.

22 comments… add one
  • Rick Taylor Dec 14, 2010 Link Reply

    I’m not against SQL Aliases, but your scenario still doesn’t convince me. If I need to have a new SQL server and decommission the previous one, why not simply give it the same name and IP? No change whatsoever.

    • Wahid Saleemi Dec 14, 2010 Link

      I think you have a point. The scenario isn’t that convincing. However, consider smaller deployments where a SQL server is shared and just the SharePoint instance is affected. Todd Klindt pointed it out as well, you could use a SQL Alias in a catastophe just when its needed. I find its still worth it though, it takes no time to do. Its helped me streamline my PowerShell scripts – I should have put more use cases in the article.

  • Wahid Saleemi Dec 17, 2010 Link Reply

    This should have been in the post but creating a SQL Alias can also help save on “round trips” between servers. For example, you can specify in the Alias that you just want to use Named Pipes or TCP/IP. By default, SQL tries various methods to connect – defining one will “help it along” and improve performance.

  • Todd Klindt Dec 19, 2010 Link Reply

    Hi Wahid,
    Thanks for the shout out. :)

    At the end of your post you say,

    “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.”

    If you don’t use a SQL alias when you initially install SharePoint there is no need to reconfigure SharePoint if there is a catastrophy. You still just add a SQL alias, no reconfiguring required.

    I stick by my point, there is no advantage to using a SQL alias when you install SharePoint. You have all the same options, with the same amount of work, if you just create the alias when you need it.

    tk

  • Wahid Saleemi Sep 5, 2011 Link Reply
  • OliK Sep 20, 2011 Link Reply

    OMG! This has literally saved me from going mad! Our SQL developer left and our alias suddenly stopped working. Having no experience in SQL Server finally finding this has got me back up and running. Phew!

  • Jshidell Oct 25, 2011 Link Reply

    Wahid, what happens if my farm is already built using a sql cluster instance name and I want to use SQL alias instead, would I have to rebuild my SharePoint farm or is there an easier way to point my SharePoint farm to the SQL alias name via powershell without rebuilding?

    • Wahid Saleemi Oct 25, 2011 Link

      Hi James,

      In that case, if everything is working I wouldn’t set up a SQL Alias. If you wanted to use an alias, you could create one and then use the SharePoint Products and Technologies Wizard to disconnect each server and re-connect it (now using the alias). However, that’s unnecessary downtime.

    • Jshidell Oct 25, 2011 Link

      Nevermind found out how to do this!

    • kamald Nov 8, 2012 Link

      @jshidell , can you let me know how you accomplish this?- what happens if my farm is already built using a sql cluster instance name and I want to use SQL alias instead, would I have to rebuild my SharePoint farm or is there an easier way to point my SharePoint farm to the SQL alias name.
       
      Thanks
      Kamal

    • wahidsaleemi Nov 8, 2012 Link

      @kamald   As I told James, you could run the SharePoint Products and Technologies Configuration Wizard (PSConfigUI.exe) and disconnect/reconnect using the alias. You should evaluate if you really need an alias if you’ve got everything working just fine.

    • kamald Nov 8, 2012 Link

      @wahidsaleemi
       Thanks for the quick response. do we really need to run the PSConfig? can we just use the Rename-SPServer and replace the SQL cluster name with an alias.? In our case , we used SQL cluster name directly while configuring the farm and applications .And now we want to create aliases on our sharepoint servers which will just point to the same SQL cluster . Please suggest.
       You will agree It’s a good practice to use aliases in case we need to move databases to a new cluster or the cluster gets corrupted for any reason and we have a new cluster , then we just need to change the alias on our sharepoint servers.
       
      Thanks
      kamal

    • wahidsaleemi Nov 8, 2012 Link

      Rename-SPServer would work too but I haven’t tested it on a production server for any side effects. If your reasons for using an alias are so that you don’t have to reconfigure SharePoint in the future (just change the alias) – you could do it if something ever happens to your SQL cluster. Or, you could just use the Rename-SPServer cmdlet at that time. In any case, let us know what you did and if you found any issues!

    • kamald Nov 18, 2012 Link

      @kamald  @wahidsaleemi
       Appreciate if someone can answer my query asked in previous post.
       
      Thanks
      Kamal

    • wahidsaleemi Nov 20, 2012 Link

      @kamald Looks like my reply didn’t come through. I haven’t tried using Rename-Server but it looks like it would work. If you want to be the first to test it, let us know if it works. Otherwise, you can just use a SQL alias. Your alias would be whatever the current cluster name is (that you have in SharePoint) and you point it to your new cluster. This would be confusing to someone who doesn’t know what happened though, they’d see the old cluster name in SharePoint and go there to troubleshoot. That’s why I’m in favor of creating aliases at install time and using “Alias_” as a prefix.

  • jcdmc Feb 22, 2012 Link Reply

    Wahid,

    Great blog.

    Is there any problem with creating a SQL alias that is the same as the name of an existing server on the network? Will the client try the alias before trying to connect directly to the named server? For example if I am migrating SharePoint content from a SQL Server instance on the same server as SharePoint to a new SQL Server instance on another server and I must move the configuration and Central Administration content DBs, I need to stop SharePoint and point it to the new SQL server. Can this be accomplished by creating an alias on the SharePoint server with the same name as that server itself, or will requests still be routed to the older server? In other words, can an alias and a server with the same name exist on the same network, and the alias is used only by SQL clients?

    • wahidsaleemi Feb 22, 2012 Link

      @jcdmc

      No problems with that, if you create an alias with the same name as the server, the alias will still be used. This is one way to enforce encryption or specify a port number. For your use case, you should just create an alias pointing to the new server after you’ve moved the databases. Your alias will be “New_SQLServer” and it’ll point to “Old_SQLServer”

      This can be confusing and its one reason why some people don’t like to use aliases. In your SharePoint configuration, the old server name will still be listed and SharePoint will still connect to that name but since it’s aliased, it will be connecting to the new server.

      You’re correct, an alias is used only be the SQL clients on that box, so you can name it anything you like, including existing server names on the network.

    • jcdmc Feb 22, 2012 Link

      @wahidsaleemi @jcdmc

      Thanks for that. Another question: even though you use the SQL Server Wizard to create the alias, the alias does not need SQL Server to be installed to be used, correct? So I could uninstall SQL Server on “Old_SQLServer” after creating the alias pointing to “New_SQLServer”?

    • wahidsaleemi Feb 22, 2012 Link

      @jcdmc

      Correct, you don’t need SQL Server. The SQL Alias is really just a registry entry. In my follow up to this blog post, I show you how to do this by using PowerShell: https://wahidsaleemi.com/2011/09/a-follow-up-note-on-sql-aliases/

  • kamald Nov 20, 2012 Link Reply

    Thanks @wahidsaleemi. I will try with some of the options suggested here and let’s see what happens.

    Thanks
    Kamal

Leave a Reply