Jump to content
  • Setting up a Spotfire database in Azure SQL database


    Table of Contents

    Setting up a Spotfire database in Azure SQL database

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

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


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...