Lars Nielsen's Discoveries

June 30, 2010

Moving the SharePoint Database Server

Filed under: Administration,SharePoint — Lars Nielsen @ 9:00 pm
Tags: ,

Following a move to a rationalise the database infrastructure across the organisation, I had to help with switching the SharePoint database server from a dedicated (though virtualised) server to a new SQL cluster.  Given that SharePoint basically IS its databases, this obviously involves taking down the whole SharePoint  farm.

Microsoft has posted a reasonable set of instructions on how to move all the databases to a new server .  This was the basis of what I did, but I found a few problems as I followed them along, so I’ll post my notes up here for future reference.  The problems I had were with the SSP and eventually I decided to skip over the steps that involve deleting and restore the SSP, and everything worked fine.  So I’ll post up here the simpler procedure that I followed:

Step 1 – Make preparations

It’s going to take some time to do this – and the whole farm will be out of action while the migration takes place.  So plan a time to take it down.  If you’re using a virtualized environment, you can make a snapshot of the whole database server so you can roll back if necessary.

Make preparations on the new database server.  Ensure that any access for the SharePoint accounts are set up the same as the existing database server, especially the SharePoint installation / setup account, and the database access account.  They need to the in the local Administrators group on the new database server.

Step 2 – Back up the SSP

Make a record of the important SSP settings so that in the worst case, you can regenerate the SSP.  Record things like:

  • Web applications associated with the SSP – names, URL, content databases
  • User Profile Import connections and any custom LDAP query
  • Profile import schedule
  • Search content sources
  • Search crawl rules, schedules, scopes, authoritative pages, best bets
  • Personalization permissions
  • Audiences
  • Usage reporting

Recording all this information might seem like a lot of work but it’s a good exercise anyway – it’s effectively documenting your SharePoint farm.

Afterwards log on to the server that is running the Central Admin (use the SharePoint installer account which should be a local administrator on that server) and make a backup of your SSP using STSADM, something like this

stsadm -o backup -directory -backupmethod full -item SharedServices1

If you get errors with this take a look at permissions.  The command will execute on both the FE server you’re on, and on the database server as well.  You need to make a share for the UNC path and give it write access to everyone, just while this command executes, then un-share it again.  Ensure that the SharePoint install account is a local administrator on the FE servers and the database server.

Step 3 – Stop the Farm

On each web front end server stop all the SharePoint services:

  • Microsoft Single Sign-On service
  • Office Document Conversions Launcher service  (if necessary)
  • Office Document Conversions Load Balancer service (if necessary)
  • Office SharePoint Server Search service
  • Windows SharePoint Services Administration service
  • Windows SharePoint Services Search service
  • Windows SharePoint Services Timer service
  • Windows SharePoint Services Tracing service
  • Windows SharePoint Services VSS Writer service (if necessary)
  • To be safe, make a note of the startup mode of the Office SharePoint Server Search and the Windows SharePoint Services Timer services, and then disable them so they don’t start up again while you’re working.  Then stop IIS with iisreset /stop

    Step 4 – Back up all the databases

    Log on to the database server using either the SharePoint Install account or the Database Access account.  Use SQL Server Management Studio to back up all the SharePoint databases to .BAK files at a suitable location that you can reach from both the old and the new database server.  Give each file a filename that is the same as the name of the database (you’ll need those later).  Make sure you include:

    • Configuration Database (usually called SharePoint_Config)
    • SSP databases (usually names that start  SharedServices….)
    • Content databases for the Search web application
    • Content database for the SSP Provider web application
    • Content database for the My Sites  web application
    • All other content databases

    If your server is a dedicated database server for SharePoint, you’ll be backing up all the databases.  An easy way to do this is to create a maintenance job which will do them all for you in one go.

    Record the account of the owner of each of the databases in Server Management Studio.

    Step 5 – Restore the Database Backups on the New Server

    Copy all the .BAK files onto the new database server.  Use SQL Server Management Studio to restore them with the same database names as they had originally.   Be careful to ensure that the MDF and LDF files are put into the right locations – not necessarily the default ones.

    Change the owners of the databases to be the same as they were on the old database server.

    Step 6 – Replicate the SQL Server Logins on the New Server

    One of the things you discover (usually pretty early on) when you try to copy databases from one server to another is that you also need to migrate the SQL logins across, otherwise you’ll get problems with accounts not being able to log on to SQL Server.  There are some useful scripts to help with this – you pick the one appropriate to your version of SQL Server and run it, that will create a stored procedure that will itself create the script to set up logins on the new database server.  So you run that stored proc on the old server, and it outputs a whole load of CREATE commands to create the logins – something like this:

    
    /* sp_help_revlogin script
    
    -- Login: BUILTIN\Administrators
    CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
    
    -- Login: NT AUTHORITY\SYSTEM
    CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
    
    -- Login: Domain\SPInstallerAccount
    CREATE LOGIN [Domain\SPInstallerAccount] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
    
    -- Login: Domain\SPDatabaseAcct
    CREATE LOGIN [Domain\SPDatabaseAcct] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
    
    -- Login: Domain\SPSearch
    CREATE LOGIN [Domain\SPSearch] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
    
    

    You copy this output into a query window on the new server, and run it there.  You might want to edit it first to prevent it from creating all the logins – you should only need to SharePoint ones.

    Step 7 – Create an Alias to the New Database Server

    This is the key step your Server Admin / Infrastructure people (or you, if you’re one of those people) might not like.  You’re going to set up a SQL Server alias trick, which still trick SharePoint into using the new database server even though it thinks it’s going to the old one.   It is going to be a bit confusing; when you look at your SharePoint farm in Central Admin, it will tell you that its database server is still the same as it was before.  But because of the alias, it’s not, and really SharePoint is using your new database server.

    To set up an alias, start the SQL Server Native Client Network Utility on all the servers that need to connect to the SharePoint database server.  That means all that are running SharePoint (front end, application servers).  Run the program in your Windows folder, at System32\cliconfg.exe.   Then check:

    • On the General tab, TCP/IP is enabled
    • On the Alias tab click Add
    • In the Server Alias box type in the old SQL Server name and (if necessary) instance
    • In the Network Libraries area, click TCP/IP
    • In the Connection Parameters box, in Server Name, enter the name and (if necessary) instance of the new SQL Database server.

    Step 8 – Restart the Farm

    Now everything is in place to restart the farm.  On each SharePoint front end or application server, restart the services that you stopped before.  If you disabled any of the services such as Office SharePoint Server Search, reset them back to how they were.  The services you need to start should be:

  • Microsoft Single Sign-On service
  • Office Document Conversions Launcher service (if necessary)
  • Office Document Conversions Load Balancer service  (if necessary)
  • Office SharePoint Server Search service
  • Windows SharePoint Services Administration service
  • Windows SharePoint Services Search service
  • Windows SharePoint Services Timer service
  • Windows SharePoint Services Tracing service
  • Windows SharePoint Services VSS Writer service  (if necessary)
  • Then restart IIS:  iisreset /start

    Hopefully, you should now be able to browse to the Central Admin site and your other SharePoint sites and they will all show up as before.

    Step 9 – Check

    To check that your databases really have fully moved over to the new server, you can use SQL Server Management Studio to go through all the databases on the old server and take each database offline.  You should be able to take each database offline smoothly because there should be no pending connections to any of them.  If you can’t then it means SharePoint still needs them which probably means that the SQL alias wasn’t set up correctly.

    Advertisements

    Leave a Comment »

    No comments yet.

    RSS feed for comments on this post. TrackBack URI

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    Blog at WordPress.com.

    %d bloggers like this: