If you use application-consistent snapshots to roll back disks, data is not corrupted or lost. This prevents logs from being rolled back when a database is started and ensures applications such as SQL Server are started in a consistent state. In this topic, an SQL Server database is used to demonstrate how to create application- consistent snapshots for a Windows instance and verify the data backup effect of the application-consistent snapshots.

Prerequisites

  • The Elastic Compute Service (ECS) instance runs Windows Server 2019, Windows Server 2016, or Windows Server 2012 R2.
  • The disks of the ECS instance are enhanced SSDs (ESSDs) and the file systems are ext3, ext4, XFS, or NTFS. Network 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, ECS instances created from public images after December 1, 2017 are pre-installed with the Cloud Assistant client.
  • You are already familiar with how to install and use an SQL Server database. For more information, see the official SQL Server documentation.

Background information

The following table describes the information of the validation environment used in this topic.
Environment Description
ECS instance type ecs.g7.2xlarge
Operating system Windows Server 2019 Datacenter Edition 64-bit (Chinese)
Disk Enhanced SSD (ESSD)
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 validation environment
  2. Step 2: Configure a RAM role for the ECS instance
  3. Step 3: Create an application-consistent snapshot in the console
  4. Step 4: Check whether the application-consistent snapshot is created
  5. Step 5: Verify the effect of restoring data by using the application-consistent snapshot

Step 1: Prepare a database validation environment

Before you perform the following operations, make sure that Microsoft SQL Server 2019 64-bit is installed. The following section describes how to prepare a database validation environment:

  1. Connect to the ECS instance.
    For more information, see Connection methods.
  2. Connect to the SQL Server database.
    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, set the server connection information and click Connect.
  3. In Microsoft SQL Server Management Studio 18, create the database (AdventureWorks) required for validation.
    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 the table (PointInTime) required for validation.
    1. Click New Query.
    2. In the query window that appears, 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
    3. Right-click Execute.
  5. In the left-side directory tree, view the created database and table.

Step 2: Configure a RAM role for the ECS instance

Before you enable the application-consistent snapshot feature, you must configure a RAM role for the ECS 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.
    Snapshot 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.
    Attach policy
  5. Attach the AppSnapshotRoleName RAM role to the ECS instance. For more information, see Bind an instance RAM role.
    Attach the RAM role to the instance

Step 3: Create an application-consistent snapshot in the console

The following section describes how to create an application-consistent snapshot for a Windows instance where an SQL Server database resides in the 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 Instance Snapshot in the Actions column.
  3. In the Create Instance Snapshot dialog box, configure the parameters for the instance snapshot and enable the instant access feature.
  4. In the Create Instance Snapshot dialog box, select Enable Application-consistent Snapshot and Whether Writer is included by default.
  5. Click OK.
    After the configuration is complete, the Cloud Assistant command ID and the execution ID are displayed. You can check whether the application-consistent snapshot is created based on the execution ID. Execution ID

Step 4: Check whether the application-consistent snapshot is created

The following section describes how to check whether the application-consistent snapshot is created and view the effect of suspended data in the database in the ECS console:

  1. On the Cloud Assistant page, view the command execution result.
    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. Click the ID of the command execution to view the execution result.
      Result

      The preceding figure shows that the returned value of ExitCode is 0, which indicates that the application-consistent snapshot is created by running the Cloud Assistant command. The IDs of the created application-consistent snapshot and the instance snapshot are displayed in the command output.

      Note If the returned value of ExitCode is not 0, troubleshoot the issue based on the error code of ExitCode. For more information, see Error codes.
  2. In the command output, view information about the process of creating the instance snapshot.
    • Check the consistency component. The consistency component is automatically installed if no consistency component exists. Check dependencies
    • Automatically identify and load logical volumes. Logical volumes
    • Automatically add the snapshot to the corresponding SQL Server database. Databases
    • The frozen timestamp of the snapshot is 2021-08-05 16:42:59. Frozen timestamp
  3. On the Snapshots page, view the instance snapshot and disk snapshot.
    1. In the left-side navigation pane, choose Storage & Snapshots > Snapshots.
    2. Click the Instance Snapshots tab to view the instance snapshot.
    3. Click the Snapshots tab to check whether the application-consistent snapshot is created based on the tag information of the snapshot.
      In this example, the APPConsistent:True tag is displayed, which indicates that the application-consistent snapshot is created. Snapshot tag
  4. Connect to the database to view the time when data was suspended for committing.
    1. Connect to the ECS instance.
    2. Connect to the SQL Server database by using Microsoft SQL Server Management Studio 18.
    3. Click New Query.
    4. In the query window that appears, enter the following SQL statement:
      USE AdventureWorks
      select * from PointInTime
      GO
    5. Right-click Execute.
    6. View the query result. You can find that no write records are inserted into the database during the frozen time period.
      View the frozen timestamp

Step 5: Verify the effect of restoring data by using the application-consistent snapshot

After you restore data by using the application-consistent snapshot, check whether the last write time of the MySQL data is before the application pre-freeze script is executed to determine the effect of restoring data by using the application-consistent snapshot.

  1. Use the created instance snapshot to roll back the ECS instance in the ECS console.
  2. Log on to SQL Server and query the content of the PointInTime table.
    1. Connect to the ECS instance.
    2. Connect to the SQL Server database by using Microsoft SQL Server Management Studio 18.
    3. Click New Query.
    4. In the query window that appears, enter the following SQL statement:
      USE AdventureWorks
      select * from PointInTime
      GO
    5. Right-click Execute.
    6. View the query result. In the query result, you can view the time point of the last record after the data is restored.
      Restore

      Before the database is frozen, data cannot be inserted. After you use the application-consistent snapshot to restore data, the timestamp that corresponds to the last data record is 2021-08-05 16:42:57., which is earlier than the 2021-08-05 16:42:59.9732143 frozen timestamp queried in Step 4. Therefore, the result of the application-consistent snapshot about SQL Server is effective.