Guide - Setup Microsoft SQL Server as Intershop Production Database
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.
Microsoft SQL Server Database
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.
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:
SQL Server Network Configuration
- Start Sql Server Configuration Manager (MMC Snap-In) and ensure protocol TCP/IP is enabled:
- Restart the SQL Server instance to apply the modified network settings:
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.
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
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!
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.
Open Firewall Port 1433
Open Firewall
# firewall-cmd --zone=public --add-port=1433/tcp --permanent success # firewall-cmd --reload success
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!
New SQL Server Database
Important Parameters
A Microsoft SQL Server database for Intershop Commerce Management should be created with the following parameters:
| Parameter ID | Parameter Name | Parameter Value | Comment |
|---|---|---|---|
| Collation | Latin1_General_100_CI_AS | Case-insensitive database object names |
READ_COMMITTED_SNAPSHOT | Is Read Committed Snapshot On | ON (True) | Allow access to versioned rows under the default READ COMMITTED isolation level (i.e., prevent writers from blocking readers) |
@loginame (for sp_changedbowner) | Owner | intershop | Separate 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".
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.
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.