Sunday, 11 October 2009

Administration: Migrating to a new SQL Server in MOSS (SQL 2005 to 2008)

1. Overview

In this post I will describe the steps I took to successfully migrate from a SQL 2005 server to a brand new SQL 2008 server within a medium size production MOSS farm serving around 2000 users. The process makes use of the Microsoft supported method of migrating to a new DB server: SQL connection aliases.

As always, I
strongly recommend you follow the steps below in a test environment prior to carrying them out in a live farm.

2. Scenario

Prior to the migration, we had a medium sized MOSS farm consisting of the following physical servers:

  • 1 x86 SQL 2005 server running Windows Server 2003 SP2

  • 1 MOSS application server hosting the MOSS Central Administration Web site and the query / index roles.

  • 1 MOSS WFE server also hosting a MOSS Central Administration Web site for redundancy.

We host around 50 Web applications, each with their own content database.

The SQL 2005 server was very short on disk space (less than 20%) and, having considered a number of options including upgrading the disk capacity we decided that the simplest and safest (read "least risky") approach would be to purchase a new high capacity server and repurpose the existing SQL server (e.g. use it as an additional WFE server for redundnancy and performance).

We also decided that rather than install x86 Server 2003 and SQL Server 2005 on the new server, we would "future proof" the SQL back end by upgrading to x64 Server 2008 and SQL Server 2008.

As we would be repurposing the existing SQL server, we decided to go with the "Move all databases" approach documtned by Microsoft in this article:

3. Gotchas

There are a few "gotchas" that I recommend you consider when planning to move to a new SQL server:
  • There is no supported method of changing the name of the server that the MOSS Admin Content DB resides on. Instead, the documented MS resolution is to use SQL aliases to point MOSS WFE servers to the new SQL server (which in my experience seems to be very effective, but feels like a bit of a hack).
  • The process for backing up and restoring MOSS SSPs is different to that used to move content databases.
  • The entire farm must be stopped prior to moving any databases to prevent any synchronsiation issues. In my case downtime was around 3 hours.
  • The SSP backup may fail if you have renamed any of your Web applications (see below).

With regard to the last issue, the error message I received when attempting to backup the SSP as a part of the migration was as follows: "Object Shared Search Index failed in event OnPrepareBackup." This is documented here:

The steps I took to resolve the SSP backup issue are contained in the appendix of this post, and are only relevenat if the SSP backup fails for you with the same error message.

4.1 High level steps
  1. Backup all databases (including MOSS content and configuration databases)
  2. Backup IIS on all WFE servers
  3. Ensure integrity & dates of NTBackup file system backups on MOSS WFE servers (e.g. 12 hive, IIS directories)
  4. Follow DB migration process outlined below
  5. Stop SQL services on original DB server to ensure migration success
  6. Test all portals
  7. Add Backup & maintenance routines to SQL server
  8. Modify any network or off-site backup routines your organisation may have to include the new server.
4.2 Detailed DB migration steps

Note that the steps below are basically a concise version of those contained at with my own notes added in blue that I took whilst following them:
  1. Follow to prepare the new DB server.
  2. Record which Web applications are associated with the SSP
  3. Back up an SSP by performing the following steps:

    On the drive on which SharePoint Products and Technologies is installed, change to the following directory:

    %COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\12\Bin.

    If you do not already know which node you want to back up, type the following command:

    stsadm -o backup -showtree

    In our case the SSP name was "SharedServices". Note that the UNC directory must be a shared drive accessible to both the WFE and DB servers as opposed to a local path on the WFE server.
  4. Back up each SSP by typing the following command:

    stsadm -o backup -directory -backupmethod full -item

    Ensure the shared drive ("UNC Path") is available to the SQL server - i.e. in the format (\\server name\folder name) as opposed to a directory local to the server. This should complete with 0 warnings and 0 errors. The account used to perform this action must have access to backup databases on the database server (e.g. domain Administrator account).
  5. After backing up, remove each SSP by following these steps:

    On the disk on which Microsoft SharePoint Products and Technologies is installed, change to the following directory:

    %COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\12\Bin.

    To remove an SSP, use the following command:

    stsadm -o deletessp -title -deletedatabases -force

    I believe that removing the content DB in central administration has the same effect.
    If using STSADM, check the content database name for the SSP Web application in central administration. In our case the SSP contentdb name was "WSS_Content_
  6. Delete each SSP content databases in SQL Management Studio.
  7. Stop the farm by stopping all SharePoint services on all WFE servers (documented in MS article referenced above). Then, on each server that is running the Central Administration Web site, at the command prompt, type iisreset /stop.
  8. Back up all databases on the existing (old) database server. I performed a full backup of all "User" databases, then copied them to a separate server within the farm for safety.
  9. In Windows Explorer, locate the database backup (.bak) files that you want to move, and then copy or move them to the destination server.
    Important: Move only the backup files to the destination database server. Do not move any databases or other files at this time.
  10. Restore all databases on the destination database server.
  11. use to copy all SQL Server logins using a couple of stored procedures.
  12. Refer the farm to the new database server by creating a SQL Server connection alias.
    Add aliases for both the FQDN and hostname.e.g. add hostname and Perform this step on all MOSS servers in the farm.
  13. Start the farm by starting all SharePoint services on all WFE servers (documented in MS article referenced above). Then, on each server that is running the Central Administration Web site, at the command prompt, type iisreset /start.
  14. Obtain the GUID of each successful SSP backup:

    stsadm -o backuphistory -directory
  15. Restore each SSP to the new location:

    stsadm -o restore -directory -restoremethod new -backupid -newdatabaseserver

    Again, Ensure the shared drive ("UNC Path") is available to the new SQL server - i.e. in the format (\\server name\folder name) as opposed to a directory local to the server. This process took around 10 minutes to complete. This should complete with 0 warnings and 0 errors. Note that this process can be rolled back (by deleting the restored SSP) using:

    stsadm -o deletessp -title -deletedatabases -force
  16. Remove backup SSP directories.

5. Summary

In this post, we took a look at the process for migrating all databases from a SQL 2005 x86 server to a SQL 2008 x64 server. In future posts, I may go into more detail around the specification of the new server including decisions about the hardware choices and RAID configuration. Please post a comment if this post helped you!

6. Appendix: How to resolve SSP backup error "Object Shared Search Index failed in event OnPrepareBackup."

This is only appicable if you receive the error above when trying to backup a SSP. Note that this takes a LONG time if the SSP backup fails for multiple Web applications - you may decide to simply recreate the SSP rather than carry out this process but I chose this option as it seems to me like the least risky choice. In my case, this process took 10 hours for 23 Web applications but this time will vary depending on your experience with MOSS and number of Web parts you have.

  1. Document the following MOSS Central Administration settings for each Web application that needs to be recreated to allow an SSP backup to succeed: "Policy for web application", "authentication providers", "Web application general settings" (timezone) settings, which Web parts that are deployed to which Web application, any people picker settings that need to be restored using STSADM.
  2. Take the farm offline - in my case I simply created a rule to block all connections using ISA server 2006.
  3. Backup IIS for all WFE servers.
  4. Backup all MOSS DBs including content and configuration databases (you never know...).
  5. Delete the Web application in SharePoint that failed to backup - do NOT delete the IIS Web site or content DB.
  6. Ensure you have a full backup of the IIS Web site content - I used NTBackup. In particular, ensure you have a backup of the Web.config file as SharePoint overwrites this when you recreate the Web app.
  7. Delete IIS Web site
  8. Create new Web application with new IIS site & note down default (temporary ) content DB name (to be deleted)
  9. IISreset /noforce
  10. Remove temporary content db in MOSS Central Administration
  11. Delete temporary content DB in SQL Server Management Studio.
  12. Reattach correct content DB and confirm no. sites > 0- restore web.config from NTBackup-
  13. Repeat the above for all sites that are reported when attempting to backup the SSP.
  14. Backup IIS on all WFE servers again in case a manual virtual directory restore is required
  15. Restore IIS backup on all WFE servers
  16. Reconfigure the "Policy for web application", "authentication providers" and "Web application general settings" (timezone) settings for each restored Web application in MOSS central administration.
  17. Redeploy web parts to all restored web applications
  18. People picker settings for all restored web applications using STSADM.

Subscribe to the RSS feed

Follow me on Twitter

Follow my Networked Blog on

Add this blog to your
Technorati Favourites


  1. Thanks for sharing your experience with detailed instructions. For your readers who are instead changing WFEs and keeping the SQL backend the same, here is a link back to my article about that:

  2. Thanks for the post - I'm trying to undertake this process in a lab environment currently. In my process I'm going from a SQL 2000 to 2008 server. However I did hit a snag and I'm not sure whether or not its a SQL or SharePoint WFE issue. I get to the point where I establish the alias on the WFE to point to the new DB server which has all my restored DBs and can't get it to render Central Admin (I get a 404 not found). Also when I attempt to retrieve the backup history via stsadm backuphistory i recieve an Access Denied error, so this led me to believe it was a SQL 2008 permission error. I've checked for what I may have missed. I can make and ODBC connection to the server and all the logins have been copied to it.

    If you have any ideas as to what this might be then I'd love to know.


  3. @ Andrew:

    The access denied error for backuphistory via stsadm is most likely one of two things:

    1. You didn't specify a network share that is available to both the SQL and WFE servers (i.e. you may have specified a folder local to the Web server and not available to SQL or visa versa)


    2. The SQL account quite literally doesn't have NTFS permission to the file share (remember file share permissions are separate to folder permissions)

    "Again, Ensure the shared drive ("UNC Path") is available to the new SQL server - i.e. in the format (\\server name\folder name) as opposed to a directory local to the server."

    let me know if that helps.

    With regard to the 404, I assume you have tried the obvious things (e.g. event log checking, IISReset etc.)?

  4. Thanks for the reply Ben.

    Turned out it was something weird with my 'test' lab environment. The process does work and my 2nd run I got it to work (well to a degree). I have an environment that isn't a vanilla deployment one of our developers has done a fair bit of work and I can't get those things to work as they do in production. I have a couple of questions:

    I see this method described above as the defacto approach to migrating to a different server I'm going to be actually in a position where I can follow the process described on technet called:
    "Move all databases to a database server that has the same name". Which to me looks like a cleaner option and bypasses the backup / restore of the SSP. If you had the chance would you have gone down that route?

    Secondly any idea if changing the SQL Database compatibility level from 80 to 100 would have any benefit or cause any issues if I were to do that - I can see my manager carrying on about that if I don't change it.

    Third - my lab is a bit of a mess due to the fact I cobbled together a restore to a few machines that have a different domain so I have had problems. Provided a restore to a new farm on the production network would I be risking any part of the production server in doing this? (I would be keeping AAMs and DNS entrys unique to the dev farm). Sorry for all the questions.


  5. Hi Andrew,

    Sorry for the late reply - I have been exceptionally busy recently as we had a client deadline to meet.

    The answer is yes - if I were moving to a DB server with the same name I would have followed that route as the SSP restore was not pretty and took the best part of a day.

    With regard to the SQL DB compatibility I would be interested to know how you got on with this. My answer would be to ensure that you follow best practise (documented here for example - don't change the compatibility level while users are connected to the database as that might impact your existing users. I don't see it causing an issue but would always recommend performing a full DB backup before making a change such as this. The same goes for maintenance tasks such as shrink operations.

    My answer to your third query would depend on what you mean by a restore (i.e. SQL, system state, IIS etc).

    I look forward to your reply.

  6. Ben,

    what would happen if both SSP and content database are being backup and restore through the SQL, and configure SQL Alias? Will it cause issue for the SSP? What's the reason behind for the SSP need special treatment? I can't seem find any rational explaination on the web.
    Thanks in advance.

  7. vcllvc,

    One good reason is that the search indexes are not stored in SQL Server. Therefore, a SQL server backup will not allow you to restore search.

    Search indexes are stored on the file system and are backed up using the STSADM command above.

    See this article for more details:

  8. thanks for your reply, and great article link.

    So, if the search indexes are small, and we decided to be reset/recrawl after the move anyway, will the SQL Backup/restore SSP DBs work?

    My point is trying not to touch sharepoint as much as i could, and don't want to break it.


  9. Vcllc,

    I understand your concern.
    However, even if the stsadm backups/restore fails you would still presumably have the content databases as a fall back.

    I can only recommend the approach above given that a.) it worked and b.) it is MS supported.

    If you are worried why not give it a try in a test environment?


  10. That's what i am doing on my VM, now.
    Thanks again.

  11. Hello, I am new to Sharepoint Administration and need to lead a DB server migration. My scenario: Our current SP databases are already being restored onto the new SQL server. Content DB's are log shipped and the Config DB's are backup/restored, so it basically a mirror of prodcution. For the initial test I was given a VM, which I did a stand alone install on (SP and SQl Server). I got a simple Team Site stood up on the VM, however our current production site collection is using a Publishing Site template. I have been trying to detach the newly created content DB on the VM and then reattach to the mirorred Db's on the new server. I was able to attach the Content Db but the site won't render now. Do I also need to do the config Db's too? Is this even the correct way to do this migration test? Does it matter that site templates are different? Any advice would be greatly appreciated... Thanks in advance

  12. Hi Ray,

    My first thoughts are that a SharePoint DB migration can be quite a daunting task for someone new to the product so it may be perhaps worth seeking the advice of a consultant prior to going ahead (I'm not one).

    To point you in the right direction, I have a few pointers that may help:

    - The process to move all database for SharePoint 2010 is different to that of MOSS (there are more database for a start) so ensure you are following the correct procedure. The 2010 process is here:

    - Restoring a configuration database is not supported except in very specific scenarios. Moving all databases is one of those scenarios but you need to follow the specific process outlined in the article(s) referenced in my post. The key points are that the environments need to be identical and the farm needs to be stopped prior to the move.

    - SQL alises are used to "point" your SharePoint servers at the new SQL DB server.

    I hope that gives you a better idea of the overall process.


  13. This comment has been removed by a blog administrator.

  14. This comment has been removed by a blog administrator.