Migrating a database is very critical and time bound process, if
anything goes wrong then it’s very difficult to rollback things and move back
to existing environment again. So it’s very important to prepare a checklist
before migrating a database and also keep a rollback plan ready in case of
migration failure.
At some point of time we have to migrate old version of database
server to new version of database server because of technology and feature
enhancements, business requirements changes or hardware up-gradation etc. We
have two options for database migration, either we can go for in-place upgrade
where new version of SQL Server is installed on the same machine where older
version of SQL Server exists, this is automated process similar to SQL Server
installation and second method is side by side migration where new version
of SQL Server is installed on new system and when new system is ready,
applications and other links and connectivity’s are pointed to it. It is a
manual process where you have to copy or move each and every object from old
server to new server manually.
Here I am going to list key points to keep in mind when doing side
by side database migration.
Pre-Migration
Checklist:
1. Run upgrade advisor on existing database to check the
compatibility and deprecated features with respect to database and applications
and make the required changes accordingly.
2. Note down data file and log file locations and size and make
sure new server has enough disk space available.
3. Note down database recovery model, collation type and
database facets.
4. Note down compatibility level, database owner, linked
server, full text catalogs and trustworthy settings details.
5. Always keep more than one copy of latest database backup if
it is feasible and you have enough storage available.
6. Note down information regarding database logins, users and
orphan users and permissions.
7. Take a copy of all SSIS packages and config files and note
down disk locations for files to move.
8. Generate scripts for all SQL Server agent jobs.
9. Note down all existing database maintenance plan and its
properties.
10. Generate all the SQL Server logins and keep it safe to
deploy on new server.
11. Note down windows logins and groups and permissions if any.
12. Check if any Disaster recovery or high availability settings
are available and make a note of that.
Once you are ready with pre-migration checklist, start performing
migration.
Migration
Checklist:
1. Make sure you stop all applications services connected with
database.
2. Set database to read only mode if required.
3. Take the latest backup of databases.
4. Restore latest copy of database on new server.
5. Check and change the database compatibility level after
restore.
6. Migrate all the user logins and windows logins to new
server.
7. Check the database properties and alter it accordingly if
required.
8. Enable trustworthy database setting if required or keep it
as default.
9. Verify the orphan users and fix the same.
10. Execute DBCC UPDATEUSAGE command to correct pages and row
counts on migrated database.
11. Execute DBCC CHECKDB on new migrated database to check the
integrity of the objects.
12. It is very important to rebuild all indexes on newly
migrated database else you will face performance degradation while running the
applications.
13. Make required changes at application pointer to database and
other connectivity settings.
14. Update statistics on migrated database tables.
15. Recompile all stored procedures, functions and triggers with
sp_recompile.
16. Refresh all the views available in the migrated database
with sp_refershview.
17. Deploy your high availability or disaster recovery plans if
any on new database.
18.
Now test the application and correct
the errors if any else celebrate success.
Queries used in Migration
Checklist.
– Take database backups.
– Restore database
backups.
– Verify compatibility
level and change the same if required.
--Verify Compatibility Level
SELECT name, compatibility_level, collation_name FROM sys.databases
GO
--Change Compatibility Level
USE [master]
GO
ALTER DATABASE [DB_Name] SET COMPATIBILITY_LEVEL = 110
GO
– Note down Linked
Servers details.
--Verify Linked Servers
SELECT * FROM sys.sysservers
GO
– Note down recovery
model details.
--Check Recovery Model
SELECT name, recovery_model_desc FROM sys.databases
WHERE name = 'DBName'
GO
– Note down collation
setting.
--Verify Collation setting
SELECT name, collation_name FROM sys.databases
WHERE name = 'DBName';
GO
– Migrate all user
logins and password.
– Verify database
properties and alter the same if required.
--Check Database Properties
SELECT *FROM sys.databases SD
JOIN sys.syslogins SL ON SD.owner_sid = SL.sid
GO
– Enable trustworthy
database settings if required.
--Verify Trustworthy Settings
SELECT is_trustworthy_on FROM sys.databases WHERE name = 'DBName'
GO
--Enable Trustworthy Database
Settings if required
ALTER DATABASE DBName SET TRUSTWORTHY ON
GO
– Verify and fix orphan
users.
– Correct all the pages
and row counts with DBCC UPDATEUSAGE command.
--Correct
Pages and Row Counts
DBCC UPDATEUSAGE('DBName') WITH COUNT_ROWS
GO
– Check integrity of
objects with DBCC CHECKDB command.
--Check Integrity of Objects
DBCC CHECKDB('DBName') WITH ALL_ERRORMSGS
GO
– Rebuild all indexes by
creating maintenance plan or by manual query.
--Script for Index Rebuild
USE DBName
GO
ALTER INDEX ALL ON dbo.TableName REBUILD
GO
– Update database table’s
statistics.
--Update Database Statistics
USE DBName
EXEC sp_updatestats
GO
– Recompile all stored
procedures, functions and triggers.
--Recompile Objects
USE DBName
EXEC sp_recompile 'ObjectName'
GO
– Refresh all the views
of database.
--Refresh Views
USE DBName
EXEC sp_refreshview 'ViewName'
GO
