Installing Microsoft SQL Server for i2 Analyze

i2 Analyze supports using Microsoft SQL Server as its database management system. This topic describes how to install SQL Server for use with i2 Analyze.

Location

If you are creating a production deployment, you can install SQL Server in any location. When you install SQL Server, record the location of the installation directory. You must specify this location in the deployment toolkit before you can deploy i2 Analyze.

If you are creating an example deployment, install SQL Server in the default location:

  • For Windows: C:\Program Files\Microsoft SQL Server.

  • For Linux: /opt/mssql. Install the SQL Server tools in the default path: /opt/mssql-tools.

Features

In all deployments, you must ensure that the following features are installed or enabled:

  • Database Engine Services

  • SQL Server Authentication

  • TCP/IP Protocol

In all deployments, you must install the ODBC Driver for SQL Server and sqlcmd utility on your database server.

On Windows:

On Linux:

Note: To ensure that you see drivers and tools that are compatible with your version of SQL Server, select it from the drop-down menu.

You can also install Microsoft SQL Server Management Studio to administer your SQL Server installation. If you are using SQL Server on Linux, you can install SQL Server Management Studio on a Windows workstation and connect to your SQL Server installation.

To create an example deployment of i2 Analyze on Windows, the instance name that you use must be MSSQLSERVER. Regardless of your operating system, the port number must be 1433.

Users

You must have an SQL Server Authentication Login that has the following permissions:

  • Server roles:

    • dbcreator

    • bulkadmin, to ingest the example data. The bulkadmin role is not supported on Linux

  • User mappings for the msdb database:

    • SQLAgentUserRole

    • db_datareader

Note: In all scenarios, the user that you use to run the deployment scripts must have permission to create and modify the database.

Collation

In SQL Server, the collation settings that determine the case- and accent-sensitivity of comparisons in a database (for example, whether Cafe matches café) are set at creation and cannot be changed without re-creating the database.

Important: Before any deployment of i2 Analyze with the Chart Store or the Information Store, read the documentation about the Collation setting in InfoStoreNamesSQLServer.properties and change the default value if you need to.

Post-install

  • Ensure that the SQL Server Agent service is running.

  • On Windows, if you want to use the instance name to connect to SQL Server, ensure that the SQL Server Browser service is running.

Remote SQL Server database storage

If you plan to deploy i2 Analyze with remote database storage, you must install SQL Server on the database server, and SQL Server or Microsoft Command Line Utilities for SQL Server on the application server. You can install SQL Server and the Command Line Utilities according to the previous instructions.

High availability

When you install SQL Server for HADR, you must ensure that the following statements are true:

  • All database servers are in the same IP address range.

  • All database servers are members of the same domain for SQL Server Always On availability groups DNS name resolution.

  • Two static TCP/IP addresses, one for the Windows Failover Cluster and one for the SQL Server Always On availability group. The IP addresses must be in the same range.

  • The same version of Windows Server is installed.

For information about installing SQL Server with SQL Server Always On availability groups, see Prerequisites, Restrictions, and Recommendations for Always On availability groups.