Date Posted:
Product: TIBCO Spotfire®
Product: TIBCO Spotfire®
Problem:
Manual Setup of Spotfire Database in Azure SQL Database (Managed instance)
Solution:
Azure SQL Database does not support the "USE" command so the standard TIBCO Spotfire database installation scripts for Microsoft SQL Server cannot be used as is.
This is a description of how to use the content of those scripts to perform the database installation on Azure SQL Database using Microsoft SQL Server Management Studio (SSMS).
The article assumes that you are already familiar with the basics of setting up a TIBCO Spotfire Server database on Microsoft SQL Server, as e.g. documented below for TIBCO Spotfire Server 7.11.2:
https://docs.tibco.com/pub/spotfire_server/7.11.2/doc/html/TIB_sfire_server_tsas_admin_help/GUID-E7C33647-3F22-4C7F-8587-44B4AB5169E7.html
- Start by creating an instance in Azure SQL Database so you can connect with Microsoft SQL Server Management Studio (SSMS), including generating a firewall rule if connection from outside.
- Connect as Database Engine using the login for your SQL server.
- Right-click on the connection and choose New Query, this should give a window with the context "master" as the top login on the server. This is what we need to start.
- Now we need to combine the initial steps of the two TIBCO Spotfire database installation scripts (included with the TIBCO Spotfire Server installer, in the scripts directory) create_server_user.sql and create_server_db.sql. The goal is to create the database for Spotfire and the login it should use. The variables used here are the ones for the scripts, but you have to replace them with your actual values (the ones you normally place in create_database.bat when setting up a Spotfire server database on SQL Server, the $(VALUE) should be your_actual_value, no dollar sign and no parentheses):
CREATE DATABASE $(SERVERDB_NAME)
GO
CREATE LOGIN $(SERVERDB_USER) WITH PASSWORD=N'$(SERVERDB_PASSWORD)'
GO
ALTER LOGIN $(SERVERDB_USER) ENABLE
GO
The extra options on CREATE LOGIN that are in the scripts are not supported in Azure SQL Database and that also applies to the DENY VIEW (so it's not performed). - Now we have the database and the login, so we can now shift context to the newly created database and continue. This can be done in SSMS by refreshing the Databases in the Object Explorer, the newly created database should be visible.
- Right click on the new database and choose New Query.
- This should create a window showing the new database are the context (not .master at the end), this is what we need (to be able to use the script content after use $(SERVERDB_NAME) ).
- First, we finish off the content of create_server_user.sql with this
CREATE USER $(SERVERDB_USER)
GRANT CONTROL TO $(SERVERDB_USER)
GO - Then we can move on to create_server_db.sql and use all the content after "go" after "use $(SERVERDB_NAME)", the line "create table "SN_VERSION" ( " and onwards.
- After this we can use all content in populate_server_db.sql after "go" after "use $(SERVERDB_NAME)", the line "insert into SN_VERSION..." and onwards.
- If all this SQL has executed without issues, you should now have a working Spotfire Database in Azure SQL Database, which you can use with your TIBCO Spotfire Server.
- Do note that when creating the Bootstrap, you have to use the Microsoft SQL Server JDBC driver, as the DataDirect driver the TIBCO Spotfire Server ships with does not yet work with Azure.