Skip to content

SQL Server Setup Guide

This guide explains how to set up Microsoft SQL Server as the storage backend for Cesivi Server.

Overview

SQL Server is the recommended storage provider for enterprise Windows/Azure deployments. It offers: - Enterprise-grade reliability and performance - Full transaction support (ACID) - Multi-server support (load balancing) - Advanced backup/restore and high availability - Full-text search capabilities

Prerequisites

  • SQL Server 2019 or later (Express, Standard, or Enterprise)
  • .NET 10.0 SDK (for running Cesivi Server)
  • Windows Server 2019+ or Windows 10/11 (for development)
  • Azure SQL Database (alternative to on-premises)

Installation Options

Option 1: SQL Server Express (Free, Development)

Best for: Development, small production (<10 users)

Download: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

# Download SQL Server 2022 Express
Invoke-WebRequest -Uri "https://go.microsoft.com/fwlink/p/?linkid=2216019" -OutFile "SQLServer2022-SSWEB-Express.exe"

# Run installer
.\SQLServer2022-SSWEB-Express.exe

Installation Steps: 1. Choose "Basic" installation type 2. Accept license terms 3. Choose installation location 4. Wait for installation to complete 5. Note the connection string (usually: Server=.\SQLEXPRESS;...)

Limitations: - 10 GB database size limit - Single instance - No SQL Server Agent (no scheduled jobs)

Option 2: SQL Server Developer (Free, Full Features)

Best for: Development with full SQL Server features

Download: Same as Express (choose "Download Media" → "Developer")

Installation Steps: 1. Run setup.exe 2. Choose "New SQL Server stand-alone installation" 3. Accept license terms 4. Choose features: - ✅ Database Engine Services - ✅ Full-Text and Semantic Extractions for Search (optional) - ❌ Analysis Services (not needed) - ❌ Reporting Services (not needed) 5. Choose default instance or named instance 6. Choose authentication mode: - Windows Authentication Mode (recommended for development) - Mixed Mode (if you need SQL logins) 7. Add current user as administrator 8. Complete installation

No Limitations: Full SQL Server features for non-production use.

Option 3: SQL Server Standard/Enterprise (Commercial)

Best for: Production deployments

Purchase: Contact Microsoft or your reseller Download: Via Volume Licensing Service Center (VLSC)

Installation: Same as Developer edition.

Features: - Always On Availability Groups (High Availability) - Always On Failover Cluster Instances - Database mirroring - Log shipping - Online indexing - Advanced security

Option 4: Azure SQL Database (Cloud)

Best for: Cloud-native, serverless, or Azure-hosted deployments

Setup via Azure Portal:

  1. Navigate to https://portal.azure.com
  2. Create new resource → SQL Database
  3. Configure:
  4. Resource Group: Create new or select existing
  5. Database Name: Cesivi
  6. Server: Create new Azure SQL Server
    • Server name: spm-yourcompany
    • Location: Choose region
    • Authentication: SQL authentication
    • Admin login: spmadmin
    • Password: Create strong password
  7. Compute + Storage: Choose tier
    • Development: Basic (5 DTUs, $5/month)
    • Production: Standard or Premium
  8. Networking:
  9. Allow Azure services: Yes
  10. Add client IP: Yes (your current IP)
  11. Review + Create → Create

Connection String: 

Server=tcp:spm-yourcompany.database.windows.net,1433;Initial Catalog=Cesivi;User ID=spmadmin;Password=YourPassword;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Database Setup

1. Create Database

Using SQL Server Management Studio (SSMS)

  1. Download SSMS: https://aka.ms/ssmsfullsetup
  2. Install SSMS
  3. Connect to SQL Server (Windows Authentication)
  4. Right-click Databases → New Database
  5. Database name: Cesivi
  6. Click OK

Using Command Line (sqlcmd)

# Connect to SQL Server
sqlcmd -S . -E

# Create database
CREATE DATABASE Cesivi;
GO

# Verify
USE Cesivi;
GO
SELECT DB_NAME();
GO

Using T-SQL Script

CREATE DATABASE Cesivi
ON PRIMARY
(
    NAME = Cesivi_Data,
    FILENAME = 'C:\SQLData\Cesivi.mdf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
)
LOG ON
(
    NAME = Cesivi_Log,
    FILENAME = 'C:\SQLData\Cesivi_log.ldf',
    SIZE = 50MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10MB
);
GO

2. Create User (Optional, for SQL Authentication)

If using SQL Authentication instead of Windows Authentication:

USE master;
GO

-- Create login
CREATE LOGIN spmuser WITH PASSWORD = 'YourSecurePassword123!';
GO

-- Create user in Cesivi database
USE Cesivi;
GO

CREATE USER spmuser FOR LOGIN spmuser;
GO

-- Grant permissions
ALTER ROLE db_owner ADD MEMBER spmuser;
GO

3. Enable TCP/IP (If Remote Connections Needed)

By default, SQL Server Express only allows local connections. To enable remote:

  1. Open SQL Server Configuration Manager
  2. Expand SQL Server Network Configuration
  3. Click Protocols for MSSQLSERVER (or your instance name)
  4. Right-click TCP/IP → Enable
  5. Right-click TCP/IP → Properties
  6. IP Addresses tab → IPALL
  7. TCP Dynamic Ports: (leave blank)
  8. TCP Port: 1433
  9. Click OK
  10. Restart SQL Server service: 
    Restart-Service MSSQLSERVER

4. Firewall Rules (If Remote Connections Needed)

# Allow SQL Server through Windows Firewall
New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

# Verify
Get-NetFirewallRule -DisplayName "SQL Server"

EF Core Migrations

Cesivi Server uses Entity Framework Core for database schema management.

1. Install EF Core Tools

dotnet tool install --global dotnet-ef

2. Create Initial Migration

cd Cesivi.Storage.SqlServer

# Create migration
dotnet ef migrations add InitialCreate --context CesiviDbContext

# Apply migration to database
dotnet ef database update --context CesiviDbContext --connection "Server=.;Database=Cesivi;Integrated Security=true;TrustServerCertificate=true"

3. Verify Schema

USE Cesivi;
GO

-- List all tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

-- Expected tables:
-- - WebApplications
-- - SiteCollections
-- - Webs
-- - Lists
-- - ListItems
-- - Files
-- - Folders
-- - Users
-- - Groups
-- - GroupMemberships
-- - ContentTypes
-- - Fields
-- - Views
-- - RoleDefinitions
-- - RoleAssignments
-- - RecycleBinItems
-- - FileVersions
-- - ListItemVersions
-- - Attachments

Configuration

appsettings.json

{
  "Cesivi": {
    "StorageProvider": "SqlServer",
    "SqlServer": {
      "ConnectionString": "Server=.;Database=Cesivi;Integrated Security=true;TrustServerCertificate=true",
      "EnableRetryOnFailure": true,
      "MaxRetryCount": 3,
      "CommandTimeout": 30,
      "EnableSensitiveDataLogging": false,
      "EnableFullTextSearch": false
    }
  }
}

Connection String Examples

Windows Authentication (Recommended for Development): 

Server=.;Database=Cesivi;Integrated Security=true;TrustServerCertificate=true

SQL Authentication: 

Server=localhost;Database=Cesivi;User Id=spmuser;Password=YourPassword123!;TrustServerCertificate=true

Named Instance: 

Server=.\\SQLEXPRESS;Database=Cesivi;Integrated Security=true;TrustServerCertificate=true

Remote Server: 

Server=sql-server.example.com,1433;Database=Cesivi;User Id=spmuser;Password=YourPassword123!;TrustServerCertificate=true

Azure SQL Database: 

Server=tcp:spm-yourcompany.database.windows.net,1433;Initial Catalog=Cesivi;User ID=spmadmin;Password=YourPassword;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Performance Optimization

1. Create Indexes

The EF Core migrations create basic indexes, but you can add more for your specific queries:

USE Cesivi;
GO

-- Index on ListItems.FieldValues (for metadata queries)
-- Note: JSON columns already have indexes from migrations

-- Index on Files.Modified (for sorting)
CREATE NONCLUSTERED INDEX IX_Files_Modified
ON Files (Modified DESC);
GO

-- Index on ListItems.Created (for sorting)
CREATE NONCLUSTERED INDEX IX_ListItems_Created
ON ListItems (Created DESC);
GO

2. Enable Full-Text Search (Optional)

Prerequisites: - SQL Server Full-Text Search feature installed - Database has default full-text catalog

Setup:

USE Cesivi;
GO

-- Create full-text catalog
CREATE FULLTEXT CATALOG CesiviCatalog AS DEFAULT;
GO

-- Create full-text index on Files (Title, content)
CREATE FULLTEXT INDEX ON Files
(
    Title LANGUAGE 1033,
    ContentType LANGUAGE 1033
)
KEY INDEX PK_Files
ON CesiviCatalog
WITH STOPLIST = SYSTEM;
GO

-- Create full-text index on ListItems (Title)
CREATE FULLTEXT INDEX ON ListItems
(
    Title LANGUAGE 1033
)
KEY INDEX PK_ListItems
ON CesiviCatalog
WITH STOPLIST = SYSTEM;
GO

Enable in Configuration:

{
  "Cesivi": {
    "SqlServer": {
      "EnableFullTextSearch": true
    }
  }
}

Search Query Example:

-- Search for documents containing "invoice"
SELECT * FROM Files
WHERE CONTAINS(Title, 'invoice')
   OR CONTAINS(ContentType, 'invoice');

3. Configure Memory

For production, increase SQL Server memory allocation:

-- Set maximum memory to 4GB (adjust based on server RAM)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max server memory (MB)', 4096;
RECONFIGURE;

4. Enable Read Committed Snapshot

Reduce locking contention:

ALTER DATABASE Cesivi
SET READ_COMMITTED_SNAPSHOT ON;

Backup and Restore

Backup

-- Full backup
BACKUP DATABASE Cesivi
TO DISK = 'C:\Backups\Cesivi_Full.bak'
WITH FORMAT, COMPRESSION, STATS = 10;

-- Differential backup (after full backup)
BACKUP DATABASE Cesivi
TO DISK = 'C:\Backups\Cesivi_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;

-- Transaction log backup (for point-in-time recovery)
BACKUP LOG Cesivi
TO DISK = 'C:\Backups\Cesivi_Log.trn'
WITH COMPRESSION, STATS = 10;

Restore

-- Restore from full backup
RESTORE DATABASE Cesivi
FROM DISK = 'C:\Backups\Cesivi_Full.bak'
WITH REPLACE, RECOVERY, STATS = 10;

-- Restore with point-in-time recovery
RESTORE DATABASE Cesivi
FROM DISK = 'C:\Backups\Cesivi_Full.bak'
WITH NORECOVERY;

RESTORE LOG Cesivi
FROM DISK = 'C:\Backups\Cesivi_Log.trn'
WITH RECOVERY, STOPAT = '2026-01-11 14:30:00';

High Availability (Optional)

Always On Availability Groups

Requirements: - SQL Server Enterprise Edition (or Standard Edition 2016+) - Windows Server Failover Cluster (WSFC) - Shared storage or replicated storage

Setup: See Microsoft documentation: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server

Database Mirroring

Setup:

-- On principal server
ALTER DATABASE Cesivi SET PARTNER = 'TCP://mirror-server.example.com:5022';

-- On mirror server
ALTER DATABASE Cesivi SET PARTNER = 'TCP://principal-server.example.com:5022';

Connection String with Failover: 

Server=principal-server;Database=Cesivi;Failover Partner=mirror-server;Integrated Security=true;TrustServerCertificate=true

Troubleshooting

Common Issues

1. Cannot connect to SQL Server

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server

Solutions: - Verify SQL Server service is running: Get-Service MSSQLSERVER - Check SQL Server Configuration Manager → TCP/IP is enabled - Verify firewall rules allow port 1433 - Check connection string (server name, instance name) - For named instances, ensure SQL Server Browser service is running

2. Login failed for user

Error: Login failed for user 'spmuser'

Solutions: - Verify user exists: SELECT * FROM sys.database_principals WHERE name = 'spmuser'; - Verify user has permissions: EXEC sp_helpuser 'spmuser'; - For Windows Authentication, verify user is in correct domain - For SQL Authentication, verify password is correct

3. Database does not exist

Error: Cannot open database "Cesivi" requested by the login

Solutions: - Create database: CREATE DATABASE Cesivi; - Verify database name in connection string - Check database is online: SELECT state_desc FROM sys.databases WHERE name = 'Cesivi';

4. Timeout expired

Error: Timeout expired. The timeout period elapsed prior to completion of the operation

Solutions: - Increase CommandTimeout in configuration (default: 30 seconds) - Check for long-running queries: SELECT * FROM sys.dm_exec_requests; - Optimize slow queries (add indexes, update statistics) - Increase server resources (CPU, RAM)

5. Migration fails with constraint violation

Error: The INSERT statement conflicted with the FOREIGN KEY constraint

Solutions: - Ensure source data integrity (no orphaned records) - Temporarily disable constraints during migration (see STORAGE_MIGRATION.md) - Fix source data and retry

Monitoring

Query Performance

-- Top 10 slowest queries
SELECT TOP 10
    total_worker_time/execution_count AS avg_cpu_time,
    total_elapsed_time/execution_count AS avg_elapsed_time,
    execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_elapsed_time DESC;

Database Size

SELECT
    DB_NAME() AS DatabaseName,
    SUM(size) * 8 / 1024 AS SizeMB
FROM sys.master_files
WHERE database_id = DB_ID()
GROUP BY database_id;

Active Connections

SELECT
    DB_NAME(database_id) AS DatabaseName,
    COUNT(*) AS ConnectionCount
FROM sys.dm_exec_sessions
WHERE database_id > 0
GROUP BY database_id;

See Also

External Resources