CX Works

CX Works brings the most relevant leading practices to you.
It is a single portal of curated, field-tested and SAP-verified expertise for SAP Customer Experience solutions

Navigation
Last Updated: Apr 30, 2021

Install a local Microsoft SQL Server for SAP Commerce Cloud development in 5 minutes

7 min read

Install a local Microsoft SQL Server for SAP Commerce Development in 5 minutes.


As discussed in Getting Started with SAP Commerce Cloud, SAP Commerce Cloud in the Public Cloud uses an Azure SQL Database, which is based on Microsoft SQL Server (from now on referred as MS SQL Server). It is a best practice to implement your solution using the closest database type and version. This will ensure avoiding unexpected database related issues when deploying your application. For example, when adding new column types it might be supported for some databases as HANA or MySQL but not for MS SQL Server. By developing with a local MS SQL Server these kind of issues can be avoided. This article shows you how to setup an MS SQL Server for your local development. Before reading this article you may also want to refer to Build and Deploy Your First SAP Commerce Cloud Project.

Table of Contents

Which version of MS SQL Server is installed in Azure

To identify the MS SQL Server version in Azure, there is the concept of Compatibility Level. When a database is created in Azure, by default it will be using the latest available version of MS SQL Server but using the Compatibility Level, which is defined in Microsoft's Documentation as represented below:

Example compatibility table

For instance in April 2020, the Compatibility Level of a newly created Azure SQL database is 150, which corresponds to MS SQL Server 2019. Note that Microsoft does not update Database Compatibility Level for existing databases. It is up to you to do so at your own discretion. Microsoft highly recommends that customers plan to upgrade to the latest compatibility level in order to leverage the latest query optimization improvements.

To identify the Compatibility Level of your database, execute the following SQL query:

SELECT name, compatibility_level FROM sys.databases;

Note that the version displayed from SAP Commerce Cloud HAC administration console in Monitoring > Database section will not correspond to the actual version, since it does not consider the Compatibility Level.


Installation steps

It is required to install docker (https://www.docker.com/) to complete these steps. The total duration of the installation is ~5 minutes.

  1. Install latest version of MS SQL Server for docker. This should take around 2 minutes.

    docker pull microsoft/mssql-server-linux
  2. Launch Docker Image with the following command (replacing "reallyStrongPwd123" with your own password). This should be almost instant.

    docker run -d --name sql_server_demo -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=reallyStrongPwd123' -p 1433:1433 microsoft/mssql-server-linux

    Once completed you can check the container with the following command:

    docker ps


  3. Connect to local SQL Server. You can connect to the newly created local SQL Server by using the client Microsoft SQL Operations Studio or with command line using sql-cli:

    sudo npm install -g sql-cli

    To connect using command line, just run:

    mssql -u sa -p reallyStrongPwd123

    To find which is the version of the MS SQL Server installed, execute the following:

    select @@version


Create and configure database for SAP Commerce

After installing a local MS SQL server, to work with SAP Commerce, it is required to create a schema to hold all the tables and structures required. Following the steps to do so:

  1. Create database (schema)

    create database commerce;
  2. Configure the connection to SAP Commerce in local.properties connection settings. More details in the product documentation.

    db.url=jdbc:sqlserver://localhost:1433;databaseName=commerce;responseBuffering=adaptive;loginTimeout=10
    db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
    db.username=sa
    db.password=reallyStrongPwd123
    db.tableprefix=
    ##Following property allows enabling limit support for SQL Server 2012
    #sqlserver.enableLimitSupportForSQLServer2012=false

    In cases where you start your local version of SAP Commerce and you see the following error is displayed in console logs:

    [java] WARN [main] [hybrisserver] *****************************************************************************
    [java] WARN [main] [hybrisserver] ****************************** WARNING **********************************
    [java] WARN [main] [hybrisserver] *****************************************************************************
    [java] WARN [main] [hybrisserver] ************ Parameter READ_COMMITTED_SNAPSHOT is turned off!! ************
    [java] WARN [main] [hybrisserver] ** Without this parameter set if you are using ImpEx to run multi-threaded **
    [java] WARN [main] [hybrisserver] ** imports, you may experience deadlocks. Hybris recommends to have this **
    [java] WARN [main] [hybrisserver] *********** parameter set. You can set it in the following way: *************
    [java] WARN [main] [hybrisserver] ******** ALTER DATABASE database_name SET READ_COMMITTED_SNAPSHOT ON; *******
    [java] WARN [main] [hybrisserver] *****************************************************************************
    [java] WARN [main] [hybrisserver] *****************************************************************************
    [java] WARN [main] [hybrisserver] 
    [java] WARN [main] [hybrisserver] 
    [java] WARN [main] [hybrisserver] *****************************************************************************
    [java] WARN [main] [hybrisserver] ****************************** WARNING **********************************
    [java] WARN [main] [hybrisserver] *****************************************************************************
    [java] WARN [main] [hybrisserver] ************ Parameter ALLOW_SNAPSHOT_ISOLATION is turned off!! ************
    [java] WARN [main] [hybrisserver] ** Without this parameter set if you are using ImpEx to run multi-threaded **
    [java] WARN [main] [hybrisserver] ** imports, you may experience deadlocks. Hybris recommends to have this **
    [java] WARN [main] [hybrisserver] *********** parameter set. You can set it in the following way: *************
    [java] WARN [main] [hybrisserver] ******* ALTER DATABASE database_name SET ALLOW_SNAPSHOT_ISOLATION ON; *******
    [java] WARN [main] [hybrisserver] *****************************************************************************
    [java] WARN [main] [hybrisserver] *****************************************************************************

    Just execute the following from the command line after login in your local MS SQL Server instance:

    ALTER DATABASE commerce SET READ_COMMITTED_SNAPSHOT ON;
    ALTER DATABASE commerce SET ALLOW_SNAPSHOT_ISOLATION ON;

Conclusion

By following the steps described in this article you should now have a local instance of MS SQL Server connected to your local SAP Commerce instance. This will improve the quality of SAP Commerce Cloud implementations by minimizing database related issues at build, deploy and run time.

For more information on SQL Server please see the following articles:

Overlay