Course Outline
Module 1: SQL Server Security
-
Authenticating Connections to SQL Server
-
Authorizing Logins to Connect to databases
-
Authorization Across Servers
-
Partially Contained Databases
-
Create Logins
-
Create Database Users
-
Correct Application Login Issues
-
Configure Security for Restored Databases
Module 2: Assigning Server and Database Roles
-
Working with server roles
-
Working with Fixed Database Roles
-
Assigning User-Defined Database Roles
-
Assigning Server Roles
-
Assigning Fixed Database Roles
-
Assigning User-Defined Database Roles
-
Verifying Security
Module 3: Authorizing Users to Access Resources
-
Authorizing User Access to Objects
-
Authorizing Users to Execute Code
-
Configuring Permissions at the Schema Level
-
Granting, Denying, and Revoking Permissions on Objects
-
Granting EXECUTE Permissions on Code
-
Granting Permissions at the Schema Level
Module 4: Protecting Data with Encryption and Auditing
-
Options for auditing data access in SQL Server
-
Implementing SQL Server Audit
-
Managing SQL Server Audit
-
Protecting Data with Encryption
-
Working with SQL Server Audit
-
Encrypt a Column as Always Encrypted
-
Encrypt a Database using TDE
Module 5: Recovery Models and Backup Strategies
-
Understanding Backup Strategies
-
SQL Server Transaction Logs
-
Planning Backup Strategies
-
Plan a Backup Strategy
-
Configure Database Recovery Models
Module 6: Backing Up SQL Server Databases
-
Backing Up Databases and Transaction Logs
-
Managing Database Backups
-
Advanced Database Options
-
Backing Up Databases
-
Performing Database, Differential, and Transaction Log Backups
-
Performing a Partial Backup
Module 7: Restoring SQL Server 2016 Databases
-
Understanding the Restore Process
-
Restoring Databases
-
Advanced Restore Scenarios
-
Point-in-Time Recovery
-
Restoring a Database Backup
-
Restring Database, Differential, and Transaction Log Backups
-
Performing a Piecemeal Restore
Module 8: Automating SQL Server Management
-
Automating SQL Server management
-
Working with SQL Server Agent
-
Managing SQL Server Agent Jobs
-
Multi-server Management
-
Create a SQL Server Agent Job
-
Test a Job
-
Schedule a Job
-
Configure Master and Target Servers
Module 9: Configuring Security for SQL Server Agent
-
Understanding SQL Server Agent Security
-
Configuring Credentials
-
Configuring Proxy Accounts
-
Analyzing Problems in SQL Server Agent
-
Configuring a Credential
-
Configuring a Proxy Account
-
Configuring and testing the Security Context of a Job
Module 10: Monitoring SQL Server with Alerts and Notifications
-
Monitoring SOL Server Errors
-
Configuring Database Mail
-
Operators, Alerts, and Notifications
-
Alerts in Azure SOL Database
-
Configuring Database Mail
-
Configuring Operators
-
Configuring Alerts and Notifications
-
Testing Alerts and Notifications
Module 11: Introduction to Managing SQL Server by using PowerShell
-
Getting Started with Windows PowerShell
-
Configure SOL Server using PowerShell
-
Administer and Maintain SOL Server with PowerShell
-
Managing Azure SOL Databases using PowerShell
-
Getting Started with PowerShell
-
Using PowerShell to Change SOL Server settings
Module 12: Tracing Access to SQL Server with Extended events
-
Extended Events Core Concepts
-
Working with Extended Events
-
Using the System_Health Extended Events Session
-
Tracking Page Splits Using Extended Events
Module 13: Monitoring SQL Server
-
Monitoring activity
-
Capturing and Managing Performance Data
-
Analyzing Collected Performance Data
-
SQL Server Utility
Module 14: Troubleshooting SQL Server
-
A Trouble Shooting Methodology for SQL Server
-
Resolving Service Related Issues
-
Resolving Connectivity and Log-in issues
-
Troubleshoot and Resolve an SQL Login Issue
-
Troubleshoot and Resolve a Service Issue
-
Troubleshoot and Resolve a Windows Login Issue
-
Troubleshoot and Resolve a Job Execution Issue
-
Troubleshoot and Resolve a Performance Issue
Module 15: Importing and Exporting Data
-
Transferring Data to and from SQL Server
-
Importing and Exporting Table Data
-
Using bcp and BULK INSERT to Import Data
-
Deploying and Upgrading Data-Tier Application
-
Import and Excel Data Using the Import Wizard
-
Import a Delimited Text File Using bcp
-
Import a Delimited Text File using BULK INSERT
-
Create and Test an SSIS Package to Extract Data
-
Deploy a Data-Tier Application
Requirements
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
- Working knowledge of Transact-SQL.
- Working knowledge of relational databases.
- Some experience with database design
Testimonials
I appreciated Folio's wide breadth of knowledge. Not only was he familiar with the course content, but he also knew of constructs in languages we were familiar with to make examples more meaningful to us. During intervals he shared his knowledge of technologies and solutions outside the training scope to provide insights into other solutions we could use in future (and future training).
Raphael Keymer - Markit Valuations Limited
Knowledge of the lecturer
Paweł Dybowski
Commitment and passion of the trainer ☺. And with the problem of managing backupmi, Joby.
Krzysztof Klockowski
I enjoyed the attention to detail, knowledge and enthusiasm for the subject.
Bristol City Council
The ability to ask questions at any time and the more informal / less structured style. This allowed us to pursue the areas of knowledge we were most interested in.
Jim Lane
everything :)
Szymon Lasota - Lingaro Sp. z o.o.
Practical knowledge
Tomasz Bartczak - Lingaro Sp. z o.o.
Exercises on real data
- Lingaro Sp. z o.o.
learning about reading query plans (individual elements)
- Lingaro Sp. z o.o.
Very interesting examples, ease of knowledge transfer by the teacher.
- Lingaro Sp. z o.o.
a large number of topics.
- Lingaro Sp. z o.o.
Help in designing the actual database we intend to build based on the knowledge of the course; Quickly create reports.
- Carlsberg Shared Services Sp. z o. o.
I liked the practical and immediate application of concepts to lectures.
- Nordex Energy GmbH
Relevance to BigQuery.
- Spark44
We use BigQuery and the trainer decided we would use BigQuery instead to run all of the codes which was really really useful
- Spark44
topics covered, although I would have loved it better if SSIS and SSRS were also covered.
- SYKES Asia, Inc.
the in depth knowledge and customize approach
Julius Mapili - SYKES Asia, Inc.
The exercises and hands-on activity
Raymund Libre - SYKES Asia, Inc.
Overall understanding about SQL
MTU Aero Engines Polska Sp. z o. o.
A lot of useful infromation has been provided.
MTU Aero Engines Polska Sp. z o. o.
I got a great starting point in sql server administatrion
MTU Aero Engines Polska Sp. z o. o.
Pace was perfect. Topics suitable.
MTU Aero Engines Polska Sp. z o. o.
Well prepared trainer open for additional questions of any kind at any time
Thomas Griffig - MTU Aero Engines Polska Sp. z o. o.
backup and maintanace issues
Colian sp. z o.o.
Interactive exercises
Matthew Viner - Thames Water Utilites Ltd
It felt very relatable. The exercises were well picked out and I was able to work well with it due to clear knowledge provided.
Leah Bayliss - Thames Water Utilites Ltd
Phil was great! He was both patient and knowledgeable. The training went at a good pace, moving a team of mixed SQL abilities through the basics into more advanced SQL querying together. The exercises were an excellent way for us to commit our newly learned skills to memory, and Phil's instructions/class discussions on numerous ways to achieve the same thing were interesting.
Sophie Clement - Thames Water Utilites Ltd
It allowed us to have an applied hands on sproc to our company database.