If you’re moving existing workloads into SQL Azure or are developing on premise but move production environments to SQL Azure. Here’re few tools to choose from when transferring schema and data from SQL Server to SQL Azure:
- Generate Script Wizard (GSW) is available through Management Studio 2008 R2, and provides grained control on what to script. It can also move data, especially if you’re looking to move small amounts of data for one time.
- DAC Packages are a self contained package of all database schema as well as developers deployment intent so they do more than just move schema between SQL Server and SQL Azure but they can be used for easy transfer of schema between SQL Server and SQL Azure. You can use DACPacs pre or post deployment scripts to move data.
- SQL Server Integration Services provides full development lifecycle support with great debugging experience. Beyond SQL Server, It can work with diverse set of data sources and destinations for data movement. SSIS also supports easy-to-use utilities like Import & Export Wizard so can be a great powerful tool to move data around. You can access Import and Export Wizard directly from SQL Server 2008 R2 folder under start menu.
- BCP & Bulk Copy utility is both a tool (bcp.exe) and API (System.Data.SqlClient.SqlBulkCopy) to move structured files in and out of SQL Server and SQL Azure.