SQL Server拉伸数据库–将“冷”数据移动到云

Tons of new and exciting features have been introduced with the release of the latest SQL Server 2016. It simply is faster, better and more reliable! 🙂 As with the last several releases, there are again a lot of improvements with regards to the integration between on-premises databases and databases sitting into the Cloud. The ultimate goal is to have as convenient and easy as possible migration of the data in both directions. Today’s focus will be one of those features: Stretched Databases. Fasten your seatbelts and discover what is beneath this term!

最新SQL Server 2016版本引入了许多令人兴奋的新功能。它更快，更好，更可靠！ 🙂与最近的几个版本一样，本地数据库和云中的数据库之间的集成也有了很多改进。 最终目标是在两个方向上尽可能方便和轻松地迁移数据。 今天的重点将是这些功能之一：扩展数据库。 系好安全带，发现这个词的含义是什么！

核心概念 (The core concept)

Nowadays, we live in a very dynamic world where data is a valuable asset. Almost every device is generating new and important numbers pretty much all the time. So data is growing at high pace which is a challenge for database owners and administrators. Usually, the intuitive decision would be to get rid of the old data and keep only the most recent data. Which is the most recent data you need? What should be the retention period? What happens if you are a hospital and need medical records that are one year old? What happens if you must comply with specific audit rules? There are numerous questions you need to take into consideration prior to designing your cleanup strategy. The need of keeping our “cold” data (the one that we do not access on a daily basis) is becoming more and more obvious, which is pushing us into creating such solutions.

如今，我们生活在一个充满活力的世界中，数据是宝贵的资产。 几乎每台设备几乎一直都在生成新的重要数字。 因此，数据高速增长，这对数据库所有者和管理员来说是一个挑战。 通常，直观的决定是摆脱旧数据并仅保留最新数据。 您需要哪些最新数据？ 保留期应该是多少？ 如果您是医院并且需要一年的病历怎么办？ 如果您必须遵守特定的审核规则，将会怎样？ 在设计清理策略之前，您需要考虑许多问题。 保持“冷”数据（我们每天不访问的数据）的需求越来越明显，这促使我们创建此类解决方案。

Microsoft’s answer to this is SQL Server 2016 and Stretch Databases. This feature allows us to move our “cold” data to the Cloud over secure channels. This approach has a lot of advantages:

Microsoft对此的答案是SQL Server 2016和Stretch Databases。 此功能使我们可以通过安全通道将“冷”数据移至云。 这种方法有很多优点：

• Cold data can be kept there for many years

  冷数据可以在那里保存很多年

• Cold data is online

  冷数据在线

• Can be accessed from the same application which is using the “hot” data

  可以从使用“热门”数据的同一应用程序访问

• Faster backup/restore as you will have less data on-premises

  更快的备份/还原，因为您的本地数据更少

It is also worth mentioning that Stretch Databases are available in all SQL Server editions and not only in the Premium ones.

还值得一提的是，拉伸数据库在所有SQL Server版本中均可用，而不仅在高级版本中可用。

As we are now aware what the goal of this feature is, let’s play with it and get our hands dirty!

现在我们知道了此功能的目标是什么，让我们一起使用它并弄脏我们的手！

如何执行呢？ (How to implement it?)

The implementation of a Stretch Database is very straightforward and easy. There is an out-of-the-box wizard available to us and embedded into SQL Server Management Studio.

Stretch Database的实现非常简单明了。 我们提供了一个现成的向导，该向导已嵌入到SQL Server Management Studio中。

Let’s go through the process:

让我们来看一下这个过程：

1. First, you need to enable Database Stretch on instance level:

   首先，您需要在实例级别启用数据库拉伸：

    
   EXEC sys.sp_configure N'remote data archive', '1';
   RECONFIGURE;
   GO
    
   

2. Select which database you want to enable for Stretching and start the wizard (I will be using the StackOverflow database for the demonstration):

   选择要为拉伸启用的数据库，然后启动向导（我将使用StackOverflow数据库进行演示）：

   

3. Choose which table/s you want to be enabled for data movement to the Cloud:

   选择要启用数据移动到云的表：

   

4. You can migrate all of the rows from the selected table/s or specify a filter by clicking on the Entire Table link and move only the specified rows:

   您可以迁移所选表中的所有行，或通过单击整个表链接并仅移动指定的行来指定过滤器：

   

   In our example, I will choose the migrate all of the rows into the Cloud and not using filtering.

   在我们的示例中，我将选择将所有行迁移到云中，而不使用过滤。

5. Enter the details for your Azure account. You could have created your Azure SQL in advance and select it or initialize it during the Stretching enabling. We will use an existing server:

   输入您的Azure帐户的详细信息。 您可能已经预先创建了Azure SQL，并在启用Stretching期间选择了它或对其进行了初始化。 我们将使用现有服务器：

   

6. A database Master Key needs to be created in order to facilitate the stretching or if it has already been created, you need to specify the key:

   需要创建数据库主密钥以便于扩展，如果已经创建，则需要指定密钥：

   

7. Next, a Firewall rule must be created to ensure the communication between the on premise SQL and Azure SQL is possible:

   接下来，必须创建防火墙规则以确保本地SQL和Azure SQL之间的通信是可能的：

   

8. After you review the summary, the operation is good to go and you are waiting for the nice, green result 🙂

   查看摘要后，您可以进行该操作了，您正在等待不错的绿色结果🙂

   

   Note there is a log that can be reviewed after the operation (missing part is the name of the host).

   请注意，操作后有一个可以查看的日志（缺少的部分是主机的名称）。

9. After everything has finished successfully, notice the icon next to the Stretched Database:

   一切成功完成后，请注意“扩展数据库”旁边的图标：

   

   Congratulations you are now moving your “cold” data to the Cloud with a few clicks! 🙂

   恭喜，您现在只需单击几下就可以将“冷”数据转移到云中！ 🙂

监视呢？ (What about monitoring?)

The rows that you have specified are now being trickled to Azure under the hood and everything is perfect, but as a Database owner or administrator you need to have a picture of what is really going on, right? We can monitor the data movement in two ways:

您指定的行现在被隐藏到Azure中，并且一切都非常完美，但是作为数据库所有者或管理员，您需要了解实际情况，对吗？ 我们可以通过两种方式监视数据移动：

• Graphically, via Stretch Database Monitoring

  通过拉伸数据库监控以图形方式

• T-SQL, using DMVs

  使用DMV的T-SQL

The Monitoring can be started from the database you are currently stretching:

可以从您当前正在扩展的数据库启动监视：

You can see information about the Azure SQL, the tables being stretched, number of rows you have locally and in Azure and the Migration State, which could be Outbound (moving data to Azure), Inbound (transferring data from Azure back) or Paused:

您可以查看有关Azure SQL，正在拉伸的表，本地以及在Azure中具有的行数以及迁移状态的信息，这些信息可以是出站（将数据移动到Azure），入站（从Azure传输回数据）或已暂停：

Similar information can be received from T-SQL as well, but you need to combine several pieces of code. Number of rows stored locally and in Azure can be checked by running this script:

也可以从T-SQL接收类似的信息，但是您需要组合几段代码。 可以通过运行以下脚本来检查本地和Azure中存储的行数：

 
USE  [StackOverflow]
GO  
EXEC sp_spaceused N'dbo.Users' -- All of the rows in the table
EXEC sp_spaceused N'dbo.Users', @mode = 'REMOTE_ONLY' -- Rows in Azure
EXEC sp_spaceused N'dbo.Users', @mode = 'LOCAL_ONLY'  -- Rows locally
 

If you need details about which tables are being stretched currently, how is the recent data movement going and the direction of the traffic, you can use similar code:

如果您需要有关当前正在扩展哪些表，最近的数据移动如何以及流量方向的详细信息，则可以使用类似的代码：

 
USE  [StackOverflow]
GO  
SELECT * FROM sys.remote_data_archive_tables
SELECT top 10 * FROM sys.dm_db_rda_migration_status ORDER BY start_time_utc DESC
 

从本地和Azure获取数据 (Getting the data locally and from Azure)

In my opinion, the biggest advantage of this feature is that you basically do not care where your rows are currently residing and you can query the local and Azure data from the same application and code. Isn’t this awesome?! There is no difference at all. Let’s get some data from the “dbo.Users” table before it has been configured for Stretching and check the execution plan:

我认为，此功能的最大优点是您基本上不必关心行当前位于何处，并且可以从同一应用程序和代码中查询本地和Azure数据。 这不是很棒吗？ 完全没有区别。 在配置“拉伸”之前，让我们从“ dbo.Users”表中获取一些数据，并检查执行计划：

 
 SELECT TOP 100 * FROM [StackOverflow].[dbo].[Users]
 

There is nothing really interesting here as all of the data is still on premise. Stretching has been enabled and some of the rows are already in Azure. Run the same query and check the execution plan:

这里没有什么真正有趣的，因为所有数据仍在前提中。 拉伸已启用，并且某些行已在Azure中。 运行相同的查询并检查执行计划：

We now have an operator “Remote Query” which is the indication that we have read some data from the Azure prior to receiving the result of our query and that’s it! This is the difference between querying local data and getting rows from Azure. Had not we reviewed the execution plans, we would not have been able to differentiate both cases.

现在，我们有了一个运算符“ Remote Query”，这表明我们在接收查询结果之前已经从Azure读取了一些数据，仅此而已！ 这是查询本地数据和从Azure获取行之间的区别。 如果我们不审查执行计划，就无法区分这两种情况。

禁用数据库拉伸 (Disable Database Stretching)

Disabling Database Stretching is again a fairly easy procedure. You just have to disable the stretching for all tables under the respective database prior to this. When you are doing this, the decision should be made whether the rows would be transferred back to on premise database or you want to keep them in Azure:

再次禁用数据库拉伸是一个相当简单的过程。 在此之前，您只需要禁用相应数据库下所有表的扩展。 在执行此操作时，应确定是将行转移回内部数据库还是要将其保留在Azure中：

In our example, we have chosen to bring the data back from Azure and now the direction of the traffic is Inbound:

在我们的示例中，我们选择将数据从Azure带回，现在流量的方向为“入站”：

As soon as the migration finishes, the T-SQL code used above to monitor the number of rows locally and in Azure no longer succeeds as we do not have data in the Cloud anymore:

迁移完成后，上面用于监视本地行和Azure中的行数的T-SQL代码不再成功，因为我们再也没有云中的数据了：

 
USE  [StackOverflow]
GO  
EXEC sp_spaceused N'dbo.Users' -- All of the rows in the table
EXEC sp_spaceused N'dbo.Users', @mode = 'REMOTE_ONLY' -- Rows in Azure
EXEC sp_spaceused N'dbo.Users', @mode = 'LOCAL_ONLY'  -- Rows locally
 

Stretch Databases are one of the latest, cutting-edge features that have been integrated into the new SQL Server 2016. Straightforward implementation, convenient monitoring and always available “cold” data are characteristics that makes it a preferable solution for institutions that are required to keep old data for a long and even indefinite period of time.

扩展数据库是已集成到新SQL Server 2016中的最新，最先进的功能之一。易于实现的实现，方便的监视和始终可用的“冷”数据是使之成为需要保留的机构的首选解决方案的特征。长时间甚至无限期的旧数据。

Thanks for reading!

谢谢阅读！

翻译自: https://www.sqlshack.com/stretch-databases-moving-cold-data-cloud/