Eventually SQL Server databases in a production environment must move from one server to another. It’s important to plan for eventual database moves when architecting a database solution.

Designers and developers often overlook the eventuality that a database will need to move to a different server. This month’s column is the first of three dealing with the issues surrounding database moves. In this first part, you’ll learn about the reasons why you should plan for moving a database when you design and architect your application.

The Problem of Moving a Database

It’s easy to move a SQL Server database from one server to another. You can back it up and restore it to another server, detach it from one server and attach to the other, or use a database copy utility.

But getting the database to function correctly on the new server can be a real problem. In most cases, after you’ve moved a database to a new server, you must do additional work to fully recreate the original database environment, and you may have to do some reconfiguration for the application as well. In fact, you haven’t completed moving the database until you’ve done all that additional work. The problem of a database move is finishing the job!

It’s not unusual to see a database move break an application. I’ve seen one case where the production database had grown so complex that after one week of testing the client abandoned the planned move. It’s common to see cases where setting up additional supporting objects and complex configurations add significant down time and risk of error to the database move.

Here’s the irony: the longer you have a database in production, the more complex its dependencies and supporting features are likely to become, and the more difficult it will be to move it to another server. Yet the longer you have your database in production, the more likely it is that you’ll have to move it to a new server!

Movability and Database Quality

The attributes that determine the quality of a database are often grouped around the phrase "-ity’s", and include:

  • Availability
  • Scalability
  • Security
  • Maintainability
  • Deployability
  • Extensibility
  • Performance

(Performance is not an "-ity" but everyone includes it anyway!)

Designing a database so that you can more easily and reliably move it definitely contributes to a database’s quality. So how do you add it to this list? What’s a good term for the attribute that describes the degree to which you could easily move a database from one server to another?

“Database portability” while great, means the ability of an application to work on several different vendor database products. (Also, “database portability” has a special meaning in Exchange 2007 to refer to moving mailbox databases across Exchange servers.) Also, “database mobility” won’t do because mobility in the Microsoft world refers to mobile devices, not servers. “Database transportability” is getting closer and does not seem to have any prior meaning in the SQL Server world, but it’s also very close to portability (and has too many syllables!) So let’s settle for using the phrase “database movability” to describe the database quality attribute that measures how well you could move a database.

Database Movability Scenarios

When you design your application’s interface with the database, you can plan ahead by making it easy to reconfigure the application for that time when the database changes servers. In other words, do not hard-code database server names or database names, but read those values from some external source that you can easily and quickly reconfigure.

In addition, as the production database continues to grow and become more complex, you need to ensure that additional supporting features and objects will move easily with the database to another server. These include such things as server logins, database permissions, jobs, linked serves, SSIS packages, mirroring, and service broker endpoints-the list can get rather long. They all can contribute to making a database move more complex and time-consuming than you initially expect.

To better understand why you should design for database movability, let’s take a look at a number of potential scenarios where you can anticipate that you’ll need to move a database from one server to another.

Database Migration

Eventually in the life of a database, someone will need to move it to a new database server. Many people call this database migration. You’ll retire the current physical database server, perhaps because it is beyond warranty, or because the server can no longer support the required database load. So someone provisions a new server, readies it for production, and someone moves the database to it.

Sometimes the new server and old server can co-exist on the network because they have different names and IP addresses. But often the old server and new server cannot co-exist on the network because the new server must have the same name and IP address as the old server. When the two servers cannot co-exist, the time required to move the database normally takes much longer, so you must plan for increased downtime for the application.

Database Consolidation

As server hardware becomes better over time, you may determine that it’s beneficial to consolidate databases by moving them from disparate servers to a smaller number of more powerful database servers.

A similar scenario occurs when your organization places a large number of databases onto a set of database servers, and then desires to easily move them around so as to "even out" or balance the database load. In this case, you need the flexibility to move the databases among the servers in such a way that the applications depending on these databases are only minimally affected.

High Availability and Disaster Recovery

To maintain high availability in a data center, or provide disaster recovery to a remote location, you may need a hot or warm standby of the database on another server, ready to take over in the event of a failure of the main server. For SQL Server 2005, you can use database mirroring, log shipping, and possibly replication for this purpose. (Replication is more properly used for data distribution, not high availability.) Both database mirroring and log shipping have the interesting property that they put a copy of the original database on another server and keep it up to date, although in an unrecovered state. In effect, the database is “almost moved” to the other server, and the move completes when you fail over and change the roles of the servers.

When a database mirroring failover occurs, or a log shipping role change occurs, the target server takes on the role of the main database server, and what was a copy of the database now becomes the main production database. But this will not work unless you can easily reconfigure the application to the alternate server and database. In fact, all the same considerations for completing a database move come to bear in a failover scenario when your application must quickly use the database on a new server.

(You can find out more about these topics in SQL Server 2005 Books Online. In general, see “Managing Metadata When Making a Database Available on Another Server Instance.” For database mirroring, see also “Managing Logins and Jobs After Role Switching.”)

You could invest a lot of effort and expense developing a solution for keeping a database available with minimal downtime during a failover to an alternate server, only to lose whatever you gained if you can’t quickly use that database. The failover may quickly succeed, and the new database on the alternate server may be ready to go in a short amount of time. But if you haven’t designed your application to easily use the database on another server, or if reconfiguring the database requires time and intervention, that quick failover may do you no good.

Database Movability: What’s Next?

In next month’s column, I’ll take a closer look at what it means to move a database, and the strategies you can use to design in the ability to quickly move a database to a new server with minimal downtime.

In the final column of this three-part topic, I’ll take a closer look at database dependencies and how to manage them. The problem of moving a SQL Server database arises not from the database itself but from dependencies of the database in question: the more dependencies the database has, the more difficult and time-consuming it is to move the database. These dependencies can arise between the database and other database-related objects, but also from the application.