This article show you how to connect to SQL Azure using SQL Server Import and Export Wizard. Once you understand how to connect to SQL Azure, you can migrate data between SQL Azure and other data sources/destinations.

The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. The list of available providers includes the following data sources:

• SQL Server
• Oracle
• Flat files
• Microsoft Office Access
• Microsoft Office Excel

For more information on using the wizard, see Importing and Exporting Data by Using the SQL Server Import and Export Wizard .

SQL Server Import and Export Wizard is capable of copying schema and data, but not other database objects like indexes, constraints and stored procedures.  Becasue SQL Azure only support tables with a clustered index, migrating both schema and data at the same time will fail.  The wizard is better used for migrating data only. For migrating schema and other SQL Azure data migration options, see Overview of Options for Migrating Data and Schema to SQL Azure.

When you migrating data from SQL Server to SQL Azure, it is also important to note that SQL Azure does not support all SQL Server data types and features. This can complicate the data migration process.  For more inforamtion, see Guidelines and Limitations (SQL Azure Database) and Comparing SQL Server with SQL Azure.

Prerequisites

The following are the minimum requirements for following the procedures in this article:

• SQL Server 2008 R2 or SQL Server 2008 R2 Express
• A local database.  If you don't have one, you can use AdventureWorks 2008R2 SR1
• Windows Azure SDK 1.3
• Windows Azure Subscription

Preparations

You must complete the following steps:

• Getting a Windows Azure subscription. For more information on getting a trial subscription, see Acquire and Activate a Trial or Subscription: A Step-by-Step Guide.
• Creating a SQL Azure server and database. For more information, see Getting Started with SQL Azure using the Windows Azure Platform Management Portal.
• Configuring SQL Azure firewall to allow the computer running the wizard to connect to the SQL Azure server. For more information, see Getting Started with SQL Azure using the Windows Azure Platform Management Portal.

Procedures

The following procedures show you a custom solution for migrating both schema and data from SQL Server to SQL Azure. As stated in the begining of the article, the wizard is better used for migrating data only. The main goal of these procedures is to show you how to connect to SQL Azure from the wizard.

1. Migrating schema
2. Creating clustered index
3. Migrating data

To migrate schema from SQL Server to SQL Azure

1. Click Start, point to All Programs, point to SQL Server 2008 R2, click Import and Export Data.
2. From Choose a Data Source, type or select the following values, and then click Next:

   Name	Value
   Data source	SQL Server Native Client 10.0
   Server name	[DatabaseName]\[InstanceName]
   Authentication	(enter the authentication information)
   Database	(specify the database where you want to migrate data from).

3. From Choose Destination, type or select the following values, and then click Next.

   Name	Value
   Destination	.Net Framework Data Provider for SqlServer
   Encrypt	True
   Integrated Security	False
   Password	(Enter the SQL Azure administrator password)
   TrustServerCertificate	True
   User ID	(Enter the SQL Azure administrator username in the format of [username]@[sqlazureservername])
   Data Source	(Enter the FQDN of your SQL Azure Server)
   Initial Catalog	(Enter the database name where you want to insert data to)

   For more information on Encrypt and TrustServerCertifiate, see Security Guidelines and Limitations (SQL Azure Database).

4. From Sepcify Table Copy or Query, select Copy data from one or more tables or views, and then clickNext.
5. From Select Source Tables and Views, select the table that you want to migrate to SQL Azure, and then click Next.
6. From Run Package, click Next.
7. Click Finish.  The data copy process fails with the following error message:

   - Copying to [TableName] (Error)
Messages
Error 0xc020844b: Data Flow Task 1: An exception has occurred during data insertion, the message returned from the provider is: Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again. (SQL Server Import and Export Wizard)

   The error message indicates clustered index is required for SQL Azure tables.

   Note: Do not close the Wizard.  You will come back to the wizard after you create clustered index for the table.

To create clustered index for the SQL Azure database table

1. Open Windows Explorer, and browse to http://windows.azure.com.
2. Sign in Windows Azure.
3. From the lower left corner, click Database.
4. Expand the subscription, expand the SQL Azure server, and then click the database where you imported the schemas in the last procedure.
5. From the top menu, click Manage. Database Manager is opened in a different brower tab.
6. Enter the server, database and authentication information, and then click Connect.
7. From the top menu, click Open Query.
8. In the query window, create a clusted index for each of the tables you want to migrate. For more information on using the create index T-SQL statement, see Create Index (SQL Azure Database). Here is a sample:

   create clustered index idxAddressID on person.address(addressid)

   After you execute a statement, click the Messsage button to make sure the command is completed successfully.

To migrate data from SQL Server to SQL Azure

1. Switch back to the SQL Server Import and Export Wizard window.
2. Click Back until you see Specify Table Copy or Query.
3. Select Write a query to specify the data to transfer, and then click Next.
4. From Provide a Source Query, enter a select statement from one of the tables, and then click Next.
5. From Select Source Table and Views, in the Destination column, select the table you want to migrate the data to, and then click Next.
6. From Reveiw Data Type Mapping, make sur ethe mapping is correct, and then click Next.
7. From Run Package, click Next.
8. From Complete the Wizard, click Finish. Make sure all the actions are completed successfully.
9. Click Close.

Error Messages

• A network-related or instance-specific error occurred while establishing a connection to SQL Server. The Server was not found or was not accessible.
  • Use fully qualified domain name (FQDN) for the data source. For example: [sqlazureservername].database.windows.net.
• Login failed for user 'someone'
  • Use the username in this format: [username]@[SQLAzureServerName].

See Also

• Overview of Options for Migrating Data and Schema to SQL Azure