Install a local Microsoft SQL Server for SAP Commerce Cloud development in 5 minutes
7 min read
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:
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:
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.
It is required to install docker (https://www.docker.com/) to complete these steps. The total duration of the installation is ~5 minutes.
Install latest version of MS SQL Server for docker. This should take around 2 minutes.
Launch Docker Image with the following command (replacing "reallyStrongPwd123" with your own password). This should be almost instant.
Once completed you can check the container with the following command:
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:
To connect using command line, just run:
To find which is the version of the MS SQL Server installed, execute the following:
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:
Create database (schema)
Configure the connection to SAP Commerce in local.properties connection settings. More details in the product documentation.
In cases where you start your local version of SAP Commerce and you see the following error is displayed in console logs:
Just execute the following from the command line after login in your local MS SQL Server instance:
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: