clausn.dk

Automatically scheduling DB backups in Windows Azure using Mobile services

Fri, 15 Feb 2013 10:47:25 -0100

I’ve been looking for a way to easily do scheduled backups of our databases in Windows Azure, and I’ve finally come across a viable solution.

Scott Guthrie blogged about Windows Azures mobile services now supporting scheduled jobs, that can run at a certain interval. Today I also stumbled upon an article about automating SQL databases in Windows Azure, providing a list of options for download and manual integration into your system. One of them allowed a URL to be called, to initiate the backup of a database. So I thought that putting these two together might be a good idea :)

I created a Mobile Services application called DbBackups and created a scheduled script to run every 1 day at 1 pm. The script looks like this:

   1:  var req = require('request');

   2:  var serviceUrl = "https://db3prod-dacsvc.azure.com/DACWebService.svc//Export";

3:

   4:  function ISODateString(d) {

   5:      function pad(n) { return n < 10 ? '0' + n : n }

   6:      return d.getUTCFullYear() + '-' + pad(d.getUTCMonth() + 1) + '-' + pad(d.getUTCDate()) + '-' + pad(d.getUTCHours()) + '-' + pad(d.getUTCMinutes()) + '-' + pad(d.getUTCSeconds())

   7:  }

8:

   9:  function DoBackup(blobDir, blobKey, dbServer, dbName, dbUsername, dbPassword) {

  10:      var d = new Date();

  11:      var stamp = ISODateString(d);

  12:      var url = blobDir + "/" + dbName + "-" + stamp + ".bacpac";

  13:      var content = "<?xml version='1.0'?><ExportInput xmlns='http://schemas.datacontract.org/2004/07/Microsoft.SqlServer.Management.Dac.ServiceTypes'><BlobCredentials i:type='BlobStorageAccessKeyCredentials' xmlns:i='http://www.w3.org/2001/XMLSchema-instance'><Uri>" + url + "</Uri><StorageAccessKey>" + blobKey + "</StorageAccessKey></BlobCredentials><ConnectionInfo><DatabaseName>" + dbName + "</DatabaseName><Password>" + dbPassword + "</Password><ServerName>" + dbServer + "</ServerName><UserName>" + dbUsername + "</UserName></ConnectionInfo></ExportInput>";

  14:      req.post({

  15:          headers: { 'Content-Type': 'application/xml' },

  16:          url: serviceUrl,

  17:          body: content

  18:      },

  19:      function (error, result, body) {

  20:          if (!error) {

  21:              console.info("Backup started for database '" + dbName + "' to: " + url);

  22:              //console.info("Body: " + body)

  23:          }

  24:          else

  25:              console.info("Error backing up: '" + dbName + "': " + error)

  26:      });

  27:  }

28:

  29:  function BackupDbs() {

  30:      DoBackup(

  31:          "https://youstorageaccount.blob.core.windows.net/sqlbackup",

  32:          "yourverylongkeytostorage",

  33:          "servername.database.windows.net",

  34:          "databaseName",

  35:          "username",

  36:          "password"

  37:      );

  38:      DoBackup(

  39:          "https://youstorageaccount.blob.core.windows.net/sqlbackup/subfolder",

  40:          "yourverylongkeytostorage",

  41:          "servername.database.windows.net",

  42:          "databaseName",

  43:          "username",

  44:          "password"

  45:      );

  46:  }

The above code posts an XML request to the service defined in the top “serviceUrl” variable. The variable could be unique pr. database you have, but for me, all databases are located in the same datacenter. The “serviceUrl” is defined from the list in the section “List of Regions and Endpoints” at Automating Windows Azure SQL Database Backup.
Each requests starts a backup of that particular database into blob storage. The parameters to DoBackup are:

blobDir: Full URL to the container/subfolder that you want to store the backup in. Resulting URL will be concatenated by the URL provided, the database name and a date with .bacpac as postfix. I.e. you submit “https://youstorageaccount.blob.core.windows.net/sqlbackup” and the resulting URL would be “https://youstorageaccount.blob.core.windows.net/sqlbackup/databasename-2013-02-15-12-32.pacpac”.
You can provide a subfolder as well in the URL, see line 39 of the code above.
blobKey: Your blob storage access key
dbServer: Full DNS name for your DNS server. I.e. xkjfsedoij.database.windows.net
dbName: Database name to perform backup of
dbUsername: Username that has access to creating the actual backup, but does not need write permissions.
dbPassword: Password for the username provided

You can get help on the service at the URL: https://db3prod-dacsvc.azure.com/DACWebService.svc/help.

Several errors can occur and the service does not provide very good feedback, if something is wrong. I recommend using fiddler to compose a request, to the service, and see if it works with your parameters. For convenience I’ve provided the information you need to enter, like so:

Post to URL: https://db3prod-dacsvc.azure.com/DACWebService.svc//Export
Body:
<?xml version='1.0'?>
<ExportInput xmlns='http://schemas.datacontract.org/2004/07/Microsoft.SqlServer.Management.Dac.ServiceTypes'>
<BlobCredentials i:type='BlobStorageAccessKeyCredentials' xmlns:i='http://www.w3.org/2001/XMLSchema-instance'>
    <Uri>https://yourstorageaccount.blob.core.windows.net/sqlbackup/userspreprod/dbname-2013-02-15-11-42-35.bacpac</Uri>
    <StorageAccessKey>YouVeryLongKey</StorageAccessKey>
</BlobCredentials>
<ConnectionInfo>
    <DatabaseName>DatabaseName</DatabaseName>
    <Password>DatabasePassword</Password>
    <ServerName>servername.database.windows.net</ServerName>
    <UserName>Username</UserName>
</ConnectionInfo>
</ExportInput>

Have fun!

Social integration annoyance

Tue, 29 Jan 2013 13:38:38 -0100

ItWhen you browse websites today, they usually have some sort of social integration. But the following example is maybe going a bit overboard…

Visiting the site: tapiture.com:

It seems almost 3 quarters of the page is used by the top bar and an annoying “connect with your friends” notification. Granted, I resized the window to better illustrate the issue :)

But lets click the cross in the upper right corner, and lets see what happens:

Yes, the notification is gone!
But wait - a new one appeared? This time with no cross to close it?

Lets have a look at the HTML:

What you’ll notice in the Chrome developer tools, is the ID of the layer annoying me: “annoy-non-users”. Yes, thats right, annoy annoumous user!!

But there is a solution for that:

We use the delete button, and voila:

We can look at images without being disturbed :)

Chrome is actually smart enough to remember our deletion throughout the site! Very cool!

Google apps Calendar support v2

Thu, 10 Jan 2013 11:35:00 -0100

Just got a call from Google apps support, that they were sad to hear of my feedback given to the support issue.

In my last post I was dissatisfied with support saying Windows Phone calendar support was "by design", and that they could not fix it. So I gave them lowest score on the feedback form.. Turns out they take it seriously, since I just got a call from them, that they have now turned it into a feature request :)

All we can do now is wait, since they couldn't give me an ETA for completion :)

Support experience about calendar invites in Windows Phone 7 using Google apps

Mon, 07 Jan 2013 19:14:00 -0100

I use a Windows Phone 7 on a Nokia Lumia 800 and I'm very happy about it. We also use Windows Phones at our work, both Lumia 800 and 920, and all seem happy about their phones.

We cannot however accept, reject or tentatively accept calendar invites from Google calendar, using the native calendar app on our phones. This goes for both Lumia 800 and 920 phones, and therefore both Windows Phone 7 and 8.
Docs at http://www.windowsphone.com/en-us/how-to/wp8/people/use-calendars.

Admittedly, I can accept, reject or "maybe" an invitation in the email sent from Google, but I wanted my calendar to support my provider.

I recently contacted Google apps enterprise support, to confirm I had a known problem and asked for help to solve it. This was my response:
--

Hello Claus,

I have received a reply from a specialist in this field. They have confirmed that the issue you are experiencing is expected behavior. All the online documentation in relation to Windows phone will be updated to make the functions of the Windows Phone calendar app clearer.

The only workaround is to reply using the email buttons inside the invitation which will send you to the web UI version of Google Calendar, which also records the response to the event there. This will also then be reflected in the Calendar app on Windows Phone.

Sincerely,
Bryan
Enterprise Support

Love it when they are able to contact specialists in the field :) But expected behavior?

I then contacted Windows Phone support via Twitter (See conversation here: https://twitter.com/WinPhoneSupport/status/284723826191904768):

‏@WinPhoneSupport Honestly this is something that would have to be changed on Google's side & you would need to contact them regarding any update/change to how the invite is sent to a Windows Phone. ^EB

One of my questions was: "Did you try calling Google to fix this?" and the reply was that it was something Google had to change.
Google support on the other hand explained that they implemented an open ICS standard, which means it would be something they would have to change.

Sounds familiar?

I had the opportunity to grade both Google Apps support and Windows Phone twitter support, and neither got very good grades. The thank you -message from Google Apps feedback survey was execelent:

Thank you for taking the time to provide feedback on this case. Your feedback will be shared with the support representative and their manager. Product feedback is routed directly to our product management team. We take customer feedback seriously and are dedicated to making changes to our products and services based on your input.

Because neither Microsoft or Google wants to help, us Windows Phone users are stuck with no native calendar app for Google apps?