2

We are preparing to upgrade our database server from SQL Server 2000 to 2005. I'm planning on doing an in-place upgrade to maintain accounts, privileges, etc., and I'm hoping it will be pretty straightforward. This server hosts databases for several of our applications, but the main one that I'm concerned about is SharePoint (MOSS 2007), since that involves eight databases and seems to be pretty finicky about the connections between the front-end server and the DBs.

Does anyone have practical experience with this scenario? Is this as simple as it should be? Are there any tricks or tips to enlighten me?

Thanks for your help.

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
Donald Gower
  • 43
  • 4
  • What is your rollback plan? – PowerApp101 Jul 13 '09 at 15:04
  • If we do an in-place upgrade, our rollback plan would be to restore the servers from backup (BackupExec and DB backups). Do you think it would be better to do a migration instead? I'd rather go that route, but I'm a little worried that SharePoint would get out of synch because that's a little more of an involved process. – Donald Gower Jul 13 '09 at 15:31
  • IMHO copying mdf/ldf files to another location (after shutting down SQL Server) is a lot better then buggering around trying to restore master if you need to fallback to SQL Server 2000 – Nick Kavadias Aug 10 '09 at 16:13

3 Answers3

0

I've done a migration, not an upgrade. Here is a thread on StackOverflow.com which worked well for me.

Community
  • 1
andyhky
  • 2,732
  • 2
  • 25
  • 26
0

Presumably you will be going 32-bit in SQL 2005. Migration is certainly safer than upgrade in place because you have a life boat to go back to.

For moving logins, especially SQL logins and their respective passwords, don't overlook the sp_help_revlogin stored procedure that MS introduced with SQL 2005. I use sp_help_revlogin to keep a current script of SQL logins and also routinely script out database permissions for disaster recover purposes.

jl.
  • 1,076
  • 8
  • 10
0

Having read-only file groups is a gotcha from personal experience.

Make sure you run the SQL Server 2005 upgrade adviser on the production server your upgrading, it will catch 90% of problems you might run into & advise you on what you need to do to fix them.

Have a contingency / roll back plan.

Remember to backup all your data & log files, including system databases after you have taken the server offline and before you start the upgrade. Trying to re-attach an upgraded 2005 database to SQL Server 2000 won't work & you'll be in a world of pain.

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47