I've been asked to move all of our SP related DBs to a new named instance on the same SQL 2005 server. I've done a content DB move before by disconnecting the content db then reconentcing to it with the new location, but I'm a bit lost on what process to follow for the SSPs, mysites, and config. From what I can tell, the general consesus is that I should create a brand new config DB and SSP and migrate the content DB over. Is this the only clean option? My biggest concern is that it took me a while to get profile imports working due to a multitude of reasons, I'd hate to go through that again.

We have 2 WFEs, 1 search/index server, and a shared clustered SQL 2005 DB on the backend.



Generally speaking, pointing a new database instance (or instances) is a "deal breaker" when it comes to the farm. My "fallback safe opinion" tracks with the general consensus: construct a new farm (i.e., a new configuration database), build out your SSP, and then perform content database migrations into the new farm.

Here's a tip on avoiding this (painful) process in the future: implement SQL connection aliases. An article describing how to do this can be found here: http://decipherinfosys.wordpress.com/2007/11/26/using-a-server-alias-to-connect-to-sql-server-instances/. Aliases are also particularly helpful from a disaster recovery perspective, as they introduce a layer of abstraction between your farm and the supporting data layer.

I haven't tried this next item, so I mention it strictly as an investigative point ... but you might be able to implement aliases in your MOSS environment now before you do a rebuild and potentially avoid it altogether. Let's say your SQL Server is named "SQLSERVER" and you'll be going to a named instance on that server called "MOSS". For each MOSS server:

  1. Install SQL Client Tools
  2. Establish an alias named "SQLSERVER" that points to the server "SQLSERVER"
  3. Build out your named instance (SQLSERVER\MOSS) and copy over all databases for the farm to the new named instance
  4. Switch the "SQLSERVER" alias on each MOSS server to point to "SQLSERVER\MOSS" instead of "SQLSERVER" and see what happens.

At worst, this will fail and you'll have to build out a new farm as you're expecting to do now. At best ... you'll continue to run without a farm rebuild.

I hope this helps!

  • 1
    Sorry that I didn't have a better answer for you. The issue is specifically with SharePoint configuration databases; they maintain references to server/host names and the database server instance names. WSS and MOSS are sensitive to environment changes for this reason. I don't think you're completely up the river yet, though. See if you can skirt the issue with SQL aliases (steps #1 through #4) in your current environment. I have some hope that it'll work for you. I would definitely like to hear how things work out. Good luck! Sep 19 '09 at 2:04
  • 1
    I would expect a new alias to take effect immediately, but that's an educated guess. To avoid serious problems, you'll want to ensure that farm members aren't trying to connect while aliases are being established. The easiest way to do this would be temporarily take your SQL servers offline; alternatively, you'd need to shut down all SharePoint-related services (OSS, W3SVC, timer service, etc.) on all members. If you have VMs, I'd recommend testing a process there first to avoid prod problems. Your final point about aliases is accurate -- you'd be using SQLSERVER going forward. Good luck! Sep 21 '09 at 14:40
  • 1
    First off ... WOOT! That's great news, Agent; I'm glad that the aliases look to be (generally) working for you. You asked about SQL Server, aliases, and DNS. Your comparison to the HOSTS file is on-the-mark -- The SQL client will check for a local alias match before concluding that a connection string refers to a server/host name on the network. This behavior is specific to SQL server connection strings and database access Non-SQL access, on the other hand, will continue to use HOSTS and DNS for the resolution of endpoints. Aliases only affect SQL client connections. Does that help? Sep 25 '09 at 18:50
  • 1
    You also asked about SharePoint being built to check aliases. To be clear: this isn't a SharePoint behavior -- it's a SQL Server Client library behavior. Any SQL Server connection string can use the alias you established -- not just SharePoint. As I may have mentioned, this is commonly done for abstraction purposes to provide a certain level of location transparency to SQL operations for things like mirroring failover, DR, etc. Aliases are also commonly leveraged to map client access to SQL Servers running/listening on non-standard (i.e., non TCP:1433) ports. Sep 25 '09 at 18:54
  • 1
    Good question. I typically use the SQL Server Client Tools to manage aliases, so I wasn't sure about cliconfg.exe (since they aren't the same thing - but serve the same purpose). With a little checking, it would appear that cliconfg.exe is part of MDAC's (Microsoft's Data Access Components) distribution; I based that on Dan Guzman's response in the following chain: sqlnewsgroups.net/sqlserver/t1394-server-name-problem.aspx. Given that an MDAC distribution of some sort is on nearly every system these days, you're more likely than not to find it on a system regardless of MOSS. Sep 28 '09 at 17:12

I have detailed steps in a blog post on how to go about moving databases to a new database server using SQL connection aliases and I would imagine it would be of use in this scenario. Feel free to check it out at http://mossblogger.blogspot.com/2009/10/migrating-to-new-sql-server-in-moss.html and let me know if it is of any use to you.

Cheers, Benjamin Athawes.

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.