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:
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:
Set the authentication mode to Mixed Mode.
Specify the administrator password of the
saaccount.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 |
|---|---|---|---|
| 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 | 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:
- Collation: Latin1_General_100_CI_AS
- 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.