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.
- Open installation → go to planning → select upgrade advisor → wizard will be open → click next
- Enter server name and select the components to analyze
- Select instance name and provide credentials
- Select the database. If you have SSIS packages provide path of files
- Select run of files
- Select run to begin analysis
- Once analysis complete, warnings will be displayed
There are 2 types of upgradation
- Inplace upgrade
- Side by side 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.
- Fast, easy and automated process
- No additional hardware required
- Instance name will be same as earlier.
- Sql server will be in offline state during upgrade hence we have downtime.
- Roll back process is very complex
- 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 upgrade consists of installing sql server 2012 and moving old server databases to new server just like migration process.
- Upgrade process is online without affecting business
- Minimal down time required in redirecting the user requests from sql 2008 R2 to sql server 2012.
- Selected databases can migrate
- Full control on upgradation
- Roll back will be easy as original server still available
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
- Change compatibility level
- Check integrity of objects
- Set page verifications using CHECKSUM
- 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 of database using command DBCC CHECKDB(database name)
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.
The statistics need to update after upgrade to get full features of enhancement in sql 2012. Sp_ms foreach table ‘UPDATE STATISTICS’ ? with FULLSCAN
Below topics further included in this series
- SQL Server Database Administration Introduction
- SQL Server Architecture
- SQL Server Storage Architecture
- SQL Server Database Architecture
- SQL Server Transaction Log Architecture
- SQL Server installation
- SQL Server Databases
- SQL Server Backups
- SQL Server Restoration Tutorial
- SQL Server Security
- SQL Server Automation Tutorial
- SQL Server Migration
- SQL Server Upgradation
- SQL Server Log Shipping in SQL Server