This technical guidance is for CSP (Cloud Solution Provider) partners who have a need to use the Microsoft Azure Blob storage for backing up on-premises SQL Server databases. This document covers how to leverage CSP APIs to create an integrated backup and restore offerings for their customers using Azure Blob storage. The approach used in this document is an example to enable such an offering. This document is not intended to provide a final solution as-is, and also not intended to be the only way to implement a solution to enable backup and restore offering using CSP and Azure Blob storage.
The Microsoft Cloud Solution Provider (CSP) program is a Microsoft initiative where CSP partners can leverage key resources using an existing resource center that serves as a central hub to find benefits, resources and tools that enable them. One of the key benefits enabled by the CSP program is the Partner Center Portal that helps partners manage existing customer's subscriptions or create new ones. Although the CSP partners are able to manage and create new customers via the site, a set of key APIs have been delivered by Microsoft to help CSP partners integrate their own onsite automated solutions and best practices while at the same time leverage all the benefits and capabilities exposed via the APIs. The CREST API supports CSP partner's ability to create customers without having to log into the Partner Center. The CREST API is used to help build all the key elements needed to correctly provision a customer in the Partner Center Portal as well as build each unique Azure customer subscription. In addition, the Graph API is used to support Microsoft Azure Active Directory setup and configuration for both the CSP partners and their management of individual tenant subscriptions. Finally, the Azure Resource Manager (ARM) REST API is exposed to help CSP partners configure and setup new services in Azure. Although there are many additional capabilities exposed using the APIs mentioned, our primary focus in this document is to deliver technical guidance that helps partners deliver Backup and Restore services for on-premises SQL Server DB to new CSP provisioned customers. The samples and information outlined in this document include code snippets that we believe will help both technical leaders and developers understand one way the APIs could be leveraged.
This document assumes that the HSP has already been setup as a CSP partner. Some of the critical components and assumptions this procedure depends are outlined below.
In this guidance, we are using a CSV file to obtain input of customer info, server info and database info. Additionally, we are using SQL Server Express edition to store the configuration data used for processing. This is the approach for the guidance taken; however, CSP partners may choose to use different mechanisms to achieve the same results.
Key Infrastructure Dependencies:
This following is a high level diagram that we leveraged in outlining the key aspects of our end-to-end approach. The components illustrated below help identify the key aspects of our guidance throughout this document. Some of the key illustrated is how we are using the CREST API to provision new Tenant subscriptions. As new CSP tenant customers created in the Partner Center Portal, they also kick off the creation of the new subscription. After a new tenant is provisioned, a partner can manage it using the Partner Center Portal. We also included how we create each tenants Storage Resources and Containers in them by using the ARM REST API. Our approach using the identified scripts in this document outline how each unique tenants DB is backed-up into a single container. You will also see that are approach supports a single tenant storage that can contain many containers (i.e. 1 storage to many containers). What is not illustrated is the fact that we also implement a Resource Group for all created storage resources. Although there are a lot of details that can be included here, our goal was to simplify and keep it high level and only the key components needed to help illustrate one approach for fully automating backup\restore from on-premises to\from Azure.
How to backup and restore a single SQL 2014 on-premises DB to\from Azure Blob Storage offering manually.
- Name: newstoracct1 (lower case only and must contain at least 1 number)
- Resource Group
Name: newresgrp (lower case only and must contain at least 1 number)
- Location / Affinity Group: West US (Same region as where you created Virtual Network) – If multiple regions are involved it is recommended that you modify the region as appropriate.
Click Create button
- Name: dbbackups
- Access: Private
Click on Create button
Note: In real scenarios, DB server for source database will be different. For lab purposes, we are treating SQL server to be the on-premises database server.
CREATE CREDENTIAL Cred_SQL01
WITH IDENTITY = '<STORAGE_ACCOUNT_NAME>'
,SECRET = '<PRIMARY_ACCESS_KEY>'
GO
Example:
CREATE CREDENTIAL Cred_SQL01
WITH IDENTITY = 'newstoracct1'
,SECRET = 'lhLyxfNa4KibH6grJFbctn1nLg44hJStTQFYInQ3kDRxoVktD00fF7+N0zS39UKaWp0i+sy+3rNXQG9omLDRRg=='
GO
BACKUP DATABASE [AdventureWorks2014]
TO URL = 'https://<STORAGE_ACCOUNT_NAME>.blob.core.windows.net/dbbackups/AdventureWorks2014.bak'
WITH CREDENTIAL = 'Cred_SQL01'
, STATS = 5;
GO
Example:
BACKUP DATABASE [AdventureWorks2014]
TO URL = 'https://newstoracct1.blob.core.windows.net/dbbackups/adventureworks2014.bak'
WITH CREDENTIAL = 'Cred_SQL01',
STATS = 5;
GO
Results in the Tenant Site:
5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
Processed 24272 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1.100 percent processed.
Processed 3 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1.
BACKUP DATABASE successfully processed 24275 pages in 5.217 seconds (36.350 MB/sec).
Prerequisite: Ensure you have the Storage Account Name, Storage Device URL and Primary Key associated to the existing Container.
CREATE CREDENTIAL Cred_SQL01
WITH IDENTITY = '<STORAGE_ACCOUNT_NAME>'
,SECRET = '<PRIMARY_ACCESS_KEY>'
GO
RESTORE DATABASE [AdventureWorks2014]
FROM URL = 'https://<STORAGE_ACCOUNT_NAME>.blob.core.windows.net/dbbackups/AdventureWorks2014.bak'
WITH CREDENTIAL = 'Cred_SQL01'
, STATS = 5;
GO
5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
Processed 24272 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Data' on file 1.
Processed 3 pages for database 'AdventureWorks2014', file 'AdventureWorks2014_Log' on file 1.
RESTORE DATABASE successfully processed 24275 pages in 13.072 seconds (14.507 MB/sec).
As previously mentioned, one of the key benefits enabled by the CSP program is the existing partner portal that helps partners manage existing customer's subscriptions or create new ones. Although the CSP partners are able to manage and create new CSP customers via the site, partners are looking for new innovative ways to integrate their on-premises Backup and Restore solutions that are provisioned using the Partner Center. Microsoft has extended the CSP capabilities by exposing a set of APIs that we use in our code snippets below. The key API that we will be using is the CREST API for helping manage customers and services and the ARM REST API which helps support, provision and manage Azure features and services. Although each of the code snippets below may be leverage to deliver a starting point for delivering an automated solution, additional work and effort will be needed to pull all of them together into a single solution for managing customer's backup\restore scenarios.
Scenario: The recommended approach for delivering an automated solution assumes that a partner has many existing customers with various databases currently running in their on-premises environment. Each unique customer's database may live on either a single SQL server or potentially spread across various SQL servers. We also realize that any solution needs to address both a dedicated or multitenant solution so that has been considered as well. We also assume that CSP partners know which DB is associated to a specific customer they manage. Below we call out guidance using the APIs with sample (code snippets) and outlined a set of key components involved for helping partners deliver their own unique automated solution.
Assumptions: The following assumptions are applicable to the documented approach outlined below.
In the steps below, the following preparation work is essential for using the guidance identified here and building a partner's unique automated approach:
The following steps below outlines the planned sequence of events required to run. It is important to identify that we assume that the following preliminary requirements are completed.
The code snippets below can be leveraged as a clear starting point for CSP partners so they are able to deliver an automated solution of their own. It is important to note that the snippets are written as a guide only.
For each of your customers, you must identify the following information to successfully create a customer using our guidance in this document. The information captured here is to support the creation of a customer.csv file that will be used as input when creating a new customer in Partner Center and the setup of a customer Azure Subscription.
CSV Column Name | Example 1 | Example 2 | Description |
DomainPrefix | NEWKKK021233123 | MEDKKK021233123 | Identifies the prefix for the domain used |
UserName | Philtestuser | MEDtestuser | User Name |
Password | pwd@2012 | pwd@2012 | Password |
FirstName | New | MED | First Name |
LastName | testuser | testuser | Last Name |
| new@new.com | med@med.com | Email Address |
CompanyName | company111 | company111 | Company Name |
Culture | en-US | en-US | |
Language | en | en | Language |
Type | organization | organization | Can use any string needed |
AddressLine1 | AddressLine1 | AddressLine1 | Address |
AddressLine2 | AddressLine2 | AddressLine2 | Address |
City | Redmond | Redmond | City |
Region | WA | WA | Region |
PostalCode | 98052 | 98052 | Zip Code |
Country | US | US | Country |
PhoneNumber | 1234567890 | 1234567890 | Phone Number |
CustomerDatabaseBackupIntervalInHours | 12 | 12 | Value represents Interval in hours between backups |
BackupType | FullDatabaseBackup | IncrementalBackup | Full or Incremental Backup Supported |
File Format for identifying customers used in our guidance document: customer.csv
DomainPrefix, UserName, Password, FirstName, LastName, Email, CompanyName, Culture, Language, Type, AddressLine1, AddressLine2, City, Region, PostalCode, Country, PhoneNumber, CustomerDatabaseBackupIntervalInHours, BackupType
For each of your customers, you must identify the following information to successfully identify what servers in your environment have SQL Server installed and should be included for backups. The information captured here will be used to determine what databases are available on a unique server(s). The file that will be used to identify the server list has been identified below. In our code snippets we use a file called server.csv file as input to our code that will scan and determine what DB live on any SQL server identified in the list. Here is an example with 3 servers listed.
File Format for identifying servers used in our guidance document: server.csv
ServerName |
SQLBOX01 |
SQLBOX02 |
SQLBOX03 |
For each of your customers, you must determine what Databases belong to each of your customers to ensure they get backed up and dropped into the right container later. The following code snippet helps partners determine what previously identified SQL DB live on a unique box for use later.
------Start of Code Snippet----------------------------
public class SqlServerProvider
{
public string ServerConnectionString { get; set; }
public SqlServerProvider(string serverConnectionString)
{
this.ServerConnectionString = serverConnectionString;
}
public List<SqlDatabase> GetDatabases(Logger logger)
{
List<SqlDatabase> databases = new List<SqlDatabase>();
SqlConnection sqlConnection = new SqlConnection(ServerConnectionString);
try
{
using (sqlConnection)
{
SqlCommand command = new SqlCommand(@" SELECT sd.database_id as SqlDatabaseId,
sd.name as Name,
owner_sid as OwnerSid,
osp.name as Owner,
DATEADD(MINUTE, -DATEPART(TZoffset, SYSDATETIMEOFFSET()), sd.create_date) AS CreateDate
FROM sys.databases sd
JOIN sys.database_mirroring dm
ON sd.database_id = dm.database_id
LEFT JOIN sys.server_principals osp
ON sd.owner_sid = osp.sid",
sqlConnection);
sqlConnection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
try
{
SqlDatabase db = new SqlDatabase();
db.SqlDatabaseId = Guid.NewGuid();
// get the results of each column
db.SqlServerDatabaseId = (Int32)reader["SqlDatabaseId"];
db.Name = (string)reader["Name"];
db.OwnerSid = (byte[])reader["OwnerSid"];
db.Owner = (string)reader["Owner"];
db.CreateDate = new DateTimeOffset((DateTime)reader["CreateDate"]);
if (!db.IsSystem)
{
databases.Add(db);
}
}
catch (Exception ex)
{
logger.Error(ex);
}
}
}
else
{
logger.Warning("No database found in server {0}", ServerConnectionString);
}
reader.Close();
}
} catch (Exception ex)
{
logger.Error(ex);
}
return databases;
}
}
------End of Code Snippet----------------------------
For each of your customers, you must map DB ownership to a CSP customer. This ensures the backup and restore automation scripts know which customer Azure Subscription. There are many ways to map Customers to DB for a given server. In our code snippets we leveraged SQL Express for storing all captured information. A flat mapping file could also be used to support CSP partner's efforts and one possible mapping could be as follows:
File = map.csv
Customer Name | Customer ID | Server ID | Database ID |
Note: Mapping is required and we have found using a SQL DB like the illustration below can be used for mapping what is needed or simply use a file like the one mentioned above (i.e. Map.CSV).
For each of your customers, you must create a customer account and an Azure subscription. This will allow you to track usage and billing for each customer easily. To create a customer account and provision an azure subscription, leverage the CREST APIs (See Appendix A or links) to do so. Here is a code snippet that creates customer initial accounts from a csv file:
The purpose of this function code snippet below helps partners form the http web request for creating customers. It also adds the required http header in the http request which are required by the CREST API.
------Start of Code Snippet----------------------------
internal static HttpWebRequest CreateCustomerWebRequest(string resellerCid, string sa_Token)
{
var request = (HttpWebRequest)HttpWebRequest.Create(string.Format("{0}/{1}/customers/create-reseller-customer", baseUrl, resellerCid));
request.Method = "POST";
request.ContentType = "application/json";
AddCommonRequestHeaders(request);
request.Headers.Add("x-ms-tracking-id", Guid.NewGuid().ToString());
request.Headers.Add("Authorization", "Bearer " + sa_Token);
return request;
}
The following code snippet is required to get the required Authorization Bearer token.File Name: C:\Users\Phillip\Documents\GitHub\CSP\HSPDataProvider\CRESTApi\Reseller.cs
/// <summary>
/// Given the reseller domain, clientid and clientsecret of the app, this method helps to retrieve the AD token
/// </summary>
/// <param name="resellerDomain">domain of the reseller including .onmicrosoft.com</param>
/// <param name="clientId">AppId from the azure portal registered for this app</param>
/// <param name="clientSecret">Secret from the azure portal registered for this app</param>
/// <returns>this is the authentication token object that contains access_token, expiration time, can be used to get the authorization token from a resource</returns>
public static dynamic GetADToken(string resellerDomain, string clientId, string clientSecret)
{
WebRequest request;
string content;
CrestApiHelper.GetGetADTokenWebRequest(resellerDomain, clientId, clientSecret, out request, out content);
using (var writer = new StreamWriter(request.GetRequestStream()))
{
writer.Write(content);
}
try
{
var response = request.GetResponse();
using (var reader = new StreamReader(response.GetResponseStream()))
{
var responseContent = reader.ReadToEnd();
var adResponse = Json.Decode(responseContent);
return adResponse;
}
}
catch (WebException webException)
{
if (webException.Response != null)
{
using (var reader = new StreamReader(webException.Response.GetResponseStream()))
{
var responseContent = reader.ReadToEnd();
}
}
}
return string.Empty;
}
/// <summary>
/// Given the ad token this method retrieves the sales agent token for accessing any of the partner apis
/// </summary>
/// <param name="adToken">this is the access_token we get from AD</param>
/// <returns>the sales agent token object which contains access_token, expiration duration</returns>
private static dynamic GetSA_Token(string adToken)
{
HttpWebRequest request;
string content;
CrestApiHelper.GetSATokenWebRequest(adToken, out request, out content);
using (var writer = new StreamWriter(request.GetRequestStream()))
{
writer.Write(content);
}
try
{
var response = request.GetResponse();
using (var reader = new StreamReader(response.GetResponseStream()))
{
var responseContent = reader.ReadToEnd();
return Json.Decode(responseContent);
}
}
catch (WebException webException)
{
using (var reader = new StreamReader(webException.Response.GetResponseStream()))
{
var responseContent = reader.ReadToEnd();
}
}
return string.Empty;
}
------End of Code Snippet----------------------------
The purpose of the code below is to create customer in Microsoft Partner Center: There are additional methods the partners need to be aware of to successfully pull into the code sample below (Customer, resellerCID, sa Token).
Main Method:
------Start of Code Snippet----------------------------
/// <summary>
/// This method is used to create a customer in the Microsoft reseller ecosystem by the reseller
/// </summary>
/// <param name="customer">customer information: domain, admin credentials for the new tenant, address, primary contact info</param>
/// <param name="resellerCid">reseller cid</param>
/// <param name="sa_Token">unexpired access token to access the partner apis</param>
/// <returns>the newly created customer information: all of the above from customer, customer cid, customer microsoft id</returns>
public static dynamic CreateCustomer(dynamic customer, string resellerCid, string sa_Token)
{
HttpWebRequest request = CrestApiHelper.CreateCustomerWebRequest(resellerCid, sa_Token);
string content = Json.Encode(customer);
using (var writer = new StreamWriter(request.GetRequestStream()))
{
writer.Write(content);
}
try
{
var response = request.GetResponse();
using (var reader = new StreamReader(response.GetResponseStream()))
{
var responseContent = reader.ReadToEnd();
var createdCustomerResponse = Json.Decode(responseContent);
return createdCustomerResponse;
}
}
catch (WebException webException)
{
using (var reader = new StreamReader(webException.Response.GetResponseStream()))
{
var responseContent = reader.ReadToEnd();
return responseContent;
}
}
}
------End of Code Snippet----------------------------
Once a new CSP customer is created for a unique tenant in the Partner Center a new tenant Azure Subscription must also be provisioned. The following script identifies recommended guidance on what to do to create the required Azure Subscription leveraging the ARM REST API. Here is a code snippet that creates customer initial Azure Subscription:
The code snippet below goes and gets the subscriptions the may exist in the Partner Center for a customer.
------Start of Code Snippet----------------------------
/// <summary>
/// This method is to retrieve the subscriptions of a customer bought from the reseller
/// </summary>
/// <param name="customerCid">cid of the customer</param>
/// <param name="resellerCid">cir of the reseller</param>
/// <param name="sa_Token">sales agent token</param>
/// <returns>object that contains all of the subscriptions</returns>
public static dynamic GetSubscriptions(string customerCid, string resellerCid, string sa_Token)
{
HttpWebRequest request = CrestApiHelper.GetSubscriptionsWebRequest(customerCid, resellerCid, sa_Token);
try
{
var response = request.GetResponse();
using (var reader = new StreamReader(response.GetResponseStream()))
{
var responseContent = reader.ReadToEnd();
var subscriptionsResponse = Json.Decode(responseContent);
return subscriptionsResponse;
}
}
catch (WebException webException)
{
using (var reader = new StreamReader(webException.Response.GetResponseStream()))
{
var responseContent = reader.ReadToEnd();
}
}
return string.Empty;
}
------End of Code Snippet----------------------------
The code snippet below help CSP partners create a new customer Azure subscription via the CREST API if one does not exist.
------Start of Code Snippet----------------------------
/// <summary>
/// This method is used to place order on behalf of a customer by a reseller
/// </summary>
/// <param name="resellerCid">the cid of the reseller</param>
/// <param name="order">new Order that can contain multiple line items</param>
/// <param name="sa_Token">unexpired access token to call the partner apis</param>
/// <returns>order information that has references to the subscription uris and entitlement uri for the line items</returns>
public static dynamic PlaceOrder(dynamic order, string resellerCid, string sa_Token)
{
HttpWebRequest request = CrestApiHelper.PlaceOrderWebRequest(resellerCid, sa_Token);
string content = Json.Encode(order);
using (var writer = new StreamWriter(request.GetRequestStream()))
{
writer.Write(content);
}
try
{
var response = request.GetResponse();
using (var reader = new StreamReader(response.GetResponseStream()))
{
var responseContent = reader.ReadToEnd();
var placedOrder = Json.Decode(responseContent);
return placedOrder;
}
}
catch (WebException webException)
{
using (var reader = new StreamReader(webException.Response.GetResponseStream()))
{
var responseContent = reader.ReadToEnd();
}
}
return string.Empty;
}
------End of Code Snippet----------------------------
The code snippet below is a method for forming the web request for getting and creating the subscription for a unique customer.
--Creating a subscription in CSP -------Start of Code Snippet----------------------------
internal static HttpWebRequest PlaceOrderWebRequest(string resellerCid, string sa_Token)
{
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(string.Format("{0}/{1}/orders", baseUrl, resellerCid));
request.Method = "POST";
request.ContentType = "application/json";
AddCommonRequestHeaders(request);
request.Headers.Add("x-ms-tracking-id", Guid.NewGuid().ToString());
request.Headers.Add("Authorization", "Bearer " + sa_Token);
return request;
}
------End of Code Snippet----------------------------
--Getting all subscriptions for a customer ------Start of Code Snippet----------------------------
internal static HttpWebRequest GetSubscriptionsWebRequest(string customerCid, string resellerCid, string sa_Token)
{
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(string.Format("{0}/{1}/subscriptions?recipient_customer_id={2}", baseUrl, resellerCid, customerCid));
request.Method = "GET";
AddCommonRequestHeaders(request);
request.Headers.Add("x-ms-tracking-id", Guid.NewGuid().ToString());
request.Headers.Add("Authorization", "Bearer " + sa_Token);
return request;
}
------End of Code Snippet----------------------------
The code snippet below is the main code and checks if an existing subscript already exist with Azure Offer ID.
------Start of Code Snippet----------------------------
public Order CreateCustomerOrder(string customerCPID, string offerUri)
{
string resellerCid = Reseller.GetCid(MicrosoftId, _saAuthorizationToken.AccessToken);
var customerAuthorizationToken = CustomerProvider.GetCustomer_Token(customerCPID, _adAuthorizationToken);
var subscriptions = Subscription.GetSubscriptions(customerCPID, resellerCid, _saAuthorizationToken.AccessToken);
if (subscriptions != null && subscriptions.total_count > 0)
{
foreach(var s in subscriptions.items)
{
if (s.state == "Active" && s.offer_uri == offerUri)
{
return new Order
{
SubscriptionId = s.id,
SubscriptionUri = ""
};
}
}
}
var order = new
{
line_items = new List<dynamic>()
{
new
{
line_item_number = 0,
offer_uri = offerUri,
quantity = 1,
}
},
recipient_customer_id = customerCPID
};
var newCustomerPlacedOrder = OrdersProvider.PlaceOrder(order, resellerCid, _saAuthorizationToken.AccessToken);
var subscription = Subscription.GetSubscriptionByUri(newCustomerPlacedOrder.line_items[0].resulting_subscription_uri, _saAuthorizationToken.AccessToken);
return new Order
{
SubscriptionId = subscription.Id,
SubscriptionUri = newCustomerPlacedOrder.line_items[0].resulting_subscription_uri
};
}
------End of Code Snippet----------------------------
The following code snippet below outlines the code for properly creating a new Azure resource group and storage account. Here is a bit of details for each core area that is addressed in the code snippet.
Here we identify the main method that checks if an existing Azure Resource Group exist and if not, it creates one, it also checks if a storage account exist for the customer in the resource group and if not it creates one or simply uses it –
------Start of Code Snippet----------------------------
internal static void CreateResourcesInResourceGroup(AzureCustomerDetail customerDetail, string token, string storageAccountName)
{
AppSettingDataProvider settingProvider = new AppSettingDataProvider(ServiceConfigrationHelper.GetConnectionString(), ServiceConfigrationHelper.GetEncriptionCertSubjectName());
string resourceGroupName = settingProvider.GetAppSetting("storageresourceGroupName").AppSettingValue;
string defaultStorageAccountLocation = settingProvider.GetAppSetting("DefaultStorageAccountLocation").AppSettingValue;
string storageAccountType = settingProvider.GetAppSetting("storageAccountType").AppSettingValue;
string storageAccountLabel = settingProvider.GetAppSetting("storageAccountLabel").AppSettingValue;
ArmApi api = new ArmApi();
StorageResourceRequest storageRequest = new StorageResourceRequest
{
Location = defaultStorageAccountLocation,
SubscriptionId = customerDetail.CustomerSubscriptionId.ToString(),
Token = token,
AccountType = storageAccountType,
Label = storageAccountLabel,
Name = storageAccountName,
ResourceGroupName = resourceGroupName
};
var resourceGroups = api.GetAllResourceGroup(storageRequest);
bool resourceGroupExist = false;
if (resourceGroups != null && resourceGroups.Count > 0)
{
foreach (var resourceGroup in resourceGroups)
{
if (!string.IsNullOrEmpty(resourceGroup.Name) && string.Compare(resourceGroup.Name, resourceGroupName, false) == 0)
{
if (resourceGroup.Properties["provisioningState"] == "Succeeded")
{
resourceGroupExist = true;
break;
}
}
}
}
if (!resourceGroupExist)
{
api.CreateResourceGroup(storageRequest, resourceGroupName);
}
api.RegisterStorageResourceProvider(storageRequest);
var accounts = api.GetAllStorageAccountInResourceGroup(storageRequest);
if (accounts != null && accounts.Value != null && accounts.Value.Count()>0)
{
var existingAccount = accounts.Value.FirstOrDefault();
storageRequest.Name = existingAccount.Name;
storageRequest.Location = existingAccount.Location;
}
else
{
api.CreateStorageResource(storageRequest);
}
var account = api.GetStorageAccount(storageRequest);
var accountkey = api.GetStorageAccountKeys(storageRequest);
var url = StorageProvider.GetStorageAccountUrl(account.Name, accountkey.Key1);
CustomerDataProvider customerProvider = new CustomerDataProvider(ServiceConfigrationHelper.GetConnectionString(), ServiceConfigrationHelper.GetEncriptionCertSubjectName());
var customer = customerProvider.GetCustomer(customerDetail.AzureCustomerDetailId);
customer.StorageAccountId = account.Id;
customer.StorageAccountName = account.Name;
customer.StorageAccountLocation = url;
customer.StorageAccountKey = accountkey.Key1;
customerProvider.UpdateCustomer(customer);
}
------End of Code Snippet----------------------------
The following code snippet identifies the helper class which we use for calling the Azure Resource Manager (ARM) REST API.
------Start of Code Snippet----------------------------
using HSPDataProvider.Helper;
using System;
using System.Collections.Generic;
using System.Net.Http;
namespace HSPDataProvider.ARMApi
{
public class ArmApi
{
private static ArmApiUrl apiUrl = null;
static ArmApi()
{
apiUrl = new ArmApiUrl();
}
public List<ResourceGroup> GetAllResourceGroup(ArmApiParam request)
{
HttpHelper helper = new HttpHelper();
Uri uri = new Uri(string.Format(apiUrl.GetAllResourceGroupUrl, request.SubscriptionId));
return helper.GetArmResponseObject<ResourceGroupCollection>(uri, request.Token).Value;
}
public void CreateResourceGroup(ArmApiParam request, string resourceGroupName)
{
HttpHelper helper = new HttpHelper();
Uri uri = new Uri(string.Format(apiUrl.CreateResourceGroupUrl, request.SubscriptionId, resourceGroupName));
var contentBody = new { location = request.Location };
var content = new ObjectContent(contentBody.GetType(), contentBody, HttpHelper.JsonMediaTypeFormatter);
HttpResponseMessage message = helper.PutArmRequestObject(uri, request.Token, content);
}
public void RegisterStorageResourceProvider(ArmApiParam request)
{
HttpHelper helper = new HttpHelper();
Uri uri = new Uri(string.Format(apiUrl.RegisterResourceProviderWithSubscription, request.SubscriptionId));
var contentBody = new {};
var content = new ObjectContent(contentBody.GetType(), contentBody, HttpHelper.JsonMediaTypeFormatter);
HttpResponseMessage message = helper.PostArmRequestObject<HttpResponseMessage>(uri, request.Token, content);
}
public bool CheckStorageAccountNameAvailability(string subscriptionId, string storageAccountName, string token, out string messageText)
{
messageText = string.Empty;
HttpHelper helper = new HttpHelper();
Uri uri = new Uri(string.Format(apiUrl.CheckStorageAccountNameAvailability, subscriptionId));
var contentBody = new { name = storageAccountName, type = "Microsoft.Storage/storageAccounts" };
var content = new ObjectContent(contentBody.GetType(), contentBody, HttpHelper.JsonMediaTypeFormatter);
StorageAccountAvailability message = helper.PostArmRequestObject<StorageAccountAvailability>(uri, token, content);
if (!message.nameAvailable)
{
if (!string.IsNullOrEmpty(message.reason))
messageText = message.reason;
if (!string.IsNullOrEmpty(message.message))
messageText = messageText+ message.message;
}
return message.nameAvailable;
}
public StorageAccountColection GetAllStorageAccountInResourceGroup(StorageResourceRequest request)
{
HttpHelper helper = new HttpHelper();
Uri uri = new Uri(string.Format(apiUrl.GetAllStorageAccountInResourceGroupUrl, request.SubscriptionId, request.ResourceGroupName));
var accounts = helper.GetArmResponseObject<StorageAccountColection>(uri, request.Token);
return accounts;
}
public void CreateStorageResource(StorageResourceRequest request)
{
HttpHelper helper = new HttpHelper();
Uri uri = new Uri(string.Format(apiUrl.StorageResourceUrl, request.SubscriptionId, request.ResourceGroupName, request.Name));
var properties = new { accountType = request.AccountType };
var contentBody = new { location = request.Location , properties = properties };
var content = new ObjectContent(contentBody.GetType(), contentBody, HttpHelper.JsonMediaTypeFormatter);
HttpResponseMessage message = helper.PutArmRequestObject(uri, request.Token, content);
}
public StorageAccount GetStorageAccount(StorageResourceRequest request)
{
HttpHelper helper = new HttpHelper();
Uri uri = new Uri(string.Format(apiUrl.StorageResourceUrl, request.SubscriptionId, request.ResourceGroupName, request.Name));
return helper.GetArmResponseObject<StorageAccount>(uri, request.Token);
}
public StorageKey GetStorageAccountKeys(StorageResourceRequest request)
{
HttpHelper helper = new HttpHelper();
Uri uri = new Uri(string.Format(apiUrl.StorageAccountKeyUrl, request.SubscriptionId, request.ResourceGroupName, request.Name));
var contentBody = new { };
var content = new ObjectContent(contentBody.GetType(), contentBody, HttpHelper.JsonMediaTypeFormatter);
StorageKey keys = helper.PostArmRequestObject<StorageKey>(uri, request.Token, content);
return keys;
}
}
}
------End of Code Snippet----------------------------
The following code snippet below is the ARM REST API URL Helper which we use for forming the URL for ARM request.
------Start of Code Snippet----------------------------
namespace HSPDataProvider.ARMApi
{
public class ArmApiUrl
{
public ArmApiUrl()
{
ArmResourceGroupsBaseUrl = CommonConst.ArmBaseUrl + @"subscriptions/{0}/resourcegroups";
CheckStorageAccountNameAvailability = CommonConst.ArmBaseUrl + "subscriptions/{0}/providers/Microsoft.Storage/checkNameAvailability?api-version=2015-06-15";
RegisterResourceProviderWithSubscription = CommonConst.ArmBaseUrl + "subscriptions/{0}/providers/Microsoft.Storage/register?api-version=2015-01-01";
StorageAccountKeyUrl = ArmResourceGroupsBaseUrl + "/{1}/providers/Microsoft.Storage/storageAccounts/{2}/listKeys?api-version=2015-06-15";
StorageResourceUrl = ArmResourceGroupsBaseUrl + "/{1}/providers/Microsoft.Storage/storageAccounts/{2}?api-version=2015-06-15";
CreateResourceGroupUrl = ArmResourceGroupsBaseUrl + "/{1}?api-version=2015-01-01";
GetAllResourceGroupUrl = ArmResourceGroupsBaseUrl + "?api-version=2015-01-01";
GetAllStorageAccountInResourceGroupUrl = ArmResourceGroupsBaseUrl + "/{1}/providers/Microsoft.Storage/storageAccounts?api-version=2015-06-15";
}
public string ArmResourceGroupsBaseUrl { get; set; }
public string StorageAccountKeyUrl { get; set; }
public string StorageResourceUrl { get; set; }
public string CreateResourceGroupUrl { get; set; }
public string RegisterResourceProviderWithSubscription { get; set; }
public string GetAllResourceGroupUrl { get; set; }
public string CheckStorageAccountNameAvailability { get; set; }
public string GetAllStorageAccountInResourceGroupUrl { get; set; }
}
}
------End of Code Snippet----------------------------
For each of your customers, a Storage Container will be needed to house the backups. The guidance here outlines how a customer can provision this as part of the DB backup process. In other words, we create a 1 to 1 mapping where each database will get their own container. We believe this will help keep all database backups organized and simplify the backup and restore process. Here is an example of how a HSP can provision a Storage Container for an Azure Customers subscription.
------Start of Code Snippet----------------------------
private static string storageConnectionString = "DefaultEndpointsProtocol=https;AccountName={0};AccountKey={1}";
public static void CreateStorageBlobContainer(string accountName, string ContainerName, string accountKey)
{
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(string.Format(storageConnectionString, accountName, accountKey));
//Create the blob client object.
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
//Get a reference to a container to use for the sample code, and create it if it does not exist.
CloudBlobContainer container = blobClient.GetContainerReference(ContainerName);
//Create a new container, if it does not exist
container.CreateIfNotExists(BlobContainerPublicAccessType.Off);
}
------End of Code Snippet----------------------------
As previously identified in our guidance, our approach is to ensure a mapping exist between each partner's on-premises hosted DB to a unique CSP Azure Tenant's subscription. We know want to setup backup so each of the existing DB for a unique customer can be backed up regardless where it lives. The scripts below will be used for each of the key activities required to successfully backup customer databases.
Key Steps:
------Start of Code Snippet----------------------------
public CustomerDatabaseBackupType GetBackupType(AzureCustomerDetail customer, Models.Server sqlserver, SqlDatabase database, string containerName)
{
if(customer.BackupType == CustomerDatabaseBackupType.FullDatabaseBackup)
{
return CustomerDatabaseBackupType.FullDatabaseBackup;
}
ServerDataProvider dataProvider = new ServerDataProvider(hspConfigConnectionstring, hspEncriptionCertSubjectName);
var history = dataProvider.GetDatabaseBackupHistory(database.SqlDatabaseId);
if (history == null || history.Count() == 0)
{
return CustomerDatabaseBackupType.FullDatabaseBackup;
}
else
{
return CustomerDatabaseBackupType.IncrementalBackup;
}
}
------End of Code Snippet----------------------------
------Start of Code Snippet----------------------------
private Credential CreateCredentialInSqlServer(AzureCustomerDetail customer, Models.Server sqlserver)
{
if (server.Credentials.Contains(customer.StorageAccountName))
{
Credential credential = server.Credentials[customer.StorageAccountName];
return credential;
}
else
{
string credentialName = customer.StorageAccountName;
Credential credential = new Credential(server, credentialName);
credential.Create(customer.StorageAccountName, customer.StorageAccountKey);
return credential;
}
}
------End of Code Snippet----------------------------
------Start of Code Snippet----------------------------
public bool BackupDatabase(AzureCustomerDetail customer, Models.Server sqlserver, SqlDatabase database, out CustomerDatabaseBackupType type, out string backupUrl)
{
bool IsBackupDatabaseDone = true;
type = CustomerDatabaseBackupType.None;
backupUrl = null;
server.ConnectionContext.Connect();
try
{
string containerName = GetBlobContainerName(sqlserver, database);
StorageProvider.CreateStorageBlobContainer(customer.StorageAccountName, containerName, customer.StorageAccountKey);
Credential credential = CreateCredentialInSqlServer(customer, sqlserver);
type = GetBackupType(customer, sqlserver, database, containerName);
backupUrl = string.Format(@"{0}{1}/{2}-{3}{4}.bak", customer.StorageAccountLocation, containerName,
type.ToString().ToLowerInvariant(), database.Name.ToLower(), DateTime.UtcNow.ToString("s").Replace(":", "-"));
string databaseName = database.Name;
Database db = default(Database);
db = server.Databases[databaseName];
// Store the current recovery model in a variable.
int recoverymod;
recoverymod = (int)db.DatabaseOptions.RecoveryModel;
// Define a Backup object variable.
Backup bk = new Backup();
// Specify the type of backup, the description, the name, and the database to be backed up.
bk.Action = BackupActionType.Database;
bk.BackupSetDescription = type.ToString().ToLowerInvariant() + " backup of databaseName:" + databaseName;
bk.BackupSetName = databaseName + " Backup";
bk.Database = databaseName;
bk.CredentialName = credential.Name;
// Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem(backupUrl, DeviceType.Url);
// Add the device to the Backup object.
bk.Devices.Add(bdi);
// Set the Incremental property to False to specify that this is a full database backup.
bk.Incremental = (type == CustomerDatabaseBackupType.IncrementalBackup) ? true : false;
// Specify that the log must be truncated after the backup is complete.
bk.LogTruncation = BackupTruncateLogType.Truncate;
// Run SqlBackup to perform the database backup on the instance of SQL Server.
bk.SqlBackup(server);
bk.Devices.Remove(bdi);
}
catch (Exception ex)
{
IsBackupDatabaseDone = false;
if (logger != null)
logger.Error(ex);
}
finally
{
server.ConnectionContext.Disconnect();
}
return IsBackupDatabaseDone;
}
------End of Code Snippet----------------------------
------Start of Code Snippet----------------------------
private void BackupDatabases()
{
CustomerDataProvider dataProvider = new CustomerDataProvider(ServiceConfigrationHelper.GetConnectionString(), ServiceConfigrationHelper.GetEncriptionCertSubjectName());
var customers = dataProvider.GetCustomersWithStorageAccount();
ServerDataProvider serverDataProvider = new ServerDataProvider(ServiceConfigrationHelper.GetConnectionString(), ServiceConfigrationHelper.GetEncriptionCertSubjectName());
foreach (var customer in customers)
{
var databases = dataProvider.GetAllDatabsesForCustomer(customer.AzureCustomerDetailId);
if (databases != null && databases.Count() > 0)
{
foreach(var db in databases)
{
var history = serverDataProvider.GetLatestDatabaseBackupHistory(db.SqlDatabaseId);
if (history == null)
{
BackupDatabase(serverDataProvider, customer, db);
}
else
{
if(history.BackupDate.Add(customer.CustomerDatabaseBackupInterval)< DateTime.UtcNow)
{
BackupDatabase(serverDataProvider, customer, db);
}
}
}
}
}
}
------End of Code Snippet----------------------------
One of the key events that may be needed is the restoral of a previously backed up DB using the solution identified above. Since the nature of restoral is not considered a planned event we have outlined key steps that can be implemented to kick off a restoral of a unique customer's database or all DB on an existing server.
Scenario:
Since our recommended guidance included a means for capturing all DB on a defined SQL Server that is mapped to one or more customers, a restoral of a single DB or all DB on a given server can be supported. Restoring a group of DB for different customers will require that the same command options used for 1 is repeated.
The following steps outlines the sequence of events required to properly run restores from and existing Azure tenant subscription storage container. The nature of restoral is to replace the previously DB on-premises and this guidance will restore the existing DB back to the last known good backup. All information that has not been backed up will be lost. If this is critically to partner tenants, we recommended that an HA solution be implement separately. It is important to identify that we assume that the following preliminary requirements have been met.
Initial Requirements:
Once the initial requirements have been validated the following key steps below can be executed.
Key Steps:
------Start of Code Snippet----------------------------
public bool RestoreDatabase(AzureCustomerDetail customer, Models.Server sqlserver, SqlDatabase database, List<SqlDatabaseBackupHistory> backups)
{
bool isRestoreDone = false;
server.ConnectionContext.Connect();
try
{
// Restore a database and move files
string newDataFilePath = server.MasterDBLogPath + @"\" + database.Name + DateTime.Now.ToString("s").Replace(":", "-") + ".mdf";
string newLogFilePath = server.MasterDBLogPath + @"\" + database.Name + DateTime.Now.ToString("s").Replace(":", "-") + ".ldf";
Credential credential = CreateCredentialInSqlServer(customer, sqlserver);
if(backups.OrderBy(b => b.BackupDate).First().DatabaseBackupType != CustomerDatabaseBackupType.FullDatabaseBackup)
{
throw new Exception("no full database found");
}
int count = backups.Count();
int index = 1;
foreach (var bk in backups.OrderBy(b=>b.BackupDate))
{
Restore restore = new Restore();
restore.CredentialName = credential.Name;
restore.Database = database.Name;
if(index == 1 && bk.DatabaseBackupType != CustomerDatabaseBackupType.FullDatabaseBackup)
{
throw new Exception("restore failed");
}
if(bk.DatabaseBackupType == CustomerDatabaseBackupType.FullDatabaseBackup)
restore.ReplaceDatabase = true;
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem(bk.BackupUrl, DeviceType.Url);
if (index == count)
{
restore.NoRecovery = false;
}
else
{
restore.NoRecovery = true;
}
restore.Devices.Add(bdi);
restore.RelocateFiles.Add(new RelocateFile(database.Name, newDataFilePath));
restore.RelocateFiles.Add(new RelocateFile(database.Name + "_Log", newLogFilePath));
server.KillAllProcesses(database.Name);
restore.SqlRestore(server);
restore.Devices.Remove(bdi);
index++;
}
isRestoreDone = true;
}
catch (Exception ex)
{
if (logger != null)
logger.Error(ex);
}
finally
{
server.ConnectionContext.Disconnect();
}
return isRestoreDone;
}
------End of Code Snippet----------------------------
Conducting backs or restores using a manual or automatically approach is enabled by leveraging the CSP API identified above. Manual backup or restore is fairly straight forward however; using the automated approach does require additional considerations when used for the following scenarios. In a dedicated scenario where a dedicated server exist all databases will be backed-up to a unique storage device in a unique customer's subscription whereas a unique customer may also be provisioned in a Clustered scenario that requires the use of a listener. In addition, a Multitenant and Windows Azure Pack scenarios increases the complexity of backup and restores primarily due to the reality that now you need to consider how you will map each CSP partner's database to a unique storage container for each customer that will be different subscriptions. In addition, WAP may also have a listener configured as part of their HA offering.
What considerations should CSP be aware of when provisioning their dedicated clients using CSP? The illustration below is a single dedicated server with Multiple DB for a single customer that needs to be backed up to Azure. If multiple dedicated servers are involved the process would simply repeat itself for each customer.
The following are key considerations partners should be aware of when delivering a dedicated solution using our guidance.
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
What considerations should partners be aware of when provisioning multitenant clients using CSP? Partners wanting to leverage a multi-tenant solution using the same server will need to ensure all customers have been created using our recommended automation guidance. Below you will find the additional considerations that should be understood.
The following are key considerations partners should be aware of when delivering a multi-tenant solution using our automated solution.
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
What considerations should CSP be aware of when provisioning High Availability solutions for their CSP clients? Partners with customers who currently subscribe to High Availability can leverage both a manual and scripted solution. Below the illustration identifies how a unique customer subscribing to HA can leverage the backup\restore scripted solution. Some of the additional considerations that need to be identified are listed below.
The following are key considerations partners should be aware of when delivering a High Availability solution using our guidance.
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
What considerations should CSP be aware of when provisioning Windows Azure Pack (WAP) for their CSP clients? Partners with customers who currently provide a Windows Azure Pack solution can leverage both a manual and scripted solution. Below the illustration identifies how a unique customer subscribing to Distributed WAP can leverage the backup\restore scripted solution. Some of the additional considerations that need to be identified are listed below.
The following are key considerations partners should be aware of when delivering a Windows Azure Pack solution using our guidance.
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
Name | Purpose | Link |
Windows Azure PowerShell – Storage Commands | In this Blog we will walk through few PowerShell commands to work with Windows Azure Storage. | |
Azure Storage | Azure Storage Accounts Information | |
Partner Network | Microsoft Cloud Solution Provider program | |
Blob Service REST API | The Blob service stores text and binary data as blobs in the cloud. The Blob service offers the following three resources: the storage account, containers, and blobs. Within your storage account, containers provide a way to organize sets of blobs. | |
AD Graph REST | AD Graph REST | |
Azure AD Graph REST API Reference | This topic lists Azure AD entities and types that are exposed by Graph and provides information about the operations that can be performed on them with Graph. | |
CREAT APIs | This section describes the conventions that the CREST API adopts. | |
APIs | Automate customers management with our APIs | |
API Reference | Microsoft Partner Center API Reference | |
Graph Explorer | Used to validate Azure AD Subscription information |
The following steps outline how a partner can elect to save a specific set of customer's databases to a unique region. The configuration file below includes a "defaultstorageaccountlocation" parameter (highlighted below) that can be updated to any of the existing Azure global regions.
Configuration Setting File used during the initial setup setting | Data | Description |
Set-Setting MicrosoftId | a6e8dc40-43fc-4461-9aef-1cef6603d7f3 | Microsoft ID |
Set-Setting DefaultDomain | mani100.onmicrosoft.com | Default Domain used in the Partner Center Portal |
Set-Setting AppId | 75c681c3-ca80-4b40-8bec-78ec37c5222d | Assigned Application ID |
Set-Setting AppKey | RrTTpKC3ZZvV0wWmbUzZ1U5jv6NSc+MxFRlk3pAdIko= | Assigned APP Key (will be encrypted) |
Set-Setting | storageresourceGroupName DBBackupRG | Default Storage Resource Group Name that will be created for new subscriptions |
Set-Setting DefaultStorageAccountLocation | "West US" | Use to identify the targeted regions a new customers subscription will be created with or is set to |
Set-Setting storageAccountType | Standard_LRS | One of the following account types (case-sensitive):
|
Set-Setting storageAccountLabel | "CSP partner's database backup storageaccount" | Name of the storage account |
Set-Setting storageAccountBlobContainerName | DBBackup | Name of the container |
Set-Setting ARMUser | armServiceaccount@mani100.onmicrosoft.com | Name of Service Account Provisioned with the appropriate rights in CSP partner's Azure Subscription and Partner Center Portal. |
Set-Setting ARMPwd | Lion@2012 | Service Account Password (Will be encrypted) |
Set-Setting armAadClientId | 1950a258-227b-4e31-a9cf-717495945fc2 | Azure Client ID Assigned |
This document provides a great starting point for helping CSP partners build a customized automated solution for their unique requirements. The details and goals outlined here are focus on providing needed guidance for helping partners leverage the existing API currently available via Partner Center and Azure.
Since we realize that some customers may want to leverage a Sandbox environment for testing the following guidance is provided.
Below you see where to setup the Integration sandbox.
Select API settings and register an app for the Integration Sandbox environment
Below you will see the permissions established for the "amServiceaccount". Only select the Admin Agent which will give the right level of permissions needed to use the guidance scripts in this document.
Procedure and guidance for creating an encrypted backup are covered here: