This five-day instructor-led course provides students who administer and maintain SQL Server databases with the knowledge and skills to manage a SQL Server database infrastructure. In addition, it will be useful for people developing applications that deliver content from SQL Server databases.
Aimed at
- The primary audience for this course is people who administer and maintain SQL Server databases. These individuals perform database management and maintenance as their primary area of responsibility, or work in environments where databases play a key role in their core work.
Duration
35 hours
Modalities
- On-site
- Virtual live through digital platforms
- On site
Includes
- 35 hours of training
- Trained instructors
- Participant's manual in digital format
- Proof of participation
Agenda
- Authenticate connections to SQL Server
- Authorize logins to connect to databases
authorization - Partially contained databases
- Create logins
- Create database users
- Correct application login problems
- Configuring security settings for restored databases
- Working with server roles
- Working with fixed database roles
- Assign user-defined database roles
- Assign server roles
- Assign fixed database roles
- Assign user-defined database roles
- Verifying safety
- Authorizing user access to objects
- Authorize users to execute the code
- Configure permissions at schema level
- Granting, refusal and revocation of permits on objects
- Granting of EXECUTE permits in the code
- Scheme-level permitting
- Options for auditing data access in SQL Server
- Implementation of SQL Server auditing
- Manage SQL Server auditing
Data protection with encryption
- Working with SQL Server auditing
- Encrypt a column as always encrypted
- Encrypting a database using TDE
- Understanding backup strategies
- SQL Server transaction logs
- Planning backup strategies
- Plan a backup strategy
- Configure database recovery models
- Backup of databases and transaction logs
- Manage database backups
- Advanced database options
- Database backup
- Backing up transaction, differential and database logs
- Performing a partial backup
- Understanding the restoration process
- Restore databases
- Advanced restoration scenarios
- Recovery at a given point in time
- Restoring a database backup
- Restrict database, differential and transaction log backups
- Restoration in stages
- Automation of SQL Server management
- Working with SQL Server Agent
- Manage SQL Server Agent jobs
- Management of multiple servers
- Create a SQL Server Agent job
- Trying out a job
- Schedule a job
- Configure master and target servers
- Understanding SQL Server Agent security
- Configure credentials
- Configure proxy accounts
- Analysis of SQL Server Agent problems
- Configure a credential
- Set up a proxy account
- Configuring and testing the security context of a job
- SQL Server error monitoring
- Configure database mailing
- Operators, alerts and notifications
- Alerts in Azure SQL Database
- Configure database mailing
- Configure operators
- Configure alerts and notifications
- Testing of alerts and notifications
- Introduction to Windows PowerShell
- Configuring SQL Server with PowerShell
- Manage and maintain SQL Server with PowerShell
- Managing Azure SQL databases with PowerShell
- Introduction to PowerShell
- Using PowerShell to change SQL Server settings
- Basic concepts of extended events
- Working with extended events
- Using the extended System_Health event session
- Tracking of page splits through extended events
- Follow-up activity
- Performance data capture and management
- Analysis of collected performance data
- SQL Server Utility
- A troubleshooting methodology for SQL Server
- Troubleshooting service-related problems
- Troubleshooting connectivity and login problems
- Troubleshooting and resolving a SQL login problem
- Troubleshooting and resolving a service problem
- Troubleshooting and resolving a Windows logon problem
- Troubleshoot and solve a job execution problem
- Troubleshoot and resolve a performance problem
- Data transfer to and from SQL Server
- Import and export of table data
- Using bcp and BULK INSERT to import data
- Deployment and upgrade of data-tier applications
- Import and Excel data with the Import Wizard
- Importing a delimited text file using bcp
- Importing a delimited text file using BULK INSERT
- Create and test an SSIS package for data extraction
- Implementing a data-tier application
