Thursday, March 5, 2015

Migration of SQL Server



                                                                      Migration

Pre-migration check list:

1. Take backup of all databases including system databases
2. Analyze the disk space of the target server for the new database.
3. Confirm the data and log file location for the target server.
4. Collect the information of database logins, users and their permissions. (Optional)
5. Check the database for the Orphan users. (EXEC sp_change_users_login @action='report')
6. sp_change_users_login 'Auto_Fix', userNAME, NULL
7. Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers)


Procedure of migration and post migration check list.

1. Make a copy of the data and log file using detach.
2. Attach to target server.
3. Change the compatibility level.
4. Run DBCC upgrade usage DBCC Updateusage (“DB”) with count_rows.( Reports and corrects            pages and row count inaccuracies in the catalog views.)
5. Run DBCC CHECKDB DBCC CHECKDB (DB) with ALL_ERRORMSGS.
6. Update the statistics using sp_updatestats.
7. Change the page verify from Torn_Page_Detection to Check_Sum.
8. Run DBCC CHECKDB with all_errormsgs

No comments: