SQL Server 2014中的混合云和Hekaton功能

介绍 (Introduction)

Microsoft SQL Server 2014 is considered to be the first version that supports Hybrid Cloud by adding a lot of exciting new features.

通过添加许多令人兴奋的新功能，Microsoft SQL Server 2014被认为是支持混合云的第一个版本。

In this article, I will cover some of the top new features in these main points including Hekaton and Hybrid Cloud enhancements:

在本文中，我将介绍这些要点的一些主要新功能，包括Hekaton和混合云增强功能：

赫卡顿 (Hekaton)

Hekaton is the code name of the new feature of In-Memory OLTP. It is a new database engine, fully integrated with SQL server and designed to enhance memory resident data and OLTP workloads. In simple words, with Hekaton we can store the entire table in memory.

Hekaton是内存中OLTP新功能的代号。 它是一个新的数据库引擎，与SQL Server完全集成，旨在增强内存驻留数据和OLTP工作负载。 简而言之，使用Hekaton，我们可以将整个表存储在内存中。

Let’s list some of the benefits of this new feature:

让我们列出此新功能的一些好处：

• Memory-Optimized-Tables can be accessed using T-SQL like Disk-Based-Tables.
  可以使用T-SQL（如基于磁盘的表）访问内存优化表。
• Both of Memory-Optimized-Tables and Disk-Based-Tables can reference in the same query, and also we can update both types of tables by one transaction.
  内存优化表和基于磁盘的表都可以在同一查询中引用，并且我们可以通过一个事务更新这两种类型的表。
• Stored procedures that only reference Memory-Optimized-Tables can natively compile into machine code which results in improving performance.
  仅引用内存优化表的存储过程可以本地编译为机器代码，从而提高性能。
• This new engine designed for a high level of session concurrency for OLTP transactions.
  这个新引擎设计用于OLTP事务的高级别会话并发。

There are still some limitations for Memory-Optimized-Tables in SQL server 2014 which are:

SQL Server 2014中的内存优化表仍然存在一些限制，这些限制是：

• ALTER TABLE statement, SP_RENAME stored procedure, ALTER BUCKET_COUNT statement, and add\remove index outside statement of CREATE TABLE, all of these not supported by In-Memory table
  ALTER TABLE语句，SP_RENAME存储过程，ALTER BUCKET_COUNT语句以及CREATE TABLE语句外的添加/删除索引，内存表不支持所有这些
• Some constraints not supported like (CHECK, FOREIGN KEY, UNIQUE)
  不支持某些约束，例如（CHECK，FOREIGN KEY，UNIQUE）
• RANGE INDEXES and TRIGGERS not supported by In-Memory table
  内存表不支持RANGE INDEXES和TRIGGERS
• REPLICATION, MIRRORING, and LINKED SERVERS are incompatible with Memory-Optimized-Tables.
  复制，镜像和链接服务器与内存优化表不兼容。

To know more information, you can check SQL Server Support for In-Memory OLTP.

要了解更多信息，可以检查SQL Server对内存中OLTP的支持 。

Memory-Optimized-Tables are appropriate for the following scenarios:

内存优化表适用于以下情况：

• A table has a high insertion rate of data from multiple concurrent sources
  一个表具有来自多个并发源的高数据插入率
• A table cannot meet scale-up requirements for high performance of reading operations especially with periodic batch inserts and updates
  表格无法满足放大读取的高性能要求，尤其是对于定期批量插入和更新而言
• Intensive logic processing inside a stored procedure
  存储过程内部的密集逻辑处理
• A database solution cannot achieve low latency business transaction
  数据库解决方案无法实现低延迟的业务交易

Let’s now go through the steps to create a Memory-Optimized-Table

现在让我们完成创建内存优化表的步骤

1. 
   
   

   
   

   Figure 1 – Creating MEMORY-OPTIMIZED-DATA file group 图1 –创建MEMORY-OPTIMIZED-DATA文件组

   Note: You can’t create more than one MEMORY-OPTIMIZED-DATA filegroup at the same database.

   注意：在同一数据库上不能创建多个MEMORY-OPTIMIZED-DATA文件组。

   Then, we will add a file to this filegroup. Also, we can implement this step using the following code:

   然后，我们将文件添加到该文件组。 另外，我们可以使用以下代码实现此步骤：

    
   USE [master]
   GO
   ALTER DATABASE [SQL_Shack2014]
     ADD FILEGROUP [MemoryOptimized] CONTAINS MEMORY_OPTIMIZED_DATA 
   GO
   ALTER DATABASE [SQL_Shack2014] 
     ADD FILE (NAME = N'MemoryOptimizedHekaton2014', FILENAME = N'D:\MemoryOptimizedHekaton2014') 
     TO FILEGROUP [MemoryOptimized]
   GO
    
   

2. Create a MEMORY-OPTIMIZED-TABLE using the following code as example:

   使用以下代码作为示例创建一个MEMORY-OPTIMIZED-TABLE：

    
   USE [SQL_Shack2014]
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE TABLE DailyWorkRate(
    WorkRateID int NOT NULL,
    WorkRateDate datetime NOT NULL,
    WorkCode nchar (3),
    WorkAverageRate money NOT NULL,
    EndOfWorkDayRate money NOT NULL,
    WorkModifiedDate datetime NOT NULL,
    CONSTRAINT PK_DailyWorkRate_WorkRateID PRIMARY KEY NONCLUSTERED HASH (WorkRateID) WITH (BUCKET_COUNT = 1024),
   ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
   GO
    
   

   混合云增强 (Hybrid Cloud enhancements)

   Hybrid Cloud is a cloud computing environment that uses a mix of On-premises private cloud and public cloud platforms.

   混合云是一种使用本地私有云和公共云平台混合的云计算环境。

   SQL server 2014 provides this feature through three new solutions:

   SQL Server 2014通过三个新解决方案提供了此功能：

   1. Windows Azure中的数据文件 (1. Data files in Windows Azure)

   Means that now, we can have databases created in an on-premises or in a Windows Azure virtual machine which its data and log files stored in windows azure storage.

   意味着现在，我们可以在本地或Windows Azure虚拟机中创建数据库，并将其数据和日志文件存储在Windows Azure存储中。

   
   
   

   
   

   Figure 2 – SQL Server Data Files in Windows Azure 图2 – Windows Azure中SQL Server数据文件

   So we can move our storage to the cloud while keeping all transactions running on the local server, which gives us the following benefits:

   因此，我们可以将存储移动到云中，同时保持所有事务在本地服务器上运行，这给我们带来了以下好处：

   • Less downtime and easy migration
     减少停机时间，易于迁移
   • Less cost and more storage for large environments
     成本更低，适用于大型环境的更多存储
   • Easier high availability and disaster recovery solutions
     更轻松的高可用性和灾难恢复解决方案
   • Separates the compute instance from the storage instance which means we can use Transparent Data Encryption (TDE) certificates which are stored locally in the master database to encrypt all data that resides on the public cloud
     将计算实例与存储实例分开，这意味着我们可以使用存储在主数据库本地的透明数据加密（TDE）证书来加密驻留在公共云上的所有数据

   We can use the following code to create a database whose files will be stored on a Windows Azure storage container and policy that generated a Shared Access Signature (SAS) key:

   我们可以使用以下代码创建一个数据库，该数据库的文件将存储在Windows Azure存储容器中，并生成了共享访问签名（SAS）密钥的策略：

    
   -- Create a credential  
   CREATE CREDENTIAL [https://.......]  
   WITH IDENTITY='SHARED ACCESS SIGNATURE',  
   SECRET = 'your SAS key'  
   -- Create database with data and log files in Windows Azure container.  
   CREATE DATABASE testdb   
   ON  
   (NAME = SQL_SHACK,  
       FILENAME = N'https://......../SQL_SHACK.mdf')  
    LOG ON  
   (NAME = testdb_log,  
       FILENAME = N'https://....../SQL_SHACK.ldf') 
    
   

   2.将数据库部署到Microsoft Azure虚拟机 (2. Deploy databases to a Microsoft Azure Virtual Machine )

   There is a new wizard option added to SQL Server 2014 for deploying a database from an instance of the database engine to SQL Server in Windows Azure Virtual machine using full database backup operation.

   SQL Server 2014中添加了一个新的向导选项，用于使用完整数据库备份操作将数据库从数据库引擎实例部署到Windows Azure虚拟机中SQL Server。

   There are some requirements you must know to complete this wizard successfully:

   成功完成此向导必须满足一些要求：

   • Details of the Microsoft account linked to your Windows Azure subscription
     链接到Windows Azure订阅的Microsoft帐户的详细信息
   • Your Windows Azure publishing profile
     您的Windows Azure发布配置文件
   • The management certificate linked to your Windows Azure subscription
     链接到Windows Azure订阅的管理证书
   • The management certificate saved to the computer where the wizard is running
     将管理证书保存到运行向导的计算机上
   • Temporary storage is available to the computer where the wizard is running and also available to the computer where the database hosted
     临时存储可用于运行向导的计算机，也可用于托管数据库的计算机
   • The SQL Server instance –you are deploying to- must be configured to listen on a TCP/IP port
     必须将要部署到SQL Server实例配置为侦听TCP / IP端口
   • The VM must have the SQL Server Cloud Adapter configured and ran
     VM必须已配置并运行SQL Server云适配器
   • You must configure an open endpoint for your SQL Server Cloud Adapter on the Windows Azure gateway with private port 11435
     您必须使用专用端口11435在Windows Azure网关上为SQL Server云适配器配置开放终结点

   To start the wizard follow these steps:

   要启动向导，请按照下列步骤操作：

   • Connect with SSMS to your desired instance.
     使用SSMS连接到所需实例。

   • In object explorer, right click on your desired database then choose tasks then select deploy database to Windows Azure VM to launch the following introduction screen:

     在对象资源管理器中，右键单击所需的数据库，然后选择任务，然后选择将数据库部署到Windows Azure VM以启动以下介绍屏幕：

     
     
     
     Figure 3 – Deploy Data database to Windows Azure VM wizard (Introduction section)b 图3 –将数据数据库部署到Windows Azure VM向导（“简介”部分）b
   • Complete its sections by providing the information requested which you prepared according to the above requirements.
     通过提供您根据上述要求准备的所需信息来完成其部分。

   3.备份到Windows Azure存储并从中还原 (3. Backup to and restore from Windows Azure Storage)

   This feature introduced in SQL Server 2012 SP1, but it was supported only using T-SQL.

   SQL Server 2012 SP1中引入了此功能，但仅使用T-SQL才支持。

   Now in SQL Server 2014, we can use SSMS to backup to Windows Azure Storage by filling in these requirements as shown in figure 3:

   现在在SQL Server 2014中，我们可以通过填写以下要求使用SSMS备份到Windows Azure存储，如图3所示：

   
   
   

   
   

   Figure 4 – Backup to Windows Azure Storage 图4 –备份到Windows Azure存储
   • File name: the name of the backup file you want to specify
     文件名：您要指定的备份文件的名称
   • SQL credential: it stores the name of the storage account and the access key values, and you can specify an existing one or create a new one
     SQL凭证：它存储存储帐户的名称和访问密钥值，您可以指定一个现有帐户或创建一个新帐户
   • Azure storage container: The name of the Windows Azure storage container to store the backup files
     Azure存储容器：Windows Azure存储容器的名称，用于存储备份文件
   • URL prefix: this is generated automatically according to the previous information
     URL前缀：根据前面的信息自动生成

   We can use SSMS to restore from Windows Azure Storage as shown in figure 5:

   我们可以使用SSMS从Windows Azure存储还原，如图5所示：

   
   
   

   
   

   Figure 5 – Restore from Windows Azure Storage 图5 –从Windows Azure存储还原

   By providing SQL credentials, SQL server will connect to the Windows Azure storage and open the Locate Backup File in Windows Azure dialog where we can select our backup file from the displayed files and complete the restore process.

   通过提供SQL凭据，SQL Server将连接到Windows Azure存储并在Windows Azure中打开“查找备份文件” 对话框，我们可以从显示的文件中选择备份文件并完成还原过程。

   Before we start using this new feature we should know some limitations first:

   在开始使用此新功能之前，我们应该首先了解一些限制：

   • Backup to premium storage not supported
     不支持备份到高级存储
   • The maximum size of the backup file must be less than 1 TB
     备份文件的最大大小必须小于1 TB
   • We cannot append to an existing backup file
     我们无法附加到现有的备份文件
   • We cannot backup to multiple files at the same operation
     我们无法在同一操作中备份到多个文件
   • We cannot specify a block size with backup
     我们无法通过备份指定块大小
   • We cannot specify backup options(RETAINDAYS and EXPIREDATE)
     我们无法指定备份选项（RETAINDAYS和EXPIREDATE）

翻译自: https://www.sqlshack.com/hybrid-cloud-and-hekaton-features-in-sql-server-2014/