SharePoint 2010: Migrating SharePoint Database Instance to Another Server

  • Article

The first line in this article must be written to thank to Todd Klindt, Nauplius Trevor and Sean McDonough – world renowned SharePoint MVPs – for quickly helping me on Twitter in migrating a SharePoint database instance to another server.

http://i1235.photobucket.com/albums/ff428/thuansoldier/twitter_zps4b2ce55f.png

Migration is surely a nightmare to those who are responsible for it, and I’m not an exception. Having not slept approximately 32 consecutive hours (it’s meant I had to work while people were sleeping, not what I spent all), now I feel like I’m comfortably flying to a paradise even I’m really tired due to long period continuously working on a migration I have to bear from another deployment team who did before. The worst practice the deployment team did was install both production instance and development instance on the same physical database server. The following image depicts topology of the existing SharePoint farm:

http://i1235.photobucket.com/albums/ff428/thuansoldier/migration-image_zps937971ef.png

I know there are a lot of articles that walk readers through steps to migrating SharePoint database to another server. In my case, it might be different from those because I ran into several issues during the migration and now I would like to share those issues and workarounds. As a result, you will fill up your knowledge pocket that is a fellow-traveler on your career path.

As you see, the production database server has two instances: SVSQL5 (default instance) and SVSQL5\SPDEV (named instance). Both SharePoint production server and development server connect to the production database server (SVSQL5). When having such an installation, there are some potential issues you may encounter:

  • Poor performance on database server because simply development team develops on SharePoint development server while end-users are doing their tasks on SharePoint production server. Data continuously writes to drives while SharePoint services are performing.
  • Carelessness of DBA who don’t have much security mindset potentially exposures passwords to evildoer so your production farm would be able to be attacked.
  • Execution of code performed by developers would effect on production farm.

There are a few more issues I may have missed because to be honest I’m not a DBA expert.

As highly recommended by me during SharePoint environment analysis stage, the development instance should have to be detached from the production environment and will be copied to a new server. Another requirement is that the migration is expected not to result into any downtime of the production environment.

You have two approaches basically:

  • Move entirely the development instance to a new server: this way requires you having expertise of SQL Server in-depth skills in order to move all components and system configurations.
  • Backup all SharePoint-related databases and then restore them to a new server: this way would be much easier when you only need to backup and restore databases to the new server and then point SharePoint to a named alias created on the new server. However, for some cases that require a more secure and stable migration, the first option should be considered. Note that if you decide to choose this approach, make sure you have the same SQL Server edition on both source and destination, unless the copies of databases will never be possible to restore back to different lower SQL Server edition. For example, you cannot restore from R2-instance to non-R2 instance.

Backup and Restore SharePoint-related databases

As an experienced SharePoint administrator, you don’t have to be much worried about that. There are countless articles covering SharePoint backup/restore whether performed through Central Administration, PowerShell, SQL Server Management Studio or 3rd-party tools. You may be asking which way is the most common used. My immediate answer is “it does depend” even I know some folks raise against the answer. My development instance doesn’t have many databases, just the following basics:

  • Central Administration content database
  • Farm Configuration database
  • State Service Database
  • Content database
  • Search service application database
  • Managed metadata service database
  • User Profile service application database
  • Usage and Health Data Collection

I used Backup feature on SQL Server Management Studio to backup all these databases and then copied them to a drive on the new server, in my case it’s SVSQL6. When I restored using Restore feature, I got error: The backup set holds a backup of a database other than existing database. The workaround to solve it is to execute a T-SQL script that has WITH REPLACE.

Make sure you put commas after REPLACE and between MOVE command lines, unless you will get error saying Incorrect syntax near ‘MOVE’ or something else.

SQL Server can’t restore database because you wrongly specify physical name or logical name of the backed-up database. To list them, use the script below:

1 2 RESTORE FILELISTONLY FROM DISK = ‘E:\Database_Move\SharePoint_AdminContent_Dev.bak’

http://i1235.photobucket.com/albums/ff428/thuansoldier/filelistonly_zps519d10a7.png

You don’t have to move a database and its log to directly physical name listed because location on destination may be differently configured. For example, my source server has two volumes: E and F. Volume E stores SharePoint databases and volume F stores SharePoint database logs with the correspondend paths you see in the image. However, location on my destination server is totally different even it has two volumes E and F. Volume E has root folder named Data that stores SharePoint databases and volume F has root folder named Logs that stores SharePoint databases logs restored from the source server. In this case, the script should be:

1 2 3 4 5 RESTORE DATABASE SharePoint_AdminContent_Dev FROM DISK = 'E:\Database_Move\SharePoint_AdminContent_Dev.bak' WITH REPLACE, MOVE 'SharePoint_AdminContent_GUID' TO 'E:\Data\SharePoint_AdminContent_Dev.mdf', MOVE 'SharePoint_AdminContent_Dev_GUID_log' TO 'F:\Logs\SharePoint_AdminContent_Dev_log.ldf'

After restoring all databases, you should compare size of database between the source and destination to make sure you successfully perform a restore.

Pointing SharePoint to a new alias

SQL alias has made my day wonderfully! I secretly thank to Microsoft in case the concept of SQL Alias is coined by them, but still hate them sometimes due to vague errors on SharePoint. It is not easy to explain what it is but according to Microsoft, an alias is an alternate name that can be used to make a connection. Todd Klinkt has an explanation more straightforward and understandable than Microsoft’s, imagine a SQL alias as a HOSTS file for SQL connection. It it configured at client side to expect a server can connect to a SQL server that has the same alias name. To know the SQL alias your SharePoint server specifies to, open Central Administration and then click Application Management. Hit Specify the default database server under Databases section.

http://i1235.photobucket.com/albums/ff428/thuansoldier/alias_zpscde17270.png

You might get surprised when seeing under Database server is not the name of your database server or named instance. I’m sure that if you are not familiar with SQL alias. In fact, the name of database server in my case is SVSQL5\SPDEV. Svsqldevsp is the name of alias that is configured to facilitate SQL Server client connection.

As recommended by Todd Klindt, you should have to stop the following services using net stop <service name> command

  • World Wide Web Publishing Service service (w3svc)
  • SharePoint 2010 Timer service (sptimerv4)
  • SharePoint 2010 Administration service (spadminv4)
  • SharePoint 2010 Tracing service (sptracev4)
  • SharePoint Server Search 14 (osearch14)

The next step is to create the same name alias on the new server (SVSQL6), and then change alias configuration on the development server. On SharePoint server, open SQL Server Client Network Utility by opening Run and type cliconfg.exe (full path C:\Windows\System32\cliconfg.exe). Click Alias tab and edit the existing alias.

http://i1235.photobucket.com/albums/ff428/thuansoldier/alias-confg_zps6742e32c.png

Keep Server alias value by default and change Server name value to the new destination, in my case it’s SVSQL6. Do the same steps on all SharePoint application servers you have.

On the destination server, do the same steps but you have to type Server alias value.

Using net start <service_name> and iisreset to complete a reset for a whole SharePoint application server.

After restarting, I tried to open Central Administration web application and then it was working well but there was a very vague commonly error when opening one of my site collections.

http://i1235.photobucket.com/albums/ff428/thuansoldier/error-sp_zps01121f49.png

I looked up the correlation ID on ULS Viewer and noticed that something was wrong with SesstionStateService database (System.Data.SqlClient.SqlException: Cannot open database “SessionStateService_GUID” requested by the login. The login failed for user…) that I didn’t restore because honestly I thought it was not important so I would do later.

http://i1235.photobucket.com/albums/ff428/thuansoldier/uls-state_zps8b117f0c.png

I restored it and that site collection worked like a charm. Make sure you move all SharePoint databases to new destination, don’t be careless like me.

The last thing I would highly recommend is to check on Logins on SQL Server Management Studio to make sure all accounts have at least Login permission. For specific database, database role memberships are copied after you restore. You wouldn’t run into permission-related issues in this case.

Additional references: