Database Server

Microsoft SQL Server

Overview

Microsoft SQL Server is an enterprise relational database management system (RDBMS) developed and provided by Microsoft. As a high-performance database solution that runs primarily on Windows platforms and now also on Linux environments, it is widely adopted by enterprises worldwide. Since the release of its first version in 1989, it has undergone continuous enhancements and has now evolved into a comprehensive data platform integrating high availability, business intelligence, and machine learning capabilities.

Details

SQL Server is a comprehensive database platform designed to meet enterprise-level data management requirements. In addition to standard SQL, it provides T-SQL (Transact-SQL) with proprietary extensions, supporting advanced programming features such as stored procedures, triggers, and user-defined functions.

Key features include high availability and disaster recovery through Always On Availability Groups, automatic performance tuning with Query Store, accelerated analytical queries with columnstore indexes, and high-speed transaction processing with In-Memory OLTP.

SQL Server offers multiple editions: Express (free), Standard, Enterprise, and Developer editions. The Enterprise edition provides features necessary for mission-critical workloads, including Always On Availability Groups supporting up to 8 secondary replicas, automatic tuning, and advanced encryption capabilities.

Recently, cloud-native managed services are also available as Azure SQL Database and Azure SQL Managed Instance, enabling hybrid deployments between on-premises and cloud. Additionally, SQL Server 2017 and later versions added Linux support, making it possible to run in Docker containers.

Pros and Cons

Pros

• High integration with Windows: Seamless integration with Active Directory, .NET Framework, and PowerShell
• Comprehensive toolset: Rich management and development tools including SQL Server Management Studio (SSMS) and SQL Server Data Tools (SSDT)
• Excellent performance: High-speed processing through Query Store, automatic tuning, columnstore indexes, and In-Memory OLTP
• High availability solutions: Always On Availability Groups, failover clustering, and database mirroring
• Integrated BI capabilities: SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS), and SQL Server Integration Services (SSIS)
• Security features: Transparent Data Encryption (TDE), Always Encrypted, row-level security, and dynamic data masking
• Cross-platform: Support for Windows, Linux, and Docker environments
• Azure integration: Seamless integration with Azure SQL Database and Azure Synapse Analytics

Cons

• Licensing cost: Enterprise edition is expensive with complex per-core licensing model
• Resource consumption: Requires significant memory and CPU with proper sizing being critical
• Windows-centric design: Linux version has feature limitations, with some features only available on Windows
• Complex management: Specialized knowledge and experience required to leverage advanced features
• Upgrade complexity: Major version upgrades require careful planning and testing
• Vendor lock-in: Dependency on T-SQL specific features makes migration to other databases difficult
• Size limitations: Express edition has a 10GB database size limit

Reference Pages

• Microsoft SQL Server Official Site
• SQL Server Documentation
• SQL Server Management Studio (SSMS)
• Azure SQL Database
• SQL Server Data Tools (SSDT)
• SQL Server Express Edition (Free)
• SQL Server Sample Databases
• SQL Server Community

Code Examples

Basic Connection and Query

-- Connect using SQL Server Management Studio or sqlcmd
-- Windows Authentication
sqlcmd -S localhost\SQLEXPRESS -E
-- SQL Server Authentication
sqlcmd -S localhost\SQLEXPRESS -U sa -P password

-- Basic SELECT statement
SELECT 
    ProductID,
    Name,
    ProductNumber,
    StandardCost,
    ListPrice,
    ListPrice - StandardCost AS Profit
FROM Production.Product
WHERE ListPrice > 0
ORDER BY Profit DESC;

-- Create a table
CREATE TABLE Sales.Customer (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    CreatedDate DATETIME2 DEFAULT SYSDATETIME(),
    ModifiedDate DATETIME2 DEFAULT SYSDATETIME()
);

T-SQL Stored Procedure

-- Create a stored procedure
CREATE OR ALTER PROCEDURE Sales.usp_GetCustomerOrders
    @CustomerID INT,
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Set default parameter values
    IF @StartDate IS NULL
        SET @StartDate = DATEADD(YEAR, -1, GETDATE());
    
    IF @EndDate IS NULL
        SET @EndDate = GETDATE();
    
    -- Query with error handling
    BEGIN TRY
        SELECT 
            o.OrderID,
            o.OrderDate,
            o.TotalAmount,
            od.ProductID,
            p.Name AS ProductName,
            od.Quantity,
            od.UnitPrice
        FROM Sales.Orders o
        INNER JOIN Sales.OrderDetails od ON o.OrderID = od.OrderID
        INNER JOIN Production.Product p ON od.ProductID = p.ProductID
        WHERE o.CustomerID = @CustomerID
            AND o.OrderDate BETWEEN @StartDate AND @EndDate
        ORDER BY o.OrderDate DESC;
        
        -- Also return order summary
        SELECT 
            COUNT(DISTINCT OrderID) AS TotalOrders,
            SUM(TotalAmount) AS TotalSpent,
            AVG(TotalAmount) AS AverageOrderValue
        FROM Sales.Orders
        WHERE CustomerID = @CustomerID
            AND OrderDate BETWEEN @StartDate AND @EndDate;
            
    END TRY
    BEGIN CATCH
        -- Get error information
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            ERROR_LINE() AS ErrorLine,
            ERROR_PROCEDURE() AS ErrorProcedure;
        
        -- Re-throw the error
        THROW;
    END CATCH
END;
GO

-- Execute the stored procedure
EXEC Sales.usp_GetCustomerOrders 
    @CustomerID = 123,
    @StartDate = '2024-01-01',
    @EndDate = '2024-12-31';

Always On Availability Groups

-- Create availability group (execute on primary replica)
CREATE AVAILABILITY GROUP [AG_Production]
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    DB_FAILOVER = ON,
    DTC_SUPPORT = PER_DB
)
FOR DATABASE [AdventureWorks], [Sales]
REPLICA ON 
    N'SQL-SERVER-01' WITH (
        ENDPOINT_URL = N'TCP://SQL-SERVER-01.domain.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY,
                      READ_ONLY_ROUTING_URL = N'TCP://SQL-SERVER-01.domain.com:1433')
    ),
    N'SQL-SERVER-02' WITH (
        ENDPOINT_URL = N'TCP://SQL-SERVER-02.domain.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY,
                      READ_ONLY_ROUTING_URL = N'TCP://SQL-SERVER-02.domain.com:1433')
    );

-- Create listener
ALTER AVAILABILITY GROUP [AG_Production]
ADD LISTENER N'AG-LISTENER' (
    WITH IP ((N'192.168.1.100', N'255.255.255.0')),
    PORT = 1433
);

-- Configure read-only routing
ALTER AVAILABILITY GROUP [AG_Production]
MODIFY REPLICA ON N'SQL-SERVER-01'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL-SERVER-02','SQL-SERVER-01')));

Performance Tuning

-- Enable Query Store
ALTER DATABASE [AdventureWorks] 
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1000,
    INTERVAL_LENGTH_MINUTES = 60,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200
);

-- Analyze execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT c.CustomerID, c.FirstName, c.LastName,
       COUNT(o.OrderID) AS OrderCount,
       SUM(o.TotalAmount) AS TotalSpent
FROM Sales.Customer c
LEFT JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= DATEADD(MONTH, -6, GETDATE())
GROUP BY c.CustomerID, c.FirstName, c.LastName
HAVING COUNT(o.OrderID) > 5
ORDER BY TotalSpent DESC;

-- Create columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON Sales.FactSales
WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0);

-- Enable automatic tuning
ALTER DATABASE [AdventureWorks] 
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

In-Memory OLTP

-- Add memory-optimized filegroup
ALTER DATABASE [AdventureWorks] 
ADD FILEGROUP [InMemory_FG] CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE [AdventureWorks] 
ADD FILE (
    NAME = N'InMemory_File',
    FILENAME = N'C:\Data\AdventureWorks_InMemory.ndf'
) TO FILEGROUP [InMemory_FG];

-- Create memory-optimized table
CREATE TABLE Sales.OrdersInMemory (
    OrderID INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    Status TINYINT NOT NULL,
    
    INDEX IX_CustomerID NONCLUSTERED (CustomerID),
    INDEX IX_OrderDate NONCLUSTERED (OrderDate)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- Natively compiled stored procedure
CREATE PROCEDURE Sales.usp_InsertOrderInMemory
    @CustomerID INT,
    @TotalAmount DECIMAL(18,2)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
    INSERT INTO Sales.OrdersInMemory (CustomerID, OrderDate, TotalAmount, Status)
    VALUES (@CustomerID, SYSDATETIME(), @TotalAmount, 1);
END;

Security Features

-- Enable Transparent Data Encryption (TDE)
-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password_123!';

-- Create certificate
CREATE CERTIFICATE TDE_Certificate 
WITH SUBJECT = 'TDE Certificate for AdventureWorks';

-- Create database encryption key
USE [AdventureWorks];
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

-- Enable TDE
ALTER DATABASE [AdventureWorks]
SET ENCRYPTION ON;

-- Configure Always Encrypted
CREATE COLUMN MASTER KEY [CMK_AlwaysEncrypted]
WITH (
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/Certificate_Thumbprint'
);

CREATE COLUMN ENCRYPTION KEY [CEK_AlwaysEncrypted]
WITH VALUES (
    COLUMN_MASTER_KEY = [CMK_AlwaysEncrypted],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x...
);

-- Row-Level Security
CREATE FUNCTION Security.fn_SecurityPredicate(@UserID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    SELECT 1 AS fn_SecurityPredicate_Result
    WHERE @UserID = USER_ID() OR IS_ROLEMEMBER('db_owner') = 1
);

CREATE SECURITY POLICY Sales.CustomerSecurityPolicy
ADD FILTER PREDICATE Security.fn_SecurityPredicate(UserID) 
ON Sales.Customer
WITH (STATE = ON);