Duration
3 days
Learning Objectives:
By the end of this course, participants will:
- Understand the core components and architecture of MS SQL Server.
- Install, configure, and manage SQL Server instances.
- Design and implement databases, tables, and indexes.
- Develop and execute T-SQL queries for data retrieval and manipulation.
- Perform basic and advanced SQL Server security and permissions management.
- Set up and automate backups, restore databases, and implement disaster recovery plans.
- Monitor and optimize database performance.
- Troubleshoot common SQL Server issues.
Prerequisites:
- Basic knowledge of relational database concepts
- Familiarity with Windows Server operating system
- Experience using basic command-line tools
- Some experience in writing SQL queries is beneficial, though not required
Module 1: Introduction to SQL Server
- Overview of SQL Server editions and versions
- SQL Server architecture and components
- Installing SQL Server (hands-on)
- Introduction to SQL Server Management Studio (SSMS)
Module 2: Database Design and Creation
- Database fundamentals: tables, schemas, and normalization
- Creating databases and tables
- Understanding data types and constraints
- Introduction to Indexes: clustered vs. non-clustered indexes
Module 3: Basic T-SQL Querying
- Overview of T-SQL and its syntax
- Writing basic SELECT statements
- Filtering data with WHERE clause
- Sorting and grouping data
Module 4: Advanced Querying Techniques
- Joins (INNER, OUTER, CROSS, SELF)
- Subqueries and common table expressions (CTEs)
- Aggregating data with GROUP BY and HAVING clauses
- Using Views and Stored Procedures
Module 5: Data Modification
- Inserting, updating, and deleting data
- Transaction management: COMMIT and ROLLBACK
- Understanding triggers and their uses
- Bulk data import/export techniques
Module 6: SQL Server Security
- Authentication and authorization in SQL Server
- Managing users and roles
- Implementing row-level security and encryption
- Best practices for SQL Server security
Module 7: Backup and Restore
- Types of backups: Full, Differential, Transaction Log
- Automating backups using SQL Server Agent
- Restoring databases from backups (hands-on)
- Planning and implementing a disaster recovery strategy
Module 8: Performance Tuning and Optimization
- Indexing strategies for performance
- Query optimization tips and best practices
- Analyzing query execution plans
- Monitoring performance with SQL Profiler and Database Engine Tuning Advisor
Module 9: SQL Server Maintenance and Troubleshooting
- Database consistency checks (DBCC)
- Implementing SQL Server Maintenance Plans
- Troubleshooting common performance and connection issues
- Introduction to high availability (Always On) and replication basics
Programme scope, delivery arrangements and suitability are confirmed during enquiry.