Designing Database Solutions for Microsoft SQL Server 2012 (M20465)

Duration

5 Days

Audience:

Employees of federal, state and local governments; and businesses working with the government.

Course Description

In this course, you will learn to create plans and designs for database structure, storage, objects, and servers through hands-on labs and design tasks. You will learn to design and monitor high performance, highly available data solutions with Microsoft SQL Server 2012 along with data compression, high availability, data migration, security, and scalability.

This course incorporates material from the Official Microsoft Learning Product 20465 and covers the skills and knowledge measured by Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012 and, in conjunction with on-the-job experience, can help you prepare for the exam.

What You’ll Learn

  • Design an appropriate database server infrastructure for a given business application scenario
  • Design a logical schema for a database based on application requirements
  • Design the physical implementation of a database for a given set of requirements
  • Evaluate options for including binary large object data in a database design
  • Plan and manage indexes to optimize performance
  • Key considerations for designing security for SQL Server instances and databases
  • Plan policy-based management to manage server instances, databases, and other SQL Server 2012 objects more efficiently
  • Plan SQL Server health monitoring
  • Implement SQL Server health monitoring by using SQL Server Utility
  • Identify and implement the appropriate backup strategy for a given scenario
  • Plan and manage multi-server maintenance and automation
  • Benefits of using PowerShell to manage SQL Server 2012
  • Design an optimal replication strategy from a given set of business and technical requirements
  • Plan and implement a high availability solution

Who Needs to Attend

  • IT professionals who design database solutions and have experience with database development and administration of SQL Server databases

Prerequisites

You should have the following technical knowledge to attend this course:

  • Transact-SQL
  • SQL Server 2012 components and tools
  • Objects in a SQL Server database
  • Enterprise Windows network and security architecture
  • MCSA: SQL Server 2012 certification or the knowledge and skills from the following courses:
  • Querying Microsoft SQL Server 2012 (M10774)
  • Administering Microsoft SQL Server 2012 Databases (M10775)
  • Developing Microsoft SQL Server 2012 Databases (M10776)

Course Outline

1. Designing a Database Server Infrastructure

  • Planning a Database Server Infrastructure
  • Planning Server Hardware
  • Considerations for Database Server Consolidation
  • Managing Server Resources in a Consolidated Database Infrastructure

2. Designing a Logical Database Schema

  • Relational Database Design Techniques
  • Planning Schemas and Tables

3. Designing a Physical Database Implementation

  • Planning Files and Filegroups
  • Planning Data Partitioning and Compression

4. Incorporating Data Files into Databases

  • Considerations for Working with Data Files in SQL Server 2012
  • Implementing FileStream and FileTables
  • Searching Data Files

5. Tuning Database Performance

  • Optimizing Query Performance with Indexes
  • Managing Query Plans
  • Performance Monitoring

6. Designing Database Security

  • Security Planning
  • Plan Security
  • Contained Databases
  • Protecting Data with Encryption

7. Policy-Based Management

  • Basics of Policy-Based Management
  • Planning and Implementing Policy-Based Management

8. Monitoring Server Health

  • Basics of Server Health Monitoring
  • SQL Server Utility

9. Designing a Database Backup Solution

  • SQL Server Backup and Restore
  • Planning a Recovery Strategy

10. Automating Multi-Server Maintenance

  • Maintenance Automation
  • Managing Multiple Servers

11. Managing SQL Server with PowerShell

  • Windows PowerShell
  • Scripting with Windows PowerShell

12. Replicating Data

  • SQL Server Replication
  • Planning Replication

13. Designing for High Availability

  • High Availability in SQL Server 2012
  • AlwaysOn Availability Groups

Labs

Lab 1: Plan Database Server Consolidation

  • Plan for Consolidation
  • Manage Resources for an Instance of SQL Server
  • Manage Resources for Multiple SQL Server Instances on a Single Windows Server

Lab 2: Design a Logical Database Schema

  • Plan a Database Schema
  • Create a View to Display Employee Payment Information

Lab 3: Design a Physical Database Implementation

  • Plan Files and Filegroups
  • Implement the Timesheet Archive Strategy

Lab 4: Implement a Solution for Storing Data Files

  • Create a FileTable
  • Create and Use Full-Text Index

Lab 5: Use Indexes and Plan Guides

  • Plan Indexes
  • Test Indexing Strategies
  • Work with Execution Plans

Lab 6: Plan and Implement Security

  • Plan Server and Database Security
  • Implement a Data Access Strategy
  • Implement Transparent Data Encryption

Lab 7: Plan Policy-Based Management

  • Plan Policy-Based Management Strategy
  • Implement Policy-Based Management
  • Test Policy Compliance

Lab 8: Monitor Server Health

  • Create a Utility Control Point
  • Configure Health Policies

Lab 9: Plan and Implement a Backup Strategy

  • Plan a Backup and Restore Strategy
  • Implement a Backup Strategy
  • Perform a Piecemeal Restore

Lab 10: Automate Multi-Server Maintenance

  • Plan and Implement a Multi-Server Environment
  • Plan and Implement Multi-Server Jobs

Lab 11: Manage SQL Server with Windows PowerShell

  • Use PowerShell to Manage SQL Server
  • Create PowerShell Scripts

Lab 12: Plan and Implement SQL Server Replication

  • Plan Replication
  • Implement Replication

Lab 13: Implement High Availability

  • Create and Use an AlwaysOn Availability Group
  • Test Failover for an AlwaysOn Availability Group