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
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
- Step 1: Prepare a database verification environment
- Step 2: Attach a Resource Access Management (RAM) role to the instance
- Step 3: Create application-consistent snapshots in the ECS console
- Step 4: Check whether application-consistent snapshots are created
- 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.
- Connect to the ECS instance. For more information, see Connection methods .
- Connect to SQL Server.
- In the lower-left corner, click the Search icon and enter
ssms
in the search box. - Click Microsoft SQL Server Management Studio 18.
- In the Connect to Server dialog box, configure server connection information and click Connect.
- In the lower-left corner, click the Search icon and enter
- In Microsoft SQL Server Management Studio 18, create a test database named AdventureWorks.
- In Microsoft SQL Server Management Studio 18, create a test table named PointInTime.
- 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.
- Log on to the RAM console by using your Alibaba Cloud account.
- 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.
- Create a permission policy for the application-consistent snapshot feature. For more information, see Create a custom 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": {} } ] }
- Attach the AppSnapshotPolicy policy to the AppSnapshotRoleName RAM role. For more information, see Grant permissions to a RAM role.
- Attach the AppSnapshotRoleName RAM role to the ECS instance. For more information, see Attach an instance RAM role to an ECS 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.
- Go to the Instances page in the ECS console.
- Log on to the ECS console.
- In the left-side navigation pane, choose .
- In the top navigation bar, select the region where the ECS instance is located.
- Find the instance for which you want to enable the application-consistent snapshot feature and choose in the Actions column.
- In the Create Snapshot-consistent Group dialog box, configure parameters to create a snapshot-consistent group and enable the instant access feature.
- For more information about the parameters, see Create a snapshot-consistent group.
- For more information about the instant access feature, see Enable or disable the instant access feature.
- In the Create Snapshot-consistent Group dialog box, select Enable Application-consistent Snapshot and Whether Writer is included by default.
- 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.
- On the Cloud Assistant page, view the command execution results.
- 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
.
- On the Snapshots page, view the created snapshot-consistent group and disk snapshots.
- Connect to the SQL Server database to view the time when insert operations were suspended.
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.
- 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.
- Log on to SQL Server and query the content of the PointInTime table.