Administering a SQL Database Infrastructure Course 55316A

After completing this course, students will be able to:

· Describe T-SQL, sets, and predicate logic.

· Describe how SQL Server uses data types.

· Write a single table SELECT statement.

· Write a multi-table SELECT statement.

· Write SELECT statements with filtering and sorting.

· Write DML statements.

· Write queries that use built-in functions.

· Write queries that aggregate data.

· Create and implement views and table-valued functions.

· Use set operators to combine query results.

· Adopt T-SQL to extract/transform data in databases

Module 1: SQL Server Security
• Authenticating connections to SQL Server
• Authorizing logins to connect to databases
• Authorization across servers
• Partially contained databases
• Lab: SQL Server security configuration

Module 2: Assigning Server and Database Roles
• Working with server roles
• Working with fixed database roles
• User-defined database roles
• Lab: Managing roles and permissions

Module 3: Authorizing Users to Access Resources
• Authorizing user access to objects
• Authorizing users to execute code
• Configuring permissions at the schema level

Module 4: Protecting Data with Encryption and Auditing
• Options for auditing data access
• Implementing and managing SQL Server Audit
• Protecting data with encryption (TDE, column-level encryption)
• Lab

Module 5: Recovery Models and Backup Strategies
• Understanding recovery models
• SQL Server transaction logs
• Planning backup strategies

Module 6: Backing Up SQL Server Databases
• Backing up databases and transaction logs
• Managing database backups
• Advanced backup options
• Lab

Module 7: Restoring SQL Server Databases
• Restoring user and system databases
• Point-in-time recovery
• Troubleshooting restore operations
• Lab

Module 8: Automating SQL Server Management
• Automating administrative tasks
• SQL Server maintenance plans
• Introduction to PowerShell for SQL Server
• Lab

Module 9: Configuring Security for SQL Server Agent
• SQL Server Agent security model
• Credentials, proxies, and job ownership
• Demo

Module 10: Monitoring SQL Server with Alerts and Notifications
• Configuring alerts and operators
• Notifications and monitoring strategies
• Demo
• Lab

Module 11: Managing SQL Server Using PowerShell
• SQL Server PowerShell provider
• Automating management and administration tasks

Module 12: Tracing Access and Activity
• Tracing SQL Server activity
• Using Extended Events

Module 13: Monitoring a SQL Server Infrastructure
• Performance monitoring
• Identifying bottlenecks and issues

Module 14: Troubleshooting SQL Server Infrastructure
• Common administrative issues
• Diagnosing and resolving infrastructure problems
• Demo
• Labs

Module 15: Importing and Exporting Data
• Import and export techniques
• Working with SQL Server data tools
• Demo
• Labs

• Database Administrators (DBAs) who administer SQL Server databases on-premises and/or Azure SQL Database.

• Infrastructure/System Administrators responsible for installing, configuring, securing, and maintaining database servers and related services.

• Cloud/Platform Engineers supporting Azure-based database workloads (operations, security, backup/recovery, monitoring).

• Operations/Support Engineers (2nd/3rd line) who troubleshoot availability and performance issues in SQL Server environments.

• DevOps engineers or application developers who own database operations tasks (deployments, automation, monitoring) in addition to development work.

• Technical leads responsible for defining operational standards for security, maintenance, and recoverability of SQL Server platforms.