SQL Server

Detailed information on the SQL Server state store component

Component format

To setup SQL Server state store create a component of type state.sqlserver. See this guide on how to create and apply a state store configuration.

  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> # Required.
  12.   - name: tableName
  13.     value: <REPLACE-WITH-TABLE-NAME>  # Optional. defaults to "state"
  14.   - name: keyType
  15.     value: <REPLACE-WITH-KEY-TYPE>  # Optional. defaults to "string"
  16.   - name: keyLength
  17.     value: <KEY-LENGTH> # Optional. defaults to 200. Yo be used with "string" keyType
  18.   - name: schema
  19.     value: <SCHEMA> # Optional. defaults to "dbo"
  20.   - name: indexedProperties
  21.     value: <INDEXED-PROPERTIES> # Optional. List of IndexedProperties.

Warning

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

If you wish to use SQL server as an actor state store, append the following to the yaml.

  1.   - name: actorStateStore
  2.     value: "true"

Spec metadata fields

FieldRequiredDetailsExample
connectionStringYThe connection string used to connect. If the connection string contains the database it must already exist. If the database is omitted a default database named “Dapr” is created.“Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;”
tableNameNThe name of the table to use. Alpha-numeric with underscores. Defaults to “state”“table_name”
keyTypeNThe type of key used. Defaults to “string”“string”
keyLengthNThe max length of key. Used along with “string” keytype. Defaults to “200”“200”
schemaNThe schema to use. Defaults to “dbo”“dapr”,“dbo”
indexedPropertiesNList of IndexedProperties.‘[{“column”: “transactionid”, “property”: “id”, “type”: “int”}, {“column”: “customerid”, “property”: “customer”, “type”: “nvarchar(100)”}]’
actorStateStoreNIndicates that Dapr should configure this component for the actor state store (more information).“true”

Create 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 and in Docker.

In order to setup SQL Server as a state store, you 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 does not exist
  • Table Name: The database table name. Will be created if does not exist
  • 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

Last modified June 23, 2022: Merge pull request #2550 from ItalyPaleAle/cosmosdb-harcoded-dapr-version (cf03237)