Sitecore 9.2: Replace Azure Service Bus with SQL Database

• Wednesday, May 20, 2020
• By Simone Locatelli, Senior Sitecore Developer

When installing Sitecore on Azure using the Sitecore Azure Toolkit, by default an Azure Service Bus resource is created to handle the Sitecore Message Bus implementation.

This is a new change introduced with Sitecore 9.2 that replaces the previous implementation that was based on a SQL Server Database. That said, the SQL implementation hasn’t been abandoned completely. Considering that Sitecore does not offer a migration tool from SQL to Azure Service Bus and a few other reasons, we’ve decided to keep using a SQL database for the Messaging Queue. Little we knew, that Sitecore did not offer any documentation in this regard.

By comparing our local Sitecore 9.2 development instance with the one on Azure and with help from the Sitecore support team, we were able to successfully switch back to SQL Server and remove the Azure Bus. When we’ve contacted Sitecore Support to point us for the official documentation on this topic, we were told they didn’t have any article yet and that our request has been registered. Hopefully the gap in their documentation will be soon filled, but in the meantime, let’s see how we’ve managed resolved the issue:

1. Create a new SQL Server Database for Messaging Queue from the Azure portal Since we already had a SQL Server hosting all other Sitecore databases, we’ve created a new Database with size Standard S0 and named it with the suffix -msgqueue-db
   
   
2. Create a new SQL user with read and write permissions Connect to your Azure SQL Server using SQL Server Management Studio using the admin credentials. If you get an error make sure that your IP has been added in the list of exceptions (SQL Server resource - > Firewalls and virtual networks). Copy the script below into, replace the password value and then run the script.
   
   # Create a new user
   CREATE USER messaginguser WITH PASSWORD = '';
   
   # Assign Read and Write rights
   EXEC sp_addrolemember 'db_datareader', 'messaginguser’;
   GO
   EXEC sp_addrolemember 'db_datawriter', 'messaginguser';
   GO
   
   It’s recommended to test the new user using its credentials and targeting the messaging queue database (Connect -> Database Engine… -> Options -> tab Connection Properties -> Connect to database and enter the name of the database).
   
   
3. Update connection strings
   For each of the app services listed below, open the KUDU console and navigate to the connection strings file (site/wwwroot/App_Config/ConnectionStrings.config) and replace the value for the messaging connection string with a valid SQL Server connection string (use the other database connection strings as examples)
   
   
   1. Content Delivery
   2. Content Management
   3. Marketing Operations
   4. xConnect Collection
   5. xConnect Search
      
      
4. Update the messaging connection string in your ConnectionStrings.config files. Since we’ had a Sitecore XP Scaled instance, we then updated the following services:
   
   
   1. Content Management
   2. Content Delivery
   3. XC Collect
   4. XC Search
   5. Marketing Operations
      
      
5. Disabled Azure.xml files in:
   1. Cortex Processing
      1. App_Data/Config/Sitecore/EmailCampaign
      2. App_Data/Config/Sitecore/Messaging
         
         
   2. Marketing Operations
      1. App_Data/Config/Sitecore/EmailCampaign
      2. App_Data/Config/Sitecore/Messaging
      3. App_Data/jobs/continuous/AutomationEngine/App_Data/Config/sitecore/EmailCampaign
      4. App_Data/jobs/continuous/AutomationEngine/App_Data/Config/sitecore/Messaging
         
         It's worth double-checking the other services if you’re using a different architecture.
         
         Update Sitecore Web Jobs connection strings. These web jobs are usually located within other app services, under the directory wwwroot/App_Data/jobs In the case of our XP1 Scaled instance, we’ve updated the following ConnectionStrings.config
         
         
   3. Marketing Operation app service
      App_Data/jobs/continuous/AutomationEngine/App_Config/ConnectionStrings.config
   4. Cortex Processing app service
      App_Data/jobs/continuous/ProcessingEngine/App_Config/ConnectionStrings.config 
      NOTE: Certain Sitecore jobs are installed within other Sitecore App Services and they also contain ConnectionStrings.config file that need to be updated
      
      
6. Open the web.config for all Sitecore XP roles, search for the setting messagingTransport:define and set its value to SQL
   
   That in all xConnect instances, also jobs inside of the "App_Data\jobs" folder:
   -> in the "\App_Data\Config\Sitecore\EmailCampaign" folder, azure messaging config disabled but its SQL analogs is enabled;
   -> in the "\App_Data\Config\Sitecore\Messaging" folder, file "sc.XConnect.Messaging.SqlServer.xml" is enabled

Finally, restart all the App Services to ensure all the changes made are applied.

I hope that will help if you find yourself having to swap Service Bus to a SQL solution.