[SOLVED] Dapr and using sql statestore instead of redis

Dapr and using sql statestore instead of redis

We would like to test switching from redis to sql statestore component.

From the docs it looks like we can do this with two small modifications:

apiVersion: dapr.io/v1alpha1
kind: Component
metadata:
  name: statestore
spec:
  type: state.sqlserver  # Switch from redis
  version: v1
  metadata:
  - name: connectionString
    value: Server=db;Database=dapr;user id=sa;Password=Your_password123;
  - name: tableName
    value: dapr

And in our docker compose file, we added an sql server

  db: # added 
    image: "mcr.microsoft.com/mssql/server"
    ports:
      - "1433:1433"
    environment:
      SA_PASSWORD: "Your_password123"
      ACCEPT_EULA: "Y"
    networks:
      - smi-network

I assumed that a new table named dapr would be created in the database dapr. But instead we get the follwing error:

level=warning msg="error initializing state store statestore (state.sqlserver/v1): failed to create db schema: Unable to open tcp connection with host 'db:1433': dial tcp 172.29.0.2:1433: connect: connection refused" app_id=daprbackend

What have I missed, I assumed since all are on the same network smi-network the database would be callable by db (if I log into the shell of db, I see that the IP address is the same 172.29.0.2 )?

Solution

Looking at the code the database is not created for you. Only the table. So if your connection string includes a database you have to have created it first.

If you don't include a database in your connection string the master database will be use and table created there.

I am submitting a pull request now to fix this. If the login you use has permissions the database will be created for you.