Microsoft has planned to come up with new version of SQL Server on every 3-5 years time frame. The current release of SQL Server is SQL Server 2012. Upgrading a SQL Server is critical activity which a DBA has perform to continue to get Microsoft support.
This weekend, I was performing a SQL Server upgrade from SQL Server 2000 to SQL Server 2005, still pretty old but the customer application is not being tested on SQL Server 2008 / R2 so that the only version which is achievable.
There are multiple ways to upgrade SQL Server
- Side by Side upgrade
- In Place upgrade
SIDE by SIDE upgrade is always an better option for DBAs, this option will leave the current instance as it is and we install a new SQL Server instance as a named instance and perform a backup and restore method to transfer databases from old version to newer version of SQL Server. In this solution, no down time is required as both instances can in parallel.
In Place upgrade, in this option we upgrade binaries from existing installation to a newer version of SQL Server. This method requires downtime and may fail in betwwen.
In my case, I did a In place upgrade because of following two limitations.
- Limitation of Storage / resources - The server which I am gonna upgrade is lower end and doesn’t’ have enough storage to run two copied of databases.
- Application Limitation – Application SME is not available to modify connection string from default instance to Named instance.
This is a development server upgrade and production environment will be upgrade once every application level functionality is being verified by business on the DEV environment.
Step by Step Guide to Upgrade SQL Server In place Upgrade from SQL Server 2000 to SQL Server 2005 Enterprise Edition.
PRE Upgrade TASK
Prior to actual upgrade, please ensure that your database / database objects / application are compatible for newer version of SQL Server. Every version of SQL Server add some new features and deprecate some old / legacy features. So prior to upgrade, please check and confirm that you are not using any feature of SQL Server / TSQL Code which is deprecate in newer version of SQL Server.
Upgrade Advisor checks for any potential compatibility issues, deprecated functionalities, and feature and behavior changes, and provides you with a recommended list of tasks that need to be done before and after the upgrade.
This is tuff task, thus Microsoft has come up with a solution named “Upgrade Advisor” , which check and verifies each and every piece of your database / database objects will work in next version f SQL Server. If there is any deprecate feature, which you are currently using, SQL Server Upgrade Advisor will produce a report for the same.
If any feature / Code is being reported by Upgrade Advisor as warning / Error / deprecate feature, please work with developer to take special consideration.
To run Upgrade Advisor for SQL Server, you must have this application installed on your system. If you don’t have this Upgrade Advisor Installed, No worries, this is a free application from Microsoft. you can find Upgrade Advisor at Microsoft Site or it’s also available in SQL Server installation media / DVD.
Install Upgrade Advisor. Start the Upgrade Advisor from SQL server installation media
Enter Registration information and finally select upgrade advisor to install.
Once the Upgrade Advisor is installed, run that for all user databases and system databases and check the report.
This may take hours to run depending on size of the database. In my case this took approx 2 1/2 hours to complete.
Then verify that you are ready to upgrade and all your database objects will work on newer version of SQL Server.
SQL Server Upgrade Actually
Prior to start SQL Server Installation / upgrade. Please verify that you have met all pre requisite for SQL Server 2005 installation like Windows Installer Services. In case of SQL Server 2008, .Net framework is also required.
Ensure that you have Windows Installer Services Installed on your Windows System. If you don’t have Windows Installed, then you can install Windows Installer 4.5 from Microsoft. In my case I need to install This on Windows 32 bit so I downloaded and installed WindowsServer2003-KB942288-v4-x86.exe
Once this is being Done, Restart system and begin with Windows 2005 Setup installer by double clicking SETUP.EXE in installation media file.
Enter the product key and License aggreement
Select the component which you want to install
Click on Installed Instances and select the instance which you want to upgrade
Once the Instance in identified, which you want to upgrade, the setup will show you current version and once you click on Details, it will show you available options during this setup session.
Select the mode for authentication mechanism. This credentials will be used by SQL Server connection to old instance, which we are upgrading.
OPEN SQL Server Management studio and verify SQL Server version
Prior to send this good news to business, please validate SQL Server installation log files to verify there is no issue with any component of sql server. Please check my previous article to check installation logs (BOOTSTRAP files).
Now apply Updated Service pack to keep software upto date. so now in my case, I need to install and apply SQL service pack 4, which is latest available for this version of SQL Server 2005.