1

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.

Agent
  • 314
  • 4
  • 16

2 Answers2

5

Agent,

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!

Sean P. McDonough
  • 786
  • 4
  • 6
  • Well that absolutely blows. I just don't understand why MS didn't make it as modular when it comes to the DB. I never got the chance to work with SPS 2003 but from what I understand, it was a bit more forgiving about DB moves like these. It's problematic and time consuming enough when I'm talking about my fairly small environment, but I can't imagine the hassle of something like this in much bigger environments with custom solutions, huge/multiple DBs, thousands of sites, uptime requirements, etc. Thanks Sean, I'll also look into the aliases option, just have to talk to the DBAs about it. – Agent Sep 18 '09 at 21:32
  • 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! – Sean P. McDonough Sep 19 '09 at 02:04
  • Thanks for your help Sean, just a couple of other question. I understand the general concept, but how would this work while Sharepoint is running? If I add the alias to a production WFE then change the mapping to the new server, will it start connecting through the alias as soon as I press Ok or should I restart any services before/after for this switch to prevent any corruption of the content/config DBs? Also, it seems with this I won't be able to change the name of the alias for accuracy, it will have to stay SQLSERVER instead of SQLSERVER\MOSS. Not that it's a big deal for now I guess. – Agent Sep 21 '09 at 14:19
  • 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! – Sean P. McDonough Sep 21 '09 at 14:40
  • Thanks, I most likely will try this after business hours today. I'm sure I'll encounter some issues as I always seem to with SP, but even if it doesn't work as expected in this case, it's very nice to know about SQL aliases for future reference. I'll update this post after the switch. – Agent Sep 21 '09 at 15:12
  • Interestingly enough, I'll be in a position to test this soon myself. I'm building a new database server for one of my farms in the next week or so, and aliases aren't in use (due to an incompatibility between their use and DPM 2007's operation). As part of the move, I'll give this a shot (using aliases) and report my results, as well! – Sean P. McDonough Sep 23 '09 at 16:39
  • Sean, I made the change yesterday and looks like it worked *almost* perfectly. I'm not exactly sure what caused the connections to the DBs to fail but signs point to the DBA team not allowing one of the SP user accounts access to the config DB once I made the alias change. Once they granted access, Sharepoint access came back online! The only thing I'm curious about is that I sort of got into it with a DBA who said that the SP server will use DNS to find the remote server and that my cliconfg settings were not right. Continuing in next comment...blasted limit – Agent Sep 25 '09 at 14:18
  • The reason he thought this was because my alias name was set to the "old" DB server name. I told him that this was the point of the alias, so that SP does not have to be changed, the server handles redirecting the connections from old server to the new. I guess my question is, is SP built to check aliases/cliconfig first and then DNS, similar to how Windows checks the HOSTS file before hitting DNS? Thanks again! – Agent Sep 25 '09 at 14:27
  • 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? – Sean P. McDonough 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. – Sean P. McDonough Sep 25 '09 at 18:54
  • I guess what I meant to say was, is cliconfg a standard utility with Windows or was it installed during the MOSS install? I have a dozen or so other apps I manage but have never had a vendor or DBA suggest using this utility to manage connections. It would've made a LOT of things a hell of a lot easier for 15-20 various DB migrations we've done in the past couple of months. – Agent Sep 28 '09 at 15:46
  • 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: http://www.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. – Sean P. McDonough Sep 28 '09 at 17:12
  • Great, that answers all my questions...for now! Thanks! – Agent Sep 29 '09 at 16:50
0

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.