Create application-consistent snapshots for a Windows instance on which SQL Server is deployed - Elastic Compute Service

When you create application-consistent snapshots, the system suspends the data being written to the applications to ensure the integrity and consistency of the data captured by the snapshots. Using an application-consistent snapshot to restore data helps mitigate the risk of data corruption and loss. This ensures that applications, such as SQL Server, can start as expected, and the data remains in the state when the snapshot was created. This topic describes how to check whether application-consistent snapshots can be used as expected to restore data on an Elastic Compute Service (ECS) Windows instance. In the following example, Windows Server 2019 and Microsoft SQL Server 2019 (64-bit) are used.

Prerequisites

The disks that are attached to the ECS instance are Enterprise SSDs (ESSDs) for which multi-attach is disabled.
The ECS instance is in the Running state. Cloud Assistant is in the Normal state on the instance. For information about how to view the status of Cloud Assistant, see View the status of Cloud Assistant and handle anomalies.
Microsoft SQL Server is installed on the ECS instance.
A Resource Access Management (RAM) role is attached to the ECS instance and a custom policy that contains the permissions on application-consistent snapshots is attached to the RAM role. For more information, see Create an instance RAM role and attach the RAM role to an ECS instance.
Note
When you create application-consistent snapshots, you must invoke Cloud Assistant to access the ECS instance and run commands. Before you invoke Cloud Assistant, grant Cloud Assistant the required permissions by using the RAM role.
- Configure a custom RAM role, such as AppSnapshotRoleName.
- Attach a custom policy to the RAM role. The following sample code shows a custom policy, which indicates that the RAM role has the permissions to query snapshots, create snapshots, add tags, and query disk information.
```
{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ecs:DescribeSnapshot*",
                "ecs:CreateSnapshot*",
                "ecs:TagResources",
                "ecs:DescribeDisks"
            ],
            "Resource": [
                "*"
            ],
            "Condition": {}
        }
    ]
}
```

Workflow

This practice verifies how to create an application-consistent snapshot to ensure that the database remains in the state when the snapshot was created, which ensures application consistency. The following figure shows the workflow.

Step 1: Prepare a database verification environment
Create database tables and trigger the storage procedure to simulate data write operations for later verification of application-consistent snapshots.
Step 2: Create application-consistent snapshots in the ECS console
Create application-consistent snapshots for a Windows instance on which an SQL Server database resides in the ECS console. When you create application-consistent snapshots, write operations on the database are temporarily stopped to ensure data consistency.
Step 3: Check whether application-consistent snapshots are created
View the Cloud Assistant command execution result to check whether the application-consistent snapshots are created, and view the points in time at which write operations are suspended and resumed on the database.
Step 4: Check whether the application-consistent snapshots can be used to restore data as expected
Use the application-consistent snapshots to restore data. To check whether data is restored as expected, compare the last time when data was written to the SQL Server database with the time when write operations were suspended on the database.
- If the last write time of SQL Server data is earlier than the database suspension time, no write operations are performed on the database during the suspension period. The last write time of SQL Server data after data restoration reflects the last state before write operations are suspended. The database remains in the state when the snapshot was created.
- If the last write time of the SQL Server data is later than or equal to the time when the database is suspended, write operations are still performed on the database during the suspension period, and the effect of application-consistent snapshots is not as expected.

Procedure

Step 1: Prepare a database verification environment

Connect to the Windows instance.
For more information, see Use Workbench to connect to a Windows instance over RDP.
Connect to SQL Server.
1. In the lower-left corner, click the Search icon, enter ssms in the search box, and then press the Enter key.
2. Click Microsoft SQL Server Management Studio 18.
3. In the Connect to Server dialog box, configure the server connection information and click Connect.
In Microsoft SQL Server Management Studio 18, create a test database named AdventureWorks.
1. Click New Query.
2. In the query window that appears, enter the following SQL statement:
```
CREATE DATABASE AdventureWorks;
```
3. Right-click the query window and select Execute.

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

Click New Query.

In the query window that appears, enter the following SQL statements:

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

Right-click the query window and select Execute.

In the left-side directory tree, view the created database and table.

Step 2: Create application-consistent snapshots in the ECS console

Go to ECS console - Snapshot-consistent Groups.

In the top navigation bar, select the region and resource group of the resource that you want to manage.
On the Snapshot-consistent Groups tab, click Create Snapshot-consistent Group.
In the Create Snapshot dialog box, configure the parameters described in the following table.
1. By default, the Resource Type parameter is set to Instance.
2. Select a Windows instance from the Select Instances drop-down list. Then, in the Select Cloud Disks section, select the ESSDs for which you want to create snapshots from the instance.
3. Expand Advanced Settings, select Enable Application-consistent Snapshot, then select Contain Writers by Default.
  Note
  Writers are a key component in the Volume Shadow Copy Service (VSS) architecture. The Windows operating system provides Writers to ensure data consistency during backup. For more information, see Volume Shadow Copy Service.
Click OK.
After the snapshot-consistent group is created, a message that contains the Cloud Assistant command ID and the task ID is displayed. You can check whether application-consistent snapshots are created based on the task ID.

Step 3: Check whether application-consistent snapshots are created

Click the task ID on the Command Execution Result tab of the ECS Cloud Assistant page to view the command execution results.
If application-consistent snapshots are created, the return value of ExitCode is 0. The command output indicates that application-consistent snapshots were created and contains the ID of the snapshot-consistent group, as shown in the preceding figure.
Note
If the return value of ExitCode is not 0, an error occurred. Troubleshoot the error based on the returned error code in the ExitCode column. For more information, see the Error codes section of the "Create application-consistent snapshots" topic.
In the command output, view information about the snapshot-consistent group creation process to ensure that the created snapshots meet the data integrity and availability requirements.
- Check whether the required consistency components, such as COM+ and VSS, are installed.
- Ensure that the system correctly identifies and loads all logical volumes that need to be backed up.
- Check whether the corresponding database in SQL Server is automatically added to ensure the integrity and consistency of the database.
- The following command output indicates that a snapshot was created at 2025-03-03 13:53:30. The snapshot data was captured at this point in time and the write operations of the database were suspended.
View the created snapshot-consistent group and disk snapshots.
1. On the Snapshot-consistent Groups tab, find the snapshot-consistent group that you created and click the ID of the snapshot-consistent group to view snapshot details.
2. In the Snapshot Information section, check whether application-consistent snapshots are created based on the tags.
  If an application-consistent snapshot is created, the APPConsistent:True tag appears in the Tag column corresponding to the snapshot, as shown in the following figure.
Connect to the SQL Server database and view the point of time when write operations were suspended.
1. Connect to the Windows instance.
  For more information, see Use Workbench to connect to a Windows instance over RDP.
2. Use Microsoft SQL Server Management Studio 18 to connect to the SQL Server database.
3. Click New Query.
4. In the query window that appears, enter the following SQL statements:
```
USE AdventureWorks
select * from PointInTime
GO
```
5. Right-click the query window and select Execute.
6. In the query result, view the period during which write operations on the database were suspended.
  The query result shows that no data was added during the period from 2025-03-03 13:53:30 to 2025-03-03 13:53:33, which indicates that the write operations on the database were suspended at 13:53:30 and resumed at 13:53:33.

Step 4: Check whether the application-consistent snapshots can be used to restore data as expected

Roll back disks by using a snapshot-consistent group
For more information, see Roll back disks by using a snapshot-consistent group.
Log on to SQL Server and query the content of the PointInTime table.
1. Connect to the Windows instance.
  For more information, see Use Workbench to connect to a Windows instance over RDP.
2. Use Microsoft SQL Server Management Studio 18 to connect to the SQL Server database.
3. Click New Query.
4. In the query window that appears, enter the following SQL statements:
```
USE AdventureWorks
select * from PointInTime
GO
```
5. Right-click the query window and select Execute.
6. In the query results, find the point in time when data was last written to the SQL Server database after data was restored.
  The query results show that the point in time when the last data record was added to the database is 2025-03-03 13:53:29, which is earlier than the point in time 2025-03-03 13:53:30 of the suspension queried in Step 3. This verifies that the application-consistent snapshots can back up data for SQL Server as expected.