SQL Database Migration Key Points

Migration related:

·         Make sure application will be supported with the upgraded or migrated version of SQL server

·         Run upgrade advisor to make sure there is no issues  migrating or upgrade.

·         Verify with the application team that the service pack is also compatible with the application

·         Take cutover full backup for all databases along with the system database.

·         Take scripts of replication, logins, agent job, linked server, Triggers, SQL operators, mail profiles, alerts, maintenance plans etc

·         Make sure enough space is available in target server to accommodate backup file and data file.

Methods of migration:

·         Backup Restore

·         Detach attach

·         Manual schema build and data import / Export

·         Transaction log shipping for vldbs

·         Copy Database wizard

Be careful always start migration with DEV QA and finally with prod making sure that there is no issue.

Migration from SQL Server 2008 to SQL Server 2014 Checklist

Source SQL ServerChecklist

Action Item	Yes/No, N/A
1.     Identify databases you would like to migrate
2.     Backup all user databases
3.     Script out all the existing login
4.     Script out all the server Roles if applicable
5.     Script out all the Audit and Audit Specifications if applicable
6.     Script out backup devices if Applicable
7.     Script out server level triggers if applicable
8.     Script out Replication along with Configuration if Applicable
9.     Script out Mirroring if Applicable
10.            Script out Data Collection if applicable
11.            Script out Resource Governor’s objects if applicable
12.            Script out Linked Server if Applicable
13.            Script out Log shipping if Applicable
14.            Script  out all SQL Server Agent Jobs
15.            Script out all DB Mail objects such as Profile and its settings
16.            Script out All Proxy accounts and credentials if Applicable
17.            Script out all Operators if Applicable
18.            Script out all alerts if applicable
19.            Save SQL Server, Server configuration in a file
20.            Data Encryption Keys

Destination SQL Server Checklist

Action Item	Yes/No, N/A
1.     Required SQL Server is installed
2.     DBA SQL Server Check list is completed
3.     Enough Space for storing Backup and source scripts
4.     Applications compatibility is signed off

In place VS Side by Side Migration :

Action Item	In Place Upgrade	Parallel or side by side upgrade
Upgrade Efforts	·         Minimum ·         Upgrade is Automated ·         No data migration needed	·         Efforts needs to be planned ·         Manual migration ·         Data migration required
Downtime	·         Requires downtime	·         Minimum or no downtime required ·         Production will be online while migration is taking place
Configuration Changes	·         Configuration changes are Automated by the system ·         Application configuration is not needed since it would use same connection information	·         All configurations needs to be preserved and migrated over to new SQL Server instance ·         Applications needs to change configuration to connect to new SQL Server instance ·         Requires manual check of all configurations (system and user)
Data Transfer	·         No Data Transfer required ·         All Server integration changes remains intact and gets upgraded by the system (Replication, Mirroring, Linked Server and SQL Server Agent Jobs etc.	·         Data Transfer required and can be planned in staging manner ·         All server integrations needs to be created over to new SQL Server Instance
Server Name and IP	·         No changes in Server and SQL server instance ·         Application can continue using same server Name and IP address	·         Server and IP address will change ·         All application needs to change their connection to connect to new server with new IP address
Testing	·         Testing is relatively faster as same testing script and be used once upgrade is completed	·         Thorough testing required as there are chances to a configuration mismatch ·         Testing needs to be planned and new testing script required since Server Instance has changed for testing script
Rollback	·         Rollback usually is rebuilding the whole system ·         Requires backup of all configuration, databases including system databases	·         Rollback is very easy as production system remains online during migration, if testing fails, it can be remediated with no downtime required and all application can connect to previous Online production system
SQL Versioning and Architecture	·         Versioning upgrade would be possible with same architecture (32 bit to 32 bit), 32bit to 64 bit is not supported	·         Since it would be new install, any version with any architecture can be installed and tested