Elastic Compute Service:Best practices for creating application-consistent snapshots for a Windows instance on which SQL Server is deployed

Procedure

In the following example, Microsoft SQL Server 2019 64-bit is installed on the ECS instance that runs the Windows Server 2019 operating system. You create an application-consistent snapshot for data backup. The operations may vary based on the actual environment.

Step 1: Prepare a database verification environment

Prepare a database verification environment that is used to check whether the instance and database can interact as expected.

1. Connect to the Windows instance.

   For more information, see Connect to a Windows instance by using a password or key.

2. 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.

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 RAM role to the ECS instance

Before you enable the application-consistent snapshot feature for the ECS instance, you must configure a Resource Access Management (RAM) role that has permissions to perform operations such as querying snapshot details and creating snapshots.

1. Log on to the RAM console.

2. Create a RAM role for application-consistent snapshots, assign permissions, and attach it to the ECS instance. For more information, see Create a RAM role and attach it to the ECS instance. The process involves:

   • RAM role: Example: AppSnapshotRoleName

   • Selected Trusted Entity: Alibaba Cloud Service

   • Custom policy: Example: AppSnapshotPolicy. The policy content example is as follows:

     {
         "Version": "1",
         "Statement": [
             {
                 "Effect": "Allow",
                 "Action": [
                     "ecs:DescribeSnapshot*",
                     "ecs:CreateSnapshot*",
                     "ecs:TagResources",
                     "ecs:DescribeDisks"
                 ],
                 "Resource": [
                     "*"
                 ],
                 "Condition": {}
             }
         ]
     }

     This policy allows querying snapshot information, creating snapshots, specifying tags, and querying disk information.

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

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

1. Go to the Snapshot-consistent Groups tab on the Snapshots page.

   1. Log on to the ECS console.

   2. In the left-side navigation pane, choose Storage & Snapshots > Snapshots.

   3. In the top navigation bar, select the region where the ECS instance resides.

   4. On the Snapshot-consistent Groups tab, click Create Snapshot-consistent Group.

2. Configure parameters in the Create Snapshot-consistent Group dialog box.

   1. In the Resource Type section, select Instance.

   2. Select an Enterprise SSD (ESSD) for which you want to create an application-consistent snapshot and configure other snapshot parameters.

   3. Select Enable Application-consistent Snapshot and Contain Writers by Default.

3. Click OK.

   After the snapshot-consistent group is created, a message containing the Cloud Assistant command ID and the execution ID is returned. You can check whether the application-consistent snapshot is created based on the execution ID.

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

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

1. On the ECS 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 > Cloud Assistant.

   3. Click the Command Execution Result tab.

   4. Find the task (execution) ID obtained in the previous step and click it to view the execution results.

      

      If the application-consistent snapshot is created, the return value of ExitCode is 0 and the ID of the created application-consistent snapshot 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 occurs. Troubleshoot the error based on the returned error code in the ExitCode column. For more information, see the Error code information section of the "Create application-consistent snapshots" topic.

2. In the command output, view the actions and information involved in the procedure for creating the snapshot-consistent group.

   • Check consistency components. If consistency components do not exist, they are automatically installed.

   • Automatically recognize and load logical volumes.

   • Automatically add the corresponding SQL Server database.

   • The point of time when the system suspended the operation of writing data to the database is 2021-08-05 16:42:59.

3. View the details of the created snapshot-consistent group and snapshot.

   1. In the left-side navigation pane, choose Storage & Snapshots > Snapshots.

   2. Click the Snapshot-consistent Groups tab, find the snapshot-consistent group that you created, and then click the ID of the snapshot-consistent group to view snapshot details.

   3. In the Snapshot Information section, check whether the application-consistent snapshot is created.

      If the application-consistent snapshot is created, the APPConsistent:True tag appears in the Tag column corresponding to the snapshot, as shown in the following figure.

      

4. 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 Connect to a Windows instance by using a password or key.

   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. The query results indicate that no write operations were recorded in the database during the suspension period.

      

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

You can use the application-consistent snapshot to restore data. To check whether data is restored as expected, compare the previous time when data was written to the SQL Server database with the time when the prescript.sh script was run.

1. In the ECS console, use the snapshot-consistent group that you created to roll back data on the ECS instance.

   For more information, see Roll back disks by using a snapshot-consistent group.

2. Log on to SQL Server and query the content of the PointInTime table.

   1. Connect to the Windows instance.

      For more information, see Connect to a Windows instance by using a password or key.

   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 previous time when data was last written to the SQL Server database after data was restored.

      

      The system stopped writing data into the database before the database was suspended. The query results indicate that after you used the application-consistent snapshot to restore data, the previous write time is 2021-08-05 16:42:57.980, which is earlier than the suspension time 2021-08-05 16:42:59.9732143 obtained in Step 4. This indicates that the application-consistent snapshot backed up the SQL Server database as expected.