sql oltp_内存中的OLTP系列– SQL Server 2014上的数据迁移指南过程

sql oltp

In this article we will review migration from disk-based tables to in-memory optimized tables. This article assumes that you already understand the pros and cons about In-Memory Technology, for more articles about this, please refer here.

在本文中，我们将介绍从基于磁盘的表到内存中优化表的迁移。 本文假设您已经了解内存技术的优缺点，有关更多信息，请参阅此处 。

There are some options available on SQL Server 2014 and SQL Server 2016 that will help you to identity, discover and track the tables and stored procedures that can be good candidates to be ported to memory. Furthermore it’s possible to check if the table meets to all the pre-requisites established. Here is the list of the features that we will take a look into.

SQL Server 2014和SQL Server 2016上有一些可用选项，可以帮助您识别，发现和跟踪表和存储过程，这些表和存储过程可以很好地移植到内存中。 此外，可以检查表是否满足所有已建立的先决条件。 这是我们将要研究的功能列表。

SQL Server 2014 ( SQL Server 2014 )

1. Memory Optimization Advisor (Tables)
   内存优化顾问（表）
2. Native Compilation Advisor (Stored Procedure)
   本机编译顾问（存储过程）
3. Memory Usage By Memory Optimized Objects
   内存优化对象的内存使用情况

SQL Server 2016 ( SQL Server 2016 )

1. Data Collector [Transaction Performance Analysis Overview Report]
   数据收集器[交易绩效分析概述报告]
2. Generate In-Memory OLTP Migration CheckLists on GUI & PowerShell
   在GUI和PowerShell上生成内存中OLTP迁移清单

内存优化顾问 ( Memory Optimization Advisor )

The main objective of this feature is to show if the pre-requisites will be met before attempting to put the table in memory. This can be useful if you already know the client environment and have an idea of the most pertinent and interesting tables that the application needs to use to deliver a fast response to the end-user. To check if the table is applicable go to in-memory select the table under the database that you want and go to the Memory Optimization Advisor

此功能的主要目的是显示在将表放入内存之前是否满足先决条件。 如果您已经了解客户端环境并且对应用程序需要使用的最相关，最有趣的表有所了解，以对最终用户做出快速响应，那么这将非常有用。 要检查该表是否适用，请转到内存中选择所需数据库下的表，然后转到Memory Optimization Advisor

The process will pass for all verifications to know if the table selected is ready to be ported. If you encounter any issue during this process a link will be showed to you, this will give you the right direction for the changes that you might want to make to make the table eligible for the process.

该过程将使所有验证通过，以了解所选表是否准备好移植。 如果您在此过程中遇到任何问题，将显示一个链接，这将为您提供正确指导，使您可以进行更改以使表适合该过程。

In this case we received a warning message about a view that is being referenced by the table that we want to migrate. There is no problem at this stage because it’s just a warning statement but we need to have in mind that if we change the table name that view will no longer be referenced by the correct table which means that the code inside of the view must change to reflect the modification that was made.

在这种情况下，我们收到有关要迁移的表正在引用的视图的警告消息。 在此阶段没有问题，因为它只是一个警告语句，但是我们需要记住，如果更改表名，则正确的表将不再引用该视图，这意味着该视图内部的代码必须更改为反映所做的修改。

On the next step, we will see that SQL Server will recognize that you already have the Memory Optimized filegroup and the path that he needs to have to record the new in-memory table that will be available on the end of the process.

在下一步中，我们将看到SQL Server将识别出您已经具有“内存优化”文件组以及记录新的内存中表所需的路径，该表将在过程结束时可用。

Note that the interface already renamed the actual disk table (ondsk_Atendimentos) and put on the end of his name the suffix _old. The reason for this is to facilitate the migration process by letting the name of the new In-Memory Table be the same as previous, this way the application will not be impacted and the code will not be need to be refactored to maintain compatibility.

注意接口已经更名为实际的磁盘表（ondsk_Atendimento S），并把他的名字后缀_old结束。 这样做的原因是通过使新的内存表的名称与以前的名称相同来简化迁移过程，这样就不会影响应用程序，也不需要重构代码来保持兼容性。

Please remember to mark the checkbox “Also Copy Table Data to the New Optimized Table” as this will migrate all the data that is on the disk table to the in-memory table transparently for you.

请记住，选中“还将表数据也复制到新的优化表 ”复选框，因为这将为您透明地将磁盘表上的所有数据迁移到内存表中。

There is another checkbox that can be marked “Check this box to migrate this table to a memory-optimized table with no data durability.” This checkbox exists because there are 2 types of tables available when we’re going to the memory-optimized objects.

还有一个复选框可以标记为“ 选中此框，以将该表迁移到没有数据持久性的内存优化表。 ”之所以存在此复选框，是因为当我们要进行内存优化的对象时，有两种类型的表可用。

• S小号 chema and DataCHEMA和数据
  • This is the main and most used normally. By selecting the Schema and Data option all the data will reside in memory but will be persisted on disk, this will guarantee the ACID. By default, the interface uses this one, but if you select the checkbox the type of the data will be changed for Schema Only instead.
  • 这是主要的，也是最常用的。 通过选择模式和数据选项，所有的数据将驻留在内存中，但将 在磁盘上的坚持，这将保证ACID。 默认情况下，该接口使用此接口 ， 但是，如果选中此复选框，则数据类型将更改为“仅模式”。
• Schema Only仅架构
  • Basically this options will not persist the data on disk which means that in a restart or undesirable unavailability of the SQL Server process only the metadata will 基本上，此选项不会将数据持久化在磁盘上，这意味着在SQL Server进程重新启动或出现意外的不可用性时，仅元数据会 remain 保留 on the table. 在表中。

More about on this link – In-Memory OLTP Series – Table Creation & Types

有关此链接的更多信息– 内存OLTP系列–表创建和类型

Normally this checkbox is selected when we are intending to work with transient data, for i.e. a one-time migration from your source system to a temporary or stage place. But in normal cases we don’t use the Schema_Only option because working in real time business scenarios is necessary to keep all the transactions saved and persisted on disk all the time.

通常， 当我们打算使用瞬态数据时（例如，从源系统到临时或临时场所的一次迁移），请选中此复选框。 但是在 正常情况下，我们不使用Schema_Only选项， 因为在实时业务 场景中 工作 对于保持所有事务都一直 保存并持久保存在磁盘上 是必要 的。

Now on the Review Primary Key Conversion it’s time to select the type of index that you want to use on the Primary Key column. There are two options available for this.

现在，在“ 查看主键转换”上 ，是时候在“主键”列上选择要使用的索引类型了。 为此有两个选项。

1. NonClustered Hash Index (Hash Index)
   非聚集哈希索引（哈希索引）
2. NonClustered Index (Range Index)
   非聚集索引（范围索引）

The focus of this article is to show the easy steps migration to go to memory-optimized tables, to understand more about the Indexes Types available on the In-Memory please refer to this link – In-Memory OLTP – Three key points to entertain your watchdog – Bucket Count

本文的重点是显示迁移到内存优化表的简单步骤，以了解有关内存中可用索引类型的更多信息，请参考此链接– 内存中OLTP –娱乐您的三个关键点看门狗–桶数

Normally, the most used for Primary Keys is the Hash Index, and the main purpose is because hash values are great for equality searches and on the most of the cases we do Index Seek on the primary key instead of an Index Scan turning the hash search more useful here.

通常，最常用于主键的是Hash Index ，其主要目的是因为哈希值对于相等搜索非常有用，并且在大多数情况下，我们对主键执行Index Seek而不是使用Index Scan来进行哈希搜索在这里更有用。

As a last step we must set the appropriate Bucket Count on the Hash Index. As explained on the previous link, the bucket size needs to be set properly otherwise it can significantly impact the workload performance as well the recovery time process of the database. Normally the BUCKET_COUNT should be between 1 and 2 times the number of distinct values on the index key.

最后，我们必须在哈希索引上设置适当的存储桶数。 如前一个链接所述，需要正确设置存储桶的大小，否则会严重影响工作负载性能以及数据库的恢复时间。 通常，BUCKET_COUNT应该是索引键上不同值的数量的1到2倍。

Detailed information can be found here – Determining the Correct Bucket Count for Hash Indexes

可在此处找到详细信息– 确定哈希索引的正确存储桶数

As final step you will be directed to the summary page called as Verify Migration Actions to visualize and approve the modifications that you made, click in Migrate. Also if you want you be able to generate the scripts of all the process by clicking on Script at the bottom button.

最后一步，您将转到摘要页面，称为“ 验证迁移操作” 要显示并批准您所做的修改，请单击“ 迁移” 。 另外，如果您希望能够通过单击底部按钮上的“脚本”来生成所有过程的脚本。

To double check if the tables was migrated to memory, right click on the table, that in this case is – dbo.ondsk_atendimentos and go to properties.

要仔细检查表是否已迁移到内存，请右键单击表（在本例中为– dbo.ondsk_atendimentos），然后转到属性。

本机编译顾问 ( Native Compilation Advisor )

The Native Compilation Advisor is another Wizard that will guide you through the steps to migrate the Stored Procedure to the new Native Stored Procedure. The process will scan all the Stored Procedure and check if the particular code is able to be transposed to the Native Compiled Stored Procedure.

本机编译顾问是另一个向导，它将引导您完成将存储过程迁移到新的本机存储过程的步骤。 该过程将扫描所有存储过程，并检查特定代码是否能够转换为本机编译存储过程。

By selecting the Native Compilation Advisor on the Programmability folder the process will pass through all the requirements and mandatory steps to check the T-SQL code inside of the SP.

通过选择本机编译顾问 在Programmability文件夹中，该过程将通过所有要求和强制性步骤，以检查SP中的T-SQL代码。

The SP – dbo.proc_Insere_ondsk_Atendimentos has passed on all the verification step process as you can see below.

SP – dbo.proc_Insere_ondsk_Atendimentos已通过所有验证步骤过程，如下所示。

But changing the SP and using the – dbo.proc_InsertDskTB_ShoppingCart we can see that now an error was capture.

但是，更改SP并使用– dbo.proc_InsertDskTB_ShoppingCart，我们可以看到现在捕获了一个错误。

You can find the error by clicking on the Unsupported Transact-SQL Elements to be able to see the error message. Now you can open the procedure and fix the code and run again the process.

您可以通过单击不支持的Transact-SQL元素来查找错误，以查看错误消息。 现在，您可以打开该过程并修复代码，然后再次运行该过程。

内存优化对象的内存使用情况 ( Memory Usage By Memory Optimized Objects )

Once you ported the most important tables to memory you can use a new SSMS Standard Report called – Memory Usage By Memory Optimized Objects to visualize the amount of memory used as well information about the tables by itself.

将最重要的表移植到内存后，您可以使用新的SSMS标准报告– 内存优化对象的内存使用情况，以可视化使用的内存量以及有关表本身的信息。

To enter on the report, first go under the database configured to have the memory-optimized tables, right click Reports – Standard Reports.

要输入报告，首先进入配置为具有内存优化表的数据库，然后右键单击“报告”“标准报告”。

At this portion of the report you can visualize the amount of used memory for the memory-optimized objects, off course that underneath this there is a dynamic management view taking place and showing these results.

在报告的此部分，您可以可视化内存优化对象的已用内存量，当然，在此下方有一个动态管理视图。 并显示这些结果。

Have in mind that more tables and SPs in memory you have, the more the recovery time objective (RTO) will be affected because once you Start/Restart SQL Server on the server, the first process that will be triggered will be the memory-optimized objects because they need to be loaded into memory and the first pre-requisite is to have the necessary memory to load these objects.

请记住，内存中的表和SP越多，受影响的恢复时间目标（RTO）越多，因为一旦在服务器上启动/重新启动SQL Server，将首先对内存进行优化，这将触发第一个进程对象，因为它们需要被加载到内存中，并且第一个前提条件是拥有必要的内存来加载这些对象。

Those are the Memory-Optimized Process that will be launched prior on SQL Server. (Detailed information about this process can be found here.)

这些是将在SQL Server之前启动的内存优化过程。 （有关此过程的详细信息，请参见此处 。）

1. Recovery Process Phase
   恢复过程阶段
2. Redo Phase
   重做阶段
3. Undo Phase
   撤消阶段

The last part of the report shows to you the amount of some aspects i.e. Table Used Memory, Table Unused Memory, Index Used Memory, Index Unused Memory.

该报告的最后一部分向您显示了某些方面的数量，即表已用内存，表未用内存，索引已用内存，索引未用内存。

结论 ( Conclusion )

At this stage we dug into all the possibilities inside of SQL Server 2014, in order to facilitate the migration process to the memory-optimized objects. In the next article of this series I will bring up the new enhancements about migration process on SQL Server 2016.

在此阶段，我们将挖掘SQL Server 2014内部的所有可能性，以促进向内存优化对象的迁移过程。 在本系列的下一篇文章中 ，我将介绍有关SQL Server 2016上的迁移过程的新增强功能。

翻译自: https://www.sqlshack.com/in-memory-oltp-series-data-migration-guideline-process-on-sql-server-2014/

sql oltp