-
COURSE OBJECTIVES
After completing this course, students will be able to:
- Authenticate and authorize users
- Assign server and database roles
- Authorize users to access resources
- Protect data with encryption and auditing
- Describe recovery models and backup strategies
- Backup SQL Server databases
- Restore SQL Server databases
- Automate database management
- Configure security for the SQL Server agent
- Manage alerts and notifications
- Managing SQL Server using PowerShell
- Trace access to SQL Server
- Monitor a SQL Server infrastructure
- Troubleshoot a SQL Server infrastructure
- Import and export data
COURSE OUTLINE
1 - AUTHENTICATING AND AUTHORIZING USERS
- Authenticating connections to SQL Server
- Authorizing logins to access databases
- Authorization across servers
- Partially contained databases
- Lab: Authenticating Users
2 - ASSIGNING SERVERS & DATABASE ROLES
- Working with server roles
- Working with Fixed database roles
- Creating user-defined database roles
- Lab: Assigning server and database roles
3 - AUTHORIZING USERS TO ACCESS RESOURCES
- Authorizing user access to objects
- Authorizing users to execute code
- Configuring permissions at the schema level
- Lab: Authorizing users to access resources
4 – PROTECTING DATA WITH ENCRYPTION & AUDITING
- Options for auditing data access in SQL Server
- Implementing SQL Server audit
- Managing SQL Server audit
- Protecting data with encryption
- Lab: Using Auditing and Encryption
5 - SQL SERVER RECOVERY MODELS
- Backup strategies
- Understanding SQL Server transaction login
- Planning a SQL Server backup strategy
- Lab: Understanding SQL Server recovery models
6 - BACKUP OF SQL SERVER DATABASES
- Backing up databases and transaction logs
- Managing database backups
- Working with backup options
- Lab: Backing up SQL Server databases
7 - RESTORING SQL SERVER DATABASES
- Understanding the restore process
- Restoring databases
- Working with point-in-time recovery
- Restoring system databases and individual files
- Lab: Restoring SQL Server User Databases
8 – AUTOMATING SQL SERVER MANAGEMENT
- Automating SQL Server management
- Working with SQL Server agent
- Managing SQL Server agent jobs
- Multi-server management
- Lab: Automating SQL Server Management
9 - CONFIGURING SECURITY FOR SQL SERVER AGENT
- Understanding SQL Server Agent Security
- Configuring credentials
- Configuring proxy accounts
- Lab: Configuring Security for SQL Server Agent
10 - MONITORING SQL SERVER WITH ALERTS & NOTIFICATIONS
- Configuration of database mail
- Monitoring SQL Server errors
- Configuring operators, alerts, and notifications
- Lab: Monitoring SQL Server with Alerts and Notifications
11 – INTRODUCTION TO MANAGING SQL SERVER BY USING POWERSHELL
- Configure SQL Server using PowerShell
- Administer SQL Server using PowerShell
- Maintain the SQL Server environment using PowerShell
- Lab: Administering SQL Server using PowerShell
12 - TRACING ACCESS TO SQL SERVER
- Capturing activity using SQL Server profiler
- Improving performance with the database engine tuning advisor
- Working with tracing options
- Distributed replay
- Monitoring locks
- Lab: Tracing Access to SQL Server
13 - MONITORING SQL SERVER
- Monitoring activity
- Capturing and managing performance data
- Analyzing collected performance data
- SQL Server utility
- Lab: Monitoring SQL Server
14 - TROUBLESHOOTING SQL SERVER
- SQL Server troubleshooting methodology
- Resolving service related issues
- Resolving login and connectivity issues
- Troubleshooting common issues
- Lab: Troubleshooting Common Issues
15 – IMPORTING & EXPORTING DATA
- Transferring data to/from SQL Server
- Importing and exporting table data
- Using BCP and BULK INSERT to import data
- Deploying and upgrading data-tier applications
- Lab: Importing and Exporting Data