SQL Server Always On Availability Groups feature provides high availability (HA) and disaster recovery solutions. We can add multiple databases in a single Always on Availability Group on SQL Server Enterprise edition. Before we add a database in the AG group, we need to use the following checklist.

SQL Server Always On可用性组功能提供了高可用性（HA）和灾难恢复解决方案。 我们可以将多个数据库添加到SQL Server Enterprise版的“始终可用”组中。 在将数据库添加到AG组之前，我们需要使用以下清单。

在SQL Server Always On可用性组中添加数据库的清单 (Checklist to add a database in SQL Server Always On Availability Group)

The following checklist is applicable to add a database on Always On Availability Groups.

以下清单适用于在Always On可用性组上添加数据库。

• We can add only User databases in an AG group
  我们只能在AG组中添加用户数据库

• The User database should be in a read-write mode. You can verify the database status using the following query

  用户数据库应处于读写模式。 您可以使用以下查询来验证数据库状态

  Select  Case is_read_only    When 1 then 'DB is in Read-Only Mode'    When 0 then 'DB is in Read-Write Mode'      End as Database_Status  From sys.databases   Where name='AdventureWorks2014'

• The database recovery model should be FULL. We can check the recovery model using the following query

  数据库恢复模型应为FULL。 我们可以使用以下查询检查恢复模型

  Select recovery_model_desc From sys.databasesWhere  name='AdventureWorks2014'

• Initiate a full recovery log chain using the full database backup. If we do not have an existing database backup, take a manual full database backup before adding a database into the AG group
  使用完整的数据库备份启动完整的恢复日志链。 如果我们没有现有的数据库备份，请在将数据库添加到AG组之前进行手动的完整数据库备份
• We cannot add a database into multiple availability groups
  我们无法将数据库添加到多个可用性组中

• The database must not set to Auto Close. We can verify it using the following query

  数据库不得设置为“自动关闭”。 我们可以使用以下查询进行验证

  Select  Case is_auto_close_on    When 1 then 'DB is in Auto Close Mode'    When 0 then 'DB is not in Auto Close Mode'      End as Database_Status  From sys.databases   Where name='AdventureWorks2014'

We should have a similar location for both the data and log files on the primary and all applicable secondary replicas.

对于主副本和所有适用的辅助副本，数据和日志文件都应具有相似的位置。

Recently I come across an issue of low disk space on disk holding data file for a database in Always On Availability Groups. We can use the following approach to resolve the incidents for low disk space.

最近，我在Always On可用性组中遇到了数据库的磁盘保存数据文件磁盘空间不足的问题。 我们可以使用以下方法解决磁盘空间不足的事件。

1. Expand the data file disk size. It is the simplest method to resolve this issue. You can simply ask the storage or VM administrator to expand the disk to have sufficient free space
   扩展数据文件磁盘大小。 这是解决此问题的最简单方法。 您可以简单地要求存储或VM管理员扩展磁盘以具有足够的可用空间
2. If you have multiple data files for SQL database in SQL Server Always On Availability Group, you can plan to move some of the data files into another drive having sufficient free disk space
   如果您在“ SQL Server Always On可用性”组中有多个用于SQL数据库的数据文件，则可以计划将某些数据文件移动到具有足够可用磁盘空间的另一个驱动器中

The method to move data files on a database in Always On Availability Groups is different from the standalone database. Let’s explore the methods to move the data files in an Always On Availability Group.

在Always On可用性组中的数据库上移动数据文件的方法与独立数据库不同。 让我们探讨将数据文件移动到Always On可用性组中的方法。

本文的环境详细信息 (Environment Details for this article)

• TestDB database in Synchronous data commit mode with automatic failover and readable secondary replica
  具有自动故障转移和可读辅助副本的同步数据提交模式下的TestDB数据库

• Primary Replica data file locations

  主副本数据文件位置

  

  Secondary Replica data file locations

  辅助副本数据文件位置

  

• SELECT sadc.database_name,        ag.name AS ag_name,        dhrs.is_local,        dhrs.is_primary_replica,        dhrs.synchronization_state_desc,        dhrs.is_commit_participant,        dhrs.synchronization_health_descFROM sys.dm_hadr_database_replica_states AS dhrs     INNER JOIN sys.availability_databases_cluster AS sadc ON dhrs.group_id = sadc.group_id AND dhrs.group_database_id = sadc.group_database_id     INNER JOIN sys.availability_groups AS ag ON ag.group_id = dhrs.group_id     INNER JOIN sys.availability_replicas AS sar ON dhrs.group_id = sar.group_id  AND dhrs.replica_id = sar.replica_id;

  

Suppose D drive is having space issues, and we want to move the existing data file to another drive (E:\TestDB) having sufficient free space. We can use the following approach to do it.

假设D驱动器存在空间问题，并且我们想将现有数据文件移动到具有足够可用空间的另一个驱动器（E：\ TestDB）。 我们可以使用以下方法来做到这一点。

将数据文件移动到SQL Server Always On可用性组数据库中的另一个驱动器 (Move data file to another drive in SQL Server Always On availability group database)

In this approach, we have a small downtime requirement (we will cover it later with the steps). We configure the full and log backup on the AG database to prepare the database in case of any disaster. Take a log backup before the activity and disable all backups during the activity time. We should not break the backup chain else the restoration will not be possible. It ensures that the backup LSN (log sequence number) chain is intact and we are competent to perform restoration in case of any issues.

在这种方法中，我们对停机时间的要求很小（我们将在后面的步骤中介绍）。 我们在AG数据库上配置完整备份并进行日志备份，以备万一发生灾难时准备数据库。 在活动之前进行日志备份，并在活动时间内禁用所有备份。 我们不应打破备份链，否则将无法恢复。 它可以确保备份LSN（日志序列号）链完整无缺，并且在发生任何问题时我们有能力执行恢复。

Let’s move the data file into another drive using this method.

让我们使用此方法将数据文件移动到另一个驱动器中。

在SQL Server始终在可用性组中的辅助副本上执行的步骤 (Steps to perform on Secondary replica in SQL Server Always on Availability Group)

Step 1: Connect to the secondary replica and go to Always On High Availability. Expand the High Availability. Right-click on the database and suspend the data movement.

步骤1：连接到辅助副本，然后转到Always On High Availability。 扩展高可用性。 右键单击数据库，然后暂停数据移动。

It opens the following Suspend data movement wizard.

它会打开以下“挂起数据移动”向导。

Click on Ok to suspend data movement. Once the data movement is suspended, the database status on Secondary Always on Availability Group to Not Synchronizing. You can also see the health status of Not Healthy.

单击确定以暂停数据移动。 暂停数据移动后，“备用始终存在”组上的数据库状态将变为“ 不同步” 。 您还可以看到不正常的健康状态。

Step 2: In this step, remove the database out from the SQL Server Always On Availability Group from the secondary replica. Expand the High Availability. Right-click on the database and Remove Secondary Database.

步骤2：在此步骤中，从辅助副本SQL Server Always On可用性组中删除数据库。 扩展高可用性。 右键单击数据库，然后删除“辅助数据库” 。

It opens another wizard to remove a database from the AG group. Click on OK.

它打开另一个向导以从AG组中删除数据库。 单击确定 。

Once you remove the database from the secondary replica AG group, it shows a warning sign with the database name in the Availability databases list.

从辅助副本AG组中删除数据库后，它会在可用性数据库列表中显示一个警告标志，并带有数据库名称。

The Secondary AG database state changes to Restoring state.

备用AG数据库状态更改为“正在还原”状态。

We removed the availability group database from SQL Server Always On Availability Group. The database is still available on the primary replica and available for the users. Only data synchronization between primary and secondary replica is not available.

我们从SQL Server Always On可用性组中删除了可用性组数据库。 该数据库在主副本上仍然可用，并且可供用户使用。 仅主副本和辅助副本之间的数据同步不可用。

Step 3: Use the Alter database command on the master database of the secondary replica to move the data file to another drive. You can refer the article for more details on alter database command.

步骤3：在辅助副本的主数据库上使用Alter database命令将数据文件移动到另一个驱动器。 您可以参考文章以获取有关alter database命令的更多详细信息。

ALTER DATABASE TestDB       MODIFY FILE (NAME = TestDB,                     FILENAME = 'E:\TestDB\TestDB.mdf');  GO

Once you execute this command, you get the following message:

一旦执行此命令，您将收到以下消息：

The file “TestDB” has been modified in the system catalog. The new path will be used the next time the database is started.

文件“ TestDB”已在系统目录中修改。 下次启动数据库时将使用新路径。

Step 4: Stop the SQL Service on the secondary replica using the SQL Server Configuration Manager.

步骤4：使用SQL Server配置管理器停止辅助副本上SQL服务。

Step 5: Move the database file to the new location and start the SQL Service. Execute the Sp_helpDB command to verify the data file location

步骤5：将数据库文件移动到新位置并启动SQL Service。 执行Sp_helpDB命令以验证数据文件位置

It still shows the old data file location on the secondary replica

它仍然显示辅助副本上的旧数据文件位置

If we check using the database view sys.database_files, it also returns the wrong data file location

如果我们使用数据库视图sys.database_files进行检查，它还会返回错误的数据文件位置

Use TestDBGoSELECT name,physical_name   FROM sys.database_files

Sp_helpDB executes a stored procedure sp_helpfile, and its scope is set a database level. The secondary database is a replica of the primary in SQL Server Always On. In this article, we changed the data file location for the secondary database AG replica, but the primary database is still pointing to the old location. Secondary replica still gets the old data file path the primary replica. Due to this, we get old or incorrect data file locations using both sp_helpdb and sys.master_files commands for SQL Server Always On Availability Group.

Sp_helpDB执行存储过程sp_helpfile，其作用域设置为数据库级别。 辅助数据库是SQL Server Always On中主数据库的副本。 在本文中，我们更改了辅助数据库AG副本的数据文件位置，但是主数据库仍指向旧位置。 辅助副本仍将旧数据文件路径作为主副本。 因此，对于SQL Server Always On可用性组，使用sp_helpdb和sys.master_files命令会得到旧的或不正确的数据文件位置。

Let’s check the data file location using the system view sys.master_files.

让我们使用系统视图sys.master_files检查数据文件位置。

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatusFROM sys.master_files  WHERE database_id = DB_ID(N'TestDB')  GO

It shows the correct location for the data file in the E drive.

它显示了E驱动器中数据文件的正确位置。

We get the correct results using the sys.master_files because this system view is scoped at the master database level. We have changed the system catalog in the master database on the secondary replica. The system database Master cannot be a part of the availability group. It is maintained separately for each instance. Due to this reason, we get the correct location of the data and log file on the secondary replica as well despite the different location of the data file on the primary replica.

我们使用sys.master_files可以得到正确的结果，因为此系统视图的作用域是master数据库级别。 我们已更改了辅助副本上主数据库中的系统目录。 系统数据库主数据库不能是可用性组的一部分。 每个实例分别对其进行维护。 由于这个原因，尽管数据文件和日志文件在主副本上的位置不同，我们也可以在辅助副本上获得正确的数据和日志文件的位置。

• Note: 注意：

Step 6: On the secondary replica, right-click on the database and click on Join to Availability Group.

步骤6：在辅助副本上，右键单击数据库，然后单击Join to Availability Group。

Once the database joins to an availability group, verify the status is synchronized. It might take a little time to become synchronized depending upon the number of transactions on the primary replica during the activity time.

数据库加入可用性组后，请验证状态是否已同步。 根据活动期间主副本上的事务数量，可能需要花费一些时间来进行同步。

故障转移并在SQL Server Always on可用性组中的新辅助服务器上执行步骤 (Failover and perform the steps on new Secondary Server in SQL Server Always on Availability Group)

Step 7: Perform a manual failover from the primary replica to secondary replica. After the failover, the old secondary replica becomes the primary, and we can verify the data file locations now using the sp_helpdb command.

步骤7：执行从主副本到辅助副本的手动故障转移。 故障转移后，旧的辅助副本将成为主副本，我们现在可以使用sp_helpdb命令验证数据文件的位置。

Step 8: Now, we need to perform the same steps 1 to 7 on the new secondary AG database. After this step, both the primary and secondary replica database is moved to E drive. Monitor the AG dashboard.

步骤8：现在，我们需要在新的辅助AG数据库上执行相同的步骤1至7。 完成此步骤后，主副本数据库和辅助副本数据库都将移至E驱动器。 监视AG仪表板。

Step 9: Enable both the full and log database backups on the AG replica (depending upon the backup configuration and priority on AG replica instances).

步骤9：在AG副本上启用完整数据库备份和日志数据库备份（取决于AG副本实例上的备份配置和优先级）。

结论 (Conclusion)

In this article, we learned to move the data file for SQL Server Always On Availability Group database. Try exploring this on the test environment and become familiar with the steps.

在本文中，我们学习了如何移动SQL Server Always On可用性组数据库的数据文件。 尝试在测试环境中进行探索，并熟悉这些步骤。

翻译自: