|
ASK THE EXPERT-MR. DATABASE
HANDLING MS SQL DATABASE MIGRATIONS
BY ERIC GROSS, DBA
We have our reasons to install Microsoft SQL Server, but over time business needs change and databases eventually have to be migrated to another location, perhaps even a clustered instance. DBAs are tasked with making the database migration succeed – and with this job comes all of the complexities associated with migrations. This article describes some best practices that will help you ensure migration success.
First some background on the types of complexities that exist. MS SQL provides client access to the company data that is stored inside of the user databases. All MS SQL instances include system databases in addition to the databases directly used by clients. Data for smaller and simpler applications can exist within a single database while the more complex applications typically span multiple databases. Depending on the inter-database communication requirements of the databases being migrated, there might be dependencies among the databases. When there is significant inter-database communication, the simplest way to accomplish the migration of any one database is to move all of the related databases as a unit.
Each database lives within the context of a single MS SQL instance – this creates a plethora of dependencies on the instance containing the database. First the users – logins are defined at the instance level. Database users are each defined as a set of privileges mapped to an instance login. Logins that are used within the database will need to be available on the target instance in order to maintain accessibility to the contents of the database. Microsoft provides a script that exports in an obfuscated format all logins defined in an instance and subsequently imports any missing logins to the target instance. This procedure works for both internal instance logins as well as logins defined in the Active Directory. If Active Directory logins are being used, issues arise when the instances are not using the same Windows domain.
Sometimes there are additional infrastructure requirements such as those required by stored procedures and scheduled agent jobs. Unless the migrating DBA is familiar with the contents of the database at hand, migrations can fail due to missing dependencies such as system libraries or a JRE. Extended stored procedures also call outside the MS SQL instance context requiring additional dependencies.
So where does automation come into play in all this? Obviously, with a migration being as much work as it is, anything that can cut down on the effort level is a positive thing. But there are certain specific benefits that automation can bring you that can turn this hideously complex process into something smooth and predictable.
Migrating a database requires that you notify the clients that depend on the database of the schedule of change. In most cases the clients will need to be redirected to the new instance – planning is essential here, especially when there are manual procedures involved in a large number of servers. Before moving any databases it is prudent to confirm that the database and objects within have integrity. This can be accomplished by using the DBCC tools provided as part of SQL Server. Additionally, any sane DBA would take a physical backup of the database before attempting any changes that may disrupt the environment.
Since all of the enterprise level methods used to upgrade a database from one MS SQL release to another (for example, MS SQL 2000 to MS SQL 2005) involve a migration of data from an instance running an older version of the software to a newer version, it is essential to plan upgrades in concert with migrations. Luckily in the MS SQL environment, upgrades of a database are generally handled automatically by the system upon import/restoration to the new instance.
No database migration can be guaranteed to be infallible, but if the proper planning and testing occurs, combined with adequate communications with those involved, your migration is on the path to success.
Newsletter Home
|
