Database Creation — CREATE DATABASE, File Groups and Configuration

Creating a SQL Server database correctly from the start prevents configuration issues that are hard to fix later. Key decisions: recovery model (Simple vs Full — affects backup strategy and log file growth), AUTO_SHRINK and AUTO_CLOSE settings (both should be OFF in production — they cause performance problems), collation (affects string sorting and comparison), and compatibility level (affects which T-SQL syntax and query optimiser behaviour is available). The BlogApp database creation script establishes these settings explicitly rather than relying on defaults.

BlogApp Database Creation #

-- ── Create the BlogApp database with explicit configuration ────────────────
USE master;
GO

-- Create only if it doesn't exist
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'BlogApp')
BEGIN
    CREATE DATABASE BlogApp
    ON PRIMARY (
        NAME         = N'BlogApp_Data',
        FILENAME     = N'C:\SQLData\BlogApp.mdf',   -- explicit file path
        SIZE         = 128MB,                         -- initial size
        MAXSIZE      = UNLIMITED,
        FILEGROWTH   = 64MB                           -- grow in 64MB chunks
    )
    LOG ON (
        NAME         = N'BlogApp_Log',
        FILENAME     = N'C:\SQLData\BlogApp_log.ldf',
        SIZE         = 64MB,
        MAXSIZE      = 2048MB,
        FILEGROWTH   = 64MB
    );

    PRINT 'Database BlogApp created.';
END
ELSE
    PRINT 'Database BlogApp already exists.';
GO

-- ── Configure database settings ────────────────────────────────────────────
ALTER DATABASE BlogApp SET RECOVERY SIMPLE;       -- Simple for dev (Full for prod)
ALTER DATABASE BlogApp SET AUTO_CLOSE OFF;         -- Keep DB open (do not unload from memory)
ALTER DATABASE BlogApp SET AUTO_SHRINK OFF;        -- Never auto-shrink data files
ALTER DATABASE BlogApp SET AUTO_CREATE_STATISTICS ON;  -- Let SQL Server manage statistics
ALTER DATABASE BlogApp SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE BlogApp SET COMPATIBILITY_LEVEL = 160;  -- SQL Server 2022 level
GO

-- ── Verify the database configuration ─────────────────────────────────────
SELECT
    name,
    recovery_model_desc,
    is_auto_close_on,
    is_auto_shrink_on,
    compatibility_level,
    collation_name,
    state_desc
FROM sys.databases
WHERE name = 'BlogApp';
GO

-- ── Create application login and user ─────────────────────────────────────
USE master;
GO

-- Create SQL Server login (if it doesn't exist)
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = N'blogapp_user')
BEGIN
    CREATE LOGIN blogapp_user WITH PASSWORD = N'BlogApp_Str0ng!Pass#2025';
    PRINT 'Login blogapp_user created.';
END
GO

-- Create database user for the login
USE BlogApp;
GO
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = N'blogapp_user')
BEGIN
    CREATE USER blogapp_user FOR LOGIN blogapp_user;
    -- Grant data read/write (application permissions)
    ALTER ROLE db_datareader ADD MEMBER blogapp_user;
    ALTER ROLE db_datawriter ADD MEMBER blogapp_user;
    -- Grant DDL for EF Core migrations (remove in production if migrations run separately)
    ALTER ROLE db_ddladmin   ADD MEMBER blogapp_user;
    PRINT 'Database user blogapp_user created.';
END
GO

Common Mistakes #

Mistake 1 — Setting AUTO_SHRINK = ON (causes fragmentation and performance degradation) #

❌ Wrong — AUTO_SHRINK enabled on production database; SQL Server constantly shrinks and re-grows files; severe I/O overhead.

✅ Correct — always ALTER DATABASE X SET AUTO_SHRINK OFF; manage file sizes manually with DBCC SHRINKFILE only when explicitly needed.

Mistake 2 — Using FULL recovery model without transaction log backups (unbounded log growth) #

❌ Wrong — FULL recovery model on production but no log backups configured; transaction log grows until disk is full.

✅ Correct — either use SIMPLE recovery (less protection) or FULL recovery with scheduled transaction log backups every 15-60 minutes.