How to: Use the Import and Export Wizard to Migrate a Database to Windows Azure SQL Database
When migrating a SQL Server database to Windows Azure SQL Database, the SQL Server Import and Export Wizard in an easy way to create a SQL Server Integration Services package to transfer data. The package can then be modified to add more robust error handling and retry logic.
Before you begin:
Recommendations, Limitations and Restrictions, Prerequisites
Use the Import Export Wizard to:
Author: Shaun Tinline-Jones
SQL Server Import and Export Wizard
The SQL Server Import and Export wizard configures the source and destination connections for a package. It then adds any data transformations that are required to perform an export from one of several data sources, such as a SQL Server database, or import to a data source such as Windows Azure SQL Database. You can run the package immediately, save it to run later, or modify the package in SSIS Designer.
The SSIS ADO.NET adapter supports Windows Azure SQL Database. It provides an option to bulk load data specifically for Windows Azure SQL Database. Use the SSIS ADO.NET Destination adapter to transfer data to Windows Azure SQL Database.
Limitations and Restrictions
While the SQL Server Import and Export Wizard can transfer schema information, it only transfers table definitions, not indexes or other constraints. Windows Azure SQL Database requires all tables have a clustered index, so only use the Import and Export Wizard to transfer data. Use another process to transfer the schema, such as the Generate Scripts Wizard or data-tier application (DAC) packages. For more information, see Choosing Tools to Migrate a Database to Windows Azure SQL Database.
A package might fail due to throttling or network issues. Build the package so that it can be resumed at the point of failure instead of having to rerun the entire package after a failure.
Connecting to Windows Azure SQL Database by using OLEDB is not supported.
|On a 64-bit computer, Integration Services installs the 64-bit version of the SQL Server Import and Export Wizard (DTSWizard.exe). However, some data sources, such as Access or Excel, only have a 32-bit provider available. To work with these data sources, you might have to install and run the 32-bit version of the wizard. To install the 32-bit version of the wizard, select either Client Tools or Business Intelligence Development Studio during SQL Server setup.|
The SQL Server Import and Export Wizard installed with the client utilities from SQL Server 2008 R2 or later supports Windows Azure SQL Database.
Migrate Data by Using the Import and Export Wizard
There are several ways to start the wizard, either from the command prompt or from the various SQL Server tools:
On the Start menu, point to All Programs. Point to Microsoft SQL Server 2012, and then click either Import and Export Data (64-bit) or Import and Export Data (32-bit).
In SQL Server Data Tools, right-click the SSIS Packages folder from Solution Explorer, and then click SSIS Import and Export Wizard.
In SQL Server Business Tools, on the Project menu, click SSIS Import and Export Wizard.
In SQL Server Management Studio, connect to the Database Engine server type. Expand Databases, right-click a database, point to Tasks, and then click Import Data or Export data.
In a command prompt window, run DTSWizard.exe. The 64-bit wizard is located in C:\Program Files\Microsoft SQL Server\110\DTS\Binn. The 32-bit wizard is located in C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn.
The migration involves the following main steps:
Choose a data source from which to copy data.
Choose a destination where to copy data to.
To export data to Windows Azure SQL Database, choose the .NET Framework Data Provider for SQLServer as the destination:
Specify table copy or query.
Select source objects.
Save and run the package.
|If you save the package, you must add the package to an existing Integration Services project before you can change the package or run the package in SQL Server Data Tools.|