Running Sitecore Commerce 8.2.1 with Microsoft Azure SQL databases


This article describes known issues, workarounds, and limitations when you run Sitecore Commerce 8.2.1 with Microsoft Azure SQL databases.

Note: Sitecore currently provides experimental support for Azure SQL and Sitecore Commerce 8.2.1 compatibility. Sitecore Commerce 8.2.1 has not been extensively tested with Azure SQL and other potential issues can exist. In case of related issues, Sitecore Support will help with troubleshooting, although there is no guarantee that a fix will be available.

Deployment Issues

Some PowerShell cmdlets (Initialize-CSSite) and SQL scripts (Commerce.Storefront.ProfileDatabase.dacpac, CommerceServicesDbScript.sql [1]) used during Sitecore Commerce deployment do not work with Azure SQL due to the references to Windows users or cross-database queries.

Solution: To resolve this issue, use an on-premise SQL Server as an intermediate storage during deployment and then follow the steps documented in the Sitecore Commerce 8.2.1 Deployment Guide.

After you finish the deployment and verify the functionalities using on-premise SQL instance(s), you can use the Database Migration Tool to move the local database(s) to Microsoft Azure SQL. The following steps are based on SQL Server Management Studio (SSMS) version 17.2 (minimal version requirement).

To move local databases to Microsoft Azure SQL:

  1. Launch SSMS. In a typical Sitecore Commerce 8.2.1 deployment, the following databases are installed and must be moved to Azure SQL:
    • MSCS_Admin
    • YourCommerceServerSiteName_productcatalog
    • YourCommerceServerSiteName_profiles
    • SitecoreCommerce_Global (NOTE: This database must be moved AFTER the bootstrap process mentioned in later steps)
    • SitecoreCommerce_SharedEnvironments
    • YourSitecoreSiteNameSitecore_Core
    • YourSitecoreSiteNameSitecore_Master
    • YourSitecoreSiteNameSitecore_Web
    • YourSitecoreSiteNameSitecore_Analytics
    • YourSitecoreSiteNameSitecore_Sessions
  2. For each database, delete all the Windows users or replace them with SQL users under Security -> Users.
  3. For each database, right-click on the database node and from the context menu, click Tasks -> Deploy Database to Microsoft Azure SQL Database.
  4. Provide the Connect information for your Azure SQL Database server. You must keep the same database names.
  5. Follow the on-screen instructions to finish the rest of the steps.

After all the databases are deployed to Azure SQL, you must change the connection string references.

For Commerce Server:

Note: If you copy the connection string directly from the Azure portal, you must remove the tcp: prefix. Otherwise, the Commerce Server Manager will throw an exception.

For Commerce Engine (for example, CommerceAuthoring):

  1. Find the environment json files corresponding to your installation (for a default deployment, it is under the C:\inetpub\CommerceAuthoring\wwwroot folder) and make the following changes:
    • Inside the bootstrap\Global.json file, under the Sitecore.Commerce.Plugin.Customers.Cs.ProfilesSqlPolicy, Sitecore.Commerce.Plugin.Customers.Cs type, change the connection information to SitecoreCommerce_Global. 
    • Inside the data\Environments\PlugIn.Habitat.CommerceAuthoring-1.0.0.json file, under the Sitecore.Commerce.Plugin.SQL.EntityStoreSqlPolicy, Sitecore.Commerce.Plugin.SQL type, change the connection information to SitecoreCommerce_SharedEnvironments. 
    • Under the Sitecore.Commerce.Plugin.Customers.Cs.ProfilesSqlPolicy, Sitecore.Commerce.Plugin.Customers.Cs type, change the connection information to YourCommerceServerSiteName _profiles.
    • Under the Sitecore.Commerce.Plugin.Inventory.Cs.InventoryCatalogSqlPolicy, Sitecore.Commerce.Plugin.Inventory.Cs type, change the connection information to YourCommerceServerSiteName _productcatalog.

    The changed connection information to Azure SQL should look like the following:

    "Server": "URLtoAzureSQLInstance",
    "Database": "CFSolutionStorefrontSite_profiles", (keep the same database names)
    "TrustedConnection": false,
    "UserName": "AzureUser",
    "Password": "AzurePassword"
  2. To refresh the old connection strings cache, restart the Commerce Engine application pool.
  3. To update the connection strings information inside the SitecoreCommerce_Global database, run Bootstrap (for example, http://localhost:5000/commerceops/Bootstrap()).
  4. Now you can move the SitecoreCommerce_Global database to Azure SQL following the steps described previously.

For Sitecore XP:

Other Considerations