Administering a SQL Database Infrastructure

Descrizione del corso

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 of use to individuals who develop applications that deliver content from SQL Server databases.

Programma

1. Authenticating and Authorizing Users
   • Authenticating connections to SQL Server
   • Authorizing logins to access databases
   • Authorization across servers
   • Partially contained databases
2. Assigning Server and Database Roles
   • Working with server roles
   • Working with Fixed database roles
   • Creating user-defined database roles
3. Authorizing Users to Access Resources
   • Authorizing user access to objects
   • Authorizing users to execute code
   • Configuring permissions at the schema level
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
5. SQL Server Recovery Models
   • Backup strategies
   • Understanding SQL Server transaction login
   • Planning a SQL Server backup strategy
6. Backup of SQL Server Databases
   • Backing up databases and transaction logs
   • Managing database backups
   • Working with backup options
7. Restoring SQL Server Databases
   • Understanding the restore process
   • Restoring databases
   • Working with point-in-time recovery
   • Restoring system databases and individual files
8. Automating SQL Server Management
   • Automating SQL Server management
   • Working with SQL Server agent
   • Managing SQL Server agent jobs
   • Multi-server management
9. Configuring Security for SQL Server Agent
   • Understanding SQL Server Agent Security
   • Configuring credentials
   • Configuring proxy accounts
10. Monitoring SQL Server with Alerts and Notifications
    • Configuration of database mail
    • Monitoring SQL Server errors
    • Configuring operators, alerts, and notifications
    • Alerts in Azure SQL database
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
    • Managing Azure SQL Server databases 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
13. Monitoring SQL Server
    • Monitoring activity
    • Capturing and managing performance data
    • Analyzing collected performance data
    • SQL Server utility
14. Troubleshooting SQL Server
    • SQL Server troubleshooting methodology
    • Resolving service related issues
    • Resolving login and connectivity issues
    • Troubleshooting common issues
15. Importing and 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