Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automatically upload SQL Server Database Backups to Azure with Integration Services


By:   |   Updated: 2016-09-14   |   Comments   |   Related: More > Azure

Storage and High Availability Options for SQL Server in the Cloud

Free MSSQLTips Webinar: Storage and High Availability Options for SQL Server in the Cloud

This webinar will cover best practices for optimizing cloud storage and cost, how to leverage the cloud for disaster recovery, availability options and requirements for SQL Server and key factors to consider in your selection.


Problem

I need to backup my local SQL Server databases and upload the backup files to Azure automatically.  Can this be completed in SQL Server Integration Services (SSIS)? Is it also possible to delete the local backup after the file transfer has completed?  Read this tip for the step by step directions.

Solution

In this tip we will demonstrate the Feature Pack in SSIS to connect to Azure to copy your SQL Server database backups.

Requirements

  1. SQL Server 2016 or later.
  2. The SSDT should be installed.
  3. An Azure Subscription.
  4. Internet access.

Introduction

In this demo, we will do the following:

  1. In the Azure Portal, create an Azure Storage Account (a place to store in Azure).
  2. In Microsoft Azure Storage Explorer (MASE), create a BLOB container inside the Azure Storage account (we will explain what is MASE shortly).
  3. In SSIS, create a backup task, use the Azure Blob Upload Task to upload the backup in Azure and delete the local backup using the File System Task.

Getting Started

We will create an Azure Storage Account first in the Azure Portal.

  1. In your Azure Portal, go to New > Data + Storage > Storage Account:


  2. Microsoft Azure Storage Account


  3. Specify a "Name" of your preference. With the Deployment model, use "Resource manager" ("Classic" is the old model). In "Account kind" you can use "General purpose" or "Blob storage". Use "General purpose" if you need several types of storage like message queue, tables, etc. In performance, the cheapest option is "Standard".  The "Premium" option is used when higher performance is required. In replication, the cheapest option is "Locally-Redundant storage (LRS)". You can replicate Geographically, in the same zone or geographically with read only access in the replica. In "Location", specify the location closest to you:

    Microsoft Azure Storage Account Configuration


  4. Once you have an Azure Storage Account, install MASE. MASE is the easiest way to create a Blob Storage. The BLOB (Binary Large Object) storage is a place to store SQL Backups, Videos and other documents. You can Install MASE using the following link: Microsoft Azure Storage Explorer.
  5. Once installed, go to Azure Account Settings and login with your Azure Account:


  6. Microsoft Azure Storage Explorer


  7. Go to the Storage Account created in step 2 and go to Blob Containers:


  8. Microsoft Azure Blob Containers


  9. We will create a Blob Container named bc1 or any name of your preference:


  10. Blob Container named bc1


  11. We will now install Microsoft SQL Server 2016 Integration Services Feature Pack for Azure: SSIS Feature Pack.

    install Microsoft SQL Server 2016 Integration Services Feature Pack for Azure


  12. Once installed the feature pack in the SQL Server Data Tools (SSDT), go to File > New > Project:

    New SQL Server Data Tools Project


  13. Select Integration Services Project:

    New SQL Server Integration Services Project


  14. This Feature Pack installed will show new Tasks to Upload and Download files to an Azure Storage Account and to interact with Hadoop (HDInsight):


  15. New SSIS Tasks to Upload and Download files to an Azure Storage Account and to interact with Hadoop


  16. If you drag a Data Flow Task to the design pane, we will see new Data Flow Tasks installed:

    drag a Data Flow Task to the design pane


  17. In SSIS Toolbox, you will be able to see the Azure Blob Destination and Source. These are the new tasks installed:

    Microsoft Azure Blob Destination and Source


  18. In Control Flow Pane, drag and drop the Back Up Database task to the design pane and disable the Data Flow Task:

    SSIS Back Up Database Task


  19. Drag and drop the Azure Blob Upload Task and join the Back Up Database task with the Azure Blob Upload task:

    Azure Blob Upload Task


  20. Double click on the Back Up Database task and press the New button for the Connection:

    SQL Server Integration Services New Connection for the Back Up Database Task


  21. Add a descriptive Name for the connection then select a SQL Server Name and the authentication method:


  22. SQL Server Connection Properties in SSIS


  23. Select a database to backup (we would recommend a small one if you are just testing):


  24. SQL Server Integration Services Back Up Database Task select the database


  25. In Destination, specify an empty folder to store the SQL Backup:


  26. SQL Server Integration Services Back Up Database Task to specify the backup location


  27. Return to the design pane and double click Azure Blob Upload Task. Click AzureStorageConnection and select New Connection:


  28. SQL Server Integration Services Azure Blob Upload Task Editor to configure a new connection


  29. You need your Storage account name and an Account Key:


  30. Azure Storage Connection Manager Editor


  31. To get the storage name and the account key go to the Azure Portal, All Resources > mssqltips (or the name used in step 2) > Access Keys:


  32. Microsoft Azure All Resource Access Keys


  33. Copy the Storage account and key1:


  34. Microsoft Azure Storage Account Name and Key 1


  35. Paste the information provided in step 22 and Test the connection:


  36. Update the Account and Test in Azure Storage Connection Manager Editor


  37. In the Blob Container specify the name created in step 6 and in a local directory use the path of the backup specified in step 18:


  38. Azure Blob Upload Task Editor Completed


  39. We will use the File System task to delete the local backup once it is in Azure. This is not a recommended task for most situations, but I just wanted to demonstrate this task that is not often used which can be very useful:


  40. SQL Server Integration Services File System Task to delete the local SQL Server back up file


  41. We will delete the contents of the folder (in this case, the local SQL backup) using the Delete directory content option and create a new connection to specify the path of the directory:


  42. SQL Server Integration Services File System Task Editor


  43. Specify the folder where the backup is stored specified on step 18:


  44. SQL Server Integration Services File Connection Manager Editor


  45. Start the package:


  46. Start the SQL Server Integration Services Package


  47. If the SSIS Package runs correctly, in MASE you will be able to see the backup after refreshing the Blob Container:


  48. in MASE you will be able to see the backup after refreshing the Blob Container

Conclusion

In this tip, we learned how to create an Azure Storage Account in the Portal, we installed MASE to create a Blob Container and finally we installed the SSIS feature pack for Azure to automatically upload our SQL Backup to Azure.

Next Steps

For more information, refer to the following links:



Last Updated: 2016-09-14


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools