Table of contents
TOC
Collapse the table of content
Expand the table of content

Configuring an SQL Server or PostgreSQL Database

j-martens|Last Updated: 1/11/2017
|
1 Contributor

Applies to: Microsoft R Server 9.0.1

The operationalization feature for R Server installs and uses a local SQLite database by default to store R session information, web service definitions, snapshots, and batch execution tracking information. Later, you can update the configuration to use another database locally or remotely. This is particularly useful when you want to use a remote database or when you have multiple web nodes.

The database provides internal storage for the sessions, web services, snapshots and other entities created as a result of operationalization. When a request comes in to a web node (for example, to consume a service), the web node connects to the databases, retrieves parameters for the service, and then sends the information to a compute node for execution.

Consider the size of the machine hosting this database carefully to ensure that database performance does not degrade overall performance and throughput.

This feature uses a SQLite 3.7+ database by default, but can be configured to use:

  • On Windows: SQL Server Professional, Standard, or Express Version 2008 or greater
  • On Linux: PostgreSQL 9.2 or greater
Important

Any data that was saved in the default local SQLite database will be lost if you configure a different database.

To use a different local or remote database, do the following:

These steps assume that you have already set up SQL Server or PostgreSQL as described for that product.

Create this database and register it in the configuration file below BEFORE the service for the control node is started.

  1. On each web node, stop the service.

  2. Update the database properties to point to the new database as follows:

    1. Open the external configuration file, appsettings.json file.

      • On Windows, this file is under <MRS_home>\deployr\Microsoft.DeployR.Server.WebAPI\ where <MRS_home> is the path to the Microsoft R Server installation directory. To find this path, enter normalizePath(R.home()) in your R console.

      • On Linux, this file is under /usr/lib64/microsoft-deployr/9.0.1/Microsoft.DeployR.Server.WebAPI/.

    2. Locate the ConnectionStrings property block.

    3. Within that property block, locate the type of database you want to set up.

      • For SQL Server, look for "sqlserver": {.

      • For SQL Server, look for "postgresql": {.

    4. In the appropriate database section, enable that database type by adding the property "Enabled": true,. For example:

       "ConnectionStrings": {
               "sqlserver": {
                   "Enabled": true,
                   ...
      },
      
    5. Add the connection string.

      For SQL Server Database (Integrated Security), use your string properties that are similar to:

      "Connection":  "Data Source=<DB-SERVER-IP-OR-FQDN>\\<INSTANCE-NAME>;Initial Catalog=<DB-NAME>;Integrated Security=True;"
      

      For SQL Server Database (SQL authentication), use your string properties which are similar to:

      "Connection":  "Data Source=<DB-SERVER-IP-OR-FQDN>\\<INSTANCE-NAME>;Initial Catalog=<DB-NAME>; Integrated Security=False; User Id=<USER-ID>;Password=<PASSWORD>;"
      

      For PostgreSQL Database, use your string properties which will:

      "Connection":  "User ID=<DB-USERNAME>;Password=<USER-PASSWORD>;Host=<DB-SERVER-IP-OR-FQDN>;Port=5432;Database=<DB-NAME>;Pooling=true;"
      
    6. For better security, we recommend you encrypt the connection string for this database before adding the information to appsettings.json.

      1. Use the administration utility to encrypt the connection string.

      2. Copy the encrypted string returned by the administration utility into "ConnectionStrings": { property block and set "Encrypted": to true. For example:

       "ConnectionStrings": {
               "sqlserver": {
                   "Enabled": true,
                   "Encrypted": true,
                   "Connection": "eyJ0IjoiNzJFNDg5QUQ1RDQ4MEM1NURCMDRDMjM1MkQ1OTVEQ0I2RkQzQzE3QiIsInMiOiJFWkNhNUdJMUNSRFV0bXZHVEIxcmNRcmxXTE9QM2ZTOGtTWFVTRk5QSk9vVXRWVzRSTlh1THcvcDd0bCtQdFN3QVRFRjUvL2ZJMjB4K2xTME00VHRKZDdkcUhKb294aENOQURyZFY1KzZ0bUgzWG1TOWNVUkdwdjl3TGdTaUQ0Z0tUV0QrUDNZdEVMMCtrOStzdHB"
               },
               ...
      },
      
    7. Save the changes you've made to appsettings.json.

  3. Open the database port on the remote machine to the public IP of each web node as described in these articles: SQL Server | PostgreSQL

  4. Launch the administrator's utility and:

    1. Restart the web node and the database is created upon restart.

    2. Run the diagnostic tests to ensure the connection can be made to your new database.

© 2017 Microsoft