Guide - Setup Microsoft SQL Server as Intershop Development Database

Introduction

The installation of the Microsoft SQL Server software and database instance is not part of the Intershop deployment tools, but is performed by the MS SQL setup routine. This document describes how to configure the MS SQL Edition and Intershop 7 to use it as a development database.

Please consider the deployment changes regarding Microsoft SQL Server:

Guide - 7.10 Database Deployment Configuration

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

To install SQL Server, perform the following steps:

Launch the downloaded installer application (SQLServer2017-SSEI-Dev.exe) and select Custom as installation type:
On some systems, the installation will ask you if you want to continue in English (United States).
For the following screenshots Yes was selected:
Set a folder for the media location:
Click Install and wait until it is finished:
Use the SQL Server Installation Center application to install a New SQL Server stand-alone installation:
Enter a product key or select a free edition to use, i.e., the Developer edition should be sufficient in most cases (non-production use only):
Accept the license agreement:
Select the target folders for the new installation and the features to be installed and make sure to include the Full-Text Search option:
Enter an ID for the new SQL Server instance:
Verify the service accounts and adjust the system service startup types:
Set the default collation of the new SQL Server instance to Latin1_General_100_CI_AS:
Adjust the database engine configuration:
1. Set the authentication mode to Mixed Mode.
2. Specify the administrator password of the sa account.
3. Add additional administrative users.
Verify all configuration settings and start the installation process:

SQL Server Network Configuration

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

3. If you install the newer version (SQL2019-SSEI-Dev.exe) then you will find the configurations from point 1 and 2 in "Windows Search" → "Computer Management":

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!

Microsoft SQL Server Management Studio (SSMS)

This guide explains how to use SSMS to install and configure an MS SQL database. SSMS is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, as well as build queries and scripts.

Download SQL Server Management Studio (SSMS)

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
`COLLATE`	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)
`DEFAULT_LANGUAGE`	Default Language	`English`	Can also be set on server level and/or for the login. The default language affects date and number formats as well as system error messages. However, SQL queries should be implemented independently of the default language setting (i.e. use date format patterns instead of relying on the default language's date format pattern).
`@loginame` (for `sp_changedbowner`)	Owner	`intershop`	Separate login with SQL Server authentication and the default database set to the newly created database

Note

The schema for your database needs to be the default one - "dbo".

SQL Script

The attached SQL script can be used to (re-)create the login and database for Intershop Commerce Management development. Check the output of the script to see if it was executed successfully.

Step by Step Instructions

Follow these steps to create a new Microsoft SQL Server database for Intershop Commerce Management using Microsoft SQL Server Management Studio (SSMS):

Connect to the target SQL Server as system administrator:
Go to Object Explorer | Security | Logins | New Login... to create a new login:
Enter the login name, select SQL Server authentication, enter the password and uncheck the password policy checkboxes:

The Default database will be changed at a later stage of the process.
Go to Object Explorer | Databases | New Database... to create a new database:
Enter the name of the new database, use the login created in step 3 as database owner and set the correct storage locations of the database files:
Set the following non-default database options:
1. Collation: Latin1_General_100_CI_AS
2. Is Read Committed Snapshot On: True
Change the default database of the login created in step 3 to the newly created database:

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.

Table of Contents