This state store component can be used with both Microsoft SQL Server and Azure SQL.
To set up this state store, create a component of type state.sqlserver
. See this guide on how to create and apply a state store configuration.
apiVersion: dapr.io/v1alpha1
kind: Component
metadata:
name: <NAME>
spec:
type: state.sqlserver
version: v1
metadata:
# Authenticate using SQL Server credentials
- name: connectionString
value: |
Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;
# Authenticate with Microsoft Entra ID (Azure SQL only)
# "useAzureAD" be set to "true"
- name: useAzureAD
value: true
# Connection string or URL of the Azure SQL database, optionally containing the database
- name: connectionString
value: |
sqlserver://myServerName.database.windows.net:1433?database=myDataBase
# Other optional fields (listing default values)
- name: tableName
value: "state"
- name: metadataTableName
value: "dapr_metadata"
- name: schema
value: "dbo"
- name: keyType
value: "string"
- name: keyLength
value: "200"
- name: indexedProperties
value: ""
- name: cleanupIntervalInSeconds
value: "3600"
# Uncomment this if you wish to use Microsoft SQL Server as a state store for actors (optional)
#- name: actorStateStore
# value: "true"
If you wish to use SQL server as an actor state store, append the following to the metadata:
- name: actorStateStore
value: "true"
The following metadata options are required to authenticate using SQL Server credentials. This is supported on both SQL Server and Azure SQL.
Field | Required | Details | Example |
---|---|---|---|
connectionString | Y | The connection string used to connect. If the connection string contains the database, it must already exist. Otherwise, if the database is omitted, a default database named “Dapr” is created. | "Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;" |
Authenticating with Microsoft Entra ID is supported with Azure SQL only. All authentication methods supported by Dapr can be used, including client credentials (“service principal”) and Managed Identity.
Field | Required | Details | Example |
---|---|---|---|
useAzureAD | Y | Must be set to true to enable the component to retrieve access tokens from Microsoft Entra ID. | "true" |
connectionString | Y | The connection string or URL of the Azure SQL database, without credentials. If the connection string contains the database, it must already exist. Otherwise, if the database is omitted, a default database named “Dapr” is created. | "sqlserver://myServerName.database.windows.net:1433?database=myDataBase" |
azureTenantId | N | ID of the Microsoft Entra ID tenant | "cd4b2887-304c-47e1-b4d5-65447fdd542b" |
azureClientId | N | Client ID (application ID) | "c7dd251f-811f-4ba2-a905-acd4d3f8f08b" |
azureClientSecret | N | Client secret (application password) | "Ecy3XG7zVZK3/vl/a2NSB+a1zXLa8RnMum/IgD0E" |
Field | Required | Details | Example |
---|---|---|---|
tableName | N | The name of the table to use. Alpha-numeric with underscores. Defaults to "state" | "table_name" |
metadataTableName | N | Name of the table Dapr uses to store a few metadata properties. Defaults to dapr_metadata . | "dapr_metadata" |
keyType | N | The type of key used. Supported values: "string" (default), "uuid" , "integer" . | "string" |
keyLength | N | The max length of key. Ignored if “keyType” is not string . Defaults to "200" | "200" |
schema | N | The schema to use. Defaults to "dbo" | "dapr" ,"dbo" |
indexedProperties | N | List of indexed properties, as a string containing a JSON document. | '[{"column": "transactionid", "property": "id", "type": "int"}, {"column": "customerid", "property": "customer", "type": "nvarchar(100)"}]' |
actorStateStore | N | Indicates that Dapr should configure this component for the actor state store (more information). | "true" |
cleanupIntervalInSeconds | N | Interval, in seconds, to clean up rows with an expired TTL. Default: "3600" (i.e. 1 hour). Setting this to values <=0 disables the periodic cleanup. | "1800" , "-1" |
Follow the instructions from the Azure documentation on how to create a SQL database. The database must be created before Dapr consumes it.
In order to setup SQL Server as a state store, you need the following properties:
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
This state store supports Time-To-Live (TTL) for records stored with Dapr. When storing data using Dapr, you can set the ttlInSeconds
metadata property to indicate after how many seconds the data should be considered “expired”.
Because SQL Server doesn’t have built-in support for TTLs, Dapr implements this by adding a column in the state table indicating when the data should be considered “expired”. “Expired” records are not returned to the caller, even if they’re still physically stored in the database. A background “garbage collector” periodically scans the state table for expired rows and deletes them.
You can set the interval for the deletion of expired records with the cleanupIntervalInSeconds
metadata property, which defaults to 3600 seconds (that is, 1 hour).
cleanupIntervalInSeconds
to a smaller value - for example, 300
(300 seconds, or 5 minutes).cleanupIntervalInSeconds
to a value <= 0 (e.g. 0
or -1
) to disable the periodic cleanup and reduce the load on the database.The state store does not have an index on the ExpireDate
column, which means that each clean up operation must perform a full table scan. If you intend to write to the table with a large number of records that use TTLs, you should consider creating an index on the ExpireDate
column. An index makes queries faster, but uses more storage space and slightly slows down writes.
CREATE CLUSTERED INDEX expiredate_idx ON state(ExpireDate ASC)