Databases Reference
In-Depth Information
The database you use in these examples is AWMini, which is a mini version of the AdventureWorks database.
This database can be downloaded from the APress site for this topic.
The Import/Export Service
In 2012 Microsoft released some great functionality for migrating an on-premises database to SQL Azure in the form
of the Import/Export service. To understand what this service is and how it works, you first need to know about the
Data-Tier Application Framework, more commonly known as DAC Fx.
Data-Tier Application Framework
DAC Fx was introduced in SQL Server 2008 R2 as part of the Application and Multi-Server Management services,
a way to manage your database environments efficiently and proactively. It was designed with scale in mind to
provide an effective way to manage the deployment of the database schema between environments, such as from
Development to Test, and Test to Production.
DAC Fx is a set of tools, APIs, and services designed to improve the development, deployment and management
of SQL Server database schemas. In most environments, the DBA creates and maintains sets of T-SQL scripts that
create or alter database objects, such as tables and stored procedures. The issue is that in many instances the DBAs
need to maintain multiple sets of scripts; one for the initial creation and another for updates and modifications that
update the database from one version to another. Add on top of that multiple versions of those scripts, and you have
the makings of a complex maintenance scenario.
This is where DAC Fx comes in. Instead of building and maintaining sets of T-SQL scripts, SQL Server 2008 R2
introduced the concept of a
BACPAC
. A DAC, or Data-Tier Application, is an entity that contains all of the database
objects used by an application. Its purpose is to provide a single unit for creating, deploying, and managing objects
instead of managing each object individually.
When a DAC is ready to be deployed, it is built into a DAC package, or
DACPAC
. This package is a file that
contains the DAC definition, and is simply a zip file containing multiple XML files.
However, these
DACPAC
s only contain schema, not data. This is where the new version of the DAC Fx comes in.
Version 2.0 now supports data as well as schema, dumping your table data right alongside your schema in a new file
format called
.bacpac
. Using the DAC Fx, you can now extract a BACPAC from an existing database and deploy it to a
different SQL Server environment, including, wait for it . . . SQL Azure.
Now, it should be noted that SQL Server 2012 comes with the DAC Fx version 2.0 and the ability to export to a
BACPAC. The next two sections are going to walk you through creating and deploying BACPACs using SQL Server
2012. You will see how the Import/Export service is used in the Windows Azure Management Portal to import and
export to and from a SQL Azure database.
Deploying to SQL Azure via a BACPAC using SQL Server 2012
For all those who have not upgraded to SQL Server 2012 the next session will address that, but for now, this section
will use SQL Server 2012. First you'll see how to deploy an on-premises database directly to SQL Azure, and then how
to use the built-in import and export features to migrate using BACPACs from SQL Server 2012.
Keep in mind that all of the examples in this section assume that your database is
Azure-ready
, meaning that your
on-premises SQL Server database is ready to be migrated to SQL Azure. For example, if you were to download the full
AdventureWorks database, such as the SQL Server 2008 R2 version, and then try to walk through the examples in this
section, the examples in this section will fail due to objects that aren't supported in Windows Azure SQL Database
(such as native encryption and Full Text Search). The section “Generate and Publish Scripts Wizard” in this chapter
explains how to make your database Azure-ready by taking an on-premise database and applying the appropriate
modifications and changes. More importantly, it also explains why those changes need to be made.
