Monday 20 February 2012

How to Upgrade SQL Server 2000 Instance to SQL Server 2005


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


The main drawback of using this option is that you cannot upgrade the single database to the newer version. In that case your need to adopt another option called, Side-by-Side upgrade.

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.

Thursday 2 February 2012

Recovery Modals in SQL Server

Being a SQL Server DBA, one should be able to understand how many recovery modals do we have in SQL Server.While determining the Backup starategies for you databases , we should give a deep dive into the recovery modals of the databases as they define the types of backups that we can perform with the databases.

There are three types of Recovery Models in SQL Server:-

1) Full Recovery Model
2) Bulk-logged Recovery Model
3) Simple Recovery Model

Full Recovery Model:-

In this Model, we can take FULL, Differential & Log backups . Every transaction in is logged in the databases log file.

Bulk-logged Recovery Model

In this Model, we can take FULL, Differential & Log backups. The main difference between Full & Bulk logged is that all the Bulk Load operaions are minimally logged into the log file of the databases. In other words , we can say that only the allocation & deallocation of pages that aee affected with the Bulk operations are logged into the log file.

Simple Recovery Model

Under this recovery model, we are only capable of taking Full & Differential backups of the databases. Log backup is not possible in this recovery model.


How to change the Recovery Models in SQL Server

1) Go to the database properties
2) Go to the option tab
3) Select the recovery modal from the drop down box

Sanjee-SQLMaster

Dear All,

very good maorning to all of you.