Description
This five-day, instructor-led course provides students who administer and maintain SQL Server databases with the knowledge and skills to administer a SQL Server database infrastructure. Additionally, it will be useful to people who develop applications that serve content from SQL Server databases.
Addressed to
- The primary audience for this course is people who administer and maintain SQL Server databases. These individuals perform database administration and maintenance as their primary area of responsibility, or work in environments where databases play a key role in their primary job.
Duration
35 hours
Modalities
- Face-to-face
- Virtual live through digital platforms
- on site
Related Courses
- 20761-C: Querying Data with Transact-SQL
- 20762-C: Developing SQL Databases
It includes
- 35 hours of training
- Trained instructors
- Participant manual in digital format
- Proof of participation
Temary
Lesson 1: SQL Server Security
- Authenticate connections to SQL Server
- Authorize logins to connect to databases
- Authorization between servers
- Partially contained databases
Lab: User Authentication
- Create logins
- Create database users
- Successful app login issues
- Configure security for restored databases
Lesson 2: Assigning Database and Server Roles
- Work with server roles
- Work with fixed database roles
- Assign user-defined database roles
Lab : Server and Database Role Assignment
- Assign server roles
- Assign fixed database roles
- Assign user-defined database roles
- Checking security
Lesson 3: Authorizing users to access resources
- Authorize user access to objects
- Authorize users to run code
- Set schema-level permissions
Lab : Authorize Users to Access Resources
- Granting, denying, and revoking permissions on objects
- Granting EXECUTE permissions on code
- Granting permissions at the schema level
Lesson 4: Protecting data with encryption and auditing
- Options for auditing data access in SQL Server
- Implementation of SQL Server auditing
- Manage SQL Server auditing
- Data protection with encryption
Lab : Using Auditing and Encryption
- Work with SQL Server auditing
- Encrypt a column as always encrypted
- Encrypt a database using TDE
Lesson 5: Recovery Models and Backup Strategies
- Understanding Backup Strategies
- SQL Server transaction logs
- Planning of backup strategies
Lab : Understanding SQL Server Recovery Models
- Plan a backup strategy
- Configure database recovery models
Lesson 6: SQL Server Database Backup
- Backup of databases and transaction logs
- Manage database backups
- Advanced database options
Lab: Database Backup
- Database backup
- Backing up database, differential, and transaction logs
- Performing a partial backup
Lesson 7: Restoring SQL Server 2016 Databases
- Understanding the restore process
- restore databases
- Advanced restore scenarios
- Recovery at a certain time
Lab : Restoring SQL Server Databases
- Restoring a database backup
- Restrict database, differential, and transaction log backups
- Performing a staged restore
Lesson 8: Automating SQL Server Administration
- SQL Server management automation
- Work with SQL Server Agent
- Manage SQL Server Agent Jobs
- Management of multiple servers
Lab : Automating SQL Server Administration
- Create a SQL Server Agent job
- try a job
- Schedule a job
- Configure master and destination servers
Lesson 9: Configuring Security for SQL Server Agent
- Understanding SQL Server Agent Security
- Configure credentials
- Configure proxy accounts
Lab : Security Configuration for SQL Server Agent
- Analyzing problems in SQL Server Agent
- Set up a credential
- Set up a proxy account
- Configure and test the security context of a job
Lesson 10: SQL Server Monitoring with Alerts and Notifications
- SQL Server error monitoring
- Configure database mail
- Operators, alerts and notifications
- Alerts in Azure SQL Database
Lab : SQL Server Monitoring with Alerts and Notifications
- Configure database mail
- Configure operators
- Set up alerts and notifications
- Test alerts and notifications
Lesson 11: Introduction to SQL Server Administration Using PowerShell
- Introduction to Windows PowerShell
- Configure SQL Server with PowerShell
- Manage and maintain SQL Server with PowerShell
- Manage Azure SQL databases with PowerShell
Lab : Using PowerShell to Administer SQL Server
- Introduction to PowerShell
- Using PowerShell to change SQL Server settings
Lesson 12: Tracking Access to SQL Server with Extended Events
- Basics of Extended Events
- Work with extended events
Lab: Extended Events
- Using the System_Health Extended Event Session
- Tracking page splits using extended events
Lesson 13: Monitoring SQL Server
- tracking activity
- Performance data capture and management
- Analysis of collected performance data
- SQL Server Utility
Lab : SQL Server Monitoring
Lesson 14: Troubleshooting SQL Server
- A troubleshooting methodology for SQL Server
- Troubleshooting service-related issues
- Troubleshoot connectivity and login issues
Lab: Troubleshooting Common Problems
- Troubleshoot and resolve a 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
Lesson 15: Importing and exporting data
- Data transfer to and from SQL Server
- Import and export of table data
- Using bcp and BULK INSERT to import data
- Deploying and updating data tier applications
Lab: Data Import and Export
- Import and data from Excel with 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
