Share via

Windows Azure SQL Database Data Migration Using SQL Server Import and Export Wizard

  • Article

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.

Note

If you wish to contribute to this page, use the Edit tab at the top (sign-in required). If you wish to provide feedback for this documentation please either send e-mail to azuredocs@microsoft.com or use the Comment field at the bottom of this page (sign-in required).

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:

Preparations

You must complete the following steps:

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