Create application-consistent snapshots for SQL Server-Elastic Compute Service(ECS)-阿里云帮助中心

When you create an application-consistent snapshot, the system temporarily freezes data writes to ensure the integrity and consistency of the captured data. Restoring data from an application-consistent snapshot reduces the risk of data corruption and loss. This ensures that applications such as SQL Server can start correctly and that the data is consistent with the state at the time the snapshot was created. This topic demonstrates how to verify that an application-consistent snapshot provides a reliable backup, using a Microsoft SQL Server 2019 (64-bit) database on a Windows Server 2019 instance as an example.

Prerequisites

The Elastic Compute Service (ECS) instance uses enterprise SSDs (ESSDs), and the multi-attach feature is disabled for the disks.
The ECS instance is in the Running state and the Cloud Assistant client is running correctly. To learn how to check the client status, see View the status of the Cloud Assistant client and handle exceptions.
You have a Microsoft SQL Server database installed on the instance. For more information, see Deploy a SQL Server database (Windows).
You have configured a RAM role and a custom policy with the required permissions for application-consistent snapshots and attached the role to the ECS instance. For more information, see Step 1: Create a RAM role.

Note
Because creating an application-consistent snapshot requires Cloud Assistant to access the ECS instance and run commands, you must grant it the necessary permissions through a RAM role.
- RAM role: A custom role, for example, AppSnapshotRoleName.
- Custom policy: The following policy grants permissions to query snapshots and disk information, create snapshots, and add tags to resources during the snapshot creation process.
```
{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ecs:DescribeSnapshot*",
                "ecs:CreateSnapshot*",
                "ecs:TagResources",
                "ecs:DescribeDisks"
            ],
            "Resource": [
                "*"
            ],
            "Condition": {}
        }
    ]
}
```

How it works

This guide demonstrates how to use an application-consistent snapshot to preserve the database state when the snapshot is created, ensuring application consistency. The workflow is as follows:

Step 1: Prepare the database verification environment

Create a database table and a stored procedure to simulate data writes. This allows you to verify the effectiveness of the application-consistent snapshot.
Step 2: Create an application-consistent snapshot in the console

Create an application-consistent snapshot for the Windows instance that hosts the SQL Server database. During this process, database writes are temporarily paused to ensure data consistency.
Step 3: Verify that the application-consistent snapshot was created

Use the Cloud Assistant command results to confirm the snapshot was created and to identify when database writes were paused and resumed.
Step 4: Verify data restoration from the application-consistent snapshot

First, roll back the cloud disk by using the application-consistent snapshot. Then, compare the timestamp of the last data entry in the restored SQL Server database with the time the database was paused. This comparison verifies that the data was restored correctly.
- If the last write time is earlier than the pause time: This indicates that no writes occurred during the pause. The last write time reflects the final state before the pause, which confirms that the database state is consistent with the snapshot.
- If the last write time is the same as or later than the pause time: This indicates that writes continued during the pause, and the application-consistent snapshot did not perform as expected.

Procedure

Step 1: Prepare the database verification environment

Remotely connect to the ECS instance.

For more information, see Log on to a Windows instance by using Workbench.
Connect to the SQL Server database.
1. In the lower-left corner, click the search icon and enter ssms in the search bar.
2. Click Microsoft SQL Server Management Studio 18.
3. In the Connect to Server dialog box, configure the server connection settings and click Connect.
In Microsoft SQL Server Management Studio 18, create a database named AdventureWorks for verification.
1. Click New Query.
2. In the query window, enter the following SQL statement.
```
CREATE DATABASE AdventureWorks;
```
3. Click Run.

In Microsoft SQL Server Management Studio 18, create a table named PointInTime for verification.

Click New Query.

In the query window, enter the following SQL statement.

USE AdventureWorks
GO
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'PointInTime' AND TYPE ='U')
CREATE TABLE PointInTime (PIT datetime)
WHILE ( 1 = 1 )
BEGIN
    INSERT PointInTime SELECT GETDATE()
    WAITFOR DELAY '00:00:001'
END

Click Run.

In the left-side navigation pane, you can view the created database and table.

Step 2: Create an application-consistent snapshot

Go to ECS console - snapshot consistency group.
In the upper-left corner of the page, select a region and resource group.
On the Snapshot-consistent Groups tab, click Create Snapshot-consistent Group.
In the Create Snapshot dialog box, configure the snapshot-consistent group parameters.
1. Resource Types is set to Instance by default.
2. Select an instance and its ESSD-type cloud disks.
3. Expand Advanced Configurations, select Enable Application-consistent Snapshot, and then select Contain Writers by Default.
  
  Note
  A writer is a key component in the VSS architecture. The Windows operating system includes many built-in writers that ensure data consistency during the backup process. For more information, see Volume Shadow Copy Service.
Click OK.

After the snapshot is created, a Cloud Assistant command execution ID is returned. Use this ID to view the result.

Step 3: Verify the application-consistent snapshot

Click the command execution ID to view the result on the Cloud Assistant page.

As shown in the figure, an Exit Code return value of 0 indicates that the Cloud Assistant command to create the application-consistent snapshot succeeded. The output displays the IDs of the created application-consistent snapshot and snapshot-consistent group.

Note
If the Exit Code return value is not 0, troubleshoot the issue based on the Exit Code value. For more information, see Error codes.
In the Cloud Assistant output, review the creation process details to ensure the snapshot meets data integrity and availability requirements.
- Check whether the consistency components (COM+ and VSS) are installed.
- Ensure that the system correctly identifies and loads all logical volumes that need to be backed up.
- Verify that the corresponding SQL Server database is automatically included to ensure database integrity and consistency.
- The snapshot was created at 2025-03-03 13:53:30. At this time, the snapshot data was captured and database writes were paused.
View the snapshot-consistent group and cloud disk snapshot information.
1. Go to ECS console - snapshot consistency group.
2. On the Snapshot-consistent Groups tab, find the group you created and click its ID to view the snapshot details.
3. In the Snapshot Information section, check the snapshot's tags to verify that an application-consistent snapshot was created.
  
  In this example, the cloud disk snapshot has the tag APPConsistent:True, which confirms that it is an application-consistent snapshot.
Connect to the SQL Server database and check the time when database writes were paused.
1. Remotely connect to the ECS instance.
  
  For more information, see Log on to a Windows instance by using Workbench.
2. Connect to the SQL Server database by using Microsoft SQL Server Management Studio 18.
3. Click New Query.
4. In the query window, enter the following SQL statement.
```
USE AdventureWorks
select * from PointInTime
GO
```
5. Click Run.
6. In the query results, find the time when the database was paused.
  
  The results show that no data was inserted between 2025-03-03 13:53:30 and 2025-03-03 13:53:33. This indicates database writes were paused at 13:53:30 and resumed at 13:53:33.

Step 4: Verify data restoration

Roll back the cloud disk using the created snapshot-consistent group.

For more information, see Roll back a cloud disk by using a snapshot-consistent group.
Log on to the SQL Server database again and query the PointInTime table.
1. Remotely connect to the ECS instance.
  
  For more information, see Log on to a Windows instance by using Workbench.
2. Connect to the SQL Server database by using Microsoft SQL Server Management Studio 18.
3. Click New Query.
4. In the query window, enter the following SQL statement.
```
USE AdventureWorks
select * from PointInTime
GO
```
5. Click Run.
6. In the query results, check the timestamp of the last record in the database after restoration.
  
  The last data entry was inserted at 2025-03-03 13:53:29, which is earlier than the pause time of 2025-03-03 13:53:30 noted in Step 3. This result confirms that the application-consistent snapshot for SQL Server backed up the data correctly.