Hello
Your cart is empty. View / Checkout
United States Cisco SMARTnet Pricing
813-421-2951
Other Solutions

M6231 London

Maintaining a Microsoft SQL Server 2008 R2 Database

  • Course Availability
Price :
Course Overview :

Microsoft Certified Technology Specialist (MCTS): Microsoft SQL Server 2008, Implementation and Maintenance M6231 70-432

Course Title: Maintaining a Microsoft SQL Server 2008 R2 Database

Course Code: M6231
Version: B
Level: 200
Duration: 5 Days

Course Overview

The Maintaining a Microsoft SQL Server 2008 R2 Database M6231 instructor-led training course has been designed to equip candidates with the skills and understanding to be able to maintain a Microsoft SQL Server 2008 R2 database. Emphasis is also placed on how to make use of the SQL Server 2008 R2 product features and tools in relation to database maintenance.

Target Audience

Candidates who should consider attending the M6231 Microsoft training course are those who wish to achieve Microsoft Certification as a Microsoft Certified Technology Specialist (MCTS) as well as individuals seeking to administer and maintain SQL Server databases. The Maintaining a Microsoft SQL Server 2008 R2 Database course is also advantageous for application developers who deliver content from SQL Server databases.

Course Objectives

On successfully completing the M6231 course candidates will be able to:
  • Define the architecture of the SQL Server 2008 R2 and resource requirements
  • Implement pre-checks of I/O subsystems
  • Install, plot and configure SQL Server 2008 R2
  • Restore and backup databases
  • Import and export wizards as well as define how they relate to SSIS
  • Make use of BCP and BULK INSERT to import data
  • Manage security
  • Configure and allocate fixed database roles as well as generate and allocate user defined database roles
  • Configure and allocate permissions
  • Implement audits, manage agents and jobs for the SQL Server 2008 R2
  • Configure database mails, notifications and alerts
  • Maintain databases
  • Use the Database Tuning Advisor and configure SQL Profiler Traces
  • Use Dynamic Management Views to monitor data
  • Configure a central management server and implement multi-server queries
  • Install a data-tier-application
  • Troubleshoot common problems

Course Prerequisites

Candidates attending the M6231 Microsoft training course are advised to familiarise themselves with the course content by completing the E-Learning and Self-Paced Training Kits available for training course M6231 - Maintaining a Microsoft SQL Server 2008 R2 Database beforehand:
  • Microsoft e-learning collection 6369: Implementing and Maintaining a Microsoft SQL Server 2008 Database (16 hours)
  • Microsoft Certified Technology Specialist (MCTS) Self-Paced Training Kit for Microsoft Exam Number 70-432: Microsoft SQL Server 2008 - Implementation and Maintenance

Testing and Certification

The M6231 Microsoft training course is the recommended preparation for the 70-432 - TS: Microsoft SQL Server 2008, Implementation and Maintenance exam.

To prepare fully for this exam candidates are recommended attend and complete the M6232 - Implementing a Microsoft SQL Server 2008 Database course to achieve the Microsoft Certified Technology Specialist (MCTS) SQL Server 2008, Implementation and Maintenance certification.

Course Content

Module 1: Introduction to SQL Server 2008 R2 and its Toolset

In this module candidates will learn about the SQL Server platform and its major tools, versions editions, the fundamentals of network listeners, services and service accounts concepts.


Lessons:
  • Introduction to SQL Server Platform
  • Working with SQL Server Tools
  • Configuring SQL Server Services
Lab: Introduction to SQL Server 2008 R2 and its Toolset
  • Validate SQL Server component installation
  • Modify Service Accounts for new instance
  • Enable Named Pipes Protocol for both instances
  • Generate Aliases for AdventureWorks and Proseware
  • Ensure SQL Browser is disabled and configure a Fixed TCP/IP Port
  • Define the SQL Server Platform
  • Work with SQL Server Tools
  • Configure SQL Server Services
Module 2: Preparing Systems for SQL Server 2008 R2

In this module candidates will learn how to plan installations in relation to SQL Server I/O requirements, 32 bit versus 64 bit, options for memory configuration and I/O subsystem pre-installation checks using SQLIOSim and SQLIO.


Lessons:
  • Overview of SQL Server 2008 R2 Architecture
  • Planning Server Resource Requirements
  • Pre-installation Testing for SQL Server 2008 R2
Lab: Preparing Systems for SQL Server 2008 R2
  • Alter memory configuration
  • Execute pre-installation stress testing
  • Explore specific I/O operations
  • Plot server resource requirements
  • Define the SQL Server architecture
  • Implement pre-installation stress testing for SQL Server
Module 3: Installing and Configuring SQL Server 2008 R2

In this module candidates will learn how to install and configure SQL Server 2008 R2.


Lessons:
  • Preparing to Install SQL Server 2008 R2
  • Installing SQL Server 2008 R2
  • Upgrading and Automating Installation
Lab: Installing and Configuring SQL Server 2008 R2
  • Appraise installation requirements
  • Install the SQL Server instance
  • Post-installation setup and checks
  • Configure Server memory
  • Prepare to install SQL Server
  • Install SQL Server
  • Automate and upgrade the installation of SQL Server
Module 4: Working with Databases

In this module candidates will learn about the SQL Server 2008 R2 system databases, the physical structure of databases and the relevant common configuration options.


Lessons:
  • Overview of SQL Server Databases
  • Working with Files and Filegroups
  • Moving Database Files
Lab: Working with Databases
  • Alter tempdb configuration
  • Generate the RateTracking database
  • Attach the OldProspects database
  • Add multiple files to tempdb
  • Define the role and structure of SQL Server databases
  • Work with files and filegroups
  • Move database files within servers and between servers
Module 5: Understanding SQL Server 2008 R2 Recovery Models

In this module candidates will learn about the transaction log and SQL Server recovery models and details on the different backup strategies available for the SQL Server 2008 R2.


Lessons:
  • Backup Strategies
  • Understanding SQL Server Transaction Logging
  • Planning a SQL Server Backup Strategy
Lab: Understanding SQL Server 2008 R2 Recovery Models
  • Plot a backup strategy
  • Configure recovery models
  • Evaluate recovery models and strategy
  • Detail the critical concepts surrounding backup strategies
  • Describe the transaction logging competencies within the SQL Server database engine
  • Plot a SQL Server backup strategy
Module 6: Backup of SQL Server 2008 R2 Databases

In this module candidates will about SQL Server 2008 R2 Backup as well as the different backup types.


Lessons:
  • Backing up Databases and Transaction Logs
  • Managing Database Backups
  • Working with Backup Options
Lab: Backup of SQL Server 2008 R2 Databases
  • Examine backup compression
  • Transaction log and differential backup
  • Copy only and partial backup
  • Back up databases and transaction logs
  • Manage database backups
  • Working with advanced backup options
Module 7: Restoring SQL Server 2008 R2 Databases

In this module candidates will learn how to manage the restoration of databases.


Lessons:
  • Understanding the Restore Process
  • Restoring Databases
  • Working with Point-in-time Recovery
  • Restoring System Databases and Individual Files
Lab: Restoring SQL Server 2008 R2 Databases
  • Define a restore strategy
  • Restore the database
  • Making use of the STANDBY mode
  • Understanding the restore process
  • Restore databases
  • Work with point-in-time recovery
  • Restore system databases and individual files
Module 8: Importing and Exporting Data

In this module candidates will learn how to use the import and export wizards and how these relate to SSIS. The module also introduces BCP.


Lessons:
  • Transferring Data To/From SQL Server 2008 R2
  • Importing & Exporting Table Data
  • Inserting Data in Bulk
Lab: Importing and Exporting Data
  • Importing an Excel spreadsheet
  • Importing a CSV file
  • Generate and test an extraction package
  • Compare loading performance
  • Transfer data to and from SQL Server
  • Import and export table data
  • Insert bulk data and enhance the bulk insert procedure
Module 9: Authenticating and Authorizing Users

In this module candidates will learn about the SQL Server 2008 R2 security models, logins and users.


Lessons:
  • Authenticating Connections to SQL Server
  • Authorising Logins to Access Databases
  • Authorisation Across Servers
Lab: Authenticating and Authorizing Users
  • Creation of logins
  • How to correct an application login issue
  • Creating database users
  • Correct access to restored database
  • Define how SQL Server authenticates connections
  • Describe how logins are authorized to access databases
  • Define the requirements for authorization across servers
Module 10: Assigning Server and Database Roles

In this module candidates will learn about fixed server roles, fixed database roles and user-defined database roles.


Lessons:
  • Working with Server Roles
  • Working with Fixed Database Roles
  • Creating User-defined Database Roles
Lab: Assigning Server and Database Roles
  • Allocate server roles
  • Allocate fixed database roles
  • Generate and allocate user-defined database roles
  • Check role assignments
  • Work with server roles
  • Work with fixed database roles
  • Generate user-defined database roles
Module 11: Authorizing Users to Access Resources

In this module candidates will learn about permissions and the assignment of permissions.


Lessons:
  • Authorising User Access to Objects
  • Authorising Users to Execute Code
  • Configuring Permissions at the Schema Level
Lab: Authorising Users to Access Resources
  • Allocate Schema level permissions
  • Allocate Object level permissions
  • Test permissions
  • Allow user access to objects
  • Allow users to execute code
  • Configure permissions at the schema level
Module 12: Auditing SQL Server Environments

In this module candidates will learn about SQL Server auditing.


Lessons:
  • Options for Auditing Data Access in SQL Server
  • Implementing SQL Server Audit
  • Managing SQL Server Audit
Lab: Auditing SQL Server Environments
  • Define audit configuration and generate audit
  • Generate server audit specifications
  • Generate database audit specifications
  • Test audit functionality
  • Define the options for auditing data access in SQL Server
  • Implement SQL Server audit
  • Manage SQL Server audit
Module 13: Automating SQL Server 2008 R2 Management

In this module candidates will learn about the SQL Server agent, jobs and job history.


Lessons:
  • Automating SQL Server Management
  • Working with SQL Server Agent
  • Managing SQL Server Agent Jobs
Lab: Automating SQL Server 2008 R2 Management
  • Generating a data extraction job
  • Scheduling the data extraction job
  • Troubleshooting a failing job
  • Automating SQL Server management
  • Working with SQL Server agent
  • Managing SQL Server agent jobs
Module 14: Configuring Security for SQL Server Agent

In this module candidates will learn about SQL Server agent security, proxy accounts and credentials.


Lessons:
  • Understanding SQL Server Agent Security
  • Configuring Credentials
  • Configuring Proxy Accounts
Lab: Configuring Security for SQL Server Agent
  • Troubleshoot the failure of job execution
  • Solve the security issue
  • Implement further troubleshooting
  • Describe SQL Server Agent security
  • Configure credentials and proxy accounts
Module 15: Monitoring SQL Server 2008 R2 with Alerts and Notifications

In this module candidates will learn how to configure database mail, alerts and notifications.


Lessons:
  • Configuration of Database Mail
  • Monitoring SQL Server Errors
  • Configuring Operators, Alerts and Notifications
Lab: Monitoring SQL Agent Jobs with Alerts and Notifications
  • Configure database mail
  • Implement notifications
  • Implement alerts
  • Monitor SQL Server errors
  • Configure operators, alerts and notifications
Module 16: Performing Ongoing Database Maintenance

In this module candidates will learn about database maintenance plans.


Lessons:
  • Ensuring Database Integrity
  • Maintaining Indexes
  • Automating Routine Database Maintenance
Lab: Ongoing Database Maintenance
  • Check for database integrity by making use of DBCC CHECKDB
  • Correcting index fragmentation
  • Generate a database maintenance plan
  • Explore table lock performance
  • Ensuring database integrity
  • Maintaining indexes
  • Automating routine database maintenance
Module 17: Tracing Access to SQL Server 2008 R2

In this module candidates will learn about SQL Profiler and SQL Trace stored procedures.


Lessons:
  • Capturing Activity using SQL Server Profiler
  • Improving Performance with the Database Engine Tuning Advisor
  • Working with Tracing Options
Lab: Tracing Access to SQL Server 2008 R2
  • Capturing a trace by making use of the SQL Server Profiler
  • Analysing a trace by making use of the Database Engine Tuning Advisor
  • Configuring SQL Trace
  • Capturing activity by making use of SQL Server Profiler
  • Enhancing performance with the Database Engine Tuning Advisor
  • Working with tracing options
Module 18: Monitoring SQL Server 2008 R2

In this module candidates will learn about DMVs and the configuring data collection.


Lessons:
  • Monitoring Activity
  • Capturing and Managing Performance Data
  • Analysing Collected Performance Data
Lab: Monitoring SQL Server 2008 R2
  • Exploring DMVs
  • Configuring management data warehouse
  • Configuring instances for data collection
  • Working with data collector reports
  • Monitoring current activity
  • Capturing and managing performance data
  • Analysing collected performance data
Module 19: Managing Multiple Servers

In this module candidates will learn about central management servers and multi-server queries, virtualization of SQL Server and data-tier applications.


Lessons:
  • Working with Multiple Servers
  • Virtualizing SQL Server
  • Deploying and Upgrading Data-Tier Applications
Lab: Managing Multiple Servers
  • Configuring CMS and executing multi-server queries
  • Deploying a data-tier application
  • Registering and extracting a data-tier application
  • Upgrading a data-tier application
  • Working with multiple servers
  • Determining options for virtualizing SQL Server
  • Upgrading data-tier applications
Module 20: Troubleshooting Common SQL Server 2008 R2 Administrative Issues

In this module candidates will learn about common troubleshooting issues and gives direction on where to start looking for solutions.


Lessons:
  • SQL Server Troubleshooting Methodology
  • Resolving Service-related Issues
  • Resolving Concurrency Issues
  • Resolving Login and Connectivity Issues
Lab: Troubleshooting Common Issues
  • Troubleshooting and resolving SQL Server administrative issues
  • Defining SQL Server troubleshooting methodology
  • Resolving service-related issues
  • Resolving concurrency issues
  • Resolving login and connectivity issues
PLEASE NOTE: Every effort has been made to ensure the accuracy of all information contained herein. IT Help and Support Centre Ltd makes no warranty expressed or implied with respect to accuracy of this information, including price, product editorials or product specifications. IT Help and Support Centre Ltd or its suppliers shall not be liable for incidental, consequential or special damages arising from, or as a result of, any electronic transmission or the accuracy of the information contained herin, even if IT Help and Support Centre Ltd has been advised of the possibility of such damages. Product and manufacturer names are used only for the purpose of identification.

This Website is not sponsored by, endorsed by or affiliated with Cisco Systems, Inc. CCDA, CCDP, CCIE, CCIP, CCNA, CCNP, Cisco, Cisco IOS, Cisco Systems, the Cisco Systems logo, and Networking Academy are registered trademarks or trademarks of Cisco Systems, Inc. and/or its affiliates in the U.S. and certain other countries. All other trademarks mentioned in this web site are the property of their respective owners.
Privacy Statement Terms & Conditions