Background

I maintain a collection of over 140 open source static code analysis rules based on the DacFX API, covering T-SQL best practices for design, naming, and performance.

I previously blogged about the launch of the T-SQL Analyzer extension for Visual Studio, and more recently about the significant update that brought live analysis to SSMS and Visual Studio.

This post covers the newest addition to the family: a dedicated Visual Studio Code extension, so you get the same real-time feedback on your SQL scripts no matter which editor you prefer.

What's new

Live analysis in VS Code

The extension analyzes your .sql files in real time and highlights design, naming, and performance issues right in the editor. Problems appear as squiggles while you edit, just like compiler errors.

Key features:

• Live squiggles — problems appear while you edit, just like compiler errors
• 140+ built-in rules — covering design best practices, naming conventions, and performance pitfalls
• Hover for details — hover over any squiggle to see the rule id, a plain English description, and a link to the full documentation
• Zero configuration — works out of the box with sensible defaults
• Customizable — disable individual rules, promote warnings to errors, or target a specific SQL Server version
• Status bar indicator — shows when analysis is running

Any SQL script analyzed

Just like the SSMS and Visual Studio extensions, the VS Code extension analyzes any SQL script you have open — whether it is a stored procedure, a query, a migration script, a one-off data fix, or anything else. If you're writing SQL, you get feedback.

Customizing rules

All rules are enabled by default. You can fine-tune them in Settings using a rules expression:

Browse the full rule catalogue at github.com/ErikEJ/SqlServer.Rules/docs.

Settings

Commands

Open the Command Palette (Ctrl+Shift+P / Cmd+Shift+P) and type T-SQL Analyzer:

• T-SQL Analyzer: Analyze Active File — run analysis on demand.
• T-SQL Analyzer: Restart Analysis Server — restart the background analyzer process (useful after updating settings).

Getting started

1. Install the .NET 10 SDK (or later).
2. Install the extension from the VS Code Marketplace.
3. Open any .sql file — analysis starts automatically.

That's it. The analyzer tooling is downloaded automatically on first use; there is nothing else to install.

Summary

Feedback and contributions

Should you encounter bugs or have feature requests, head over to the GitHub repository to open an issue if one doesn't already exist.

If you enjoy using the extension, please give it a ★★★★★ rating on the VS Code Marketplace.

Another way to help out is to sponsor me on GitHub.

That fixed "startup tax" has been one of the most requested pain points for years - and I am thrilled to say it is finally being addressed. With the DacFx 170.5.60-preview release, a warm publish can now complete in around 2 seconds. That is a 10x improvement on the part of the deployment that used to feel like pure overhead.

I actually attempted something similar a while ago, and will share my experience from that attempt with the DacFX team.

Why was it always 20 seconds?

Every publish begins by loading and validating the model contained in the .dacpac, building an in-memory representation of your schema, and comparing it against the target, requiring large metadata queries against the target database. Historically a large chunk of that work was repeated on every run, regardless of whether anything had actually changed. For small projects the model work completely dominated the wall-clock time - hence the constant ~20 second floor.

The work latest sqlpackage and DacFX preview removes that redundant work, so repeated publishes of the same .dacpac no longer pay the full model cost each time.

How to try it out

The feature is in preview, so you need the latest preview build of SqlPackage.

1. Make sure you have the .NET SDK installed.

2. Install (or update to) the latest preview of the SqlPackage global tool:

   dotnet tool install -g microsoft.sqlpackage --prerelease
   

3. Confirm you are on the preview build (170.5.60.2 or higher):

   sqlpackage /version
   

4. Publish with the new additional parameters and enjoy the speed

   sqlpackage /Action:Publish ^
       /SourceFile:MyDatabase.dacpac ^
       /TargetServerName:localhost ^
       /TargetDatabaseName:MyDatabase ^
       /p:LogDeployment=true ^
       /p:EnableFastComparison=true
   

Run the same publish a second time to see the warm-path improvement in action.

You will see this logged to the console when fast deployment kicks in:

Fast comparison match found, source model checksum and deployment options match last logged deployment. Database reverse engineering and deployment plan generation is skipped. Disable fast comparison to run a full model comparison.

Notice that this feature assumes no drift (ouside schema changes) between deployments. In other words, it assumes that the .dacpac owns the database schema.

Under the hood

The key change is that the model checksum and additional metadata is now cached in a system table in the target database, so that subsequent publishes can skip the model reverse engineer and model build steps, resulting in significantly faster deployments.

The system table to store the "vectors" of the currently deployed package has the folloiwng structure:

CREATE TABLE [dbo].[__DacFxDeploymentHistory](
   [deployment_id] [int] IDENTITY(1,1) NOT NULL,
   [dacpac_name] [varchar](256) NOT NULL,
   [project_id] [uniqueidentifier] NOT NULL,
   [dac_version] [nvarchar](64) NOT NULL,
   [deployment_options] [xml] NULL,
   [model_checksum] [varbinary](32) NOT NULL,
   [date_created] [datetime2](7) NOT NULL,
   [created_by] [sysname] NOT NULL,
   [description] [nvarchar](4000) NULL,
 CONSTRAINT [PK___DacFxDeploymentHistory] PRIMARY KEY CLUSTERED 
(
  [deployment_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[__DacFxDeploymentHistory] ADD  DEFAULT (sysutcdatetime()) FOR [date_created]
GO

ALTER TABLE [dbo].[__DacFxDeploymentHistory] ADD  DEFAULT (original_login()) FOR [created_by]
GO

The /p:LogDeployment=true parameter is required to enable the creation of this table and the logging of the model checksum and deployment options etc.

You can monitor the contents of this table to see the last deployed model checksum and deployment options. You can also use this to verify that the fast comparison would work as expected before your turn on /p:EnableFastComparison=true.

Any changes to the schema will update the stored model checksum, so that subsequent publishes can detect that a full model comparison is required.

Likewise any changes to the deployment options will also update the stored deployment options, so that subsequent publishes can detect that a full model comparison is required.

Based on this query executed by sqlpackage, you can see that the decision to use skip full deployment is based on entries scoped to the current ProjectGuid of the .dacpac:

exec sp_executesql N'SELECT TOP 1 
      [deployment_id], [dacpac_name], [project_id], [dac_version],
      [deployment_options], [model_checksum],
      [date_created], [created_by], [description]
FROM [dbo].[__DacFxDeploymentHistory] WITH (NOLOCK)
WHERE [project_id] = @projectId
ORDER BY [date_created] DESC',N'@projectId uniqueidentifier',@projectId='726D17BA-DCCA-4607-8C1F-3A68523C7112'

/p:EnableFastComparison=true requires /p:LogDeployment=true in order to work. And if the outcome of the fast comparision is a match, then deployment is skipped.

I tried changing a post deployment script, and the next publish incorrectly skipped the model comparison, so it seems that only schema and deployment options changes are detected.

This is a known limitation of the current implementation. I have filed a bug for this.

One caveat: the missing ProjectGuid

While trying this out, you may hit an issue where the speed-up does not kick in because the .dacpac is missing a ProjectGuid, which is used to identify the project across publishes. This is being tracked in microsoft/DacFx#824. If you do not see the improved timings, this is the most likely culprit - keep an eye on the issue for the recommended workaround and follow-up fixes.

Wrapping up

This is a preview, so expect a few rough edges, but it is a fantastic step forward for anyone doing frequent, iterative database deployments - especially in inner-loop development and CI where those seconds add up fast.

Give it a try, and please report any feedback on the DacFx repo. Huge thanks to the DacFx team for tackling this long-standing request!

Happy (fast) deploying!

Background

I maintain a collection of over 140 open source static code analysis rules based on the DacFX API, covering T-SQL best practices for design, naming, and performance.

I previously blogged about the launch of the T-SQL Analyzer extension for Visual Studio, which provided live analysis of SQL scripts as you author them.

This post covers a significant update to both the Visual Studio and the new SQL Server Management Studio extension.

What's new

Now available in SQL Server Management Studio 22

The live analysis experience is no longer limited to Visual Studio — it is now also available in SQL Server Management Studio 22 (SSMS). You get the same real-time feedback on your SQL scripts as you type, directly in the SSMS query editor.

The SSMS extension is available in the new SSMS VSIX Gallery.

Analysis of any SQL script — not just CREATE statements

Previously, live analysis only triggered on SQL scripts that contained CREATE statements — the kind typically found inside SQL database projects. The extension has now been revamped to analyze any SQL script you have open in the editor, whether it is a stored procedure, a query, a migration script, a one-off data fix, or anything else. If you're writing SQL, you now get feedback.

If you encounter any issues with this new feature, please create an issue in the GitHub repo

Configure via Tools / Options

Both extensions now expose settings under Tools → Options → *T-SQL Analyzer in Visual Studio and SSMS, so you can customize behavior without editing project files.

These options apply globally when a script is not part of a SQL database project. When a project is involved, the extension defers to the project configuration (see below).

Project settings are still respected

If your SQL script is part of a SQL database project (based on MSBuild.Sdk.SqlProj, Microsoft.Build.Sql, or a classic .sqlproj), the extension continues to respect the project's own configuration:

<Project Sdk="MSBuild.Sdk.SqlProj/3.2.0">
  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <SqlServerVersion>Sql170</SqlServerVersion>
    <RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
    <CodeAnalysisRules>-SqlServer.Rules.SRD0006</CodeAnalysisRules>
    <!-- This property supports wildcard rule filters -->
    <!-- and overrides 'CodeAnalysisRules' above if present -->
    <AnalyzerCodeAnalysisRules>-SqlServer.Rules.SRD0006;-Microsoft.*</AnalyzerCodeAnalysisRules>
  </PropertyGroup>
</Project>

This means if analysis is disabled in the project, or if you have suppressed specific rules via CodeAnalysisRules or AnalyzerCodeAnalysisRules, the live extension honours those settings. The global Tools/Options settings only apply when no project configuration is found.

Installation

Visual Studio

Download the extension from the Visual Studio Marketplace.

• Visual Studio 2026 and later: No separate installation required — the extension uses the dnx command to run the T-SQL Analyzer CLI as a NuGet package automatically.
• Visual Studio 2022: Install the latest version of the CLI tool first:

dotnet tool install -g ErikEJ.DacFX.TSQLAnalyzer.CLI

SSMS

Download the extension from the new SSMS VSIX Gallery.

The extension automatically uses the dnx command to run the T-SQL Analyzer CLI tool. No separate installation is needed, but the .NET 10 SDK is required (automatically installed with the Database DevOps workload in SSMS).

Summary

Feedback and contributions

Should you encounter bugs or have feature requests, head over to the GitHub repo to open an issue if one doesn't already exist.

If you enjoy using the extensions, please give them a ★★★★★ rating on the Visual Studio Marketplace.

Another way to help out is to sponsor me on GitHub.

Note that everything demonstrated in this blog post is already available in Visual Studio and VS Code.

Requirements

• .NET 10.0 runtime (minimum requirement for MCP Server mode)
• SSMS 22.7 or later

Background: Why This Matters

As a SQL developer, you want to catch design issues, performance anti-patterns, and security vulnerabilities early—ideally before code reaches production. The T-SQL Analyzer tool includes 140+ rules covering design, naming, and performance concerns. With SSMS 22.7's MCP Server support, you can now leverage this analysis directly in your IDE, with the added benefit of being able to rely on .NET 10's latest performance improvements.

What's New in SSMS 22.7?

SSMS 22.7 adds native Agent support that can communicate with MCP Servers. This means tools like T-SQL Analyzer can now:

• Analyze SQL scripts deterministically using pre-defined rules
• Provide actionable feedback within the editor context
• Integrate with your existing SQL development workflow

Setting Up the T-SQL Analyzer MCP Server

Step 1: Verify .NET 10.0 Installation

Ensure you have .NET 10.0 runtime installed:

dotnet --version

If you don't have .NET 10.0, download it from dot.net.

Step 2: Configure SSMS 22.7 MCP Server

Create or update your SSMS MCP configuration file at:

C:\Users\<username>\.mcp.json

Replace <username> with your Windows username. The recommended configuration uses the .NET 10 dnx launcher to automatically restore and invoke the T-SQL Analyzer package:

{
    "servers": {
        "tsqlanalyzer": {
            "type": "stdio",
            "command": "dnx",
            "args": [
                "ErikEJ.DacFX.TSQLAnalyzer.Cli",
                "--yes",
                "--",
                "-mcp"
            ]
        }
    }
}

This configuration:

• Uses dnx to download and run the latest T-SQL Analyzer package from NuGet
• --yes automatically restores the package if needed
• -- separates package arguments from MCP arguments
• -mcp tells the analyzer to launch in MCP Server mode

Step 3: Enable the MCP Server in SSMS

1. Open SSMS 22.7
2. Open GitHub Copilot, select Agent mode and enable the find_sql_script_problems in the TSQLAnalyzerMCP local MCP server.

How It Works: Deterministic Analysis

The MCP Server exposes a single analysis tool: find_sql_script_problems. This tool:

1. Accepts the text content of your SQL CREATE script
2. Parses the script using DacFx's T-SQL parser
3. Evaluates it against 140+ built-in rules (design, naming, performance, security)
4. Returns a structured list of issues with line numbers, severity, and descriptions

Example: Detecting a Potential SQL Injection

CREATE PROCEDURE [dbo].[GetUserData]
    @userId VARCHAR(255)
AS
BEGIN
    DECLARE @sql NVARCHAR(1024);
    SELECT @sql = CONCAT(N'SELECT * FROM Users WHERE Id = ''', @userId, N'''');
    EXEC [sys].[sp_executesql] @stmt = @sql;
END;

When you analyze this procedure in SSMS:

1. Right-click on the query editor or use the SSMS Agent
2. Select "Analyze with T-SQL Analyzer"
3. The MCP Server immediately detects:
   • SRD0096 (Design): Potential SQL injection issue (tainted variable in dynamic SQL)
   • Any other applicable rule violations
4. GitHub Copilot provides a summary of the findings, and may offer to fix them.

The analysis is deterministic—the same script always produces the same results based on the rule set.

Supported Rule Categories

The analyzer covers three main categories:

Design Rules (SRD*)

• Hardcoded credentials
• Unnamed primary keys
• SQL injection vulnerabilities
• Missing schema qualifiers
• And more...

Naming Rules (SRN*)

• Single-character aliases and variables
• Non-standard naming patterns
• Case sensitivity issues

Performance Rules (SRP*)

• Implicit range windows in window functions
• Deprecated functions (CAST, CONVERT misuse)
• Cross-server joins
• Missing indexes and query store settings
• And more...

Key Advantages in SSMS 22.7

1. In-Context Feedback: Analyze without leaving the editor
2. Deterministic Results: Same analysis every time; no LLM variance or hallucinations
3. Rule-Based: Transparent, predictable logic based on SQL Server best practices
4. Quick Feedback Loop: Immediate results as you work

Example Workflow

1. Open your stored procedure in SSMS
   ↓
2. Use SSMS Copilot Agent to invoke the MCP Server
   ↓
3. T-SQL Analyzer scans the script against 140+ rules
   ↓
4. Results appear in SSMS—each issue shows line number, rule ID, and description
   ↓
5. Fix the issues or add IGNORE comments for false positives
   ↓
6. Re-analyze to confirm

Comparison: T-SQL Analyzer MCP Server vs. Command Line

Getting Started

1. Verify .NET 10.0: Run dotnet --version to confirm .NET 10.0 is installed
2. Create configuration file: Create or edit C:\Users\<username>\.mcp.json (replace <username> with your Windows username)
3. Add the MCP server entry to your configuration file (see Step 2 above)
4. Enable the server in SSMS: Enable the MCP server in the GitHub Copilot window
5. Analyze: Ask Copilot to use the MCP Server
6. Fix: Ask Copilot to address the issues

Why .NET 10.0?

The T-SQL Analyzer MCP Server requires .NET 10.0 to take advantage of the latest performance improvements and framework enhancements. The dnx package runner automatically handles fetching and executing the latest version of the tool, ensuring you always have access to the newest rules and optimizations without manual updates.

Next Steps

• Check out the documentation for detailed rule descriptions
• Review the GitHub repository for source code and community contributions
• Follow the blog for updates on new rules and SSMS features

Call to Action

The combination of SSMS 22.7's MCP Server support and the T-SQL Analyzer brings professional-grade SQL static analysis to your fingertips. Try it out and let me know how it improves your SQL development workflow!

• GitHub
• X

Follow the blog for more posts on SQL Server, static analysis, and developer tools.

The problem: slow feedback loop for programmability objects

SQL Database Projects are a great way to manage your database schema in source control. The normal development workflow is:

1. Edit your .sql files in Visual Studio
2. Build the project to produce a .dacpac file
3. Deploy the .dacpac to your development database (via dacpac publish or schema compare)
4. Test your changes

This workflow works well for schema objects like tables and indexes, where dacpac deployment is essential — it handles complex operations like renaming columns, adding constraints, and managing data migrations safely. You really do need the full deployment pipeline for those.

However, for programmability objects — stored procedures, views, functions, and triggers — the situation is different. These objects can be replaced atomically using SQL Server's CREATE OR ALTER statement, which has been supported since SQL Server 2016. There is no need to build and deploy a full .dacpac just to update a stored procedure body.

Unfortunately, SQL Database Projects (and the underlying DacFx tooling) do not allow you to selectively deploy a single file. Every change still requires building the full .dacpac and running a deployment, even if you only changed a single stored procedure. This has been raised as a feature request with the DacFx team, but in the meantime SQL Project Power Tools provides a practical workaround.

The solution: Publish programmability objects on save

The new Publish programmability objects on save feature detects when you save a .sql file that contains a supported programmability object and immediately executes a CREATE OR ALTER version of that script against your development database. The status bar shows a confirmation when the publish completes, or an error message if something goes wrong.

Important distinction: This feature only works for programmability objects — stored procedures, views, functions, and triggers. Schema objects such as tables, indexes, and constraints are deliberately excluded. Changing a table definition has data implications that require the full DACpac deployment pipeline to handle safely.

How it works

When a .sql file is saved in Visual Studio:

1. The extension checks whether the file belongs to a SQL Database Project.
2. It looks for an .env file in the project directory containing an AutoPublish connection string.
3. If found, it parses the script using the T-SQL parser and verifies it contains only supported statements: CREATE PROCEDURE, CREATE VIEW, CREATE FUNCTION, or CREATE TRIGGER (and SET options).
4. Any CREATE statement that is not already CREATE OR ALTER is automatically rewritten to use CREATE OR ALTER.
5. The rewritten script is executed against the database specified in the connection string.
6. The Visual Studio status bar shows Publish completed: <filename> on success, or Publish failed: <filename> on failure.

Files that contain table definitions, ALTER TABLE, INSERT, DROP, or any other statements that are not programmability object definitions are silently skipped — the feature does nothing for those files.

Setting up the feature

Step 1: Enable the option

The feature is off by default. To turn it on, open Tools > Options > SQL Server Tools > SQL Project Power Tools in Visual Studio and check the Enable auto publish on save option.

Step 2: Add the connection string

Create a file named .env in the root of your SQL Database Project directory (the same folder as your .sqlproj or .csproj file). Add a line in the following format:

AutoPublish=Server=localhost;Database=MyDevDatabase;Integrated Security=true;TrustServerCertificate=true

You can use any valid SQL Server connection string. The key must be AutoPublish (case-insensitive).

Security tip: The .env file contains a connection string, so make sure to add it to .gitignore to avoid committing credentials to source control.

Step 3: Save a programmability object

Open a .sql file in your project that contains a CREATE PROCEDURE, CREATE VIEW, CREATE FUNCTION, or CREATE TRIGGER statement, make a change, and save. The extension will automatically rewrite the statement to CREATE OR ALTER and execute it against the development database. Watch the status bar at the bottom of Visual Studio for the completion message.

Example

Suppose you have a stored procedure in your project at dbo/Stored Procedures/GetCustomerById.sql:

CREATE PROCEDURE [dbo].[GetCustomerById]
    @Id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Id, Name, Email
    FROM dbo.Customers
    WHERE Id = @Id;
END

When you save this file with the feature enabled and a valid .env file present, the extension will execute the following against your development database:

CREATE OR ALTER PROCEDURE [dbo].[GetCustomerById]
    @Id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Id, Name, Email
    FROM dbo.Customers
    WHERE Id = @Id;
END

The stored procedure is updated immediately, without a full build and deploy cycle.

Other features in SQL Project Power Tools

SQL Project Power Tools includes many other features to improve the SQL Database Project developer experience:

• Templates — Project and item templates for creating new SDK-style SQL Database Projects and common object types from File > New > Project and Add > New Item.
• Import database — Import the full schema of an existing database into your project, with configurable file layout (flat, by object type, by schema, or by schema and object type).
• Schema compare — Visually compare your database project with a live database and apply changes in either direction.
• Analyze — Run static code analysis on your project and view a detailed report of issues.
• Manage code analysis rules — Enable or disable individual static analysis rules and set their severity directly from a visual dialog, for SDK-style projects.
• Create Mermaid E/R diagram — Generate an Entity-Relationship diagram of selected tables for documentation.
• .dacpac Solution Explorer node — Browse the contents of a built .dacpac file directly from Solution Explorer.
• Script Table Data — Generate INSERT statements for table data to use as seed data in post-deployment scripts, based on generate-sql-merge.
• Add pre- and post-deployment scripts — Quickly add new pre- and post-deployment scripts to your project from the context menu.
• Scaffold SQL MCP Server (preview) — Generate a SQL MCP Server configuration file based on your database project schema.

A getting started guide covers all of these features in more detail.

Getting the extension

Visual Studio

Install SQL Project Power Tools from the Visual Studio Marketplace, or search for it directly in Visual Studio via Extensions > Manage Extensions.

SSMS

Install SQL Project Power Tools from the new SSMS Gallery.

Contribute

The source code is open source and available on GitHub. If you find the extension useful, a ★★★★★ rating on the Marketplace is always appreciated. Bug reports and feature requests are welcome on the GitHub issue tracker.

SqlServer.Rules is an open-source static code analysis library and toolset for SQL Server database projects, command line, and Visual Studio, that helps teams catch design flaws, naming inconsistencies, performance anti-patterns, and risky T-SQL constructs early — during development and build time instead of in production.

The value is straightforward: it shifts SQL quality checks left, gives fast and repeatable feedback in CI/CD and local workflows, and helps improve reliability, maintainability, and performance of database code with clear, actionable rule-based guidance.

This major release expands rule coverage significantly and continues consolidation around the SqlServer.Rules codebase and tooling.

Highlights

• Large set of new and migrated design and performance rules
• New SQL project/database option checks
• Completed migration from legacy TSQLSmells packaging
• Tooling and repository improvements

New and Ported Rules (with docs links)

Design rules

• SRD0078 Single-character aliases are poor practice
• SRD0079 Single-character variables are poor practice
• SRD0080 TOP expression should use parentheses
• SRD0081 TOP(100) PERCENT is ignored by optimizer
• SRD0082 Avoid changing DATEFORMAT
• SRD0083 Avoid changing DATEFIRST
• SRD0084 CONCAT_NULL_YIELDS_NULL must be ON
• SRD0085 ANSI_NULLS should be ON
• SRD0086 ANSI_PADDING should be ON
• SRD0087 ANSI_WARNINGS should be ON
• SRD0088 NUMERIC_ROUNDABORT should be OFF
• SRD0089 QUOTED_IDENTIFIER should be ON
• SRD0090 SET FORCEPLAN should be OFF
• SRD0091 Avoid ORDER BY in derived tables for final ordering
• SRD0092 Avoid named primary key constraints on temp tables
• SRD0093 Do not name default constraints on temporary tables
• SRD0094 Avoid named foreign keys on temporary tables
• SRD0095 Named check constraints on temp tables
• SRD0096 Potential SQL injection issue

Performance rules

• SRP0026 Avoid cross-server joins
• SRP0027 Avoid explicit conversion of columnar data
• SRP0028 Explicit RANGE window frame
• SRP0029 Implicit RANGE window frame
• SRP0030 Specify FAST_FORWARD for cursors

SQL project/database option rules

• SRD0700 Database PAGE_VERIFY option is not CHECKSUM
• SRD0701 Database QUERY_STORE option is not READ_WRITE
• SRD0703 Database QUERY_STORE_CAPTURE_MODE option is not AUTO
• SRD0704 Database TARGET_RECOVERY_TIME option is not set
• SRD0705 Database AUTO_CLOSE option is not OFF
• SRD0706 Database AUTO_SHRINK option is ON

Tooling and cleanup

• Documentation generation moved to a separate tool
• Solution updates (including .slnx)
• Legacy TSQLSmells package/sign/test removal

Upgrade

<PackageReference Include="ErikEJ.DacFX.SqlServer.Rules" Version="5.0.0">
  <PrivateAssets>all</PrivateAssets>
</PackageReference>

Full changelog

• Release notes

Thanks to all contributors and everyone using and testing SqlServer.Rules.

👉 Find it here

This new page gives SQL Server developers and DBAs a quick overview of tools that can improve day-to-day work in SQL Server Management Studio 22.

The goal with this launch is simple: make it easier to discover both free and commercial SSMS extensions in one place.

I got inspired to build this by the blog post from Brent Ozar here.

If you build or maintain an SSMS 22 extension and want it included, please open an issue or pull request.

What is Visual Schema Compare?

Visual Schema Compare is a built-in feature of the SQL Database Project Power Tools Visual Studio extension and the SQL Database Project Power Tools for SSMS extension. It surfaces the full power of the Microsoft DacFx schema comparison engine — the same engine used by SQL Server Data Tools — directly inside SSMS and Visual Studio, in a purpose-built tool window.

With a single right-click on your database project, you can:

• Compare your project against a live database to see what has drifted
• Compare a live database against your project to see what your project is missing
• Review per-object T-SQL diffs side by side
• Copy the generated deployment script ready for review or execution

No extra tooling, no manual diffing, and no leaving Visual Studio / SSMS.

Why You Need This

The Problem: Schema Drift

Even with a database project in source control, schemas drift. A hotfix goes directly to production, a developer makes a change in their local environment but forgets to commit the SQL script, or an ORM migration runs against a shared database. Over time, the gap between what is in the project and what is in the database grows — and at some point you need to reconcile them.

Traditional approaches involve:

• Exporting scripts and doing a file diff
• Writing your own comparison queries against sys.objects

None of these fit naturally into a code-first database development workflow.

The Solution: Visual Schema Compare

Visual Schema Compare gives you a first-class schema diff experience without leaving your development environment.

Key Capabilities

• Project-to-database and database-to-project comparison – Choose which side is the source and which is the target, covering both deployment and reverse-engineering scenarios.
• Side-by-side T-SQL diffs – Selecting any row in the differences grid renders the source and target T-SQL definitions side by side, powered by DiffPlex, so you can immediately see what changed.
• Actionable differences grid – The grid shows the object name, object type, difference type (Added, Deleted, Changed), and the update action DacFx recommends.
• Deployment script generation – DacFx generates a full T-SQL deployment script for the detected differences. You can copy it to the clipboard and use it for manual review, peer review, or execution in your own pipeline.
• Async comparison – The comparison runs asynchronously so Visual Studio stays responsive while DacFx processes large schemas.

Getting Started

Prerequisites

• Visual Studio 2022 (any edition) or SSMS 22
• SQL Database Project Power Tools for Visual Studio installed
• or SQL Database Project Power Tools for SSMS installed
• An SDK-style SQL Database Project in your solution

Running a Comparison

1. In Solution Explorer, right-click your SQL Database Project.

2. Select SQL Project Power Tools > Visual Schema Compare (preview)...

3. In the dialog that appears, enter the connection details for your database and choose the comparison direction:

   • Database is source – compares the live database against your project (useful for reverse-engineering changes made directly to the database)
   • Project is source – compares your project against the live database (useful before deployment)

4. Click Compare. The Visual Schema Compare tool window opens and populates with the results.

   

5. Browse the differences. Click any row in the grid to see the side-by-side T-SQL diff for that object.

6. When you are ready to act on the differences, click Copy Deployment Script to copy the DacFx-generated T-SQL deployment script to your clipboard.

Benefits of SQL Database Project Power Tools

Visual Schema Compare is just one of the features in SQL Database Project Power Tools. The extension as a whole is designed to reduce friction in every stage of the database development lifecycle:

Together, these features make SQL database projects a viable choice for teams that want to apply software engineering best practices — source control, code review, CI/CD — to their database schemas while enabling peformant, cross platform build and deployment processes.

Acknowledgements

The Visual Schema Compare feature was inspired by the excellent Database Schema Compare feature in Axial SQL Tools. The concept of surfacing DacFx schema comparison inside a Visual Studio tool window, with a per-object T-SQL diff view and a deployment script tab, originated there. Many thanks to the Axial SQL Tools team for sharing the idea openly.

Installation

Install from the Visual Studio Marketplace or search for SQL Database Project Power Tools in Extensions > Manage Extensions inside Visual Studio 2022.

Feedback, bug reports, and contributions are welcome on the GitHub repository.

If you find the extension useful, please give it a ★★★★★ rating on the Visual Studio Marketplace and consider sponsoring the author on GitHub.

If you've used the new Code Analysis Settings dialog in VS Code, this brings an even better experience to Visual Studio and SQL Server Management Studio (SSMS).

Why This Matters

SQL code analysis has been part of the SSDT workflow for years. Before deploying schema changes, you can run static analysis rules against your project to catch potential issues — things like missing primary keys, deprecated syntax, or performance anti-patterns.

But configuring which rules to disable or set warning levels has always meant manually editing XML in your project file:

<PropertyGroup>
  <RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
  <SqlCodeAnalysisRules>-SqlServer.Rules.SRD0004;+!SqlServer.Rules.SRN0005</SqlCodeAnalysisRules>
</PropertyGroup>

Not terrible, but not exactly intuitive either. You need to know the rule IDs, the syntax for enabling/disabling, and how to set severity levels. For teams with many rules to configure, this quickly becomes error-prone.

The Visual Rule Manager

The new Rule Manager dialog changes this completely.

To open it: right-click your SQL database project in Solution Explorer → SQL Project Power Tools → Manage code analysis rules.

From the dialog you can:

• Enable or disable code analysis on build — a checkbox at the top controls whether analysis runs as part of your build
• Install community analyzers - If these packages, that add more than 130 additional rules, are not present, add them to the project.
• Search for rules — find rules by ID, description, or category using the search box
• Link to detailed documentation - use the ? link to better understand the rules, with examples and more
• Filter by severity — quickly see which rules are configured as errors vs. warnings
• Enable or disable individual rules — toggle rules with checkboxes
• Change rule severity — set individual rules to Warning or Error using a drop-down
• Manage rules by category — enable or disable entire categories at once using the group checkbox
• Reset to defaults — revert all settings with a single button click

Select OK to save your changes to the project file, or Cancel to discard.

What is SQL Database Project Power Tools?

If you're not familiar with the extension, SQL Database Project Power Tools is a free, open-source Visual Studio (and SSMS) extension that enhances your experience when working with SQL Server database projects. Key features include:

• Templates - for Visual Studio, project and item templates to get you started quickly.
• Database Import — Import an existing database schema into your project, automatically generating all necessary SQL scripts organized by object type
• Visual Schema Compare — Compare your project with a database to see what needs to be deployed, or compare a database with your project to update your files
• Static Code Analysis — Find potential issues before deployment using 150+ rules covering design, naming, performance, and security
• E/R Diagrams — Generate Mermaid diagrams showing relationships between your tables, perfect for documentation
• Table Data Scripting — Generate INSERT statements for seed data
• dacpac Explorer — Browse the contents of your built .dacpac files directly in Solution Explorer

Getting Started

1. Install the extension in Visual Studio: In Visual Studio, go to Extensions → Manage Extensions, search for "SQL Database Project Power Tools", and click Install. Or download from the Visual Studio Marketplace.

2. Install the extension in SSMS: Download from the Open VSIX Gallery. Once downloaded, double click to install.

3. Open the Rule Manager: Right-click your SQL database project → SQL Project Power Tools → Manage Code Analysis Rules.

4. Configure your rules: Enable the rules that matter to your team, set appropriate severity levels, and save.

The Rule Manager works with SDK-style SQL database projects using the MSBuild.Sdk.SqlProj community SDK or the Microsoft https://learn.microsoft.com/sql/tools/sql-database-projects/get-started?view=sql-server-ver17&pivots=sq1-visual-studio-sdk SDK.

Also Available in SSMS

The Rule Manager is also available in SQL Server Management Studio 22+ through the SSMS version of the extension. If you're taking advantage of the new Database DevOps Preview features in SSMS, you can configure your code analysis rules without leaving your favorite tool.

Summary

The visual Rule Manager removes the friction from configuring SQL code analysis. No more memorizing rule IDs or editing XML by hand. Just open the dialog, check the boxes, and save.

Combined with the schema compare, database import, and E/R diagram features, SQL Database Project Power Tools makes working with SQL database projects more productive — whether you're in Visual Studio or SSMS.

Download SQL Database Project Power Tools | View on GitHub

This post covers the general advantages of using MSBuild.Sdk.SqlProj and then dives into the container publishing workflow.

What is MSBuild.Sdk.SqlProj?

MSBuild.Sdk.SqlProj is an MSBuild SDK that produces a SQL Server Data-Tier Application package (.dacpac) from a set of SQL scripts. It provides much of the same functionality as the classic SQL Server Data Tools (SSDT) .sqlproj project format, but is built on top of SDK-style projects that were first introduced in Visual Studio 2017—the same project style used by modern .NET libraries and applications.

You can get started with a single command:

dotnet new install MSBuild.Sdk.SqlProj.Templates
dotnet new sqlproj
dotnet build

The result is a .dacpac file in your bin/Debug/net8.0 folder, ready to be deployed with SqlPackage.

General Advantages of MSBuild.Sdk.SqlProj

Works with the Standard .NET Toolchain

Because MSBuild.Sdk.SqlProj projects are SDK-style projects, they work seamlessly with the standard .NET toolchain:

• dotnet build compiles your SQL scripts and produces a .dacpac.
• dotnet pack wraps the .dacpac in a NuGet package so it can be shared and referenced by other database projects.
• dotnet publish deploys the database or publishes a container image (see below).

No special tooling, no Visual Studio requirement, and no Windows-only build agents needed.

Cross-Platform Support

Unlike the classic .sqlproj format, which has historically been tied to Windows and Visual Studio, MSBuild.Sdk.SqlProj runs on any platform that supports .NET SDK: Windows, macOS, and Linux. This means you can build and test your database schema on a Mac laptop and deploy from a Linux-based CI agent without any changes to the project file.

Version Control Friendly

All of your database schema lives in plain .sql files alongside your application code. Changes are tracked in Git with the same pull-request workflow you use for application code—complete with code review, branch policies, and history.

NuGet Package References

MSBuild.Sdk.SqlProj supports referencing other .dacpac files through NuGet package references. This makes it straightforward to share common database objects (such as reference data tables, shared schemas, or system database definitions) across multiple database projects in the same way that class libraries are shared across .NET projects.

<ItemGroup>
    <PackageReference Include="MySharedDatabasePackage" Version="1.0.0" />
</ItemGroup>

Built-in Code Analysis

The SDK integrates with DacFX rules and supports community rule packages such as ErikEJ.DacFX.SqlServer.Rules and ErikEJ.DacFX.TSQLSmellSCA. Warnings and errors surface in the standard MSBuild output or in your IDE, and you can configure which warnings to suppress or treat as errors—just like you would for C# compiler warnings.

Visual Studio and VS Code Support

Projects can be opened and edited in Visual Studio. For teams that still want the visual schema designer experience, you can keep a companion .sqlproj project alongside your SDK-style project. The SQL Project Power Tools extension enhances the experience further with project and item templates, visual schema compare, import of existing databases, E/R diagrams, and static analysis reporting.

.NET Aspire Integration

MSBuild.Sdk.SqlProj integrates with .NET Aspire via the CommunityToolkit.Aspire.Hosting.SqlDatabaseProjects package, which lets you publish SQL database projects as part of your .NET Aspire AppHost projects.

Publishing as a Container Image

Starting with MSBuild.Sdk.SqlProj version 4.0.0, you can publish your database project as a runnable container image using dotnet publish /t:PublishContainer. The image bundles both SqlPackage and your .dacpac file(s), so everything needed to deploy the schema is self-contained inside the image.

Why Use a Container Image for Database Deployment?

Container-based database deployment solves several common problems:

• Reproducibility: The exact same SqlPackage version and .dacpac are used in every environment—dev, test, staging, and production.
• No tool installation: The deployment pipeline does not need SqlPackage or the .NET SDK installed. Only a container runtime (Docker, Kubernetes, etc.) is required.
• Portability: The image can be pushed to any container registry and pulled from any environment that can run containers, making it ideal for Kubernetes-based deployments and GitOps workflows.
• Immutability: Each release of your database schema produces a new, tagged container image. You can roll back to a previous image the same way you roll back an application image.

Building and Publishing the Container Image

To publish your database project as a container image, run:

dotnet publish /t:PublishContainer

By default, the image is tagged with the name of your project. You can customize the repository name and tag using properties on the command line:

dotnet publish /t:PublishContainer /p:ContainerRepository=my-database-image /p:ContainerImageTag=v1.0.0

Or set them in your project file:

<Project Sdk="MSBuild.Sdk.SqlProj/4.0.0">
  <PropertyGroup>
    <ContainerRepository>my-database-image</ContainerRepository>
    <ContainerImageTag>v1.0.0</ContainerImageTag>
  </PropertyGroup>
</Project>

Note: By default, the published container will contain the latest version of SqlPackage available at the time of publishing. To pin a specific version, set the SqlPackageDownloadUrl property to the download URL for the Linux .NET 8 version from the SqlPackage release notes. For example:

<SqlPackageDownloadUrl>https://go.microsoft.com/fwlink/?linkid=2338525</SqlPackageDownloadUrl>

Important: Since SqlPackage currently only supports x64 architecture, the container image is also x64. Only Linux-based containers are supported at this time.

Running the Container Image

Once the image is built and pushed to a registry, deploy your database by running the image and providing a connection string:

docker run --rm my-database-image:v1.0.0 /TargetConnectionString="<your-connection-string>"

The container is preconfigured to call SqlPackage with /Action:Publish and /SourceFile:<your-dacpac-file>.dacpac. Any additional SqlPackage parameters—such as /p:BlockOnPossibleDataLoss=True or /Variables:MyVar=value—can be appended to the docker run command. For a full list of available parameters, see the SqlPackage documentation.

Example CI/CD Workflow

A typical pipeline using the container publishing approach looks like this:

1. Build and test – dotnet build validates the schema and runs code analysis.

2. Publish image – dotnet publish /t:PublishContainer /p:ContainerRepository=myregistry.azurecr.io/my-database /p:ContainerImageTag=$(BuildNumber) builds and pushes the image to a container registry.

3. Deploy – A release job runs the container image against the target SQL Server instance:

   docker run --rm myregistry.azurecr.io/my-database:$(BuildNumber) \
     /TargetConnectionString="$(ConnectionString)"
   

This pattern works with any CI/CD platform that supports Docker, including GitHub Actions, Azure Pipelines, GitLab CI, and Jenkins.

Getting Started

To try container publishing yourself:

1. Install the SDK templates:

   dotnet new install MSBuild.Sdk.SqlProj.Templates
   

2. Create a new project:

   dotnet new sqlproj -n MyDatabase
   cd MyDatabase
   

3. Add your SQL schema files and build:

   dotnet build
   

4. Publish as a container image:

   dotnet publish /t:PublishContainer
   

For more details on all available features, see the MSBuild.Sdk.SqlProj README.