You may already have used Docker to run SQL Server within a container if you work with SQL Server.

Docker isn't the only choice, though. Another container program that can run Docker-compatible container images is Podman. This tutorial will demonstrate how to use Podman Desktop on Windows to run SQL Server 2025.

Additionally, we will use SQL Server Management Studio (SSMS) to connect to SQL Server and ensure that our database data is preserved between container restarts.

What is Podman?
Podman is an open-source tool for running and managing containers.
If you have used Docker before, Podman will feel very familiar.

For example, with Docker we use:
docker pull
docker run
docker ps
docker stop

With Podman, the commands are almost the same:
podman pull
podman run
podman ps
podman stop

One important difference is that Podman does not require a central daemon like Docker.
Podman also supports rootless containers, which means containers can run without giving them unnecessary root permissions.

For developers, the main point is simple:
We can run the official Microsoft SQL Server container image using Podman.

What Do We Need?
For this example, we need:

• Windows 10 or Windows 11
• WSL 2
• Podman Desktop
• SQL Server Management Studio (SSMS)
• Enough RAM and disk space to run SQL Server

Step 1: Install WSL 2
Podman needs a Linux environment because the SQL Server container runs on Linux.
Open PowerShell as Administrator and check WSL:

wsl --status

If WSL is not installed, you can install it using:
wsl --install

If it is already installed, you can update it:
wsl --update

Restart Windows if required.

Step 2: Install Podman Desktop
Download and install Podman Desktop on your Windows machine.
During the setup, you can use WSL 2 as the machine provider.
Once the installation is complete, open PowerShell and run:
podman --version

You can also check the Podman machine:
podman machine list

If the machine is stopped, start it:
podman machine start

Now Podman is ready.

Step 3: Test Podman
Before installing SQL Server, let's make sure Podman is working.

Run:
podman run --rm hello-world
Podman will download the test image and run it.
If this works successfully, we can move to SQL Server.

Step 4: Download the SQL Server 2025 Image
Microsoft provides official SQL Server container images.

Run:
podman pull mcr.microsoft.com/mssql/server:2025-latest

The image is quite large, so downloading it may take some time depending on your internet connection.

After the download finishes, check it using:
podman images

You should see the SQL Server image in the list.

Step 5: Create Storage for SQL Server
Database data is important.
We don't want our database to disappear if we delete and recreate the container.

Let's create a Podman volume:
podman volume create sql2025data

Check the volume:
podman volume ls

We will use this volume to store SQL Server database files.

Step 6: Run SQL Server 2025
Now let's create our SQL Server container.

Open PowerShell and run:
podman run -d `
  --name sql2025 `
  --hostname sql2025 `
  -e ACCEPT_EULA=Y `
  -e MSSQL_SA_PASSWORD="YourStrongPasswordHere" `
  -p 1433:1433 `
  -v sql2025data:/var/opt/mssql `
  mcr.microsoft.com/mssql/server:2025-latest

Replace:
YourStrongPasswordHere

with a strong password.

Let's understand the command.
--name sql2025 gives our container the name sql2025.
ACCEPT_EULA=Y accepts the Microsoft SQL Server license agreement.
MSSQL_SA_PASSWORD sets the password for the SQL Server sa account.
-p 1433:1433 makes SQL Server available on port 1433.
-v sql2025data:/var/opt/mssql stores our SQL Server data in the Podman volume.

Step 7: Check SQL Server
Run:
podman ps

You should see the sql2025 container running.

You can also check SQL Server logs:
podman logs sql2025

SQL Server may take a few seconds to start.

If the container stops automatically, run:
podman ps -a

Then check the logs:
podman logs sql2025

The logs normally tell you why SQL Server failed to start.

Step 8: Connect from SSMS
Now open SQL Server Management Studio.
Enter:
Server Name: 127.0.0.1,1433

Authentication:
SQL Server Authentication

Login:
sa

Password:
Your SQL Server password

Click Connect.

You should now be connected to SQL Server 2025 running inside your Podman container.

Step 9: Check the SQL Server Version
Open a new query window in SSMS and run:
SELECT @@VERSION;

This will show the SQL Server version running inside the container.

You can also check the server name:
SELECT @@SERVERNAME;

Step 10: Create a Test Database
Let's create a simple database.

Run:
CREATE DATABASE PodmanDemo;
GO

Now use the database:
USE PodmanDemo;
GO

Create a table:
CREATE TABLE Employees
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(100)
);
GO

Add some data:
INSERT INTO Employees (Name, Department)
VALUES
('John', 'Development'),
('Sarah', 'Database'),
('Mike', 'DevOps');
GO

Check the data:
SELECT *
FROM Employees;

Our SQL Server database is now running successfully inside Podman.

Step 11: Stop and Start SQL Server
You don't need to create the container every time.

To stop SQL Server:
podman stop sql2025

To start it again:
podman start sql2025

To restart it:
podman restart sql2025

After starting the container again, connect from SSMS and run:
USE PodmanDemo;

SELECT *
FROM Employees;

Your data should still be there.

Running Multiple SQL Server Containers
One useful feature of containers is that we can easily run multiple SQL Server environments.

Our first SQL Server uses port:
1433

We can run another SQL Server container using port 1434.

For example:
podman run -d `
  --name sql2025-test `
  -e ACCEPT_EULA=Y `
  -e MSSQL_SA_PASSWORD="YourStrongPasswordHere" `
  -p 1434:1433 `
  mcr.microsoft.com/mssql/server:2025-latest

Now we have two SQL Server instances.

The first one can be accessed using:
127.0.0.1,1433

And the second one using:
127.0.0.1,1434

This is very useful when you need separate development and testing databases.
Useful Podman Commands

Here are some commands you will probably use often.

Check running containers:
podman ps

Check all containers:
podman ps -a

Check SQL Server logs:
podman logs sql2025

Watch logs continuously:
podman logs -f sql2025

Stop SQL Server:
podman stop sql2025

Start SQL Server:
podman start sql2025

Restart SQL Server:
podman restart sql2025

Check CPU and memory usage:
podman stats

Open the Linux shell inside the SQL Server container:
podman exec -it sql2025 bash

Remove the container:
podman rm sql2025

Podman Desktop or Command Line?
Podman Desktop provides a graphical interface where you can see:

• Containers
• Images
• Volumes
• Pods
• Logs
• Podman machines

This is useful when you are getting started.

However, I prefer using commands for most development and DevOps work.

Commands are easy to save in documentation or scripts and can be reused on another machine.

Podman vs Docker
Podman and Docker solve a similar problem: they allow us to run applications inside containers. For basic SQL Server development, both work well.
Docker has a very large ecosystem and is supported by many development and CI/CD tools.

Podman has some interesting advantages. It is open source, supports rootless containers, does not require a central Docker-style daemon, and its commands are very similar to Docker.

If you already know Docker, learning Podman is quite easy.

For example:
docker ps

becomes:
podman ps

and:
docker run

becomes:
podman run

So you don't have to learn everything again.

Common Problems
SQL Server Container Stops Immediately
Check the logs:
podman logs sql2025

One common reason is that the sa password does not meet SQL Server password requirements.

SSMS Cannot Connect
First check that the container is running:
podman ps

Then try connecting using:
127.0.0.1,1433

Port 1433 Is Already in Use
Another SQL Server instance may already be using port 1433.

Use another port:
-p 1434:1433

Then connect using:
127.0.0.1,1434

Database Data Is Lost
Don't store important databases only inside the container.

Use a Podman volume:
-v sql2025data:/var/opt/mssql

This keeps the database files separate from the container.

When Should You Use Podman?
Podman is a good option if you want:

• An alternative to Docker Desktop
• Rootless containers
• An open-source container tool
• Local SQL Server development environments
• Separate SQL Server environments for development and testing
• A container tool that works well with Linux and Kubernetes environments

It is also useful for developers who want to learn more about container technologies without depending only on Docker.

Conclusion
Running SQL Server 2025 using Podman is quite simple. We installed Podman Desktop, downloaded Microsoft's official SQL Server image, created persistent storage, started SQL Server, and connected to it using SSMS. The best part is that developers who already know Docker will find Podman very familiar.  Commands such as pull, run, ps, stop, and start work almost the same way. For local development, testing, database migration testing, and DevOps environments, running SQL Server with Podman is a useful option. Docker is still a great container platform, but Podman gives us another good choice, especially if we want an open-source, daemonless, and rootless container solution.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Several teams frequently collaborate inside the same database in contemporary engineering firms. Over time, inconsistent SQL writing has become the largest performance bottleneck rather than wasteful queries. Subtle friction is introduced by disparate name standards, formatting styles, and query patterns. These variations appear innocuous on their own. When taken as a whole, they raise cognitive burden, impede growth, and make systems more difficult to sustain.

This article explains the importance of SQL consistency at scale and how teams can use automation and organized procedures to successfully enforce it.

The Hidden Cost of SQL Inconsistency
Inconsistent SQL does not break systems immediately. Instead, it introduces ongoing inefficiencies that affect daily engineering work.
Common issues include:

• Naming inconsistencies: CustomerId, customer_id, and cust_id all refer to the same concept but require mental translation.
• Formatting differences: Variations in indentation, casing, and query structure slow down code reviews.
• Multiple solutions to the same problem: Different developers implement similar logic in different ways, increasing maintenance complexity.

Example:
-- Version 1
SELECT customer_id, MAX(order_date)
FROM orders
GROUP BY customer_id;

-- Version 2
SELECT o.customer_id, o.order_date
FROM orders o
WHERE o.order_date = (
    SELECT MAX(order_date)
    FROM orders
    WHERE customer_id = o.customer_id
);

Both queries produce the same result, but inconsistent approaches make code harder to standardize and maintain.

Why Documentation Alone Fails
Most teams maintain SQL style guides. However, documentation alone is not enough to enforce consistency.

Typical challenges include:

• Different interpretations of the same rules
• Reliance on senior developers for decisions
• Variations introduced by different tools and editors

As a result, pull requests often turn into style discussions instead of focusing on correctness and performance.

Moving from Guidelines to Enforcement
To achieve consistency, rules must be embedded into the development workflow.

This includes:

• Defining a shared formatting standard
• Enforcing naming conventions automatically
• Detecting risky query patterns early
• Integrating validation into CI/CD pipelines

When enforcement is automated, consistency becomes the default rather than a manual effort.

Practical Implementation
1. Standardized Formatting
Use a consistent formatting style across all SQL queries.
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;

This ensures readability and reduces review time.

2. Naming Conventions
Define and enforce rules such as:

• Use snake_case for table and column names
• Avoid abbreviations unless standardized
• Use consistent prefixes for keys (e.g., customer_id)

3. Automated Validation in CI/CD
Integrate SQL linting and validation into your pipeline:

• Reject queries that do not follow formatting rules
• Flag missing WHERE clauses in UPDATE/DELETE
• Prevent full table scans on large datasets

4. Safe Query Practices
Introduce safeguards for shared environments:
-- Risky
SELECT * FROM large_table;

-- Safer
SELECT * FROM large_table LIMIT 100;

This prevents accidental performance degradation.

Impact on Code Reviews and Onboarding
When SQL is standardized:

• Reviews focus on logic, not formatting
• Feedback cycles become faster
• New developers ramp up quickly

Without consistency, developers spend time interpreting structure instead of solving problems.

Before vs After

Strategic Takeaways

• Consistency is not cosmetic; it directly impacts scalability
• Rules must be enforceable, not just documented
• Automation reduces human error and review overhead
• SQL should follow the same discipline as application code

Conclusion
Shared databases are more complicated as businesses expand. Small variations in SQL add up to major operational difficulties in the absence of uniform standards. Teams may increase cooperation, lower friction, and maintain high-quality database systems by establishing explicit rules and integrating them into automated procedures. Clean code is only one aspect of consistency. It involves creating mechanisms that effectively grow with your team.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

For essential functions like authentication, transactions, reporting, and business processes, modern.NET applications frequently rely on SQL Server. Network disruptions, connection pool pressure, database failovers, or transitory infrastructure problems can cause temporary failures even when an application and database are configured appropriately.

An application can recover from these transient errors without promptly delivering an error to the user by using a retry strategy. Microsoft.Data.The built-in retry features of the SqlClient library may be set up for SQL Server connections and procedures. This article describes the operation of retry logic, how to set it up in a.NET application, when to use it, and what production errors to prevent.

What Is Retry Logic in Microsoft.Data.SqlClient?
Retry logic allows Microsoft.Data.SqlClient to automatically retry certain operations when a transient SQL Server failure occurs.

Instead of following this pattern:
Application
    ↓
SQL Server
    ↓
Temporary failure
    ↓
Application returns error

a retry-enabled application can follow:
Application
    ↓
SQL Server
    ↓
Temporary failure
    ↓
Wait
    ↓
Retry
    ↓
SQL Server
    ↓
Success

This is particularly useful for cloud-hosted databases and distributed applications where temporary connectivity problems can occur. Retry logic is not intended to hide permanent failures. For example, an invalid SQL statement, authentication failure, or missing table generally cannot be fixed by repeatedly executing the same operation.

Why Transient SQL Failures Happen
Transient failures are temporary conditions where the same operation may succeed if attempted again after a short delay.

Common examples include:

The important distinction is between transient and non-transient failures.

Configuring Retry Logic with Microsoft.Data.SqlClient
The retry functionality is available through Microsoft.Data.SqlClient.

A basic connection can be created like this:
using Microsoft.Data.SqlClient;

var connectionString =
    "Server=localhost;Database=SalesDb;Integrated Security=True;TrustServerCertificate=True;";

using var connection = new SqlConnection(connectionString);

await connection.OpenAsync();

To add retry behavior, configure a retry provider and assign it to the connection.

For example:
using Microsoft.Data.SqlClient;

var connectionString =
    "Server=localhost;Database=SalesDb;Integrated Security=True;TrustServerCertificate=True;";

var retryProvider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(
    retryCount: 5,
    maxTimeInterval: TimeSpan.FromSeconds(10),
    deltaTime: TimeSpan.FromSeconds(1));

using var connection = new SqlConnection(connectionString)
{
    RetryLogicProvider = retryProvider
};

await connection.OpenAsync();

Here, the retry provider controls how the client responds when a retryable transient error occurs.

Understanding the Retry Parameters
The following values are important:
SqlConfigurableRetryFactory.CreateExponentialRetryProvider(
    retryCount: 5,
    maxTimeInterval: TimeSpan.FromSeconds(10),
    deltaTime: TimeSpan.FromSeconds(1));

retryCount specifies how many retry attempts can be made.
maxTimeInterval limits the maximum delay between retry attempts.
deltaTime controls the delay growth used by the exponential retry strategy.
The goal is to avoid immediately sending the same failed request repeatedly.

Exponential Backoff and Why It Matters
A fixed retry strategy might look like this:
Attempt 1 → Fail
Wait 1 second
Attempt 2 → Fail
Wait 1 second
Attempt 3 → Fail

This can put unnecessary pressure on an already struggling database.

Exponential backoff gradually increases the waiting period:
Attempt 1 → Fail
Wait
Attempt 2 → Fail
Wait longer
Attempt 3 → Fail
Wait even longer
Attempt 4 → Success

This gives the underlying infrastructure time to recover.

In distributed systems, exponential backoff is generally more appropriate than aggressively retrying the same request without delay.
Using Retry Logic with ASP.NET Core

In an ASP.NET Core application, it is common to centralize database configuration instead of creating connections throughout the application.

For example:
using Microsoft.Data.SqlClient;

var builder = WebApplication.CreateBuilder(args);

var connectionString =
    builder.Configuration.GetConnectionString("DefaultConnection");

var retryProvider =
    SqlConfigurableRetryFactory.CreateExponentialRetryProvider(
        retryCount: 5,
        maxTimeInterval: TimeSpan.FromSeconds(10),
        deltaTime: TimeSpan.FromSeconds(1));

builder.Services.AddScoped<SqlConnection>(_ =>
{
    return new SqlConnection(connectionString)
    {
        RetryLogicProvider = retryProvider
    };
});

var app = builder.Build();

app.MapGet("/products", async (SqlConnection connection) =>
{
    await connection.OpenAsync();

    using var command = new SqlCommand(
        "SELECT Id, Name, Price FROM Products",
        connection);

    using var reader = await command.ExecuteReaderAsync();

    var products = new List<object>();

    while (await reader.ReadAsync())
    {
        products.Add(new
        {
            Id = reader.GetInt32(0),
            Name = reader.GetString(1),
            Price = reader.GetDecimal(2)
        });
    }

    return Results.Ok(products);
});

app.Run();

The main benefit of this approach is consistency. Database connections created through the configured dependency-injection registration receive the same retry configuration.
For larger applications, you would typically keep database access inside a repository or data-access service rather than placing SQL directly inside an endpoint.

Connection Retry vs Command Retry
One important aspect of Microsoft.Data.SqlClient retry logic is understanding what is actually being retried.

A database operation can involve:

• Opening a connection
• Executing a command
• Reading results
• Committing a transaction

Retry behavior must therefore be considered carefully, particularly for commands that modify data.

A read operation such as:
SELECT Id, Name
FROM Products
WHERE Id = @id

is generally easier to retry safely.

A write operation such as:
UPDATE Accounts
SET Balance = Balance - @amount
WHERE Id = @id

requires more consideration.

If the server processed the update but the client lost the connection before receiving the response, blindly retrying could potentially execute the business operation again.

The key issue is idempotency.

Designing Retry-Safe Database Operations
Before enabling aggressive retry behavior for write operations, determine whether repeating the operation produces the same intended result.

For example, this operation is not naturally idempotent:
UPDATE Accounts
SET Balance = Balance - 100
WHERE Id = 10;

Running it twice subtracts the amount twice.
A better design can use an operation identifier or another mechanism that allows the application to recognize an already-processed request.

For example:
INSERT INTO PaymentOperations
(
    OperationId,
    AccountId,
    Amount
)
VALUES
(
    @OperationId,
    @AccountId,
    @Amount
);

A unique constraint on OperationId can help prevent duplicate processing. The exact implementation depends on the application's business requirements, but the principle is important:
Retrying infrastructure operations is different from safely retrying business operations.

Retry Logic Compared with Polly
.NET applications have traditionally used libraries such as Polly for resilience strategies. Microsoft.Data.SqlClient retry logic and Polly can solve related but different problems.

If your requirement is specifically SQL Server client retry behavior, the built-in Microsoft.Data.SqlClient functionality can be a straightforward choice. If you need a broader resilience pipeline covering HTTP calls, caching, messaging, database operations, and other dependencies, an application-level resilience library may be more appropriate.

The two approaches should not be added blindly on top of each other. Layering multiple retry policies can result in unexpectedly large numbers of attempts.

Best Practices for Production
1. Keep Retry Counts Reasonable
More retries do not automatically mean better reliability.
A request that fails repeatedly can hold application resources and increase latency.
Start with conservative values and adjust them based on the application's requirements.

2. Use Exponential Backoff
Avoid immediate retry loops.
An exponential strategy gives SQL Server and the underlying infrastructure time to recover.

3. Do Not Retry Every Exception
Retry logic should only apply to failures that are actually transient.
An authentication problem will not normally be solved by five more login attempts.

4. Consider Operation Idempotency
Pay particular attention to:

• Payments
• Account balances
• Inventory updates
• Order creation
• Message processing
• Other state-changing operations

A retry can turn a temporary connection problem into a duplicate business operation if the design is not idempotent.

5. Monitor Retries
Retries can hide problems if they are not observable. Track useful information such as:

1. Number of retry attempts
2. Operation duration
3. Final failure
4. Database server
5. Exception type
6. Application endpoint or operation

A system that succeeds only after multiple retries may appear healthy to users while the underlying database infrastructure is experiencing problems.

Common Mistakes
Setting an Excessive Retry Count
A configuration such as:
retryCount: 50

may keep requests alive for an unnecessarily long time.

Retry policies should be based on the application's acceptable latency and failure-recovery requirements.

Retrying Permanent Errors
Retrying invalid SQL repeatedly wastes resources.
Fix the underlying problem instead of increasing the retry count.

Adding Multiple Retry Layers
For example:
HTTP retry
    ↓
Service retry
    ↓
Repository retry
    ↓
SqlClient retry

A single failed database operation can potentially trigger many actual database requests.
Define clear ownership of retry behavior.

Ignoring Transactions
Transactions require special attention because a connection failure does not always tell the client whether the server completed the transaction. Do not assume that an unsuccessful client response means that the database definitely rolled back the operation.

Troubleshooting Retry Behavior
When retry logic does not behave as expected, check the following:

• Verify that the application is using Microsoft.Data.SqlClient, not an unrelated SQL client package.
• Confirm that the retry provider is assigned to the connection or command configuration being used.
• Check the actual exception and SQL error information.
• Verify that the failure is transient and eligible for retry.
• Check application logs for repeated attempts.
• Review connection and command timeout settings.
• Check SQL Server health, networking, resource utilization, and failover events.
• Make sure another resilience layer is not already retrying the same operation.

Logging should make it possible to distinguish an operation that succeeded immediately from one that succeeded only after several retries.

Advantages and Disadvantages
Advantages

• Built specifically for SQL Server client operations
• Reduces failures caused by temporary connectivity problems
• Supports configurable retry behavior
• Exponential retry strategies reduce aggressive retry traffic
• Can simplify SQL-specific resilience configuration
• Works naturally with Microsoft.Data.SqlClient

Disadvantages

• Does not solve permanent database failures
• Excessive retries can increase application latency
• Retrying write operations can introduce duplicate business operations
• It does not replace broader application resilience patterns
• Multiple retry layers can create unexpectedly high request counts

Conclusion
Transient database failures are a normal consideration for distributed .NET applications, particularly when applications depend on remote or cloud-hosted SQL Server environments. Microsoft.Data.SqlClient provides a practical way to introduce SQL-aware retry behavior without implementing every retry mechanism manually. Exponential backoff, sensible retry limits, correct transient-error handling, and good observability are the key pieces of a reliable implementation.

However, retry logic should not be treated as a universal solution. The most important production consideration is understanding what is being retried and whether repeating that operation is safe. For read-heavy workloads, carefully configured SqlClient retry logic can provide useful resilience against temporary database connectivity problems. For write-heavy or transactional workloads, combine retry configuration with idempotent application design, transaction awareness, monitoring, and an overall resilience strategy.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

How SQL Server Uses the Transaction Log to Asynchronously Record Row Modifications
Tracking row alterations (INSERT, UPDATE, and DELETE) is crucial for auditing, event streaming, and downstream synchronization in high-throughput business relational databases. Although custom timestamp columns or application triggers have typically been utilized for this purpose, they result in severe lock contention and transaction complexity.

An asynchronous, log-based substitute is offered by SQL Server's Change Data Capture (CDC). Without stopping ongoing user transactions, CDC pulls mutations from the transaction log by working directly at the database engine level.

1. Internal Infrastructure: The cdc Schema
When CDC is enabled on a database and a target table using system stored procedures, SQL Server automatically creates a dedicated system schema named cdc.
-- Step 1: Enable CDC at Database Level
EXEC sys.sp_cdc_enable_db;
GO

-- Step 2: Enable CDC on a Specific Table
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Orders',
@role_name     = NULL; -- Optional gating security role
GO

System Objects Generated
Enabling CDC initializes several metadata objects inside the database:

• cdc.change_tables: Stores a registry of all tracked tables, their capture instances, and initial Log Sequence Number (LSN) boundaries.
• cdc.captured_columns: Maps every source column to its corresponding position in the change tables.
• cdc.lsn_time_mapping: Maps binary LSN values to real-world DATETIME2 execution timestamps.
• cdc.dbo_Orders_CT (The Change Table): A shadow table dedicated specifically to dbo.Orders that acts as the physical repository for all row modification histories.

2. The Internal Data Pipeline
CDC operates asynchronously to avoid impacting user write latency. The engine uses a background log-scanning mechanism rather than synchronous triggers.
Image 05-08-26 at 9.22 PM

Execution Steps

• Transaction Logging: When a transaction updates a tracked table, SQL Server writes the operational log record to the active transaction log file (.ldf) as normal and assigns it a sequential Log Sequence Number (LSN).
• Log Scanning (sys.sp_cdc_scan): A dedicated SQL Server Agent job continually runs sys.sp_cdc_scan. This process reads committed log records associated with CDC-enabled tables asynchronously.
• Shadow Table Insertion: The capture job parses the before- and after-images of modified rows from the transaction log and appends them to the corresponding cdc.<capture_instance>_CT table.

3. Dissecting the Change Table (_CT) Schema

The auto-generated change table mirrors the data columns of the source table, augmented with five system metadata columns at the front:

DML Representation Example

When an UPDATE operation changes an order amount from $150.00 to $200.00, CDC writes two rows to the change table:

4. Querying Captured Data Programmatically
Directly querying cdc.<capture_instance>_CT tables is discouraged because underlying schema metadata can shift. Instead, SQL Server automatically generates system Table-Valued Functions (TVFs).

A. Fetching All Detailed Changes
To extract every historical row mutation between two LSN boundaries:
-- 1. Determine active LSN boundaries
DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_Orders');
DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

-- 2. Extract detailed change records
SELECT
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS CommitTime,
    CASE __$operation
        WHEN 1 THEN 'DELETE'
        WHEN 2 THEN 'INSERT'
        WHEN 3 THEN 'UPDATE (Before)'
        WHEN 4 THEN 'UPDATE (After)'
    END AS Operation,
    OrderID,
    CustomerName,
    Amount
FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, N'all')
ORDER BY __$start_lsn, __$seqval;

B. Fetching Net Changes
If a single record undergoes dozens of updates within a processing window, running fn_cdc_get_net_changes collapses intermediate states and returns only the final net state of the modified row:
SELECT
    CASE __$operation
        WHEN 1 THEN 'DELETE'
        WHEN 2 THEN 'INSERT'
        WHEN 4 THEN 'UPDATE'
    END AS NetOperation,
    OrderID,
    CustomerName,
    Amount
FROM cdc.fn_cdc_get_net_changes_dbo_Orders(@from_lsn, @to_lsn, N'all');

5. Retention and Storage Management
Because change tables continuously capture row history, SQL Server provisions an automated Cleanup Job (sys.sp_cdc_cleanup_change_table).

Default Retention: 4,320 minutes (3 days).

Cleanup Execution: The cleanup job evaluates the low-watermark LSN corresponding to the retention threshold and purges expired entries from the _CT tables.

Transaction Log Impact: Until the CDC capture job scans a log record, SQL Server cannot truncate that portion of the active transaction log (.ldf), even after a log backup. Ensuring low capture latency is crucial to prevent log bloat.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Learning Objectives
By the end of this article, you'll learn how to:

• Create a file upload component in Angular
• Handle file selection using the change event
• Store the selected file in the component
• Write unit tests for file input controls
• Simulate file selection in Angular unit tests

HTML Template
<input
  id="myFile"
  type="file"
  (change)="onFileSelected()"
  #fileInput
/>

Whenever the user selects a file, the change event calls the onFileSelected() method.

Component
import { Component } from '@angular/core';

@Component({
  selector: 'app-input-file',
  templateUrl: './input-file.component.html'
})
export class InputFileComponent {

  uploadedFile!: File;

  onFileSelected(): void {

    const inputNode = document.querySelector('#myFile') as HTMLInputElement;

    if (inputNode.files && inputNode.files.length > 0) {
      this.uploadedFile = inputNode.files[0];
      console.log(this.uploadedFile);
    }

  }

}

How It Works
When the user selects a file:

• The component locates the file input element.
• The browser stores the selected file(s) in the files collection.
• The first file is assigned to the uploadedFile property.

Although this works, Angular recommends avoiding direct DOM access where possible.

Writing the Unit Test
The browser normally populates the files property, so during unit testing we need to mock it ourselves.
import { ComponentFixture, TestBed } from '@angular/core/testing';
import { InputFileComponent } from './input-file.component';

describe('InputFileComponent', () => {

  let component: InputFileComponent;
  let fixture: ComponentFixture<InputFileComponent>;

  beforeEach(async () => {

    await TestBed.configureTestingModule({
      declarations: [InputFileComponent]
    }).compileComponents();

    fixture = TestBed.createComponent(InputFileComponent);
    component = fixture.componentInstance;
    fixture.detectChanges();

  });

  it('should store the selected file', () => {

    const file = new File(
      ['Dummy Content'],
      'sample.txt',
      {
        type: 'text/plain'
      }
    );

    const input = fixture.nativeElement.querySelector('#myFile');

    Object.defineProperty(input, 'files', {
      value: [file]
    });

    input.dispatchEvent(new Event('change'));

    expect(component.uploadedFile).toEqual(file);

  });

});

Understanding the Test
Step 1: Create a Mock File
const file = new File(
  ['Dummy Content'],
  'sample.txt',
  {
    type: 'text/plain'
  }
);

This creates a fake File object that behaves exactly like a file selected by the user.

Step 2: Mock the Browser's files Property
Object.defineProperty(input, 'files', {
  value: [file]
});

Since the browser owns the files property, we replace it with our mock file during testing.

Step 3: Simulate File Selection
input.dispatchEvent(new Event('change'));

This triggers the same event that occurs when a user selects a file.

Step 4: Verify the Result
expect(component.uploadedFile).toEqual(file);

The test passes if the component correctly stores the selected file.

A Better Angular Approach
Instead of querying the DOM with document.querySelector(), Angular encourages passing the event object directly.
<input
  type="file"
  (change)="onFileSelected($event)"
/>

Component
onFileSelected(event: Event): void {

  const input = event.target as HTMLInputElement;

  if (!input.files?.length) {
    return;
  }

  this.uploadedFile = input.files[0];

}

This approach is:

• More Angular-friendly
• Easier to unit test
• Doesn't directly access the DOM
• Better for maintainability

Unit Test for the Improved Version
it('should store the selected file', () => {

  const file = new File(
    ['Angular Testing'],
    'document.pdf',
    {
      type: 'application/pdf'
    }
  );

  const event = {
    target: {
      files: [file]
    }
  } as unknown as Event;

  component.onFileSelected(event);

  expect(component.uploadedFile).toEqual(file);

});

Notice that we no longer need to manipulate the DOM. We simply create a mock event object and call the component method directly, making the unit test cleaner and easier to understand.

Conclusion
Unit testing file uploads in Angular is straightforward once you know how to mock the browser's files property. While older implementations often relied on document.querySelector(), modern Angular applications should use the event object passed by the change event. This results in cleaner code, simpler unit tests, and components that are easier to maintain.

The overall testing strategy is simple:

• Create a mock File object.
• Assign it to the input's files property (or pass it through the event object).
• Trigger the change event.
• Verify that the component stores the selected file correctly.

Following this approach will help you confidently test file upload functionality in any Angular application.

Summary
Testing file uploads in Angular involves simulating the browser's file selection behavior by creating mock File objects and triggering the change event. While directly accessing the DOM with document.querySelector() works, passing the event object to the component method is the recommended Angular approach because it improves maintainability, simplifies unit testing, and reduces coupling between the component and the DOM.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

One of the most crucial SQL Server speed optimization strategies is the use of indexes. Well-designed indexes may greatly speed up query execution and increase application responsiveness in Online Transaction Processing (OLTP) systems, where thousands of transactions may take place per second.

Indiscriminately creating indexes, however, might complicate maintenance, slow down insert and update processes, and increase storage utilization. Making indexes that minimize overhead while supporting the most popular query patterns is the aim.

In this article, you'll learn practical indexing strategies for large OLTP systems, understand different index types, analyze execution plans, and follow a structured methodology for evaluating indexing changes.

Note: This article focuses on indexing strategies and execution plan analysis. Performance improvements vary depending on workload, schema, hardware, and data distribution.

Why Indexes Matter
Without an index, SQL Server typically scans an entire table to locate matching rows.

Application
      │
      ▼
 SQL Query
      │
      ▼
Table Scan
      │
      ▼
Slow Response

With a properly designed index:

Application
      │
      ▼
 SQL Query
      │
      ▼
 Index Seek
      │
      ▼
Fast Response

An Index Seek usually requires significantly less work than a Table Scan, especially for large tables.

Clustered vs Nonclustered Indexes

SQL Server supports several index types.

Choosing the correct index depends on how the application queries the data.

Clustered Index
A table can have only one clustered index.

Example:
CREATE TABLE Orders
(
    Id INT PRIMARY KEY CLUSTERED,
    CustomerId INT,
    OrderDate DATETIME,
    Total DECIMAL(18,2)
);

Clustered indexes work well for:

• Primary keys
• Sequential inserts
• Range queries

Avoid frequently changing clustered key values because updating them affects the physical row order.

Nonclustered Index
Suppose orders are frequently searched by customer.
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON Orders(CustomerId);

Instead of scanning the entire table, SQL Server can locate matching rows using the index.

Composite Indexes
Queries often filter on multiple columns.

Example:
SELECT *
FROM Orders
WHERE CustomerId = 10
AND OrderDate >= '2026-01-01';

A composite index is more appropriate.
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON Orders(CustomerId, OrderDate);

The order of columns is important. Place the most selective or frequently filtered column first when it aligns with your query patterns.

Covering Indexes
Consider the following query:
SELECT CustomerId,
       OrderDate,
       Total
FROM Orders
WHERE CustomerId = 10;

Instead of performing additional key lookups, include the required columns.
CREATE NONCLUSTERED INDEX IX_Orders_Customer
ON Orders(CustomerId)
INCLUDE (OrderDate, Total);

A covering index allows SQL Server to satisfy the query directly from the index.
Filtered Indexes

When only a subset of rows is queried frequently, use filtered indexes.

Example:
CREATE NONCLUSTERED INDEX IX_Orders_Open
ON Orders(Status)
WHERE Status = 'Open';

Filtered indexes are smaller and can improve performance for selective workloads.
Avoid Over-Indexing

Every index must be maintained during:

• INSERT
• UPDATE
• DELETE

Too many indexes can slow write operations.

Instead of indexing every column, analyze real query patterns and create only the indexes that provide measurable value.

Analyze Execution Plans
Execution plans reveal how SQL Server executes queries.

Enable an actual execution plan in SQL Server Management Studio or use:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Review execution plans for:

• Table Scan
• Index Scan
• Index Seek
• Key Lookup
• Sort
• Hash Match

A frequent Table Scan on a large table often indicates a missing or ineffective index.

Missing Index Recommendations
SQL Server may suggest missing indexes during execution plan analysis.

Example recommendation:

Missing Index:

• Orders(CustomerId)
• INCLUDE(OrderDate)

Treat these suggestions as starting points rather than automatic solutions. Evaluate each recommendation against your workload before implementing it.

Fragmentation
Over time, indexes become fragmented due to insert, update, and delete operations. Common maintenance tasks include:

• Reorganize indexes
• Rebuild indexes
• Update statistics

Regular maintenance helps SQL Server generate efficient execution plans.

End-to-End Optimization Workflow
A structured tuning process includes:

• Identify slow queries.
• Capture the execution plan.
• Review scans and key lookups.
• Create or modify indexes.
• Update statistics if necessary.
• Re-test the query.
• Monitor production performance.

This workflow helps ensure indexing decisions are based on evidence rather than assumptions.

Index Type Comparison

Performance Evaluation Methodology
The research brief mentions execution plan comparisons but does not include benchmark results. To evaluate indexing changes in your own environment:

Test Environment
Maintain consistency across benchmark runs:

• SQL Server version
• Database size
• Hardware
• Application version
• Query workload
• Test Scenarios

Compare:

• No index
• Single-column index
• Composite index
• Covering index
• Filtered index
• Metrics to Collect

Measure:

• Query execution time
• Logical reads
• Physical reads
• CPU utilization
• Execution plan cost
• Insert/update duration
• Index storage size
• Useful Tools

Useful tools include:

• SQL Server Management Studio
• Actual Execution Plans
• Query Store
• SQL Server Profiler (where appropriate)

Extended Events
SET STATISTICS IO

SET STATISTICS TIME

Validate changes using production-like data volumes rather than small development datasets.

Best Practices

• Index columns frequently used in filters and joins.
• Keep indexes as narrow as possible.
• Use covering indexes for critical read queries.
• Review execution plans before adding indexes.
• Update statistics regularly.
• Monitor index fragmentation.
• Remove unused indexes.
• Validate changes under representative workloads.

Common Mistakes

Troubleshooting
Query Still Performs a Table Scan

Verify:

• Query predicates
• Index column order
• Updated statistics
• Parameter values
• Data selectivity

A scan is sometimes the optimal choice for very small tables or non-selective queries.

Insert Operations Become Slower
Review:

• Number of indexes
• Index maintenance
• Fragmentation
• Fill factor settings

Reducing unnecessary indexes often improves write performance.

High Fragmentation
Schedule regular index maintenance and monitor fragmentation levels using SQL Server's dynamic management views.

FAQs
Should every foreign key have an index?
Not always, but indexing foreign keys is often beneficial because they are commonly used in joins and filtering operations.

How many indexes should a table have?
There is no fixed number. Create indexes that support your workload while balancing read performance against write overhead.

What is a covering index?
A covering index contains all the columns required to satisfy a query, eliminating additional key lookups.

Should I always follow SQL Server's missing index suggestions?
No. Review each recommendation carefully. Some suggested indexes may duplicate existing ones or increase write costs without providing sufficient benefit.

How often should indexes be rebuilt?
The appropriate maintenance schedule depends on workload and fragmentation levels. Monitor index health regularly rather than rebuilding on a fixed schedule without analysis.

Conclusion
Effective indexing is essential for maintaining high-performance SQL Server OLTP systems. Well-designed indexes reduce query execution time, improve scalability, and minimize resource consumption, while poorly planned indexes can have the opposite effect. By understanding index types, analyzing execution plans, monitoring fragmentation, and validating changes through structured testing, you can build indexing strategies that support both fast reads and efficient transactional workloads in production environments.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

High-Scale Enterprise Use Cases, System-Versioning Patterns, and Architectural Mechanisms

Storing a database record's current state alone is no longer adequate in contemporary corporate software engineering. While corporate operations increasingly require "time-travel" capabilities to assess historical patterns, recreate former data states, and recover from unintentional data corruptions, regulations such as HIPAA, GDPR, and SOX necessitate strict auditability.

While techniques like custom audit triggers, Change Data Capture (CDC), or event sourcing exist, SQL Server’s System-Versioned Temporal Tables provide a built-in, declarative solution. By binding a current operational table to an automated system history table, temporal tables give developers point-in-time querying capabilities with zero application-level change tracking code.

1. Engine-Level Architecture: Dual-Table Binding
A temporal table consists of two separate, physically linked storage entities managed entirely by the database engine:
Current / Primary Table (dbo.Employees): Stores active, latest-state records.

History Table (dbo.EmployeesHistory): Stores superseded or deleted historical versions of records.


1. Period Columns (SysStartTime and SysEndTime)
Temporal tables require two non-nullable datetime2 columns designated as the system period. These determine the exact UTC validity window of every row version:

• SysStartTime: The precise UTC timestamp when the row version became active.
• SysEndTime: The UTC timestamp when the row version was modified or deleted. (Active rows in the main table have SysEndTime set to 9999-12-31 23:59:59.9999999).

2. DDL Implementation & Clean Schema Design
Creating a temporal table requires declaring the period columns and attaching the SYSTEM_VERSIONING table option. Marking period columns as HIDDEN prevents them from dirtying standard application SELECT * payloads.
CREATE TABLE dbo.Employees (
    EmployeeID INT NOT NULL PRIMARY KEY CLUSTERED,
    Name VARCHAR(100) NOT NULL,
    Position VARCHAR(100) NOT NULL,
    Salary DECIMAL(12, 2) NOT NULL,
    DepartmentID INT NOT NULL,

    -- Mandatory System-Versioning Period Columns
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory)
);

Automatic DML Mechanics
INSERT: The record enters dbo.Employees. SysStartTime is stamped with the UTC commit time; SysEndTime is set to max value (9999-12-31). Nothing is written to dbo.EmployeesHistory.

UPDATE: SQL Server executes a two-step atomic operation:

• The existing active row is copied to dbo.EmployeesHistory, updating its SysEndTime to the current transaction timestamp.
• The row in dbo.Employees is updated in-place with new values, updating its SysStartTime to the transaction timestamp.

DELETE: The row is removed from dbo.Employees and inserted into dbo.EmployeesHistory with SysEndTime set to the deletion timestamp.

3. Querying Temporal Tables: The Time-Travel Extensions
Applications query temporal tables by adding the FOR SYSTEM_TIME clause directly after the primary table name in T-SQL. The database engine automatically generates an execution plan that performs a UNION ALL across dbo.Employees and dbo.EmployeesHistory.

A. Point-in-Time Reconstruction (AS OF)
Retrieves the exact state of a record or entire database schema at a specific historical moment.
-- What was Employee 101's salary and position on October 15, 2025?
SELECT EmployeeID, Name, Position, Salary
FROM dbo.Employees
FOR SYSTEM_TIME AS OF '2025-10-15 09:30:00'
WHERE EmployeeID = 101;

B. Full Audit Trail (ALL)
Returns every version of a record throughout its entire lifecycle.
-- Inspect the full career and compensation history for Employee 101
SELECT
    EmployeeID,
    Position,
    Salary,
    SysStartTime AS ValidFrom,
    SysEndTime AS ValidTo
FROM dbo.Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 101
ORDER BY SysStartTime ASC;

C. Interval Filtering (BETWEEN ... AND ...)

Retrieves all row versions active at any point inside a date range.
SELECT *
FROM dbo.Employees
FOR SYSTEM_TIME BETWEEN '2026-01-01' AND '2026-06-30'
WHERE DepartmentID = 4;

4. How Enterprise Applications Leverage Temporal Tables
Enterprise Use Case 1: Regulatory Compliance & Zero-Code Auditing

In financial, healthcare, and e-commerce systems (governed by SOX, HIPAA, or PCI-DSS), auditing who altered critical data—and what the previous value was—is mandatory.

Legacy Approach: Writing explicit AFTER UPDATE, DELETE triggers on every table or relying on application-level logging libraries.

Temporal Solution: Enabling system-versioning provides an immutable, engine-managed audit trail. Because application code cannot modify or delete rows in the history table directly, audit data remains tamper-proof.

Enterprise Use Case 2: Slow Changing Dimensions (SCD Type 2) in BI & Data Warehousing
Data warehouses require tracking historical changes to dimensions (e.g., tracking a customer's address changes over time to attribute past sales to the correct territory).

Enterprise Pattern: Instead of building complex ETL pipelines to handle Type 2 SCD logic, business intelligence tools (like Power BI or Azure Synapse) run point-in-time joins (FOR SYSTEM_TIME AS OF FactTable.TransactionDate) directly against temporal dimension tables.

Enterprise Use Case 3: "Point-in-Time" Financial Calculations & Invoicing
Billing systems often need to re-calculate invoices based on historical pricing rules or retroactively audit customer balances.

Enterprise Pattern: A SaaS application computes usage metrics by joining transaction logs against PricingPlans FOR SYSTEM_TIME AS OF UsageDate. Even if a pricing tier changes today, historical invoice calculations stay accurate.

Enterprise Use Case 4: Instant Data Repair & Accidental Mass-Delete Recovery

If a bug or improper UPDATE statement without a WHERE clause corrupts 100,000 active customer records, traditional recovery requires restoring a full database backup to a staging server.

Temporal Solution: Engineers execute an in-place MERGE or UPDATE query bringing data directly back from the history table:

-- Restore current table state from 1 hour ago
MERGE INTO dbo.Employees AS target
USING (
    SELECT * FROM dbo.Employees FOR SYSTEM_TIME AS OF '2026-08-05 08:00:00'
) AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET
        target.Position = source.Position,
        target.Salary = source.Salary;

5. Enterprise Storage & Index Optimization
Because history tables grow indefinitely as data mutates, unoptimized temporal tables can bloat database storage and degrade query performance over time.

1. Indexing the History Table
For optimal performance with AS OF and interval queries, create a Clustered Columnstore Index or a composite B-Tree index on the history table structured around the period columns:
-- Optimal B-Tree Index for Time-Travel Lookups
CREATE CLUSTERED INDEX IX_EmployeesHistory_PK
ON dbo.EmployeesHistory (SysEndTime ASC, SysStartTime ASC, EmployeeID);

2. Automated History Retention Policies
SQL Server allows setting an automatic retention policy on history tables to drop historical records older than a configured threshold:
ALTER TABLE dbo.Employees
SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.EmployeesHistory,
        HISTORY_RETENTION_PERIOD = 12 MONTHS -- Automatically purges history older than 1 year
    )
);

Conclusion
SQL Server Temporal Tables transfer the burden of state history and auditability from the application logic to the database engine. Enterprise applications use declarative, maintainable SQL code to provide point-in-time time travel, smooth compliance audits, rapid data recovery, and streamlined analytical reporting by utilizing system-versioned history tables and FOR SYSTEM_TIME syntax.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

While developing an ASP.NET Core Web API using Entity Framework Core and SQL Server, one of the most common migration errors developers encounter is:

A network-related or instance-specific error occurred while establishing a connection to SQL Server.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
 Error Number:2, State:0, Class:20

This error usually appears while executing:
Update-Database

or

dotnet ef database update

Although the message appears to indicate a network issue, the actual root cause is often related to SQL Server configuration, an incorrect connection string, startup project selection, or Entity Framework Core design-time configuration.

This article explains the complete troubleshooting process used by professional .NET developers to diagnose and resolve this issue.

Understanding the Error
When Entity Framework Core executes a migration, it attempts to establish a connection with SQL Server.

If SQL Server cannot be reached using the configured connection string, the following exception is thrown:
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

This does not necessarily mean SQL Server is unavailable. It simply means the application could not establish a connection.

Step 1 – Verify SQL Server Service
First, verify that SQL Server is installed and running.

Open Command Prompt and execute:
sc query MSSQL$SQLEXPRESS

Expected Output:
SERVICE_NAME: MSSQL$SQLEXPRESS
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

If the service is stopped:
Press Win + R
Type:
services.msc

Locate SQL Server (SQLEXPRESS)
Click Start

Step 2 – Verify SQL Server Connectivity
Before debugging your application, verify that SQL Server itself is accessible.

List Installed SQL Server Instances
sqlcmd -L

Example Output:
Servers:
    DEV-SERVER\SQLEXPRESS

If no server is listed, SQL Server Browser may not be running or your SQL Server instance may not be discoverable.

Connect to SQL Server
sqlcmd -S .\SQLEXPRESS -E

or

sqlcmd -S DEV-SERVER\SQLEXPRESS -E

Successful connection:

1>

This confirms:

• SQL Server is installed
• SQL Server service is running
• Windows Authentication is working
• SQL Server accepts connections

Step 3 – Verify SQL Server Information

After connecting successfully, execute the following SQL commands in cmd .
Check Connected Server

SELECT @@SERVERNAME;
2> GO click enter
                                                                                        Output
--------------------------------------------------------------------------------------------------------------------------------
DEV-SERVER\SQLEXPRESS

Check SQL Server Version
SeLECT @@VERSION;
2> GO click enter
                                                                                        Output
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)
        Oct  8 2022 05:58:25
        Copyright (C) 2022 Microsoft Corporation
        Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 26200: ) (Hypervisor)

(1 rows affected)

Verify Current Database
SELECT DB_NAME();
GO

Expected:
master

List All Databases
SELECT name
2> FROM sys.databases
3> ORDER BY name;
4> GO click enter

Output
name
--------------------------------------------------------------------------------------------------------------------------------
1000SQLDB
model
msdb
SqlInDepth
SQLJourney
SQLPracticeQuery
tempdb
TestData

Verify Current Login
SELECT SYSTEM_USER;
2> GO click enter
                                                                                        Output
--------------------------------------------------------------------------------------------------------------------------------
DEV-SERVER\HP

(1 rows affected)

Verify Authentication Mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');
GO

Output:

Exit sqlcmd

EXIT

Step 4 – Verify the Connection String
A large number of SQL Server connection issues are caused by incorrect connection strings.

Example:
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=DEV-SERVER\\SQLEXPRESS;Database=SampleERPDB;Integrated Security=True;TrustServerCertificate=True;MultipleActiveResultSets=True"
  }
}

Parameter Explanation

The Server value should match the value returned by:
SELECT @@SERVERNAME;
GO

Step 5 – Register DbContext Correctly
Register Entity Framework Core inside the Infrastructure layer.
services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(
        configuration.GetConnectionString("DefaultConnection"),
        sql =>
        {
            sql.MigrationsAssembly(typeof(ApplicationDbContext).Assembly.FullName);
        });
});

Avoid multiple DbContext registrations.

Step 6 – Verify Dependency Injection
Your API should register both Application and Infrastructure services.
builder.Services.AddApplication();

builder.Services.AddInfrastructure(builder.Configuration);

This ensures the DbContext receives the correct configuration.

Step 7 – Verify Startup Project
Incorrect startup project selection is one of the most common causes of migration failures.

Startup Project
SampleERP.API

Default Project
SampleERP.Infrastructure

When using Visual Studio Package Manager Console:

• Startup Project → API
• Default Project → Infrastructure

Step 8 – Verify appsettings.Development.json
ASP.NET Core loads configuration in this order:
    appsettings.json

    appsettings.Development.json

    Environment Variables

    User Secrets

If appsettings.Development.json contains another connection string, it overrides appsettings.json.

Example:
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=OLD-SERVER\\SQLEXPRESS;Database=OldDatabase;"
  }
}

Always verify both files.

Step 9 – Check Design-Time DbContext Factory
Search your solution for:
IDesignTimeDbContextFactory

or

ApplicationDbContextFactory

Example:
public class ApplicationDbContextFactory
    : IDesignTimeDbContextFactory<ApplicationDbContext>
{
    public ApplicationDbContext CreateDbContext(string[] args)
    {
        var builder = new DbContextOptionsBuilder<ApplicationDbContext>();

        builder.UseSqlServer(
            "Server=OLD-SERVER\\SQLEXPRESS;Database=OldDatabase;");

        return new ApplicationDbContext(builder.Options);
    }
}

During migrations, Entity Framework Core uses this factory instead of Program.cs.

Step 10 – Search for Multiple UseSqlServer Registrations
Search the entire solution:
UseSqlServer(

There should ideally be only one registration.

Step 11 – Display the Active Connection String
Temporarily print the active connection string.
var connectionString =
    builder.Configuration.GetConnectionString("DefaultConnection");

Console.WriteLine(connectionString);

services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString);
});

If the printed value differs from the expected connection string, another configuration source is overriding it.

Step 12 – Verify SQL Server Browser Service
Open:
services.msc

Ensure the following service is running:

SQL Server Browser
Although optional for local development, it assists clients in locating named SQL Server instances.

SQL Server Health Check Commands
Run these commands after connecting via sqlcmd.
SELECT @@SERVERNAME;
GO

SELECT @@VERSION;
GO

SELECT SYSTEM_USER;
GO

SELECT DB_NAME();
GO

SELECT name
FROM sys.databases
ORDER BY name;
GO

If all commands execute successfully, SQL Server is healthy and accepting connections.

Common Root Causes
Most Error 40 issues are caused by one or more of the following:

• Incorrect SQL Server instance name
• SQL Server service is stopped
• Invalid connection string
• Wrong startup project
• appsettings.Development.json overriding configuration
• Hardcoded connection string inside IDesignTimeDbContextFactory
• Multiple DbContext registrations
• SQL Server Browser service stopped
• Environment variables overriding configuration

Best Practices

• Store connection strings in configuration files or secret stores.
• Avoid hardcoding connection strings.
• Keep only one DbContext registration.
• Use dependency injection consistently.
• Keep Entity Framework Core package versions aligned.
• Use verbose logging during migration debugging.
• Verify the startup project before running migrations.
• Test SQL Server connectivity using sqlcmd or SQL Server Management Studio before debugging Entity Framework Core.

Before spending hours debugging, verify the following:

• SQL Server service is running.
• SQL Server instance name is correct.
• Connection string is valid.
• Database exists or migrations are ready to create it.
• Startup project is correct.
• DbContext registration is correct.
• No configuration overrides exist.
• Design-time DbContext factory uses the correct connection string.
• TCP/IP is enabled.
• SQL Server Browser service is running.

Conclusion
The "Named Pipes Provider, Error 40" exception is one of the most common SQL Server connectivity issues in ASP.NET Core applications.
In most cases, the problem is not SQL Server itself, but a configuration mismatch between the application, Entity Framework Core, and the SQL Server instance.
By following a structured troubleshooting approach—verifying SQL Server connectivity, validating the connection string, checking dependency injection, confirming the startup project, reviewing configuration files, and inspecting Entity Framework Core's design-time configuration—you can identify the root cause quickly and resolve it with confidence.

Always begin by confirming that SQL Server is reachable using sqlcmd or SQL Server Management Studio before investigating your application. A methodical process saves time, reduces frustration, and leads to faster, more reliable solutions.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Data that doesn't necessarily fit cleanly into defined database columns is frequently used in modern applications. Records may differ in terms of user choices, product characteristics, application settings, logs, and API answers. Many developers decide to save this kind of data as JSON rather than generating dozens of optional columns.

The JSONB data type in PostgreSQL stores JSON in a binary format that is ideal for indexing and querying. JSONB is a popular option for high-traffic applications because it combines the efficiency of a relational database with the flexibility of JSON.

But JSON data storage is insufficient on its own. As your data expands, JSONB columns may become a performance barrier if suitable indexing and query optimization are not implemented.

This post will teach you how JSONB functions, typical performance issues, and useful methods for PostgreSQL JSONB query optimization.

What Is JSONB?
JSONB is a PostgreSQL data type that stores JSON documents in a binary format.

Unlike plain JSON, JSONB:

• Removes unnecessary whitespace
• Stores data in a format optimized for searching
• Supports efficient indexing
• Allows fast querying of nested values

For example, you can store product details like this:
CREATE TABLE Products
(
    Id SERIAL PRIMARY KEY,
    Name TEXT,
    Details JSONB
);

A sample record might contain:
{
  "brand": "Contoso",
  "color": "Black",
  "storage": "256GB",
  "wirelessCharging": true
}

This approach allows you to store flexible attributes without constantly changing your database schema.

Querying JSONB Data
PostgreSQL provides operators to read values from JSONB columns.

For example, to retrieve the product brand:
SELECT Details ->> 'brand'
FROM Products;

To filter products by color:
SELECT *
FROM Products
WHERE Details ->> 'color' = 'Black';

These queries are easy to write, but performance can decrease if the table contains millions of rows and no indexes are available.

Use GIN Indexes
One of the biggest advantages of JSONB is that it supports indexing.
For most JSONB search scenarios, a GIN (Generalized Inverted Index) provides excellent performance.

Create a GIN index like this:
CREATE INDEX idx_products_details
ON Products
USING GIN (Details);

Instead of scanning every row, PostgreSQL can use the index to locate matching records much more efficiently.

For applications with frequent JSON searches, this is one of the most effective optimizations.

Avoid Storing Everything in JSONB
Although JSONB is flexible, it should not replace every database column.

For example, avoid storing frequently queried values like this:
{
  "price": 999.99,
  "category": "Laptop"
}

If your application filters or sorts by price or category regularly, these values should usually be stored in dedicated columns.

A better design might look like:
CREATE TABLE Products
(
    Id SERIAL PRIMARY KEY,
    Name TEXT,
    Category TEXT,
    Price NUMERIC,
    Details JSONB
);

Use JSONB for optional or dynamic attributes, while keeping frequently accessed fields in standard columns.

Query Only the Data You Need
Avoid returning entire JSON documents when you only need a few values.

Instead of:
SELECT Details
FROM Products;

Retrieve only the required field:
SELECT Details ->> 'brand'
FROM Products;

Fetching less data reduces network traffic and improves query performance.

Keep JSON Documents Small
Large JSON documents consume more storage and require more processing time.
Instead of storing unrelated information in a single JSONB column, split data into logical sections.
For example, avoid combining:

• Product specifications
• Customer reviews
• Inventory history
• Shipping information

into one large JSON document.
Smaller JSON documents are easier to query and maintain.

Monitor Query Performance
PostgreSQL provides tools to identify slow queries.

Use:
EXPLAIN ANALYZE
SELECT *
FROM Products
WHERE Details ->> 'brand' = 'Contoso';

This command shows how PostgreSQL executes the query and whether indexes are being used.

If a query performs a sequential scan instead of using an index, it may indicate that further optimization is needed.

Regular performance monitoring helps identify bottlenecks before they affect users.
Use JSONB Operators Efficiently

PostgreSQL includes several operators for working with JSONB.

Some commonly used operators are:

Choosing the appropriate operator can improve query readability and performance.

Best Practices
When working with JSONB in high-traffic PostgreSQL applications, follow these recommendations:

• Use JSONB instead of JSON when querying data frequently.
• Create GIN indexes for searchable JSONB columns.
• Store frequently filtered fields in dedicated database columns.
• Keep JSON documents as small as practical.
• Select only the JSON properties required by the application.
• Monitor query execution plans using EXPLAIN ANALYZE.
• Avoid unnecessary nesting in JSON documents.
• Test query performance with production-like datasets before deployment.

Conclusion
With JSONB, PostgreSQL can store semi-structured data with the same flexibility and relational database performance. It allows developers to create applications that can adapt to changing data structures without compromising query performance when utilized properly.

The secret to success is striking a balance between solid database design and adaptability. You can create high-traffic applications that are quick, scalable, and simple to manage as your data expands by indexing JSONB columns, keeping documents small, storing commonly accessed fields separately, and keeping an eye on query speed.

In contemporary applications, tracking data modifications is frequently necessary. Organizations frequently need to know what changed, when it changed, and what the prior values were. Maintaining historical records is crucial for auditing, compliance, reporting, and retrieving material that has been inadvertently altered.

A built-in method for automatically preserving and maintaining the history of data changes is offered by SQL Server Temporal Tables. Rather of developing intricate triggers or bespoke audit tables, SQL Server keeps track of all updates and deletions while retaining earlier iterations of rows.

This article will explain what Temporal Tables are, how they function, how to construct and query them, and the best ways to use them.

Temporal Tables: What Are They?
A system-versioned temporal table, also referred to as a temporal table, automatically maintains a comprehensive history of data modifications.

Each temporal table consists of:

• A current table that stores the latest data.
• A history table that stores previous versions of rows.
• Two system-managed datetime columns that define the validity period of each record.

Whenever a row is updated or deleted, SQL Server moves the previous version to the history table automatically.

This eliminates the need for custom auditing logic in many scenarios.

Why Use Temporal Tables?
Maintaining historical data manually often requires triggers, audit tables, or application-level code.

Temporal Tables simplify this process by providing:

• Automatic history tracking
• Built-in auditing
• Point-in-time data recovery
• Change history analysis
• Simplified reporting
• Reduced development effort
• Native SQL Server support

These features make Temporal Tables an excellent choice for applications that require historical data.

How Temporal Tables Work
When a record is inserted, it is stored in the main table.
When the record is updated:

• The existing row is copied to the history table.
• The current table is updated with the new values.
• SQL Server updates the validity period automatically.

When a record is deleted:

• The deleted row is moved to the history table.
• The row is removed from the current table.
• All of this happens without requiring additional application code.

Create a Temporal Table
The following example creates a temporal table for storing employee information.
CREATE TABLE Employees
(
    EmployeeId INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(100),

    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,

    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(
    SYSTEM_VERSIONING = ON
);

SQL Server automatically creates and manages the associated history table.

Insert Data
Insert data just as you would with any regular table.

INSERT INTO Employees
(EmployeeId, Name, Department)
VALUES
(1, 'John Smith', 'Sales');

At this point, the current table contains one record, while the history table remains empty because no changes have occurred yet.

Update Data
Now update the employee's department.
UPDATE Employees
SET Department = 'Marketing'
WHERE EmployeeId = 1;

SQL Server automatically:

• Stores the previous version in the history table.
• Updates the current table.
• Adjusts the validity period.

No trigger or manual insert into an audit table is required.

View Current Records
Query the current table as usual.
SELECT *
FROM Employees;

This returns only the latest version of each record.

View Historical Data
To retrieve all historical versions of a record, use the FOR SYSTEM_TIME ALL clause.

• SELECT *
• FROM Employees
• FOR SYSTEM_TIME ALL;

The result includes:

• Current records
• Previous versions
• Validity periods

This provides a complete history of changes.

Query Data at a Specific Point in Time
One of the most powerful features of Temporal Tables is point-in-time querying.

For example:
SELECT *
FROM Employees
FOR SYSTEM_TIME AS OF '2026-07-20 10:00:00';

This query returns the data exactly as it existed at the specified date and time.

This capability is especially useful for auditing and troubleshooting.

View Changes Within a Time Range
You can also retrieve records that were valid during a specific period.
SELECT *
FROM Employees
FOR SYSTEM_TIME
BETWEEN
'2026-07-01'
AND
'2026-07-31';

This helps generate historical reports or investigate changes over time.

Common Use Cases
Temporal Tables are useful in many business scenarios.

Examples include:

• Employee record history
• Customer profile tracking
• Inventory changes
• Financial transaction auditing
• Product price history
• Insurance policy updates
• Healthcare records
• Regulatory compliance
• Data recovery
• Historical reporting

Any application that needs to preserve previous versions of data can benefit from Temporal Tables.

Performance Considerations
Although Temporal Tables simplify history management, they also increase storage requirements.

Keep these factors in mind:

• History tables continue to grow over time.
• Large update operations create additional historical records.
• Indexing the history table improves query performance.
• Historical queries may require more resources than current data queries.

Monitoring storage growth is important for long-running applications.

Best Practices
When working with Temporal Tables, follow these recommendations:

• Enable Temporal Tables only where historical tracking is required.
• Create indexes on frequently queried columns.
• Monitor the size of history tables.
• Archive historical data if retention policies allow.
• Use point-in-time queries for auditing instead of maintaining custom audit tables.
• Test historical queries on large datasets.
• Review retention requirements to balance compliance and storage costs.

These practices help maintain good performance while preserving valuable historical data.

Common Mistakes to Avoid
Developers sometimes misuse Temporal Tables by treating them as a replacement for every auditing solution.

Avoid these common mistakes:

• Enabling temporal history on every table without a business need.
• Ignoring the growth of history tables.
• Failing to index historical data.
• Assuming Temporal Tables capture every type of database activity.
• Forgetting to test historical queries under production-sized workloads.

Using Temporal Tables selectively helps maximize their value while minimizing overhead.

Temporal Tables vs Traditional Audit Tables

Here's a comparison of the two approaches.

For many applications, Temporal Tables offer a simpler and more maintainable alternative to custom auditing solutions.

Conclusion
Without the need for audit tables or special triggers, SQL Server Temporal Tables offer a robust and effective method of automatically tracking changes in historical data. They make auditing, reporting, compliance, and data recovery easier by preserving earlier iterations of rows and facilitating point-in-time queries.

Temporal tables may assist maintain important historical data while lowering development complexity, whether you're creating corporate business software, HR applications, finance systems, or inventory management systems. You may fully utilize this functionality while preserving outstanding database performance by adhering to best practices for indexing, storage management, and selective usage.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.