数据库dba_DBA延伸数据库指南

数据库dba

One of the new features in SQL Server 2016 – and there is a lot – is the ability to stretch the on premise databases to an Azure environment.

SQL Server 2016的新功能之一（还有很多功能）是能够将内部数据库扩展到Azure环境。

This blogpost will cover some of the aspects of this – including:

该博客文章将涵盖其中的某些方面–包括：

• Primarily setup – how to get started
  初步设置-如何开始
• Monitoring state of databases that are in ‘stretch mode’
  监视处于“拉伸模式”的数据库的状态
• Daily work with stretch databases
  拉伸数据库的日常工作
• Backup – what’s good to know
  备份–有什么好消息

With the release of SQL Server 2016, the new feature called stretch database is also released. The feature lets you as a database administrator, make databases stretch (read: copy old data) to an Azure environment. The data is still able to respond to the normal queries that are used, in other way; there is no need to change the current setup for existing applications and other data-contracts to use this feature.

随着SQL Server 2016的发布，还发布了称为拉伸数据库的新功能。 使用此功能，您可以作为数据库管理员，使数据库拉伸（读取：复制旧数据）到Azure环境。 数据仍然能够以其他方式响应所使用的常规查询； 无需更改现有应用程序和其他数据合同的当前设置即可使用此功能。

那么什么时候应该考虑拉伸数据库 (So when is the stretch database something you should consider)

• When you only sometimes need to query the historical data
  当您仅有时需要查询历史数据时
• The transactional data that are stored needs all historical data
  存储的事务数据需要所有历史数据
• The size of the database tables are growing out of control (but not an issue of bad design – then you need to take other actions…)
  数据库表的大小已超出控制范围（但不是设计不良的问题，那么您需要采取其他措施……）
• The backup times are too long in order to make the daily timeslots for maintenance
  备份时间太长，无法进行日常维护工作

If you have one or more marks on the above list, then you have a database that are candidate for stretching into Azure.

如果您在上面的列表中有一个或多个标记，则您有一个适合扩展到Azure的数据库。

A typical database in stretch mode are a transactional database with very large amounts of data (more than a billion rows) stored in a small number of tables.

拉伸模式下的典型数据库是具有少量表中存储的大量数据（超过十亿行）的事务型数据库。

The feature is applied to individual tables in the database – but a need for enabling the feature on database level is a prerequisite.

该功能适用​​于数据库中的各个表，但是前提条件是必须在数据库级别启用该功能。

局限性 (The limitations)

No free goodies without limitations.

没有限制的免费赠品。

There are a list of limitations to a stretch database. Two types of limitations, datatypes and features.

拉伸数据库有一些限制。 限制的两种类型，数据类型和功能。

The datatypes that are not supported for stretch database is:

拉伸数据库不支持的数据类型是：

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

The features that are not supported:

不支持的功能：

• 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
  引用表的索引视图

Therefore, it is advisable to have an agreement with your developers if you plan to use the stretch feature. It is more likely that they can code without the above lists, but if they already have implemented features, and needs to work around them, then you are not in good standing for a while.

因此，如果您打算使用拉伸功能，建议与开发人员达成协议。 他们很可能没有上面的列表就可以进行编码，但是如果他们已经实现了功能，并且需要解决这些问题，那么您暂时不会处于良好状态。

安全 (Security)

In order to handle and maintain the stretch feature the current user must be a member of the db_owner group and CONTROL DATABASE permissions is needed for enabling stretch on database level.

为了处理和维护拉伸功能，当前用户必须是db_owner组的成员，并且需要CONTROL DATABASE权限才能在数据库级别启用拉伸。

设置-如何开始 (Setup – how to get started)

First, get an Azure account. If you not already have one. Then…

首先，获得一个Azure帐户。 如果您还没有。 然后…

A small change in sp_configure is needed to get the feature ready.

为了使此功能就绪，需要对sp_configure进行一些小的更改。

EXEC sp_configure ‘remote data archive’ , ‘1’;
RECONFIGURE;

EXEC sp_configure'远程数据存档'，'1';
重新配置；

启用数据库 (Enabling the database)

It is a prerequisite to enable the database for stretch in order to enable its tables.

必须启用数据库以进行扩展才能启用其表。

It is pretty straight forward – just right-click the database – choose tasks and select ‘Enable database for stretch’:

这非常简单-只需右键单击数据库-选择任务，然后选择“启用数据库以进行拉伸”：

Then the SQL Server asks you to sign in to your Azure environment.

然后，SQL Server要求您登录到Azure环境。

You need to choose a set of settings for your stretch database in Azure – including:

您需要为Azure中的拉伸数据库选择一组设置-包括：

• Location for the server
  服务器位置
• Credential for the server
  服务器的凭证
• Firewall rules
  防火墙规则

There is a summary page with all info – when complete, just hit ‘Finish’.

有一个包含所有信息的摘要页面-完成后，只需点击“完成”即可。

Note: the current applications and data-contracts are NOT able to access the data in Azure directly. The only way to access this data is through the normal on premise database. This database then makes the call to access the Azure database or not based on the current configuration and state of migration (see below for help in the latter).

注意：当前的应用程序和数据合同无法直接访问Azure中的数据。 访问此数据的唯一方法是通过普通的本地数据库。 然后，该数据库将根据当前配置和迁移状态发出是否访问Azure数据库的调用（请参阅下文中的帮助）。

启用表格伸展 (Enabling tables for stretch)

As easy as the database, so is the tables.

表也​​和数据库一样容易。

Right-click the table that you want to stretch – choose ‘Stretch’ and ‘Enable Stretch’.

右键单击要拉伸的表-选择“拉伸”和“启用拉伸”。

Ass seen on the screenshot you can also here do the following tasks: Disable, Pause and Resume stretch. All 3 hopefully self-explainable.

在屏幕截图上看到的屁股，您还可以在此处执行以下任务：禁用，暂停和恢复拉伸。 希望这三者都能自解释。

在拉伸模式下监视数据库和表的状态 (Monitoring the state of databases and tables in stretch mode)

There is released a list of Dynamic management views (DMVs) and updated to existing catalog views to help with the work of monitoring the state of stretch databases.

已发布动态管理视图（DMV）列表，并已更新为现有目录视图，以帮助监视拉伸数据库的状态。

The DMV sys.dm_db_rda_migration_status shows you the current state, in batches and rows, of the data in the stretched tables. For more information, refer to MSDN: sys.dm_db_rda_migration_status.

DMV sys.dm_db_rda_migration_status以批量和行的形式向您显示拉伸表中数据的当前状态。 有关更多信息，请参考MSDN： sys.dm_db_rda_migration_status 。

The catalog views sys.databases and sys.tables now also contains information about the stretch feature on each part respectively. See more as MSDN: sys.databases and sys.tables.

目录视图sys.databases和sys.tables现在还分别包含有关每个零件上的拉伸功能的信息。 另请参见MSDN： sys.databases和sys.tables 。

To view the remote databases and tables for stretch data use the two new catalog views sys.remote_data_archive_databases and sys.remote_data_archive_tables.

要查看用于拉伸数据的远程数据库和表，请使用两个新的目录视图sys.remote_data_archive_databases和sys.remote_data_archive_tables 。

A big note for the current CTP 2.2 release:
This release only supports the stretch data for entire tables. This meaning that an architectural decision needs to be taken to move historical data to separate tables. I will assume that the final release will contain a query based configuration in order to find and detect the historical data to be moved to the Azure environment.

当前CTP 2.2版本的重要说明：
此版本仅支持整个表的拉伸数据。 这意味着需要做出体系结构决策才能将历史数据移动到单独的表中。 我将假定最终版本将包含基于查询的配置，以便查找和检测要移至Azure环境的历史数据。

备份还原 (Backup and restore)

The backup and restore is the same as before the stretch feature. The same strategy must be taken and also the same precautions for data storage in Azure.

备份和还原与拉伸功能之前的相同。 在Azure中必须采取相同的策略并采取相同的预防措施。

One must keep in mind that the on premise backup only happens with on premise data.

必须记住，本地备份仅与本地数据一起发生。

The restore process adds a step to the checklist when restoring a database with stretch enabled.

还原启用了拉伸的数据库时，还原过程将一个步骤添加到清单中。

Upon the end of restore a connection to the stretched database in Azure must be reestablished with the stored procedure sys.sp_reauthorize_remote_data_archive.

还原结束后，必须使用存储过程sys.sp_reauthorize_remote_data_archive重新建立与Azure中拉伸数据库的连接。

When this SP is executed, the vertical arrow on this illustration is reestablished:

执行此SP时，将重新建立该图上的垂直箭头：

结论 (Conclusion)

The stretch database feature is a very nice and good feature to get with the release of SQL Server 2016. It enables the DBA to handle historical data and storage capacity without having the consult the developers and/or architects of new solutions. Also current applications and solutions can be configured to use this new feature.

扩展数据库功能是SQL Server 2016发行版中很好的一项很好的功能。它使DBA可以处理历史数据和存储容量，而无需咨询新解决方案的开发人员和/或架构师。 还可以将当前的应用程序和解决方案配置为使用此新功能。

This post makes a great place to begin with the stretch feature of SQL Server 2016. Personally, I hope that the final feature has a bit more configuration to handle the historical data.

本文是从SQL Server 2016的拉伸功能开始的好地方。我个人希望最终功能具有更多配置以处理历史数据。

翻译自: https://www.sqlshack.com/the-dbas-guide-to-stretch-database/

数据库dba