Click here to Skip to main content
15,867,330 members
Articles / Hosted Services / Azure

SQL Azure Import-Export Service / bacpac / dac / Extract Dat-Tier Application

Rate me:
Please Sign up or sign in to vote.
4.90/5 (3 votes)
21 Nov 2011CPOL4 min read 25.7K   3   3
How to use the Dac Export Service from code

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.

C#
public class BackupUsingDacService
    {
        public BackupUsingDacService(Credentials credentials)
        {
            _Credentials = credentials;
        }

        private Credentials _Credentials;
        const string _EndPoint = 
    "https://ch1prod-dacsvc.azure.com/DACWebService.svc"; // North Central US
        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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
Co-director of Chabad Student Center @ SUNY New Paltz
Lecturer of Computer Science @ SUNY New Paltz

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:21
professionalKanasz Robert24-Sep-12 6:21 
Interesting article
QuestionGreat Work Rabb! Pin
Pat Tormey28-Nov-11 10:33
Pat Tormey28-Nov-11 10:33 
AnswerRe: Great Work Rabb! Pin
Rabb Moshe Plotkin28-Nov-11 15:47
Rabb Moshe Plotkin28-Nov-11 15:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.