Windows Sharepoint Services and SQL 2005 Enterprise
December 21st, 2005
Two weeks ago I had to migrate my WSS Database Server from a SQL 2000 Standard Edition to a SQL 2005 Enterprise Edition. I couldn’t find a complete step-by-step guide about this process, so now, after 2 weeks of watching my SQL 2005 server work as a WSS database server I’ve taken a couple of minutes to share my experience.
This is the scenario will be working with:
1. Stop production WSS Web Site to avoid user to continue adding items to content databases.
2. Backup Content databases and Configuration database on SVR-SQL2000 server.
3. Restore both databases on SVR-SQL2005. Restoration of SQL2000 Backup on a SQL2005 is quite natural, just restore them using the SQL Server Management Studio.
4. If you are using Windows Authentication, there should be a user called DOMAIN\SVR-WSS$ on both Content and Configuration Databases. You should create a login called DOMAIN\SVR-WSS$ on SVR-SQL2005 and map that user to the just restored Content and Configuration databases.
Now that databases are restored and permissions are properly configured we will redirect SVR-WSS to point to SVR-SQL2005 databases.
5. To set configuration database. Start SharePoint Central Administration on SVR-WSS.
6. Under Server Configuration, click Set configuration database server.
7. Complete the form using the SVR-SQL2005 parameters. Don’t forget to check the “Connect to existing configuration database” option.
8. Click OK. If permissions are properly configured the configuration database should be already redirected. Now we have to worry about the Content databases.
9. Under Virtual Server Configuration, click Configure virtual server settings.
10. Select your virtual server.
11. Under Virtual Server Management on the Virtual Server Settings page, click Manage content databases.
12. Under Content Databases, click Add a content database.
13. In the Database Information area of the Add Content Database page, click Specify database server settings.
14. Type the name of the server SVR-SQL2005 in the Database server box.
15. Type the name of the content database that you restored from backup in the Database name box.
16. In the Database Capacity Settings area, type the number that you want in the Number of sites before a warning event is generated box. Then, type the number that you want in the Maximum number of sites that can be created in this database box.
17. Click OK.
18. Remove the content database that was present before we added the new Content Database. Make sure to remove the content database that is pointing to the SVR-SQL2000 server.
19. You are done! Now you could startup again your WSS web site and make sure that WSS is using the SVR-SQL2005. The simplest way to do this is to take offline the Content and Configuration databases on SVR-SQL2000 and perform an smoke test on any pre-existing WSS site.