Introduction
The purpose of this tutorial is to lay out the general guidelines for planning a SQL Server 2005 upgrade. As the new version of SQL Server is coming, all the companies want to upgrade the older version of SQL Server to the newer version. Here the DBA role has again come into the picture for doing the same.
Why do we require to Upgrade
The newer version contains additional features & improvements in the newer product. Below are the few reasons for upgrading SQL server:-
1) High Availability solutions like Database Mirroring
2) Managing VLDB (Very large Databases) easily & efficiently
3) Stronger Security
4) BIDS (Business Intelligence Development Studio)
For more information, please refer the below link @Microsoft, "Why to upgrade to SQL Server 2005"
SQL Server Upgrade menthods
In SQL server we are provided with two methods for SQL upgradtion:-
1) In-Place Upgrade
2) Side-by-side Upgrade
In this blog I will cover the In-Place upgrade method. Second option will be covered in the next blog very soon.
In-Place Upgrade
This option is the easiest way of upgrading the SQL Server to the newer version. If everything goes well its good, otherwise its very time consuming approach to roll back the changes back to the older vrsion.This is the automatic process in which you replace the older version of SQL server instance to the newer version, called, In-Place upgrade.
Below are the few characteristics of In-Place upgrade
Number of instances | One only |
Number of servers involved | One |
Data transfer | Automatic |
Server instance configuration | Automatic |
Helping utility | SQL Server Setup |
Secondly, remember you cannot upgrade “CROSS-BIT” upgrade like, upgrading SQL server 32 bit instance to SQL server 64 bit instance or vice versa. This can be only possible with Side-by-Side upgrade method.
Below diagram shows how the SQL Instance is replaced with the newer version of SQL Server.
Advantages of In-place Upgrade:-
1) Its speedy
2) It’s easy to perform
3) It’s a automated process
4) Less downtime required
5) No additional hardware required
Disadvantages of In-Place Upgrade
1) Having to upgrade the whole instance
2) Not able to upgrade the single database
3) Disk space issues during upgrade process
4) Check the instance for Backward compatibility
5) Rollback is complex in case of failure
Pre-requisite for In-Place upgrade
1) Download /Install .Net Framework 2.0 or higher
2) Download/Install Windows Installer 3.1 Service.
3) A remote Registry service is required during up gradation. So, try to start this service & see if it started successfully. If it doesn’t check its dependencies & see if RPC is running or not. Without this you are not able to install the Service Packs on the upgraded SQL server Instance because it will show you the error that upgradtion has not completed successfully.
4) Backup the System databases
5) Run DBCC Checkdb to ensure database are clean
How to do in place upgrade:-
Run the setup from the SQL 2005 & pass on all the dialog box until the below one
In the above window, you need to select the instance to upgrade; in our case we will take the default instance. Now click on the Installed Instances button
Here you will have to check the Check box to upgrade the installed instance on the SQL server. For coming windows after the above window, select the default values.
Post Upgradtion Tasks
1) Change the Compatibility Modal of Databases to 90
2) Executed DBCC UPDATEUSAGE on destination databases to update usage counters to ensure that correct values exists for Table and index row counts.
3) Executed DBCC CHECKDB WITH DATA_PURITY on destination databases to check the databases for column values that are not valid or are out of range.
4) Take the fresh Full backups
Hope this topic helps the DAB to perform the in place up gradation smoothly & efficiently.
