Configure Microsoft SQL Server for SystemLink TDM

This article demonstrates how to configure a Microsoft SQL Server and databases to use for SystemLink TDM functionality. Using an external Microsoft SQL Server can improve scalability and fault tolerance.

Prerequisites

• SystemLink Server must be installed.
• The server PC must have at least one of the following modules installed:
  • NI SystemLink TDM DataFinder Module.
  • NI SystemLink TDM Data Preparation Add-On.
  • NI SystemLink TDM Analysis Add-On.

This article details the steps listed below. Refer to the relevant section, depending on your requirements.

1. Installing Microsoft SQL Server.
2. Installing Microsoft SQL Server Management Studio (SSMS).
3. Using an SQL Server for the SystemLink Resource Manager Service.
4. Using an SQL Server to Store DataFinder Indexed Data.

 

1: Installing Microsoft SQL Server

1. In a web browser, navigate to [External] SQL Server Downloads.
2. Scroll down to download the Developer or Express editions and click Download.
   • The Developer edition is recommended, but either is suitable.

 

3. Run the executable.
4. Select whichever installation type is preferable. For this tutorial, the Basic installation is used.
5. Click Accept to accept the software license terms.
6. Specify an install location. For this tutorial, the default location is used.
7. Click Install.
8. Once the installation completes, connect to the server.
   1. Click Connect Now.
   2. A Command Prompt window will appear. A line prompt showing 1> indicates that a connection has been made.
   3. Make a note of the server name, printed on the third line (similar to the below image).

 

9. Exit the installer.

 

2: Installing Microsoft SQL Server Management Studio (SSMS)

SSMS is a graphical tool that allows you to manage and configure SQL servers and databases.

1. On the same PC where Microsoft SQL Server is installed, go to [External] Download SQL Server Management Studio (SSMS).
2. Scroll to the Download SSMS section and click the download link.
3. Run the executable.
4. Once the installer completes, launch SQL Server Management Studio.
5. In the pop-up window, set the following options:
   • Server Type should be Database Engine.
   • Server Name should be set to the server name seen after installing Microsoft SQL Server.
   • Authentication should be set to Windows Authentication.

 

6. Click Connect.

 

3: Using an SQL Server for the SystemLink Resource Manager Service

Using an SQL Server for the SystemLink Resource Manager Service allows you to view and query TDM jobs. This includes information about DIAdem Workers and Jupyter Workers used to execute Data Preparation Procedures (DPPs) and Analysis Automation Procedures (ANPs).

1. Open Microsoft SQL Server Management Studio (SSMS).
2. Ensure that you have a connection to the SQL Server.
   • If a connection does not exist, select File >> Connect Object Explorer.
   • In the pop-up window, enter the same settings described in section 2: Installing Microsoft SQL Server Management Studio (SSMS).
3. Right-click the Databases folder and select New Database...
4. In Database Name, select a name to be used for the SystemLink Resource Manager.
   • Note: this database will be used to store TDM job information, not indexed file data from DataFinder Instances.
5. Click OK.

 

6. Open the NI SystemLink TDM Configuration application.
7. Expand the Other section, and select the Resource Manager Service tab.
8. Configure the following settings:
   • Enable Use SQL Server.
   • Server should be set to the server name.
   • Database name should be set to the database name just created.
9. Click Apply to save changes and restart SystemLink services.

 

10. Click OK on the pop-up confirming that the server has been restarted.
11. Open the NI SystemLink Server Configuration application and wait until all services are running.
    • If the FileIngestion service errors, refer to Error 1385 With SystemLink FileIngestion Service Using Microsoft SQL Server.
    • If the TDM_ResouceManager service errors or hangs, refer to Many of the TDM Services in SystemLink Refuse to Start.
12. In SSMS, right-click the database and select Refresh.
13. Expand the <database name> >> Tables section. The following tables will be populated:
    • dbo.batches
    • dbo.batchjobs
    • dbo.environmentjobs
    • dbo.environments
    • dbo.jobs
    • dbo.settings

4: Using an SQL Server to Store DataFinder Indexed Data

A DataFinder Instance can be configured to store indexed file data on your SQL Server.

1. Open Microsoft SQL Server Management Studio (SSMS).
2. Ensure that you have a connection to the SQL Server.
   • If a connection does not exist, select File >> Connect Object Explorer.
   • In the pop-up window, enter the same settings described in section 2: Installing Microsoft SQL Server Management Studio (SSMS).
3. Right-click the Databases folder and select New Database...
4. In Database Name, select a name to be used for a specific DataFinder Instance.
   • Note: each DataFinder Instance requires it's own database.
5. Click OK.
6. In the SystemLink web interface, open the navigation menu and select Data Administration >> Data Indexing.
7. Click DataFinder Instances.
8. From the top right-hand corner, click Global Settings. Ensure that Display SQL Server Settings is enabled.

 

9. Click New >> DataFinder Instance to create a new DataFinder Instance.
10. Enter a name for the DataFinder Instance and click Next.
11. Configure the following options:
    • Name should be the name given to the folder that stores data files.
    • Path should be the location where the data files are stored.
    • Workspace should be whichever workspace the DataFinder Instance is in.
12. Click Next.
13. Ensure that Enable MS SQL support is checked. Enter the following settings:
    • Server name should be the name of your SQL Server.
    • Database name should be the name of the database specifically created for this DataFinder Instance.
    • Use Windows security settings (SystemLink TDM User Account) should be selected.
    • User name and Password should be the same credentials used in the NI SystemLink TDM Configuration application >> SystemLink TDM User Account.

 

14. Click Test connection to ensure that the DataFinder Instance can reach the SQL Server.
15. Click Finish.
16. Double-click the new DataFinder Instance.
17. Select the DataPlugins tab and ensure that the DataPlugin for your specific file type is enabled.
    • If any settings are changed, click Apply on the banner to save changes.
18. Click the ellipses (...) on the right hand side and Start the DataFinder Instance.

 

19. After some data files have been indexed, open SSMS.
20. Right-click the DataFinder database and select Refresh.
21. Expand <Database name> >> Tables. The following tables will now be populated:
    • dbo.tContextVariables
    • dbo.tDirectory
    • dbo.tFile
    • dbo.tIndexJobs
    • dbo.tMasterInformation
    • dbo.tSearchArea
    • dbo.tStatistics
    • dbo.tTdmChannel
    • dbo.tTdmChannellA
    • dbo.tTdmChannellANames
    • dbo.tTdmFileview
    • dbo.tTdmFileviewExtension
    • dbo.tTdmFileviewNames
    • dbo.tTdmFileviewTmp
    • dbo.tTdmGroup
    • dbo.tTdmGrouplA
    • dbo.tTdmGrouplANames
    • dbo.tTdmRoot
    • dbo.tTdmRootlA
    • dbo.tTdmRootlANames
    • dbo.tTreeViews