Setting up the Spotfire database (SQL Server)

If you are running Microsoft SQL Server, follow these steps to set up the Spotfire database before you run the Spotfire Server installer.

About this task

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).

Before you begin

You have downloaded and unzipped the Spotfire Server installation kit from the TIBCO eDelivery web site; for instructions, see Downloading installation software.
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.bat script. See step 2 below.
- The command line database tools (sqlcmd, etc.) must be in the system path.

Procedure

Copy the <installation files dir>/scripts/mssql_install directory to a location where you can edit it.
Optional: If your installation of SQL Server uses a case-sensitive collation by default, or if you need to define a different collation, follow these steps:
1. On the SQL Server computer, open the mssql_install directory, and then open the create_server_db.sql script in a text editor.
2. Locate the line --create database $ (SERVERDB_NAME) collate Latin1_General_CI_AS;
3. Remove the leading dashes (--).
4. If needed, replace Latin1_General_CI_AS with the name of the desired collation, but make sure it is case-insensitive (CI). See the SQL Server documentation for information about available collations.
5. Comment out the next line by inserting leading dashes (--), so that the line looks like this: --create database $(SERVERDB_NAME)
6. Save the file and close the text editor.
Open the mssql_install directory, and then, in a text editor, open the create_databases script that corresponds to your platform:
- Windows: create_databases.bat
- Linux: create_databases.sh
- Windows (SQL Server running on Amazon RDS): create_databases_rds.bat
- Linux (SQL Server running on Amazon RDS): create_databases_rds.sh

In the section under "Set these variables to reflect the local environment", edit the create_databases script by providing the appropriate database server details.

Table 1. Definitions of the variables in create_databases
Variable	Description
CONNECTIDENTIFIER	Replace `<SERVER>` with the name of the server running the SQL Server instance, and replace `<MSSQL_INSTANCENAME>` with the name of the SQL Server instance. 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 SERVER name. The connection will be made to the unnamed instance.
ADMINNAME	Name of a user with SQL database administrator privileges, usually "sa".
ADMINPASSWORD	Password of the `ADMINNAME` user.
SERVERDB_NAME	Name of the Spotfire database that will be created; spotfire_server is the default.
SERVERDB_USER	Name of the user that will be created to set up the Spotfire database.
SERVERDB_PASSWORD	Password for `SERVERDB_USER`.

Example

This is what the file section might look like after modification:

rem Set these variable to reflect the local environment:
set CONNECTIDENTIFIER=DBSERVER\MSSQL	
set ADMINNAME=sa
set ADMINPASSWORD=admin123
set SERVERDB_NAME=spotfire_server
set SERVERDB_USER=spotfire_db
set SERVERDB_PASSWORD=spotfire_db123

Save the file and close the text editor.
Open a command line as an administrator and go to the directory where you placed the scripts.
Type create_databases.bat (Windows) or create_databases.sh (Linux) and press Enter.
If the parameters are correct, text that is similar to the following text is displayed at the command line:

Note: The log.txt file is created in the same directory as the create_databases file. Examine this file to verify that no errors occurred and retain the log for future reference.

Note: Because the scripts contain sensitive information, it is good practice to remove them after your Spotfire environment has been installed.

What to do next

Install Spotfire Server