Manual Setup of Spotfire Database in Azure SQL Database (Managed instance)

Manual Setup of Spotfire Database in Azure SQL Database (Managed instance)

book

Article ID: KB0078725

calendar_today

Updated On:

Products Versions
Spotfire Server 7.11 and higher

Description

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

Issue/Introduction

This article describes the manual steps to setup a Spotfire database in a Managed instance of Azure SQL Database using Microsoft SQL Server Management Studio

Environment

Azure SQL Database (Managed instance) and Microsoft SQL Server JDBC driver (note: the DataDirect driver can not be used)

Resolution

  1. 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.
  2. Connect as Database Engine using the login for your SQL server.
  3. 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.
  4. 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).
  5. 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.
  6. Right click on the new database and choose New Query.
  7. 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) ).
  8. First, we finish off the content of create_server_user.sql with this
    CREATE USER  $(SERVERDB_USER)

    GRANT CONTROL TO  $(SERVERDB_USER)
    GO
  9. 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.
  10. 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.
  11. 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.
  12. 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.
Please refer to the TIBCO Spotfire Server manual for further instructions on how to install and configure TIBCO Spotfire Server (for TIBCO Spotfire Server 7.11.2, see https://docs.tibco.com/pub/spotfire_server/7.11.2/doc/html/TIB_sfire_server_tsas_admin_help/GUID-807FDCB0-5C34-4B0D-8635-F17B55B7DA76.html) 

Additional Information

Doc: Setting up the Spotfire database (SQL Server): Doc: (Spotfire Server) Installation and Configuration
Powered by Wolken Software