Automatic SQL Azure backup part 1

by Rok Bermež 18. November 2011 14:24
Backup for SQL Azure was one of the most voted-on features since the beginning. Sure, we had SQL Migration wizard, BCP, SSIS, PowerShell cmdlets from Cerebrata , and later a very nice tool from RedGate (that I still use a lot) - SQL Azure backup. All of them have one flaw, there are either impossible or very hard to use for automatic backups that require no on premises infrastructure.
For a while now, Import and Export CTP functionality has been available through Windows Azure management portal, that exports or imports Sql DacPac package. This is the exact functionality that should be integrated with my SQL Azure using cloud applications.
MSDN documentation for that REST API seems to be completely lacking, but fortunately there are some SQL DAC Examples on CodePlex project that can we can use to see how it’s done.
First, we add a service reference to http://dacdc.cloudapp.net/DACWebService.svc/mex and generate required proxy classes.

Now we can make WebRequests for specific actions (import,export, status) to URLs, that are specific to each Windows Azure datacenter. Here is the mapping:

RegionUrl
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

 

Export

To create a backup to storage we need to create an object of type ExportInput and POST it to datacenters url + “/export”.

 

public Guid ExportToStorage(string storageContainer, string fileName, BlobContainerPublicAccessType blobContainerPublicAccessType = BlobContainerPublicAccessType.Off)
        {
            var blobCredentials = GetBlobCredentials(storageContainer, fileName, true, blobContainerPublicAccessType);
            var exportRequest = new ExportInput
            {
                BlobCredentials = blobCredentials,
                ConnectionInfo = _connectionInfo
            };
            var req = GetRequest(new Uri(_dataCenterUrl + "/Export"), RequestMethod.POST);
            var serializer = new DataContractSerializer(typeof(ExportInput));
            var requestStream = req.GetRequestStream();
            serializer.WriteObject(requestStream, exportRequest);
            requestStream.Close();
            var resp = req.GetResponse();
            return GetGuidFromResponse(resp);
        }
        private BlobStorageAccessKeyCredentials GetBlobCredentials(string storageContainer, string fileName, bool createIfNotExist = false, BlobContainerPublicAccessType blobContainerPublicAccessType = BlobContainerPublicAccessType.Off)
        {
            var storageCredentials = new StorageCredentialsAccountAndKey(_storageConnectionInfo.AccountName, _storageConnectionInfo.AccountKey);
            var storageAccount = new CloudStorageAccount(storageCredentials, _storageConnectionInfo.UseHttps);
            var cloudBlobClient = storageAccount.CreateCloudBlobClient();
            var cloudBlobContainer = cloudBlobClient.GetContainerReference(storageContainer);
            if (createIfNotExist)
            {
                if (cloudBlobContainer.CreateIfNotExist())
                {
                    var permissions = cloudBlobContainer.GetPermissions();
                    permissions.PublicAccess = blobContainerPublicAccessType;
                    cloudBlobContainer.SetPermissions(permissions);
                }
            }
            var cloudBlob = cloudBlobContainer.GetBlobReference(fileName);
            var backupBlobUri = cloudBlob.Uri.ToString();
            var blobCredentials = new BlobStorageAccessKeyCredentials
            {
                StorageAccessKey = _storageConnectionInfo.AccountKey,
                Uri = backupBlobUri,
            };
            return blobCredentials;
        }
        private HttpWebRequest GetRequest(Uri uri, RequestMethod requestMethod)
        {
            var req = (HttpWebRequest)WebRequest.Create(uri);
            req.Method = requestMethod.ToString().ToUpper();
            req.ContentType = "application/xml";
            return req;
        }

Import

For import the process is very similar, we have object of type ImportInput and POST it to datacenters url + “/import”.

 

 public Guid ImportFromStorage(string storageContainer, string fileName, SqlAzureEdition sqlAzureEdition = SqlAzureEdition.Web, SqlAzureSize sqlAzureSize=SqlAzureSize.GB_1, string newDbName=null)
        {
            var blobCredentials = GetBlobCredentials(storageContainer,fileName);
            ImportInput importRequest = new ImportInput();
            BlobCredentials creds = blobCredentials;
            importRequest.BlobCredentials = creds;
            importRequest.AzureEdition = sqlAzureEdition.ToString().ToLower();
            importRequest.DatabaseSizeInGB = (int)sqlAzureSize;
            importRequest.ConnectionInfo = (String.IsNullOrEmpty(newDbName)) ? _connectionInfo : new ConnectionInfo() { DatabaseName = newDbName, ServerName = _connectionInfo.ServerName, UserName = _connectionInfo.UserName, Password = _connectionInfo.Password};
            var req = GetRequest(new Uri(_dataCenterUrl + "/Import"), RequestMethod.POST);
            var serializer = new DataContractSerializer(typeof(ImportInput));
            var requestStream = req.GetRequestStream();
            serializer.WriteObject(requestStream, importRequest);
            requestStream.Close();
            var resp = req.GetResponse();
            return GetGuidFromResponse(resp);
        }

Status

Both actions return GUID representing current action that we can use to check if operation was successful. We do this by making GET request to datacenters url + “/status? servername={0}&username={1}&password={2} &reqId={3}”. If we want to get history and their statuses for this datacenter we can make the same request without reqId.

 

        public StatusInfo GetStatusInfo(Guid requestId)
        {
            string uriBuilder = _dataCenterUrl + string.Format("/Status?servername={0}&username={1}&password={2}&reqId={3}", _connectionInfo.ServerName, _connectionInfo.UserName, _connectionInfo.Password, requestId.ToString());
            var req = GetRequest(new Uri(uriBuilder), RequestMethod.GET);
            var response = req.GetResponse();
            var statusInfos = GetStatusInfoFromResponse(response);
            return statusInfos[0];
        }

 

Here ( SqlAzure.rar (22,71 kb) ) you can download a complete library that you can use in your Azure Worker tasks to automatically back up you SQL Azure databases. Next.... how to create cheduler that uses it.

 

Tags:

c# | Azure | SQL

Add comment

Calendar

<<  October 2017  >>
MonTueWedThuFriSatSun
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar

Page List

Month List