Using SQL Aliases with SharePoint

[Update: I wrote a newer post that may complement this article]

I recently found out about SQL Aliases and how to use them in combination with SharePoint installations. It enables you to define local alias name to connect to with a SQL Client, so you can change the actual connection later on. This may come in handy when switching over to your mirror database server, when moving servers, when virtualizing you database server etc.

This is how it’s done:

1) Open the cliconfg.exe tool and go to the Alias tab

2) Click Add to add a new alias. Choose a Network Library and then configure your alias. In my example I use MossDBServer as my alias, and ActualServerName:1433 as the underlying connection.

Add SQL Alias

3) Use the alias name in your client app, e.g. the SharePoint Configuration Wizard:

Configuration Wizard

4) When time has come to change the underlying connection, just launch cliconfg.exe again and make your change:

Change Server Name

I can think of at least a couple scenario’s where this might come in handy. Fancy a simpler way to move all of your MOSS databases anyone? Change the port SQL Server is listening to?

Comments

  1. says

    Hi Thomas,

    Just to be clear: this needs to be configured on the MOSS servers locally, right, and not on the SQL box? And if so, then it would need to be configured on all MOSS servers part of the farm, too, right?

    Great post by the way.

    Greetings,

    Dirk

  2. says

    Indeed, it needs to be configured on every MOSS server. You could also do this [i]after[/i] MOSS is already installed: configure the alias exactly as your existing connection, and change it when needed.

      • says

        You could use a hosts file or even a DNS alias in some cases. A SQL alias is more granular as it only applies to SQL connections. You can also use it to hide the fact you are using a named instance – very handy in sime cases – or a dynamic port.

  3. says

    Thanks Thomas–great post!

    Seeing as how we configure remote connections to SQL Server to allow TCP/IP *and* Named Pipes, is there any particular reason for configuring the alias in your example using TCP/IP? Do you know if this forces the WFE to use a TCP/IP connection over a Named Pipe?

  4. Eric says

    This works great for our Sharepoint DR exercises. Our plans are to restore copies of our virtual Production WFE’s and point them to the DR instance of the database. (restored Sharepoint db’s to a different SQL Database name.)
    Getting the Sharepoint databases to point to a different SQL Server was extremely problematic/cumbersome using Sharepoints’ STSADM/PSCONFIG tools. I was banging my head against the bricks using the renameserver, deletecontentdb switches and so on. (recommended by Microsoft) Was very messy and not working as I needed it too. Anyway the SQL Alias method is working like a charm. Thanks for the information.

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">