There are many ways to migrate the databases, no matter what database version of SQL Server that we are using, below options are the solutions we can use to upgrade and migrate SQL Server instances:
In-place Upgrade: There will be a down time for the application, since this is completely an automated process and will be helpful only in case of upgrade to be perform on the same server or if we are planning to have databases in the same hardware and a must to have the same configuration and Same instance name of the SQL Server, this is completely managed by the Upgrade wizard from Microsoft upgrades for the SQL Server databases and its components. This type of upgrade is mostly used for non-prod instances, if the installation of upgrade is failed then the roll back time is very high.
Backup Restore method: Here the down time for the application is minimal, where we will be backing up the databases and restoring it to new environment in same location on the physical Windows drives as the existing ones, along with the certificates and also creating a database master key in the master database or the database itself is based on the encryption level at the source, then we would have a log shipping or database mirroring enabled from the existing to new environment, during the cutover phase, we will fail it over the new environment and bring down the existing environment, this is the best practice followed across the industry. Otherwise we will be performing a full backup and restore during the cutover phase for most of the databases, if the size of the database is large, then we will take the differential backup, tail log backup from the source server and restore them to destination server and bring the database to ONLINE state, during the cut over phase. The main disadvantage with this method is that, it needs a lot of manual intervention and the time for preparation work is more than the actual execution phase.
SSIS package: This takes a lot of time for the migration and involves a lot of effort. Create an SSIS package will do copy the data from the existing current environment to a target environment, and then it creates a copy to the new database, to compatible with application. This is a time consuming process as it include writing SSIS package to new database, and then using larger database.
Best practice would be to backup and restore the database from one server to another Server for upgrading the databases, but not all servers can be use this approach. Below are the recommendations to migrate the databases:
Build the Windows Server for SQL and install required Latest SQL server Version on the new build server
Best practices for configuration settings can be implemented while performing the SQL Server upgrade or side-by-side migration.
Restore all the User databases from the Source Server to Target Server .
Stop your SQL agent on Target machine.
Move all your logins from source server to target server using stored proc’s sp_hexadecimal and sp_revlogins
Move all jobs from source to destination server by generating the job scripts on the source server.
Move all your DTS and SSIS packages storing it as file system file and copy it to destination server and make the required configuration changes.
Move all Linked servers from source to target server.
Script out replication setup on source server and move the script to destination server and execute the script to configure replication on the destination server.
Migrate and Configure SSRS, SSAS instance databases.
Also need to understand the new features impact (like SQL 2008 Backup compression strategies, Policy Base management, Resource Governor etc..)
Check Long running queries / any performance issues etc.,
Also move Maintenance DBA jobs to the new environment