Configuring AlwaysOn SQL using LCM

Configure the AlwaysOn SQL service for DSE Analytics using Lifecycle Manager.

AlwaysOn SQL is a high availability service that responds to SQL queries from JDBC and ODBC applications. By default, AlwaysOn SQL is disabled. It is built on top of the Spark SQL Thriftserver, but provides failover and caching between instances so there is no single point of failure. AlwaysOn SQL provides enhanced security, leveraging the same user management as the rest of DSE, executing queries to the underlying database as the user authenticated to AlwaysOn SQL.

Configure the AlwaysOn SQL service for DSE Analytics using Lifecycle Manager. Add a configuration profile and associate it with the datacenter in the Clusters workspace of LCM, then run a configure job to push the configuration to all nodes in the datacenter.

Prerequisites

Review the documentation for the feature. See Using AlwaysOn SQL service in the DataStax documentation and Introducing AlwaysOn SQL for DSE Analytics on the DataStax Developer Blog.
Important: When AlwaysOn SQL is enabled within a DSE Analytics datacenter, all nodes within the datacenter must have AlwaysOn SQL enabled.

Procedure

  1. Click Config Profiles from the Lifecycle Manager navigation menu.
  2. Click the edit icon for the configuration profile you want to edit, or click Add Config Profile to add a configuration profile.
  3. In the Config Profile pane under the Cassandra section, click dse.yaml.
  4. Scroll down to the AlwaysOn SQL Server Options panel and select the enabled option.
    The panel expands to show available configuration options.
  5. Adjust the default settings as appropriate for your environment. See AlwaysOn SQL options for complete details.
  6. LCM configuration profiles have authentication enabled by default through DSEAuthenticator and its internal scheme. To enable AlwaysOn SQL to work with this configuration:
    1. In the Config Profile pane under the Spark section, click hive-site.xml.
    2. Set the jdbc-auth-type to password for SparkSQL JDBC connections. This step assumes the configuration profile has an internal authentication scheme enabled, which is the default.
  7. If Transport Layer Security (TLS) is enabled in cassandra.yaml, enable SSL for AlwaysOn SQL:
    1. In the Config Profile pane under the Spark section, click hive-site.xml.
    2. To specify using SSL for JDBC connections, select use-ssl.
    3. Enter the same keystore path and keystore-password as those set for client-to-node encryption in cassandra.yaml. For background information, see Enabling SSL for AlwaysOn SQL.
  8. Click Save to save the configuration profile.
  9. Go to the Clusters workspace to select the configuration profile.
    1. Click the ellipsis next to the cluster, datacenter, or node where the configuration profile will be applied and click Edit.
    2. Select the Config Profile to apply and then click Save.
  10. Run a configure job to apply the configuration profile changes.
  11. Complete the steps in Using authentication with AlwaysOn SQL to create the auth_user role, grant that role sufficient permissions, and restart AlwaysOn SQL.

What's next

On the Nodes page in OpsCenter, monitor the node status. Hovering the cursor over a node where AlwaysOn SQL is enabled lists AlwaysOn SQL in the Status pane.

Search Analytics node with AlwaysOnSQL Nodes Ring View OpsCenter Monitoring