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 Resource Access Management (RAM) console.

2. Create a RAM role, grant the RAM role permissions to create application-consistent snapshots, and then attach the RAM role to the ECS instance. For more information, see the Create an instance RAM role and attach the instance RAM role to an ECS instance section in the "Grant ECS access to resources of other Alibaba Cloud services by using instance RAM roles" topic. Take note of the following parameters:

   • RAM Role Name: Example: AppSnapshotRoleName.

   • Selected Trusted Entity: The system sets this parameter to Alibaba Cloud Service.

   • Custom policy: Example: AppSnapshotPolicy is used. The following sample code provides an example of the policy content:

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

     This policy grants the permissions to query snapshot information, create snapshots, specify tags, and query 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 Instance 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 resides.

2. Find the instance that you want to manage and choose > Disk and Image > Create Snapshot-consistent Group in the Actions column.

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

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

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

4. Click OK.

   After the snapshot-consistent group is created, a message containing the Cloud Assistant command ID and the execution ID is displayed, as shown in the following figure. 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 codes 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. On the Snapshots page, 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., 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.