Description

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:

• To change the connection to the MSCS_Admin database, run the C:\Program Files (x86)\Commerce Server 11\CSConfig.exe /f command and provide Azure SQL connection information in the Administration Database screen.
• To change the connection to the YourCommerceServerSiteName_productcatalog database in the Commerce Server Manager click Commerce Sites -> Your site name -> Site Resources -> Product catalog, and then click the Connection Strings tab.
• Change the connection to the YourCommerceServerSiteName_profiles database in Commerce Server Manager:
  
  • Under Global Resources -> Profiles(YourSiteName), the s_BizDataStoreConnectionString, s_CommerceProviderConnectionString and s_ProfileServiceConnectionString.
  • Under Global Resources -> Profiles(YourSiteName) -> Profile Catalog -> Profile Definitions -> Data Sources -> ProfileService_SQLSource -> Partitions -> SQLSource.

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:

• Change the connection information inside the \App_Config\ConnectionStrings.config file.

Other Considerations

• Microsoft Azure SQL has limited support for cross-database query and distributed transaction (DTC). Therefore, for a scaled deployment on Azure SQL where multiple Sitecore Commerce instances are involved, we recommend that you deploy Sitecore, Commerce Server and Commerce Engine databases on the same Azure SQL server.
• Commerce Server staging is being used to stage catalog data between preproduction and production environments. The Commerce Server Staging (CSS) service account, which is a Windows user, needs to be granted access permissions in source and destination catalog databases in Azure SQL. You can create the same LOCAL account on source and destination Commerce Server installs and assign Azure SQL access permissions to the same user name (although they exist independently on source and destination installs). You might also need to manually create staging project(s) on the destination server, as the New Project wizard can fail due to Windows user authentication.
• When running Sitecore Commerce databases on Azure SQL, you must tune the size of the service tier to match that of your expected usage. You can do this by monitoring DTU consumption using published Microsoft guidelines and running a representative load against your system. This is particularly important for the Catalog database. If you exceed the DTU limits imposed by the service tier, the commerce commands will fail until you increase the performance level of the database.

Notes

• [1] The Sitecore.Commerce.Engine.DB.dacpac file found in the Commerce Engine SDK can be used instead of the CommerceServicesDbScript.sql script to deploy a Commerce Engine database directly to Azure SQL. However, you cannot follow the steps in the deployment guide for the rest of the deployment. You must follow the steps described in this article for Commerce Engine connection information.