Administering Microsoft SQL Server 2012 Databases

Course Description

In this course, you will gain the knowledge and skills to maintain a SQL Server 2012 database by utilizing the SQL Server 2012 product features and tools in the beta version of pre-released software. You will complete SQL Azure-enabled exercises throughout the labs.

Skills Gained

• Plan and install SQL Server
• Physical structure and configuration options for databases
• Create and implement different backup strategies with transaction log, SQL Server recovery models, and restoration of SQL Server databases
• Import/Export wizards and how they relate to SSIS
• Partial database containment option provided by SQL Server 2012
• SQL Server security models, logins, users, and database containment with users authenticated at the database level
• Fixed server roles, fixed database roles, user-defined server roles, and user-defined database roles
• Assigning and using permissions
• SQL Server Audit
• SQL Server Agent, jobs, and job history
• Implement SQL Server Agent security, proxy accounts, and credentials
• Configure database mail, alerts, and notifications
• Create database maintenance plans
• How hot fixes, cumulative updates, and service packs are related and when to apply each type of update
• How to enable and when to use automatic updates
• Upgrading data-tier applications with SQL Server 2012
• SQL Profiler and SQL Trace stored procedures
• Dynamic Management Views (DMV) and configuration of data collection
• Core concepts of High Availability, AlwaysOn, and Replication in SQL Server 2012
• Work with central management servers, multi-server queries, virtualization of SQL Server, and data-tier applications
• Troubleshoot SQL Server databases

Who Can Benefit

• Database professionals who administer and maintain SQL Server databases including database maintenance
• Database professionals who work in environments where databases play a key role in their primary job responsibilities or develop applications that deliver content from SQL Server databases

Prerequisites

• Working knowledge of relational databases
• Basic knowledge of Microsoft Windows operating systems and its core functionality
• Experience with database design

Syllabus

1. SQL Server 2012 and Toolset

• SQL Server Platform
• Working with SQL Server Tools
• Configuring SQL Server Services

2. Preparing Systems for SQL Server 2012

• SQL Server Architecture
• Planning Server Resource Requirements
• Pre-Installation Testing for SQL Server

3. Installing and Configuring SQL Server 2012

• Preparing to Install SQL Server
• Installing SQL Server
• Upgrading and Automating Installation

4. Working with Databases

• SQL Server Databases
• Files and Filegroups
• Moving Database Files

5. SQL Server 2012 Recovery Models

• Backup Strategies
• SQL Server Transaction Logging
• Planning a SQL Server Backup Strategy

6. Backup of SQL Server 2012 Databases

• Backing up Databases and Transaction Logs
• Managing Database Backups
• Working with Backup Options

7. Restoring SQL Server 2012 Databases

• 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
• Importing and Exporting Table Data
• Inserting Data in Bulk

9. Authenticating and Authorizing Users

• Authenticating Connections to SQL Server
• Authorizing Logins to Access Databases
• Authorizing Across Servers

10. Assigning Server and Database Roles

• Server Roles
• 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
• Implementing SQL Server Audit
• Managing SQL Server Audit

13. Automating SQL Server 2012 Management

• Automating SQL Server Management
• 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 2012 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 2012

• Capturing Activity Using SQL Server Profiler
• Improving Performance with the Database Engine Tuning Advisor
• Working with Tracing Options

18. Monitoring SQL Server 2012

• 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-Tiered Applications

20. Troubleshooting Common SQL Server 2012 Administrative Issues

• SQL Server Troubleshooting Methodology
• Resolving Service Related Issues
• Resolving Concurrency Issues
• Resolving Login and Connectivity Issues