We carried out another upgrade to SQL 2005 over the weekend. It was an in-place upgrade from SQL 2000 to SQL 2005, and the fascinating thing was - at the end of it all none of the end users knew that anything had changed. Imagine spending two days working hard on something and at the end of it all being very happy that none of your users noticed that anything had changed!
The world I dream of is a world where after a software upgrade, there are no users complaining about how that menu item changed ever so slightly, and when things work the way they are supposed to work! Maybe some lovely users who discover new features and talk about how much they love them. :-)
I think we get pretty close to that ideal world during a SQL server upgrade and that's why I love it so much!
Here are my notes, to try and ensure this dream upgrade goes along fine -
There are generally two scenarios -
1. You are carrying out an in-place upgrade on the same server
2. You are migrating to a new server.
I will cover Scenario 1 in this post.
- Check if your version is supported for Upgrade from http://msdn2.microsoft.com/en-us/library/ms143393.aspx
- Run SQL Server 2005 Pre-Upgrade Advisor. If you see any errors, try and resolve them - or contact Microsoft.
- If possible, carry out a test upgrade in virtual test environment.
- Back up the following in case you have to roll back -
- Dynamics DB,
- Company Databases,
- SQL Logins(Use the capture logins Script to do this),
- SQL Jobs,
- SQL Operators,
- DB Maintenance plans
- SQL Server Configuration
- Run the SQL Server installer and choose Upgrade. This works fine in 90% of the cases, if you have run the upgrade advisor first. It didnt work for us in one case during the test upgrade and we were able to figure out the problem being related to a warning which we ignored in the upgrade advisor.
- Change Compatibility Level of Server to 90
- In case of a problem with the Upgrade, use your backups and do the following. These are the same steps which you would carry out while migrating SQL Server
- Uninstall SQL Server 2000
- Install SQL Server 2005
- Restore Dynamics and Company Databases
- Run DEX_Req script to create the DEX_Lock and DEX_Session in tempdb
- Use Grant sql script to grant permission to all users in the DYNGRP DB Role
- Make DYNSA the dbowner of all databases
Some of the steps above might be extensive - but I recommend you carry them all, if you aim to carry out a stress free upgrade. The last thing you want is end users getting held up and then telling you "what was that upgrade all about - nothing even changed"(Yeah, the dreams I have go along pretty well with the nightmares)!
Good Luck!
No comments:
Post a Comment