4

My client has asked me to move all the Database used by the SharePoint Farm to a new SQL Server Machine. I came across this article http://technet.microsoft.com/en-us/library/cc512725(v=office.14).aspx (Move All Databases) in Microsoft Technet.

Have anyone tried the steps mentioned in this article and came across any issues?

For setting up SQL Server Connection Alias http://technet.microsoft.com/en-us/library/cc512725(v=office.14).aspx#Alias Do I need to run the SQL Server configuration manager on SQL Server Mahcine or Do I need to use CLICONFG.EXE on the Application Server(Central Admin machine)?

Someone has mentioned in this http://msdn.microsoft.com/en-us/library/ms190445(v=sql.105).aspx article (go to the bottom of the article) that we should use CLICONFG.exe on the Application Server and the terminology "SQL Server Configuration Manager" is bit confusing.

Could someone verify if this SQL Alias settings will work?

enter image description here

Your suggestion much appreciated.

Improve this question

3 Answers 3

Reset to default
5

The procedure described in these articles should work just fine. I've done this multiple times.

You don't need to install the SQL Management or Client Tools on every SharePoint server. Just us the cliconfg.exe program to setup the alias. You can find more information on my blog at:

http://thomasvochten.com/archive/2009/03/using-sql-aliases-with-sharepoint/

It is also considered a good practice to always connect SharePoint to SQL Server by using an alias. You can even create multiple aliases should you decide to move different databases to different servers in the future. For example: a SPCONTENT alias for your content databases and a SPSEARCH alias for your search databases.

There are of course also downside to aliases. Someone who is not familiar with your farm might not realize SharePoint is using an alias in the first place. Second, you'll have to create these aliases on every SharePoint server (manually or scripted).

Also be aware of the fact that on a 64 bit system, you create an alias for the 64 bit programs only by just using cliconfg.exe If you have 32 bit applications that want to use the same alias (SQL Mgmt Studio for instance), you also have to create a 32 bit alias:

http://thomasvochten.com/archive/2011/06/sql-aliases-in-the-real-world/

Improve this answer
6
  • Hi Thomas, After creating SQL Alias do I need to change the settings in SharePoint Configuration Wizard (I mean the database name settings as mentioned in your article) and re-run the config wizard...
    – Kannan Karmegam
    Nov 14, 2012 at 3:56
  • Hi Thomas, I have updated the post with the screen shots of SharePoint Config settings and the SQL Alias settings. Is this correct?
    – Kannan Karmegam
    Nov 14, 2012 at 7:29
  • 2
    You should always connect SharePoint to SQL Server with a NETBIOS hostname, not with an IP address or a fully qualified host name! So first create the SQL Alias e.g. "SPALIAS" and use that one in the config wizard.
    – Thomas Vochten
    Nov 14, 2012 at 8:25
  • Hi Thomas, Sorry to bother you again. There is no option to edit the database server name in config wizard. Do I need to disconnect from the existing farm and then connect to new farm. I assume when joining to new farm I have to provide the SQL Alias name? Please correct me if i'm wrong.
    – Kannan Karmegam
    Nov 16, 2012 at 0:16
  • You can indeed disconnect and connect again. BUT you can also create an alias with the old server name pointing to the new server name. A bit confusing perhaps, but probably safer. I've done this too it works great, SharePoint won't notice.
    – Thomas Vochten
    Nov 16, 2012 at 8:00
0

If you are working with a number of SQL Server instances (or have really complex server/instance names!), you may already be familiar with aliases and using it to connect to instances. We create aliases for instances using SQL Server Configuration Manager.

However, SQL Server Configuration Manager is not shipped with OS and it will not be available on client machines.

To define an alias on a client machine, you can use the classic SQL Server Client Network Utility. You can launch this by going to Start > Run > CliConfg.exe

Improve this answer
1
  • One thing I've heard about, which you want to keep an eye on is if you have 64 bit OS. Then it is possible that the registry entries is under some other key, which cliconfg.exe doesn't understand. I dont't have such a machine to play with right now to verify, though.
    – Falak Mahmood
    Nov 23, 2012 at 15:23
0

In SharePoint Moving a database from one server to another server in SQL Server 2008,I can suggest 1 URL please go through it http://blogs.msdn.com/b/sreekarm/archive/2009/09/11/move-a-database-from-one-server-to-another-server-in-sql-server-2008.aspx

Improve this answer
1
  • Link-only answers tend to get void if the link is broken, kindly add relevant part of the posts in your answer.
    – Arsalan Adam Khatri
    Apr 22, 2015 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.