Setting up a Spotfire database in Google Cloud SQL for SQL Server

Prerequisites

• You have access to a SQL server database on Google Cloud.
• You have downloaded and installed SQL Server Management Studio (SSMS) on your computer.
• You have downloaded the Spotfire Server installation kit from the TIBCO eDelivery website, and you have extracted the files.

Important: Use the Microsoft JDBC Driver for SQL Server for the database connection.

Perform this task in your terminal or at the command prompt.

Instructions

1. Open Microsoft SQL Server Management Studio (SSMS).

   In the Connect to Server dialog, select Database Engine for Server Type, enter the name of the Google Cloud SQL database and your authentication type and credentials, and then click Connect.

2. In the Object Explorer pane of SSMS, expand Databases > System Databases, right-click the master database, and then click New Query.

3. Create the Spotfire database by entering the following text in the query pane, replacing SERVERDB_NAME with the name of the Spotfire database to create:

    

   CREATE DATABASE SERVERDB_NAME

GO

4. Click Execute.

   Result: The message pane displays "Commands completed successfully.

   Note: This result text should be displayed when you click Execute in SSMS.

5. In the query pane, replace the previous command with the following text, replacing SERVERDB_USER with the name of the user to create to set up the Spotfire database, and SERVERDB_PASSWORD with the password for that user:

    

   CREATE LOGIN SERVERDB_USER WITH PASSWORD=N'SERVERDB_PASSWORD'

GO

6. Click Execute.

7. In the query pane, replace the previous command with the following text, replacing SERVERDB_USER  with the name of the user that was created in the previous step:

    

   ALTER LOGIN SERVERDB_USER ENABLE

GO

8. Click Execute.

9. In the Object Explorer pane, refresh the database view. The newly created database should be visible.

10. To finish setting up the Spotfire database user, right-click the newly created database, and then click New Query.

11. In the new query pane, enter the following text, replacing SERVERDB_USER  with the name of the user that was created in step 9:

     

    CREATE USER SERVERDB_USER

GO

12. Click Execute.

13. In the query pane, replace the previous command with the following text, again replacing SERVERDB_USER with the name of the user that was created in step 9:

     

    GRANT CONTROL TO SERVERDB_USER

GO

14. Click Execute.

15. In a text editor, open the file <Spotfire server installation kit dir>\scripts\mssql_install\create_server_db.sql.

16. In the SSMS query pane, replace the previous command with all the text from the file create_server_db.sql.

17. In the query pane, delete all the text before the following line: 

create table "SN_VERSION" (

18. Click Execute.

19. In a text editor, open the file <Spotfire Server installation kit dir>\scripts\mssql_install\populate_server_db.sql.

20. In the SSMS query pane, replace the previous commands with all of the text from the file populate_server_db.sql.

21. In the query pane, delete all the text before the following line:

insert into SN_VERSION (SPOTFIRE_VERSION, SCHEMA_VERSION) values ('SPOTFIRE_VERSION', 'SPOTFIRE_SCHEMA_VERSION');

22. Click Execute.