Caching with SQL Server

In dev/docker-compose/.env set the following:

CacheType=SQL
CacheConnectionString=Data Source=sqlserver,1433;Initial Catalog=DccCache;User=sa;Password=dv4ZUD3GGsKeaMsc;Integrated Security=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

To use SQL Server you'll have to add an SQL Server image to the compose file. The image is stored in our own cloud so retrieving it requires some extra steps.

Pull the SQL Server image:
- Run the batch script /dev/PullSqlServerImage.bat by double clicking it.
- A browser tab will open asking you to log in to Azure. Do that.
- Wait for the script to finish (it will take some time).
Prepare storage by creating an Sql subfolder in DockerPersistence

Add the sql image, by adding the following section under services to dev\docker-compose\docker-compose.yml:

  sqlserver:
    # pulling this may require auth: 1. install 'azure CLI' if missing 2. run 'az login' and login via browser/popup 3. run 'az acr login --name amcsmainprdcr'
    image: amcsdevopsdevcr.azurecr.io/sql-server:2019-win2019-1.0.0
    ports:
      - "1431:1433"
    environment:
      - SA_PASSWORD=dv4ZUD3GGsKeaMsc
      - ACCEPT_EULA=Y
      - MSSQL_PID=Developer
      - 'attach_dbs=[{dbFiles: "c:\\databases\\DccCache.mdf", dbName: "DccCache"}]'
    volumes:
      - ../../DockerPersistence/Sql:c:\databases
    networks:
      - $Network
    healthcheck:
      test: ["CMD", "sqlcmd", "-q", "SELECT 1"]
      interval: 30s
      timeout: 10s
      retries: 5

Optionally add a dependency so the components doesn't start until the sql server is running. The easiest is to add it to the the dapr components under the x-services:dapr-service:depends_onsection:

    depends_on:
      rabbitmq:
        condition: service_healthy
      redis:
        condition: service_healthy
      sqlserver:
        condition: service_healthy

Initialize the cache database:
- In VS open the "dev" folder in solution explorer, right click the docker-compose project and choose "Set as Startup Project", then press Ctrl+F5 to start without debugging. This will build and start a number of containers, including the one containing the database.
- Open Docker Desktop. In "Containers" there should be an "globaldcc-compose" entry. Unfold it and wait for the sqlserver container to have status running.
- Open SQL Server Management Studio (SSMS). When asked which server to connect to, choose
  - Server type: Database Engine
  - Server name: 127.0.0.1,1431
  - Authentication: SQL Server Authentication
  - Login: sa
  - Password: dv4ZUD3GGsKeaMsc
- In SSMS open "Databases". This folder should be empty except for a couple of standard subfolders.
- In VS open the "tools" folder in solution explorer, right click the DccCache.DbUp project and choose "Set as Startup Project", then press F5 to run DbUp. This should create the database and the needed tables.
- In SSMS right click "Databases" and click "Refresh". There should now be a DccCache database.
- In Docker Desktop stop globaldcc-compose by clicking the stop button.

Optional: Removing Redis

If you switch the state to using Azure Storage and the Cache to using SQL Server you can remove the Redis dependency from your image:

Delete dev\dapr\components\state.yml
Delete the services:redis section in dev\docker-compose\docker-compose.yml
Delete the redis dependency from the x-services:dapr-service:depends_on section in dev\docker-compose\docker-compose.yml
Delete the redis entry from the volumes section in dev\docker-compose\docker-compose.yml

Optional: Removing Redis​

Optional: Removing Redis