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.
There are 2 types of upgradation
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.
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.
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 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