横向扩展纵向扩展数据库_扩展数据库–减少扩展的艺术

最新推荐文章于 2022-12-20 20:33:44 发布

culuo4781

最新推荐文章于 2022-12-20 20:33:44 发布

阅读量664

点赞数

文章标签：数据库 python java mysql 大数据

原文链接：https://www.sqlshack.com/stretch-database-the-art-of-extend-to-reduce/

版权

横向扩展纵向扩展数据库

Let’s continue the hybrid saga! After two articles talking about the Azure Blob Storage and what we can do using it, it’s time to check an alternative solution, that does the same of what was presented in the last article (a Hybrid Database used as archival solution for who didn’t read). This time we are not using the Azure Blob Storage, but we are still using Azure!

让我们继续混合传奇！在讨论了有关Azure Blob存储以及我们可以使用它的方法的两篇文章之后，是时候检查另一种解决方案了，该解决方案与上一篇文章中介绍的功能相同（将混合数据库用作没有解决方案的归档解决方案）读）。这次我们不使用Azure Blob存储，但是我们仍在使用Azure！

The feature is…Stretch Database

功能是… 拉伸数据库

Yes, this is a new feature! So new that is only available in the CTP version of SQL Server 2016. However this is available for download, for free of course, and you can start testing its implementation.

是的，这是一项新功能！如此新，仅在SQL Server 2016的CTP版本中可用。但是，此内容可免费下载，当然，您可以开始测试其实现。

此功能有什么作用？ ( What does this feature do? )

Basically the same as we presented in the other article, but with more advantages and an easy way to implement…

与我们在另一篇文章中介绍的基本相同，但是具有更多的优点和易于实现的方法……

In the previous part of this series, we identified a challenge: keep historical data in the database, transparently to the users and applications, without compromise te performance and increase costs.

在本系列的前一部分中，我们确定了一个挑战：将历史数据保留在数据库中，对用户和应用程序透明，而又不影响性能和增加成本。

The solution found was:
Implement table partitioning, pointing the partition with the old data to a file in Azure Blob Storage service.

找到的解决方案是：
实现表分区，将带有旧数据的分区指向Azure Blob存储服务中的文件。

Advantages:
Transparent, improves the performance in the access to current data and eliminates the physical disk used to store the old data.

优点：
透明，可提高访问当前数据的性能，并消除用于存储旧数据的物理磁盘。

Well, that solution works, but there are other points make us thing:

好的，该解决方案有效，但还有其他几点使我们变得如此：

- And we need to maintain this during the database/table lifecycle.
  而且我们需要在数据库/表生命周期内维护它。
By default (there are workarounds), the backup will remain the same, and maybe slower as we are accessing the network to read the historical data now.
默认情况下（有解决方法），备份将保持不变，并且由于我们现在访问网络以读取历史数据而可能会变慢。

Those problems are solved if you start using the Stretch Database! Why? I will explain…

如果您开始使用Stretch Database，这些问题将得到解决！为什么？我会解释…

The “Stretch Database” is a feature/service based on “Azure SQL Database”, so, no Azure Blob Storage here… When you enable this capability in the database a SQL Database will be created under you Azure subscription.

“ Stretch数据库”是基于“ Azure SQL数据库”的一项功能/服务，因此此处没有Azure Blob存储…在数据库中启用此功能后，将在Azure订阅下创建一个SQL数据库。

The first step to start using this, in fact, is enable the feature (of course!), as shown in the next image, right-click in the database, select “Tasks” and “Enable Database for Stretch”:

实际上，开始使用此功能的第一步是启用该功能（当然！），如下图所示，右键单击数据库，选择“任务”，然后选择“启用数据库进行拉伸”：

A Wizard will be opened, where you will need to authenticate to you Azure subscription and define the settings of this feature.

将打开一个向导，您需要在其中向Azure订阅进行身份验证并定义此功能的设置。

There are two possible models:

有两种可能的模型：

Archive Table: Where the entire table will be moved to the cloud.
存档表：将整个表移到云中的位置。
Archive Rows: Where only the selected rows will be moved.
存档行：仅将选定的行移动到的位置。

The second option, is closer of what we talked in the previous article, anyway, you can do the same of “Archive Table” option using Azure Blob Storage… And it is easier than implement the table partitioning.

第二个选项与我们在上一篇文章中讨论的内容更加接近，无论如何，您可以使用Azure Blob存储执行“存档表”选项的操作……而且比实现表分区更容易。

Still talking about the “Archive Rows” option, this is still not available in the current CTP, as it is still under development, but the main idea is select the eligible rows to be moved to Azure, based in some define conditions (like a where clause).

仍在谈论“存档行”选项，该选项在当前CTP中仍不可用，因为它仍在开发中，但是主要思想是根据某些定义条件（例如where子句）。

All the rows will be moved silently using a retry option, to assure that there’s no data loss. This is true for both options… Oh, this feature is also transparent for the applications and users using the database, which is awesome!

所有行将使用重试选项以静默方式移动，以确保不会丢失数据。这对两个选项都是正确的……哦，对于使用数据库的应用程序和用户来说，此功能也是透明的，太棒了！

By using this, you may have something like the showed in the following image, for the “Archive Rows” mode:

通过使用此选项，对于“存档行”模式，您可能会具有下图所示的内容：

You that read the previous article should be thinking “Ok, but what is the advantage of this feature against the other one?”.

阅读上一篇文章的您应该思考“好吧，但是此功能相对于另一功能有什么优势？”。

We can start from the basic:

我们可以从基本开始：

It is easier to implement, and won’t be changing the physical structure of the database (no extra files and filegroups are needed).
它更易于实现，并且不会更改数据库的物理结构（不需要额外的文件和文件组）。
We won’t need to implement a partitioning logic (no partition scheme, function, etc..).
我们不需要实现分区逻辑（无需分区方案，函数等）。
There’s a wizard integrated to SQL Server Management Studio that helps us to reach our objective (wizards are always interesting).
SQL Server Management Studio中集成了一个向导，可帮助我们实现目标（向导总是很有趣）。
And the best! As we are based in a Azure SQL Database, the database size on-premises will be reduced, reducing backups size and time to complete!
最好的！由于我们基于Azure SQL数据库，因此将减少本地数据库的大小，从而减少备份的大小和完成时间！
Because of the same reason, the database maintenance will be improved (comparing time x efficiency).
由于相同的原因，数据库维护将得到改善（比较时间x效率）。
All those differences in the database maintenance are automatic, we won’t need to change nothing…
数据库维护中的所有这些差异都是自动的，我们无需进行任何更改…

要使用“拉伸数据库”功能，我需要具备什么条件？ ( What would I need to have in order to use the Stretch Database feature? )

First of all, the very basic: An Azure account, of course 🙂

首先，非常基本：一个Azure帐户，当然🙂

Having this, we need to enable the “Stretch” in the local instance, using the following code:

有了这个，我们需要使用以下代码在本地实例中启用“ Stretch”：

 
EXEC sp_configure 'remote data archive'
GO_
EXEC sp_configure 'remote data archive', '1'
GO_
RECONFIGURE
GO

After that we are good to run the already referred wizard!

之后，我们很好地运行已经引用的向导！

I found the official documentation an interesting topic showing some cases were this feature fits… So if you identify yourself with one of those phrases, Stretch Database is for you!

我发现官方文档是一个有趣的主题，其中显示了一些适合该功能的情况……因此，如果您用其中一个短语标识自己，那么Stretch Database非常适合您！

Typical use cases for Stretch Database:

Stretch Database的典型用例：

I need to keep data for a long time.
我需要长时间保存数据。
I want to find a way to save money on storage.
我想找到一种省钱的方法。
The size of my tables is getting out of control.
我的桌子的大小已无法控制。
I can’t backup or restore such large tables within the SLA.
我无法在SLA中备份或还原这么大的表。

But be aware that there are some limitations… The following data types are not supported:

但是请注意，存在一些限制……不支持以下数据类型：

filestream
文件流
timestamp
时间戳记
sql_variant
sql_variant
XML
XML格式
geometry
几何
geography
地理
hierarchyid
等级标识
CLR user-defined types (UDTs)
CLR用户定义类型（UDT）

If you have one of the following features, your table won’t be elegible as well:

如果您具有以下功能之一，那么您的表格也将无法清晰显示：

Column Set
列集
Computed Columns
计算列
Check constraints
检查约束
Foreign key constraints that reference the table
引用表的外键约束
Default constraints
默认约束
XML indexes
XML索引
Full text indexes
全文索引
Spatial indexes
空间指标
Clustered columnstore indexes
集群列存储索引
Indexed views that reference the table
引用表的索引视图

To finalize, I’d like to introduce a “new” tool, that is still under CTP as well, but it works well uf you are planning ahead a migration to SQL Server 2016… When I say “new tool”, is just because it was released in a different way, because in fact the tool is quite old 🙂 I’m talking about SQL Server 2016 Upgrade Advisor!

最后，我想介绍一个仍在CTP下的“新”工具，但是如果您正在计划迁移到SQL Server 2016，它会很好地工作……当我说“新工具”时，仅仅是因为它以不同的方式发布，因为实际上该工具已经很老了🙂我正在谈论SQL Server 2016升级顾问！

This new version brought a new interface and “scenarios”, including the Stretch Database. This way, you can analyze your database and find tables that would benefit from the Stretch Database feature. The analysis can be customized, to better fit with your needs.

这个新版本带来了新的界面和“方案”，包括拉伸数据库。这样，您可以分析数据库并查找可以从“拉伸数据库”功能中受益的表。可以自定义分析，以更好地满足您的需求。