SQL Server Administration

The course SQL Server Administration from SpiralTrain is designed for individuals who want to learn how to effectively manage and maintain a Microsoft SQL Server database.

SQL Server Intro

The course SQL Server Administration starts with an overview of SQL Server and its various components, such as the database engine, SQL Server Management Studio, and Transact-SQL.

Server Configuration

Next attention is paid to configuring the SQL Server instance. The various settings including memory, network protocols and collation are discussed in this respect.

Database Configuration

Also the creation and configuration of databases, including filegroups, file sizes, and file placement is on the schedule of the course SQL Server Administration. Table partitioning and table compression are covered as well.

Indexes

Another subject in the course content is which different types of indexes are available in SQL Server and how to create and maintain them for optimal performance.

Database Consistency

Furthermore the importance of maintaining database consistency is discussed. Different techniques for ensuring the integrity of data in SQL Server like checking and fixing errors, checking consistency and emergency mode are covered.

SQL Server Security

An important subject covered in the course SQL Server Administration are the various security features available in SQL Server, including authentication, authorization, and encryption.

Backup and Restore

Also how to create and manage backups of an SQL Server databases, as well as how to restore them in case of disaster is a topic.

High Availability

Then the strategies for ensuring the availability of SQL Server databases, including clustering, log shipping and replication are treated. And also attention is paid to failover and recovery modes.

Performance

Finally the techniques for improving the performance of SQL Server databases, including index tuning, query optimization and monitoring performance are discussed.

Audience Course SQL Server Administration

The course SQL Server Administration is intended for database administrators who need to manage, monitor and support Microsoft SQL Server databases and Microsoft SQL Server servers.

Prerequisites SQL Server Administration

Prior knowledge of the SQL query language and databases is required for participation in the course SQL Server Administration.

Realization training SQL Server Administration

The course material is covered on the basis of presentations. Demos clarify the theory and practical exercises ensure that the material well understood.

Certificate SQL Server Administration

After successful completion of the course participants receive a certificate SQL Server Administration.

Modules

Module 1 : SQL Server Intro

• SQL Server Editions
• Storage Requirements
• OS Considerations
• Database Engine Service
• Analysis Server
• Data Quality Client
• Connectivity Tools
• Integration Services
• GUI Installation
• Standalone Instance
• Instance Configuration
• Feature Selection

Module 2 : Server Configuration

• Instance Configuration
• Using sp_configure
• Processor Configuration
• Memory Configuration
• Trace Flags
• Ports and Firewalls
• System Databases
• mssqlsystemresource
• MSDB
• Master and Model
• Buffer Pool
• Hybrid Buffer Pool

Module 3 : Database Configuration

• Data Storage
• Filegroups
• Filegroup Maintenance
• Memory-Optimized Filegroups
• Scoped Configurations
• Recovery Model
• Log Fragmentation
• Table Partitioning
• Partition Elimination
• Table Compression
• Durability
• Compiled Objects

Module 4 : Indexes

• Clustered Indexes
• Primary Key Clustering
• Nonclustered Indexes
• Covering Indexes
• Filtered Indexes
• In-Memory Indexes
• Maintaining Indexes
• Index Fragmentation
• Filtered Statistics
• Incremental Statistics
• Managing Statistics

Module 5 : Database Consistency

• Consistency Errors
• Page Verify Option
• Suspect Pages
• Master DB Corruption
• Corruption of Binaries
• DBCC CHECKDB
• Checking for Errors
• Fixing Errors
• Emergency Mode
• DBCC Commands
• Consistency Checks

Module 6 : SQL Server Security

• Security Hierarchy
• Server Roles
• Logins
• Granting Permissions
• Database Level Security
• Schemas
• Object Level Security
• Server Audits
• Security Reports
• SQL Data Discovery
• Vulnerability Assessment

Module 7 : Backup and Restore

• Backup Fundamentals
• Recovery Models
• Backup Types
• Partial Backup
• Transaction Log Backup
• Backup Strategies
• Restoring a Database
• Restoring via T-SQL
• Using Management Studio
• Restoring Files
• Restoring Pages

Module 8 : High Availability

• Availability Concepts
• Cost of Downtime
• Standby Servers
• Failover Clustering
• Automatic Page Repair
• Recovery Modes
• Availability Groups
• Log Shipping
• Scaling Workloads
• Database Snapshots
• Replication

Module 9 : Performance

• SQL Server Metadata
• Buffer Cache Usage
• Exposing File Stats
• Performance Tuning
• Retrieving Perfmon Counters
• Analyzing Waits
• Cycling Snapshots
• Rebuilding Indexes
• Lock Granularity
• Understanding Deadlocks
• Transactional Properties