Database migration is one of the core responsibilities of SQL Server DBA. Couple of applications in a company may be hosted on environment of which Microsoft will be ending support in near future. Hence, it is likely for project team to do application migration with in a good time frame. This process needs lot of meetings and brain storming session to take decision. Database driven application are more likely to have database movement from old servers to new host server.
There could be following possible decision which can be taken for database perspective:
- Move old database (version 2005) host on server 2003 to a new database instance (version 2012) without changing compatibility level to 110. This is a best choice if your goal is to just eliminate old instances of SQL Server or application is complex and time frame is very less to wrap up your development work.
- Move old database (version 2005) host on server 2003 to a new database instance (version 2012) with compatibility level changed to 110. This is a best choice if your team has a time to rewrite development code if required and you want to utilize latest functionalities of SQL Server in application.
With whatever reason you are going on, it is not just that simple. There are chances that you can miss important steps while migrating database.
Following are the recommendations while working on database migration process:
- Prepare a list of all databases host on same server and are interlinked with the database (one application can user multiple databases) which you are going to migrate.
- Compatibility Test 1 – Use Microsoft® SQL Server® 2012 Upgrade Advisor tool against all listed databases to collect incompatibility report with respect to SQL Server 2012. Microsoft Upgrade Advisor analyzes instances of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 in preparation for upgrading to SQL Server 2012. On the basis of report, you need to decide about the amount of work hours that you need to invest before and after migration of databases. There may be couple of deprecation announcement or deprecated feature being used in current application and those code features should be removed from SQL code.
SQL Server Upgrade Advisor (SSUA)—This Microsoft tool analyzes an existing older version of a SQL Server database and identifies any issues with existing objects. For example, the SSUA tool will identify any stored procedures that use old and deprecated syntax. This tool currently tops out at SQL Server 2008 R2, but it’s still very useful. To learn more about this tool, see the Microsoft “Use Upgrade Advisor to Prepare for Upgrades” page.
- Compatibility Test 2-If you have statement level trace files (24*7 traces capturing each query running on your SQL instance) on old SQL Server, you should also run them against Microsoft® SQL Server® 2012 Upgrade Advisor to get information about all deprecated query features which have been used as ADHOC query on application level code.
- Collect properties of each database like
- Auto Stats
- DB Owner
- Recovery Mode
- Compatibility level
- Trustworthy option
- SQL Logins
- SQL Jobs
- Maintenance plans and activities
- Database Size
- Data and log file location
- Information of dependent applications
- Linked Servers
- Performance Test – Performance testing is also necessary for complete migration. Because, at last all database will also get migrate to new instance. Just do not assume that new version will perform better. New version might be running legacy code that isn’t efficient or won’t benefit for new functionality in new server. Best option is to prepare a testing VM machine with same configuration as that of current database environment. Once, machine is ready , get latest full backups of each database and restore them on ready targeted test VM machine. Make connection of test applications to this test database instance and start capturing 24*7 profiler trace at query/statement level. Run those captured instances against Upgrade Assistance tool.
Upgrade Assistant for SQL Server 2012 (UAFS)—This tool, which is written by Scalability Experts and Microsoft, incorporates SQL Server 2012’s new distributed replay feature to incorporate workload testing to identify compatibility issues that exist in an application’s source code, such as an ASP.NET application, rather than inside the database. Because this tool is specially designed for SQL Server 2012, we’ll focus on it in this article.
Quickly and easily determines problems that might slow or prevent upgrading from an existing SQL Server database to SQL Server 2012
- Once performance testing is completed and results are compiled, start restoring backups on target server and if your team has decided to use latest feature version and have time to invest, then do change compatibility level from 90 to 110 after restore. If your team want to move databases to newer instance of SQL Server but at the same time have it backward compatible, you need to make sure that the compatibility stays the same. Do not change compatibility level to 110.
(Note – Compatibility level does not automatically change when we upgrade a database from older version into latest database engine. This is not a major problem, but there are certain features that we will not be able to take advantage of unless we change compatibility level to latest one available)
- Transfer logins to new server and fix orphaned users.
- Transfer SQL Jobs to new server
- Add changes on the basis of performance test report. Like updating Statistics of all tables and indexes, recompiling all procedures.