SQL Server

Detailed information on the SQL Server state store component

配置

To setup SQL Server state store create a component of type state.sqlserver. 请参阅本指南，了解如何创建和应用状态存储配置。

  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>  # Required.
  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.
  22.   - name: tableName
  23.     value: <REPLACE-WITH-TABLE-NAME>  # Required.
  24.   - name: keyType 
  25.     value: <REPLACE-WITH-KEY-TYPE>  # Optional. defaults to "string"
  26.   - name: keyLength
  27.     value: <KEY-LENGTH> # Optional. defaults to 200. Yo be used with "string" keyType
  28.   - name: schema
  29.     value: <SCHEMA> # Optional. defaults to "dbo"
  30.   - name: indexedProperties
  31.     value: <INDEXED-PROPERTIES> # Optional. List of IndexedProperties.
  32.   - name: tableName
  33.     value: <REPLACE-WITH-TABLE-NAME>  # Required.
  34.   - name: keyType 
  35.     value: <REPLACE-WITH-KEY-TYPE>  # Optional. defaults to "string"
  36.   - name: keyLength
  37.     value: <KEY-LENGTH> # Optional. defaults to 200. Yo be used with "string" keyType
  38.   - name: schema
  39.     value: <SCHEMA> # Optional. defaults to "dbo"
  40.   - name: indexedProperties
  41.     value: <INDEXED-PROPERTIES> # Optional. List of IndexedProperties.

Warning

以上示例将 Secret 明文存储。 更推荐的方式是使用 Secret 组件， 这里。

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

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

元数据字段规范

字段必填详情示例
connectionStringYThe connection string used to connect“Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;”
tableNameYThe name of the table to use. Alpha-numeric with underscores“table_name”
keyTypeNThe type of key used. Defaults to “string”“string”
keyLengthNThe max length of key. Used along with “string” keytype. 默认值为 “200”“200”
schemaNThe schema to use. Defaults to “dbo”“dapr”,“dbo”
indexedPropertiesNList of IndexedProperties.“[{“ColumnName”: “column”, “Property”: “property”, “Type”: “type”}]”
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. The database must be created before Dapr consumes it. 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 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

相关链接