This article show you how to connect to Windows Azure SQL Database using SQL Server Import and Export Wizard. Once you understand how to connect to SQL Database, you can migrate data between SQL Database 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.  Because SQL Database only supports 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 Database data migration options, see Overview of Options for Migrating Data and Schema to Windows Azure SQL Database.

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

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 Database server and database. For more information, see Getting Started with Windows Azure SQL Database using the Windows Azure Platform Management Portal.
• Configuring SQL Database firewall to allow the computer running the wizard to connect to the SQL Database server. For more information, see Getting Started with Windows Azure SQL Database 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 Database. As stated in the beginning 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 Database from the wizard.

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

To migrate schema from SQL Server to SQL Database

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 Database administrator password)
   TrustServerCertificate	True
   User ID	(Enter the SQL Database administrator username in the format of [username]@[sqlazureservername])
   Data Source	(Enter the FQDN of your SQL Database 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 (Windows Azure SQL Database).

4. From Specify Table Copy or Query, select Copy data from one or more tables or views, and then click Next.
5. From Select Source Tables and Views, select the table that you want to migrate to SQL Database, 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 Database 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 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 Database 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 browser 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 clustered index for each of the tables you want to migrate. For more information on using the create index T-SQL statement, see Create Index (Windows Azure SQL 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 Database

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 Review Data Type Mapping, make sure the 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 Windows Azure SQL Database