locked
How to export data (SQL Query) from Azure SQL Db to Blob storage RRS feed

  • Question

  • Hi All,

    I was looking for the best way to automate a nightly data export (from a SQL Select statement) to a csv file in blob storage.  I can spin up a worker role and do this, but is there a better way?

    Thanks,

    George

    Monday, November 4, 2019 12:32 PM

Answers

All replies

  • Hi 

    There are definitely different methods for exporting the data from Azure SQL DB which have been well documented here:

    https://docs.microsoft.com/en-us/sql/relational-databases/import-export/overview-import-export?view=sql-server-ver15

    But I guess the main question is how to automate the same. There are various methods to do so:

    1) Azure Data Factory - You can use the Integration runtime to acheive this but looking at your use ADF could be a big solution. 

    https://docs.microsoft.com/en-us/azure/data-factory/concepts-integration-runtime

    2) Azure Automation - You can use the Azure Automation Account runbook feature to acheive this. This Document explains this in detail.

    https://docs.microsoft.com/en-us/azure/automation/automation-intro
    https://blogs.msdn.microsoft.com/azuresqlemea/2017/02/07/automate-export-azure-sql-db-to-blob-storage-use-automation-account/

    3) Elastic Jobs - As the SQL Agent is not there in Azure SQL DB for the automating Day to Day tasks, Azure Elastic jobs can be used for the same. Here are details for the same.

    https://docs.microsoft.com/en-us/azure/sql-database/elastic-jobs-overview

    Hope this helps. Please get back to us for any questions.

    Thanks
    Navtej S

    Monday, November 4, 2019 6:19 PM
  • https://medium.com/azure-sqldb-managed-instance/migrate-from-azure-sql-managed-instance-using-bcp-674c92efdca7

    bcp dbname.schema.table out C:\temp\bcp\fileEXPORT.bcp  -S"AZURESERVER.database.windows.net" -n -U"***" -P"***"

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 5, 2019 5:45 AM
  • Thanks Navtej and Uri.  All good information.  What I ended up doing was I added a GenerateReport to my API web server in Azure and then calling it with Azure Automation once a day.  I can pass it any kind of sql (dynamic or stored proc) (i.e.

    SELECT emp.EmployeeID, emp.ManagerID, con.FirstName, con.LastName
      FROM [HumanResources].[Employee] emp
      JOIN [Person].[Contact] con  ON emp.[ContactID] = con.[ContactID]

    ) and even do a fan out query across x number of Azure SQL DB and combine the results into a single csv file and push it to blob storage or return it directly to the caller.

    So using Automation and Web Role work well.  I imaging that Azure Data Factory could do that as well.  Might be fun to experiment with that and compare the cost of solutions.

    If I want to do this on a local machine, I wrote a program called SQLAzureMW that can do this as well.  Basically, what I can do there is execute a sql command such as this:

    IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.MyTable'))
    BEGIN
        DROP TABLE dbo.MyTable
    END
    SELECT emp.EmployeeID, emp.ManagerID, con.FirstName, con.LastName
      INTO dbo.MyTable
      FROM [HumanResources].[Employee] emp
      JOIN [Person].[Contact] con  ON emp.[ContactID] = con.[ContactID]

    My query results is stored into MyTable and then SQLAzureMW uses BCP to pull that data to my local machine and then drop the table if I want.

    Anyway, I was interested if there was a more efficient way to export query data from Azure SQL Db to Azure Blob storage from Azure services.  I will take a look at ADF.

    Thanks!

    George

    Tuesday, November 5, 2019 2:35 PM
  • Hi George

    You are already on the right path. SQL Business Intelligence has always been a neat product in On premise SQL server to perform various ETL tasks. ADF helps you to achieve the same in cloud.

    Here is another document that can help:

    https://docs.microsoft.com/en-us/azure/data-factory/tutorial-deploy-ssis-packages-azure

    Thanks
    Navtej S

    • Marked as answer by ByGeo Tuesday, November 5, 2019 4:36 PM
    Tuesday, November 5, 2019 3:18 PM