Shola Salako

Hello SharePoint Gurus:

I need to upgrade my SharePoint 2003 and MOSS 2007 databases from SQL Server 2000 to SQL Server 2005. Is there a whitepaper available explaining how to do this Any feedback or pointers on how to accomplish this would be greatly appreciated.

Thanks in advance!

Shola Salako.




Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

Stephen-SP911

Shola,

Are you MOVING the databases to a different machine or are you just upgrading all on one machine If you are staying on the same machine I would recommend upgrading SharePoint first then upgrading SQL. If you are moving everything to a different server it could be more involved.






Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

Shola Salako

Thanks for the response, Stephen.

We have separate macines for SharePoint and SQL Server. We are actually moving from the SQL 2000 databases on one machine to another machine that is running SQL 2005. The SharePoint versions would stay the same (we are running both SharePoint versions - 2003 and 2007 - on various machines). I pretty much need to repoint SharePoint servers to new SQL 2005 servers. Is this going to be very involving What are the risks or impacts in doing this

Thanks again,

Shola Salako.






Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

satish

hi, i am using share point 2007 ,i have designed the form in infopath 2007 and pulblish in sharepoint 2007 with datbase connection , i am using sql server as database.Now i have created table in database and if i enter data in sharepoint form its not storing in database and i used sumbit button in sumbit button what code should i use or i can use rules to store the data. please help me



Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

Stephen-SP911

It is a pretty detail intensive process. Here is a Micrsoft KB for doing it in 2003:

http://support.microsoft.com/default.aspx/kb/894164






Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

Shola Salako

Thanks, Stephen. I found this MS article as well, but it did not say anything about moving from SQL 2000 to SQL 2005. I guess the SQL 2000 SharePoint databases can be restored to an SQL 2005 server.

Thanks for your help.

Shola Salako.






Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

David Goebel

Hi Shola,

I am in the exact same situation as you. I too have seen the KB mentioned above, but it does not address our scenario of migrating from one SQL 2000 box to another SQL 2005 box. I have also been in contact with a memeber of the Microsoft SharePoint team, but so far he has not been able to provide me with any documentation either. This a real pain!





Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

David Goebel

I retried following KB article 894164 using my configuration listed below:

Portal Server

Windows Server 2003 with SP2

SharePoint Portal Server 2003 with SP2 with a legacy backwards compatible document library (BCDL)

Windows SharePoint Services SP2

Internet Explorer 6

SQL Server (current)

Windows 2000 with SP4

SQL Server 2000 每 8.00.2039

SQL Server (new)

Windows Server 2003 with SP1

SQL Server 2005 每 9.0.2047

It all went well until the restore on the new SQL Server 2005, where it failed. The event viewer on the SQL box listed it as a ※Failure Audit§ with event ID 18456. The description is ※Login failed for user 'domain\nameofserver$'. [CLIENT: xxx.xx.xx.xx]§ where it listed my domain, the name of the server and the IP address.

I then attempted to create a new portal, however it also had event 18456 login failure for the user that I use for the portal application pool stuff, which is a domain admin level account. After the portal builds, when you attempt to open the homepage I get an error that states ※An unexpected error has occurred. Web Parts Maintenance Page: If you have permission, you can use this page to temporarily disable Web Parts or remove personal settings. For more information, contact your site administrator. ※ When I then click on Web Parts Maintenance Page, it pulls up a blank page that says ※Array cannot be null. Parameter name: source§.

I then deleted the corrupted portal I had just created and then I went back through the steps in the KB article and switched all of the database settings from the SQL 2005 box 每 back to the original SQL 2000 box. I then performed a restore from the Manage Portal Sites screen in SharePoint Portal Server Central Administration and thankfully it seems to have restored back to where I started.

Are we having fun yet

So when attempting to restore to the new SQL 2005 box, it appears that there is a permissions issue# but where the conflict resides is another story. I am guessing that something in SQL 2005 is blocking the service account that I am using for SharePoint. I will continue to search for a solution and will post back the results when I have some more info.





Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

Shola Salako

Thank you for sharing your experience, David.

I am also getting ready to test the KB article as well using some virtual servers in my lab. I will let you know what I come up with. Concerning the error you are getting, does your SPS service account have full access (including dbo permissions) to the SQL 2005 server databases It sounds like a permissions issue. I remember seeing something similar when I was installing a new test instance of SPS 2003 and forgot to grant the appropriate permissions on the SQL 2005 box for my SPS service account.

Thanks again,

Shola.






Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

David Goebel

Ok, I have a solution that worked. Follow these steps in order:

Portal Server Data Backup

  • Be sure to save a complete and full backup using SharePoint*s Backup and Restore interface or by using the run command (spsbackup.exe)

Backup SQL 2000 databases directly through SQL

  • Use Enterprise Manager in SQL 2000 (or Management Studio in SQL 2005) to individually backup the SharePoint databases.
  • Keep the filename the same and be sure to type .bak as the file extension when naming.

Restore SQL 2000 databases on new SQL 2005 box

  • Add the account you use for your application pools to the main Administrators group on the SQL 2005 box, this would be the account that you used when first installing SPS 2003 (domain level account). No special roles need to be given directly within SQL to this account.
  • Use SQL Server Management Studio to restore the SQL 2000 backup databases (SITE, PROF, SERV) to the new SQL 2005 box.
  • Be sure to check and/or edit the path when saving to the correct location on that particular SQL box. For example, in my environment I restored to ※D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\...name of database§

Perform only the steps listed below from KB article 894164

  • Delete the portal sites on the server farm - Important: As noted in the article, be sure that you do not click the checkbox to delete the databases
  • Remove e-mail server settings
  • Remove component assignments
  • Disconnect the server from the configuration database
  • Specify the content access account and the portal site application pool identity
  • Create the configuration database
  • Configure server farm account settings
  • Specify component assignments

Restore Portal site from SQL 2005 databases

  • Go to Manage Portal Sites from Central Administration
  • Click on the Restore Portal link
  • Type in the names of the databases (SITE, PROF, SERV)
  • Click Ok twice to restore

That's it!

What you end up with is databases in both locations, the old SQL 2000 and the new SQL 2005. You could then go into Enterprise Manager on the old SQL 2000 box and manually delete the old databases. My next task is to run a SharePoint backup to see if there are any issues now that the databases have been migrated and upgraded. I will post back the results.





Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

David Goebel

Well, scratch those instructions listed above as I encountered numerous problems with that solution. First of all, by restoring directly from the databases alone I lost some critical items 每 such as Alerts, search schedules, and Active Directory imports. I also encountered errors when attempting to run my first backup on the new SQL 2005 box. So this pretty much put me back to square one with the first task of resolving the missing Alerts and search schedules.

After numerous weeks of research and testing, it looks like I may have found the correct solution. I am now just verifying that it works correctly every time and I will be posting up the instructions soon.





Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

David Goebel

After many weeks of failed attempts, I have dialed in the correct process on how to migrate SharePoint 2003 databases from a separate SQL Server 2000 box to a new SQL Server 2005 box (not an in-place upgrade).

PORTAL BACKUP

  • Successfully save a complete and full backup using SharePoint*s Backup and Restore interface (this is a must).
  • Use SQL Server to manually backup each SharePoint database 每 config, PROF, SERV, SITE (just in case).

INSTALL NECESSARY UPGRADE ITEMS

MIGRATE THE DATABASES TO SQL 2005

SUMMARY

It turns out that KB article 894164 is correct, however when migrating from SQL 2000 to SQL 2005 you will need to install the necessary items listed above. It really isn*t that big of deal, however the problem is that Microsoft does not have this documented ANYWHERE on the net. At least I was never able to find it, nor was an actual member of the SharePoint team. Anyway, the process above works as I have tested it multiple times in my test lab. Next up for me is actually doing this on the production box. I will post up the results when the production migration is complete.

Until then, happy SharePointing Smile





Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

Anwar Ahmed Siddqiui

Hi ,

I have been successful in upgrading SPPS 2003, Windows 2003 32 Bit with SQL 2000 to SPPS 2007 on Windows 2003 32 bit and SQL 2000 through gradual upgrade process

However, I want to upgrade SPPS 2003 installed over Windows 2003 32 bit with SP1 and SQL 2000 To SPPS 2007 on Windows 2003 64 Bit and SQL 2005.

My question is Is this possible to

1. Install SPPS 2007 (on Windows 2003 64 bit with SQL 2005) and restore the database of SPPS 2003 (on Windows 2003 32 bit with SQL 2000) on it

2. Install SPPS 2003 on Windows 2003 64 bit with SQL 2005 and restore the database of SPPS 2003 (on Windows 2003 32 bit with SQL 2000) on it and then upgrade it to SPPS 2007.

Any tiger comments would really be helpfull for all those who wants to go for 64 bit please

Anwar Ahmed Siddiqui






Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

David Goebel

I have successfully completed the migration/upgrade on the production server using my instructions above. I encountered 0 errors! Smile





Re: SharePoint - Setup, Upgrade, Administration and Operation SharePoint Database Upgrade - SQL 2000 to 2005

TRACEYMS

Im about to do this im the DBA and we have another Sharepoint person. I will just bak and restore databases, some of our have full text catalogs - did you have any of these.

Anything else to consider from Database side of things