13. SQL Server Upgradation

What is SQL Server UPGRADATION?

Upgrading means, migrating sql server old version to sql server 2012.

Before upgradation we need to run upgrade advisor tool.

Microsoft sql server 2012 upgrade advisor

Under installation menu  → in planning → click upgrade advisor.

SQL server 2012 upgrade advisor analyzes sql server 2008 R2 (or) other lower version sql server instances and gives results that may impact upgrade process.

Steps in installing upgrade advisor.

1. Open installation  → go to planning → select upgrade advisor → wizard will be open  → click next
2. Enter server name and select the components to analyze
3. Select instance name and provide credentials
4. Select the database. If you have SSIS packages provide path of files
5. Select run of files
6. Select run to begin analysis
7. Once analysis complete, warnings will be displayed

There are 2 types of upgradation

1. Inplace upgrade
2. Side by side upgrade

Inplace Upgrade:

Source server will become destination server after upgrading. It is going to install sql server 2012 components on old version. All the lower version support files will be converted to sql server 2012 supportable. This type of upgradation in Inplace upgrade.

Advantages:

1. Fast, easy and automated process
2. No additional hardware required
3. Instance name will be same as earlier.

Disadvantages:

1. Sql server will be in offline state during upgrade hence we have downtime.
2. Roll back process is very complex
3. Source server may be affected

Inplace upgrade process from Installation →from installation  → select upgrade from sql server 2008 R2 and continue remaining installation process.

Side-by-Side

Side by side upgrade consists of installing sql server 2012 and moving old server databases to new server just like migration process.

Advantages:

1. Upgrade process is online without affecting business
2. Minimal down time required in redirecting the user requests from sql 2008 R2 to sql server 2012.
3. Selected databases can migrate
4. Full control on upgradation
5. Roll back will be easy as original server still available

Disadvantages:

1.manual process

Side -by-side process

2. Follow the same process which performed in migration like script out logins, jobs and take backup and keep.

3. Install sql server 2012

4. Copy the database backups and restore

5. Create logins and jobs by running scripts

6. Find and fix orphaned users.

7. Verify database connectivity and functionality.

Post upgrade checks

1. Change compatibility level
2. Check integrity of objects
3. Set page verifications using CHECKSUM
4. Update statistics

Change the compatibility level of database once upgradation complete successful using command alter database sql test 2012 set compatibility level=110

Check integrity:

Check integrity of database using command DBCC CHECKDB(database name)

Page verification:

CHECKSUM verifies computation of page and results are stores in page header. Using command alter db dbname set PAGE_VERIFY CHECK SUM with NO_WAIT.

Updating statistics:

The statistics need to update after upgrade to get full features of enhancement in sql 2012. Sp_ms foreach table ‘UPDATE STATISTICS’ ? with FULLSCAN