Database Reference
In-Depth Information
In addition to these three tools, we will also briefly mention a free utility found on CodePlex called
the SQL Azure Migration Wizard which provides a wizard-driven interface to walk you through migrating
your database and data to SQL Azure.
The examples in this chapter use SQL Server 2008 R2 Community Technology Preview (CTP), which
at the time of this writing is available from Microsoft's MSDN site. These examples also work with SQL
Server 2008, although some the screens may be a bit different.
You may wonder why the SQL Server Import and Export Wizard isn't listed here. The answer is that
the SQL Server Import and Export Wizard isn't supported for SQL Azure yet. Microsoft is working on it.
No timeframe has been given as to when the Import/Export Wizard will support SQL Azure, but support
is definitely in the works.
The database you use in these examples is TechBio, which you can download from the Apress web
site for this topic. This sample database is a mini version of the TechBio database that is behind the
TechBio application found in the download for this topic.
Generate and Publish Scripts Wizard
The Generate and Publish Scripts Wizard is used to create T-SQL scripts for SQL Server databases and/or
related objects within the selected database. You have probably used this wizard, so this section doesn't
walk through it step by step; instead, the section briefly highlights a few steps in the wizard and points
out the options necessary to effectively work with SQL Azure.
SQL Server 2008 R2 comes with the ability to script an on-premises database for the SQL Azure
environment. Because many haven't moved to SQL Server 2008 R2, the examples in this section use the
version prior to R2, which is the original release of SQL Server 2008.
One of the differences between SQL Server 2008 R2 and SQL Server 2008 (pertaining to object
scripting) is a setting in the Advanced Scripting Options dialog as you go through the wizard. This dialog
includes two properties you can set regarding the version of SQL Server for which you're scripting
database objects: Script for Server Version and “Script for the database engine type.” The Script for
Server Version option lists the version of SQL Server that the Generate and Publish Scripts wizard
supports, which ranges from SQL Server 2000 to SQL Server 2008 R2.
The “Script for the database engine type” property has two options you can choose from: “Stand-
alone instance” and “SQL Azure database.” The “SQL Azure database” option only works with the SQL
Server 2008 R2 Server version. For example, if you set the Script for Server version to SQL Server 2008
(non R2) and then set the “Script for the database engine type” property to “SQL Azure database,” the
Script for Server version property value automatically changes to SQL Server 2008 R2.
The Generate and Publish Scripts Wizard does a really nice job of appropriately scripting objects for
SQL Azure. The wizard checks for unsupported syntax and data types, and checks for primary keys on
each table. Thus, the following example sets SQL for Server Version to SQL Server 2008 (non R2) for
several reasons. First, many people aren't using SQL Server 2008 R2 and therefore don't have the option
to script for SQL Azure. Second, this exercise shows you what steps are needed to get a script ready to
run in SQL Azure.
Starting the Wizard
To start the Generate and Publish Scripts Wizard in SQL Server Management Studio (SSMS), open Object
Explorer and expand the Databases node. Select a database, right-click it, and then select Generate
Scripts from the context menu.
On the wizard's Introduction page for SQL Server 2008 R2, you're informed that you must follow
four steps to complete this wizard:
