Course Details
This instructor-led course provides you with the knowledge and skills to maintain a Microsoft SQL Server 2008 R2 database. You will learn to use SQL Server 2008 R2 product features and tools to install and configure a database, manage database files, and create and tune indexes.
This course incorporates materials from the Official Microsoft Learning Product 6231: Maintaining a Microsoft SQL Server 2008 R2 Database.
Course Prerequisites
- Basic knowledge of the Microsoft Windows operating system and its corefunctionality
- Working knowledge of Transact-SQL
- Working knowledge of relational databases
- Some experience with database design
Course Agenda
1. Introduction to SQL Server 2008 R2 and Its Toolset
- SQL Server Platform
- Working with SQL Server Tools
- Configuring SQL Server Services
2. Preparing Systems for SQL Server 2008 R2
- Overview of SQL Server 2008 R2 Architecture
- Planning Server Resource Requirements
- Pre-Installation Testing for SQL Server 2008 R2
3. Installing and Configuring SQL Server 2008 R2
- Preparing to Install SQL Server 2008 R2
- Installing SQL Server 2008 R2
- Upgrading and Automating Installation
4. Working with Databases
- Overview of SQL Server Databases
- Working with Files and Filegroups
- Moving Database Files
5. SQL Server 2008 R2 Recovery Models
- Backup Strategies
- SQL Server Transaction Logging
- Planning a SQL Server Backup Strategy
6. Backup of SQL Server 2008 R2 Databases
- Backing Up Databases and Transaction Logs
- Managing Database Backups
- Working with Backup Options
7. Restoring SQL Server 2008 R2 Databases
- The Restore Process
- Restoring Databases
- Working with Point-In-Time Recovery
- Restoring System Databases and Individual Files
8. Importing and Exporting Data
- Transferring Data to/from SQL Server 2008 R2
- Importing and Exporting Table Data
- Inserting Data in Bulk
9. Authenticating and Authorizing Users
- Authenticating Connections to SQL Server
- Authorizing Logins to Access Databases
- Authorization Across Servers
10. Assigning Server and Database Roles
- Working with Server Roles
- Working with Fixed Database Roles
- Creating User-Defined Database Roles
11. Authorizing Users to Access Resources
- Authorizing User Access to Objects
- Authorizing Users to Execute Code
- Configuring Permissions at the Schema Level
12. Auditing SQL Server Environments
- Options for Auditing Data Access in SQL Server
- Implementing SQL Server Audit
- Managing SQL Server Audit
13. Automating SQL Server 2008 R2 Management
- Automating SQL Server Management
- Working with SQL Server Agent
- Managing SQL Server Agent Jobs
14. Configuring Security for SQL Server Agent
- SQL Server Agent Security
- Configuring Credentials
- Configuring Proxy Accounts
15. Monitoring SQL Server 2008 R2 with Alerts and Notifications
- Configuration of Database Mail
- Monitoring SQL Server Errors
- Configuring Operators, Alerts, and Notifications
16. Performing Ongoing Database Maintenance
- Ensuring Database Integrity
- Maintaining Indexes
- Automating Routine Database Maintenance
17. Tracing Access to SQL Server 2008 R2
- Capturing Activity Using SQL Server Profiler
- Improving Performance with the Database Engine Tuning Advisor
- Working with Tracing Options
18. Monitoring SQL Server 2008 R2
- Monitoring Activity
- Capturing and Managing Performance Data
- Analyzing Collected Performance Data
19. Managing Multiple Servers
- Working with Multiple Servers
- Virtualizing SQL Server
- Deploying and Upgrading Data-Tier-Applications
20. Troubleshooting Common SQL Server 2008 R2 Administrative Issues
- SQL Server Troubleshooting Methodology
- Resolving Service-Related Issues
- Resolving Concurrency Issues
- Resolving Login and Connectivity Issues