Course Overview
This instructor-led MS SQL Server course provides participants with the knowledge and skills required to maintain Microsoft SQL Server databases in Production Environment. This Microsoft course focuses on teaching participants how to use SQL Server product features and tools related to maintaining a database. Also, this course helps you prepare for the Exam 70-462.
Modules can be customized and selected by the participants.
Course will be STEP-BY-STEP HANDS-ON INTERACTIVE with GUI and SQL Scripts. Training deals with the production environment.
USE CASES in Troubleshooting, Optimization, Configuration and Best Practices.
At the end of the module, there will be exercises, assignments and quiz on the related topics.
Course is applicable to SQL Server 2016 / 2014 / 2012 / 2008 R2 / 2008
Who Should Attend?
The secondary audiences for this course are participants who develop applications that deliver content from SQL Server databases.
Course Outline
Module-1
- Introduction to the SQL Server Platform
- Working with SQL Server Tools
- Configuring SQL Server Services
Module-2
- Overview of SQL Server Architecture
- Planning Server Resource Requirements
- Pre-installation Testing for SQL Server
Module-3
- Preparing to Install SQL Server
- Installing SQL Server
- Upgrading and Automating Installation
Module-4
- Overview of SQL Server Databases
- Working with Files and Filegroups
- Moving Database Files
Module-5
- Backup Strategies
- Understanding SQL Server Transaction Logging
- Planning a SQL Server Backup Strategy
Module-6
- Backing up Databases and Transaction Logs
- Managing Database Backups
- Working with Backup Options
Module-7
- Understanding the Restore Process
- Restoring Databases
- Working with Point-in-time recovery
- Restoring System Databases and Individual Files
Module-8
- Transferring Data To/From SQL Server
- Importing & Exporting Table Data
- Inserting Data in Bulk
Module-9
- Authenticating Connections to SQL Server
- Authorising Logins to Access Databases
- Authorisation Across Servers
Module-10
- Working with Server Roles
- Working with Fixed Database Roles
- Creating User-defined Database Roles
Module-11
- Authorising User Access to Objects
- Authorising Users to Execute Code
- Configuring Permissions at the Schema Level
Module-12
- Options for Auditing Data Access in SQL
- Implementing SQL Server Audit
- Managing SQL Server Audit
Module-13
- Automating SQL Server Management
- Working with SQL Server Agent
- Managing SQL Server Agent Jobs
Module-14
- Understanding SQL Server Agent Security
- Configuring Credentials
- Configuring Proxy Accounts
Module-15
- Configuration of Database Mail
- Monitoring SQL Server Errors
- Configuring Operators, Alerts and Notifications
Module-16
- Ensuring Database Integrity
- Maintaining Indexes
- Automating Routine Database Maintenance
Module-17
- Capturing Activity using SQL Server Profiler
- Improving Performance with the Database Engine Tuning Advisor
- Working with Tracing Options
Module-18
- Facets Conditions
- Boolean Operators
- Policy Evaluation
- Default Policies
- Policies Import and Export
Module-19
- Resource Governor
- Resource Pools
- Workload Groups
- Classifier Functions
Module-20
- Sessions (Event)
- Events
- Event Category
- Channel
- Event Fields and Description
- Data Storage
- Event Retention Mode
- Maximum Dispatch Latency
- Maximum Event Size, Memory Size
Module-21
- Column store indexes
- Best Practices for Columnstore Indexes
Module-22
- Memory-Optimized Tables
- Native Stored Procedures
- Comparison between 2014 and 2016
Module-23
- Getting Started with Windows PowerShell
- Configure SQL Server using PowerShell
- Administer and Maintain SQL Server with PowerShell
- Managing Azure SQL Databases using PowerShell
Module-24
- Overview of Log Shipping
- Understanding and configuring SQL Server Agent
- Concept of Job, Alert and Schedules
- Configuring Primary and Secondary Servers in Log Shipping
- Maintaining and Monitoring Log Shipping
Module-25
- Introduction to Designing a Database Mirroring Solution
- Designing Database Roles and Topology for Database Mirroring
- Setting the recovery model
- Configuring the principal, mirror and witness
- Administering mirroring
- Launching the Database Mirroring Monitor (DMM)
- Querying a snapshot of the mirror
Module-26
- Combining High-Availability Technologies like Failover clustering
- Selecting the appropriate strategies
- Determining the pros and cons of each HA technology
- Installing Windows Failover Clustering
- Defining components of a cluster
- Single-instance architecture
- Multi-instance architecture
- Shared storage
- Resource group
- Heartbeat
- Quorum
- Preparing for Windows failover clustering
- Configuring an iSCSI target
- Managing shared storage
- Installing the failover clustering feature
- Multi Sub Net Clustering
Module-27
- Implementing an AlwaysOn Availability Group
- Connecting to an AlwaysOn Availability Group
- Testing Failover
- Configure Quorum Voting
Module-28
- Replication Topology
- Replication Types
- Publisher, Distributor, Subscriber
- Replication Agents
- Replication Configuration
Module-29
- Database Snapshot Overview
- Creation of Database Snapshot
- Disaster Recovery Scenario
Course Objectives
- Plan and install SQL Server.
- Describe the system databases, the physical structure of databases and the most common configuration options related to them.
- Explain the concept of the transaction log and SQL Server recovery models and implement different backup strategies available with SQL Server.
General Notes
- All our courses can be facilitated as Customized In-House Training course.
- Course duration is flexible and the contents can be modified to fit any number of days.
- As for Open Enrolment Courses, we offer our clients the flexibility to chose the location, date, and time and our team of experts who are spread around the globe will assist in facilitating the course.
- The course fee includes facilitation, training materials, 2 coffee breaks, buffet lunch and a Certificate of successful completion of Training.
- FREE Consultation and Coaching provided during and after the course.
