MS SQL Server Upgrade /Migration

There are many ways we can migrate the encrypted database, no matter what type of database version of SQL Server that we are using, the solutions below are options we can use to upgrade and migrate SQL Server instances:

• In-place Upgrade: there is a considerable down time for the application, since this is a completely automated process and will be helpful only in case we have to upgrade on the same server or we are planning to have the databases in the same hardware and it is a must to have the same configuration and instance name of the SQL Server, this is completely managed by the installation package from Microsoft upgrades for the SQL Server databases and its components. This is the mostly used for non-prod instances, since a failure in the installation during upgrade and the roll back time is very high.

• Backup Restore method: the down time is minimal for the application, where-in we will be backing up the database and restoring it in the new environment to same location on the Windows drives as the existing ones, along with the certificates and creating a database master key in the master 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. But for Health Net we will be doing a full backup and restore during the cutover period for most of the databases, for very large sized databases, we would have to take the differential backup and tail log backup from the source server and restore them to destination and bring the database in a consistent state, during the cut over phase. The major disadvantage with this approach is that, it involves lot of manual intervention and the preparation time is more than the actual execution phase.

• SSIS package: can take lot of time for the migration and it involve lot of effort. Create an SSIS package that would copy the data from the existing environment to a new environment, and then create a copy to the new database, which is compatible with the application. This is time consuming as it involves writing the SSIS package to new database, and then using larger database.

The best practice approach would be to backup and restore the database from one server to another for upgrading the databases, but not all servers can be use this approach. Here is our recommendation to migrate the databases:

• Build the server for SQL databases and install SQL server executable on the target server
• Best practices configuration would be implemented during the SQL Server upgrade and migration
• Restore all the user databases from the source to target to the same location as the source server
• Bring SQL agent offline on the target machine
• Move all the logins from source to target using sp_hexadecimal and sp_revlogins
• Move all the jobs from source to destination by generating the job scripts on the source system.
• Move all the DTS and SSIS packages by storing it as a file system file and copy it on the destination system and make the required configuration changes
• Move all the linked servers
• Script out the replication setup on the source system and copy the script on destination and execute the script to configure replication on the destination system
• Configure and Migrate SSRS and SSAS instance databases.
• Understand the impact of new features (like SQL 2008 Backup compression strategies, Policy Base management, Resource Governor etc..)
• Identify long running queries / Batch job performance etc.,
• Migrate Maintenance DBA jobs to the new environment
• Find a suitable window to migrate databases from current production to NEW production environment