Fit Sitecore 9 databases in Aliyun RDS

Background

Aliyun is a major cloud infrastructure provider in China. Like Amazon’s AWS and Google’s GCP, it provides an option of using RDS (Relational Database Service) instead of traditional database virtual machines. This time, it becomes our client’s database of choice, and it’s my challenge to fit Sitecore 9 databases into it.

As you might probable experienced, installing Sitecore with RDS always makes life a little bit harder, as your database user will not have the full power of sysadmin role. It’s not a big deal for legacy versions of Sitecore like 7 or 8, however for 9.0 and later, the SIF (Sitecore Installation Framework) takes advantage of contained database authentication by default. It’s good for fine-grained authority control, yet the side effect is that you must have the contained database authentication enabled on your database, which can only be done with a SQL server administrator.

The challenge

For AWS RDS, you can either overcome the issue by changing database parameters, or have the AWS support engineer do it for you, or even let him temporarely grant you administrator privileges (for this part, read this great article: https://jeroen-de-groot.com/2018/07/19/deploying-sitecore-9-in-aws-rds/). But do they work for Aliyun RDS too? The simple answer is: NO. There is no place to set database parameters in Aliyun RDS console, and their cloud engineers just refuse to do scripting for you, or grant you admin privileges even for a single second.

The workaround

Since I’m not going to deeply custom my SIF code,  after some researches, I finally found a workaround. Here’s it:

1. First, install a SQL Server Express version on the server you are going to install Sitecore on, then install the sitecore instance with database settings pointed to this Express database. That allows you to complete the installation of Sitecore, we will handle the connection strings and other stuffs later.
2. Then, install a sitecore XP0 standalone instance, with the exact same isntance name and database name settings as your real instance, on a windows server with SQL Server installed. This step is to create the set of databases that to be migrated to Aliyun RDS. Or you can directly use the databases created in step 1.
3. You have 2 options now: Use Aliyun’s DTS (Database Transfer Service), or use Aliyun’s OSS (Object Storage Service) Database Migration Service to migrate the databases to Aliyun RDS. DTS is an online service that transfer databases from source to target database instance. So to use this service, your source database need to either have a public IP, or locates in your Aliyun VPC. OSS is pretty the same as Amazon S3, after you uploading your database backup files, you may choose them as the restore options for your RDS instances. I chose the OSS way as I installed the databases for migration on my local PC.
4. Change the connection strings. Change all SQL Server connection string settings with your sql account and RDS data sources, replacing the contained authentication accounts and local data sources.
5. Clean up The Xdb.Collection.ShardMapManager database. The Xdb.Collection.ShardMapManager database defines the shards settings for each shard map in the __ShardManagement.ShardsGlobal table. You need to create a script to update the fields to reflect RDS data sources (and database names if you changed them during the migration).

After these steps, your Sitecore instance should be running well with your Aliyun RDS databases.

Other Thoughts

You may be concerned about that is there any consequnces of changing contained authentication users to SQL users in connection strings. The fact is that the contained database authentication is only used during installation and has no impact on the application after installation. Whether you use SQL users or contained users, the application will work fine.

Thanks for reading.