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

Procedure

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 method overview.

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

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 based on which you want to create a snapshot-consistent group and choose > Disk and Image > Create Snapshot-consistent Group in the Actions column.

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

   1. Select the ESSDs for which you want to create snapshots and configure other snapshot parameters.

   2. Select Enable Application-consistent Snapshot and Whether Writer is included by default.

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

   

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.

      

      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.

   • Automatically recognize and load logical volumes.

   • Automatically add the corresponding SQL Server database.

   • The time when snapshots were frozen is 2021-08-05 16:42:59.

3. On the Snapshots page, view the details of the created snapshot-consistent group and disk snapshots.

   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 group ID to view the details of the snapshot-consistent group.

   3. In the Cloud disk snapshot section, click the ID of a disk 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.

      

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.

      

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.

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

      

      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.