Spotfire Server and Environment - Quick Start

Setting up the Spotfire database (SQL Server)

There are multiple ways to set up a SQL Server database server for storing the Spotfire database, as discussed in previous sections. This topic lists more information about some parameters you must provide, and detailed steps for setting up the database using the provided scripts.

About this task

As discussed on Spotfire database setup, you can create a Spotfire database with the configuration tool or using the CLI command create-db. To use the create-db command, you must first have installed the Spotfire Server. Also note that when using SQL Server from a cloud database provider, additional parameters and configuration is required.

To create and configure the Spotfire databases in a containerized environment, use the Cloud Deployment Kit for Spotfire on GitHub or the prebuilt container images.

Note: If you plan to configure Integrated Windows authentication (IWA) between Spotfire Server and the Spotfire database in SQL, see Setting up the Spotfire database (SQL Server with Integrated Windows authentication).

To use the provided scripts to set up the database, follow the steps below.

Before you begin

  • Download and unzip the Spotfire Server installation kit from the Spotfire Download site. For instructions, see Downloading installation software.
  • You must have access to a SQL server database server.
  • The following settings must be configured on the SQL Server:
    • TCP/IP communication listening on a port (the default is 1433).
    • Case-insensitive collation (at least for the Spotfire database).
      Note: If your installation of SQL Server uses a case-sensitive collation by default, or your data uses a different collation than Latin1_General_CI_AS, you must edit the create_server_db.sql script before running the create_databases_ia.bat script. See Defining the SQL Server collation for details.
    • The command-line database tool sqlcmd must be installed in the system you are going to use to configure the database server.
Note: To reduce the likelihood of encountering issues like Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction, apply MVCC/Row versioning mode by using the setting READ_COMMITTED_SNAPSHOT.
Note: By default, the Microsoft JDBC driver for SQL Server requires a valid, not self-signed, server certificate to be set up on the Microsoft SQL Server. For more information see Fixing Microsoft SQL Server JDBC driver-related issues.

Procedure

  1. Optionally, if you want to set up Spotfire Server to authenticate with a SQL Server database instance using Kerberos, there is additional preparation needed. See Running the database preparation scripts for Kerberos configuration.
  2. Go to the scripts/mssql_install directory in the downloaded installation kit and locate the create_databases script for your platform.
    For details about the available scripts, see The Spotfire database scripts.
  3. The script supports the variables described in the table below. The script will prompt you for any required variable that is not set in your environment or in the script.
    If desired, you can also provide the variables using one of these methods:
    1. Set the variables as environment variables.
    2. Open and edit the script to define the variables in the section under Uncomment to set variables.
    Variable Description Default
    DBSERVER_CONNECTIDENTIFIER This variable is composed of the <DBSERVER_ADDRESS> (listed as <SERVER> in the script) and the <MSSQL_INSTANCENAME>.
    • Replace the script variable <SERVER> with the database server address of the server running the SQL Server instance.
    • Replace the script variable <MSSQL_INSTANCENAME> with the name of the SQL Server instance.

    The syntax for this variable is DBSERVER_CONNECTIDENTIFIER = <DBSERVER_ADDRESS>\<MSSQL_INSTANCENAME>.

    Note: The default installation of SQL Server creates an unnamed instance of the SQL Server. If your SQL Server is a new installation, delete the MSSQL_INSTANCENAME part of the line and enter only the database server address (for SERVER). This causes the connection to be made to the unnamed instance.
    -
    DBSERVER_ADMIN_USERNAME Name of a user with SQL database administrator privileges.

    Default: sa.

    sa
    DBSERVER_ADMIN_PASSWORD Password of the DBSERVER_ADMIN_USERNAME. -
    SPOTFIREDB_DBNAME Name of the Spotfire database that will be created.

    Default: spotfire_server.

    spotfire_server
    SPOTFIREDB_USERNAME Name of the user created to set up the Spotfire database.

    Example: spotfire

    spotfire
    SPOTFIREDB_PASSWORD Password for SPOTFIREDB_USERNAME. -
  4. Open a command prompt and go to the directory where you copied the installation scripts. At the prompt, run the script.

Results

The required Spotfire user and database table schemas are created in your database.

The log file log.txt file is created in the working directory. If the execution should fail, then the log file contains information about the failure. If the execution succeeds, then information about the changes made to the database is available in the log file.

Important: The scripts contain sensitive information so remove them after your Spotfire environment is installed.

What to do next

Install Spotfire Server, or if you have done that already, proceed to Initial configuration.