SQL Server

Detailed information on the SQL Server state store component

Create an Azure SQL instance

Follow the instructions from the Azure documentation on how to create a SQL database. The database must be created before Dapr consumes it.

Note: SQL Server state store also supports SQL Server running on VMs.

In order to setup SQL Server as a state store, you will need the following properties:

  • Connection String: the SQL Server connection string. For example: server=localhost;user id=sa;password=your-password;port=1433;database=mydatabase;
  • Schema: The database schema to use (default=dbo). Will be created if not exists
  • Table Name: The database table name. Will be created if not exists
  • Indexed Properties: Optional properties from json data which will be indexed and persisted as individual column

Create a dedicated user

When connecting with a dedicated user (not sa), these authorizations are required for the user - even when the user is owner of the desired database schema:

  • CREATE TABLE
  • CREATE TYPE

Create a Dapr component

Currently this component does not support state management for actors

The next step is to create a Dapr component for SQL Server.

Create the following YAML file named sqlserver.yaml:

  1. apiVersion: dapr.io/v1alpha1
  2. kind: Component
  3. metadata:
  4.   name: <NAME>
  5.   namespace: <NAMESPACE>
  6. spec:
  7.   type: state.sqlserver
  8.   version: v1
  9.   metadata:
  10.   - name: connectionString
  11.     value: <REPLACE-WITH-CONNECTION-STRING>
  12.   - name: tableName
  13.     value: <REPLACE-WITH-TABLE-NAME>

Warning

The above example uses secrets as plain strings. It is recommended to use a secret store for the secrets as described here.

Example

The following example uses the Kubernetes secret store to retrieve the secrets:

  1. apiVersion: dapr.io/v1alpha1
  2. kind: Component
  3. metadata:
  4.   name: <NAME>
  5.   namespace: <NAMESPACE>
  6. spec:
  7.   type: state.sqlserver
  8.   version: v1
  9.   metadata:
  10.   - name: connectionString
  11.     secretKeyRef:
  12.       name: <KUBERNETES-SECRET-NAME>
  13.       key: <KUBERNETES-SECRET-KEY>
  14.   - name: tableName
  15.     value: <REPLACE-WITH-TABLE-NAME>

Apply the configuration

In Kubernetes

To apply the SQL Server state store to Kubernetes, use the kubectl CLI:

  1. kubectl apply -f sqlserver.yaml

Running locally

To run locally, create a components dir containing the YAML file and provide the path to the dapr run command with the flag --components-path.

Last modified February 16, 2021: Merge pull request #1235 from dapr/update-v0.11 (b4e9fbb)