ASP.NET CORE MVC TUTORIAL SERIES · APPENDIX A

Migrating from SQLite to SQL Server or MySQL

Take the completed Product Management application from Parts 0–20 and replace SQLite with a server-based relational database while keeping the MVC, Identity, authorization, ViewModel, LINQ and Razor architecture largely unchanged.

Objective

This appendix demonstrates two independent upgrade paths: SQLite → SQL Server and SQLite → MySQL. Learners should choose one path, not configure both providers at the same time.

Important distinction

Changing the EF Core database provider and creating the target schema is not the same as migrating existing production data. For this learning application, the recommended path is to create a fresh SQL Server or MySQL database from the EF Core model and then add/import test data. A real production migration containing Identity users and business records requires a planned data-transfer process and validation.

1. What Changes and What Stays the Same?

ASP.NET Core MVC ↓ Controllers ↓ ViewModels ↓ Entity Framework Core ↓ DATABASE PROVIDER ┌─────────┼─────────┐ SQLite SQL Server MySQL

Most application code does not depend directly on SQLite.

Usually unchanged	Changes when switching provider
Models	EF Core provider package
ViewModels	`Program.cs` provider configuration
MVC controllers	Connection string
Razor views	Migration strategy
Identity logic	Database server setup
Ownership/Admin authorization	Production credentials/security
Most LINQ queries	Provider-specific SQL behaviour where relevant
REST API	Backup/restore procedures

2. Back Up the Existing SQLite Database First

Open the Xubuntu Terminal:

cd ~/aspnet-mvc-tutorial/ProductManagement

sqlite3 ProductManagement.db ".backup 'ProductManagement-before-provider-change.db'"

Verify:

ls -lh ProductManagement-before-provider-change.db

sqlite3 ProductManagement-before-provider-change.db ".tables"

Do not delete the SQLite database yet.

Keep the backup until the new database has been created, tested and verified.

3. Check the Current EF Core Packages

dotnet list package

You should already have SQLite-related packages such as:

Microsoft.EntityFrameworkCore.Sqlite

The application also uses EF Core Identity and Design packages from earlier parts.

4. Why Provider Versions Matter

EF Core providers normally need to match the EF Core major version used by the application. This tutorial targets .NET 8 / EF Core 8, so choose an EF Core 8-compatible SQL Server or MySQL provider.

Course rule

Do not install an EF Core 9 or 10 provider into this .NET 8 / EF Core 8 tutorial merely because it is newer.

5. Choose One Path

Path A	Path B
Microsoft SQL Server	MySQL
`Microsoft.EntityFrameworkCore.SqlServer`	`MySql.EntityFrameworkCore`
`UseSqlServer()`	`UseMySQL()`

PATH A — SQLite to SQL Server

6. Install the SQL Server EF Core Provider

From:

cd ~/aspnet-mvc-tutorial/ProductManagement

install the EF Core 8 SQL Server provider:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 8.0.0

Restore and build:

dotnet restore
dotnet build

7. Update Program.cs for SQL Server

Open:

code Program.cs

Find the current SQLite registration:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite(
        builder.Configuration.GetConnectionString(
            "DefaultConnection")));

Replace the entire block with:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString(
            "DefaultConnection")));

The existing namespace:

using Microsoft.EntityFrameworkCore;

remains valid.

8. Change the SQL Server Connection String

Open:

code appsettings.json

The SQLite connection string currently resembles:

"DefaultConnection": "Data Source=ProductManagement.db"

For a local SQL Server instance, a SQL-authentication connection string can resemble:

"DefaultConnection":
  "Server=localhost;Database=ProductManagement;User Id=productapp;Password=YOUR_PASSWORD;TrustServerCertificate=True"

Do not commit real database passwords.

For deployment, override the connection string with an environment variable rather than storing the real production password in source-controlled JSON.

For example:

export ConnectionStrings__DefaultConnection="Server=localhost;Database=ProductManagement;User Id=productapp;Password=YOUR_PASSWORD;TrustServerCertificate=True"

9. Do Not Reuse SQLite Migrations Blindly

EF Core migration operations can contain provider-specific SQL and type decisions. Microsoft supports maintaining separate migration sets when one model targets multiple providers.

For this tutorial, the simplest learning path is to create a fresh SQL Server migration set.

Keep the existing SQLite migrations as a backup first.

Do not remove them until you have copied the project or committed the current state to source control.

10. Create a Fresh SQL Server Migration Set

Create a backup copy of the SQLite migrations:

cp -a Migrations Migrations.Sqlite.Backup

Remove the active migration folder:

rm -rf Migrations

Create a fresh initial migration for SQL Server:

dotnet ef migrations add InitialSqlServer

Inspect:

dotnet ef migrations list

11. Create the SQL Server Schema

When the target SQL Server database and credentials are available, run:

dotnet ef database update

This creates the schema represented by the current application model, including:

Products
Categories
AspNetUsers
AspNetRoles
AspNetUserRoles
other Identity tables

12. Verify SQL Server Connectivity

Run the application:

dotnet run

Verify:

the Product list opens;
registration/login works;
Categories work;
Create/Edit/Delete work;
ownership rules work;
Admin authorization works;
Dashboard aggregates work; and
the API GET endpoints return Product data.

13. SQL Server Production Configuration

In production, prefer an environment-variable connection string:

export ASPNETCORE_ENVIRONMENT=Production

export ConnectionStrings__DefaultConnection="Server=sqlserver.example.internal;Database=ProductManagement;User Id=productapp;Password=STRONG_SECRET;Encrypt=True"

The ASP.NET Core application code does not need to know where the SQL Server is physically located.

PATH B — SQLite to MySQL

14. Restore the Project Before Choosing MySQL

Choose one provider path.

If you already performed the SQL Server path in the same working project, restore the project from source control or a copy before following the MySQL path. Do not casually mix migration histories from multiple providers.

15. Install the Official MySQL EF Core Provider

From the application folder:

cd ~/aspnet-mvc-tutorial/ProductManagement

For this .NET 8 / EF Core 8 tutorial, install an EF Core 8-compatible Oracle MySQL provider:

dotnet add package MySql.EntityFrameworkCore --version 8.0.5

Then:

dotnet restore
dotnet build

16. Update Program.cs for MySQL

Open:

code Program.cs

Replace the SQLite registration:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite(
        builder.Configuration.GetConnectionString(
            "DefaultConnection")));

with:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseMySQL(
        builder.Configuration.GetConnectionString(
            "DefaultConnection")!));

The official Oracle MySQL provider uses UseMySQL().

17. Change the MySQL Connection String

Open:

code appsettings.json

A MySQL connection string can resemble:

"DefaultConnection":
  "Server=localhost;Port=3306;Database=ProductManagement;User=productapp;Password=YOUR_PASSWORD"

Again, do not store a real production password in source control.

Production example:

export ConnectionStrings__DefaultConnection="Server=mysql.example.internal;Port=3306;Database=ProductManagement;User=productapp;Password=STRONG_SECRET"

18. Create a Fresh MySQL Migration Set

Back up the SQLite migrations:

cp -a Migrations Migrations.Sqlite.Backup

Then create a new migration history for MySQL:

rm -rf Migrations

dotnet ef migrations add InitialMySql

dotnet ef migrations list

19. Create the MySQL Schema

Ensure the target MySQL server/database account exists and has the required privileges, then run:

dotnet ef database update

The current EF Core model will create the application's Product, Category and Identity schema on MySQL.

20. Verify MySQL Connectivity

dotnet run

Repeat the same application checks:

Product CRUD;
validation;
Category relationships;
search/filter/sort;
registration and login;
ownership;
Admin role;
Dashboard;
REST API GET; and
automated tests.

21. Optional MySQL Alternative: Pomelo

Another widely used MySQL/MariaDB provider is:

Pomelo.EntityFrameworkCore.MySql

Pomelo 8.0.3 targets EF Core 8 and .NET 8. Its configuration uses UseMySql() and a server-version argument or auto-detection.

Example:

dotnet add package Pomelo.EntityFrameworkCore.MySql --version 8.0.3

Configuration resembles:

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

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseMySql(
        connectionString,
        ServerVersion.AutoDetect(connectionString)));

Do not install both MySQL providers in the same tutorial project.

Choose either Oracle's MySql.EntityFrameworkCore or Pomelo and use that provider consistently.

22. What Happens to the Old SQLite Data?

Creating the SQL Server/MySQL schema does not automatically copy rows from:

ProductManagement.db

into the new server database.

SQLite schema + data ↓ Provider switch ↓ New SQL Server/MySQL schema DATA TRANSFER is a separate task

23. Recommended Learning Approach for Existing Data

For this tutorial application:

keep the SQLite backup;
create a clean target schema with EF Core;
register fresh test users;
create/import sample Products and Categories;
verify the application thoroughly; and
only then retire the SQLite development database if desired.

24. Why Identity Data Needs Special Care

The SQLite database contains Identity records such as:

AspNetUsers
AspNetRoles
AspNetUserRoles
AspNetUserClaims
AspNetUserLogins
AspNetUserTokens

These include password hashes, security stamps, role mappings and user identifiers. A production data migration must preserve relational integrity and validate the migrated Identity data.

Do not convert password hashes into plain-text passwords.

If Identity user migration is not required for a learning system, creating new accounts on the new database is safer and simpler than manually manipulating authentication records.

25. A More Advanced Multi-Provider Strategy

If one application must actively support SQLite, SQL Server and MySQL at the same time, do not continually delete and regenerate one Migrations directory.

Instead, maintain separate migration sets/projects, for example:

Migrations/
├── Sqlite/
├── SqlServer/
└── MySql/

or separate migrations projects.

EF Core documentation specifically describes maintaining migrations for multiple database providers.

26. Provider-Specific Behaviour Still Exists

EF Core provides a common programming model, but database engines are not identical.

Differences may include:

SQL data types;
collations and case sensitivity;
date/time behaviour;
decimal precision;
generated SQL;
indexes and constraints;
provider-specific functions; and
migration SQL.

Therefore:

Same LINQ code ↓ Different EF Core provider ↓ Different SQL dialect ↓ Target database

27. Re-run the Automated Tests

After changing provider:

cd ~/aspnet-mvc-tutorial

dotnet build ProductManagementTutorial.sln -c Release
dotnet test ProductManagementTutorial.sln -c Release

Testing note

The Part 18 tests using EF Core In-Memory are useful but do not prove every provider-specific behaviour. Important production queries should also be tested against the actual target database engine.

28. Update Production Deployment Configuration

Part 20 used:

Environment="ConnectionStrings__DefaultConnection=Data Source=/var/lib/productmanagement/ProductManagement.db"

After moving to SQL Server or MySQL, replace that service environment value with the server connection string.

SQL Server example:

Environment="ConnectionStrings__DefaultConnection=Server=dbserver;Database=ProductManagement;User Id=productapp;Password=SECRET;Encrypt=True"

MySQL example:

Environment="ConnectionStrings__DefaultConnection=Server=dbserver;Port=3306;Database=ProductManagement;User=productapp;Password=SECRET"

For real production, avoid placing long-lived database passwords directly in a world-readable service file.

Use an appropriately protected environment/configuration mechanism available on the deployment platform.

29. SQLite, SQL Server or MySQL?

SQLite	SQL Server	MySQL
Single-file database	Database server	Database server
Very easy development setup	Strong Microsoft/.NET ecosystem integration	Widely deployed cross-platform database
Good for small/local workloads	Suitable for larger multi-user systems	Suitable for larger multi-user systems
No server daemon required	Separate database administration	Separate database administration
Simple backup file	Server backup/restore procedures	Server backup/restore procedures

30. Troubleshooting — UseSqlServer Cannot Be Found

Verify:

dotnet list package

and confirm:

Microsoft.EntityFrameworkCore.SqlServer

is installed on the EF Core 8 major version.

31. Troubleshooting — UseMySQL Cannot Be Found

For the Oracle provider, verify:

MySql.EntityFrameworkCore

is installed and Program.cs imports:

using Microsoft.EntityFrameworkCore;

32. Troubleshooting — Connection Refused

Provider configuration may be correct while the database server is unavailable.

Check:

server hostname;
port;
database service status;
firewall rules;
username/password;
database existence;
user privileges; and
TLS requirements.

33. Troubleshooting — Existing SQLite Migrations Fail

This is why the tutorial recommends a fresh provider-specific migration set. Migration files are generated for the active provider and should not be assumed to be portable across database engines.

34. Final SQL Server Checklist

[ ] SQLite database backed up
[ ] Microsoft.EntityFrameworkCore.SqlServer 8.x installed
[ ] Program.cs uses UseSqlServer()
[ ] SQL Server connection string configured securely
[ ] Target database/server available
[ ] Fresh SQL Server migrations created
[ ] dotnet ef database update succeeds
[ ] Identity works
[ ] CRUD works
[ ] Dashboard works
[ ] Authorization works
[ ] Tests pass

35. Final MySQL Checklist

[ ] SQLite database backed up
[ ] EF Core 8-compatible MySQL provider installed
[ ] Program.cs uses the matching MySQL provider method
[ ] MySQL connection string configured securely
[ ] Target MySQL server/database available
[ ] Fresh MySQL migrations created
[ ] dotnet ef database update succeeds
[ ] Identity works
[ ] CRUD works
[ ] Dashboard works
[ ] Authorization works
[ ] Tests pass

36. Appendix Summary

The key lesson is that Entity Framework Core separates much of the application from the physical database engine.

MVC / Identity / ViewModels / LINQ ↓ ApplicationDbContext ↓ EF Core Provider ┌─────────┼─────────┐ SQLite SQL Server MySQL

The provider, connection string and migration strategy change, while most application logic remains intact.

Series extension complete

The Product Management application can now be treated not only as a SQLite tutorial project but also as a foundation for moving to SQL Server or MySQL when a server-based relational database is required.

ASP.NET CORE MVC - Deploying on Linux

Migrating from SQLite to SQL Server or MySQL

1. What Changes and What Stays the Same?

2. Back Up the Existing SQLite Database First

3. Check the Current EF Core Packages

4. Why Provider Versions Matter

5. Choose One Path

PATH A — SQLite to SQL Server

6. Install the SQL Server EF Core Provider

7. Update Program.cs for SQL Server

8. Change the SQL Server Connection String

9. Do Not Reuse SQLite Migrations Blindly

10. Create a Fresh SQL Server Migration Set

11. Create the SQL Server Schema

12. Verify SQL Server Connectivity

13. SQL Server Production Configuration

PATH B — SQLite to MySQL

14. Restore the Project Before Choosing MySQL

15. Install the Official MySQL EF Core Provider

16. Update Program.cs for MySQL

17. Change the MySQL Connection String

18. Create a Fresh MySQL Migration Set

19. Create the MySQL Schema

20. Verify MySQL Connectivity

21. Optional MySQL Alternative: Pomelo

22. What Happens to the Old SQLite Data?

23. Recommended Learning Approach for Existing Data

24. Why Identity Data Needs Special Care

25. A More Advanced Multi-Provider Strategy

26. Provider-Specific Behaviour Still Exists

27. Re-run the Automated Tests

28. Update Production Deployment Configuration

29. SQLite, SQL Server or MySQL?

30. Troubleshooting — UseSqlServer Cannot Be Found

31. Troubleshooting — UseMySQL Cannot Be Found

32. Troubleshooting — Connection Refused

33. Troubleshooting — Existing SQLite Migrations Fail

34. Final SQL Server Checklist

35. Final MySQL Checklist

36. Appendix Summary