By Yen Sheng Tee, Product Solution Architect of Alibaba Cloud Intelligent
Database is the backbone for most (if not all) the running digitalized systems. All types of data consumed within an application will be hosted in a database. One of the most popular transactional databases is Microsoft SQL Server. This article discusses the options available to safeguard your self-managed Microsoft SQL Server database running on Alibaba Cloud using different backup and restore approaches. Although the steps are specific to SQL Server, the same approach may apply to other databases in the market.
Note: The detailed steps of PaaS or on-premise database backup/restore, although being supported, are not included in this article.
Manual database backup files can be stored within shared cloud storage. This allows us to mount the same storage on different servers for restoration purposes. Storing the backup locally on the EBS disk also works, but you may face difficulties restoring it during instance failure.
The following are some of the shared cloud storage options that can be used. The steps will focus on backing up the SQL Server running on the ECS instance, as the one involving on-premises will require additional components setup (such as VPN gateway or hypervisor configuration).
File Storage NAS can be mounted to different hosts at the same time, making it an ideal shared cloud storage if you are running clustered database or want to have the ability to restore to different servers at the same time compared to local EBS. The same NAS can be mounted to on-premise database servers by routing the traffic via a NAT or VPN Gateway (preferred for security reasons).
Note: The access control and permission group to NAS is not configured in this article. It is highly recommended to configure them to avoid third-party access.
1. Create a NAS file system from the NAS console. Select a region and zone closest to your database for lower latency. Select the storage and protocol type that matches your needs. The SMB protocol is preferred for Windows clients.
2. Mount the NAS to your ECS instance using the console or manually using the provided command:
3. Mount the NAS according to the provided steps:
If you use Windows 2016 or later, run the following command to grant anonymous access to the client.
# REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\LanmanWorkstation\Parameters /f /v AllowInsecureGuestAuth /t REG_DWORD /d 1
# net use <mount-drive>: \\<nas-fileshare-url>
4. Validate the mounting in the ESC instance:
5. Run the following command to back up the SQL Server to the mount point of NAS. You need to use the UNC path.
# BACKUP DATABASE sales TO DISK = '<shared-storage-unc-path>/<backup-file>';
6. Run the following command to restore the database from the same mount point when needed:
# RESTORE DATABASE sales FROM DISK = '<shared-storage-unc-path>/<backup-file>';
Object Storage Service (OSS) is a secure and cost-effective object storage service from Alibaba Cloud. Cloud Storage Gateway (CSG) is a gateway service that can be deployed on Alibaba Cloud or within your data center. It uses OSS buckets as backend storage devices, providing standard file services via NFS or SMB protocols or block storage services over iSCSI protocol. CSG will be set up as a file gateway for this use case.
1. Create an OSS bucket from the OSS console. Select the region closest to your database for lower latency.
2. Create a Gateway Cluster from the CSG console. Select the region closest to your database for lower latency.
3. Create a Gateway within the cluster. Select a preferred edition based on your requirement.
4. Specify the OSS bucket, protocol, and share name. The mount path will be formed using the gateway IP and share name. Specify the preferred cache disk type and capacity. SMB protocol is preferred for Windows clients.
5. Accept the service agreement and create the gateway:
6. Once the gateway is created, create an SMB user on CSG. This user will be used to mount the disk later.
7. Mount the SMB share using CSG with the user defined earlier.
If you use Windows 2016 or later, run the following command to grant anonymous access to the client.
# REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\LanmanWorkstation\Parameters /f /v AllowInsecureGuestAuth /t REG_DWORD /d 1
# net use <mount-drive>: \\<csg-ip-address>\<share-name> /user:<username>\<password>
8. Validate the mounting in the ESC instance:
9. Run the following command to back up the SQL Server to the mount point of CSG:
# BACKUP DATABASE sales TO DISK = '<shared-storage-unc-path>/<backup-file>';
10. Run the following command to restore the database from the same mount point when needed:
# RESTORE DATABASE sales FROM DISK = '<shared-storage-unc-path>/<backup-file>';
This is similar to the approach discussed earlier. However, Rclone is used to mount the storage instead of Alibaba Cloud CSG. Rclone is an open-source tool that manages or migrates content on the cloud and other high-latency storage. It can be used to mount OSS on ECS instances.
Note: Rclone is an open-source tool, and WinFsp is available under the GPLv3 license. They are not supported by Alibaba Cloud officially.
1. Create an OSS bucket from the OSS console. Select a region closest to your database for lower latency.
2. Download and install the latest version of WinFsp from the following link:
https://github.com/winfsp/winfsp/releases?spm=a2c63.p38356.0.0.bdbe5c4cUQVXLA
3. Download Rclode and extract the package into a local directory (such as C:rclone):
https://rclone.org/downloads/?spm=a2c63.p38356.0.0.bdbe5c4cUQVXLA
4. Add the directory of Rclone into Windows' Environment Variables:
5. Perform a quick test in the command prompt to make sure Rclone is set up properly:
6. Create a new RAM policy, granting permission to list, get, and put objects. The following example grants permissions to every OSS bucket. It is recommended to only limit this to the specific OSS bucket.
{
"Version": "1",
"Statement": [
{
"Effect": "Allow",
"Action": [
"oss:PutObject",
"oss:GetObject",
"oss:ListBuckets"
],
"Resource": "*"
}
]
}
7. Create or re-use an existing RAM user created specifically for triggering the backup/restoration. This user will be used by Rclone to access OSS.
8. Create an access key for the newly created RAM user. Take note of the access key ID and secret for the next step:
9. Configure Rclone accordingly through the interactive prompt. A copy of the configuration is included below. Replace the access key ID and secret accordingly:
# rclone configure
C:\Users\Administrator>rclone config
2023/02/14 16:52:52 NOTICE: Config file "C:\\Users\\Administrator\\AppData\\Roaming\\rclone\\rclone.conf" not found - using defaults
No remotes found, make a new one?
n) New remote
s) Set configuration password
q) Quit config
n/s/q> n
Enter name for new remote.
name> oss-mount
Option Storage.
Type of storage to configure.
Choose a number from below, or type in your own value.
1 / 1Fichier
\ (fichier)
2 / Akamai NetStorage
\ (netstorage)
3 / Alias for an existing remote
\ (alias)
4 / Amazon Drive
\ (amazon cloud drive)
5 / Amazon S3 Compliant Storage Providers including AWS, Alibaba, Ceph, China Mobile, Cloudflare, ArvanCloud, DigitalOcean, Dreamhost, Huawei OBS, IBM COS, IDrive e2, IONOS Cloud, Liara, Lyve Cloud, Minio, Netease, RackCorp, Scaleway, SeaweedFS, StackPath, Storj, Tencent COS, Qiniu and Wasabi
\ (s3)
6 / Backblaze B2
\ (b2)
7 / Better checksums for other remotes
\ (hasher)
8 / Box
\ (box)
9 / Cache a remote
\ (cache)
10 / Citrix Sharefile
\ (sharefile)
11 / Combine several remotes into one
\ (combine)
12 / Compress a remote
\ (compress)
13 / Dropbox
\ (dropbox)
14 / Encrypt/Decrypt a remote
\ (crypt)
15 / Enterprise File Fabric
\ (filefabric)
16 / FTP
\ (ftp)
17 / Google Cloud Storage (this is not Google Drive)
\ (google cloud storage)
18 / Google Drive
\ (drive)
19 / Google Photos
\ (google photos)
20 / HTTP
\ (http)
21 / Hadoop distributed file system
\ (hdfs)
22 / HiDrive
\ (hidrive)
23 / In memory object storage system.
\ (memory)
24 / Internet Archive
\ (internetarchive)
25 / Jottacloud
\ (jottacloud)
26 / Koofr, Digi Storage and other Koofr-compatible storage providers
\ (koofr)
27 / Local Disk
\ (local)
28 / Mail.ru Cloud
\ (mailru)
29 / Mega
\ (mega)
30 / Microsoft Azure Blob Storage
\ (azureblob)
31 / Microsoft OneDrive
\ (onedrive)
32 / OpenDrive
\ (opendrive)
33 / OpenStack Swift (Rackspace Cloud Files, Memset Memstore, OVH)
\ (swift)
34 / Oracle Cloud Infrastructure Object Storage
\ (oracleobjectstorage)
35 / Pcloud
\ (pcloud)
36 / Put.io
\ (putio)
37 / QingCloud Object Storage
\ (qingstor)
38 / SMB / CIFS
\ (smb)
39 / SSH/SFTP
\ (sftp)
40 / Sia Decentralized Cloud
\ (sia)
41 / Storj Decentralized Cloud Storage
\ (storj)
42 / Sugarsync
\ (sugarsync)
43 / Transparently chunk/split large files
\ (chunker)
44 / Union merges the contents of several upstream fs
\ (union)
45 / Uptobox
\ (uptobox)
46 / WebDAV
\ (webdav)
47 / Yandex Disk
\ (yandex)
48 / Zoho
\ (zoho)
49 / premiumize.me
\ (premiumizeme)
50 / seafile
\ (seafile)
Storage> 5
Option provider.
Choose your S3 provider.
Choose a number from below, or type in your own value.
Press Enter to leave empty.
1 / Amazon Web Services (AWS) S3
\ (AWS)
2 / Alibaba Cloud Object Storage System (OSS) formerly Aliyun
\ (Alibaba)
3 / Ceph Object Storage
\ (Ceph)
4 / China Mobile Ecloud Elastic Object Storage (EOS)
\ (ChinaMobile)
5 / Cloudflare R2 Storage
\ (Cloudflare)
6 / Arvan Cloud Object Storage (AOS)
\ (ArvanCloud)
7 / DigitalOcean Spaces
\ (DigitalOcean)
8 / Dreamhost DreamObjects
\ (Dreamhost)
9 / Huawei Object Storage Service
\ (HuaweiOBS)
10 / IBM COS S3
\ (IBMCOS)
11 / IDrive e2
\ (IDrive)
12 / IONOS Cloud
\ (IONOS)
13 / Seagate Lyve Cloud
\ (LyveCloud)
14 / Liara Object Storage
\ (Liara)
15 / Minio Object Storage
\ (Minio)
16 / Netease Object Storage (NOS)
\ (Netease)
17 / RackCorp Object Storage
\ (RackCorp)
18 / Scaleway Object Storage
\ (Scaleway)
19 / SeaweedFS S3
\ (SeaweedFS)
20 / StackPath Object Storage
\ (StackPath)
21 / Storj (S3 Compatible Gateway)
\ (Storj)
22 / Tencent Cloud Object Storage (COS)
\ (TencentCOS)
23 / Wasabi Object Storage
\ (Wasabi)
24 / Qiniu Object Storage (Kodo)
\ (Qiniu)
25 / Any other S3 compatible provider
\ (Other)
provider> 2
Option env_auth.
Get AWS credentials from runtime (environment variables or EC2/ECS meta data if no env vars).
Only applies if access_key_id and secret_access_key is blank.
Choose a number from below, or type in your own boolean value (true or false).
Press Enter for the default (false).
1 / Enter AWS credentials in the next step.
\ (false)
2 / Get AWS credentials from the environment (env vars or IAM).
\ (true)
env_auth> 2
Option access_key_id.
AWS Access Key ID.
Leave blank for anonymous access or runtime credentials.
Enter a value. Press Enter to leave empty.
access_key_id> xxxxxxxxxx
Option secret_access_key.
AWS Secret Access Key (password).
Leave blank for anonymous access or runtime credentials.
Enter a value. Press Enter to leave empty.
secret_access_key> xxxxxxxxxx
Option endpoint.
Endpoint for OSS API.
Choose a number from below, or type in your own value.
Press Enter to leave empty.
1 / Global Accelerate
\ (oss-accelerate.aliyuncs.com)
2 / Global Accelerate (outside mainland China)
\ (oss-accelerate-overseas.aliyuncs.com)
3 / East China 1 (Hangzhou)
\ (oss-cn-hangzhou.aliyuncs.com)
4 / East China 2 (Shanghai)
\ (oss-cn-shanghai.aliyuncs.com)
5 / North China 1 (Qingdao)
\ (oss-cn-qingdao.aliyuncs.com)
6 / North China 2 (Beijing)
\ (oss-cn-beijing.aliyuncs.com)
7 / North China 3 (Zhangjiakou)
\ (oss-cn-zhangjiakou.aliyuncs.com)
8 / North China 5 (Hohhot)
\ (oss-cn-huhehaote.aliyuncs.com)
9 / North China 6 (Ulanqab)
\ (oss-cn-wulanchabu.aliyuncs.com)
10 / South China 1 (Shenzhen)
\ (oss-cn-shenzhen.aliyuncs.com)
11 / South China 2 (Heyuan)
\ (oss-cn-heyuan.aliyuncs.com)
12 / South China 3 (Guangzhou)
\ (oss-cn-guangzhou.aliyuncs.com)
13 / West China 1 (Chengdu)
\ (oss-cn-chengdu.aliyuncs.com)
14 / Hong Kong (Hong Kong)
\ (oss-cn-hongkong.aliyuncs.com)
15 / US West 1 (Silicon Valley)
\ (oss-us-west-1.aliyuncs.com)
16 / US East 1 (Virginia)
\ (oss-us-east-1.aliyuncs.com)
17 / Southeast Asia Southeast 1 (Singapore)
\ (oss-ap-southeast-1.aliyuncs.com)
18 / Asia Pacific Southeast 2 (Sydney)
\ (oss-ap-southeast-2.aliyuncs.com)
19 / Southeast Asia Southeast 3 (Kuala Lumpur)
\ (oss-ap-southeast-3.aliyuncs.com)
20 / Asia Pacific Southeast 5 (Jakarta)
\ (oss-ap-southeast-5.aliyuncs.com)
21 / Asia Pacific Northeast 1 (Japan)
\ (oss-ap-northeast-1.aliyuncs.com)
22 / Asia Pacific South 1 (Mumbai)
\ (oss-ap-south-1.aliyuncs.com)
23 / Central Europe 1 (Frankfurt)
\ (oss-eu-central-1.aliyuncs.com)
24 / West Europe (London)
\ (oss-eu-west-1.aliyuncs.com)
25 / Middle East 1 (Dubai)
\ (oss-me-east-1.aliyuncs.com)
endpoint> 19
Option acl.
Canned ACL used when creating buckets and storing or copying objects.
This ACL is used for creating objects and if bucket_acl isn't set, for creating buckets too.
For more info visit https://docs.aws.amazon.com/AmazonS3/latest/dev/acl-overview.html#canned-acl
Note that this ACL is applied when server-side copying objects as S3
doesn't copy the ACL from the source but rather writes a fresh one.
If the acl is an empty string then no X-Amz-Acl: header is added and
the default (private) will be used.
Choose a number from below, or type in your own value.
Press Enter to leave empty.
/ Owner gets FULL_CONTROL.
1 | No one else has access rights (default).
\ (private)
/ Owner gets FULL_CONTROL.
2 | The AllUsers group gets READ access.
\ (public-read)
/ Owner gets FULL_CONTROL.
3 | The AllUsers group gets READ and WRITE access.
| Granting this on a bucket is generally not recommended.
\ (public-read-write)
/ Owner gets FULL_CONTROL.
4 | The AuthenticatedUsers group gets READ access.
\ (authenticated-read)
/ Object owner gets FULL_CONTROL.
5 | Bucket owner gets READ access.
| If you specify this canned ACL when creating a bucket, Amazon S3 ignores it.
\ (bucket-owner-read)
/ Both the object owner and the bucket owner get FULL_CONTROL over the object.
6 | If you specify this canned ACL when creating a bucket, Amazon S3 ignores it.
\ (bucket-owner-full-control)
acl> 1
Option storage_class.
The storage class to use when storing new objects in OSS.
Choose a number from below, or type in your own value.
Press Enter to leave empty.
1 / Default
\ ()
2 / Standard storage class
\ (STANDARD)
3 / Archive storage mode
\ (GLACIER)
4 / Infrequent access storage mode
\ (STANDARD_IA)
storage_class> 1
Edit advanced config?
y) Yes
n) No (default)
y/n> n
Configuration complete.
Options:
- type: s3
- provider: Alibaba
- env_auth: true
- access_key_id: xxxxxxxxxx
- secret_access_key: xxxxxxxxxx
- endpoint: oss-ap-southeast-3.aliyuncs.com
- acl: private
Keep this "oss-mount" remote?
y) Yes this is OK (default)
e) Edit this remote
d) Delete this remote
y/e/d> y
Current remotes:
Name Type
==== ====
oss-mount s3
e) Edit existing remote
n) New remote
d) Delete remote
r) Rename remote
c) Copy remote
s) Set configuration password
q) Quit config
e/n/d/r/c/s/q> q
C:\Users\Administrator>10. Create a temporary cache disk location in your local disk (eg. c:cache) for VFS file caching of Rclone. This can be skipped if you prefer not to use cache.
11. Test the mounting manually. Remove –cache-dir and –vfs-cache-mode if you prefer not to use VFS caching.
# rclone mount <rclone-config-name>:/<bucket-name> <shared-storage-unc-path>: --cache-dir <cache-disk-location> --vfs-cache-mode writes
Note: The shared-storage-unc-path will be used in the backup command later.
12. Validate the mounting in the ESC instance:
13. Run the following command to back up the SQL Server to the mount point of Rclone:
# BACKUP DATABASE sales TO DISK = '<shared-storage-unc-path>/<backup-file>';
14. Run the following command to restore the database from the same mount point when needed:
# RESTORE DATABASE sales FROM DISK = '<shared-storage-unc-path>/<backup-file>';
Microsoft SQL Server 2022 (16.x) introduces object storage integration to the data platform, enabling you to integrate the SQL Server with S3-compatible object storage. This feature is limited to Azure Storage in the older version of the SQL Server. This feature allows you to back up the SQL Server directly into an S3-compatible object storage service (such as OSS). You may strip/stripe the backup into 64 smaller parts to support file sizes up to 12.8 TB.
1. Create a new RAM policy, granting permission to list, get, and put objects. The following example grants permissions to every OSS bucket. It is recommended to only limit this to the specific OSS bucket.
{
"Version": "1",
"Statement": [
{
"Effect": "Allow",
"Action": [
"oss:PutObject",
"oss:GetObject",
"oss:ListBuckets"
],
"Resource": "*"
}
]
}
2. Create or re-use an existing RAM user created specifically for triggering the backup/restoration. This user will be used by the SQL Server to access OSS.
3. Create an access key for the newly created RAM user. Take note of the access key ID and secret for the next step:
4. Log in to the SQL Server client (eg. SSMS) with the sysadmin privileged user.
5. Run the following command to create SQL Server credentials for authentication with the object storage endpoint. Use the RAM user's access key and secret from the earlier step:
# CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>]
WITH
IDENTITY = 'S3 Access Key',
SECRET = '<AccessKeyID>:<SecretKeyID>';
6. Run the following command to create a backup to OSS. This will perform a full database backup to the object storage endpoint, striped across multiple files.
# BACKUP DATABASE <db_name>
TO URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
, URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
, URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
, URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH FORMAT -- overwrite
, STATS = 10
, COMPRESSION;
Note: You can provide up to 64-part files in the backup command. Additional options can be included to enable encryption, compression, or setting the max transfer file size per part file. Please refer to Microsoft's official documentation for more details.
7. Validate the backups in the OSS console:
8. Run the following command to restore the database from OSS when needed:
# RESTORE DATABASE <db_name>
FROM URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
, URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
, URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
, URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH STATS = 10;
Alibaba Cloud Database Backup Service (DBS) is a cost-effective and highly reliable cloud-native database backup platform. DBS provides unlimited backup storage, second-level emergency recovery, and recovery drills. DBS supports nearly ten types of databases (such as MySQL, SQL Server, and Oracle databases). DBS supports full-site backup of files, logs, and databases. DBS also supports various environments where databases are deployed, such as data centers, cloud platforms provided by other cloud service providers, Elastic Compute Service (ECS) instances, and ApsaraDB RDS. DBS is the preferred and unified hybrid cloud backup platform for enterprises.
1. Create Backup Schedules from the DBS console. Select the region closest to your database for lower latency.
2. Once the backup schedule is created, click Configure Backup Schedule from the console:
3. Specify the backup source information from the selection. ECS-Hosted Database was selected (with OSS as the backup destination).
Note: DBS allows backups to the DBS in-built storage or OSS. Learn more about the differences via the following link:
https://www.alibabacloud.com/help/en/database-backup-service/latest/built-in-storage-and-oss
4. Configure backup objects by selecting the database you want to backup:
5. Configure the backup schedule based on the requirement:
6. Configure the retention of the backup lifecycle. Click Precheck to save and trigger the next step:
7. Pre-check will be triggered, and the status will be displayed. Click Start Task to kickstart the backup process:
8. The backup schedule will be started. You can check the status of the configuration from the console.
9. Trigger the database restore from the console when needed. Select the restore point, and specify the destination database.
Alibaba Cloud Hybrid Backup Recovery (HBR) is a fully-managed online backup service that allows you to back up data to the cloud in a convenient, efficient, and secure manner. It supports database backups, Elastic Compute Service (ECS) instances, Apsara File Storage NAS, Object Storage Service (OSS), on-premises data centers, and VMware virtual machines.
1. Click Register Database Instance from the HBR console:
2. Select the ECS instance where the database is installed and specify the login credential for the sysadmin user that has permission to the database you want to back up. You can select the Local Database instance from the tab to specify a local or external hosted database. However, this is not covered in this article.
3. Click Precheck to trigger the pre-check validation on the ECS instance:
4. Once the Precheck validation is completed, you can select Back Up to create a backup plan. Select the database instance you want to backup:
5. Select the backup type and frequency based on your requirement and click Next:
6. Specify the concurrent thread, retry time, and speed limit for the backup or accept the default:
7. Specify the Backup Vault to be used and the backup retention. You can use an existing one or create a new vault.
8. Once configured, you can validate the backup status from the HBR console. You can manually trigger a backup from the console.
9. Trigger the database restore from the console when needed. Select the backup point to restore, when to kickstart the restoration, and which database/ECS instance to restore from the interactive menu.
10. Check the restoration progress from the HBR console:
11. You can check the job history from the HBR console:
Backing up the whole server for a customer that had their Microsoft SQL Server installed within an ECS instance is one of the easiest and fastest methods. However, the backup and restore process would be much longer, as you are backing up the whole server disk instead of the database. Other than using the standard ECS snapshot service, you can use Alibaba Cloud Hybrid Backup Recovery (HBR) to handle the process.
HBR allows you to configure automated backup policies based on your requirements to handle the following use cases:
1. Backup and Restore ECS Instances
2. Clone ECS Instances for Different Environments
3. Cross-Region or Cross-Zone Disaster Recovery Implementation
HBR supports Microsoft's application-consistent backup feature, Volume Shadow Copy Service (VSS), to help prevent unexpected start-up operations during data restoration. This ensures the database starts in a consistent state, avoiding any data loss after restoration.
The backup process for an ECS instance is different compared to the other mentioned options, so the details steps will not be included. Please check the following link for more information:
https://www.alibabacloud.com/help/en/hybrid-backup-recovery/latest/create-an-ecs-instance-backup
We conducted a very simple performance test against the few options discussed in the article using SQL Server 2022 with the 10 GB dataset. Each storage option was mounted separately before triggering the backup.
Note: The following speed comparison is only provided as a reference and may vary due to different executions, configurations, or scenarios.
*An additional 1-3 minutes to sync the data from CSG to OSS
**An additional 11 minutes and 17 seconds to sync the data from the local Rclone cache to OSS.
File Storage NAS is one of the fastest from our backup testing, and it's very easy to set up. However, it is not as flexible as using OSS, where you can manage all your backups directly from the console. The only way to manage your backups is manually after mounting them to a server. The file lifecycle management and recycle bin features are available to reduce the storage fees for infrequent file access and safeguard against accidental file deletion. Overall, it is an excellent solution if you want fast shared storage to host all your backups without needing a GUI console to manage the backup files.
Backups will be generated and uploaded to CSG's internal cache before syncing to OSS. The backup generation time taken may seem high, as it involved multiple steps during the process. The data syncing speed between CSG and OSS is very fast, as it was routed through Alibaba's internal network. Files uploaded to OSS can be managed from the OSS console. You can use the OSS IA/Archive storage class to reduce storage costs (if you do not need to restore the database frequently). Overall, it is a very good solution if you want to back up the database directly to OSS.
The concept of backup via Rclone is similar to CSG. The main difference is the cache is stored locally in the disk, compared to remotely in CSG. It may seem faster at first, but the time taken to sync data back to OSS will be slower, depending on your connection speed. Rclone's VERBOSE logging is recommended during Rclone mounting if you want to monitor the sync process. Overall, it is a good backup solution if you do not mind the long syncing time. At the same time, you need to be aware that Rclone is an open-source tool, and it has a dependency on WinFsp. These tools are not owned by Alibaba Cloud, and the functionalities and capabilities are subject to change, so you will need to monitor the process regularly.
This is one of the simplest approaches to back up SQL Server natively and can be the cheapest method, especially if you use different tiers of OSS storage classes. The only drawback is it only supports the latest version of SQL Server 2022 and is unavailable in any older version. It is the most recommended manual backup solution without using a backup service, but make sure you schedule the backup frequently.
DBS is a Backup as a Service (BaaS) offering that does all backup and restore workloads for your databases. It works on a comprehensive list of databases and works seamlessly, whether it is hosted on Alibaba Cloud or not. You will need to pay for a backup schedule subscription, a storage fee (DBS's in-built storage or OSS), and any network egress traffic charges. You can back up/restore them to all supported databases. As long as you have the database account access with the correct permission, no additional client installation is needed. It may look slow in this comparison, but take note that the test was performed using the smallest micro instance. DBS is highly recommended if you want to back up all your databases in a centralized tool without installing an additional client in your database environment. It can even support seconds-level RPO for certain databases for point-in-time recovery.
HBR works great for backing up an SQL Server running on ECS or on-premise. All you need to do is register the database in the console. You can kickstart the backup and restore the operation after performing a pre-check process. You will need to pay a fixed rental fee for the database backup vault, storage usage fees for your backups, and any network egress traffic charges. The main differences between HBR and DBS are that you can only backup/restore a database after registering it in HBR, and the database must be connected to VPC using VPN or Express Connect if it's not running on Alibaba Cloud. Logical database backup can be done agent-less, but a backup gateway is needed for physical database backup. HBR is highly recommended if you want to back up your database and have a centralized backup tool for all your supported cloud and on-premise resources.
Backing up ECS instances is very straightforward and pretty common for first-level data protection. However, it comes with a lot of drawbacks, such as consuming the most storage cost (as you are backing up the whole disk), backup and restore time would be the longest, and there's no flexibility to restore to an existing running database. It only works if you restore the whole ECS instance. It works, but this would be the least preferred option unless you want to back up the whole instance for a specific reason.
Each discussed backup option in the article has pros and cons. No solution works for every use case due to different business requirements. Also, most of the mentioned backup approaches will work on databases other than SQL Server, so feel free to explore them and pick the one that works best for you!
Setting up Multi-attach EBS Disk Across Multiple ECS Instances with GFS2 File System
H Ohara - March 13, 2024
Alibaba Cloud Community - April 7, 2023
ApsaraDB - November 5, 2024
Alibaba Cloud Community - January 17, 2024
Alibaba Cloud Community - February 25, 2022
Alibaba Clouder - August 2, 2019
ApsaraDB RDS for SQL Server
An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities
Learn More
Database Backup
A reliable, cost-efficient backup service for continuous data protection.
Learn More
Hybrid Cloud Distributed Storage
Provides scalable, distributed, and high-performance block storage and object storage services in a software-defined manner.
Learn More
Database for FinTech Solution
Leverage cloud-native database solutions dedicated for FinTech.
Learn MoreMore Posts by Yen Sheng
