Upgrading SharePoint SQL Servers to SQL Server 2008.

SQL Server 2008 is now released and is supported by WSS 3.0 and MOSS 2007 SP1 and above although we probably won't see a supportability statement anytime soon due to resource constraints with the content folks. I will let you know when the official supportability statement is released.

[Update] - Support has been annouced. See https://technet.microsoft.com/en-us/library/cc262485.aspx for MOSS and https://technet.microsoft.com/en-us/library/cc288751.aspx for WSS. (look towards the bottom)

Upgrade from 2005 to 2008 is a pretty simple process. Here are some things to be aware of when using Upgrade Advisor. If you run upgrade advisor (recommended for SQL servers not dedicated to SharePoint 2007) you will see the following warnings in the report. These warnings can safely be ignored:

· Full-Text Search has changed in SQL Server 2008 – SharePoint 2007 no longer uses Full Text Search and will not be affected by these changes.

· Column aliases in ORDER BY clause cannot be prefixed by table alias – This is flagged on each instance of the proc_GetTpPageMetaData stored procedure in each content database. Though this issue suggests that this stored procedure will not work correctly in SQL 2005 and SQL 2008, it apparently does and can be ignored.

· Other Database Engine upgrade issues – The upgrade advisor doesn’t check for all possible upgrade and compatibility issues. This can be ignored for SharePoint 2007 databases.

 Here are some additional things to be aware of when building out a new farm or provisioning new services on SQL server 2008.

(Thanks to Gabe Bratton and Rahul Sakdeo for this info)

· SSRS and MOSS Report Center – On servers where MOSS is installed on top of SQL server 2008 with Reporting Services the potential for a URL conflict exists since they can both end up with the same url. The workaround would be to use a non-default web site for hosting MOSS. You can distinguish the sites using and IP address, Host Header, or Port.

· Least Privilege Deployments and WSS 3.0 – There is at least one known issue where provisioning a new web application will fail if content access account is running without sufficient permissions to the new database. I haven’t reproduced this or tested a workaround, but I imagine that if you temporarily give the content access account sys admin perms on the SQL Server you will avoid this error.

You will need to install .Net 3.5 SP1 and hotfix KB942288-v4 (Windows Installer 4.5) - Update services will be stopped and started during the install. These installs will likely require a reboot. One will wait for the other to complete before prompting to reboot. (two installs - one reboot)

Run setup.exe again after reboot and choose installation > upgrade from SQL Server 2000 or SQL Server 2005 and follow the prompts. I imagine a resource constrained or large SQL instance will take some time to upgrade. I did not notice any loss of availability with my databases during the upgrade which is good since my upgrade took almost 4 hours per instance, not including the prerequisites install. I have a feeling it took so long because of the lack of memory on my virtual server. (2GB) There was lots of paging occurring during the upgrade. The upgrade process itself seems lightweight, never consuming more than a few percent of the proc and about 30MB of memory.

If using database mirroring, upgrade the mirror and witness instances first. If running a mirroring split (principal databases on both instances) fail over to one node or the other. By upgrading the mirror and witness first you will ensure mirroring continues to work during the upgrade and you will minimize downtime due to the mandatory reboot. Make sure to upgrade the witness server and mirror server before attempting a failover. Else, the failover will fail and you will end up with unprotected databases and worse, you will need to break mirroring to bring your principal databases online.

All in all, the upgrade from SQL 2005 to SQL 2008 is a straightforward process. While I recommend you test the upgrade process I doubt you will find any surprises on a dedicated SharePoint backend. I hope that IT shops fast track this upgrade so we can focus on and take advantage of the new feature set in 2008. I plan to talk about how we SharePoint folks can leverage those new features in a post in the near future. [Update] See https://blogs.msdn.com/mikewat/archive/2008/08/19/improving-sharepoint-with-sql-server-2008.aspx for information on new features.

Comments

• Anonymous
  August 11, 2008
  PingBack from http://hoursfunnywallpaper.cn/?p=740

• Anonymous
  August 11, 2008
  SQL Server 2008 is now released and is/should be supported soon by WSS 3.0 and MOSS 2007 RTM and above

• Anonymous
  August 19, 2008
  Microsoft has announced SharePoint Products and Technologies 2007 (SP1) support for SQL Server 2008.

• Anonymous
  December 16, 2008
  I’m sure you have seen these cool SQL Reporting Services 2008 reports: Microsoft SQL Server Reporting

• Anonymous
  March 04, 2009
  very nice, SQL is VERY cool on Reporting (Services and Builder), one can even get Filestream to work for Sharepoint 2007 (but is till unsupported ;-) http://www.decatec.it/blogs/2008/11/26/SQL+Server+2008+Filestream+Working+For+Sharepoint+2007.aspx