Switching to a Real Database Server

The bookshop does well. A year on, three things are different. The owner's accountant wants to look at the stock numbers from her own computer, in her own reporting tool. The shop opens a second till that must use the same books. And someone asks, "what happens to our data if the server's disk dies?"

All three run into the same wall. The database is a file, bookshop.db, sitting next to the API on one machine. SQLite was the right start: nothing to install, nothing to configure. But a file on one machine cannot be shared easily, one writer at a time is a real limit once several people use the app, and a backup means copying a file while the program may be writing it. Real shops put their data in a database server: a separate program, usually on its own machine, that many programs can connect to, with accounts, backups and its own care.

The worry is easy to guess: will the app have to be rewritten? The honest answer is a little, and where the little goes is the point of this lesson. The course kept the database behind a service and behind the settings from the start, so that a change like this stays small. We will make the change and count what it touched.

This lesson is optional. Nothing earlier needs it, and the other lessons keep using SQLite. If you work with SQL Server (the example here), you will do this at some point. The same steps work for another database server, with another package.

Our goal: run the same API on SQL Server instead of SQLite, with the same five books, and see how little of the code has to change.

Here is the route:

1. Predict what will change and what will not.
2. Get a SQL Server to connect to.
3. Swap the EF Core package and the line in Program.cs, and see what the build says.
4. Learn why the old migrations cannot be used, and start the history again for the new database.
5. Create the database, run the API, and look inside.
6. Check that the tests still pass, and count the changes.

Left out: setting up and caring for a server (users and permissions, backups, running one on a hosting company's machine), moving the data of a live database from one system to another, and servers other than SQL Server.

Where we are

This is the solution from the end of one integration test. No project is added:

BookShop/
  Directory.Packages.props                  <- changes
  src/
    BookShop.Api/
      appsettings.json                      <- changes (the connection string)
      Program.cs                            <- changes (one line)
    BookShop.Data/
      BookShop.Data.csproj                  <- changes (the package)
      BookShopDbContext.cs                  <- changes (one line for the price)
      Migrations/                           <- DELETED, then made again for SQL Server
  tests/
    BookShop.Data.Tests/
      BookShop.Data.Tests.csproj            <- changes (the SQLite package moves here)
    BookShop.Api.IntegrationTests/
      BookShop.Api.IntegrationTests.csproj  <- changes (the SQLite package moves here)

Run every command from the solution folder, BookShop/.

What changes, and what does not

Before touching anything, make a prediction from what the course built. Here are the layers of the app, from the outside in:

The reason is the one the books service gave for putting a service in front of EF Core: the controllers talk to a service, the service talks to the DbContext, and only the bottom of the stack knows which database is underneath. EF Core translates the same C# into the SQL of whichever provider is plugged in. So the top of the stack should not notice. We will check the prediction, and not take it on trust.

A SQL Server to connect to

You need a SQL Server that you may create a database on. Pick the one that is easiest for you:

• One you already have. A development server at work or a SQL Server on your own machine (the Developer and Express editions are free). Use it, with your own server name and login.
• LocalDB (Windows only). A small SQL Server that Visual Studio can install. No password: Windows signs you in.
• A container. A container is a ready-made, sealed program that you start with one command and throw away afterwards. It needs Docker installed (a free tool; docker --version tells you whether you have it), and it is the same on Windows, macOS and Linux.

We use the container, because it is the same for everyone. Start one, with a password of your choice (SQL Server insists on a strong one: at least 8 characters, with three of upper case, lower case, digits and symbols):

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Dev-Passw0rd-1" -p 1433:1433 --name bookshop-sql -d mcr.microsoft.com/mssql/server:2022-latest

ACCEPT_EULA=Y agrees to the licence. MSSQL_SA_PASSWORD is the password for the administrator login, sa. -p 1433:1433 makes the container's port 1433, SQL Server's usual one, reachable from your machine. -d runs it in the background. The first run downloads the image, which is large and can take a while. When you are finished with the lesson, docker rm -f bookshop-sql removes it.

The API reaches the server through a connection string, as it reached the file before (Data Source=bookshop.db). A SQL Server one has more parts:

The password must not go into a file that is committed, as the login endpoint said about the signing key. ASP.NET has a way that needs no file: any setting can be given as an environment variable, a named value that the terminal passes to every program it starts. The name for ConnectionStrings and BookShop is the two joined by a double underscore. Set it in the terminal where you will run the commands below (bash, then PowerShell):

export ConnectionStrings__BookShop="Server=localhost,1433;Database=BookShop;User Id=sa;Password=Dev-Passw0rd-1;TrustServerCertificate=True"

$env:ConnectionStrings__BookShop = "Server=localhost,1433;Database=BookShop;User Id=sa;Password=Dev-Passw0rd-1;TrustServerCertificate=True"

A value set this way beats the same setting in appsettings.json, which is what we want. (It lasts for that terminal window only. Open a new one and you must set it again.) The file should still hold something that matches the new database, without the password. Open src/BookShop.Api/appsettings.json. The whole file is:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Microsoft.EntityFrameworkCore": "Warning"
    },
    "Console": {
      "FormatterName": "simple",
      "FormatterOptions": {
        "TimestampFormat": "yyyy-MM-dd HH:mm:ss "
      }
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "BookShop": "Server=localhost,1433;Database=BookShop;User Id=sa;TrustServerCertificate=True"
  },
  "Jwt": {
    "Issuer": "BookShop.Api",
    "Audience": "BookShop",
    "MinutesValid": 60
  }
}

Only the BookShop line changed. It shows the shape, and leaves out the password, which comes from the environment variable.

Swap the package and the line

EF Core talks to each database through a provider, a package. The SQLite provider is in BookShop.Data. Take it out, and put in the one for SQL Server:

dotnet remove src/BookShop.Data package Microsoft.EntityFrameworkCore.Sqlite
dotnet add src/BookShop.Data package Microsoft.EntityFrameworkCore.SqlServer

Visual Studio: right-click the BookShop.Data project, Manage NuGet Packages, uninstall Microsoft.EntityFrameworkCore.Sqlite and install Microsoft.EntityFrameworkCore.SqlServer. As always, the version goes into Directory.Packages.props. The whole project file is:

<Project Sdk="Microsoft.NET.Sdk">
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" />
  </ItemGroup>
</Project>

Now build, and let the compiler show what depends on SQLite:

dotnet build

src/BookShop.Api/Program.cs(16,69): error CS1061: 'DbContextOptionsBuilder' does not contain a definition for 'UseSqlite' ...
tests/BookShop.Data.Tests/BookServiceTests.cs(1,22): error CS0234: The type or namespace name 'Sqlite' does not exist in the namespace 'Microsoft.Data' ...
tests/BookShop.Data.Tests/BookServiceTests.cs(9,13): error CS0246: The type or namespace name 'SqliteConnection' could not be found ...

(We shortened the lines and removed repeats.) Two places, and both are honest:

1. Program.cs says UseSqlite. This is the line that chooses the database. Change it, in src/BookShop.Api/Program.cs. Only one line changes, so here is only that line and the one above it for context:

var connectionString = builder.Configuration.GetConnectionString("BookShop");
builder.Services.AddDbContext<BookShopDbContext>(options => options.UseSqlServer(connectionString));   // was UseSqlite

2. The tests use SQLite on purpose: the service tests and the integration tests make an in-memory SQLite database, which is quick and needs no server. They got the SQLite package by accident, through the Data project. Now they must ask for it themselves. Add it to both test projects:

dotnet add tests/BookShop.Data.Tests package Microsoft.EntityFrameworkCore.Sqlite
dotnet add tests/BookShop.Api.IntegrationTests package Microsoft.EntityFrameworkCore.Sqlite

The package versions live in Directory.Packages.props. The two providers now sit side by side (your numbers may be newer):

<Project>
  <PropertyGroup>
    <ManagePackageVersionsCentrally>true</ManagePackageVersionsCentrally>
  </PropertyGroup>
  <ItemGroup>
    <PackageVersion Include="Microsoft.AspNetCore.Authentication.JwtBearer" Version="10.0.14" />
    <PackageVersion Include="Microsoft.AspNetCore.Mvc.Testing" Version="10.0.14" />
    <PackageVersion Include="Microsoft.AspNetCore.OpenApi" Version="10.0.12" />
    <PackageVersion Include="Microsoft.EntityFrameworkCore.Design" Version="10.0.13">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageVersion>
    <PackageVersion Include="Microsoft.EntityFrameworkCore.Sqlite" Version="10.0.13" />
    <PackageVersion Include="Microsoft.EntityFrameworkCore.SqlServer" Version="10.0.14" />    <!-- new -->
    <PackageVersion Include="MSTest" Version="4.0.1" />
    <PackageVersion Include="Scalar.AspNetCore" Version="2.18.2" />
  </ItemGroup>
</Project>

Build again. It succeeds, and may print warnings:

warning NU1903: Package 'System.Security.Cryptography.Pkcs' 9.0.11 has a known high severity vulnerability, https://github.com/advisories/GHSA-846f-f764-76mx

(Shortened. Yours may differ, or may not appear, since it depends on the versions of the day.) NuGet checks every package against a public list of security problems, and this one comes from inside the SQL Server driver. It does not stop the build. The fix is usually a newer version of the package that brought it in, so look at the warning before anything goes live.

Two small changes so far, and the compiler found them both. Not a line in a controller, a service or a DTO.

Why the old migrations cannot come along

The tables must be made on the new server. EF Core made them from migrations, so try the old ones. Ask EF Core for the SQL that the existing migrations would send (this command only prints, and needs no server):

dotnet ef migrations script --project src/BookShop.Data --startup-project src/BookShop.Api

Unable to cast object of type 'System.Decimal' to type 'System.String'.

(The real output starts with a stack trace, which we cut.) It fails, and the message is a puzzle until you look inside an old migration. Open the first one, Migrations/..._InitialCreate.cs, and look at how it describes the table:

Id = table.Column<int>(type: "INTEGER", nullable: false)
    .Annotation("Sqlite:Autoincrement", true),
Title = table.Column<string>(type: "TEXT", nullable: false),
Price = table.Column<decimal>(type: "TEXT", nullable: false),

A migration is not a neutral list of tables and columns. EF Core wrote it for the provider that was plugged in at the time: SQLite's type names (INTEGER, TEXT), SQLite's way of numbering rows (Sqlite:Autoincrement), and the price as TEXT because SQLite has no real decimal (EF Core with SQLite showed that). The seed migration then tries to write the price 39.90 as a number into a column typed TEXT, and that is the cast that failed.

The old files are a history of how the SQLite database was built. They cannot build a SQL Server one. The usual answer, when there is no live data to carry over, is to start the history again for the new database. (If the shop had real data, moving it across would be a job of its own: a script, or a tool, and a plan for the day of the switch. That is left out.)

Start the migrations again

Delete the Migrations folder in src/BookShop.Data/, and make a fresh first migration. EF Core reads the model (the Book class and the DbContext) and writes SQL Server's version:

dotnet ef migrations add InitialCreate --project src/BookShop.Data --startup-project src/BookShop.Api

The command succeeds, with a warning that is worth reading (the date and time are the ones from the console format of the logging lesson):

2026-10-31 18:44:09 warn: Microsoft.EntityFrameworkCore.Model.Validation[30000]
      No store type was specified for the decimal property 'Price' on entity type 'Book'. This will cause values to be silently truncated if they do not fit in the default precision and scale. Explicitly specify the SQL server column type that can accommodate all the values in 'OnModelCreating' using 'HasColumnType', specify precision and scale using 'HasPrecision', or configure a value converter using 'HasConversion'.

(We shortened the line; the real one is longer.) SQLite did not mind, since it keeps a price as text. SQL Server has a true decimal type, and a decimal column needs two numbers: precision, how many digits in all, and scale, how many of them after the point. A price like 39.90 is 4 digits with 2 after the point, and decimal(18,2) gives plenty of room. EF Core would pick its own default and warn that you did not say. Say it. Delete the Migrations folder again (it holds only the migration we just made), and add one line to the model. Open src/BookShop.Data/BookShopDbContext.cs. The whole file is:

using Microsoft.EntityFrameworkCore;

namespace BookShop.Data;

public class BookShopDbContext : DbContext
{
    public BookShopDbContext(DbContextOptions<BookShopDbContext> options)
        : base(options)
    {
    }

    public DbSet<Book> Books { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>(book =>                                                                  // new
        {                                                                                                  // new
            book.Property(b => b.Price).HasPrecision(18, 2);                                               // new
            book.ToTable(table => table.HasCheckConstraint("CK_Books_Price", "CAST(Price AS REAL) >= 0")); // new
        });                                                                                                // new
        // modelBuilder.Entity<Book>()                                                                     // replaced
        //     .ToTable(table => table.HasCheckConstraint("CK_Books_Price", "CAST(Price AS REAL) >= 0"));  // replaced
    }
}

(The commented lines show the old form and can be deleted. The check constraint itself is unchanged: CAST(Price AS REAL) >= 0 is as valid in SQL Server as in SQLite. In SQL Server alone, Price >= 0 would do, since a decimal compares as a number. But the tests still build their tables from this model on SQLite, where the CAST is needed (rules the API cannot see), so one rule that works in both keeps the tests honest.)

Add the migration again, and look at what it made:

dotnet ef migrations add InitialCreate --project src/BookShop.Data --startup-project src/BookShop.Api
dotnet ef migrations script --project src/BookShop.Data --startup-project src/BookShop.Api

The first command ends with Done. and no warning. The second prints the SQL. If you know SQL Server, this will look like home:

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

BEGIN TRANSACTION;
CREATE TABLE [Books] (
    [Id] int NOT NULL IDENTITY,
    [Title] nvarchar(max) NOT NULL,
    [Author] nvarchar(max) NOT NULL,
    [Price] decimal(18,2) NOT NULL,
    [Stock] int NOT NULL,
    [Category] nvarchar(max) NOT NULL,
    CONSTRAINT [PK_Books] PRIMARY KEY ([Id]),
    CONSTRAINT [CK_Books_Price] CHECK (CAST(Price AS REAL) >= 0)
);

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20261031104444_InitialCreate', N'10.0.14');

COMMIT;
GO

(Your migration id has your own date and time. Compare it with the SQLite script: the same table, in SQL Server's words.) Every column is nvarchar(max), because we never set a length; lengths on the database side were left out in more validation rules.

The five books were a migration of their own, written by hand in reading books. Its content does not depend on the database: it is a list of values. Make the migration, and give it the same Up and Down as before:

dotnet ef migrations add SeedBooks --project src/BookShop.Data --startup-project src/BookShop.Api

Open the new Migrations/..._SeedBooks.cs. The whole file is:

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace BookShop.Data.Migrations
{
    /// <inheritdoc />
    public partial class SeedBooks : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.InsertData(
                table: "Books",
                columns: new[] { "Id", "Title", "Author", "Price", "Stock", "Category" },
                values: new object[,]
                {
                    { 1, "The Pragmatic Programmer", "Andrew Hunt", 39.90m, 12, "Programming" },
                    { 2, "Clean Code", "Robert C. Martin", 34.50m, 7, "Programming" },
                    { 3, "Refactoring", "Martin Fowler", 44.00m, 3, "Programming" },
                    { 4, "Salt Fat Acid Heat", "Samin Nosrat", 29.00m, 4, "Cooking" },
                    { 5, "The Joy of Cooking", "Irma Rombauer", 32.50m, 2, "Cooking" }
                });
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DeleteData(
                table: "Books",
                keyColumn: "Id",
                keyValues: new object[] { 1, 2, 3, 4, 5 });
        }
    }
}

The books have fixed ids, but the Id column now numbers itself (IDENTITY), and SQL Server refuses to take a number for it unless told to. EF Core knows, and wraps the insert for us. This is what the migration turns into:

BEGIN TRANSACTION;
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Title', N'Author', N'Price', N'Stock', N'Category') AND [object_id] = OBJECT_ID(N'[Books]'))
    SET IDENTITY_INSERT [Books] ON;
INSERT INTO [Books] ([Id], [Title], [Author], [Price], [Stock], [Category])
VALUES (1, N'The Pragmatic Programmer', N'Andrew Hunt', 39.9, 12, N'Programming'),
(2, N'Clean Code', N'Robert C. Martin', 34.5, 7, N'Programming'),
(3, N'Refactoring', N'Martin Fowler', 44.0, 3, N'Programming'),
(4, N'Salt Fat Acid Heat', N'Samin Nosrat', 29.0, 4, N'Cooking'),
(5, N'The Joy of Cooking', N'Irma Rombauer', 32.5, 2, N'Cooking');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Title', N'Author', N'Price', N'Stock', N'Category') AND [object_id] = OBJECT_ID(N'[Books]'))
    SET IDENTITY_INSERT [Books] OFF;

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20261031104505_SeedBooks', N'10.0.14');

COMMIT;
GO

(That is the part of migrations script for this migration only. The SET IDENTITY_INSERT lines are the ones SQL Server people write by hand.)

Create the database and run the API

Everything is ready. Make sure the environment variable is set in this terminal, and the container is running. Then apply the migrations, the same command as for SQLite:

dotnet ef database update --project src/BookShop.Data --startup-project src/BookShop.Api

EF Core connects with the connection string, creates the BookShop database if it is missing, and runs the two migrations. The command ends with Done.

Look inside with whatever tool you use for SQL Server (SQL Server Management Studio, Azure Data Studio, or sqlcmd), on server localhost,1433 with the sa login: the database BookShop, the table Books with five rows, and __EFMigrationsHistory with two. Then start the API, and ask for the books, as in reading books:

dotnet run --project src/BookShop.Api

Log in as bob, and ask for /books with the token (the commands are in protect endpoints and roles). The five books come back, in title order, the same JSON as before. A request that used to read a file now reads a server, and nothing in the controller, the service or the answer knows. A POST of a new book gets the next number from IDENTITY: the answer is 201 with Location: http://localhost:5101/books/6, exactly as before.

One more check, on the rule from rules the API cannot see: the database must refuse a negative price even when the API is skipped. In your SQL tool, try:

INSERT INTO Books (Title, Author, Price, Stock, Category) VALUES ('Bad', 'Nobody', -1, 1, 'Test');

The server refuses:

Msg 547, Level 16, State 1, Server 38d4a7bb2f9d, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Books_Price". The conflict occurred in database "BookShop", table "dbo.Books", column 'Price'.
The statement has been terminated.

(Your server name differs.) It is the same rule by the same name, now enforced by SQL Server. No row is added, but the refused insert still uses up a number, so the next new book gets 7 and not 6 if you try this first.

The tests still pass

The tests did not move to SQL Server. They build in-memory SQLite databases, which is why they needed their own package. Run them. No server is involved:

dotnet test

Passed!  - Failed:     0, Passed:    15, Skipped:     0, Total:    15, Duration: 2 s - BookShop.Api.Tests.dll (net10.0)
Passed!  - Failed:     0, Passed:     4, Skipped:     0, Total:     4, Duration: 2 s - BookShop.Data.Tests.dll (net10.0)
Passed!  - Failed:     0, Passed:     3, Skipped:     0, Total:     3, Duration: 2 s - BookShop.Api.IntegrationTests.dll (net10.0)

(Those are the counts in our scratch solution, which holds only the tests this part of the course touches. Yours are higher.) Note what the integration test factory from the last lesson did here. It removes both of the API's database settings and puts its own SQLite in memory, so the API's new UseSqlServer line never ran in a test. Had it removed only one of them, EF Core would have stopped with Only a single database provider can be registered. That is the reason for the two RemoveAll lines.

What we touched

Count it. These are all the changes:

And the code that did not change: every controller, every service, every record, every test, the pages, the login. That is what putting the database behind a service and behind the settings bought. The rest of the answer is honest too.

• A stand-in is not the real thing. The tests run on SQLite, and live data lives on SQL Server. They differ in small ways (the decimal as text, for one). A bug that shows only on SQL Server would pass every test here. Teams that care run their tests against a real server started in a container for the test run, with a library for it called Testcontainers. It is the step up from this course's tests, and is left out.
• Migrations belong to a provider. If the app must run on two databases at once, one history for each is the clean way. Most apps pick one database and keep it.
• The first move is the biggest. The change is small because the course built it small. In a project that wrote SQL in the controllers, the same change would touch the whole project.

Stop the API, and when you are finished remove the container:

docker rm -f bookshop-sql

Where we ended

Compare with your solution. No project was added:

BookShop/
  Directory.Packages.props                  plus Microsoft.EntityFrameworkCore.SqlServer
  src/
    BookShop.Api/
      appsettings.json                      connection string in the SQL Server shape, no password
      Program.cs                            UseSqlServer
    BookShop.Data/
      BookShop.Data.csproj                  the SQL Server provider
      BookShopDbContext.cs                  price precision and the check rule
      Migrations/                           InitialCreate and SeedBooks, made for SQL Server
  tests/
    BookShop.Data.Tests/                    asks for the SQLite package itself
    BookShop.Api.IntegrationTests/          asks for the SQLite package itself

Recap

• A database server replaces the file when many programs, many machines or real backups are needed. SQL Server is one; the steps are the same for the others, with their own package.
• EF Core reaches each database through a provider package. Switching is a package, one line in Program.cs (UseSqlServer) and a connection string.
• Because the controllers talk to services and the services to the DbContext, none of them changes.
• Migrations are written for one provider (TEXT and Sqlite:Autoincrement against nvarchar(max) and IDENTITY), so a new database starts a new history. Moving the data of a live database is a separate job.
• SQL Server's decimal needs a precision and scale, said in the model with HasPrecision(18, 2).
• A password does not go in a committed file. An environment variable such as ConnectionStrings__BookShop overrides the setting.
• The tests run on in-memory SQLite and cannot see differences in a real server. Testcontainers is the next step.

Questions to think about

Try to answer each one before you open the answer.

1. Why did the controllers and the services not change when the database changed?

2. Why can the old SQLite migrations not build the SQL Server tables?

3. Why does a connection string with a password not go into appsettings.json?

4. The tests still use SQLite. What is the risk of that?

Next lesson: there is none. This is the last lesson of the course. Next, try the exercises left along the way: a test for the 400 of a bad book, or the same switch for another database server.