SQL Server 2014 cloud features: Seamless integration with public cloud

This is my manuscripts in IT168, the original address: http://tech.it168.com/a2014/0620/1637/000001637358_all.shtml

The IT industry has entered the cloud era, and more and more enterprise IT infrastructures will be deployed in public, private, or hybrid clouds, and the database as the most important part of the IT infrastructure and the cloud will become very important. SQL Server 2014 opens up the relationship between the public cloud and the organization, whether it's running on-premises or on a Microsoft Azure virtual machine, and SQL Server 2014 directly supports deploying data files and logs to Microsoft Azure Public cloud storage. This allows you to seamlessly reach the boundaries of public and private clouds, as shown in Architecture 1.

Figure 1. SQL Server 2014 directly supports the deployment of database files to Azure BLOBs

Why you need to deploy SQL Server data files in Microsoft Azure

Deploying data directly in Azure BLOB storage can directly bring benefits such as performance, data migration, data virtualization, high availability, and disaster preparedness. But the main application scenarios and advantages are as follows:

• portability : In an azure virtual machine environment, deploying data in an azure blob is easier to migrate, simply separating the database and attaching it to another azure virtual machine without moving the database file itself.
• Database Virtualization : In a cloud environment that provides services to tenants, you can smoothly move databases on high-load virtual machines to other virtual machines with the inclusion database feature after SQL Server 2012, which does not affect the proper functioning of the virtual machine environment.
• high availability and disaster preparedness: Because the database files are now located on the BLOB storage in Microsoft Azure, even if the virtual machine itself crashes, you only need to attach the database files to another standby machine. The database can be recovered in a very short time and the data itself is not affected by virtual machine corruption, thus guaranteeing high RTO and RPO.
• Scalability: Storage ioPS are subject to specific environments, both on azure virtual machines and within the enterprise, and IOPS can be very high on azure blob storage.

The new interaction module for SQL Server 2014 and Azure blob storage is not just an adaptation mechanism in an existing software environment, but is integrated directly into the SQL Server storage engine. Data access mechanism 2 is shown.

Figure 2. Data access mechanism for database files on Azure blobs

As can be seen from Figure 2, the data interaction mechanism eliminates the need for virtual machines to interact with the host and IO channels, where the virtual machine can call storage directly from the rest API. This allows better performance than accessing local storage directly on a virtual machine.

Deploying SQL Server data files in Microsoft Azure

The following example shows how to deploy a SQL Server database file on Azure blob storage. First, you need to establish a container for storing SQL Server database files in azure storage, as shown in 3.

Figure 3: Creating a container for storing database files

The container is named Testsqlfile, the access type is private, and the container name identifies the access address of the database file, as shown in 4.

Figure 4. Creating a new container

Next, create a policy that accesses the container and a shared access signature (signature). SQL Server requires this policy and signature to interact with data files on Azure storage, to create policies and signatures in a programmatic way, or to use rest APIs, which in this case is created using CodePlex's existing tool (available in HTTP// azurestorageexplorer.codeplex.com download).

First find the account name and access key to access the store in the Microsoft Portal, as shown in 5.

Figure 5. Find the account name and access key

Use this tool to create policies and access signatures, as shown in 6 and Figure 7.

Figure 6: Creating a Policy

Figure 7: Generating a signature

Once the signature has been generated successfully, you can establish the credentials in SQL Server to access the Azure storage container through the following code:

CREATE Credential [https:

 with IDENTITY=' SHARED ACCESS SIGNATURE ',

' Sr=c&si=newpolicy&sig=%2fhfh82xmxmyspgvc404wqbk6giuffrxmekkxcmiogwa%3d '

After the credential is established, you can create a database on BLOB storage in SQL Server 2014 with this credential, creating 3 data files and a log file in this example, totaling 31GB, creating 36 seconds, and 8 as shown.

Figure 8: Creating a database file on Azure storage

Now that you are connected to Azure's storage environment through SQL Server Management Studio, you will be able to see the database file you just created, as shown in 9.

Figure 9. Database files created on an azure blob

Summarize

SQL Server 2014 has deeper integration with Microsoft Azure and hides unnecessary detail through the storage engine, allowing users to create a database that stores database files on Azure storage with the ability to create a normal database, resulting in performance, availability, scalability , disaster preparedness, and even data virtualization benefits.