Monday, November 12, 2012

How to Create SQL Server Alias for SharePoint

In My SQL Server best practices for SharePoint 2010, I wrote a point on SQL Server Alias. Let me elaborate that by going little deep on configuring SQL alias for SharePoint.

Why We need SQL Alias for SharePoint?
Because, SharePoint SQL alias best practice! Creating SQL Alias for SharePoint enables you to change the Database Server of your SharePoint Farm! This will be a great help during disaster recovery, virtualizations or migrations. Also its difficult to move SharePoint to a different SQL Server, If you don't use SQL Alias.
When disaster strikes, just change SQL Server alias that SharePoint references.

Where to Setup SQL Alias? 
SQL Alias to be set on All SharePoint Servers except DB Server (SQL Server!)
CliConfig.exe located at C:\windows\system32\cliconfg.exe in Windows 2003. For Windows 2008 and 2012, (or in 64 bit OS), its located at C:\windows\syswow64\cliconfg.exe

You don't need to install anything on your machine, as this SQL Alias Configuration utility comes with Windows. You can create it with SQL Server Config Manager also, When you have SQL Server  installed it on the SharePoint server.

How to Setup SQL Alias
Launch SQL Server Client Network Utility as stated above.
SQL Server Alias for SharePoint
Select the "Alias" tab and click on "Add" button.

Select "TCP/IP" and provide the name for alias. Then  fill in the host name of your database server and select the "Dynamically determine port" check box.
SharePoint SQL alias best practice
Now you have defined your SQL alias name. Click "OK" to complete.

How to Verify the SQL Alias Created?
We've created the SQL Alias. Lets make sure, Our SQL Alias works. Here is how: Login to any of your servers where you created SQL Alias (E.g. SharePoint Web Front end Server)
  1. Create an empty text file, say: Test.txt, then rename it from Test.txt to Test.udl
  2. Double-click the test.udl file. From the Data Link Properties screen,  Enter your SQL alias name, choose your authentication method, and click on "Test connection" button. You should get "Successful" message.
  3. Also, in the database drop down, you should see the list of databases from your SQL Server! 
How to test SQL Server Alias

OK, Alias is configured on all SharePoint WFE/APP Servers. Now what?
Its a best practice to setup SQL alias during initial farm setup. if not  re-run SharePoint Configuration wizard on a established farm . If your SharePoint is already up and running, launch SharePoint Products and technologies wizard and configure your SharePoint farm with the new SQL Alias.

Don't want to use SQL Server Alias? Run stsadm.exe -o renameserver -oldservername <oldname> -newservername <newname> on all servers to change the name of the specified server in the configuration database if the SQL Server is renamed.



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

1 comment :

  1. we have added the SQL alias to the web front ends and that did the magic.

    Question:
    I just happened to check back on the alias which does not exist any more in the configuration, it could have been wiped out after the OS patches? but SP never complained and still working without any issues.. the odd thing is, when I look at the central admin, the DB name that is refereed is no longer exists but SP still works.. I am just curios and wondering if the SP web front end servers once gets connected through alias, they don’t care about checking the alias configuration again to continue to work?

    Thanks!

    ReplyDelete

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...