Database Migration to Windows Azure SQL VM via Virtual Disk
The previous way to transfer the backup database from the local computer to a virtual machine with a SQL Server in the Cloud used Azure Storage, which is not NTFS-visible. Thus, before restoring the backup, the database had to be copied from Azure Storage to a virtual disk so that the SQL Server installed on the virtual server saw it. In the case of a large database, this leads to unnecessary space consumption in Azure Storage and, as a result, additional costs: first, the backup is loaded into the cloud storage, and then copied to vhd, which is stored there. To avoid these costs, in this article we will consider a different way. A separate vhd will be created locally, on which the database backup will be placed. Then vhd will be loaded into Azure Storage and attached as an additional cloud virtual machine disk. A backup copy will be restored from it.
To create a virtual disk locally, go to Administrative Tools \ Computer Management \ Disk Management and select the Create VHD item from the context menu. Specify the full path to the vhd-file to be created, the desired size, sufficient to accommodate the backup database, as well - whether this size is fixed or dynamically expandable. We click on the disk and select the item “Initialize the disk” from the context menu. Create a volume and format it under NTFS. The created vhd begins to be seen as an additional disk of the local machine. We copy on it a base.
To check the created vhd can be added as an additional disk to the local virtual machine. Just in case, let me remind you that in Windows 8 Hyper-V virtualization technology (including the Hyper-V Manager management tool) is supported out of the box. You do not need to install Hyper-V Server separately and administer using RSAT, as was the case with Windows 7. I occasionally have a local virtual machine, which I do shutdown and attach an additional disk. Before starting the vhd virtual machine, you need to write to Disk Management on the host. After making sure that the contents of the disk are normally read on the local virtual machine, extinguish it, disconnect the disk by clicking the Remove button in Figure 10, and load the disk into Azure Storage. The easiest way to do this is with the csupload utility, which is part of the Windows Azure SDK, which we installed in the previous article . Before downloading, you must first create an X.509 v3 certificate, as specified in the documentation. To do this, I will use the makecert utility, which is installed, for example, as part of Visual Studio:
Script 1
Now the certificate must be put into the Cloud. Go to the Azure Management Portal, click on Settings in the left pane, click the Upload button below. Find the certificate file c: \ Temp \ MyCert.cer, created in Script 1 and specify it for download. The certificate is successfully uploaded. To upload vhd to Azure Storage there must be a Storage Account. At least one was created automatically during the creation of a virtual machine , and the second we created in the previous article to load the backup file. It does not matter which one to use. I'll take the last one (tststorage) in which I will create a private container myvhds to put a newly created virtual disk with a backup of the base there. Let me remind you that capital letters in the name of the container are not perceived. To use the csupload utility, you need to form a connection string. This can be done directly at the time of loading or in advance.
')
The ServiceManagementEndpoint is a constant. The main parameters when forming the connection string are the SubscriptionID and the CertificateThumbprint. They can be copied in the certificate properties. The widths of the columns are moved apart and, if necessary, scrolled horizontally. In the direct loading of the disk, everything is also clear: where do we get the vhd-file (LiteralPath), where we put (Storage Account \ container of the blob storage site - Destination) and how we call (Label). The Add-Disk parameter indicates that the disk is being loaded, and not, say, an image. The OS parameter is that it will be a disk for a Windows virtual machine, and not Linux. By default, it is assumed, of course, Windows. You can get acquainted with the parameters of the csupload utility in the documentation .
Script 3
After loading, the newly installed disk can be seen in the Azure Management Portal -> Virtual Machines -> Disks. It remains to attach it to the cloud VM. Virtual can not extinguish. In the upper line, we switch to Virtual Machine Instances, in the lower line, click Attach and select the disk among the available downloaded and unconnected ones. In our example, this is now just one. We are waiting for the virtual status to change from Running (Updating) to just Running and connect to it via a remote RDP connection. We see the F disk: with Volume Label = aaa, as ordered, and there is a backup copy of the AdventureWorks2012.bak database on which we are recovering. Then the aaa disk is no longer needed. We disconnect it in the same way as the connection (the Detach Disk button on the bottom line) and delete it from the disks along with its corresponding vhd-file (not from Storage) by clicking the Delete Disk -> Delete the associated VHD button so as not to pay for the extra space.
To create a virtual disk locally, go to Administrative Tools \ Computer Management \ Disk Management and select the Create VHD item from the context menu. Specify the full path to the vhd-file to be created, the desired size, sufficient to accommodate the backup database, as well - whether this size is fixed or dynamically expandable. We click on the disk and select the item “Initialize the disk” from the context menu. Create a volume and format it under NTFS. The created vhd begins to be seen as an additional disk of the local machine. We copy on it a base.
To check the created vhd can be added as an additional disk to the local virtual machine. Just in case, let me remind you that in Windows 8 Hyper-V virtualization technology (including the Hyper-V Manager management tool) is supported out of the box. You do not need to install Hyper-V Server separately and administer using RSAT, as was the case with Windows 7. I occasionally have a local virtual machine, which I do shutdown and attach an additional disk. Before starting the vhd virtual machine, you need to write to Disk Management on the host. After making sure that the contents of the disk are normally read on the local virtual machine, extinguish it, disconnect the disk by clicking the Remove button in Figure 10, and load the disk into Azure Storage. The easiest way to do this is with the csupload utility, which is part of the Windows Azure SDK, which we installed in the previous article . Before downloading, you must first create an X.509 v3 certificate, as specified in the documentation. To do this, I will use the makecert utility, which is installed, for example, as part of Visual Studio:
"C:\Program Files (x86)\Windows Kits\8.0\bin\x86\makecert.exe" -sky exchange -r -n "CN=MyCert" -pe -a sha1 -len 2048 -ss My "c:\Temp\MyCert.cer"
Script 1
Now the certificate must be put into the Cloud. Go to the Azure Management Portal, click on Settings in the left pane, click the Upload button below. Find the certificate file c: \ Temp \ MyCert.cer, created in Script 1 and specify it for download. The certificate is successfully uploaded. To upload vhd to Azure Storage there must be a Storage Account. At least one was created automatically during the creation of a virtual machine , and the second we created in the previous article to load the backup file. It does not matter which one to use. I'll take the last one (tststorage) in which I will create a private container myvhds to put a newly created virtual disk with a backup of the base there. Let me remind you that capital letters in the name of the container are not perceived. To use the csupload utility, you need to form a connection string. This can be done directly at the time of loading or in advance.
')
"C:\Program Files\Microsoft SDKs\Windows Azure\.NET SDK\2012-06\bin\csupload.exe" Set-Connection "SubscriptionID=1145d36f-60d2-4db3-91e7-7cd730599e27;CertificateThumbprint=8BC0B6D0C1A010CABECF558612A21D2CCDFD679F;ServiceManagementEndpoint=https://management.core.windows.net"
Script 2The ServiceManagementEndpoint is a constant. The main parameters when forming the connection string are the SubscriptionID and the CertificateThumbprint. They can be copied in the certificate properties. The widths of the columns are moved apart and, if necessary, scrolled horizontally. In the direct loading of the disk, everything is also clear: where do we get the vhd-file (LiteralPath), where we put (Storage Account \ container of the blob storage site - Destination) and how we call (Label). The Add-Disk parameter indicates that the disk is being loaded, and not, say, an image. The OS parameter is that it will be a disk for a Windows virtual machine, and not Linux. By default, it is assumed, of course, Windows. You can get acquainted with the parameters of the csupload utility in the documentation .
"C:\Program Files\Microsoft SDKs\Windows Azure\.NET SDK\2012-06\bin\csupload.exe" Add-Disk -Destination "https://tststorage.blob.core.windows.net/myvhds/aaa_disk" -Label aaa_disk -LiteralPath "c:\temp\aaa.vhd" -OS WindowsScript 3
After loading, the newly installed disk can be seen in the Azure Management Portal -> Virtual Machines -> Disks. It remains to attach it to the cloud VM. Virtual can not extinguish. In the upper line, we switch to Virtual Machine Instances, in the lower line, click Attach and select the disk among the available downloaded and unconnected ones. In our example, this is now just one. We are waiting for the virtual status to change from Running (Updating) to just Running and connect to it via a remote RDP connection. We see the F disk: with Volume Label = aaa, as ordered, and there is a backup copy of the AdventureWorks2012.bak database on which we are recovering. Then the aaa disk is no longer needed. We disconnect it in the same way as the connection (the Detach Disk button on the bottom line) and delete it from the disks along with its corresponding vhd-file (not from Storage) by clicking the Delete Disk -> Delete the associated VHD button so as not to pay for the extra space.
Source: https://habr.com/ru/post/156951/
All Articles