Create backup copies of SQL Server 2014 CTP2 database in Windows Azure
Actually, the idea to reserve a base in the Cloud is not new. This functionality appeared in the current SQL Server 2012 with the release of 01/01/2013 of the cumulative update No. 2 to Service Pack 1 ( Build 11.0.3339.0 ). We analyzed it in the post Create a backup copy of the database in Azure Storage . Therefore, I will not repeat that this is a very convenient feature that provides data migration from on-premise SQL Server to the cloud (IaaS) and vice versa, as well as facilitates the transfer of the database between two separated on-premise SQL Servers when the Cloud performs the role of a hub. Until now, this could only be done from the T-SQL script. In the 2014 CTP version, two new features have been added to this process. First, you can now back up to Windows Azure directly from the SQL Server Management Studio interface and include it on a regular basis in an automated database support plan , and second, and importantly, encrypt backups using certificates or asymmetric keys on based cryptographic protection algorithms. However, first things first.
In this post, I will do, in fact, the same as in the previous one , just not from the command line, but from a new graphical interface. In contrast to the laconic scripting solution, there will be more pictures.
To begin, we, as you might guess, will need, in fact, SQL Server 2014 CTP2, which can be taken here . This trial version expires March 31, 2014. When downloading, you will need to select the distribution type (iso / cab / Azure), bit depth (32-bit or 64-bit) and language. Currently available in English, German, Spanish, Japanese, Chinese (Traditional). In the release of Russian, as always, will be supported, well, so far here is what is. Installation does not cause any surprises / difficulties compared to the current version. I put 2014 CTP2 side by side with a named instance near 2012. They coexist normally, do not fight, unlike PAGE1 . You can begin to experiment.
We start new SQL Server Management Studio. For happy owners of Windows 8.x who do not use the detailed tiles on the start screen, where they are grouped by SQL Server 2012, SQL Server 2014, etc.
Pic1
')
but prefer to quickly type something like "sql" in the search bar:
Pic2
Now there are several of these tiles. You need to select the one that belongs to C: \ ProgramData \ Microsoft \ Windows \ Start Menu \ Programs \ Microsoft SQL Server 2014 CTP2, i.e. refers to C: \ Program Files (x86) \ Microsoft SQL Server \ 120 \ Tools \ Binn \ ManagementStudio \ Ssms.exe.
In SSMS, from the context menu of the database, we choose, as always, Tasks -> Back Up and note that in the new backup interface, along with the traditional disk, another URL has appeared as an alternative option to host a backup.
Pic.3
If in SSMS 2014 CTP2 you connect an instance of server version 2012 and below, this option will not be available for it. For instances of 2014 PP2, it is available regardless of the compatibility level in which the base is located.
After selecting Back up to = URL, the interface of the Destination section is modified so as to provide the ability to enter parameters that we previously used in the BACKUP DATABASE ... TO URL command (see Script 2). Required to ask. File name - the name of the backup file that will be saved in the Windows Azure history.
Pic.4
Pressing the Create button next to SQL Credential corresponds to the execution of the CREATE CREDENTIAL command (see Script 2). To confirm authenticity when accessing a cloud subscription, you can use a management certificate or a publication profile (a file with the .publishsettings extension). We used the second method in the post Introduction to the development and testing of applications in the Cloud (Figure 16-17), so here for a change we will look at the first method.
Pic.5
Open the Visual Studio command line on behalf of the administrator and create a self-signed certificate for working with Windows Azure with a key length of at least 2048 bits, which is stored in the default certificate store - Personal.
Script 1
Pic.6
If Visual Studio is not present, you can download the makecert utility, for example, as part of the Windows 8 SDK . After that, you need to look for it somewhere in C: \ Program Files (x86) \ Windows Kits \ 8.x \ bin \ x64.
To verify that the certificate was created successfully, we call the usual command window (again, better as an administrator) and enter certmgr.msc. In the Personal section we see the newly created certificate.
Fig.7
We export it (without a private key) to a .cer file. From the context menu of the certificate, select All Tasks -> Export:
Fig.8
Fig.9
Pic.10
Now we go to the Windows Azure administration portal, select the last Settings item in the panel on the left, Management Certificates in the top menu, and click the Upload button at the bottom and specify the .cer file exported in Figure 10.
Figure 11
It appears in the list of certificates:
Fig.12
We return to Figure 5, click the Select button (management certificate from the certificate store) and in the opened window select the line with the newly created AzureCert certificate:
Fig.13
A thumbprint of the specified certificate appears in the text box to the left of the Select button.
It remains to enter a subscription - its name or ID can be read there, in the Settings Portal of the Windows Azure Administration (Fig.11-12), if in the top menu move to the left by the Subcriptions item.
Fig.14
After entering the Windows Azure Management Certificate and Subscription ID, we move to the Storage Account box. There is a slight pause, during which SSMS tries to go to the cloud, indicating the specified subscription and certificate, it all works out and the list of Azure storage accounts belonging to this subscription opens at the bottom.
Fig.15
We select the interesting one, in which the backup will take place, and press the Create button. Credential is created, and we return to Fig.4. It remains to specify the container of the selected Azure storage account. As well as in the previous post , I will specify the public container container1. The prefix URL field is automatically populated. If everything is correct, editing is not required.
Figure 16
Click the OK button, the backup starts to run.
Pic.17
After it is finished, go to the container1 container tststorage storage account on the Windows Azure management portal and observe the blob that was recently created in it, which contains the backup file for the AdventureWorks database:
Pic.18
Recovery is similar.
In the next series, we will analyze the new encryption features of backups.
In this post, I will do, in fact, the same as in the previous one , just not from the command line, but from a new graphical interface. In contrast to the laconic scripting solution, there will be more pictures.
To begin, we, as you might guess, will need, in fact, SQL Server 2014 CTP2, which can be taken here . This trial version expires March 31, 2014. When downloading, you will need to select the distribution type (iso / cab / Azure), bit depth (32-bit or 64-bit) and language. Currently available in English, German, Spanish, Japanese, Chinese (Traditional). In the release of Russian, as always, will be supported, well, so far here is what is. Installation does not cause any surprises / difficulties compared to the current version. I put 2014 CTP2 side by side with a named instance near 2012. They coexist normally, do not fight, unlike PAGE1 . You can begin to experiment.
We start new SQL Server Management Studio. For happy owners of Windows 8.x who do not use the detailed tiles on the start screen, where they are grouped by SQL Server 2012, SQL Server 2014, etc.
Pic1
')
but prefer to quickly type something like "sql" in the search bar:
Pic2
Now there are several of these tiles. You need to select the one that belongs to C: \ ProgramData \ Microsoft \ Windows \ Start Menu \ Programs \ Microsoft SQL Server 2014 CTP2, i.e. refers to C: \ Program Files (x86) \ Microsoft SQL Server \ 120 \ Tools \ Binn \ ManagementStudio \ Ssms.exe.
In SSMS, from the context menu of the database, we choose, as always, Tasks -> Back Up and note that in the new backup interface, along with the traditional disk, another URL has appeared as an alternative option to host a backup.
Pic.3
If in SSMS 2014 CTP2 you connect an instance of server version 2012 and below, this option will not be available for it. For instances of 2014 PP2, it is available regardless of the compatibility level in which the base is located.
After selecting Back up to = URL, the interface of the Destination section is modified so as to provide the ability to enter parameters that we previously used in the BACKUP DATABASE ... TO URL command (see Script 2). Required to ask. File name - the name of the backup file that will be saved in the Windows Azure history.
Pic.4
Pressing the Create button next to SQL Credential corresponds to the execution of the CREATE CREDENTIAL command (see Script 2). To confirm authenticity when accessing a cloud subscription, you can use a management certificate or a publication profile (a file with the .publishsettings extension). We used the second method in the post Introduction to the development and testing of applications in the Cloud (Figure 16-17), so here for a change we will look at the first method.
Pic.5
Open the Visual Studio command line on behalf of the administrator and create a self-signed certificate for working with Windows Azure with a key length of at least 2048 bits, which is stored in the default certificate store - Personal.
makecert -sky exchange -r -n "CN=AzureCert" -pe -a sha1 -len 2048 -ss MyScript 1
Pic.6
If Visual Studio is not present, you can download the makecert utility, for example, as part of the Windows 8 SDK . After that, you need to look for it somewhere in C: \ Program Files (x86) \ Windows Kits \ 8.x \ bin \ x64.
To verify that the certificate was created successfully, we call the usual command window (again, better as an administrator) and enter certmgr.msc. In the Personal section we see the newly created certificate.
Fig.7
We export it (without a private key) to a .cer file. From the context menu of the certificate, select All Tasks -> Export:
Fig.8
Fig.9
Pic.10
Now we go to the Windows Azure administration portal, select the last Settings item in the panel on the left, Management Certificates in the top menu, and click the Upload button at the bottom and specify the .cer file exported in Figure 10.
Figure 11
It appears in the list of certificates:
Fig.12
We return to Figure 5, click the Select button (management certificate from the certificate store) and in the opened window select the line with the newly created AzureCert certificate:
Fig.13
A thumbprint of the specified certificate appears in the text box to the left of the Select button.
It remains to enter a subscription - its name or ID can be read there, in the Settings Portal of the Windows Azure Administration (Fig.11-12), if in the top menu move to the left by the Subcriptions item.
Fig.14
After entering the Windows Azure Management Certificate and Subscription ID, we move to the Storage Account box. There is a slight pause, during which SSMS tries to go to the cloud, indicating the specified subscription and certificate, it all works out and the list of Azure storage accounts belonging to this subscription opens at the bottom.
Fig.15
We select the interesting one, in which the backup will take place, and press the Create button. Credential is created, and we return to Fig.4. It remains to specify the container of the selected Azure storage account. As well as in the previous post , I will specify the public container container1. The prefix URL field is automatically populated. If everything is correct, editing is not required.
Figure 16
Click the OK button, the backup starts to run.
Pic.17
After it is finished, go to the container1 container tststorage storage account on the Windows Azure management portal and observe the blob that was recently created in it, which contains the backup file for the AdventureWorks database:
Pic.18
Recovery is similar.
In the next series, we will analyze the new encryption features of backups.
Source: https://habr.com/ru/post/201568/
All Articles