Disks can be rolled back based on application-consistent snapshots without corruption or loss of data. This ensures that applications such as SQL Server start in a consistent state and prevents log rollbacks on database startup. This topic describes how to create application-consistent snapshots for an Elastic Compute Service (ECS) Windows instance and how to check whether the snapshots can be used as expected to restore data. SQL Server is used in the examples.

Prerequisites

  • An ECS instance that runs Windows Server 2019, Windows Server 2016, or Windows Server 2012 R2 is created.
  • The disks of the ECS instance are enhanced SSDs (ESSDs) and the file systems are ext3, ext4, XFS, or New Technology File System (NTFS). Network file systems or shared file systems are not supported.
  • The Cloud Assistant client is installed on the instance. For more information, see Install the Cloud Assistant client.
    Note By default, the Cloud Assistant client is pre-installed on ECS instances that were created from public images after December 1, 2017.
  • You are already familiar with how to install and use SQL Server. For more information, see the official SQL Server documentation.

Background information

The following table describes the configurations of the verification environment used in this topic.
Configuration Description
Instance type ecs.g7.2xlarge
Operating system Windows Server 2019 Datacenter Edition 64-bit (Chinese)
Disk Enhanced SSDs (ESSDs)
Application Microsoft SQL Server 2019 64-bit
Note For information about how to install and deploy Microsoft SQL Server, see the official documentation.

Procedure

  1. Step 1: Prepare a database verification environment
  2. Step 2: Attach a Resource Access Management (RAM) role to the instance
  3. Step 3: Create application-consistent snapshots in the ECS console
  4. Step 4: Check whether application-consistent snapshots are created
  5. Step 5: Check whether application-consistent snapshots can be used as expected to restore data

Step 1: Prepare a database verification environment

Before you perform the following operations, make sure that Microsoft SQL Server 2019 64-bit is installed on the ECS instance. This section describes how to prepare a database verification environment on the instance.

  1. Connect to the ECS instance.
    For more information, see Connection methods.
  2. Connect to SQL Server.
    1. In the lower-left corner, click the Search icon and enter ssms in the search box.
    2. Click Microsoft SQL Server Management Studio 18.
    3. In the Connect to Server dialog box, configure server connection information and click Connect.
  3. 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.
  4. In Microsoft SQL Server Management Studio 18, create a test table named PointInTime.
    1. Click New Query.
    2. 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
    3. Right-click the query window and select Execute.
  5. In the left-side directory tree, view the created database and table.

Step 2: Attach a Resource Access Management (RAM) role to the instance

Before you enable the application-consistent snapshot feature for an ECS instance, you must configure the required RAM role to the instance.

  1. Log on to the RAM console by using your Alibaba Cloud account.
  2. Create a RAM role for the application-consistent snapshot feature. For more information, see Create a RAM role for a trusted Alibaba Cloud service.
    The following figure shows how to create the AppSnapshotRoleName RAM role. RAM role for the application-consistent snapshot feature
  3. Create a permission policy for the application-consistent snapshot feature. For more information, see Create a custom policy.
    Permission policy
    Create the AppSnapshotPolicy policy to grant permissions to query snapshot details, create snapshots, configure tags, and query disk details. You can use the following policy:
    {
        "Version": "1",
        "Statement": [
            {
                "Effect": "Allow",
                "Action": [
                    "ecs:DescribeSnapshot*",
                    "ecs:CreateSnapshot*",
                    "ecs:TagResources",
                    "ecs:DescribeDisks"
                ],
                "Resource": [
                    "*"
                ],
                "Condition": {}
            }
        ]
    }
  4. Attach the AppSnapshotPolicy policy to the AppSnapshotRoleName RAM role. For more information, see Grant permissions to a RAM role.
    Grant permissions
  5. Attach the AppSnapshotRoleName RAM role to the ECS instance. For more information, see Attach an instance RAM role.
    Attach the RAM role to the instance

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

This section describes how to create application-consistent snapshots for a Windows instance on which a SQL Server database resides in the ECS console.

  1. Go to the Instances page in the ECS console.
    1. Log on to the ECS console.
    2. In the left-side navigation pane, choose Instances & Images > Instances.
    3. In the top navigation bar, select the region where the ECS instance is located.
  2. Find the instance for which you want to enable the application-consistent snapshot feature and choose More > Disk and Image > Create Snapshot-consistent Group in the Actions column.
  3. In the Create Snapshot-consistent Group dialog box, configure parameters to create a snapshot-consistent group and enable the instant access feature.
  4. In the Create Snapshot-consistent Group dialog box, select Enable Application-consistent Snapshot and Whether Writer is included by default.
    Create Snapshot-consistent Group
  5. Click OK.
    After the snapshot-consistent group is created, a message is displayed and indicates the Cloud Assistant command ID and the execution ID, as shown in the following figure. You can check whether application-consistent snapshots are created based on the execution ID. Execution ID

Step 4: Check whether application-consistent snapshots are created

This section describes how to check whether application-consistent snapshots are created and how to check whether insert operations are suspended for the SQL Server database.

  1. On the Cloud Assistant page, view the command execution results.
    1. Log on to the ECS console.
    2. In the left-side navigation pane, choose Maintenance & Monitoring > ECS Cloud Assistant.
    3. Click the Command Execution Result tab.
    4. Find the execution ID obtained in the previous step and click it to view the execution results.
      Execution results

      If application-consistent snapshots are created, the return value of ExitCode is 0 and the IDs of the created application-consistent snapshots and snapshot-consistent group are displayed in the command output, as shown in the preceding figure.

      Note If the return value of ExitCode is not 0, an error has occurred. Troubleshoot the error based on the returned error code in the ExitCode column. For more information, see Error codes.
  2. In the command output, view the actions and information involved in the procedure to create the snapshot-consistent group.
    • Check consistency components. If consistency components do not exist, they are automatically installed. Check dependencies
    • Automatically recognize and load logical volumes. Logical volumes
    • Automatically add the corresponding SQL Server database. Database
    • The time when snapshots were frozen is 2021-08-05 16:42:59. Freezing time
  3. On the Snapshots page, view the created snapshot-consistent group and disk snapshots.
    1. In the left-side navigation pane, choose Storage & Snapshots > Snapshots.
    2. Click the Snapshot-consistent Group tab, find the snapshot-consistent group that you created, and then click the group ID to view the details of the snapshot-consistent group.
    3. In the Cloud disk snapshot section, click the ID of a snapshot.
    4. On the Snapshots tab, check based on tags whether application-consistent snapshots are created for the disks on the ECS instance.
      If application-consistent snapshots are created, the APPConsistent:True tag is displayed in the Tag column corresponding to the snapshots, as shown in the following figure. Snapshot tag
  4. Connect to the SQL Server database to view the time when insert operations were suspended.
    1. Connect to the ECS instance.
    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. View the query results. In the query results, you can find that no write records were inserted into the database during the freezing time window.
      View the freezing time

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

Use application-consistent snapshots to restore data on the ECS instance. Then, check whether the last write time of SQL Server data precedes the time when the application pre-freeze script was executed. If yes, the SQL Server data was properly backed up in the application-consistent snapshots and is restored as expected on the ECS instance.

  1. In the ECS console, use the snapshot-consistent group that you created to roll back data on the ECS instance.
  2. Log on to SQL Server and query the content of the PointInTime table.
    1. Connect to the ECS instance.
    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. View the query results. In the query results, you can find the last write time to the SQL Server database after data is restored.
      Restoration

      Insert operations have been stopped before the database was frozen. In this example, after you use the application-consistent snapshots in the snapshot-consistent group that you created to restore data, the last write time to the SQL Server database is 2021-08-05 16:42:57. This time precedes the 2021-08-05 16:42:59.9732143 freezing time obtained in Step 4. Application-consistent snapshots have properly backed up SQL Server data.