1.
Background:
The purpose of this article is to lay out the structure for database
migration from SQL 2000/2005 to SQL 2008/R2/2012. This article describes migration
process in a simpler way for database only which
includes pre and post migration steps also.
2.
What is 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.
3.
What is 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 of SQL
Server 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.
4.
High
Level Architecture:
5.
Before Migration
5.1 What to Migrate
This step includes surveying environment to
determine SQL Server components that will be migrating and then the best technical
option(s) can be determined. Here we are planning to migrate SQL Server database.
1) Verify that SQL Server 2008/R2/2012 environment meets hardware and
software requirements.
2) Ensure that SQL Server 2008/R2/2012 environment is capable of supporting
future needs from a user and transaction perspective. (using SQL Server 2008
Upgrade Advisor to determine potential issues)
3) Set up an environment to test the migration process as well as front-end
applications and dependent (upstream and downstream) systems' functionality.
5.3 SQL
Server 2012 Upgrade Advisor
Perhaps the most
important tool of the several tools typically used for upgrade planning is
Upgrade Advisor. Upgrade Advisor smoothes the transition to SQL Server 2012 by
predicting issues in your legacy instances of SQL Server 2000 and SQL Server
2005. It analyzes objects and code within legacy instances and produces reports
detailing upgrade issues, if there are any, organized by SQL Server component.
The resulting reports show detected issues and provide guidance about how to
fix the issues or work around them. The reports are stored on disk, and you can
review them by using Upgrade Advisor or export them to Microsoft Excel for
further analysis.
In addition to analyzing data and database
objects, Upgrade Advisor can analyze Transact-SQL scripts and SQL Server
Profiler/SQL Trace traces. Upgrade Advisor examines SQL code for syntax that is
no longer valid in SQL Server 2008/R2/2012. It generates a report listing the
code in question, together with links to where you can find more information to
help resolve the questionable code.
5.4 Requirements for running Upgrade Advisor 2012 are as
follows:
1) Windows Vista SP1, or SP2, Windows 7 and Windows Server 2008 R2.
2) Windows Installer beginning with version 4.5. You can install Windows
Installer from the Windows Installer Web site.
3) Microsoft .NET Framework 4. .NET Framework 4 is available on the SQL
Server 2012 product media, and from the .NET Framework 4 download
page.
4) SQL Server 2000 Decision Support Objects
(DSO) if analyzing SSAS (you can use SQL Server 2000 Setup to install DSO)
5) SQL Server 2000 client components if analyzing DTS (you can use SQL
Server 2000 Setup to install the SQL Server 2000 client components)
6) Pentium III-compatible processor
or a later version, with a processor speed of at least 500 MHz
7) Available Disk Space should be
15MB.
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 server, and you can execute it from the
Command Prompt window by using a configuration file name as an input parameter.
Note: You can run the SQL Server 2008/2012 Upgrade Advisor only against instances
of SQL Server 2000 and SQL Server 2005. You cannot run it against instances of
SQL Server 2008/2012 or on SQL Server 7.0.
Upgrade Advisor is a
separate download. The most recent downloadable version is available as part of
the below link:
5. Key SQL Server 2012 migration considerations
Here
are additional technical considerations that are keys to the migration:
1) Execute DBCC CHECKDB to validate that the
previous environment (SQL Server 2000 or 2005) is free of corruption prior to
the upgrade.
2) Issue final backups and ensure a rollback
plan is in place to continue business operations in case an unforeseen problem
is encountered.
3) Develop enterprise standards for SQL Server
2005 configurations, operations (security, maintenance, etc.) and development.
4)
Archive unneeded data and perform data
cleansing for the migrated data.
5) Ensure all code is operating accurately
prior to migration so that migration is the blame for broken code.
6) Perform database maintenance such as UPDATE
STATISTICS, DBCC CHECKDB and index rebuilds post upgrade.
7) Validate that the SQL Server 2000/2005
configurations and database compatibility modes are accurate post upgrade.
6. What are the reasons for Migration
to SQL Server 2008/R2/2012?
There are several reasons to
migrate to SQL Server 2008/R2/2012 of which are involves server level
improvements that are not performance related. SQL
Server 2008/2012 offers certain reliability and availability improvements, such as
the following:
1)
SQL Server 2008/2012's database mirroring
is more robust and easier to configure and manage.
2) Policy-based management can be a tremendous
feature for improving the consistency of SQL Server's configuration and ongoing
management, especially with compliance or other restrictive requirements.
3)
A new data auditing feature that provides
more granular auditing of SQL Server activity.
4) Transparent Data Encryption (TDE) lets you turn on encryption without
requiring massive application-level changes and can help meet specific security
and compliance requirements.
6) The Resource
Governor gives
administrators central control over CPU and memory allocation, which is useful
for servers that host multiple databases and experience significant resource
contention between those applications.
7) If you manage multiple servers, Server
Group management enables you to run T-SQL queries against multiple servers
simultaneously from a central management server. This makes server farm
management easier and more consistent.
8) To avail all the new features of the SQL Server 2008/2012.
7.
Different ways of Database Migration (SQL Server 2000/2005 to SQL Server 2012)
There are several ways to migrate the
databases from SQL Server 2000/2005 to SQL Server 2008/2012:
i. Copy database Wizard: The Copy
Database Wizard lets you move or copy databases and their objects easily from
one server to another or one instance to another instance, with no server
downtime. Using this wizard, you can do the following:
1)
Pick
a source and destination server.
2)
Select
databases to move or copy.
3)
Specify
the file location for the databases.
4)
Create
logins on the destination server.
5)
Copy
additional supporting objects, jobs, user-defined stored procedures, and error
messages.
6)
Schedule
when to move or copy the databases.
In addition to copying databases, you can copy associated
metadata, for example, logins and objects from the master database that
are required by a copied database
ii. Database Backup and Restore: You can take the backup the database from the previous
version and restore it into the new version of SQL Server (SQL Server 2008).
iii. Detaching and Attaching: To move a database using detach and attach, you should
make the following steps:
1) Detach the database.
2) Move the database file(s)
to the desired location on another server or disk.
3) Attach the database
specifying the new location of the moved file(s): After detaching, the database will be removed
from SQL Server but will be intact within the data and transaction log files
that compose the database. You can use these data and transaction log files to
attach the database to any instance of SQL Server, including the server from
which the database was detached. After attaching, the database will be
available in exactly the same state it was in when it was detached.
6.
Post Migration testing:
After migration, we need to
compare the previous version’s database to migrate database.
Followings are the things that we
need to check:
1)
Check
the total no. of tables, stored procedures, views and their names in the
migrated database. It should be same as previous versioned database.
2)
Check the row counts of the tables
