Data Import and export for a SaaS solution

Question

Hi Folks,

For a Multi-tenant SaaS solution hosted on a single SQL Server 2008 R2 instance (many db's and many clients per db). What is the best mechanism for a tenant to import and export data (say to a csv file on a local directory). Assume data migrations are small but frequency is high due to many users. Should I consider running an export through a Stored Procedure, SSIS or dynamic SQL through an ODBC or ADO data provider. The user interacts with the system through a web interface. It has been suggested that MVC and entity framework is the way to go.

Any and all comments are welcome.

Kind regards

Jon

Answer 1

Hi Jon,

For importing and exporting data to SQL Azure database, we always use Data-Tier Application Import and Export with SQL Azure (en-US).  The import and export features provide the ability to retrieve and restore an entire database, including schema and data, in a single file operation.

In your scenario, I prefer using SSIS to import and export csv file.

Some references:

http://blogs.msdn.com/b/benjones/archive/2010/03/08/using-ssis-with-sql-azure-databases.aspx

http://www.youtube.com/watch?v=wAaKvrpK9Uw

---

Iric Wen
TechNet Community Support

Answer 2

Many thanks for the reply and links.

Kind regards

Jon