Suppose that you're the administrator of hundreds of SQL Server instances in a virtualized environment. Previously, many of your SQL Server computers
were running a single departmental application and its database, but you've recently consolidated some of those applications and their databases on a
single SQL Server instance to reduce the number of SQL Server computers.
Now you'd like the ability to move individual databases from one SQL Server instance to another, just as you can move OS resources (and users of OS
resources) in a virtualized environment. Specifically, you'd like to be able to use a dashboard to monitor the resource utilization of each SQL Server
instance and each database. If you notice that one instance is being overutilized or underutilized, you'd like to balance the resources by using the
dashboard to quiesce a database, then drag and drop it from one instance to another.
Currently, there are a few challenges that you'd have to overcome to implement this idea.
Challenge #1.
Databases aren't standalone resources that can be moved between instances at will. You need to move logins along with the databases, and logins live in
the master database, not your database. So, to move Database A from Instance A to Instance B, you must, at minimum, move the logins from Instance A's
master database to Instance B's master database. In addition, you must make sure that your database has no dependencies on msdb, other database objects
in the master database, or instance-level configuration parameters such as instance collation. In other words, you need to limit the objects in your
database to make sure it's as instance-independent as possible.
Challenge #2.
What mechanism do you use for the drag-and-drop operation? Do you use backup and restore operations, plus something to accommodate logins? Do you
script the database objects and data?
Using backup and restore operations might not work because your instances might be running different versions of SQL Server and those operations don't
accommodate restoring a newer version on an older instance. The drag-and-drop mechanism needs to be instance-independent, with intelligence in the
drag-and-drop API to check for possible object-version conflicts (e.g., spatial columns in a database you're going to drag and drop onto a SQL Server
2005 instance).
Using scripts might not work either. DBAs spend a nontrivial amount of time honing their scripts and checking for conflicts when doing a database move,
SQL Server upgrade, or application upgrade. But suppose you want to write a script that generalizes "the drag-and-drop a database" concept for
databases that the script doesn't have intimate knowledge of? The script would need to do enough checking to "just work" and return the appropriate
errors so that the drag-and-drop operation doesn't cause subtle problems down the road.
From Theory to the Real World
Let's move from discussing this as a theoretical problem to a real-world use case. The types of challenges that I've described are an intrinsic part of
moving databases in a SQL Azure Database platform. Currently, the SQL Azure Database platform doesn't have an msdb database, and only a discrete set of
database objects are permitted. Except for database logins, none of the usual custom database objects are allowed in the master database. You also have
no control of the instance environment. You only have control of logins and database-level objects. And almost all database-level properties (e.g.,
collation) aren't user-settable.
As a DBA, you might need to deploy on-premises databases to a SQL Azure Database platform. To do so, you need a portable, instance-independent, and
version-independent database representation that lets you move schema and data. Neither attach and detach operations nor backup and restore operations
will work for this because the SQL Azure version isn't under your control. These operations also don't work across trust environments, such as
on-premises-to-cloud or appliance-to-cloud.
However, you can use a data-tier application (DAC) to create a "database container" that lets you move schema (and soon data) to and from SQL Server
and SQL Azure databases. It's helpful to think of DAC as the .msi file equivalent for databases. DAC operates at the logical layer, so it provides a
file artifact that is open, transparent, portable, and trustable. DAC isn't for everyone, though. A multi-database application with lots of
dependencies on msdb, the master database, linked servers, and other instance-level and cross-database objects (such as Service Broker routes) is not a
good candidate for DAC.
DAC targets departmental databases in the enterprise as well as simple third-party applications that contain a few gigabytes of data. These are also
the types of applications that would be well-suited for consolidation and likely well-suited for a SQL Azure-based deployment.