MSDN Tutorials

SQL Server Administration

Overview

SQL Server administration covers the tasks required to install, configure, monitor, and maintain a SQL Server environment.

  • Installation & Configuration
  • Security Management
  • Backup & Recovery
  • Performance Optimization
  • High Availability & Disaster Recovery

Installation & Configuration

Use the SQL Server Installation Center or command‑line options for silent installs.

Setup.exe /Q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT Service\MSSQLSERVER"

After installation, configure the server settings via sp_configure or the SQL Server Configuration Manager.

Security & Auditing

Implement the principle of least privilege using roles and permissions.

  • Create logins: CREATE LOGIN [app_user] WITH PASSWORD='StrongP@ssw0rd';
  • Map to database user: CREATE USER [app_user] FOR LOGIN [app_user];
  • Grant role: EXEC sp_addrolemember 'db_datareader', 'app_user';

Enable SQL Server Audit to capture security‑relevant events.

Backup & Restore

Schedule regular full, differential, and transaction‑log backups.

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\Backups\AdventureWorks_Full.bak' WITH INIT, COMPRESSION;

Restore sequence:

RESTORE DATABASE [AdventureWorks] FROM DISK = N'C:\Backups\AdventureWorks_Full.bak' WITH NORECOVERY; RESTORE LOG [AdventureWorks] FROM DISK = N'C:\Backups\AdventureWorks_Log.trn' WITH RECOVERY;

Performance Tuning

Monitor key metrics with sys.dm_exec_query_stats, sys.dm_os_wait_stats, and SQL Server Profiler.

SELECT TOP 10 qs.total_elapsed_time/qs.execution_count AS AvgTimeMs, SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS QueryText FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY AvgTimeMs DESC;

Use the Database Engine Tuning Advisor to generate index recommendations.

High Availability

Choose from Always On Availability Groups, Failover Cluster Instances, or Log Shipping based on business needs.

  • AGs: Automatic failover, readable secondary replicas.
  • FCI: Shared storage, Windows Server failover clustering.
  • Log Shipping: Simple, asynchronous standby.

Monitoring & Alerts

Set up SQL Server Agent jobs for routine checks and configure alerts for critical errors.

EXEC msdb.dbo.sp_add_alert @name=N'Long Running Query', @severity=0, @message_id=0, @wmi_namespace=N'\\\\.\\root\\Microsoft\\SqlServer\\ServerEvents', @wmi_query=N'SELECT * FROM __InstanceCreationEvent ' + N'WHERE TargetInstance ISA ''SqlServer:QueryStatistics'' ' + N'AND TargetInstance.Duration > 60000';