Database migration to Windows Azure SQL VM. BLOB Storage + REST
Now that we have created the Cloud with SQL Server installed on it and are able to connect to it from the client side, as with the local SQL Server, it remains to fill it with data. Suppose, in a hybrid scenario, part of the database is planned to be migrated to Azure SQL VM. In this article, we will consider a scenario when a database is isolated in the form of a file (or several files) by creating its backup copy, detach, data-tier application , etc., the file is delivered to Azure SQL VM and converted back to the database by restoring from backup, attach, deploy / import data-tier application , etc. The first and last action does not raise questions from the DBA. It remains to understand how best to deliver the alienated file with the database (.bak, .mdf, .bacpac, ...) to a cloud-based virtual server with SQL Server.
For example, we will transfer our favorite AdventureWorks database in the form of its backup copy:
Script 1
Files of small sizes, like this one, can be easily crafted without any further ado by using ordinary Copy / Paste to the remote desktop of the SQL Server virtual machine. It even occurs to me to create a shared folder on the virtual machine and copy it there using advanced copying tools with the ability to parallelize, correct and resume in case of failures, and also transfer the file via FTP. These methods are obvious. In this post, we will use a different method: transfer the backup file from the local machine to Azure Storage in the form of a blob and download it from there into the cloud virtual machine. We already have one Storage Account, created automatically when creating a virtual machine, in which there was automatically a container named vhds, in which the virtual disk of our virtual machine is stored in the form of a blob. For the purity of the experiment, we’ll create a new Storage Account called tststorage in the same data center as the cloud virtual machine to reduce overhead.
Inside Azure Storage, data can be stored as blobs or tables - see Azure Data Management and Business Analytics in the Windows Azure documentation. Tables are not tables in a strictly relational sense. These are just loosely structured sets of key-value pairs similar to what was once called SQL Data Services - see Introduction to SQL Azure. Compared to SDS, current tables can be partitioned by key. Different partitions are stored on different machines in the Cloud, thereby achieving horizontal scaling, as with sharding in the case of SQL Azure Database. Blobs are block and page. The structure of block blobs is optimized for sub-dock access, page - for random read / write. The page structure allows you to write a range of bytes into the blob. The difference between them is explained in detail, for example, here - blogs.msdn.com/b/windowsazurestorage/archive/2010/04/11/using-windows-azure-page-blobs-and-how-to-efficiently-upload-and- download-page-blobs.aspx. Virtual disks are stored as page blobs. Blobs are stored inside containers that are created as part of the Storage Account. Create a container1 container in the tststorage account for storage of AdventureWorks2012.bak.
The public container allows anyone to see the blobs contained in it. Public blob allows anyone to access any blob, but the contents of the container are not available. Finally, a private container means that to access the blob you will need to specify the Storage Account key. Subsequently, you can change the level of access to the container using the Edit Container button.
The backup of the database made in Script 1 will be uploaded to Azure Storage as a block blob for simplicity. For operations on blobs in the Cloud (as well as on tables and queues), you can use REST, which allows you to work directly via the Internet (HTTP Request / Response), attracting a wide range of development tools. The REST API for working with blobs is described here - msdn.microsoft.com/en-us/library/dd135733.aspx . So you can see which blobs are in the public container: tststorage.blob.core.windows.net/container1?restype=container&comp=list
Container container1 is now empty. To load AdventureWorks2012.bak into it, you need to use the PUT method:
using System;
using System.Net;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using System.Globalization;
class Program
{
static void Main (string [] args)
{
string fileFullName = @ "c: \ Temp \ AdventureWorks2012.bak"; //@Filec:\Temp\aaa.txt ";
string storageAccount = "tststorage";
string containerName = "container1";
string accessKey = "xws7rilyLjqdw8t75EHZbsIjbtwYDvpZw790lda0L1PgzEqKHxGNIDdCdQlPEvW5LdGWK / qOZFTs5xE4P93A5A ==";
HttpWebRequest req = (HttpWebRequest) WebRequest.Create (String.Format ("https: // {0} .blob.core.windows.net / {1} / {2}", storageAccount, containerName, Path.GetFileName (fileFullName) ));
FileStream fs = File.OpenRead (fileFullName);
byte [] fileContent = new byte [fs.Length];
fs.Read (fileContent, 0, fileContent.Length);
fs.Close ();
req.Method = "PUT";
req.ContentLength = fileContent.Length;
req.Headers.Add ("x-ms-blob-type", "BlockBlob");
req.Headers.Add ("x-ms-date", DateTime.UtcNow.ToString ("R", CultureInfo.InvariantCulture));
req.Headers.Add ("x-ms-version", "2011-08-18");
string canonicalizedString = BuildCanonicalizedString (req, String.Format ("/ {0} / {1} / {2}", storageAccount, containerName, Path.GetFileName (fileFullName)));
req.Headers ["Authorization"] = CreateAuthorizationHeader (canonicalizedString, storageAccount, accessKey);
req.Timeout = 100 * 60 * 1000;
Stream s = req.GetRequestStream ();
s.Write (fileContent, 0, fileContent.Length);
DateTime dt = DateTime.Now;
req.GetResponse ();
System.Diagnostics.Debug.WriteLine (DateTime.Now - dt);
}
static string CreateAuthorizationHeader (string canonicalizedString, string storageAccount, string accessKey)
{
HMACSHA256 hmacSha256 = new HMACSHA256 (Convert.FromBase64String (accessKey));
byte [] dataToHMAC = Encoding.UTF8.GetBytes (canonicalizedString);
string signature = Convert.ToBase64String (hmacSha256.ComputeHash (dataToHMAC));
return "SharedKey" + storageAccount + ":" + signature;
}
static string BuildCanonicalizedString (HttpWebRequest req, string canonicalizedResource)
{
StringBuilder sb = new StringBuilder ();
sb.Append (req.Method + "\ n \ n \ n");
sb.Append (String.Format ("{0} \ n \ n \ n \ n \ n \ n \ n \ n \ n", req.ContentLength));
sb.Append ("x-ms-blob-type:" + req.Headers ["x-ms-blob-type"] + '\ n');
sb.Append ("x-ms-date:" + req.Headers ["x-ms-date"] + '\ n');
sb.Append ("x-ms-version:" + req.Headers ["x-ms-version"] + '\ n');
sb.Append (canonicalizedResource);
return sb.ToString ();
}
}
Script 2
In this code, everything is pretty obvious except, perhaps, for one point. Despite the fact that container1 was created as a public container, recording a blob requires authorization. Who and what operations can be performed on blobs and containers depending on the set access level is described here -msdn.microsoft.com/en-us/library/dd179354.aspx. Regardless of the access level, the owner has the right to write. To authorize as an owner in an HTTP Request, you need to set the Authorization header. The string written to this header, in accordance with the requirements of authentication schemes, contains a signature, which is a Hash-based Message Authentication Code (HMAC) of the canonized string in the UTF-8 encoding, where the hash is calculated using the SHA256 algorithm based on the access key. The canonized string consists of the REST access method, the size of the uploaded file, blob type (x-ms-blob-type = block or page) date / time of the HTTP request in UTC format (x-ms-date), version date of the Azure blob service, serving this HTTP-request (x-ms-version), etc. There is no need to shine with high programming art, you only need painstaking and attentive, because
Access keys (primary and secondary) are formed at the stage of creating the Storage Account, you can see them in the container properties (Manage Keys). Any of them can be set as an accessKey for creating a digital signature during authorization - HMACSHA256 hmacSha256 = new HMACSHA256 (Convert.FromBase64String (accessKey));
For more granular rights management, you can use a Shared Access Signature. A shared signature allows you to create a policy that allows you to perform a specific operation, for example, recording within a specific container within the allotted time period. The person who receives the signature will be able to act within the framework of this policy. Another signature, for example, may authorize reading from another container for a different period.
Other comments.
• If a blob with that name exists in the container, it is silently frayed.
• The container name is case sensitive.
• The loading time obviously depends on the speed of the grid. For example, from work, this 45-megapixel backup was whistled for 00:01:07. From home it turned out many times slower.
In this demo, the backup was quite “childish” in size. Block blobs are limited to 200 GB. A block blob less than 64 MB in size can be loaded with a single write operation, as we saw in Script 2 example. Otherwise, it should be broken into pieces and loaded block by block using the Put Block / Put Block List methods. When uploading large files to Azure Storage, use page blobs. Page blob consists of 512-byte pages, its maximum size is 1 TB. An example of writing / reading a page blob page range is given here - blogs.msdn.com/b/windowsazurestorage/archive/2010/04/11/using-windows-azure-page-blobs-and-how-to-efficiently-upload- and-download-page-blobs.aspx .
For example, we will transfer our favorite AdventureWorks database in the form of its backup copy:
backup database AdventureWorks2012 to disk = 'c:\Temp\AdventureWorks2012.bak' with init, compression, stats = 10 Script 1
Files of small sizes, like this one, can be easily crafted without any further ado by using ordinary Copy / Paste to the remote desktop of the SQL Server virtual machine. It even occurs to me to create a shared folder on the virtual machine and copy it there using advanced copying tools with the ability to parallelize, correct and resume in case of failures, and also transfer the file via FTP. These methods are obvious. In this post, we will use a different method: transfer the backup file from the local machine to Azure Storage in the form of a blob and download it from there into the cloud virtual machine. We already have one Storage Account, created automatically when creating a virtual machine, in which there was automatically a container named vhds, in which the virtual disk of our virtual machine is stored in the form of a blob. For the purity of the experiment, we’ll create a new Storage Account called tststorage in the same data center as the cloud virtual machine to reduce overhead.
Inside Azure Storage, data can be stored as blobs or tables - see Azure Data Management and Business Analytics in the Windows Azure documentation. Tables are not tables in a strictly relational sense. These are just loosely structured sets of key-value pairs similar to what was once called SQL Data Services - see Introduction to SQL Azure. Compared to SDS, current tables can be partitioned by key. Different partitions are stored on different machines in the Cloud, thereby achieving horizontal scaling, as with sharding in the case of SQL Azure Database. Blobs are block and page. The structure of block blobs is optimized for sub-dock access, page - for random read / write. The page structure allows you to write a range of bytes into the blob. The difference between them is explained in detail, for example, here - blogs.msdn.com/b/windowsazurestorage/archive/2010/04/11/using-windows-azure-page-blobs-and-how-to-efficiently-upload-and- download-page-blobs.aspx. Virtual disks are stored as page blobs. Blobs are stored inside containers that are created as part of the Storage Account. Create a container1 container in the tststorage account for storage of AdventureWorks2012.bak.
The public container allows anyone to see the blobs contained in it. Public blob allows anyone to access any blob, but the contents of the container are not available. Finally, a private container means that to access the blob you will need to specify the Storage Account key. Subsequently, you can change the level of access to the container using the Edit Container button.
The backup of the database made in Script 1 will be uploaded to Azure Storage as a block blob for simplicity. For operations on blobs in the Cloud (as well as on tables and queues), you can use REST, which allows you to work directly via the Internet (HTTP Request / Response), attracting a wide range of development tools. The REST API for working with blobs is described here - msdn.microsoft.com/en-us/library/dd135733.aspx . So you can see which blobs are in the public container: tststorage.blob.core.windows.net/container1?restype=container&comp=list
Container container1 is now empty. To load AdventureWorks2012.bak into it, you need to use the PUT method:
using System;
using System.Net;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using System.Globalization;
class Program
{
static void Main (string [] args)
{
string fileFullName = @ "c: \ Temp \ AdventureWorks2012.bak"; //@Filec:\Temp\aaa.txt ";
string storageAccount = "tststorage";
string containerName = "container1";
string accessKey = "xws7rilyLjqdw8t75EHZbsIjbtwYDvpZw790lda0L1PgzEqKHxGNIDdCdQlPEvW5LdGWK / qOZFTs5xE4P93A5A ==";
HttpWebRequest req = (HttpWebRequest) WebRequest.Create (String.Format ("https: // {0} .blob.core.windows.net / {1} / {2}", storageAccount, containerName, Path.GetFileName (fileFullName) ));
FileStream fs = File.OpenRead (fileFullName);
byte [] fileContent = new byte [fs.Length];
fs.Read (fileContent, 0, fileContent.Length);
fs.Close ();
req.Method = "PUT";
req.ContentLength = fileContent.Length;
req.Headers.Add ("x-ms-blob-type", "BlockBlob");
req.Headers.Add ("x-ms-date", DateTime.UtcNow.ToString ("R", CultureInfo.InvariantCulture));
req.Headers.Add ("x-ms-version", "2011-08-18");
string canonicalizedString = BuildCanonicalizedString (req, String.Format ("/ {0} / {1} / {2}", storageAccount, containerName, Path.GetFileName (fileFullName)));
req.Headers ["Authorization"] = CreateAuthorizationHeader (canonicalizedString, storageAccount, accessKey);
req.Timeout = 100 * 60 * 1000;
Stream s = req.GetRequestStream ();
s.Write (fileContent, 0, fileContent.Length);
DateTime dt = DateTime.Now;
req.GetResponse ();
System.Diagnostics.Debug.WriteLine (DateTime.Now - dt);
}
static string CreateAuthorizationHeader (string canonicalizedString, string storageAccount, string accessKey)
{
HMACSHA256 hmacSha256 = new HMACSHA256 (Convert.FromBase64String (accessKey));
byte [] dataToHMAC = Encoding.UTF8.GetBytes (canonicalizedString);
string signature = Convert.ToBase64String (hmacSha256.ComputeHash (dataToHMAC));
return "SharedKey" + storageAccount + ":" + signature;
}
static string BuildCanonicalizedString (HttpWebRequest req, string canonicalizedResource)
{
StringBuilder sb = new StringBuilder ();
sb.Append (req.Method + "\ n \ n \ n");
sb.Append (String.Format ("{0} \ n \ n \ n \ n \ n \ n \ n \ n \ n", req.ContentLength));
sb.Append ("x-ms-blob-type:" + req.Headers ["x-ms-blob-type"] + '\ n');
sb.Append ("x-ms-date:" + req.Headers ["x-ms-date"] + '\ n');
sb.Append ("x-ms-version:" + req.Headers ["x-ms-version"] + '\ n');
sb.Append (canonicalizedResource);
return sb.ToString ();
}
}
Script 2
In this code, everything is pretty obvious except, perhaps, for one point. Despite the fact that container1 was created as a public container, recording a blob requires authorization. Who and what operations can be performed on blobs and containers depending on the set access level is described here -msdn.microsoft.com/en-us/library/dd179354.aspx. Regardless of the access level, the owner has the right to write. To authorize as an owner in an HTTP Request, you need to set the Authorization header. The string written to this header, in accordance with the requirements of authentication schemes, contains a signature, which is a Hash-based Message Authentication Code (HMAC) of the canonized string in the UTF-8 encoding, where the hash is calculated using the SHA256 algorithm based on the access key. The canonized string consists of the REST access method, the size of the uploaded file, blob type (x-ms-blob-type = block or page) date / time of the HTTP request in UTC format (x-ms-date), version date of the Azure blob service, serving this HTTP-request (x-ms-version), etc. There is no need to shine with high programming art, you only need painstaking and attentive, because
Access keys (primary and secondary) are formed at the stage of creating the Storage Account, you can see them in the container properties (Manage Keys). Any of them can be set as an accessKey for creating a digital signature during authorization - HMACSHA256 hmacSha256 = new HMACSHA256 (Convert.FromBase64String (accessKey));
For more granular rights management, you can use a Shared Access Signature. A shared signature allows you to create a policy that allows you to perform a specific operation, for example, recording within a specific container within the allotted time period. The person who receives the signature will be able to act within the framework of this policy. Another signature, for example, may authorize reading from another container for a different period.
Other comments.
• If a blob with that name exists in the container, it is silently frayed.
• The container name is case sensitive.
• The loading time obviously depends on the speed of the grid. For example, from work, this 45-megapixel backup was whistled for 00:01:07. From home it turned out many times slower.
In this demo, the backup was quite “childish” in size. Block blobs are limited to 200 GB. A block blob less than 64 MB in size can be loaded with a single write operation, as we saw in Script 2 example. Otherwise, it should be broken into pieces and loaded block by block using the Put Block / Put Block List methods. When uploading large files to Azure Storage, use page blobs. Page blob consists of 512-byte pages, its maximum size is 1 TB. An example of writing / reading a page blob page range is given here - blogs.msdn.com/b/windowsazurestorage/archive/2010/04/11/using-windows-azure-page-blobs-and-how-to-efficiently-upload- and-download-page-blobs.aspx .