DBA SQL Server 2012-2014

Module 1: Microsoft SQL Server Architecture

• Understanding SQL Server system databases and business (non-system) databases.
• Understanding the SQL Server database structure.
• What are SQL Server file groups and data files.
• What are SQL Server Transactional logs and database recovery models.
• What is the difference between a SQL Server instance and database.
• Client-Server Communication in SQL Server

Module 2: Working with Databases

• Overview of SQL Server Databases
• Prepare File System and Raid disk for SQL Database
• Creating Databases
• Concept of TempDB, Data File, Log File and File Group
• Resizing Database
• Moving User databases with Detach – Attach
• Moving System databases

Module 3: Locking and Blocking

• Understanding Locking
• Understanding Deadlocks
• Understanding Transactions

Module 4: Backup and Restoring SQL Server Databases

• Recovery Models Concept
• Planning a Backup Strategy
• Backing Up User Databases
• Working with Backup Options
• Managing Database Backups
• Understanding the Restore Process
• Restoring User Databases

Module 4: Backup and Restoring SQL Server Databases

• Recovery Models Concept
• Planning a Backup Strategy
• Backing Up User Databases
• Working with Backup Options
• Managing Database Backups
• Understanding the Restore Process
• Restoring User Databases

Module 5: Implementing Security

• Overview of SQL Server Security
• Authenticating Connections to SQL Server
• About Password policies
• Managing Windows and SQL Server Logins
• Working with Server Roles

Module 6: Monitoring SQL Server

• Using Standard reports
• Configuring the SQL Serer Log
• Viewing Activity with Dynamic Management View
• Monitoring with Activity Monitor
• Capturing Resource Usage with Performance Monitor
• Collecting Capacity Planning and Performance Data with Data Collector
• Capturing Activity using SQL Server Profiler and SQL Trace

Module 7: Transferring Data

• Overview of ETL Process
• Transferring Data To/From SQL Server
• Importing and Exporting Table Data
• Copying Database
• Deploying Data-Tier Applications
• Considering for Bulk Import/Export

Module 8: Automating SQL Server Management

• Benefit of Automation
• Working with SQL Server Agent
• Configuring Operators
• Configuring of Database Mail
• Working with SQL Server Agent Jobs
• Creating Database Maintenance Plans
• Implementing Alerts and Notifications

Module 9: Implementing High Availability

• Introduction to High Availability
• Implementing Log Shipping
• How to Log Shipping work
• Primary Server vs. Secondary Server
• Fail over with Log Shipping
• Implementing Always on High Availability
• Introduction to SQL Clustering

Module 10: Query Store

• What Is Query Store?
• Overview and Architecture of the Query Store
• Configuring Query Store
• Standard Query Store Reports

Module 11: In-Memory OLTP

• In-Memory OLTP architecture
• Creating in-memory tables
• In-Memory OLTP indexing
• SQL Server Graph

Module 12: Introduction to graph databases

• Graph tables
• Inserting Data
• Querying Data