mssql.md

+++ title = "MSSQL" availability = "v2.2+" maintainer = "Microsoft" description = "Scale applications based on Microsoft SQL Server (MSSQL) query results." go_file = "mssql_scaler" +++

Trigger Specification

This specification describes the mssql trigger that scales based on the results of a Microsoft SQL Server (MSSQL) query result. This trigger supports local MSSQL containers as well as SQL Server endpoints hosted in the cloud, such as Azure SQL Database.

triggers:
- type: mssql
  metadata:
    connectionStringFromEnv: MSSQL_CONNECTION_STRING
    query: "SELECT COUNT(*) FROM backlog WHERE state='running' OR state='queued'"
    targetValue: 1
    metricName: backlog_process_count # optional - the generated value would be `mssql-{sha256hash}`

💡 NOTE: The connection string format supported by this scaler has some incompatibilities with connection string formats supported by other platforms, like .NET. For example, the MSSQL instance's port number must be separated into its own Port property instead of adding it to the Server property. You can learn more about all the supported connection string formats for this mssql scaler here.

Alternatively, you configure connection parameters explicitly instead of providing a connection string:

triggers:
- type: mssql
  metadata:
    username: "kedaUser"
    passwordFromEnv: MSSQL_PASSWORD
    host: mssqlinst.namespace.svc.cluster.local
    port: "1433" # optional
    database: test_db_name
    query: "SELECT COUNT(*) FROM backlog WHERE state='running' OR state='queued'"
    targetValue: 1
    metricName: backlog_process_count # optional - the generated value would be `mssql-test_db_name`

The mssql trigger always requires the following information:

• query - A T-SQL query that returns a single numeric value. This can be a regular query or the name of a stored procedure.
• targetValue - A threshold that is used as targetAverageValue in the Horizontal Pod Autoscaler (HPA).

Note that the query must return a single integer value. If the query has a possibility of returning null, a default value can be set using the COALESCE function. For example, SELECT COALESCE(column_name, 0) FROM table_name;. See MSSQL documentation for more information on the COALESCE function.

To connect to the MSSQL instance, you can provide either:

• connectionStringFromEnv - The name of an environment variable containing a valid MSSQL connection string.

Or provide more detailed connection parameters explicitly (a connection string will be generated for you at runtime):

• host - The hostname of the MSSQL instance endpoint.
• port - The port number of the MSSQL instance endpoint. The default for MSSQL is 1433.
• database - The name of the database to query.
• username - The username credential for connecting to the MSSQL instance.
• passwordFromEnv - The name of an environment variable containing the password credential for connecting to the MSSQL instance.

When configuring with a connection string, you can use either a URL format (note the URL encoding of special characters):

sqlserver://user1:Password%231@example.database.windows.net:1433?database=AdventureWorks

Or the more traditional ADO format:

Server=example.database.windows.net;Port=1433;Database=AdventureWorks;Persist Security Info=False;User ID=user1;Password=Password#1;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

You can also optionally assign a name to the metric using the metricName value. If not specified, the metricName will be generated automatically based on the database value (if specified), or the host value, or will be in the form mssql-{sha256hash} where {sha256hash} is a SHA-256 hash of the connection string.

Authentication parameters

As an alternative to using environment variables, you can authenticate with the MSSQL instance using connection string or password authentication via TriggerAuthentication configuration.

Connection string authentication:

• connectionString - The connection string for the MSSQL instance.

Password authentication:

• password - The password credential for connecting to the MSSQL instance.

Example

The following is an example of how to deploy a scaled object with the mssql scale trigger that uses TriggerAuthentication and a connection string.

apiVersion: v1
kind: Secret
metadata:
  name: mssql-secrets
type: Opaque
data:
  mssql-connection-string: U2VydmVyPWV4YW1wbGUuZGF0YWJhc2Uud2luZG93cy5uZXQ7cG9ydD0xNDMzO0RhdGFiYXNlPUFkdmVudHVyZVdvcmtzO1BlcnNpc3QgU2VjdXJpdHkgSW5mbz1GYWxzZTtVc2VyIElEPXVzZXIxO1Bhc3N3b3JkPVBhc3N3b3JkIzE7RW5jcnlwdD1UcnVlO1RydXN0U2VydmVyQ2VydGlmaWNhdGU9RmFsc2U7 # base64 encoded value of MSSQL connectionString of format "Server=example.database.windows.net;port=1433;Database=AdventureWorks;Persist Security Info=False;User ID=user1;Password=Password#1;Encrypt=True;TrustServerCertificate=False;"
---
apiVersion: keda.sh/v1alpha1
kind: TriggerAuthentication
metadata:
  name: keda-trigger-auth-mssql-secret
spec:
  secretTargetRef:
  - parameter: connectionString
    name: mssql-secrets
    key: mssql-connection-string
---
apiVersion: keda.sh/v1alpha1
kind: ScaledObject
metadata:
  name: mssql-scaledobject
spec:
  scaleTargetRef:
    name: consumer # e.g. the name of the resource to scale
  triggers:
  - type: mssql
    metadata:
      targetValue: 1
      query: "SELECT COUNT(*) FROM backlog WHERE state='running' OR state='queued'"
    authenticationRef:
      name: keda-trigger-auth-mssql-secret