Database Reference
In-Depth Information
Executing the Script Against an Azure Database
You don't have a SQL Azure database to run the script against, so let's create one now:
1.
Connect to your SQL Azure instance (refer to Chapter 2 for reference if needed),
making sure you're connecting to the master database.
2.
Open a new query window, and use the syntax discussed in Chapter 2 to create your
SQL Azure database. Name it TechBio, because this is the name the examples use
throughout this chapter.
3.
Click over to the generated script. This query window is currently connected to your
local SQL instance, so you need to change it to your SQL Azure instance and the
database you just created. Right-click anywhere in the script, and select Connection
Change Connection from the context menu.
4.
In the Connect to Database Engine dialog, enter the information for your SQL Azure
instance, and enter the name of the database you just created on the Connection
Properties tab.
Click Connect.
You now have your script, a database, and a connection to that database. Click the Execute button.
Your script should run and create the tables, procedures, and data in your SQL Azure Database.
The SQL Server Generate and Publish Script wizard is a great way to start understanding the
required changes that need to be made when migrating to SQL Azure. With this foundation, let's discuss
one of the other options, SQL Server Integration Services.
5.
SQL Server Integration Services
SQL Server Integration Services (SSIS) is a data-integration and workflow-solutions platform, providing
ETL (Extract, Transformation, Load) solutions for data warehousing as well as extractions and
transformations. With its graphical tools and wizards, developers often find that SSIS is a quick solution
for moving data between a source and destination. As such, it's a great choice for migrating data
between a local database and a SQL Azure database. Notice, however, that the previous sentence says
data
. When you're using SSIS, the database and tables must already exist in SQL Azure.
Note
Volumes of information (books, articles, online help, and so on) are available about SSIS. This section isn't
intended to be an SSIS primer. If you're unfamiliar with SSIS, this section provides enough information to give you
a foundation and get you started.
If you're familiar at any level with SSIS, you're probably wondering why it has the limitation of only
moving data. Several SSIS tasks can provide the functionality of moving objects as well data, such as the
Transfer SQL Server Objects task. When asked about this task, Microsoft replied that SSIS relies on SMO
(SQL
Server Management Objects
) for this task, and SMO doesn't currently support SQL Azure. In
addition, some of the SSIS connection managers use SMO and therefore are limited when dealing with
objects. Thus, the current solution is to create databases and tables using straight SQL and then use SSIS
