Blog Archive » Setting Up a SQL Server Alias with SharePoint Server 2013

Setting Up a SQL Server Alias with SharePoint Server 2013

I made a similar post about this for SharePoint 2010, but I wanted to update it (and make it more clear) for 2013. 

Using a SQL Server alias, you’ll be able to change the database server in your SharePoint farm behind the scenes, without anyone in your SharePoint universe even knowing you’ve made a change.  In addition, setting an alias should really be a best practice for most new farm builds.  This technique is useful when moving or virtualising your database server, setting up a test environment, or especially when migrating from SQL Server 2005 / 2008 / 2008 R2 to SQL Server 2012 / 2014.

Repeat the following steps for all your SharePoint Frontend and Application Servers.

Important: Only do this on the SharePoint Servers. Don’t do this on the SQL Server!

The steps we’re going to run include the following:

1. Setup the alias using 32-bit config tool.

2. Setup the (same) alias using 64-bit config tool.

Let’s begin…

1. Run the 32-bit SQL tool.  Run the following application, “C:\windows\system32\cliconfg.exe”

Select the tab “Alias” and click the button “Add”.

image

Next (as seen in the graphic below), (1) Select “TCP/IP” and define your alias (2). Next, fill in the original server name (3) of your database server and unselect the “Dynamically determine port” checkbox (4) – finally, define the port (5). After that click “OK”.

Note 1: If you know the port (typically 1433), you will need to uncheck the “Dynamically determine port” checkbox, and set the fixed value.

Note 2: If your new (or old) server is using an instance, be sure to include that, for example, something like itgrooveSQLServer\INSTANCENAME  (ex. itgrooveSQLServer\SharePoint2013)

Note 3: If you have an existing SQL server without an Alias and are migrating that to a new machine and setting up an alias, the “Server Alias” value should be the name of the existing SQL server (2), and the Server name (3) should be the name of your new SQL server.

image

Now you have defined your SQL alias name. Click “OK”.

SNAGHTML1bfa81a

Note: It’s recommended that you should setup an alias for the server name itself, as well as it’s fully qualified name – for example, setup an alias for “itgrooveSQLServer”, as well as “itgrooveSQLServer.domain.com”.

2. Run the 64-bit SQL tool. Run the following application, “C:\windows\syswow64\cliconfg.exe”

Using the 64bit tool, repeat all the same steps you did above.

After you’ve completed steps (1) and (2) on each SharePoint Server, you can either create your new farm or restart your existing farm, and it should be working again with your new alias.

Original Source: http://splog.cairo.ag/2011/02/22/using-sql-server-alias-names-with-sharepoint-server-2010/ – Many thanks!

16 responses to “Setting Up a SQL Server Alias with SharePoint Server 2013”

  1. Cool. I’m about to install SP 2013 on Windows 2012 and was wondering whether to run the 32-bit tool or the 64. So I need *both* for SharePoint to see the SQL server as “SPSQL” or whatever?

    1. You need to setup alias’ for both 32 and 64 bit versions of the tool. Not doing so runs the risk that you’ll end up in a scenario where communications via 64 bit do not complete correctly (or 32-bit as the case may be).

  2. I am doing a single server farm installation. Whether the above mentioned steps are recommended in a scenario where SQL server is on the same machine where WFE and App servers exists.

    1. I don’t see any reason why you couldn’t (or shouldn’t). In fact, if you eventually wanted to move the SQL server off to another server in the farm, it would be quite advantageous. I think this is a great idea.

      1. I tried to perform a single server farm installation (SP 2013), it does not take the SQL alias, only the server[instance] when creating the farm.

  3. Thanks for the detailed instructions. Do you know it is possible to put in the sql server alias AFTER the SharePoint 2013 farm is setup without sql server alias? If possible, how to achieve this?

    I followed your instructions setting up the sql server alias, but it does not look like it works.

    Thanks.

    1. Yes, you can setup the alias anytime you’d like. The alias and the target can be exactly the same to start, and then later on if you change your target SQL server, your alias can remain the same, and point at the new target.

      1. hmm, it is not working. any trouble shooting tips? I rebooted the box after setting up the sql server client alias.

        1. Without actually looking at it, I don’t know how to help. If you followed the steps above, it should work fine.

          1. Tony, I have the same situation with an existing Farm but alias is not working with sharepoint. Using SQL Managent Studio I can connect to the alias but Sharepoint services does not start. If I remove the alias sharpoint works. Did you find a solution?

  5. Hi Colin,
    You mention to create both the alias for the machine name as well as for the FQDN. When I try to create the second one, it tells me: A network protocol configuration already exists with the same name… Am I supposed to create the alias using a different name?

Comments are closed.