对 Stretch Database 进行管理和故障排除Manage and troubleshoot Stretch Database

适用于:是SQL Server(仅从 2016 开始的 Windows)否Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server (Windows only starting with 2016) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

要对 Stretch Database 进行管理和故障排除,请使用本文中所述的工具和方法。To manage and troubleshoot Stretch Database, use the tools and methods described in this article .

管理本地数据Manage local data

获取对 Stretch Database 启用的本地数据库和表的相关信息。Get info about local databases and tables enabled for Stretch Database

打开目录视图 sys.databasessys.tables ,查看有关已启用延伸的 SQL Server 数据库和表的信息。Open the catalog views sys.databases and sys.tables to see info about Stretch-enabled SQL Server databases and tables. 有关详细信息,请参阅 sys.databases (Transact SQL)sys.tables (Transact SQL)For more info, see sys.databases (Transact-SQL) and sys.tables (Transact-SQL).

若要查看 SQL Server 中已启用延伸的表使用的空间量,请运行以下语句。To see how much space a Stretch-enabled table is using in SQL Server, run the following statement.

USE <Stretch-enabled database name>;
GO
EXEC sp_spaceused '<Stretch-enabled table name>', 'true', 'LOCAL_ONLY';
GO

管理数据迁移Manage data migration

检查应用到表的筛选器函数Check the filter function applied to a table

打开目录视图 sys.remote_data_archive_tables ,并检查 filter_predicate 列的值,以标识 Stretch Database 用于选择要迁移的行的函数。Open the catalog view sys.remote_data_archive_tables and check the value of the filter_predicate column to identify the function that Stretch Database is using to select rows to migrate. 如果值为 null,则整个表都可迁移。If the value is null, the entire table is eligible to be migrated. 有关详细信息,请参阅 sys.remote_data_archive_tables (Transact SQL)通过使用筛选器函数选择要迁移的行For more info, see sys.remote_data_archive_tables (Transact-SQL) and Select rows to migrate by using a filter function.

检查数据迁移的状态Check the status of data migration

在 SQL Server Management Studio 中选择数据库的“任务 | 延伸 | 监视”以便在 Stretch Database 监视器中监视数据迁移。Select Tasks | Stretch | Monitor for a database in SQL Server Management Studio to monitor data migration in Stretch Database Monitor. 有关详细信息,请参阅数据迁移的监视与故障排除 (Stretch Database)For more info, see Monitor and troubleshoot data migration (Stretch Database).

或者,打开动态管理视图 sys.dm_db_rda_migration_status 以查看有多少批数据和数据行已迁移。Or, open the dynamic management view sys.dm_db_rda_migration_status to see how many batches and rows of data have been migrated.

数据迁移故障排除Troubleshoot data migration

有关故障排除建议,请参阅 数据迁移的监视与故障排除 (Stretch Database)For troubleshooting suggestions, see Monitor and troubleshoot data migration (Stretch Database).

管理远程数据Manage remote data

获取 Stretch Database 使用的远程数据库和表的相关信息。Get info about remote databases and tables used by Stretch Database

打开目录视图 sys.remote_data_archive_databasessys.remote_data_archive_tables ,以了解有关存储已迁移数据的远程数据库和表的信息。Open the catalog views sys.remote_data_archive_databases and sys.remote_data_archive_tables to see info about the remote databases and tables in which migrated data is stored. 有关详细信息,请参阅 sys.remote_data_archive_databases (Transact SQL)sys.remote_data_archive_tables (Transact SQL)For more info, see sys.remote_data_archive_databases (Transact-SQL) and sys.remote_data_archive_tables (Transact-SQL).

若要查看 Azure 中已启用延伸的表使用的空间量,请运行以下语句。To see how much space a Stretch-enabled table is using in Azure, run the following statement.

USE <Stretch-enabled database name>;
GO
EXEC sp_spaceused '<Stretch-enabled table name>', 'true', 'REMOTE_ONLY';
GO

删除已迁移数据Delete migrated data

若要删除已迁移到 Azure 的数据,请按照 sys.sp_rda_reconcile_batch中所述的步骤进行操作。If you want to delete data that has already been migrated to Azure, follow the steps described in sys.sp_rda_reconcile_batch.

管理表架构Manage table schema

不要更改远程表的架构Don't change the schema of the remote table

不要更改为 Stretch Database 配置的 SQL Server 表相关联的远程 Azure 表的架构。Don't change the schema of a remote Azure table that's associated with a SQL Server table configured for Stretch Database. 特别是,不要修改列的名称或数据类型。In particular, don't modify the name or the data type of a column. Stretch Database 功能对与 SQL Server 表的架构相关的远程表的架构作出各种假设。The Stretch Database feature makes various assumptions about the schema of the remote table in relation to the schema of the SQL Server table. 如果更改了远程架构,Stretch Database 将停止处理已更改的表。If you change the remote schema, Stretch Database stops working for the changed table.

协调时间表列Reconcile table columns

如果意外删除了远程表中的列,运行 sp_rda_reconcile_columns 可将存在于已启用延伸的 SQL Server 表但不存在于远程表中的列添加到远程表。If you have accidentally deleted columns from the remote table, run sp_rda_reconcile_columns to add columns to the remote table that exist in the Stretch-enabled SQL Server table but not in the remote table. 有关详细信息,请参阅 sys.sp_rda_reconcile_columnsFor more info, see sys.sp_rda_reconcile_columns.

重要

sp_rda_reconcile_columns 重新创建你从远程表中意外删除的列时,不会还原之前位于已删除列中的数据。When sp_rda_reconcile_columns recreates columns that you accidentally deleted from the remote table, it does not restore the data that was previously in the deleted columns.

sp_rda_reconcile_columns 不会删除存在于远程表中而不存在于已启用延伸的 SQL Server 表中的列。sp_rda_reconcile_columns does not delete columns from the remote table that exist in the remote table but not in the Stretch-enabled SQL Server table. 如果远程 Azure 表中存在已启用延伸的 SQL Server 表中不复存在的列,这些额外的列不会阻止 Stretch Database 正常运行。If there are columns in the remote Azure table that no longer exist in the Stretch-enabled SQL Server table, these extra columns do not prevent Stretch Database from operating normally. 你可以选择手动删除额外列。You can optionally remove the extra columns manually.

管理性能和成本Manage performance and costs

查询性能故障排除Troubleshoot query performance

包括 Stretch 启用的表的查询应该比在为 Stretch 启用表之前执行得更慢。Queries that include Stretch-enabled tables are expected to perform more slowly than they did before the tables were enabled for Stretch. 如果查询性能显著下降,请查看以下可能的问题。If query performance degrades significantly, review the following possible problems.

  • 你的 Azure 服务器是否处在与你的 SQL Server 不同的地理区域?Is your Azure server in a different geographical region than your SQL Server? 请将你的 Azure 服务器配置为处于与你的 SQL Server 相同的地理区域以减少网络延迟。Configure your Azure server to be in the same geographical region as your SQL Server to reduce network latency.

  • 你的网络条件可能已退化。Your network conditions may have degraded. 有关最新问题或中断的信息,请与你的网络管理员联系。Contact your network administrator for info about recent issues or outages.

提高资源密集型操作(如索引)的 Azure 性能级别Increase Azure performance level for resource-intensive operations such as indexing

在生成、重新生成或重新组织为 Stretch Database 配置的大型表上的索引,并预计在此期间大量查询 Azure 中的已迁移数据时,请考虑提高响应远程 Azure 数据库的性能级别以保证操作的持续时间。When you build, rebuild, or reorganize an index on a large table that's configured for Stretch Database, and you anticipate heavy querying of the migrated data in Azure during this time, consider increasing the performance level of the corresponding remote Azure database for the duration of the operation. 有关性能级别和定价的详细信息,请参阅 SQL Server Stretch Database 定价For more info about performance levels and pricing, see SQL Server Stretch Database Pricing.

不能暂停 Azure 上的 SQL Server Stretch Database 服务You can't pause the SQL Server Stretch Database service on Azure

请确保选择适当的性能和定价级别。Make sure that you select the appropriate performance and pricing level. 如果为需占用大量资源的操作而临时增加性能级别,请在操作完成后将其还原到以前的级别。If you increase the performance level temporarily for a resource-intensive operation, restore it to the previous level after the operation completes. 有关性能级别和定价的详细信息,请参阅 SQL Server Stretch Database 定价For more info about performance levels and pricing, see SQL Server Stretch Database Pricing.

更改查询作用域Change the scope of queries

针对已启用延伸的表的查询默认返回本地和远程数据。Queries against Stretch-enabled tables return both local and remote data by default. 可以更改所有用户所有查询的作用域,或只更改管理员单个查询的作用域。You can change the scope of queries for all queries by all users, or only for a single query by an administrator.

更改所有用户所有查询的查询作用域Change the scope of queries for all queries by all users

若要更改所有用户所有查询的作用域,请运行存储的过程 sys.sp_rda_set_query_modeTo change the scope of all queries by all users, run the stored procedure sys.sp_rda_set_query_mode. 可缩小作用域以便仅查询本地数据、禁用所有查询或还原默认设置。You can reduce the scope to query local data only, disable all queries, or restore the default setting. 有关详细信息,请参阅 sys.sp_rda_set_query_modeFor more info, see sys.sp_rda_set_query_mode.

更改管理员单个查询的查询作用域Change the scope of queries for a single query by an administrator

若要更改 db_owner 角色成员单个查询的作用域,请将 WITH ( REMOTE_DATA_ARCHIVE_OVERRIDE = value ) 查询提示添加到 SELECT 语句。To change the scope of a single query by a member of the db_owner role, add the WITH ( REMOTE_DATA_ARCHIVE_OVERRIDE = value ) query hint to the SELECT statement. REMOTE_DATA_ARCHIVE_OVERRIDE 查询提示可以具有下列值。The REMOTE_DATA_ARCHIVE_OVERRIDE query hint can have the following values.

  • LOCAL_ONLYLOCAL_ONLY. 仅查询本地数据。Query local data only.

  • REMOTE_ONLYREMOTE_ONLY. 仅查询远程数据。Query remote data only.

  • STAGE_ONLYSTAGE_ONLY. 仅查询这样的表中的数据,Stretch Database 在其中暂存符合迁移条件的行并在迁移后指定时段内保留已迁移的行。Query only the data in the table where Stretch Database stages rows eligible for migration and retains migrated rows for the specified period after migration. 此查询提示是查询临时表的唯一方法。This query hint is the only way to query the staging table.

例如,下面的查询将仅返回本地结果。For example, the following query returns local results only.

USE <Stretch-enabled database name>;
GO
SELECT * FROM <Stretch_enabled table name> WITH (REMOTE_DATA_ARCHIVE_OVERRIDE = LOCAL_ONLY) WHERE ... ;
GO

进行管理更新和删除Make administrative updates and deletes

默认情况下,无法更新或删除已启用延伸的表中符合迁移条件的行或已迁移的行。By default you can't UPDATE or DELETE rows that are eligible for migration, or rows that have already been migrated, in a Stretch-enabled table. 如果必须解决此问题,db_owner 角色的成员可在语句中添加 WITH ( REMOTE_DATA_ARCHIVE_OVERRIDE = value ) 查询提示以运行 UPDATE 或 DELETE 操作。When you have to fix a problem, a member of the db_owner role can run an UPDATE or DELETE operation by adding the WITH ( REMOTE_DATA_ARCHIVE_OVERRIDE = value ) query hint to the statement. REMOTE_DATA_ARCHIVE_OVERRIDE 查询提示可以具有下列值。The REMOTE_DATA_ARCHIVE_OVERRIDE query hint can have the following values.

  • LOCAL_ONLYLOCAL_ONLY. 仅更新或删除本地数据。Update or delete local data only.

  • REMOTE_ONLYREMOTE_ONLY. 仅更新或删除远程数据。Update or delete remote data only.

  • STAGE_ONLYSTAGE_ONLY. 仅更新或删除这样的表中的数据,Stretch Database 在其中暂存符合迁移条件的行并在迁移后指定时段内保留已迁移的行。Update or delete only the data in the table where Stretch Database stages rows eligible for migration and retains migrated rows for the specified period after migration.

另请参阅See Also

数据迁移的监视与故障排除 (Stretch Database) Monitor and troubleshoot data migration (Stretch Database)
备份已启用延伸的数据库 (Stretch Database)Backup Stretch-enabled databases (Stretch Database)
还原已启用延伸的数据库 (Stretch Database)Restore Stretch-enabled databases (Stretch Database)