In a microservices architecture, ensuring each service communicates reliably with databases like SQL Server and Oracle is critical but challenging due to restricted permissions. Health checks monitor service health, and in this post, I focus on advanced checks that execute custom queries to verify table access, beyond standard NuGet packages. I’ve learned to anticipate issues like permission errors, ensuring robust systems for global teams.
I developed a MultiDatabase health check to test specific queries across SQL Server and Oracle, addressing the challenge of limited database access rights that require DBA intervention (e.g., user creation, encrypted connection strings). This check integrates with CI/CD pipelines, catching issues before deployment, complementing my 95% test coverage in projects like RunningTracker.
The MultiDatabase health check confirms connectivity and query execution for SQL Server and Oracle.
Implementation
The MultiDatabaseHealthCheck class executes custom queries, reporting health status for each database. It supports multiple providers, handles errors gracefully, and integrates with ASP.NET Core HealthChecks. Here’s the setup:
services.AddHealthChecks()
.AddCheck(name: "live", check: () => HealthCheckResult.Healthy(), tags: new[] { "live", "ready" }, TimeSpan.FromSeconds(30))
.AddKafka(kafkaConfig!, kafkaTopic!, name: "Kafka")
.AddSqlServer(sqlServer, name: "SqlServer", failureStatus: HealthStatus.Unhealthy, tags: new[] { "db", "mssql" })
.AddOracle(oracle, name: "Oracle", failureStatus: HealthStatus.Unhealthy, tags: new[] { "db", "oracle" })
.AddCheck<HealthCheckExtensionMultiDatabase>(name: "MultiDatabase", failureStatus: HealthStatus.Unhealthy, tags: new[] { "db", "mssql", "oracle" });
The MultiDatabaseHealthCheck implementation (simplified for brevity; full code linked below):
public HealthCheckExtensionMultiDatabase(IDictionary<string, (string connectionString, IList<string> Queries, string provider)> databasesQueries)
{
_databasesQueries = databasesQueries;
}
public async Task<HealthCheckResult> CheckHealthAsync(HealthCheckContext context, CancellationToken cancellationToken = default)
{
var healthCheckResults = new Dictionary<string, HealthCheckResult>();
foreach (var dbQuery in _databasesQueries)
{
foreach (var query in dbQuery.Value.Queries)
{
try
{
using (var connection = CreateConnection(dbQuery.Value.connectionString, dbQuery.Value.Provider))
{
await connection.OpenAsync(cancellationToken);
using (var command = CreateCommand(query, connection, dbQuery.Value.Provider))
{
await command.ExecuteScalarAsync(cancellationToken);
healthCheckResults[$"{dbQuery.Key}-{query}"] = HealthCheckResult.Healthy("ok");
}
}
}
catch (Exception ex)
{
var errorMessage = Regex.Unescape($"Exception during query execution: {ex.Message}");
healthCheckResults[$"{dbQuery.Key}-{query}"] = HealthCheckResult.Unhealthy(errorMessage);
}
}
}
var overallStatus = healthCheckResults.Values.All(result => result.Status == HealthStatus.Healthy) ? HealthStatus.Healthy : HealthStatus.Unhealthy;
return new HealthCheckResult(overallStatus, description: "Multi-database health check", data: healthCheckResults.ToDictionary(kvp => kvp.Key, kvp => (object)kvp.Value.Description!)!);
}
private DbConnection CreateConnection(string connectionString, string provider)
{
return provider switch
{
"SqlServer" => new SqlConnection(connectionString),
"Oracle" => new OracleConnection(connectionString),
_ => throw new NotSupportedException($"Provider {provider} is not supported.")
};
}
private DbCommand CreateCommand(string query, DbConnection connection, string provider)
{
return provider switch
{
"SqlServer" => new SqlCommand(query, (SqlConnection)connection),
"Oracle" => new OracleCommand(query, (OracleConnection)connection),
_ => throw new NotSupportedException($"Provider {provider} is not supported.")
};
}
Testing Permissions
The Oracle database is initialized via Docker Compose, executing an SQL script to create users (ADMIN, TESTUSER) and tables (SAMPLE_MESSAGES, WO_ACCESS). Run docker-compose up to set up the environment. The script configures permissions to simulate real-world scenarios:
ALTER SESSION SET CONTAINER = XEPDB1;
CREATE USER ADMIN IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO ADMIN;
CREATE TABLE ADMIN.SAMPLE_MESSAGES (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
message_id RAW(16) DEFAULT SYS_GUID() NOT NULL,
message_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
message_deleted_date TIMESTAMP DEFAULT NULL
);
CREATE TABLE ADMIN.WO_ACCESS (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
message_id RAW(16) DEFAULT SYS_GUID() NOT NULL,
message_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
message_deleted_date TIMESTAMP DEFAULT NULL
);
CREATE USER TESTUSER IDENTIFIED BY password;
GRANT CONNECT TO TESTUSER;
GRANT ALL PRIVILEGES ON ADMIN.SAMPLE_MESSAGES TO TESTUSER;
COMMIT;
Using TESTUSER, the application lacks access to ADMIN.WO_ACCESS, triggering an Oracle “Table or view doesn’t exist” error:
An Oracle permission error is detected when TESTUSER lacks access to the WO_ACCESS table.
If I configure the user in appsettings.json with proper access (e.g., ADMIN), the HealthCheck returns Healthy. This ensures deployment readiness.
Conclusion
The MultiDatabase health check addresses real-world database access challenges, integrating seamlessly with CI/CD pipelines. For simple queries, ExecuteScalarAsync verifies access; for complex queries, adapt to ExecuteReaderAsync. Explore the complete code in my GitHub repository.
Connect via LinkedIn to discuss microservices reliability!