find a location for property in a new city

Tuesday 22 May 2012

Export and back up your SQL Azure databases nightly into blob storage

With Azure I have always believed, if you can do it with the Azure Management Portal then you can do it with a REST API. So I thought it would be a breeze to make an automated job to run every night to export and back up my SQL Azure database into a BACPAC file in blob storage. I was suprised to find scheduling bacpac exports of your SQL Azure databases is not documented in the Azure Service Management API. Maybe it is because the bacpac exporting and importing is in beta? Nevermind. I successfully have a worker role backing up my databases and here's how:

It is a REST API so you can't use nice WCF to handle all your POST data for you but there is a trick still to avoid writing out all your XML parameters by hand and instead strong typing a few classes.

Go to your worker role or console application and add a service reference to your particular DACWebService (it varies by region):

  • North Central US: https://ch1prod-dacsvc.azure.com/DACWebService.svc
  • South Central US: https://sn1prod-dacsvc.azure.com/DACWebService.svc
  • North Europe: https://db3prod-dacsvc.azure.com/DACWebService.svc
  • West Europe: https://am1prod-dacsvc.azure.com/DACWebService.svc
  • East Asia: https://hkgprod-dacsvc.azure.com/DACWebService.svc
  • Southeast Asia: https://sg1prod-dacsvc.azure.com/DACWebService.svc

Once you import this Service Reference you will have some new classes that will come in handy in the following code:

//these details are passed into my method but here is an example of what is needed
var dbServerName = "qwerty123.database.windows.net";
var dbName = "mydb";
var dbUserName = "myuser";
var dbPassword = "Password!";

//storage connection is in my ServiceConfig
//I know these the CloudStorageConnection can be obtained in one line of code
//but this way is necessary to be able to get the StorageAccessKey later
var storageConn = RoleEnvironment.GetConfigurationSettingValue("Storage.ConnectionString");
var storageAccount = CloudStorageAccount.Parse(storageConn);

//1. Get your blob storage credentials
var credentials= new BlobStorageAccessKeyCredentials();
//e.g. https://myStore.blob.core.windows.net/backups/mydb/2012-05-22.bacpac
credentials.Uri = string.Format("{0}backups/{1}/{2}.bacpac",
    storageAccount.BlobEndpoint,
    dbName,
    DateTime.UtcNow.ToString("yyyy-MM-dd"));
credentials.StorageAccessKey = ((StorageCredentialsAccountAndKey)storageAccount.Credentials)
                                   .Credentials.ExportBase64EncodedKey();

//2. Get the DB you want to back up
var connectionInfo = new ConnectionInfo();
connectionInfo.ServerName = dbServerName;
connectionInfo.DatabaseName = dbName;
connectionInfo.UserName = dbUserName;
connectionInfo.Password = dbPassword;

//3. Fill the object required for a successful POST
var export = new ExportInput();
export.BlobCredentials = credentials;
export.ConnectionInfo = connectionInfo;

//4. Create your request
var request = WebRequest.Create("https://am1prod-dacsvc.azure.com/DACWebService.svc/Export");
request.Method = "POST";
request.ContentType = "application/xml";
using (var stream = request.GetRequestStream())
{
    var dcs = new DataContractSerializer(typeof(ExportInput));
    dcs.WriteObject(stream, export);
}

//5. make the POST!
using (var response = (HttpWebResponse)request.GetResponse())
{
    if (response.StatusCode != HttpStatusCode.OK)
    {
        throw new HttpException((int)response.StatusCode, response.StatusDescription);
    }
}

This code would run in a scheduled task or worker role to be set for 2am each night for example. It is important you have appropriate logging and notifications in the event of failure.

Conclusion

This sends off the request to start the back up / export of database into a bacpac file. The success of this is no indication that the back up was successful, only the request submition. If your credentials are wrong you will get a 200 OK response but it the back up will fail silently later.

To see if it has been successful you can check on the status of your exports via the Azure Management Portal, or by waiting a short while and having a look in your blob storage.

I have not covered Importing because, really, exporting is the boring yet important activity that must happen regularly (such as nightly). Importing is the one you do on the odd occassion when there has been a disaster and the Azure Management Portal is well suited to such an occassion.

Follow britishdev on Twitter

42 comments:

  1. Great post and now I know what to do thank you! Actually this Blog post helped me a lot. I hope you continue writing about this kind of entry. http://scarprin.com/

    ReplyDelete
  2. Youre so cool! I dont think Ive read anything like this before. So good to find somebody with some original thoughts on this subject. Thanks for starting this up. This blog is something that is needed on the web someone with a little originality. Good job for bringing something new to the internet!
    http://celabright.com/

    ReplyDelete
  3. Thank you for a great post. Not having an official way to do this from MS is a big hole. One problem: None of the service URLs (...DACWebService.svc) seem to work anymore?

    ReplyDelete
    Replies
    1. Guess I spoke too soon: http://dacguy.wordpress.com/2012/01/24/sql-azure-importexport-service-has-hit-production/

      Delete
  4. I concur with your conclusions and will eagerly look forward to your future updates. The usefulness and significance is overwhelming and has been invaluable to me!
    Work in Dutchess County

    ReplyDelete
  5. Great info. Do you have any info on how exporting affects performance of the database?

    ReplyDelete
  6. This is great stuff. Just implemented on my account.

    Thanks!

    ReplyDelete
  7. Thanks for great post.
    But I am getting Bad request error so please can u give me solution.

    Thanks in advance.

    ReplyDelete
  8. Thank you ! There's no an official way to do this from MS.

    ReplyDelete
  9. Excellent article thanks. The speed of the BACPAC export is unbelievable. Having previously used SQLAzure Migration wizard I had to import the blob to test that it had exported successfully as I didn't quite believe it

    ReplyDelete
  10. I think I will become a great follower.Just want to say your article is striking. The clarity in your post is simply striking and i can take for granted you are an expert on this subject.
    sell house fast

    ReplyDelete
  11. Hi All, i always get WebException after (HttpWebResponse)request.GetResponse() :-( ReceiveFailure or ConnectionClosed. Ex="The underlying connection was closed: An unexpected error occurred on a receive."} Do you have some idea what is wrong ?? It seems that i have all parameters set correctly :-(

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. In my consulting practice, I often encounter people who think those are the only DBMSs worth considering, and they are all just different flavors of the same thing. For more than a decade, I have been helping them past their confusion so they can choose a DBMS intelligently before they build a database.excel reporting dashboard

    ReplyDelete

  14. Very Interesting and wonderful information keep sharing this post kindly check
    Windows Protection

    ReplyDelete
  15. Thank you so much for this blog. If you need Dynamic Website Designing Services, Ogen Inforsystem is best for you.
    Website Designing Company in Delhi

    ReplyDelete
  16. Nice Blog, I got a lot of informative things in this blog. Thank you so much for sharing this blog.
    Mutual Fund Companies

    ReplyDelete
  17. Awesome Blog, Visit for the Music Production Courses in Delhi and all over the India.
    Audio Engineering Course

    ReplyDelete
  18. Motifz Designer Lawn. 1, 2 & 3 Piece Unstitched Premium Embroidered Lawn 2019, Premium Lawn, Lawn 2019, Best lawn collection, Motifz, Best Lawn in Pakistan, Shipping worldwide. Stitching option available.

    ReplyDelete
  19. The best things which you’ll get from TweakBox store are all the Mod and tweaked versions of apps and games. Most of the best things which are about the security of the Tweak Box app.

    ReplyDelete
  20. Buy online wholesale printed lipstick boxes and packaging at easy prices and shipping worldwide, with 24/7 customer support. Get a free custom quote now!!

    ReplyDelete
  21. Buy online wholesale custom cosmetic boxes and packaging at seashore packaging at the easy price Free shipping worldwide with 24/7 customer support.

    ReplyDelete
  22. Blackbird packaging provide you with any size, shape design, custom cosmetic boxes sooner than any other packaging competitor in the market

    ReplyDelete
  23. Packaging town is a one-stop solution for the short-run Custom Packaging, Wholesale candle Boxes needs, and supplies at ease.

    ReplyDelete
  24. mylar label bags in stock now. Ready made Cali pack designs. Fast Shipping at Your Supplies. Custom designs available

    ReplyDelete
  25. Packaging town is a one stop solution for the short-run custom beard oil boxes needs and supplies. At affordable prices. Request a free quote now.

    ReplyDelete
  26. Blackbird packaging provide you with any size, shape design, custom tincture boxes sooner than any other packaging competitor in the market

    ReplyDelete
  27. buy grocery online from alfatah at best prices. get free shipping

    ReplyDelete
  28. buy online jogger pants online from wild goat clothing at best prices. Get Free shipping

    ReplyDelete
  29. Shop your favorite brand's latest and trendy Chiffon Collection 2021 today from SAFFANA store. Available in a wide variety of premium silk chiffon, bemberg chiffon, crinkle chiffon, and embroidered bemberg crinkle chiffon. Shop from a diversified range Premium Chiffon collection available exclusively at SAFFANA store.

    ReplyDelete
  30. Blackbird packaging company can provide you with any size, shape design, and eco friendly bath bomb packaging sooner than any other packaging competitor in the market.

    ReplyDelete
  31. buy baby care product from medisense.pk at best prices delivred at your doorstep. get free shipping

    ReplyDelete
  32. buy dental care products from Medisense.pk delivered at your doorstep at best prices. Get free shipping

    ReplyDelete
  33. buy workout tshirts for men online from dshred at easy prices delivered at your doorstep.get free shipping.

    ReplyDelete
  34. buy makeup online in pakistan from alfatah at best prices delivered at your doorstep. get free delivery

    ReplyDelete
  35. Buy face facial products in pakistan at affordable prices from medisense delivered at your doorstep. Get free shipping.

    ReplyDelete
  36. buy leggings for women online from dshred at easy prices delivered at your doorstep. get free shipping

    ReplyDelete
  37. buy sports bra pakistan online from dshred at easy prices delivered at your doorstep. Get free shipping

    ReplyDelete
  38. Buy men's grooming online from Al-Fatah at best prices delivered at your doorstep. Get free shipping

    ReplyDelete
  39. Bulbul Home offers marble platters online in Australia.

    ReplyDelete