Related Documents
Document Properties
Kbid287M51
Last Modified04-Feb-2020
Added to KB27-Sep-2018
Public AccessEveryone
StatusOnline
Doc TypeGuidelines, Concepts & Cookbooks
ProductICM 7.10

Guide - Setup Microsoft SQL Server as Intershop Production Database


1 Introduction

The installation of the Microsoft SQL Server software and database instance is not part of the Intershop deployment tools. This document describes how to configure SQL Server and Intershop 7 for a production environment.

2 Microsoft SQL Server Database

2.1 Windows Installation

Download the SQL Server software installer from Microsoft (for English Windows systems). Please make sure to download the appropriate installer for your Windows system language.

2.1.1 Install SQL Server

Please install Microsoft SQL Server. The following points are changed compared to a standard installation:

  • Enable Full-Text Search option:
  • Set the default collation of the new SQL Server instance to Latin1_General_100_CI_AS:

2.1.2 SQL Server Network Configuration

  1. Start Sql Server Configuration Manager (MMC Snap-In) and ensure protocol TCP/IP is enabled:
  2. Restart the SQL Server instance to apply the modified network settings:

2.2 Linux Installation

The following step-by-step instructions describe the setup of the Microsoft SQL Server 2017 database on Linux (CentOS 7.5) for ICM development.

Refer to Quickstart: Install SQL Server and create a database on Red Hat for additional information.

2.2.1 Add Microsoft SQL Server Repositories

Execute the following commands as root user to add the Microsoft SQL Server repositories to the system:

Add Microsoft SQL Server Repositories
# curl -o /etc/yum.repos.d/mssql-server-2017.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
# curl -o /etc/yum.repos.d/mssql-prod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

2.2.2 Install Microsoft SQL Server Packages

Install SQL Server Packages
# yum install mssql-server mssql-server-fts
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.plusline.de
 * extras: mirror.infonline.de
 * updates: mirror2.hs-esslingen.de
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.3026.27-2 will be installed
---> Package mssql-server-fts.x86_64 0:14.0.3026.27-2 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================================
 Package                        Arch                      Version                              Repository                                                   Size
=================================================================================================================================================================
Installing:
 mssql-server                   x86_64                    14.0.3026.27-2                       packages-microsoft-com-mssql-server-2017                    168 M
 mssql-server-fts               x86_64                    14.0.3026.27-2                       packages-microsoft-com-mssql-server-2017                    228 M

Transaction Summary
=================================================================================================================================================================
Install  2 Packages

Total download size: 397 M
Installed size: 722 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): mssql-server-14.0.3026.27-2.x86_64.rpm                                                                                             | 168 MB  00:00:20
(2/2): mssql-server-fts-14.0.3026.27-2.x86_64.rpm                                                                                         | 228 MB  00:00:32
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                             12 MB/s | 397 MB  00:00:32
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mssql-server-14.0.3026.27-2.x86_64                                                                                                            1/2

+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
  Installing : mssql-server-fts-14.0.3026.27-2.x86_64                                                                                                        2/2
  Verifying  : mssql-server-14.0.3026.27-2.x86_64                                                                                                            1/2
  Verifying  : mssql-server-fts-14.0.3026.27-2.x86_64                                                                                                        2/2

Installed:
  mssql-server.x86_64 0:14.0.3026.27-2                                                      mssql-server-fts.x86_64 0:14.0.3026.27-2

Complete!

2.2.3 Configure Microsoft SQL Server

Configure SQL Server
# /opt/mssql/bin/mssql-conf setup
Choose an edition of SQL Server:
 1) Evaluation (free, no production use rights, 180-day limit)
 2) Developer (free, no production use rights)
 3) Express (free)
 4) Web (PAID)
 5) Standard (PAID)
 6) Enterprise (PAID)
 7) Enterprise Core (PAID)
 8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate number of licenses in place to install and run this software.

Enter your edition(1-8): 2
The license terms for this product can be found in /usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409

The privacy statement can be viewed at: https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:y

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Setup has completed successfully. SQL Server is now starting.

2.2.4 Open Firewall Port 1433

Open Firewall
# firewall-cmd --zone=public --add-port=1433/tcp --permanent
success
# firewall-cmd --reload
success

2.2.5 Install Additional Tools

Install SQL Server Tools
# yum install mssql-toolsLoaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.plusline.de
 * extras: ftp.plusline.de
 * updates: mirror.23media.de
base                                                                                                                                      | 3.6 kB  00:00:00
extras                                                                                                                                    | 3.4 kB  00:00:00
packages-microsoft-com-mssql-server-2017                                                                                                  | 2.9 kB  00:00:00
packages-microsoft-com-prod                                                                                                               | 2.9 kB  00:00:00
updates                                                                                                                                   | 3.4 kB  00:00:00
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:17.1.0.1-1 will be installed
--> Processing Dependency: msodbcsql17 < 17.2.0.0 for package: mssql-tools-17.1.0.1-1.x86_64
--> Processing Dependency: msodbcsql17 >= 17.1.0.1 for package: mssql-tools-17.1.0.1-1.x86_64
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.1.0.1-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================================
 Package                         Arch                            Version                              Repository                                            Size
=================================================================================================================================================================
Installing:
 mssql-tools                     x86_64                          17.1.0.1-1                           packages-microsoft-com-prod                          253 k
Installing for dependencies:
 msodbcsql17                     x86_64                          17.1.0.1-1                           packages-microsoft-com-prod                          4.1 M

Transaction Summary
=================================================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 4.4 M
Installed size: 4.4 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): mssql-tools-17.1.0.1-1.x86_64.rpm                                                                                                  | 253 kB  00:00:31
(2/2): msodbcsql17-17.1.0.1-1.x86_64.rpm                                                                                                  | 4.1 MB  00:01:25
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                             52 kB/s | 4.4 MB  00:01:25
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
The license terms for this product can be downloaded from https://aka.ms/odbc170eula and found in
/usr/share/doc/msodbcsql17/LICENSE.txt . By entering 'YES', you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
yes
  Installing : msodbcsql17-17.1.0.1-1.x86_64                                                                                                                      1/2
The license terms for this product can be downloaded from http://go.microsoft.com/fwlink/?LinkId=746949 and found in
/usr/share/doc/mssql-tools/LICENSE.txt . By entering 'YES', you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
yes
  Installing : mssql-tools-17.1.0.1-1.x86_64                                                                                                                      2/2
  Verifying  : msodbcsql17-17.1.0.1-1.x86_64                                                                                                                      1/2
  Verifying  : mssql-tools-17.1.0.1-1.x86_64                                                                                                                      2/2

Installed:
  mssql-tools.x86_64 0:17.1.0.1-1

Dependency Installed:
  msodbcsql17.x86_64 0:17.1.0.1-1


Complete!

3 New SQL Server Database

3.1 Important Parameters

A Microsoft SQL Server database for Intershop Commerce Management should be created with the following parameters:

Parameter IDParameter NameParameter ValueComment
COLLATE                  
CollationLatin1_General_100_CI_ASCase-insensitive database object names
READ_COMMITTED_SNAPSHOT
Is Read Committed Snapshot OnON (True)Allow access to versioned rows under the default READ COMMITTED isolation level (i.e., prevent writers from blocking readers)
@loginame (for sp_changedbowner)OwnerintershopSeparate login with SQL Server authentication and the default database set to the newly created database

Please note that the schema for your database needs to be the default one - "dbo".

3.2 Example SQL Script

The attached SQL script can be used to (re-)create the login and database for Intershop Commerce Management development.

This script is just an example. Please change at least login and password.

Check the output of the script that it was executed successfully.

3.3 Performance Option Data Files

A Microsoft SQL Server database for Intershop Commerce Management should have a couple of data files which should be at least half of the number of used cores. See the Microsoft documentation for information on how to achieve that.


Disclaimer

The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.

Customer Support
Knowledge Base
Product Resources
Support Tickets