Migrate Sitecore Azure SQL Databases to Other Environments

According to Microsoft Documentation,

A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from the database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database, Azure SQL Managed Instance, or a SQL Server instance.

In this blog, I will walk you through how I have migrated the Sitecore contents from Production to my local instance and other lower environments using a BACPAC file. In my previous work, we call it Reverse Content Population.

The way it was done 5 years ago (it was Sitecore version 7.2 back then), was that we request for a .bak file from our DevOps, which they extract from the Production SQL databases by right-clicking on the target database > Tasks > Back Up… / Restore. This will subsequently need an IIS instance stop and taking the target database offline to continue with the backup/restore task.

But now that we are using Sitecore 9.1.1 that’s hosted in Azure, we would like to achieve the same objective. To do that in this case, we need to:

1. Export the source database to a BACPAC file
2. Save the BACPAC file in Azure via Storage Account or Locally
3. Import the BACPAC file in the target SQL Server
4. Rename imported database and ensure credentials are correct
5. Update the Sitecore CM/CD’s ConnectionStrings.config
6. Set correct SXA Site Grouping (Optional)

1. Export the source database to a BACPAC file

• You can follow the following steps from this Microsoft Documentation if you’re only planning to update another Azure SQL database. But since my objective is to also update my local instance, I used the SSMS to export the BACPAC file.
• Open your SSMS.
• Connect to your target SQL Server by providing the login credentials. This can be taken from the ConnectionStrings.config.

<add name="master" connectionString="Encrypt=True;TrustServerCertificate=False;Data Source=prod-sql.database.windows.net,1433;Initial Catalog=prod-master-db;User Id=masteruser;Password=SitecoreP4ssw0rd!;" />

• Right-click the source database > Tasks > Export Data-tier Application…

• A dialog box will appear with an Introduction screen. Click Next >.
• In the Export Settings, select Save to Microsoft Azure.
• Click Connect… to login your Microsoft Azure account.
• Note the Temporary file name value because we will use this for locally importing your database.
• NOTE: To ensure that this process pushes through, it would not hurt to check that you have at least thrice the size of the source database free in your local disk storage. I have encountered an instance where I scratched my head for a bit to resolve this error, but it turned out to be a local storage issue.

2. Save the BACPAC file in Azure via Storage Account or Locally

• When you see that the Export Summary has been completed, you are rest assured that the saving to Storage Account has been successful and that a local BACPAC file has been created

• You can verify if it has uploaded it correctly in Azure portal via your Storage Account > Storage Explorer (preview) > Select Blob Container.

3. Import the BACPAC file in the target SQL Server

• If you’re importing it to another Azure SQL Server, you can follow the steps shown here.
• But for local Sitecore instance, while in SSMS, right-click the Database folder from the Object Explorer > click Import Data-tier Application…
• Skip the Introduction screen from the dialog box and proceed to locating the local BACPA file that was saved in the temporary folder or any location specified earlier during the export step.

4. Rename imported database and ensure credentials are correct

• Rename the database accordingly.
• If you need to change the user credentials after importing, expand the imported database > Security > Users > Double-click the <user. Change the password from here.

• Having the right credentials is important to set in the ConnectionStrings.config, to avoid errors like this:

To fix this, just connect to your target SQL server via SSMS and follow the following steps:

• From the target database expand the Security > Users folder, and check if there is a user with the same name with that on the error message
• It is possible that the user is missing. In this case, create it:

CREATE USER coreuser WITH PASSWORD = '{B6D53A80-0FBB-4B8F-A5A4-3DE851359DD5}';

• If you have the user but the credentials might be different:

ALTER USER coreuser WITH PASSWORD = '{B6D53A80-0FBB-4B8F-A5A4-3DE851359DD5}'

• And if you want to ensure that this user has the right access:

EXEC sp_addrolemember 'db_owner', 'coreuser';

5. Update the Sitecore CM/CD’s ConnectionStrings.config

• Lastly, update the connection strings in both CM and CD servers. Don’t forget to backup the previous config for reference.
• Run the Sitecore Desktop and verify contents.

6. Set correct SXA Site Grouping

• This step is applicable only to Sitecore Sites based on Sitecore Experience Accelerator (SXA).
• Navigate to the /sitecore/content/<Tenant Group>/<Tenant>/<Site>/Settings/Site Grouping/<Site Name> and update the value to correspond to your environment authoring and CD domain.
• Apply this to all Tenants. Publish.
• Also, update those items where you need to set something related to environment like API endpoints if you have.

After a successful Reverse Content Population, you can sunset the lower environment databases that will not be used anymore to conserve Azure resources. Enjoy your updated content!