SQL Server Administration Guide

A full backup is a complete copy of your entire database. It's the base for all other backup types.

A differential backup backs up only the data that has changed since the *last full backup*. Restoring requires the last full backup and the latest differential backup.

A transaction log backup backs up the transaction log records since the last log backup. This allows for point-in-time recovery and is only available for databases in the Full or Bulk-Logged recovery model. Restoring requires the last full backup, any subsequent differential backups, and all log backups in sequence up to the desired point in time.

-- Example: Full Backup
BACKUP DATABASE [YourDatabase] TO DISK = N'C:\Backups\YourDatabase_Full.bak' WITH NOFORMAT, NOINIT, NAME = N'YourDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- Example: Differential Backup
BACKUP DATABASE [YourDatabase] TO DISK = N'C:\Backups\YourDatabase_Diff.bak' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'YourDatabase-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

-- Example: Transaction Log Backup
BACKUP LOG [YourDatabase] TO DISK = N'C:\Backups\YourDatabase_Log.trn' WITH NOFORMAT, NOINIT, NAME = N'YourDatabase-Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

SQL Server offers several tools for performance monitoring:

• SQL Server Management Studio (SSMS): Use Activity Monitor for real-time resource usage and identify bottlenecks.
• Dynamic Management Views (DMVs): These provide deep insights into server state and performance. Common DMVs include sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_os_wait_stats, and sys.dm_db_index_usage_stats.
• Performance Monitor (PerfMon): Windows' built-in tool can track SQL Server-specific counters.
• SQL Server Profiler: Captures detailed SQL Server events, useful for diagnosing query performance issues.
• Extended Events: A more lightweight and flexible tracing system than Profiler.

Key metrics to monitor include CPU utilization, memory usage, disk I/O, query execution times, and wait statistics.

-- Example DMV query for wait statistics
SELECT
    wait_type,
    wait_time_ms / 1000.0 AS WaitS,
    (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
    signal_wait_time_ms / 1000.0 AS SignalS,
    waiting_tasks_count AS WaitCount
FROM
    sys.dm_os_wait_stats
WHERE
    (wait_time_ms - signal_wait_time_ms) > 0
ORDER BY
    WaitS DESC;

Implementing robust security is crucial for any SQL Server environment. Key best practices include:

• Principle of Least Privilege: Grant users and applications only the permissions they absolutely need.
• Strong Password Policies: Enforce complex passwords for SQL Server logins.
• Use Windows Authentication: Wherever possible, use Windows authentication instead of SQL Server authentication for improved security and manageability.
• Regularly Patch and Update: Keep SQL Server and the underlying operating system up to date with the latest security patches.
• Encrypt Sensitive Data: Use features like Transparent Data Encryption (TDE) or Always Encrypted for sensitive information.
• Limit SQL Injection Vulnerabilities: Always use parameterized queries or stored procedures.
• Audit Database Access: Implement auditing to track who is accessing what data and when.
• Firewall Configuration: Ensure SQL Server is only accessible from trusted networks.

Avoid using the sa account for routine operations. Rename it if possible and disable it if not actively managed.

Query optimization involves several steps:

• Analyze Execution Plans: Use SSMS to view the execution plan for slow queries. This reveals where the database is spending most of its time.
• Indexing: Ensure appropriate indexes exist for columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses. Avoid over-indexing, as it can impact write performance.
• Update Statistics: Regularly update database statistics so the query optimizer has accurate information to generate efficient plans.
• Rewrite Inefficient Queries: Avoid cursors when set-based operations can be used, simplify complex WHERE clauses, and ensure JOINs are efficient.
• Denormalization: In some read-heavy scenarios, controlled denormalization can improve performance by reducing the need for complex joins.
• Query Hints: Use sparingly and with caution, as they can sometimes hinder performance if not fully understood.

Consider using tools like the Query Store in SQL Server 2016+ to track query performance over time and identify regressions.

-- Example: Updating Statistics
UPDATE STATISTICS [YourDatabase].[dbo].[YourTable] WITH FULLSCAN;

-- Example: Creating an Index
CREATE INDEX IX_YourTable_Column1 ON [YourDatabase].[dbo].[YourTable] (Column1);

The transaction log file (.ldf) is a critical component of SQL Server's reliability and recoverability. Its primary purposes are:

• Transaction Atomicity: It records every modification made to the database before the change is written to the data file. This ensures that transactions are atomic (either all changes are made, or none are).
• Durability: Once a transaction is committed and its record is written to the log, the change is considered durable, meaning it will survive a server crash.
• Recovery: In case of a server failure, SQL Server uses the transaction log to bring the database back to a consistent state. It replays committed transactions that might not have made it to the data files and rolls back any uncommitted transactions.
• Point-in-Time Recovery: Transaction log backups are essential for restoring a database to a specific point in time.
• Replication and High Availability: Features like AlwaysOn Availability Groups and Transactional Replication rely heavily on the transaction log.

It's important to monitor the transaction log size and ensure it is regularly backed up (especially in Full or Bulk-Logged recovery models) to prevent it from filling up, which can halt database activity.