Steps for Deploying Package to Azure Environment:

 

Step 1: Creating Storage Account

Before deploying the package to windows azure, we required to create Azure Storage service account for storing the Input XML and logging information files.

Input XML is used for informing the Worker Role about the list of databases with service name, user id, password for login to the database and where the backup files to be stored etc. There are two log files are used in the Worker Role, one is the Action Log and another is Error Log. These log files can be used for tracing what happened while doing these activities. So let’s first create a Storage Account.

 

  1. Open the Azure Management Portal. url - http://windows.azure.com/
  2. Select the Hosted Services, Storage Accounts & CDN and select Storage Accounts from the left panel. Create a new storage account by selecting the subscription and New Storage Account icon. (Here I created storage Account Name as tsqlazurebackup)
  3. Once the Status of the Storage Account become Created, select the storage account and press the View button from the Properties. Copy the Primary Key somewhere temporarily.
  4. Create a Blob Container in the Storage Account. You can use either Windows Azure Management Tool (http://wapmmc.codeplex.com/) or Azure Storage Explore (http://azurestorageexplorer.codeplex.com/) for doing the same.
  5. Note the following information which will be used in next step.

Storage Account Name: tsqlazurebackup

Storage Access Key: ydGlhdyxrbK41hJTARbUprhLKXUS6ZmsMc1RazC94RDwq/LFlIaLsUXoNmOiQyiW+mIyn6TT0yiEomgw3cD9dw==

Storage Blob Container Name: sqlazurebackup

(Note: This information I used when publishing into my subscription for temporary purpose, this won’t work future)

 

Step 2: Updating Storage Account information in the configuration file

  1. Download the package from the Download Page and extract it. There will be two files in the .zip file.
    • ServiceConfiguration.Cloud.cscfg – Is the configuration file, which hold the configuration for the storage account and the logging informations.
    • SQLAzureBackup.cspkg – Is the worker role package file has the logic to backup.
  2. Open the ServiceConfiguration.Cloud.cscfg file and change the DataConnectionString, InputXMLBlobConnectionString settings value from UseDevelopmentStorage=true to the storage account connection string in the below format.

    DefaultEndpointsProtocol=http;AccountName=<<StorageAccountName>>;AccountKey=<<PrimaryAccessKey>>

    So for the Storage Account I created, the connection string would be

    DefaultEndpointsProtocol=http;AccountName=tsqlazurebackup;AccountKey=ydGlhdyxrbK41hJTARbUprhLKXUS6ZmsMc1RazC94RDwq/LFlIaLsUXoNmOiQyiW+mIyn6TT0yiEomgw3cD9dw==

    Below shows the DataConnectionString, InputXMLBlobConnectionString setting nodes –

    <Setting name="DataConnectionString" value="DefaultEndpointsProtocol=http;AccountName=tsqlazurebackup;AccountKey=ydGlhdyxrbK41hJTARbUprhLKXUS6ZmsMc1RazC94RDwq/LFlIaLsUXoNmOiQyiW+mIyn6TT0yiEomgw3cD9dw==" />

    <Setting name="InputXMLBlobConnectionString" value="DefaultEndpointsProtocol=http;AccountName=tsqlazurebackup;AccountKey=ydGlhdyxrbK41hJTARbUprhLKXUS6ZmsMc1RazC94RDwq/LFlIaLsUXoNmOiQyiW+mIyn6TT0yiEomgw3cD9dw==" />

  3. Replace the InputXMLBlobContainerName settings value to the container name created in Step 1.3. As I have sqlazurebackup as the container name, the settings would as below –

    <Setting name="InputXMLBlobContainerName" value="sqlazurebackup" />

  4. There are two other settings present in the configuration file which inform the worker role to log the action details and error details.

Change the LogError to True to log error occurred while taking the backup and False not required.

<Setting name="LogError" value="True" />

The same applied to the action details also

<Setting name="LogAction" value="True" />

At the last my configuration file would as below

<?xml version="1.0" encoding="utf-8"?>
<ServiceConfiguration serviceName="SQLAzureBackup" xmlns="http://schemas.microsoft.com/ServiceHosting/2008/10/ServiceConfiguration" osFamily="2" osVersion="*">
  <Role name="WorkerRole">
    <Instances count="1" />
    <ConfigurationSettings>
      <Setting name="Microsoft.WindowsAzure.Plugins.Diagnostics.ConnectionString" value="UseDevelopmentStorage=true" />
      <Setting name="DataConnectionString" value="DefaultEndpointsProtocol=https;AccountName=tsqlazurebackup;AccountKey=ydGlhdyxrbK41hJTARbUprhLKXUS6ZmsMc1RazC94RDwq/LFlIaLsUXoNmOiQyiW+mIyn6TT0yiEomgw3cD9dw==" />
      <Setting name="LogError" value="True" />
      <Setting name="LogAction" value="True" />
      <Setting name="InputXMLBlobConnectionString" value="DefaultEndpointsProtocol=http;AccountName=tsqlazurebackup;AccountKey=ydGlhdyxrbK41hJTARbUprhLKXUS6ZmsMc1RazC94RDwq/LFlIaLsUXoNmOiQyiW+mIyn6TT0yiEomgw3cD9dw==" />
      <Setting name="InputXMLBlobContainerName" value="sqlazurebackup" />
    </ConfigurationSettings>
  </Role>
</ServiceConfiguration>

 

 

Step 3: Changing the input XML

Input XML file is used for providing input to the worker role about the list of databases to take backup with login information and the place where the backup to be stored etc.

  1. Create a new xml file in any of your favorite XML editor or note pad with file name as SQLAzureBackupData.xml. If you create using notepad, save the file as XML (save the file name with double quote – “SQLAzureBackupData.xml”)
    (Note: As of now the code will look at the SQLAzureBackupData.xml from the container of the storage service configured. I will be modify the code to configure the file name also from the next version)
  2. Copy and paste the following script in the XML file and modify the list of databases as you required.

The database backup can be in two methods

  • Backup to storage account – The database backup file will be stored in blob storage with predefined formatted name. The storage account details such as storage account name, access key, and container name must be provided when preferring the backup to storage account.

    Note: There is no link between the storage account specified in this Database node and the storage account specified in the ServiceConfiguration.Cloud.cscfg configuration file. The storage account configured in Database node will be used for storing database backup and the storage account configured in the ServiceConfiguration.Cloud.cscfg file used for storing the input XML file and logging files. So each database node can refer different storage account or can be same.

  • Backup to File System – The database backup will be stored in a drive, which can be to a local system, a network drive. This method can be used when the worker role runs on on-premise server. Currently I did not verify the Azure Drives, will be updated in future.

Below picture shows the XML structure for both of the backup methods

inputXML

The attributes of Database node explained below.

    • ServerName – Specifies the SQL Azure Server Name – Required. ( For Ex: xyzabcdef.database.windows.net)
    • DatabaseName – Specifies the SQL Azure database name – Required.
    • UserWindowsAuth – does the authentication uses windows authentication for login to the database? – Required.
    • LoginId – SQL Azure login Id – Required.
    • Password – Password for login – Required.
    • ExportTo – Specifies whether the backup required being stored in Blob storage or File System – Required. (Two values acceptable – Blob or FileSystem – For Ex: ExportTo=”Blob” or ExportTo=”FileSystem”)
    • BackupFileName – Specifies the file name of the backup file – Required.
    • The name can have @Date and @Time for specifying the date and time in the file name will be replaced in ddMMMyyy and hhmmss format. For Example if the file name is Customers_@Date_@Time.bacpac, then the file name will be replaced in the code to Customer_17Oct2011_072112.bacpac.
    • StorageAccountName – The blob name for storing the backup file (Required only if ExportTo=”Blob”)
    • BlobContainerName – The storage account container name (Required only if ExportTo=”Blob”)
    • BlobDefaultEndpointsProtocol – The protocol on the blob can be accessible (http / https) (Required only if ExportTo=”Blob”)
    • StorageAccessKey – The Access Key for the storage account (Required only if ExportTo=”Blob”)
    • ExportFileDirectory – The directory where the exported file will be stored (Required only if Export=”FileSystem”)
    • TrustServerCertificate – Whether trust the server certificate? (Yes – true, No - false)
    • UseSSL – Required to use SSL? (Yes – true, No – false)
    • BackupScheduledPeriod – Specifies the period of backup interval - Required. (Currently the value will be “Daily” only – For Ex: BackupScheduledPeriod=”Daily”)
    • BackupScheduledTime – Specifies the time of the backup – Required. The backup will be taken on or before the hour and minute defined here. (For Ex: if the backup time at 12:00 AM - BackupScheduledTime=”12:00 AM”, the backup will start when the time trigger 12:00 AM each day.)
    • LastBackupDateTime – Specifies the date and time of last backup completed. This is used to track the interval period. So to take backup on today, the LastBackupDateTime value must be on yesterday. This value will be updated automatically, but at initial this value must be updated manually.

You can have required attributes for the required backup you choose in the Database node. The following XML shows an example of my XML file which I used for testing on cloud.

<?xml version="1.0" encoding="utf-8"?>
<Databases>
    <Database ServerName="j6gdqs61uk.database.windows.net"
              DatabaseName="Customers" 
              UseWindowsAuth="false" 
              LoginId="Thiru" 
              Password="welcomedb@123" 
              BackupFileName="Customers_@Date_@Time.bacpac" 
              BackupScheduledPeriod="Daily" 
              BackupScheduledTime="1:57 PM" 
              LastBackupDateTime="08-Nov-2011 06:18 PM" 
              ExportTo="Blob" 
              ExportFileDirectory="D:\DatabaseBackup\@Date" 
              TrustServerCertificate="false" 
              UseSSL="false" 
              BlobName="dbbackups" 
              BlobContainerName="customerdb" 
              BlobDefaultEndpointsProtocol="http" 
              BlobAccessKey="h0/hPhKGH3UJJQf/YDRgmWpCK60ZaXCTvxgqG9JgEG459YXYVIeXZg5zqRIA4kOA7ybVBcZZuA5PDkeSyWO/4w=="></Database>
</Databases>

Upload the XML file to the container of the storage account configured in the ServiceConfiguration.Cloud.cscfg. The worker role will start execution and when the server time meets the time mentioned in the XML file for any of the Database node with the date it will start the backup.

Currently the backup time will be considered as per the time running on the server instance. but I will work on this to standardize the timing for various zone. But as of now the backup schedule will be handled as below.

Say, we have required the backup to be done from tomorrow (01-June-2012) and the backup should be done by 12:00AM on daily basis. The values should be

BackupScheduledPeriod="Daily"

BackupScheduledTime="12:00 AM"

LastBackupDateTime="31-May-2012 12:00 AM"

When the worker role executing on 01-Jun-2012 at 12:00 AM, it will check whether the last backup date in XML equal to 31-May-2012 or less than that date. It will also check the backup scheduled time is 12 hours and minutes is less than compare to the current minute on the system. If this conditions are meet, the backup schedule will start and it will update the last backup date time to the current date time.

Upload the XML file to the blob container of the storage account configured and verify the backup file in the targeted location specified in the input XML.

More Information

The DAC whitepaper has a good overview of the DAC framework.

For more information about Import and Export on SQL Azure database can found in my blog (http://dotnettwitter.com/).

For any issues, please log into the Issue Tracker and please let me know if any change/enhancement with this tool. Love to know any positive and negative feedback…

Last edited May 30, 2012 at 3:22 PM by thirumalai_pm, version 3

Comments

thirumalai_pm Oct 16, 2012 at 6:49 AM 
Hi lswormnd, Could you please lete me know how you formatted the XML input file. If possible send me the file by removing all confidential information. I will look that and comeback to you. Hope there could be issue with datatime you mentioned.

lswormnd Sep 27, 2012 at 7:47 AM 
And now, SQL Azure's 'MicrosoftServices 0.0.0.0 0.0.0.0' rule is on.

lswormnd Sep 27, 2012 at 7:40 AM 
I've got some messages like below.
I repeatedly checked the connection information.
How can I do something?

2012_27 12520535841203619789_3a49dce5-306f-4795-8a57-ed6a7c7fb238 2012-09-27 오전 3:31:18 Validation failed for database markmount@lv7ztzbp03.database.windows.net 2012-09-27 오후 12:31:19
2012_27 12520535841205807541_62792430-715f-4e0e-8bc2-9a81e2cdd302 2012-09-27 오전 3:31:17 Validation
LastBackupDateTime : 27-Sep-2012 <= 27-Sep-2012
BackupScheduledHours : 1 == 3
and Minutes: 57 <= 31 2012-09-27 오후 12:31:19
2012_27 12520535841207682757_8a7a2f33-e5c0-4254-a611-ecb8e100a8c2 2012-09-27 오전 3:31:17 Starting Process for markmount@lv7ztzbp03.database.windows.net 2012-09-27 오후 12:31:19
2012_27 12520535841215808693_a20b6d06-d040-4e3c-bbda-faf038e9f08e 2012-09-27 오전 3:31:17 Starting the Backup Process 2012-09-27 오후 12:31:18

thirumalai_pm May 31, 2012 at 1:22 PM 
Hi harid, Yes, the input XML file name is SQLAzureBackupData.xml, that file name is fixed in the code.

whippo - You still getting this issue? please send a message if you are still facing. I will work to correct the issue. Thanks..

harid May 17, 2012 at 7:31 AM 
What should be the name of the XML file that is being uploaded to storage?I could see it is SQLAzureBackupData.xml from the screenshot you've attached... But is it correct file name that worker role is looking for?

whippo May 9, 2012 at 2:02 PM 
any idea on why i would be getting this error?
Could not load file or assembly 'Microsoft.SqlServer.SqlClrProvider, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
at Microsoft.SqlServer.Management.Common.ConnectionManager.CallerHavePermissionToUseSQLCLR()
at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor()
at Microsoft.SqlServer.Management.Common.ServerConnection..ctor()
at WorkerRole.WorkerRole.GetServerConnection(DatabaseInfo databaseInfo) in C:\Thiru\KM\Backup\Azure\Azure\Backup\SQLAzureBackup Home\SQLAzureBackup\WorkerRole\WorkerRole.cs:line 364
at WorkerRole.WorkerRole.BackupToBlob(DatabaseInfo databaseInfo) in C:\Thiru\KM\Backup\Azure\Azure\Backup\SQLAzureBackup Home\SQLAzureBackup\WorkerRole\WorkerRole.cs:line 220