Database Replacement

To prepare for a production environment, replace the PostgreSQL* database with that of another provider.

To replace the database, update these services:

• MPS
• RPS

What You'll Do

This guide focuses on updating the RPS with an MS SQL Server (MSSQL) relational database.

Here are the main tasks:

• Review DB Schema
• Add DB Client Dependency
• Update Configuration
• Implement the Code

Database Recipe

The example implementation below provides a step-by-step outline of database deployment. However, it is intended as a general guideline. You will need to write specific source code to support your custom solution.

Review DB Schema

The diagrams below illustrates the database schema and relationships.

RPS

erDiagram
    DOMAIN {
        string name
        string domain_suffix
        string provisioning_cert
        string provisioning_cert_storage_format
        string provisioning_cert_key
        datetime creation_date
        string created_by
        string tenant_id
    }

erDiagram
    PROFILE o|--o| CIRACONFIGS : has
    PROFILE ||--|{ PROFILES_WIRELESSCONFIGS : associated
    PROFILE ||--o| IEEE8021XCONFIGS : has
    PROFILE {
      string profile_name
      string activation
      string amt_password
      boolean generate_random_password
      string cira_config_name
      datetime creation_date
      string created_by
      string mebx_password
      boolean generate_random_mebx_password
      string[] tags
      boolean dhcp_enabled
      string tenant_id
      int tls_mode
      string user_consent
      boolean ider_enabled
      boolean kvm_enabled
      boolean sol_enabled
      string tls_signing_authority
      string ieee8021x_profile_name
    }
    CIRACONFIGS 
    CIRACONFIGS {
      string cira_config_name
      string mps_server_address
      int mps_port
      string user_name
      string password
      string common_name
      int server_address_format
      int auth_method
      string mps_root_certificate
      string proxydetails
      string tenant_id
    }

    WIRELESSCONFIGS ||--|{ PROFILES_WIRELESSCONFIGS : belongs
    WIRELESSCONFIGS ||--o| IEEE8021XCONFIGS : has
    WIRELESSCONFIGS {
      string wireless_profile_name
      int authentication_method
      int encryption_method
      string ssid
      int psk_value
      string psk_passphrase
      int[] link_policy
      datetime creation_date
      string created_by
      string tenant_id
      string ieee8021x_profile_name
    }
    PROFILES_WIRELESSCONFIGS {
      string wireless_profile_name
      string profile_name
      int priority
      datetime creation_date
      string created_by
      string tenant_id
    }
    IEEE8021XCONFIGS {
      string profile_name
      int auth_protocol
      string servername
      string domain
      string username
      string password
      string roaming_identity
      boolean active_in_s0
      int pxe_timeout
      boolean wired_interface
      string tenant_id
    }

MPS

erDiagram
    DEVICE {
        guid uuid
        string[] tags
        string hostname
        string mpsinstance
        boolean connectionstatus
        string mpsusername
        string tenantid
        string friendlyname
        string dnssuffix
    }

Add DB Client

Add the database client library you will use to connect to your database. To support MSSQL, this example uses the Microsoft SQL Server client* for Node.js, node-mssql.

To add the database:

Open a Terminal or Command Prompt and navigate to a directory of your choice for development:

npm install node-mssql --save

Update Configuration

Update the connection string and a folder name for your db either in your ENV or .rc file.

To modify the configuration:

"db_provider":"mssql", //This will be the name of the folder you create in the next section.
"connection_string":"Server=localhost,1433;Database=database;User Id=username;Password=password;Encrypt=true'",

Implement the Code

To support the new database:

1. Create a new folder in ./src/data. The name of the new folder should be the name you supplied for the db_provider property, which is mssql in the example above.

   

   Figure 2: New folder mssql

2. Create a file called index.ts that implements our IDB interface. Below is an example interface and query method:

   Interface

   export interface IDB {
     ciraConfigs: ICiraConfigTable
     domains: IDomainsTable
     profiles: IProfilesTable
     wirelessProfiles: IWirelessProfilesTable
     profileWirelessConfigs: IProfilesWifiConfigsTable
     query: (text: string, params?: any) => Promise<any>
   }
   

   Query Method

   This query function is responsible for taking in the query parameters and performing the execution.

   async query <T>(text: string, params?: any): Promise<mssql.IResult<T>> {
      let result
      const start = Date.now()
      return await new Promise((resolve, reject) => {
        this.sqlPool.connect(async (err) => {
          if (err) {
            this.log.error(err)
            reject(err)
          }
          result = await this.sqlPool.request().query(text)
          const duration = Date.now() - start
          this.log.verbose(`executed query: ${JSON.stringify({ text, duration, rows: result.recordset.length })}`)
          resolve(result)
          })
      })
   }
   

3. Implement each of the table interfaces. The base interface looks like this:

   export interface ITable<T> {
     getCount: (tenantId?: string) => Promise<number>
     get: (limit: number, offset: number, tenantId?: string) => Promise<T[]>
     getByName: (name: string, tenantId?: string) => Promise<T>
     delete: (name: string, tenantId?: string) => Promise<boolean>
     insert: (item: T) => Promise<T>
     update: (item: T) => Promise<T>
   }
   

   There are interfaces for each table in the ./interfaces/database which adds specific functions on top of the base ITable<> interface.

   Here's an example of the get implementation for Domains:

   /**
    * @description Get all Domains from DB
    * @param {number} top
    * @param {number} skip
    * @returns {AMTDomain[]} returns an array of AMT Domain objects from DB
    */
   async get (top: number = DEFAULT_TOP, skip: number = DEFAULT_SKIP, tenantId: string = ''): Promise<AMTDomain[]> {
      const results = await this.db.query(`
      SELECT name as  profileName, domain_suffix as  domainSuffix, provisioning_cert as  provisioningCert, provisioning_cert_storage_format as  provisioningCertStorageFormat, provisioning_cert_key as  provisioningCertPassword, tenant_id tenantId
      FROM domains 
     ORDER BY name`)
     return result
    }
   

4. Complete all the queries for each table's functions to finish the implementation.

Best Practice

That's it! Deployment complete.

After replacing the database, ensure all the APIs are working as expected by running the API Tests with the Postman* application. You'll find the tests in the ./src/test/collections folder.