SQL SERVER Upgrade: SQL2012 features

Time to upgrade your old SQL server to SQl2012:

Why we consider the SQL Server to upgrade from old version?

SQL2012 consists of important features that are not available in the old versions.

Nice features of SQL Server 2012.

1. Contained databases help to avoid the database user’s creation in the destination server after migration.

2. Server Core support significantly reduces the patching.

3. Security-User defined server role helps to create custom defined server role in SQL 2012 server.

4. Column store index assists in storing of the data as like column instead of old row level storage to improve the data access performance.

5. Tabular model mode to help personal BI users to achieve their work in excel to SSAS.

6. Data Quality Service to help BI users to implement the standards and quality of data in SSAS.

7. Power View helps end users to drag and drop the required field for analysis and report purposes in BI.

8. AlwaysOn Availability Groups helps to failover the databases as a single entity, unlike database mirroring where you can only do one database at a time.

9. Replaced processors license with Core License model.

10. Pagination is introduced in order to show the data in terms of number of record count by OFFSET and FETCH keywords.

SQl2000 to SQL2008R2 Database Migration Scripts steps

Steps required for Database migration from SQL2000 to SQL2008R2.

Follow the below steps to migrate the database from older version (SQL2000) to another version SQL2008R2.

Backup the production database required and keeps it for restore into a new version SQL server.

1. Backup live Database

BACKUP DATABASE kumarDB TO DISK = 'Z: \SQLServerBackups\kumarDB.bak' GO

 2. Restore live Database to new server

----Restore Database RESTORE DATABASE kumarDB FROM DISK = 'D:\kumarDB.bak' WITH MOVE 'YourMDFLogicalName' TO 'D:YourMDFFile.mdf', MOVE 'YourLDFLogicalName' TO 'D:YourLDFFile.ldf'

3. Generate the scripts required for changing the column collation. (COL 1 Generate_change_column_collation); Keep the scripts for future use.

declare @toCollation sysname SET @toCollation = 'Latin1_General_CI_AS' -- Database default collate SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)' WHEN DATA_TYPE in ('text','ntext') then '' WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' ) ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END +' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' WHEN 'No' THEN 'NOT NULL' END FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext') AND TABLE_TYPE = 'BASE TABLE' and COLLATION_NAME <> @toCollation

4. This scripts required to create generation of primary key constraints scripts (COL 2 Generate_primary_key_contraints); Keep the scripts for future use.

BEGIN TRAN -- Get all existing primary keys DECLARE cPK CURSOR FOR SELECT so.name,si.name,si.type_desc from sys.indexes si join sys.objects so on si.object_id = so.object_id and so.type = 'U' where si.type_desc <> 'HEAP' and si.is_Primary_Key = 1 ORDER BY so.Name DECLARE @PkTable SYSNAME DECLARE @PkName SYSNAME Declare @KeyType nvarchar(50) -- Loop through all the primary keys OPEN cPK FETCH NEXT FROM cPK INTO @PkTable, @PkName,@KeyType WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = '' SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY ' + @KeyType + ' (' -- Get all columns for the current primary key DECLARE cPKColumn CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName ORDER BY ORDINAL_POSITION OPEN cPKColumn DECLARE @PkColumn SYSNAME DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1 -- Loop through all columns and append the sql statement FETCH NEXT FROM cPKColumn INTO @PkColumn WHILE (@@FETCH_STATUS = 0) BEGIN IF (@PkFirstColumn = 1) SET @PkFirstColumn = 0 ELSE SET @PKSQL = @PKSQL + ', ' SET @PKSQL = @PKSQL + @PkColumn FETCH NEXT FROM cPKColumn INTO @PkColumn END CLOSE cPKColumn DEALLOCATE cPKColumn SET @PKSQL = @PKSQL + ')' -- Print the primary key statement PRINT @PKSQL FETCH NEXT FROM cPK INTO @PkTable, @PkName, @KeyType END CLOSE cPK DEALLOCATE cPK ROLLBACK

5. This script is required to generate scripts for alternate key indexes. (COL 3 Generate_alternate_key_indexes); Keep the scripts for future use.

BEGIN TRAN -- Get all existing primary keys DECLARE cPK CURSOR FOR select si.Object_id,si.Index_Id,so.name,si.name,si.type_desc,si.is_unique from sys.indexes si join sys.objects so on so.object_id = si.object_id and so.type = 'U' and si.is_Primary_key = 0 and si.type_desc <> 'HEAP' order by so.name Declare @ObjectID int Declare @IndexID int Declare @TableName nvarchar(50) Declare @IndexName nvarchar(50) declare @IndexType nvarchar(50) declare @IndexUnique bit -- Loop through all the primary keys OPEN cPK FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = '' Declare @KeyUnique nvarchar(10) set @KeyUnique = '' if @IndexUnique = 1 set @KeyUnique = 'Unique' SET @PKSQL = 'Create ' + @KeyUnique + ' ' + @IndexType + ' INDEX ' + @IndexName + ' ON ' + @TableName + ' (' -- Get all columns for the current key DECLARE cPKColumn CURSOR FOR select sc.name from sys.index_Columns sic join sys.columns sc on sc.object_id = sic.object_id and sc.column_id = sic.column_id where sic.object_id = @ObjectID and sic.Index_id = @IndexID OPEN cPKColumn DECLARE @PkColumn SYSNAME DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1 -- Loop through all columns and append the sql statement FETCH NEXT FROM cPKColumn INTO @PkColumn WHILE (@@FETCH_STATUS = 0) BEGIN IF (@PkFirstColumn = 1) SET @PkFirstColumn = 0 ELSE Begin SET @PKSQL = @PKSQL + ', ' end SET @PKSQL = @PKSQL + @PkColumn FETCH NEXT FROM cPKColumn INTO @PkColumn END CLOSE cPKColumn DEALLOCATE cPKColumn SET @PKSQL = @PKSQL + ')' -- Print the primary key statement PRINT @PKSQL FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique END CLOSE cPK DEALLOCATE cPK ROLLBACK

6. This script is required to generate the scripts for foreign key constraints. (COL 4 Generate_foreign_key_contraints); Keep the scripts for future use.

BEGIN TRAN -- Get all existing primary keys DECLARE cPK CURSOR FOR select sf.object_id,sf.name,so.name,sor.name from sys.foreign_keys sf join sys.objects so on so.object_id = sf.parent_object_id join sys.objects sor on sor.object_id = sf.referenced_object_id ORDER BY sf.Name DECLARE @PkTable SYSNAME DECLARE @PkName SYSNAME Declare @RefName nvarchar(50) declare @objectid bigint -- Loop through all the primary keys OPEN cPK FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = '' Declare @FKSQL Nvarchar(4000) set @fkSQL = '' SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' WITH NOCHECK ADD CONSTRAINT ' + @PkName + ' Foreign KEY ' + ' (' Set @FKSQL = ' REFERENCES ' + @RefName + ' (' -- Get all columns for the current primary key DECLARE cPKColumn CURSOR FOR select so.name,sor.name from sys.foreign_key_columns sfc join sys.columns so on so.column_id = sfc.parent_column_id and so.object_Id = sfc.parent_object_id join sys.columns sor on sor.column_id = sfc.referenced_column_id and sor.object_id = sfc.referenced_object_id where sfc.Constraint_object_id = @ObjectID OPEN cPKColumn DECLARE @PkColumn SYSNAME Declare @fkColumn sysname DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1 -- Loop through all columns and append the sql statement FETCH NEXT FROM cPKColumn INTO @PkColumn,@fkColumn WHILE (@@FETCH_STATUS = 0) BEGIN IF (@PkFirstColumn = 1) SET @PkFirstColumn = 0 ELSE Begin SET @PKSQL = @PKSQL + ', ' set @FkSQL = @FKSQL + ', ' end SET @PKSQL = @PKSQL + @PkColumn set @FkSql = @FKSQL + @FKColumn FETCH NEXT FROM cPKColumn INTO @PkColumn,@FKColumn END CLOSE cPKColumn DEALLOCATE cPKColumn SET @PKSQL = @PKSQL + ')' set @FKSql = @FKSQL + ')' -- Print the primary key statement PRINT @PKSQL Print @FKSQL FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName END CLOSE cPK DEALLOCATE cPK ROLLBACK

7. This script is required to generate the scripts for check constraints. (COL 5 Generate_check_contraints); Keep the scripts for future use.

select 'Alter Table ' + st.name + ' With Nocheck ' + 'Add Constraint ' + scc.name + ' check ' + scc.definition from sys.tables st join sys.check_constraints scc on st.object_id = scc.parent_object_id order by st.name

  8. This script is required to drop the check constaints. (COL 6 drop_check_contraints)

declare ca Cursor for select st.name,scc.name from sys.tables st join sys.check_constraints scc on st.object_id = scc.parent_object_id order by st.name declare @TableName nvarchar(50) declare @ConstraintName nvarchar(50) declare @DbName nvarchar(50) Declare @Sql nvarchar(4000) set @dbName = db_name() open ca fetch from ca into @TableName,@ConstraintName While @@Fetch_Status = 0 Begin set @SQL = 'use ' + db_name() +' Alter Table ' + @TableName + ' Drop Constraint ' + @ConstraintName + ';' print @sql exec (@Sql) fetch from ca into @TableName,@ConstraintName end close ca deallocate ca

             

9. This script is required to drop the foreign key constraints (COL 7 drop_foreign_key_contraints)

-- Get all existing Foreign keys DECLARE cPK CURSOR FOR select sf.object_id,sf.name,so.name,sor.name from sys.foreign_keys sf join sys.objects so on so.object_id = sf.parent_object_id join sys.objects sor on sor.object_id = sf.referenced_object_id ORDER BY sf.Name DECLARE @PkTable SYSNAME DECLARE @PkName SYSNAME Declare @RefName nvarchar(50) declare @objectid bigint -- Loop through all the primary keys OPEN cPK FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = '' Declare @FKSQL Nvarchar(4000) set @fkSQL = '' SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' Drop CONSTRAINT ' + @PkName -- Print the Drop key statement PRINT @PKSQL Exec(@pksql) FETCH NEXT FROM cPK INTO @objectid,@PkName,@PkTable, @refName END CLOSE cPK DEALLOCATE cPK

   

10. This script is required to drop the alternate key indexes. (COL 8 drop_alternate_key_indexes)

-- Get all existing Alternate keys DECLARE cPK CURSOR FOR select si.Object_id,si.Index_Id,so.name,si.name,si.type_desc,si.is_unique from sys.indexes si join sys.objects so on so.object_id = si.object_id and so.type = 'U' and si.is_Primary_key = 0 and si.type_desc <> 'HEAP' order by so.name Declare @ObjectID int Declare @IndexID int Declare @TableName nvarchar(50) Declare @IndexName nvarchar(50) declare @IndexType nvarchar(50) declare @IndexUnique bit -- Loop through all the primary keys OPEN cPK FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = '' Declare @KeyUnique nvarchar(10) set @KeyUnique = '' if @IndexUnique = 1 set @KeyUnique = 'Unique' SET @PKSQL = 'DROP INDEX ' + @IndexName + ' ON ' + @TableName -- Print the Alternate key statement PRINT @PKSQL exec (@pksql) FETCH NEXT FROM cPK INTO @ObjectID,@IndexId,@TableName,@IndexName,@indexType,@IndexUnique END CLOSE cPK DEALLOCATE cPK

    

    

11. This script is required to drop the primary key constraints. (COL 9 drop_primary_key_contraints)

-- Get all existing primary keys DECLARE cPK CURSOR FOR SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where Constraint_Type = 'Primary Key' ORDER BY TABLE_NAME DECLARE @PkTable SYSNAME DECLARE @PkName SYSNAME -- Loop through all the primary keys OPEN cPK FETCH NEXT FROM cPK INTO @PkTable, @PkName WHILE (@@FETCH_STATUS = 0) BEGIN DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = '' SET @PKSQL = 'use ' + db_name() + ' ALTER TABLE ' + @PkTable + ' drop CONSTRAINT ' + @PkName print @PKSQL exec(@PKSQL) FETCH NEXT FROM cPK INTO @PkTable, @PkName END CLOSE cPK DEALLOCATE cPK

             

12. This script is required to change the collation of the database. (COL 10 Change collation of database)

USE master; GO ALTER DATABASE KumarDB COLLATE SQL_Latin1_General_CP1_CI_AS ; GO

13. Get the scripts from Step 3 to change the column collation. (COL11_Change_Column_Collation)

14. Get the scripts from Step 4 to create the dropped primary keys. (COL12_Create_PK)

15. Get the scripts from Step 5 to create the dropped alternate keys. (COL13_Create_AK)

16. Get the scripts from Step 6 to create the dropped foreign keys. (COL_14_Create_FK)

17. Get the scripts from Step 7 to create the dropped check constraints. (COL_15_Create_CK)

18. Set the compatibility level of the database to new server compatibility level. (PCol1_set_compatibility_level)

ALTER DATABASE KumarDB SET COMPATIBILITY_LEVEL = 100; GO

19. Run the Database consistency check against a database. (PCol2_dbcc_dbcheck_with_data_purity)

DBCC CHECKDB ('KumarDB') WITH DATA_PURITY, NO_INFOMSGS

20. Run the Database consistency check against a database. It reports and corrects pages and row count inaccuracies in the catalog views (PCol3_dbcc_update_usage)

USE KumarDB; GO DBCC UPDATEUSAGE (KumarDB) WITH NO_INFOMSGS; GO

21. Run the Database consistency check against a database. It updates statistics for all user-defined and internal tables in the database (Pcol4_update_statistics)

EXEC sp_updatestats;

22. If anything goes wrong, Drop the database and Re run the above steps.        

   RE RUN PROCESS DROP DATABASE

23. Finally consider the creation of application role, service account and add to role, grant permissions for application role and drop the old users or unnecessary users.

Have a happy and successful database migration implementation.