SQL Server for Database Administrators Course Syllabus
1. Description
This course teaches the administration and maintenance aspects of Microsoft SQL Server. It covers all the roles performed by administrative DBAs, such as installation, security and availability. In each topic, the students will learn about the relevant tools and features within SQL Server as well as the best practices for using them. Throughout the course the students will practice each topic learned.
The course is based on SQL Server 2014, but it is relevant also for SQL Server 2005/2008/2012.
2. Target Audience
The course is intended for database administrators who are (already or going to be) responsible for one or more SQL Server instances. It is also suitable for IT professionals or support engineers who are required to perform database administration activities. Students should be familiar with relational database concepts, the syntax of Transact-SQL and Windows basics.
3. Objectives
Be familiar with SQL Server architecture
Learn how to efficiently administer and maintain SQL Server instances Learn how to properly install and configure a SQL Server instance Be able to plan and implement a disaster recovery solution
Acquire the knowledge of monitoring and troubleshooting using SQL Server tools Learn how to manage security with SQL Server
Discover high availability solutions within SQL Server
Acquire the knowledge of transferring data using SQL Server tools
4. Schedule
5 days
40 academic hours
5. Materials
Class exercises & solutions Homework
Additional resources
DVD with SQL Server 2014 RTM evaluation edition
6. Outline
a. Course Introduction (0.5 hour)
1) About the Instructor 2) About the Students 3) Course Objectives 4) Course Schedule 5) Course Materials 6) Course Outline
b. Introduction to Microsoft SQL Server (1.5 hours)
1) Databases – Relational and Others
2) The Database as Part of the System Architecture 3) The Role of the Database Administrator (DBA) 4) SQL Server Versions and Editions
5) SQL Server Services and Components 6) SQL Server Client Tools
7) Practice
c. Installing a SQL Server Instance (2 hours)
1) Preparing to Install SQL Server 2) Upgrade Considerations 3) The Installation Process 4) Configuring the Instance 5) Practice
d. Managing Databases (3.5 hours)
1) Overview of System Databases 2) Filegroups and Files
3) Introducing the Transaction Log 4) Database Configuration Options 5) Creating a Database
9) Data Compression 10) Contained Databases 11) Practice
e. Backup and Recovery (3 hours)
1) Backup Types
2) Database Recovery Models
3) Understanding the Recovery Process 4) The Restore Operation
5) Recovery Advisor
6) Performing Online Restore Operations 7) Recovering from Database Snapshot 8) Recovering System Databases 9) Planning a Backup Strategy 10) Practice
f. SQL Server Security (3 hours)
1) SQL Server Security Overview 2) Authentication Modes 3) Principals and Securables 4) Managing Permissions
5) Understanding Ownership Chaining 6) Controlling Execution Context 7) SQL Server Audit
8) Practice
g. Using Encryption (2.5 hours)
h. Monitoring and Troubleshooting (7 hours)
1) Planning a Monitoring Strategy 2) Windows and SQL Server Logs
3) Working with Windows Performance Monitor 4) The Activity Monitor
5) Working with Dynamic Management Objects 6) Using SQL Server Profiler
7) Distributed Replay
8) DDL Triggers and Event Notifications 9) Extended Events
10) The Dedicated Administrator Connection (DAC) 11) Resource Governor
12) Practice
i. Automation in SQL Server (4 hours)
1) Introduction to SQL Server Agent 2) Configuring Database Mail
3) Implementing Operators, Jobs and Alerts 4) SQL Server Agent Security
5) Using Maintenance Plans 6) Managing Multiple Instances 7) Policy-Based Management 8) Performance Data Collection 9) Practice
j. Using a Standby Server (4.5 hours)
1) Introduction to High Availability 2) Failover Clustering
3) Log Shipping 4) Database Mirroring 5) AlwaysOn
k. Implementing Replication (3.5 hours)
1) Overview of Replication
2) Implementing Snapshot Replication 3) Implementing Transactional Replication 4) Implementing Merge Replication 5) Implementing Peer-to-Peer Replication 6) Replication Topologies
7) Practice
l. Transferring Data with SQL Server (4.5 hours)
1) Overview of Data Transfer
2) Using the Import and Export Wizard 3) Performing Bulk Operations
4) The Copy Database Wizard
5) Introducing SQL Server Integration Services (SSIS) 6) Change Data Capture
