Microsoft SQL Server version migrations are one of the more difficult activities for a DBA to execute, yet they are a very common change to the infrastructure. Databases eventually have to be migrated to another location, perhaps even a clustered instance. DBAs are tasked with making the database migration succeed despite all of the complexities and failures that are possible. The concerns around database migration can be alleviated through an automated process which results in an infrastructure that will be easier for DBA teams to manage, more compliant, easier to document, faster in turnaround time for changes, and more reliable with less downtime.
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 complex process into something smooth and predictable.
When a data center is being built fresh with all new servers, automation isn’t primarily about QA (though it can help with that, too); it’s about building the environment. A new server deployment is a chance to start fresh - for example if the production environment is running older versions of SQL Server, a new server migration can be the perfect time to upgrade the environment. With the extra hardware, the provision/configure/upgrade/migration cycle can be tested over and over again with zero risk to the actual production data. In addition, any new standards that have been defined around how environments should be configured (standard file paths, versions, enabled database options, etc.), can be baked into provisioning automation, allowing new environments to be rolled out quickly and easily, and as part of testing, torn down again just as quickly. The automation ensures that at the end of the data center move, everything is secure, standardized, and easy to manage.
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 it 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.
Migrations are painful, time-consuming events, and no automation can completely remove the complexity of these processes. However, automation can provide dramatic ROI for both major kinds of migration by driving predictability. It could be predictability of QA, or predictability of upgrades and deployments, or even just getting a realistic, up-to-date picture of where the process is. In all of these cases, automation is the blueprint for guaranteeing a successful migration.