SQLSchemaCompare

A .NET 8 console application that compares SQL Server database schemas between a source server and multiple target servers, detects differences, and can generate or deploy change scripts automatically.

Built on Microsoft DacFx — the same engine that powers Microsoft's official tools such as SSDT, SqlPackage, and Azure Data Studio schema compare. This means schema extraction, comparison, and deployment produce results fully consistent with Microsoft tooling.

Designed to run as a scheduled task for continuous schema drift monitoring across environments.

Features

• Report detect schema differences and log them to a database table for monitoring/alerting
• Script generate a T-SQL deployment script without applying it
• Deploy apply schema (and optionally data) changes to target server(s)
• Extract extract a dacpac snapshot from the source database only
• Parallel execution one thread per target server for optimal performance
• Supports SQL Server 2012 through 2022
• Output files can be zipped automatically with configurable retention

Requirements

• .NET 8 Runtime (to run a published binary) or .NET 8 SDK (to build from source)
• SQL Server (any edition, 2012+)
• Windows (uses Windows Authentication or SQL login)

Getting Started

1. Configure

Copy the example config and fill in your values:

copy appsettings.example.json appsettings.json

Edit appsettings.json:

Section	Key fields
SourceServerSettings	SourceServer, SourceDatabase, Action (Report/Script/Deploy/Extract)
TargetServers	Array of target servers; Databases is semicolon-separated
ScriptFilesOptions	PathToSaveScriptFiles where output files are written
ReportServer	Connection to the DB holding the differences log table
PackageOptions	DacFx extract options (ignore permissions, extended properties, etc.)
DeployOptions	DacFx deploy options (drop objects, block on data loss, excluded types, etc.)

Never commit appsettings.json it contains server names and credentials. It is .gitignored by default.

2. Create the Report table — one-time setup

Required before first run if using the Report action. The application will fail to log differences if this table does not exist.

Run Scripts/SQLSchemaCompareDifferences.sql on the database configured in ReportServer. This creates the SQLSchemaCompareDifferences table that stores all detected schema differences. Only needs to be done once per environment.

3. Build and run

dotnet build
dotnet run                          # uses appsettings.json
dotnet run -- path/to/config.json   # custom config file
dotnet publish

Configuration Reference

Actions

Action	Effect
Report	Compares schemas, writes XML diff report, logs differences to SQLSchemaCompareDifferences table — see Scripts/SQLSchemaCompareDifferences.sql to create the table
Script	Generates a T-SQL script to bring target in sync with source does not execute
Deploy	Applies the generated script to each target database
Extract	Extracts a dacpac from the source database and exits

Authentication

Set SourceWindowsAutherntication: true for Windows auth, or false and provide SQLUser / SQLPassword for SQL login. Same applies to TargetServers and ReportServer.

Add Trust Server Certificate=True to connection strings when connecting to instances with self-signed certificates.

Excluding object types

DeployOptions.ExcludeObjectTypes is a semicolon-separated list of ObjectType names to exclude from comparison. Example:

"ExcludeObjectTypes": "Logins;Users;Roles;Permissions;ExtendedProperties"

Full DacFx option references

• DacExtractOptions PackageOptions
• DacDeployOptions DeployOptions

Monitoring integration

When using the Report action, schema differences are inserted into SQLSchemaCompareDifferences on the configured ReportServer. You can query this table from:

• A SQL Server Agent job that sends an alert email if new rows exist since the last check
• Any external monitoring tool that can run a SQL query

Logging

Dual output via log4net: colored console and a rolling log file (SQLSchemaCompare.log, max 10 MB, 5 backups). See log4net.config to adjust levels or destinations.

Changelog

2.0.0.5 — 2026-04-12

• IgnoreReplicatedObjects (DeployOptions.IgnoreReplicatedObjects: true): New config option to control whether SQL72035 ("replicated object cannot be modified") triggers the automatic retry. When true, affected table names are extracted from the exception, a filtered temp dacpac is created excluding those tables, and the operation retries. When false (default), the error is raised as before. Previously the retry was always-on with no way to disable it.

2.0.0.4 — 2026-04-12

• IgnoreDefaultConstraints (DeployOptions.IgnoreDefaultConstraints: true): New option that strips inline DEFAULT constraints from the source dacpac before comparison. Prevents DacFx from generating cascading DROP CONSTRAINT / ALTER COLUMN / DROP INDEX / CREATE INDEX statements caused by default constraint name mismatches — typically system-generated names (e.g. DF__MyTable__MyCol__3A81B327) that differ between source and target even when the default value is identical. This is common in environments where default constraints were created without explicit names. Implemented by modifying model.xml inside a temporary dacpac (original file is never touched), recomputing the SHA-256 checksum in Origin.xml, and cleaning up orphaned AttachedAnnotation disambiguator references that would otherwise cause a DacFx load error.
• SQL72035 replicated object handling: When DacFx raises SQL72035 ("replicated object cannot be modified"), the affected table names are extracted from the exception, a filtered temp dacpac is created excluding those tables, and the operation is retried automatically. Previously this error terminated script generation entirely.

2.0.0.3 — prior

• Initial .NET 8 release with Report / Script / Deploy / Extract actions, parallel target processing, and log4net dual output.