Setting up Spotfire Database in Azure SQL Database

Click for more info on: Azure SQL Database.

Part 1 - Prerequisites

• An Azure subscription and an existing resource group and virtual network for Spotfire resources.
• A Spotfire Server installation kit (you need the database scripts folder)
• You have downloaded and installed SQL Server Management Studio on your laptop or on a server within the Azure virtual network for Spotfire.

Part 1 - Instructions

Create a new Azure SQL Database

1. Select your Resource group, and then click Add.
2. From the resulting list, select Databases and click SQL Database.
3. Under the Basics tab, within the Project Details, make sure your server is created under the correct Subscription and Resource group.
4. Under Database details, provide a Database name.
5. For Server, click Create new.
6. Provide the server details. Make sure you select the correct entry under Location. Click OK. (image 1)
7. Click Review + create, review the settings and click Create. (image 2)
8. The deployment starts and the message "Your deployment is underway" is displayed. After some seconds, Deployment succeeded is displayed and you can click on Go to resource.

Image 1

image 2

Configure database connection security

1. From the created database resource, click Set server firewall. 

   Example:

   

2. Set Minimal TLS Version to 1.2.
3. Under Virtual networks, click Add existing virtual network, and then select the vnet that you created as part of your resource group. Click OK.

   Example:

   

4. Within Firewall rules, add a rule, if needed, for the client where you have the Spotfire database configuration scripts and installed SQL Server Management Studio.
5. Click Save to apply changes

Configure the Spotfire database

1. From the Windows Start menu, start SQL Server Management Studio and connect to the Azure SQL server you created. For example:

   

Now we continue with instructions on part 2.

Note the hostname and Identifier to use during the bootstrap configuration steps described.

Part 2 - Prerequisites

• You have access to a SQL database on Microsoft Azure.
• 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 web site and extracted the files.

Important: Use the Microsoft JDBC Driver for SQL Server for the database connection (not the DataDirect driver).

Part 2 - Instructions

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

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

   If SSMS cannot connect because of firewall rules, a New Firewall Rule message is displayed.

3. If you are not signed in to Azure, a Sign In button is available. Click it and sign in to Azure.

4. Click OK in the New Firewall Rule dialog.

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

   A blank query pane opens.

6. Create the Spotfire database by entering the following text in the query pane, replacing SERVERDB_NAME with the name of the Spotfire database that will be created:

    CREATE DATABASE SERVERDB_NAME  GO

7. Click Execute.

   Result: "Commands completed successfully" appears in the Messages pane.

   Note: This result text should appear each time you click Execute in SSMS.

8. In the query pane, replace the previous command with the following text, replacing SERVERDB_USER with the name of the user that will be created 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

9. Click Execute.

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

11. Click Execute.

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

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

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

15. Click Execute.

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

17. Click Execute.

18. Open the <Spotfire server installation kit dir>\scripts\mssql_install\create_server_db.sql file in a text editor.

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

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

     create table "SN_VERSION" (

21. Click Execute.

22. Open the <Spotfire Server installation kit dir>\scripts\mssql_install\populate_server_db.sql file in a text editor.

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

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

     insert into SN_VERSION (SPOTFIRE_VERSION, SCHEMA_VERSION) values ('51.0', '353');

25. Click Execute.

Result

You now have a working Spotfire database in your Azure SQL database.

To view the JDBC URL for connecting the Spotfire Server to this database:

1. In Azure, go to the Overview page of your database.

2. In the upper-right area of the page, click Show database connection strings.

The URL will resemble this:

Server=tcp:Example_SQLServerName.database.windows.net,1433;Initial Catalog=Example_DBName;Persist Security Info=False;User ID=(your_username);Password=(your_password);MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

To enter the database connection information on the Bootstrap page of the Spotfire Server configuration tool

Use the information from the JDBC URL:

• In the Hostname field of the Bootstrap page, enter the text that appears in place of 'Example_SQLServerName'.

• In the Identifier field, enter the text that appears in place of 'Example_DBName'.