Connecting to MS SQL Server

SQL Server Editions

• SQL Server Express — free, 10GB DB limit
• LocalDB — Express-এর dev সংস্করণ
• Developer — full feature, dev only, free
• Standard / Enterprise — paid
• Azure SQL — cloud, managed

Tools

• SSMS (SQL Server Management Studio) — Windows GUI
• Azure Data Studio — cross-platform
• dotnet-ef CLI

Connection String Examples

// 1. LocalDB (dev)
"Server=(localdb)\mssqllocaldb;Database=MyDb;Trusted_Connection=True;"

// 2. Local SQL Server with Windows auth
"Server=localhost;Database=MyDb;Trusted_Connection=True;TrustServerCertificate=True;"

// 3. SQL Server with username/password
"Server=localhost;Database=MyDb;User Id=sa;Password=YourPassword;TrustServerCertificate=True;"

// 4. Named instance
"Server=localhost\SQLEXPRESS;Database=MyDb;Trusted_Connection=True;"

// 5. Custom port
"Server=tcp:server.com,1433;Database=MyDb;User Id=sa;Password=...;"

// 6. Azure SQL
"Server=tcp:myserver.database.windows.net,1433;Database=MyDb;User Id=admin;Password=...;Encrypt=True;"

// 7. Connection pooling tuning
"...;Min Pool Size=5;Max Pool Size=100;Connection Timeout=30;"

Configure in Program.cs

builder.Services.AddDbContext<AppDbContext>(opt => {
    var connStr = builder.Configuration.GetConnectionString("Default");
    opt.UseSqlServer(connStr, sql => {
        sql.EnableRetryOnFailure(maxRetryCount: 3);
        sql.CommandTimeout(60);
        sql.MigrationsAssembly("MyApi");
    });

    if (builder.Environment.IsDevelopment()) {
        opt.EnableSensitiveDataLogging();
        opt.EnableDetailedErrors();
        opt.LogTo(Console.WriteLine, LogLevel.Information);
    }
});

Health Check

# dotnet add package AspNetCore.HealthChecks.SqlServer

builder.Services.AddHealthChecks()
    .AddSqlServer(builder.Configuration.GetConnectionString("Default"));

app.MapHealthChecks("/health");

Test Connection (Quick)

using var conn = new SqlConnection(connectionString);
await conn.OpenAsync();
var cmd = new SqlCommand("SELECT @@VERSION", conn);
var version = await cmd.ExecuteScalarAsync();
Console.WriteLine(version);

EF Core Database-First (Scaffold)

# বিদ্যমান DB থেকে Entity তৈরি
dotnet ef dbcontext scaffold "Server=...;Database=MyDb;..." \
    Microsoft.EntityFrameworkCore.SqlServer \
    --output-dir Models \
    --context AppDbContext \
    --use-database-names

Common Errors

Error	সমাধান
Cannot connect	SQL Server service running? Firewall? port 1433?
Login failed	SQL auth enabled? Mixed mode?
Certificate error	TrustServerCertificate=True (dev only)
Timeout	Connection Timeout বাড়ান