### Example SQL Connection String for DTFx

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/architecture.md

This example demonstrates a SQL connection string configured to authenticate with a SQL database using previously created limited user credentials. The connection string should include the {login_name} and {pw} for authentication.

```text
Server=localhost;Initial Catalog=DurableDB;User ID={login_name};Password={pw};...
```

--------------------------------

### Docker SQL Server Setup

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

This PowerShell script pulls a SQL Server Docker image, runs it with specified parameters, and creates a database with a strict binary collation. Ensure Docker is installed and running.

```powershell
# primary parameters
$pw        = "yourStrong(!)Password"
$edition   = "Developer"
$port      = 1433
$tag       = "2019-latest"
$dbname    = "DurableDB"
$collation = "Latin1_General_100_BIN2_UTF8"

# pull the image from the Microsoft container registry
docker pull mcr.microsoft.com/mssql/server:$tag

# run the image, providing some basic setup parameters
docker run --name mssql-server \
    -e 'ACCEPT_EULA=Y' \
    -e "SA_PASSWORD=$pw" \
    -e "MSSQL_PID=$edition" \
    -p ${port}:1433 \
    -d mcr.microsoft.com/mssql/server:$tag

# wait a few seconds for the container to start...

# create the database with strict binary collation
docker exec -d mssql-server /opt/mssql-tools18/bin/sqlcmd \
    -S . \
    -U sa \
    -P "$pw" \
    -Q "CREATE DATABASE [$dbname] COLLATE $collation"
```

--------------------------------

### Initialize SqlOrchestrationService and Task Hub

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

Initialize the SqlOrchestrationService with connection settings and configure a TaskHubWorker and TaskHubClient. This example includes basic console logging setup.

```csharp
var settings = new SqlOrchestrationServiceSettings
{
    ConnectionString = "Server=localhost;Database=DurableDB;...",
};

// This console logging requires Microsoft.Extensions.Logging.Console v5.0.0 or greater
ILoggerFactory loggerFactory = LoggerFactory.Create(builder =>
{
    builder.AddSimpleConsole(options => options.SingleLine = true);
});

var provider = new SqlOrchestrationService(settings);

// Install the DB schema, if necessary
await provider.CreateIfNotExistsAsync();

var worker = await new TaskHubWorker(provider, loggerFactory);
worker.AddTaskOrchestrations(/* ... */);
worker.AddTaskActivities(/* ... */);
await worker.StartAsync();

var client = new TaskHubClient(provider, loggerFactory: loggerFactory);
await client.CreateOrchestrationInstanceAsync(/* ... */);
```

--------------------------------

### Azure CLI for Database Creation

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

These Azure CLI commands set up variables for your SQL server and database, then create the necessary resources in Azure. Ensure you have the Azure CLI installed and are logged in.

```powershell
# Set variables for your server and database
$resourceGroupName = "myResourceGroup"
$location = "eastus"
$adminLogin = "myadminuser"
$password = "myStrong(!)Password"
$serverName = "mysqlserver123"
$databaseName = "DurableDB"
```

--------------------------------

### Add Durable SQL Provider Package

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

Use the dotnet CLI to add the Microsoft.DurableTask.SqlServer package to your project. This command installs the prerelease version.

```bash
dotnet add package Microsoft.DurableTask.SqlServer --prerelease
```

--------------------------------

### Send HTTP POST Request to Start Orchestrations

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Send an HTTP POST request to the 'StartManySequences' API endpoint to initiate multiple 'hello cities' orchestrations in parallel. This is used for performance testing.

```http
POST http://52.137.109.117/api/startmanysequences?count=100
```

--------------------------------

### Create Azure SQL Server and Database using Azure CLI

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

Use these Azure CLI commands to create a resource group, SQL server, and a SQL database. Ensure you have the Azure CLI installed and are logged in.

```bash
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

echo "Resource group name is $resourceGroupName"
echo "Server name is $serverName"

# Create an Azure resource group for your database
az group create --name $resourceGroupName --location $location

# Create a SQL server and configure admin credentials
az sql server create \
    --name $serverName \
    --resource-group $resourceGroupName \
    --location $location  \
    --admin-user $adminlogin \
    --admin-password $password

# Configure client IP address restrictions for the SQL server
az sql server firewall-rule create \
    --resource-group $resourceGroupName \
    --server $serverName \
    -n AllowYourIp \
    --start-ip-address $startip \
    --end-ip-address $endip

# Finally, create the database itself
az sql db create \
    --resource-group $resourceGroupName \
    --server $serverName \
    --name $databaseName \
    --collation Latin1_General_100_BIN2_UTF8 \
    --edition GeneralPurpose \
    --compute-model Serverless \
    --family Gen5 \
    --capacity 2
```

--------------------------------

### Install KEDA on AKS Cluster

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Install KEDA (Kubernetes Event-driven Autoscaling) version 2.2 or greater on your AKS cluster using the Azure Functions Core Tools. This is required for the Durable Task SQL provider's MSSQL scaler.

```powershell
# install KEDA on this AKS cluster
func kubernetes install --namespace keda

```

--------------------------------

### Deploy MSSQL Database to Kubernetes

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Use this YAML to deploy a Microsoft SQL Server instance within your Kubernetes cluster. Ensure you replace 'PLACEHOLDER' with a secure password. Persistent storage is not configured in this example, meaning data will be lost on container restart.

```yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mssql-deployment
  labels:
    app: mssql
spec:
  replicas: 1
  selector:
     matchLabels:
       app: mssql
  template:
    metadata:
      labels:
        app: mssql
    spec:
      terminationGracePeriodSeconds: 30
      hostname: mssqlinst
      containers:
      - name: mssql
        image: mcr.microsoft.com/mssql/server:2019-latest
        ports:
        - containerPort: 1433
        env:
        - name: MSSQL_PID
          value: "Developer"
        - name: ACCEPT_EULA
          value: "Y"
        - name: SA_PASSWORD
          value: "PLACEHOLDER" # replace PLACEHOLDER With a password
---
apiVersion: v1
kind: Service
metadata:
  name: mssqlinst
spec:
  type: LoadBalancer # ClusterIP may be more appropriate in production
  selector:
    app: mssql
  ports:
    - protocol: TCP
      port: 1433
      targetPort: 1433

```

--------------------------------

### Configure Concurrency Settings in .NET

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/scaling.md

Configure the maximum number of concurrent activity tasks and orchestrator events for in-process .NET apps using `SqlOrchestrationServiceSettings`. This example sets concurrency to the number of processor cores.

```csharp
var settings = new SqlOrchestrationServiceSettings
{
    MaxConcurrentActivities = Environment.ProcessorCount,
    MaxActiveOrchestrations = Environment.ProcessorCount,
};

var service = new SqlOrchestrationService(settings);
var worker = new TaskHubWorker(service);
```

--------------------------------

### Configure Concurrency Settings in Azure Functions host.json

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/scaling.md

Configure concurrency settings for Azure Functions by setting `maxConcurrentOrchestratorFunctions` and `maxConcurrentActivityFunctions` in the `host.json` file. This example sets both to 8 and specifies the SQL storage provider.

```json
{
  "version": "2.0",
  "extensions": {
    "durableTask": {
      "maxConcurrentOrchestratorFunctions": 8,
      "maxConcurrentActivityFunctions": 8,
      "storageProvider": {
        "type": "mssql",
        "connectionStringName": "SQLDB_Connection"
      }
    }
  }
}
```

--------------------------------

### Configure Local SQL Connection String

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

Set up the local SQL connection string in your local.settings.json file for local development.

```json
{
  "IsEncrypted": false,
  "Values": {
    "SQLDB_Connection": "Server=localhost;Database=DurableDB;..."
  }
}
```

--------------------------------

### Build and Push Docker Image

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Build a Docker image for the performance testing app and push it to a Docker repository. Ensure the `$repo` variable is set to your Docker repository name.

```powershell
$repo = "<your-repo-name>"
docker build -t $repo/mssql-durable-functions:latest -f ./test/PerformanceTests/Dockerfile .
docker push $repo/mssql-durable-functions:latest
```

--------------------------------

### Create Database Login and User for DTFx

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/architecture.md

These T-SQL commands illustrate the process of creating new low-privilege database credentials and a user account specifically for authenticating DTFx applications. Ensure these new users are added only to the 'dt_runtime' role for restricted access.

```sql
-- create the new database login credentials
CREATE LOGIN {login_name} WITH PASSWORD = {pw}
GO

-- create a user account associated with the new login credentials
CREATE USER {username} FOR LOGIN {login_name}
GO

-- add the user to the restricted dt_runtime role
ALTER ROLE dt_runtime ADD MEMBER {username}
GO
```

--------------------------------

### Pseudocode for Worker Auto-scale Calculation

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/scaling.md

This pseudocode illustrates the logic for calculating the recommended number of worker instances based on active and pending tasks and configured concurrency limits.

```pseudocode
live_activities = rowcount(dt.Activities)
live_orchestrators = rowcount(dt.Instances WHERE #events > 0)
recommended_activity_workers = ceil(live_activities / max_concurrent_activities)
recommended_orchestrator_workers = ceil(live_orchestrators / max_concurrent_orchestrators)
recommended_worker_count = recommended_activity_workers + recommended_orchestrator_workers
```

--------------------------------

### Create DurableDB Database using sqlcmd

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Execute these PowerShell commands to create a new database named 'DurableDB' after the MSSQL container is running. Replace 'PLACEHOLDER' with the SA password used during deployment. This command uses kubectl exec to run sqlcmd inside the MSSQL pod.

```powershell
# Get the name of the Pod running SQL Server
$mssqlPod = kubectl get pods -n mssql -o jsonpath='{.items[0].metadata.name}'

# Use sqlcmd.exe to create a database named "DurableDB".
# Replace 'PLACEHOLDER' with the password you used earlier
kubectl exec -n mssql $mssqlPod -- /opt/mssql-tools18/bin/sqlcmd -S . -U sa -P "PLACEHOLDER" -Q "CREATE DATABASE [DurableDB] COLLATE Latin1_General_100_BIN2_UTF8"

```

--------------------------------

### SQL Statements for Managing User Credentials and Roles

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/multitenancy.md

These SQL statements are used to create new login credentials, associate them with user accounts, and add users to the restricted runtime role for multitenant environments. This ensures each tenant can only access their own data.

```sql
-- create the new login credentials
CREATE LOGIN {login_name} WITH PASSWORD = {pw}
GO

-- create a user account associated with the new login credentials
CREATE USER {username} FOR LOGIN {login_name}
GO

-- add the user to the restricted dt_runtime role
ALTER ROLE {schema_name}_runtime ADD MEMBER {username}
GO
```

--------------------------------

### Configure Durable Task MSSQL Provider in host.json

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

Configure the Durable Functions extension to use the MSSQL storage provider by specifying the connection string and other settings in host.json.

```json
{
  "version": "2.0",
  "extensions": {
    "durableTask": {
      "storageProvider": {
        "type": "mssql",
        "connectionStringName": "SQLDB_Connection",
        "taskEventLockTimeout": "00:02:00",
        "createDatabaseIfNotExists": true,
        "schemaName": "dt"
      }
    }
  }
}
```

--------------------------------

### Enable Diagnostic Logging for Durable Task MSSQL

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

Configure logging levels for Durable Task MSSQL and Core to monitor runtime performance and potential issues.

```json
{
  "logging": {
    "logLevel": {
      "DurableTask.SqlServer": "Information",
      "DurableTask.Core": "Warning"
    }
  }
}
```

--------------------------------

### Apply MSSQL Deployment YAML

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Apply the MSSQL deployment configuration to your Kubernetes cluster using kubectl. This command assumes the YAML is saved as 'mssql-deployment.yml' and targets the 'mssql' namespace.

```powershell
kubectl apply -f ./mssql-deployment.yml -n mssql

```

--------------------------------

### Deploy Azure Functions App to Kubernetes

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Deploy the Azure Functions app to your Kubernetes cluster using the 'func kubernetes deploy' command. This command uses the specified Docker image and Kubernetes secret.

```powershell
$deploymentName = "durabletask-mssql-app"
func kubernetes deploy --name $deploymentName --image-name "$repo/mssql-durable-functions:latest" --secret-name "mssql-secrets" --max-replicas 5
```

--------------------------------

### Add Durable Task MSSQL NuGet Package (.NET InProc)

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

Use this command to add the required NuGet package for .NET InProc Azure Functions projects.

```bash
dotnet add package Microsoft.DurableTask.SqlServer.AzureFunctions
```

--------------------------------

### Add Durable Task MSSQL NuGet Package (.NET Isolated)

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

Use this command to add the required NuGet package for .NET Isolated Azure Functions projects.

```bash
dotnet add package Microsoft.Azure.Functions.Worker.Extensions.DurableTask.SqlServer
```

--------------------------------

### Configure Task Hub Name in DTFx SqlOrchestrationServiceSettings

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/taskhubs.md

For self-hosted Durable Task Framework (DTFx) applications that opt out of multitenant mode, configure the task hub name and connection string directly within the SqlOrchestrationServiceSettings class.

```csharp
var settings = new SqlOrchestrationServiceSettings
{
    TaskHubName = "MyTaskHub",
    TaskHubConnectionString = Environment.GetEnvironmentVariable("SQLDB_Connection"),
};

```

--------------------------------

### Configure Custom Schema in Self-Hosted DTFx

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/multitenancy.md

For self-hosted Durable Task Framework applications, configure the custom schema name directly within the SqlOrchestrationServiceSettings constructor. Ensure the connection string is correctly provided.

```csharp
var settings = new SqlOrchestrationServiceSettings(
    connectionString: Environment.GetEnvironmentVariable("SQLDB_Connection"),
    schemaName: "MyCustomSchemaName");
```

--------------------------------

### Deploy Kubernetes Secret

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Apply the Kubernetes secret definition to your cluster using kubectl. Ensure the YAML file is named 'mssql-secrets.yml'.

```powershell
kubectl apply -f ./mssql-secrets.yml
```

--------------------------------

### Enable Shared Schema Multitenancy in SQL

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/multitenancy.md

Use this T-SQL command to re-enable shared schema multitenancy. This is the default mode where the task hub is inferred from the SQL login username.

```sql
-- Enable multi-tenancy mode
EXECUTE dt.SetGlobalSetting @Name='TaskHubMode', @Value=1
```

--------------------------------

### KEDA ScaledObject for MSSQL Auto-scaling

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/scaling.md

This Kubernetes ScaledObject configuration uses the KEDA MSSQL scaler to automatically adjust the number of durabletask-mssql-app deployment replicas based on scale recommendations from the SQL database. Ensure the connection string and database credentials match those used by the application.

```yaml
apiVersion: keda.sh/v1alpha1
kind: ScaledObject
metadata:
  name: mssql-scaledobject
spec:
  scaleTargetRef:
    name: durabletask-mssql-app
  triggers:
  - type: mssql
    metadata:
      connectionStringFromEnv: SQLDB_Connection
      targetValue: "1"
      query: "SELECT dt.GetScaleRecommendation(8, 8)"
```

--------------------------------

### Clone Durable Task MSSQL Repository

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Clone the GitHub repository containing the Durable Task MSSQL implementation. This is the first step if you plan to build the container image yourself.

```powershell
git clone https://github.com/microsoft/durabletask-mssql
```

--------------------------------

### Create Kubernetes Secret for MSSQL Connection

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Define a Kubernetes secret to securely store the SQL database connection string. Replace 'PLACEHOLDER' with your chosen password.

```yaml
apiVersion: v1
kind: Secret
metadata:
  name: mssql-secrets
type: Opaque
stringData:
  # Replace PLACEHOLDER with the password you chose earlier
  SQLDB_Connection: "Server=mssqlinst.mssql.svc.cluster.local;Database=DurableDB;User ID=sa;Password=PLACEHOLDER;Persist Security Info=False;TrustServerCertificate=True;Encrypt=True;"
```

--------------------------------

### Monitor Kubernetes Pod Autoscaler

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/kubernetes.md

Watch the Horizontal Pod Autoscaler (HPA) generated by KEDA to monitor the scaling of deployment replicas in real-time. This helps in observing how the application handles load.

```powershell
kubectl get hpa keda-hpa-durabletask-mssql-app --watch
```

--------------------------------

### Configure Task Hub Name in Durable Functions host.json

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/taskhubs.md

Configure an explicit task hub name for Durable Functions apps by setting the 'hubName' property in the 'durableTask' section of the host.json file. This is used when multitenancy is disabled.

```json
{
  "version": "2.0",
  "extensions": {
    "durableTask": {
      "hubName": "MyTaskHub",
      "storageProvider": {
        "type": "mssql",
        "connectionStringName": "SQLDB_Connection"
      }
    }
  }
}
```

--------------------------------

### Grant Database Access using Managed Identity

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/quickstart.md

When using Managed Identities (version 0.10.0+), grant your application's identity access to the database using these SQL statements. Replace `nameofapplication` with your application's identity name.

```sql
CREATE USER [nameofapplication] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [nameofapplication];
```

--------------------------------

### Configure Custom Schema in Azure Functions host.json

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/multitenancy.md

Specify a custom schema name for Durable Task Framework in Azure Functions by updating the host.json file. This ensures your task data is stored in a dedicated schema.

```json
{
  "version": "2.0",
  "extensions": {
    "durableTask": {
      "storageProvider": {
        "type": "mssql",
        "connectionStringName": "SQLDB_Connection",
        "schemaName": "MyCustomSchemaName"
      }
    }
  }
}
```

--------------------------------

### Disable Shared Schema Multitenancy in SQL

Source: https://github.com/microsoft/durabletask-mssql/blob/main/docs/multitenancy.md

Use this T-SQL command to disable shared schema multitenancy. This causes the runtime to infer the task hub from the APP_NAME() SQL function.

```sql
-- Disable multi-tenancy mode
EXECUTE dt.SetGlobalSetting @Name='TaskHubMode', @Value=0
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.