In this blog I will describe how you can use the Import and Export Wizard to transfer data from an on-premise SQL Server database to an SQL Azure database. What do you need to have to start using the Import and Export wizard.
- Windows Azure subscription
- Create a SQL Azure server and database
- Configure the SQL Azure firewall
- On premise SQL Server with SQL 2008 R2 with SSMS.
- Create the tables you need in the SQL Azure database.
- Create a clustered index on each table to which you want transfer data. Without a clustered index you CAN'T transfer data to a SQL Azure database.
- Choose a datasource. This is your on-premise SQL Server.
- Choose a destination. This will be your SQL Azure database.
- Destination: .NET Framework Data Provider for SqlServer
- Encrypt: True
- Integrated Security: False
- Password: SQL Azure administrator password
- TrustedServer: Certificate: True
- User ID: SQL Azure administrator name. [username]@[sqlAzureservername]
- Data Source: FQDN of your SQL Azure Server
- Inintial Catalog: The SQL Azure database you have created to insert data.
- Press next and execute the same steps as you normally do in the Import and Export wizard.
- In case you forgot to create a clustered index you will get next message:
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.