Migration and Upgrade
Migration: Migration is a manual process in which the DBA installs a new instance of SQL Server and copies the metadata and data from an old instance to the new instance. Migration provides access to two instances of the system, letting you verify and compare the two systems. During migration, both the old and new systems remain online until migration to the new instance is complete. At the end of the migration, all applications are directed to access the new instance, and the old instance is manually removed.
Upgrade: Upgrade is an automated process in which the upgrade tool, called Setup, moves an old instance of SQL Server to a new instance while maintaining the data and metadata of the old instance. At the end of the upgrade, the old instance is no longer available and the new instance has the same name as the old instance
Migration
There are several reasons to migration
- Whenever Moving Databases from production to testing or testing to production
- Moving from old hardware to new hardware
- Moving the databases between the servers located in Different geographic locations
- Instead of maintaining multiple servers, we will maintain one big server.
- If we are migrating to a higher version to avail all the new features of the SQL Server
Pre-Migration Checklist
- Perform Backup of System and User databases
- Analyze the disk space of the target server for the new database; if the disk space is not enough, add more space on the target server
- Confirm the data and log file location for the target server
- Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy Option, etc)
- Collect the information of dependent applications; make sure application services will be stopped during the database migration
- Collect the information of database logins, users and their (Optional)
- Check the database for the Orphan users, if any
- Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers)
- Check if the database is part of any maintenance plan
- Whether you choose an in-place upgrade or a side-by-side upgrade, run Upgrade Advisor on your legacy systems. You can run Upgrade Advisor from a local or remote
Steps in Migration
Moving Databases from Source server to destination Server
- Moving Logins from Source server to destination Server
- Moving Jobs from Source server to destination Server
Moving Databases from Source server to destination Server
There are several ways to migrate the databases from one server to another, as below
- Database Backup and Restore
- Detaching and Attaching
- Copy database Wizard
- Import and Export
Moving Logins from Source server to destination Server
There are several ways to migrate the Logins from one server to another server, as below
- Master Database Restore (if the versions are same)
- By using stored procedures are named sp_hexadecimal and Sp_revlogin (99 % will use)
- SSIS Transfer Logins task
Moving Jobs from Source server to destination Server
There are several ways to migrate the Logins from one server to another server, as below
- MSDB Database Restore (if the versions are same)
- SSIS TransferJobs task
- SSIS Import & Export task
- Script-based Jobs transfer (99 % will use)
Post Migration Steps:
Check for server and version compatibility with appropriate patches
- Check the total number of tables, stored procedures, views, and their names in the migrated database. It should be the same as the previous versioned database.
- Run DBCC CHECKDB on all databases to check consistency
- Find and fix orphan users
- Verify linked servers. jobs alerts are moved
- Check for server and version compatibility with appropriate patches
- Run DBCC UPDATEUSAGE on all databases to correct any incorrect row or page
- Run sp_updatestats against all Migrated
Upgradation
If you have an existing installation of SQL Server, you can choose to upgrade it to SQL Server instead of installing a new instance.
A SQL Server 32-bit instance running on the 32-bit systems cannot be upgraded to 64-bit SQL Server directly. You need to install a 64-bit SQL server on a 64-bit system, and then you can restore it on 64-bit systems.
SQL Server Upgrade Advisor, Data Migration Assistant is a tool that can help you analyze your SQL database for possible incompatibilities before being upgraded to SQL Server to a higher version and help you proactively resolve them.
components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade.
Upgrade Advisor analyzes the following SQL Server components:
- Database Engine
- Analysis Services
- Reporting Services
- Integration Services
Pre-Upgrade Tasks
Following is the list of pre-upgrade activities identified for this iteration:
Check hardware/software requirements: Check the minimal hardware and software requirements before upgrading.
Run Upgrade Advisor: Upgrade Advisor analyzes installed components from earlier versions of SQL Server and then generates a report that identifies issues to fix either before or after you upgrade
Resolve issues identified by SQL Server Upgrade Advisor: issues related to this upgrade iteration need to be resolved, and document the issues and resolution steps.
Take the backup and validate backups. It is extremely important to verify the Backups by running Restore Verify only or restoring it to another Environment
Perform database consistency checks: in order to verify the consistency of all databases being upgraded, the DBCC CHECKDB should be executed against all upgrade candidates during low activity hours and corresponding results documented for further reference.
Resolve database consistency errors: if DBCC results report any database consistency errors, there are essentially three approaches to resolve the problem:
Subbu-SQL Server DBA
Script Logins, Jobs, Alerts, Linked Servers and Operators: Scripting these are very important in case of rollback.
Collect the information about the database properties: Script the database properties like Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy Option, etc.
Check for the version, edition, and services installed: To Get the services installed, goto ->All Programs->Microsoft SQL SERVER 2008-> Configuration Tools-> Click on SQL Server Installation Center, select Tools Page, and click on Installed SQL Server Features Discovery Report
Keep the Installable Ready: Copy the SQL Server 2012 Enterprise Edition Installation and Copy all the Service Packs
Need to get the service accounts and passwords: We need to get the service accounts used in SQL server services and agent services and there passwords. While upgrading, it asks for Service accounts to be used
Post-Upgrade Tasks
Following is the list of post-upgrade activities identified for this iteration:
Perform database consistency checks: execute DBCC CHECKDB WITH DATA_PURITY against all upgraded databases during low activity hours and document results for further reference.
Verify overall integration into the environment: verify that the following additional requirements affecting the upgrade are also met.
- Linked Servers, Scheduled Jobs, Alerts.
- Imports and exports.
- Patches, hotfixes and cumulative updates
Update Usage: Run DBCC UPDATEUSAGE on all databases to correct any incorrect row or page counts.
Update Statistics: Execute system-stored procedure sp_updatestats against all upgraded databases. Repopulate full-text catalogues in all full-text enabled databases.
Take a performance baseline: setup and execute a performance baseline that captures key performance metrics associated with all upgraded SQL Server instances and databases included in the current iteration. Verify the overall health of the system and the performance of each upgraded application under the new environment.
Check the error log: Check the SQL Server Error Log for login failures and other errors