Introduction
It goes by many names, but to me it is one thing ...
The much needed SQL Azure backup solution that has been so sorely missing since SQL Azure launched.
At first, they tried telling us that there is no need for backups because their datacenters are so secure that nothing could ever go wrong, and they could be correct - they will never lose our data. BUT - and this is a big but - one of the main reasons we backup data is to protect against human error. So unless we could create versioned backups, our data was vulnerable.
There were many quazy-solutions. SqlAzureMW, Sync Framework, etc. but none of them allowed you to schedule a backup or give your users a way to create an instantaneous backup before they do some action that could corrupt data. Until now ... I am hoping that this new Export service might finally be what we need.
Before We Start
It appears that the service - which is an http endpoint that exports your entire database to an Azure storage account - needs to be run be the "server-level principal login (created by the provisioning process)". There are many documents that suggest that there is some combination of permissions that would allow another user to execute the command and trigger a backup. However I have not been able to get any other user to be able to execute it. Maybe because most of those documents (including the http error returned by the server) seem to be written for denali (primarily the on-premises version). Even if I could get it to work, I would not want to give those permissions to any user using the database interactively - much less every user.
That leaves us with the only option being creating a service on a secure server that will execute the command to trigger the backup using my personal login. Being that the location of the backups will be hardcoded into the service (i.e. not a parameter) together with my Azure storage access key. I can be pretty comfortable allowing anyone to trigger a backup without being concerned with it being a security risk. As I can know exactly where those bacpacs are going and that they cannot be redirected into the wrong hands.
That being said... how do you call this http endpoint?
The Good Stuff
Redgate has a free utility that takes your parameters and issues the call on your behalf. They even have a command prompt that you could put in a batch file. but what if you want to include it in your application (or run it in your service)?
The Dac team put out a sample project on CodePlex but no documentation.
I found the sample project to be too complex to show the basic ideas. So I put something together for anyone who is trying to figure it out.
The service has a general endpoint http://dacdc.cloudapp.net/DACWebService.svc and some region specific endpoints like north central us - https://ch1prod-dacsvc.azure.com/DACWebService.svc.
The service is a REST like service so you cannot just use WCF to call straight into it. You will need to use an http library. However the service requires the parameters to be passed in as an XML document. The easiest way to create XML in the proper format is probably to download the contracts from the service.
You can do that by using svcutil.exe or by "Add A Service Reference" in VS. Either of those tools will create proxy wrappers for the data contracts in the service. Neither of them will create the app.config sections that usually come along with WCF client proxies (because the service is not a WCF service in that sense). Neither will their operation contract proxies be of any use.
Once you have the classes matching the Datacontracts, the following C# snippet should be all that you need.
I restructured the CodePlex project so that the logic is more direct and I stripped out all of the parts that distracted from the example.
I also used the task library to wrap the async pattern which I find to be more understandable and this way it is ready for the asnyc and await keywords in C# 5.
public class BackupUsingDacService
{
public BackupUsingDacService(Credentials credentials)
{
_Credentials = credentials;
}
private Credentials _Credentials;
const string _EndPoint =
"https://ch1prod-dacsvc.azure.com/DACWebService.svc";
const string _DbServerName = "###.database.windows.net";
public Task<Task<Guid>> Backup()
{
var ImportExport = new DacImportExport.ExportInput
{
BlobCredentials = new DacImportExport.BlobStorageAccessKeyCredentials
{
Uri = "http://###.blob.core.windows.net/sqlbackup/coc_" +
DateTime.Now.ToString() + ".bacpac",
StorageAccessKey = "###"
},
ConnectionInfo = new DacImportExport.ConnectionInfo
{
ServerName = _DbServerName,
DatabaseName = _Credentials.InitialCatalog,
UserName = _Credentials.UserName,
Password = _Credentials.Password
}
};
var req = (HttpWebRequest)WebRequest.Create
( new Uri( _EndPoint + "/Export" ) );
req.Method = "POST";
req.ContentType = "application/xml";
return Task.Factory.FromAsync<Stream>
( req.BeginGetRequestStream, req.EndGetRequestStream, req )
.ContinueWith( ar =>
{
DataContractSerializer serializer =
new DataContractSerializer( typeof( DacImportExport.ExportInput ) );
serializer.WriteObject( ar.Result, ImportExport );
ar.Result.Close();
var req2 = ar.AsyncState as HttpWebRequest;
return Task.Factory.FromAsync<WebResponse>
( req2.BeginGetResponse, req2.EndGetResponse, req2 )
.ContinueWith<Guid>( ar2 =>
{
Guid resultGuid = Guid.Empty;
try
{
Encoding enc = Encoding.GetEncoding( 1252 );
using (var responseStream = new StreamReader
( ar2.Result.GetResponseStream(), enc ))
{
using (XmlDictionaryReader reader =
XmlDictionaryReader.CreateTextReader
( responseStream.BaseStream,
new XmlDictionaryReaderQuotas() ))
{
DataContractSerializer dcs =
new DataContractSerializer( typeof( Guid ) );
resultGuid = (Guid)dcs.ReadObject( reader, true );
}
}
ar2.Result.Close();
} catch (WebException ex)
{
if ((ex.Response != null) &&
(ex.Response is HttpWebResponse))
{
HttpWebResponse response =
ex.Response as HttpWebResponse;
Console.WriteLine( "Service Error: {0} {1} ",
response.StatusCode, response.StatusDescription );
} else
{
Console.WriteLine( "Service Error: {0}", ex.Message );
}
}
return resultGuid;
} );
} );
}
History
- 21st November, 2011: Initial post
