[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.
3) Use the alias name in your client app, e.g. the SharePoint Configuration Wizard:
4) When time has come to change the underlying connection, just launch cliconfg.exe again and make your change:
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?
{ 4 comments… read them below or add one }
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
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.
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?
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.
{ 2 trackbacks }