Administering Microsoft SQL Server 2012 Databases

193 

Loading....

Loading....

Loading....

Loading....

Loading....

Full text

(1)

Administering Microsoft SQL Server 2012 Databases

Cursusduur: 5 Dagen Cursuscode: M10775

Beschrijving:

Deze cursus is vervangen door cursus M20462 Administering Microsoft SQL Server Databases. This course has been replaced by course M20462 Administering Microsoft SQL Server Databases.

In deze vijfdaagse SQL Server 2012-cursus onder leiding van een docent doen deelnemers kennis en vaardigheden op in het onderhouden van een Microsoft SQL Server 2012-database. De cursus is gericht op het gebruik van de functies en hulpmiddelen die SQL Server 2012 aanbiedt voor het onderhoud van een database.==================================================

This five-day instructor-led SQL Server 2012 training course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2012 database. The course focuses on teaching individuals how to use SQL Server 2012 product features and tools related to maintaining a database.

Doelgroep:

In eerste instantie is deze cursus bedoeld voor personen die SQL Server-databases beheren en onderhouden. Het is hun belangrijkste verantwoordelijkheid om databases te beheren en te onderhouden, of zij werken in omgevingen waar databases een vooraanstaande rol spelen.

Een tweede doelgroep voor deze cursus bestaat uit personen die applicaties ontwikkelen die gebruik maken van gegevens uit SQL Server-databases.

==================================================

The primary audience for this course is individuals who administer and maintain SQL Server databases. These individuals perform database administration and maintenance as their primary area of responsibility, or work in environments where databases play a key role in their primary job.

The secondary audiences for this course are individuals who develop applications that deliver content from SQL Server databases.

Doelstelling:

Below are the some of the new features of SQL Server that has The course shows how SQL Server 2012 allows users to be been discussed in this course authenticated at the database level, rather than at the server level.

This is an important first step in for database containment. Discussion around servicing SQL Server (Service Packs,

Cumulative Updates, Hotfixes)

Understanding the relationship between the different levels of User-defined server roles updates to the SQL Server product is very important for those

working with it. The course now covers details on how hotfixes, In earlier versions of SQL Server, user-defined roles could be cumulative updates and service packs are related and when to created at the database level but not at the server level. In SQL apply each type of update. Server 2012, user-defined roles can also be defined at the server

level. This can help to increase the security of systems. Having the ability to configure more fine-grained permissions lets logins be assigned only the permissions that they need to do their work. SQL Server use of automated update

SQL Server can now utilize Windows Update for servicing. The

course shows how to enable automated updates and when the In-place upgrades of data-tier applications use of these updates should and should not be used.

In earlier versions of SQL Server, an upgrade of a data-tier

application involved migrating all the data within a database to a new database. In SQL Server 2012 this is no longer needed and the Partial database containment course shows how data-tier applications can be upgraded much

more quickly, and using less system resources. A significant challenge when migrating databases between

servers is the management of objects such as logins that are not contained within the database. The course now discusses the

partial containment options that are provided by SQL Server Appendix with intro to AlwaysOn, High Availability and Replication 2012. In particular, authentication-related changes are concepts

discussed.

Students who will be attempting the certification exam that is relevant to this course require knowledge of high availability and

(2)

replication. In earlier versions of this course, none of these details Users with passwords (contained users) were covered. In this version of the course, an appendix that covers

the core concepts for high availability (including SQL Server 2012 AlwaysOn technologies) and replication is provided.

Vereiste kennis en vaardigheden:

Examens en certificering

In addition to their professional experience, students who attend this training should already have the following technical knowledge:

Basic knowledge of the Microsoft Windows operating system and its core functionality.

Working knowledge of Transact-SQL. Working knowledge of relational databases. Some experience with database design.

Students who attend this training can meet the prerequisites by attending the following courses, or obtaining equivalent knowledge and skills:

(3)

Cursusinhoud:

Module 1: Introduction to SQL Server 2012 and

its Toolset line line

line Working with SQL Server Tools Working with SQL Server Tools Configuring SQL Server ServicesLab : Configuring SQL Server ServicesLab : This module introduces the entire SQL Server Introduction to SQL Server and its Introduction to SQL Server and its Toolset platform and its major tools. It covers editions, Toolset Verifying SQL Server Component versions, basics of network listeners, and Verifying SQL Server Component Installation

concepts of services and service accounts. Installation Altering Service Accounts for New

line Altering Service Accounts for New Instance

Introduction to the SQL Server Platform Instance Enabling Named Pipes Protocol for Both Enabling Named Pipes Protocol for Both Instances

Instances Creating an Alias for AdvDev

line Creating an Alias for AdvDev Ensuring SQL Browser is Disabled and Working with SQL Server Tools Ensuring SQL Browser is Disabled and Configure a Fixed TCP/IP Port (Only if Configuring SQL Server ServicesLab : Configure a Fixed TCP/IP Port (Only if time permitsAfter completing this module, Introduction to SQL Server and its Toolset time permitsAfter completing this module, students will be able to:

Verifying SQL Server Component students will be able to: Describe the SQL Server Platform. Installation Describe the SQL Server Platform. Work with SQL Server Tools. Altering Service Accounts for New Instance Work with SQL Server Tools. Configure SQL Server Services. Enabling Named Pipes Protocol for Both Configure SQL Server Services. Planning Server Resource Requirements Instances Planning Server Resource Requirements Pre-installation Testing for SQL ServerLab Creating an Alias for AdvDev Pre-installation Testing for SQL : Preparing Systems for SQL Server Ensuring SQL Browser is Disabled and ServerLab : Preparing Systems for SQL Adjust memory configuration Configure a Fixed TCP/IP Port (Only if time Server Pre-installation Stress Testing permitsAfter completing this module, Adjust memory configuration Check Specific I/O OperationsAfter students will be able to: Pre-installation Stress Testing completing this module, students will be Describe the SQL Server Platform. Check Specific I/O OperationsAfter able to:

Work with SQL Server Tools. completing this module, students will be Describe the SQL Server architecture. Configure SQL Server Services. able to: Plan for server resource requirements. Planning Server Resource Requirements Describe the SQL Server architecture. Conduct pre-installation stress testing for Pre-installation Testing for SQL ServerLab : Plan for server resource requirements. SQL Server.

Preparing Systems for SQL Server Conduct pre-installation stress testing for Installing SQL Server

Adjust memory configuration SQL Server. Upgrading and Automating InstallationLab Pre-installation Stress Testing Installing SQL Server : Installing and Configuring SQL Server Check Specific I/O OperationsAfter Upgrading and Automating Review installation requirements completing this module, students will be able InstallationLab : Installing and Install the SQL Server instance to: Configuring SQL Server Perform Post-installation Setup and Describe the SQL Server architecture. Review installation requirements Checks

Plan for server resource requirements. Install the SQL Server instance Configure Server MemoryAfter completing Conduct pre-installation stress testing for Perform Post-installation Setup and this module, students will be able to: SQL Server. Checks Prepare to install SQL Server. Installing SQL Server Configure Server MemoryAfter Install SQL Server.

Upgrading and Automating InstallationLab : completing this module, students will be Upgrade and automate the installation of Installing and Configuring SQL Server able to: SQL Server.

Review installation requirements Prepare to install SQL Server. Working with Files and Filegroups Install the SQL Server instance Install SQL Server. Moving Database FilesLab : Working with Perform Post-installation Setup and Checks Upgrade and automate the installation of Databases

Configure Server MemoryAfter completing SQL Server. Adjust tempdb configuration this module, students will be able to: Working with Files and Filegroups Create the RateTracking database Prepare to install SQL Server. Moving Database FilesLab : Working with Attach the OldProspects database Install SQL Server. Databases Add multiple files to tempdbAfter Upgrade and automate the installation of Adjust tempdb configuration completing this module, students will be SQL Server. Create the RateTracking database able to:

Working with Files and Filegroups Attach the OldProspects database Describe the role and structure of SQL Moving Database FilesLab : Working with Add multiple files to tempdbAfter Server databases.

Databases completing this module, students will be Work with files and filegroups. Adjust tempdb configuration able to: Move database files within servers and Create the RateTracking database Describe the role and structure of SQL between servers.

Attach the OldProspects database Server databases. Understanding SQL Server Transaction Add multiple files to tempdbAfter completing Work with files and filegroups. Logging

this module, students will be able to: Move database files within servers and Planning a SQL Server Backup

Describe the role and structure of SQL between servers. StrategyLab : Understanding SQL Server Server databases. Understanding SQL Server Transaction Recovery Models

(4)

Move database files within servers and Planning a SQL Server Backup Configure Recovery Models

between servers. StrategyLab : Understanding SQL Server Review recovery models and strategyAfter Understanding SQL Server Transaction Recovery Models completing this module, students will be Logging Plan a backup strategy able to:

Planning a SQL Server Backup StrategyLab Configure Recovery Models Describe the critical concepts surrounding : Understanding SQL Server Recovery Review recovery models and backup strategies.

Models strategyAfter completing this module, Explain the transaction logging capabilities Plan a backup strategy students will be able to: within the SQL Server database engine. Configure Recovery Models Describe the critical concepts Plan a SQL Server backup strategy. Review recovery models and strategyAfter surrounding backup strategies. Managing Database Backups

completing this module, students will be able Explain the transaction logging Working with Backup OptionsLab : Backup to: capabilities within the SQL Server of SQL Server Databases

Describe the critical concepts surrounding database engine. Investigate backup compression backup strategies. Plan a SQL Server backup strategy. Transaction log backup Explain the transaction logging capabilities Managing Database Backups Differential backup within the SQL Server database engine. Working with Backup OptionsLab : Copy-only backup

Plan a SQL Server backup strategy. Backup of SQL Server Databases Partial backupAfter completing this Managing Database Backups Investigate backup compression module, students will be able to: Working with Backup OptionsLab : Backup Transaction log backup Back up databases and transaction logs. of SQL Server Databases Differential backup Manage database backups.

Investigate backup compression Copy-only backup Work with more advanced backup options. Transaction log backup Partial backupAfter completing this Restoring Databases

Differential backup module, students will be able to: Working with Point-in-time recovery Copy-only backup Back up databases and transaction logs. Restoring System Databases and Partial backupAfter completing this module, Manage database backups. Individual FilesLab : Restoring SQL Server students will be able to: Work with more advanced backup 2012 Databases

Back up databases and transaction logs. options. Determine a restore strategy Manage database backups. Restoring Databases Restore the database

Work with more advanced backup options. Working with Point-in-time recovery Using STANDBY modeAfter completing Restoring Databases Restoring System Databases and this module, students will be able to: Working with Point-in-time recovery Individual FilesLab : Restoring SQL Understand the restore process. Restoring System Databases and Individual Server 2012 Databases Restore databases.

FilesLab : Restoring SQL Server 2012 Determine a restore strategy Work with Point-in-time Recovery. Databases Restore the database Restore system databases and individual Determine a restore strategy Using STANDBY modeAfter completing files.

Restore the database this module, students will be able to: Importing ; Exporting Table Data Using STANDBY modeAfter completing this Understand the restore process. Inserting Data in BulkLab : Importing and module, students will be able to: Restore databases. Exporting Data

Understand the restore process. Work with Point-in-time Recovery. Import the Excel spreadsheet Restore databases. Restore system databases and individual Import the CSV file

Work with Point-in-time Recovery. files. Create and test an extraction package Restore system databases and individual Importing ; Exporting Table Data Compare loading performanceAfter files. Inserting Data in BulkLab : Importing and completing this module, students will be Importing ; Exporting Table Data Exporting Data able to:

Inserting Data in BulkLab : Importing and Import the Excel spreadsheet Transfer data to and from SQL Server. Exporting Data Import the CSV file Import and export table data.

Import the Excel spreadsheet Create and test an extraction package Insert data in bulk and optimize the bulk Import the CSV file Compare loading performanceAfter insert process.

Create and test an extraction package completing this module, students will be Authorizing Logins to Access Databases Compare loading performanceAfter able to: Authorization Across ServersLab : completing this module, students will be able Transfer data to and from SQL Server. Authenticating and Authorizing Users to: Import and export table data. Create Logins

Transfer data to and from SQL Server. Insert data in bulk and optimize the bulk Correct an Application Login Issue Import and export table data. insert process. Create Database Users

Insert data in bulk and optimize the bulk Authorizing Logins to Access Databases Correct Access to RestoredAfter insert process. Authorization Across ServersLab : completing this module, students will be Authorizing Logins to Access Databases Authenticating and Authorizing Users able to:

Authorization Across ServersLab : Create Logins Describe how SQL Server authenticates Authenticating and Authorizing Users Correct an Application Login Issue connections.

Create Logins Create Database Users Describe how logins are authorized to Correct an Application Login Issue Correct Access to RestoredAfter access databases.

Create Database Users completing this module, students will be Explain the requirements for authorization Correct Access to RestoredAfter completing able to: across servers.

this module, students will be able to: Describe how SQL Server authenticates Working with Fixed Database Roles Describe how SQL Server authenticates connections. Creating User-defined Database RolesLab connections. Describe how logins are authorized to : Assigning Server and Database Roles

(5)

Describe how logins are authorized to access databases. Assign Server Roles access databases. Explain the requirements for authorization Assign Fixed Database Roles

Explain the requirements for authorization across servers. Create and Assign User-defined Database across servers. Working with Fixed Database Roles Roles

Working with Fixed Database Roles Creating User-defined Database Check Role AssignmentsAfter completing Creating User-defined Database RolesLab : RolesLab : Assigning Server and this module, students will be able to: Assigning Server and Database Roles Database Roles Work with server roles.

Assign Server Roles Assign Server Roles Work with fixed database roles. Assign Fixed Database Roles Assign Fixed Database Roles Create user-defined database roles. Create and Assign User-defined Database Create and Assign User-defined Authorizing Users to Execute Code Roles Database Roles Configuring Permissions at the Schema Check Role AssignmentsAfter completing Check Role AssignmentsAfter completing LevelLab : Authorizing Users to Access this module, students will be able to: this module, students will be able to: Resources

Work with server roles. Work with server roles. Assign Schema-level Permissions Work with fixed database roles. Work with fixed database roles. Assign Object-level Permissions Create user-defined database roles. Create user-defined database roles. Test PermissionsAfter completing this Authorizing Users to Execute Code Authorizing Users to Execute Code module, students will be able to: Configuring Permissions at the Schema Configuring Permissions at the Schema Authorize user access to objects. LevelLab : Authorizing Users to Access LevelLab : Authorizing Users to Access Authorize users to execute code. Resources Resources Configure permissions at the schema Assign Schema-level Permissions Assign Schema-level Permissions level.

Assign Object-level Permissions Assign Object-level Permissions Implementing SQL Server Audit

Test PermissionsAfter completing this Test PermissionsAfter completing this Managing SQL Server AuditLab : Auditing module, students will be able to: module, students will be able to: SQL Server Environments

Authorize user access to objects. Authorize user access to objects. Determine audit configuration and create Authorize users to execute code. Authorize users to execute code. audit

Configure permissions at the schema level. Configure permissions at the schema Create server audit specifications Implementing SQL Server Audit level. Create database audit specifications Managing SQL Server AuditLab : Auditing Implementing SQL Server Audit Test audit functionalityAfter completing this SQL Server Environments Managing SQL Server AuditLab : module, students will be able to:

Determine audit configuration and create Auditing SQL Server Environments Describe the options for auditing data audit Determine audit configuration and create access in SQL Server.

Create server audit specifications audit Implement SQL Server Audit. Create database audit specifications Create server audit specifications Manage SQL Server Audit. Test audit functionalityAfter completing this Create database audit specifications Working with SQL Server Agent module, students will be able to: Test audit functionalityAfter completing Managing SQL Server Agent JobsLab : Describe the options for auditing data this module, students will be able to: Automating SQL Server Management access in SQL Server. Describe the options for auditing data Create a Data Extraction Job Implement SQL Server Audit. access in SQL Server. Schedule the Data Extraction Job Manage SQL Server Audit. Implement SQL Server Audit. Troubleshoot a Failing JobAfter completing Working with SQL Server Agent Manage SQL Server Audit. this module, students will be able to: Managing SQL Server Agent JobsLab : Working with SQL Server Agent Automate SQL Server Management. Automating SQL Server Management Managing SQL Server Agent JobsLab : Work with SQL Server Agent. Create a Data Extraction Job Automating SQL Server Management Manage SQL Server Agent jobs. Schedule the Data Extraction Job Create a Data Extraction Job Configuring Credentials Troubleshoot a Failing JobAfter completing Schedule the Data Extraction Job Configuring Proxy AccountsLab :

this module, students will be able to: Troubleshoot a Failing JobAfter Configuring Security for SQL Server Agent Automate SQL Server Management. completing this module, students will be Troubleshoot job execution failure Work with SQL Server Agent. able to: Resolve the security issue

Manage SQL Server Agent jobs. Automate SQL Server Management. Perform further troubleshootingAfter Configuring Credentials Work with SQL Server Agent. completing this module, students will be Configuring Proxy AccountsLab : Manage SQL Server Agent jobs. able to:

Configuring Security for SQL Server Agent Configuring Credentials Explain SQL Server Agent security. Troubleshoot job execution failure Configuring Proxy AccountsLab : Configure credentials.

Resolve the security issue Configuring Security for SQL Server Configure Proxy accounts. Perform further troubleshootingAfter Agent Monitoring SQL Server Errors completing this module, students will be able Troubleshoot job execution failure Configuring Operators, Alerts and to: Resolve the security issue NotificationsLab : Monitoring SQL Agent Explain SQL Server Agent security. Perform further troubleshootingAfter Jobs with Alerts and Notifications Configure credentials. completing this module, students will be Configure Database Mail Configure Proxy accounts. able to: Implement Notifications

Monitoring SQL Server Errors Explain SQL Server Agent security. Implement AlertsAfter completing this Configuring Operators, Alerts and Configure credentials. module, students will be able to: NotificationsLab : Monitoring SQL Agent Configure Proxy accounts. Configure database mail. Jobs with Alerts and Notifications Monitoring SQL Server Errors Monitor SQL Server errors. Configure Database Mail Configuring Operators, Alerts and Configure operators, alerts and

(6)

Implement Notifications NotificationsLab : Monitoring SQL Agent notifications. Implement AlertsAfter completing this Jobs with Alerts and Notifications Maintaining Indexes

module, students will be able to: Configure Database Mail Automating Routine Database Configure database mail. Implement Notifications MaintenanceLab : Performing Ongoing Monitor SQL Server errors. Implement AlertsAfter completing this Database Maintenance

Configure operators, alerts and notifications. module, students will be able to: Check database integrity using DBCC Maintaining Indexes Configure database mail. CHECKDB

Automating Routine Database Monitor SQL Server errors. Correct index fragmentation MaintenanceLab : Performing Ongoing Configure operators, alerts and Create a database maintenance plan Database Maintenance notifications. Investigate table lock performanceAfter Check database integrity using DBCC Maintaining Indexes completing this module, students will be CHECKDB Automating Routine Database able to:

Correct index fragmentation MaintenanceLab : Performing Ongoing Ensure database integrity. Create a database maintenance plan Database Maintenance Maintain indexes.

Investigate table lock performanceAfter Check database integrity using DBCC Automate routine database maintenance. completing this module, students will be able CHECKDB Improving Performance with the Database to: Correct index fragmentation Engine Tuning Advisor

Ensure database integrity. Create a database maintenance plan Working with Tracing OptionsLab : Tracing Maintain indexes. Investigate table lock performanceAfter Access to SQL Server 2012

Automate routine database maintenance. completing this module, students will be Capture a trace using SQL Server Profiler Improving Performance with the Database able to: Analyze a trace using Database Engine Engine Tuning Advisor Ensure database integrity. Tuning Advisor

Working with Tracing OptionsLab : Tracing Maintain indexes. Configure SQL TraceAfter completing this Access to SQL Server 2012 Automate routine database maintenance. module, students will be able to:

Capture a trace using SQL Server Profiler Improving Performance with the Capture activity using SQL Server Profiler Analyze a trace using Database Engine Database Engine Tuning Advisor and Extended Events Profiler.

Tuning Advisor Working with Tracing OptionsLab : Improve performance with the Database Configure SQL TraceAfter completing this Tracing Access to SQL Server 2012 Engine Tuning Advisor.

module, students will be able to: Capture a trace using SQL Server Profiler Work with tracing options.

Capture activity using SQL Server Profiler Analyze a trace using Database Engine Capturing and Managing Performance and Extended Events Profiler. Tuning Advisor Data

Improve performance with the Database Configure SQL TraceAfter completing Analyzing Collected Performance DataLab Engine Tuning Advisor. this module, students will be able to: : Monitoring SQL Server 2012

Work with tracing options. Capture activity using SQL Server Profiler Investigating DMVs

Capturing and Managing Performance Data and Extended Events Profiler. Configure Management Data Warehouse Analyzing Collected Performance DataLab : Improve performance with the Database Configure Instances for Data Collection Monitoring SQL Server 2012 Engine Tuning Advisor. Work with Data Collector ReportsAfter Investigating DMVs Work with tracing options. completing this module, students will be Configure Management Data Warehouse Capturing and Managing Performance able to:

Configure Instances for Data Collection Data Monitor current activity.

Work with Data Collector ReportsAfter Analyzing Collected Performance Capture and manage performance data. completing this module, students will be able DataLab : Monitoring SQL Server 2012 Analyze collected performance data. to: Investigating DMVs Virtualizing SQL Server

Monitor current activity. Configure Management Data Warehouse Deploying and Upgrading Data-Tier Capture and manage performance data. Configure Instances for Data Collection ApplicationsLab : Managing Multiple Analyze collected performance data. Work with Data Collector ReportsAfter Servers

Virtualizing SQL Server completing this module, students will be Configure CMS and execute multi-server Deploying and Upgrading Data-Tier able to: queries

ApplicationsLab : Managing Multiple Servers Monitor current activity. Deploy a data-tier application

Configure CMS and execute multi-server Capture and manage performance data. Register and extract a data-tier application queries Analyze collected performance data. Upgrade a data-tier applicationAfter Deploy a data-tier application Virtualizing SQL Server completing this module, students will be Register and extract a data-tier application Deploying and Upgrading Data-Tier able to:

Upgrade a data-tier applicationAfter ApplicationsLab : Managing Multiple Work with multiple servers. completing this module, students will be able Servers Describe options for virtualizing SQL to: Configure CMS and execute multi-server Server.

Work with multiple servers. queries Deploy and upgrade Data-Tier Describe options for virtualizing SQL Server. Deploy a data-tier application Applications.

Deploy and upgrade Data-Tier Applications. Register and extract a data-tier Resolving Service-related Issues Resolving Service-related Issues application Resolving Concurrency Issues Resolving Concurrency Issues Upgrade a data-tier applicationAfter Resolving Login and Connectivity Resolving Login and Connectivity IssuesLab completing this module, students will be IssuesLab : Troubleshooting Common : Troubleshooting Common Issues able to: Issues

Troubleshoot and resolve SQL Server Work with multiple servers. Troubleshoot and resolve SQL Server administrative issuesAfter completing this Describe options for virtualizing SQL administrative issuesAfter completing this module, students will be able to: Server. module, students will be able to:

(7)

Explain SQL Server troubleshooting Deploy and upgrade Data-Tier Explain SQL Server troubleshooting

methodology. Applications. methodology.

Resolve service-related issues. Resolving Service-related Issues Resolve service-related issues. Resolve concurrency issues. Resolving Concurrency Issues Resolve concurrency issues. Resolve login and connectivity issues Resolving Login and Connectivity Resolve login and connectivity issues

IssuesLab : Troubleshooting Common Issues

line Troubleshoot and resolve SQL Server line

Working with SQL Server Tools administrative issuesAfter completing this Working with SQL Server Tools Configuring SQL Server ServicesLab : module, students will be able to: Configuring SQL Server ServicesLab : Introduction to SQL Server and its Toolset Explain SQL Server troubleshooting Introduction to SQL Server and its Toolset Verifying SQL Server Component methodology. Verifying SQL Server Component Installation Resolve service-related issues. Installation

Altering Service Accounts for New Instance Resolve concurrency issues. Altering Service Accounts for New Enabling Named Pipes Protocol for Both Resolve login and connectivity issues Instance

Instances Enabling Named Pipes Protocol for Both

Creating an Alias for AdvDev Instances

Ensuring SQL Browser is Disabled and line Creating an Alias for AdvDev

Configure a Fixed TCP/IP Port (Only if time Working with SQL Server Tools Ensuring SQL Browser is Disabled and permitsAfter completing this module, Configuring SQL Server ServicesLab : Configure a Fixed TCP/IP Port (Only if students will be able to: Introduction to SQL Server and its time permitsAfter completing this module, Describe the SQL Server Platform. Toolset students will be able to:

Work with SQL Server Tools. Verifying SQL Server Component Describe the SQL Server Platform. Configure SQL Server Services. Installation Work with SQL Server Tools. Planning Server Resource Requirements Altering Service Accounts for New Configure SQL Server Services. Pre-installation Testing for SQL ServerLab : Instance Planning Server Resource Requirements Preparing Systems for SQL Server Enabling Named Pipes Protocol for Both Pre-installation Testing for SQL ServerLab Adjust memory configuration Instances : Preparing Systems for SQL Server Pre-installation Stress Testing Creating an Alias for AdvDev Adjust memory configuration Check Specific I/O OperationsAfter Ensuring SQL Browser is Disabled and Pre-installation Stress Testing completing this module, students will be able Configure a Fixed TCP/IP Port (Only if Check Specific I/O OperationsAfter to: time permitsAfter completing this module, completing this module, students will be Describe the SQL Server architecture. students will be able to: able to:

Plan for server resource requirements. Describe the SQL Server Platform. Describe the SQL Server architecture. Conduct pre-installation stress testing for Work with SQL Server Tools. Plan for server resource requirements. SQL Server. Configure SQL Server Services. Conduct pre-installation stress testing for Installing SQL Server Planning Server Resource Requirements SQL Server.

Upgrading and Automating InstallationLab : Pre-installation Testing for SQL Installing SQL Server

Installing and Configuring SQL Server ServerLab : Preparing Systems for SQL Upgrading and Automating InstallationLab Review installation requirements Server : Installing and Configuring SQL Server Install the SQL Server instance Adjust memory configuration Review installation requirements Perform Post-installation Setup and Checks Pre-installation Stress Testing Install the SQL Server instance Configure Server MemoryAfter completing Check Specific I/O OperationsAfter Perform Post-installation Setup and this module, students will be able to: completing this module, students will be Checks

Prepare to install SQL Server. able to: Configure Server MemoryAfter completing Install SQL Server. Describe the SQL Server architecture. this module, students will be able to: Upgrade and automate the installation of Plan for server resource requirements. Prepare to install SQL Server. SQL Server. Conduct pre-installation stress testing for Install SQL Server.

Working with Files and Filegroups SQL Server. Upgrade and automate the installation of Moving Database FilesLab : Working with Installing SQL Server SQL Server.

Databases Upgrading and Automating Working with Files and Filegroups Adjust tempdb configuration InstallationLab : Installing and Moving Database FilesLab : Working with Create the RateTracking database Configuring SQL Server Databases

Attach the OldProspects database Review installation requirements Adjust tempdb configuration Add multiple files to tempdbAfter completing Install the SQL Server instance Create the RateTracking database this module, students will be able to: Perform Post-installation Setup and Attach the OldProspects database Describe the role and structure of SQL Checks Add multiple files to tempdbAfter Server databases. Configure Server MemoryAfter completing this module, students will be Work with files and filegroups. completing this module, students will be able to:

Move database files within servers and able to: Describe the role and structure of SQL between servers. Prepare to install SQL Server. Server databases.

Understanding SQL Server Transaction Install SQL Server. Work with files and filegroups. Logging Upgrade and automate the installation of Move database files within servers and Planning a SQL Server Backup StrategyLab SQL Server. between servers.

: Understanding SQL Server Recovery Working with Files and Filegroups Understanding SQL Server Transaction Models Moving Database FilesLab : Working with Logging

(8)

Plan a backup strategy Databases Planning a SQL Server Backup

Configure Recovery Models Adjust tempdb configuration StrategyLab : Understanding SQL Server Review recovery models and strategyAfter Create the RateTracking database Recovery Models

completing this module, students will be able Attach the OldProspects database Plan a backup strategy to: Add multiple files to tempdbAfter Configure Recovery Models

Describe the critical concepts surrounding completing this module, students will be Review recovery models and strategyAfter backup strategies. able to: completing this module, students will be Explain the transaction logging capabilities Describe the role and structure of SQL able to:

within the SQL Server database engine. Server databases. Describe the critical concepts surrounding Plan a SQL Server backup strategy. Work with files and filegroups. backup strategies.

Managing Database Backups Move database files within servers and Explain the transaction logging capabilities Working with Backup OptionsLab : Backup between servers. within the SQL Server database engine. of SQL Server Databases Understanding SQL Server Transaction Plan a SQL Server backup strategy. Investigate backup compression Logging Managing Database Backups

Transaction log backup Planning a SQL Server Backup Working with Backup OptionsLab : Backup Differential backup StrategyLab : Understanding SQL Server of SQL Server Databases

Copy-only backup Recovery Models Investigate backup compression Partial backupAfter completing this module, Plan a backup strategy Transaction log backup students will be able to: Configure Recovery Models Differential backup Back up databases and transaction logs. Review recovery models and Copy-only backup

Manage database backups. strategyAfter completing this module, Partial backupAfter completing this Work with more advanced backup options. students will be able to: module, students will be able to: Restoring Databases Describe the critical concepts Back up databases and transaction logs. Working with Point-in-time recovery surrounding backup strategies. Manage database backups.

Restoring System Databases and Individual Explain the transaction logging Work with more advanced backup options. FilesLab : Restoring SQL Server 2012 capabilities within the SQL Server Restoring Databases

Databases database engine. Working with Point-in-time recovery Determine a restore strategy Plan a SQL Server backup strategy. Restoring System Databases and Restore the database Managing Database Backups Individual FilesLab : Restoring SQL Server Using STANDBY modeAfter completing this Working with Backup OptionsLab : 2012 Databases

module, students will be able to: Backup of SQL Server Databases Determine a restore strategy Understand the restore process. Investigate backup compression Restore the database

Restore databases. Transaction log backup Using STANDBY modeAfter completing Work with Point-in-time Recovery. Differential backup this module, students will be able to: Restore system databases and individual Copy-only backup Understand the restore process. files. Partial backupAfter completing this Restore databases.

Importing ; Exporting Table Data module, students will be able to: Work with Point-in-time Recovery. Inserting Data in BulkLab : Importing and Back up databases and transaction logs. Restore system databases and individual Exporting Data Manage database backups. files.

Import the Excel spreadsheet Work with more advanced backup Importing ; Exporting Table Data Import the CSV file options. Inserting Data in BulkLab : Importing and Create and test an extraction package Restoring Databases Exporting Data

Compare loading performanceAfter Working with Point-in-time recovery Import the Excel spreadsheet completing this module, students will be able Restoring System Databases and Import the CSV file

to: Individual FilesLab : Restoring SQL Create and test an extraction package Transfer data to and from SQL Server. Server 2012 Databases Compare loading performanceAfter Import and export table data. Determine a restore strategy completing this module, students will be Insert data in bulk and optimize the bulk Restore the database able to:

insert process. Using STANDBY modeAfter completing Transfer data to and from SQL Server. Authorizing Logins to Access Databases this module, students will be able to: Import and export table data.

Authorization Across ServersLab : Understand the restore process. Insert data in bulk and optimize the bulk Authenticating and Authorizing Users Restore databases. insert process.

Create Logins Work with Point-in-time Recovery. Authorizing Logins to Access Databases Correct an Application Login Issue Restore system databases and individual Authorization Across ServersLab : Create Database Users files. Authenticating and Authorizing Users Correct Access to RestoredAfter completing Importing ; Exporting Table Data Create Logins

this module, students will be able to: Inserting Data in BulkLab : Importing and Correct an Application Login Issue Describe how SQL Server authenticates Exporting Data Create Database Users

connections. Import the Excel spreadsheet Correct Access to RestoredAfter Describe how logins are authorized to Import the CSV file completing this module, students will be access databases. Create and test an extraction package able to:

Explain the requirements for authorization Compare loading performanceAfter Describe how SQL Server authenticates across servers. completing this module, students will be connections.

Working with Fixed Database Roles able to: Describe how logins are authorized to Creating User-defined Database RolesLab : Transfer data to and from SQL Server. access databases.

(9)

Assign Server Roles Insert data in bulk and optimize the bulk across servers.

Assign Fixed Database Roles insert process. Working with Fixed Database Roles Create and Assign User-defined Database Authorizing Logins to Access Databases Creating User-defined Database RolesLab Roles Authorization Across ServersLab : : Assigning Server and Database Roles Check Role AssignmentsAfter completing Authenticating and Authorizing Users Assign Server Roles

this module, students will be able to: Create Logins Assign Fixed Database Roles

Work with server roles. Correct an Application Login Issue Create and Assign User-defined Database Work with fixed database roles. Create Database Users Roles

Create user-defined database roles. Correct Access to RestoredAfter Check Role AssignmentsAfter completing Authorizing Users to Execute Code completing this module, students will be this module, students will be able to: Configuring Permissions at the Schema able to: Work with server roles.

LevelLab : Authorizing Users to Access Describe how SQL Server authenticates Work with fixed database roles. Resources connections. Create user-defined database roles. Assign Schema-level Permissions Describe how logins are authorized to Authorizing Users to Execute Code Assign Object-level Permissions access databases. Configuring Permissions at the Schema Test PermissionsAfter completing this Explain the requirements for authorization LevelLab : Authorizing Users to Access module, students will be able to: across servers. Resources

Authorize user access to objects. Working with Fixed Database Roles Assign Schema-level Permissions Authorize users to execute code. Creating User-defined Database Assign Object-level Permissions Configure permissions at the schema level. RolesLab : Assigning Server and Test PermissionsAfter completing this Implementing SQL Server Audit Database Roles module, students will be able to: Managing SQL Server AuditLab : Auditing Assign Server Roles Authorize user access to objects. SQL Server Environments Assign Fixed Database Roles Authorize users to execute code. Determine audit configuration and create Create and Assign User-defined Configure permissions at the schema

audit Database Roles level.

Create server audit specifications Check Role AssignmentsAfter completing Implementing SQL Server Audit

Create database audit specifications this module, students will be able to: Managing SQL Server AuditLab : Auditing Test audit functionalityAfter completing this Work with server roles. SQL Server Environments

module, students will be able to: Work with fixed database roles. Determine audit configuration and create Describe the options for auditing data Create user-defined database roles. audit

access in SQL Server. Authorizing Users to Execute Code Create server audit specifications Implement SQL Server Audit. Configuring Permissions at the Schema Create database audit specifications Manage SQL Server Audit. LevelLab : Authorizing Users to Access Test audit functionalityAfter completing this Working with SQL Server Agent Resources module, students will be able to:

Managing SQL Server Agent JobsLab : Assign Schema-level Permissions Describe the options for auditing data Automating SQL Server Management Assign Object-level Permissions access in SQL Server.

Create a Data Extraction Job Test PermissionsAfter completing this Implement SQL Server Audit. Schedule the Data Extraction Job module, students will be able to: Manage SQL Server Audit. Troubleshoot a Failing JobAfter completing Authorize user access to objects. Working with SQL Server Agent this module, students will be able to: Authorize users to execute code. Managing SQL Server Agent JobsLab : Automate SQL Server Management. Configure permissions at the schema Automating SQL Server Management Work with SQL Server Agent. level. Create a Data Extraction Job Manage SQL Server Agent jobs. Implementing SQL Server Audit Schedule the Data Extraction Job Configuring Credentials Managing SQL Server AuditLab : Troubleshoot a Failing JobAfter completing Configuring Proxy AccountsLab : Auditing SQL Server Environments this module, students will be able to: Configuring Security for SQL Server Agent Determine audit configuration and create Automate SQL Server Management. Troubleshoot job execution failure audit Work with SQL Server Agent. Resolve the security issue Create server audit specifications Manage SQL Server Agent jobs. Perform further troubleshootingAfter Create database audit specifications Configuring Credentials completing this module, students will be able Test audit functionalityAfter completing Configuring Proxy AccountsLab :

to: this module, students will be able to: Configuring Security for SQL Server Agent Explain SQL Server Agent security. Describe the options for auditing data Troubleshoot job execution failure Configure credentials. access in SQL Server. Resolve the security issue

Configure Proxy accounts. Implement SQL Server Audit. Perform further troubleshootingAfter Monitoring SQL Server Errors Manage SQL Server Audit. completing this module, students will be Configuring Operators, Alerts and Working with SQL Server Agent able to:

NotificationsLab : Monitoring SQL Agent Managing SQL Server Agent JobsLab : Explain SQL Server Agent security. Jobs with Alerts and Notifications Automating SQL Server Management Configure credentials.

Configure Database Mail Create a Data Extraction Job Configure Proxy accounts. Implement Notifications Schedule the Data Extraction Job Monitoring SQL Server Errors Implement AlertsAfter completing this Troubleshoot a Failing JobAfter Configuring Operators, Alerts and module, students will be able to: completing this module, students will be NotificationsLab : Monitoring SQL Agent Configure database mail. able to: Jobs with Alerts and Notifications Monitor SQL Server errors. Automate SQL Server Management. Configure Database Mail Configure operators, alerts and notifications. Work with SQL Server Agent. Implement Notifications

(10)

Automating Routine Database Configuring Credentials module, students will be able to: MaintenanceLab : Performing Ongoing Configuring Proxy AccountsLab : Configure database mail. Database Maintenance Configuring Security for SQL Server Monitor SQL Server errors. Check database integrity using DBCC Agent Configure operators, alerts and CHECKDB Troubleshoot job execution failure notifications.

Correct index fragmentation Resolve the security issue Maintaining Indexes

Create a database maintenance plan Perform further troubleshootingAfter Automating Routine Database Investigate table lock performanceAfter completing this module, students will be MaintenanceLab : Performing Ongoing completing this module, students will be able able to: Database Maintenance

to: Explain SQL Server Agent security. Check database integrity using DBCC Ensure database integrity. Configure credentials. CHECKDB

Maintain indexes. Configure Proxy accounts. Correct index fragmentation Automate routine database maintenance. Monitoring SQL Server Errors Create a database maintenance plan Improving Performance with the Database Configuring Operators, Alerts and Investigate table lock performanceAfter Engine Tuning Advisor NotificationsLab : Monitoring SQL Agent completing this module, students will be Working with Tracing OptionsLab : Tracing Jobs with Alerts and Notifications able to:

Access to SQL Server 2012 Configure Database Mail Ensure database integrity. Capture a trace using SQL Server Profiler Implement Notifications Maintain indexes.

Analyze a trace using Database Engine Implement AlertsAfter completing this Automate routine database maintenance. Tuning Advisor module, students will be able to: Improving Performance with the Database Configure SQL TraceAfter completing this Configure database mail. Engine Tuning Advisor

module, students will be able to: Monitor SQL Server errors. Working with Tracing OptionsLab : Tracing Capture activity using SQL Server Profiler Configure operators, alerts and Access to SQL Server 2012

and Extended Events Profiler. notifications. Capture a trace using SQL Server Profiler Improve performance with the Database Maintaining Indexes Analyze a trace using Database Engine Engine Tuning Advisor. Automating Routine Database Tuning Advisor

Work with tracing options. MaintenanceLab : Performing Ongoing Configure SQL TraceAfter completing this Capturing and Managing Performance Data Database Maintenance module, students will be able to:

Analyzing Collected Performance DataLab : Check database integrity using DBCC Capture activity using SQL Server Profiler Monitoring SQL Server 2012 CHECKDB and Extended Events Profiler.

Investigating DMVs Correct index fragmentation Improve performance with the Database Configure Management Data Warehouse Create a database maintenance plan Engine Tuning Advisor.

Configure Instances for Data Collection Investigate table lock performanceAfter Work with tracing options.

Work with Data Collector ReportsAfter completing this module, students will be Capturing and Managing Performance completing this module, students will be able able to: Data

to: Ensure database integrity. Analyzing Collected Performance DataLab Monitor current activity. Maintain indexes. : Monitoring SQL Server 2012

Capture and manage performance data. Automate routine database maintenance. Investigating DMVs

Analyze collected performance data. Improving Performance with the Configure Management Data Warehouse Virtualizing SQL Server Database Engine Tuning Advisor Configure Instances for Data Collection Deploying and Upgrading Data-Tier Working with Tracing OptionsLab : Work with Data Collector ReportsAfter ApplicationsLab : Managing Multiple Servers Tracing Access to SQL Server 2012 completing this module, students will be Configure CMS and execute multi-server Capture a trace using SQL Server Profiler able to:

queries Analyze a trace using Database Engine Monitor current activity.

Deploy a data-tier application Tuning Advisor Capture and manage performance data. Register and extract a data-tier application Configure SQL TraceAfter completing Analyze collected performance data. Upgrade a data-tier applicationAfter this module, students will be able to: Virtualizing SQL Server

completing this module, students will be able Capture activity using SQL Server Profiler Deploying and Upgrading Data-Tier to: and Extended Events Profiler. ApplicationsLab : Managing Multiple Work with multiple servers. Improve performance with the Database Servers

Describe options for virtualizing SQL Server. Engine Tuning Advisor. Configure CMS and execute multi-server Deploy and upgrade Data-Tier Applications. Work with tracing options. queries

Resolving Service-related Issues Capturing and Managing Performance Deploy a data-tier application

Resolving Concurrency Issues Data Register and extract a data-tier application Resolving Login and Connectivity IssuesLab Analyzing Collected Performance Upgrade a data-tier applicationAfter : Troubleshooting Common Issues DataLab : Monitoring SQL Server 2012 completing this module, students will be Troubleshoot and resolve SQL Server Investigating DMVs able to:

administrative issuesAfter completing this Configure Management Data Warehouse Work with multiple servers. module, students will be able to: Configure Instances for Data Collection Describe options for virtualizing SQL Explain SQL Server troubleshooting Work with Data Collector ReportsAfter Server.

methodology. completing this module, students will be Deploy and upgrade Data-Tier Resolve service-related issues. able to: Applications.

Resolve concurrency issues. Monitor current activity. Resolving Service-related Issues Resolve login and connectivity issues Capture and manage performance data. Resolving Concurrency Issues

Analyze collected performance data. Resolving Login and Connectivity Virtualizing SQL Server IssuesLab : Troubleshooting Common

(11)

line Deploying and Upgrading Data-Tier Issues

Working with SQL Server Tools ApplicationsLab : Managing Multiple Troubleshoot and resolve SQL Server Configuring SQL Server ServicesLab : Servers administrative issuesAfter completing this Introduction to SQL Server and its Toolset Configure CMS and execute multi-server module, students will be able to: Verifying SQL Server Component queries Explain SQL Server troubleshooting Installation Deploy a data-tier application methodology.

Altering Service Accounts for New Instance Register and extract a data-tier Resolve service-related issues. Enabling Named Pipes Protocol for Both application Resolve concurrency issues. Instances Upgrade a data-tier applicationAfter Resolve login and connectivity issues Creating an Alias for AdvDev completing this module, students will be

Ensuring SQL Browser is Disabled and able to:

Configure a Fixed TCP/IP Port (Only if time Work with multiple servers. line

permitsAfter completing this module, Describe options for virtualizing SQL Working with SQL Server Tools students will be able to: Server. Configuring SQL Server ServicesLab : Describe the SQL Server Platform. Deploy and upgrade Data-Tier Introduction to SQL Server and its Toolset Work with SQL Server Tools. Applications. Verifying SQL Server Component Configure SQL Server Services. Resolving Service-related Issues Installation

Planning Server Resource Requirements Resolving Concurrency Issues Altering Service Accounts for New Pre-installation Testing for SQL ServerLab : Resolving Login and Connectivity Instance

Preparing Systems for SQL Server IssuesLab : Troubleshooting Common Enabling Named Pipes Protocol for Both Adjust memory configuration Issues Instances

Pre-installation Stress Testing Troubleshoot and resolve SQL Server Creating an Alias for AdvDev

Check Specific I/O OperationsAfter administrative issuesAfter completing this Ensuring SQL Browser is Disabled and completing this module, students will be able module, students will be able to: Configure a Fixed TCP/IP Port (Only if to: Explain SQL Server troubleshooting time permitsAfter completing this module, Describe the SQL Server architecture. methodology. students will be able to:

Plan for server resource requirements. Resolve service-related issues. Describe the SQL Server Platform. Conduct pre-installation stress testing for Resolve concurrency issues. Work with SQL Server Tools. SQL Server. Resolve login and connectivity issues Configure SQL Server Services. Installing SQL Server Planning Server Resource Requirements Upgrading and Automating InstallationLab : Pre-installation Testing for SQL ServerLab Installing and Configuring SQL Server line : Preparing Systems for SQL Server Review installation requirements Working with SQL Server Tools Adjust memory configuration Install the SQL Server instance Configuring SQL Server ServicesLab : Pre-installation Stress Testing Perform Post-installation Setup and Checks Introduction to SQL Server and its Check Specific I/O OperationsAfter Configure Server MemoryAfter completing Toolset completing this module, students will be this module, students will be able to: Verifying SQL Server Component able to:

Prepare to install SQL Server. Installation Describe the SQL Server architecture. Install SQL Server. Altering Service Accounts for New Plan for server resource requirements. Upgrade and automate the installation of Instance Conduct pre-installation stress testing for SQL Server. Enabling Named Pipes Protocol for Both SQL Server.

Working with Files and Filegroups Instances Installing SQL Server

Moving Database FilesLab : Working with Creating an Alias for AdvDev Upgrading and Automating InstallationLab Databases Ensuring SQL Browser is Disabled and : Installing and Configuring SQL Server Adjust tempdb configuration Configure a Fixed TCP/IP Port (Only if Review installation requirements Create the RateTracking database time permitsAfter completing this module, Install the SQL Server instance Attach the OldProspects database students will be able to: Perform Post-installation Setup and Add multiple files to tempdbAfter completing Describe the SQL Server Platform. Checks

this module, students will be able to: Work with SQL Server Tools. Configure Server MemoryAfter completing Describe the role and structure of SQL Configure SQL Server Services. this module, students will be able to: Server databases. Planning Server Resource Requirements Prepare to install SQL Server. Work with files and filegroups. Pre-installation Testing for SQL Install SQL Server.

Move database files within servers and ServerLab : Preparing Systems for SQL Upgrade and automate the installation of

between servers. Server SQL Server.

Understanding SQL Server Transaction Adjust memory configuration Working with Files and Filegroups Logging Pre-installation Stress Testing Moving Database FilesLab : Working with Planning a SQL Server Backup StrategyLab Check Specific I/O OperationsAfter Databases

: Understanding SQL Server Recovery completing this module, students will be Adjust tempdb configuration

Models able to: Create the RateTracking database

Plan a backup strategy Describe the SQL Server architecture. Attach the OldProspects database Configure Recovery Models Plan for server resource requirements. Add multiple files to tempdbAfter Review recovery models and strategyAfter Conduct pre-installation stress testing for completing this module, students will be completing this module, students will be able SQL Server. able to:

to: Installing SQL Server Describe the role and structure of SQL Describe the critical concepts surrounding Upgrading and Automating Server databases.

(12)

Explain the transaction logging capabilities Configuring SQL Server Move database files within servers and within the SQL Server database engine. Review installation requirements between servers.

Plan a SQL Server backup strategy. Install the SQL Server instance Understanding SQL Server Transaction Managing Database Backups Perform Post-installation Setup and Logging

Working with Backup OptionsLab : Backup Checks Planning a SQL Server Backup

of SQL Server Databases Configure Server MemoryAfter StrategyLab : Understanding SQL Server Investigate backup compression completing this module, students will be Recovery Models

Transaction log backup able to: Plan a backup strategy Differential backup Prepare to install SQL Server. Configure Recovery Models

Copy-only backup Install SQL Server. Review recovery models and strategyAfter Partial backupAfter completing this module, Upgrade and automate the installation of completing this module, students will be students will be able to: SQL Server. able to:

Back up databases and transaction logs. Working with Files and Filegroups Describe the critical concepts surrounding Manage database backups. Moving Database FilesLab : Working with backup strategies.

Work with more advanced backup options. Databases Explain the transaction logging capabilities Restoring Databases Adjust tempdb configuration within the SQL Server database engine. Working with Point-in-time recovery Create the RateTracking database Plan a SQL Server backup strategy. Restoring System Databases and Individual Attach the OldProspects database Managing Database Backups

FilesLab : Restoring SQL Server 2012 Add multiple files to tempdbAfter Working with Backup OptionsLab : Backup Databases completing this module, students will be of SQL Server Databases

Determine a restore strategy able to: Investigate backup compression Restore the database Describe the role and structure of SQL Transaction log backup Using STANDBY modeAfter completing this Server databases. Differential backup module, students will be able to: Work with files and filegroups. Copy-only backup

Understand the restore process. Move database files within servers and Partial backupAfter completing this Restore databases. between servers. module, students will be able to: Work with Point-in-time Recovery. Understanding SQL Server Transaction Back up databases and transaction logs. Restore system databases and individual Logging Manage database backups.

files. Planning a SQL Server Backup Work with more advanced backup options. Importing ; Exporting Table Data StrategyLab : Understanding SQL Server Restoring Databases

Inserting Data in BulkLab : Importing and Recovery Models Working with Point-in-time recovery Exporting Data Plan a backup strategy Restoring System Databases and Import the Excel spreadsheet Configure Recovery Models Individual FilesLab : Restoring SQL Server Import the CSV file Review recovery models and 2012 Databases

Create and test an extraction package strategyAfter completing this module, Determine a restore strategy Compare loading performanceAfter students will be able to: Restore the database

completing this module, students will be able Describe the critical concepts Using STANDBY modeAfter completing to: surrounding backup strategies. this module, students will be able to: Transfer data to and from SQL Server. Explain the transaction logging Understand the restore process. Import and export table data. capabilities within the SQL Server Restore databases.

Insert data in bulk and optimize the bulk database engine. Work with Point-in-time Recovery. insert process. Plan a SQL Server backup strategy. Restore system databases and individual Authorizing Logins to Access Databases Managing Database Backups files.

Authorization Across ServersLab : Working with Backup OptionsLab : Importing ; Exporting Table Data Authenticating and Authorizing Users Backup of SQL Server Databases Inserting Data in BulkLab : Importing and Create Logins Investigate backup compression Exporting Data

Correct an Application Login Issue Transaction log backup Import the Excel spreadsheet Create Database Users Differential backup Import the CSV file

Correct Access to RestoredAfter completing Copy-only backup Create and test an extraction package this module, students will be able to: Partial backupAfter completing this Compare loading performanceAfter Describe how SQL Server authenticates module, students will be able to: completing this module, students will be connections. Back up databases and transaction logs. able to:

Describe how logins are authorized to Manage database backups. Transfer data to and from SQL Server. access databases. Work with more advanced backup Import and export table data.

Explain the requirements for authorization options. Insert data in bulk and optimize the bulk across servers. Restoring Databases insert process.

Working with Fixed Database Roles Working with Point-in-time recovery Authorizing Logins to Access Databases Creating User-defined Database RolesLab : Restoring System Databases and Authorization Across ServersLab : Assigning Server and Database Roles Individual FilesLab : Restoring SQL Authenticating and Authorizing Users Assign Server Roles Server 2012 Databases Create Logins

Assign Fixed Database Roles Determine a restore strategy Correct an Application Login Issue Create and Assign User-defined Database Restore the database Create Database Users

Roles Using STANDBY modeAfter completing Correct Access to RestoredAfter Check Role AssignmentsAfter completing this module, students will be able to: completing this module, students will be this module, students will be able to: Understand the restore process. able to:

(13)

Work with fixed database roles. Work with Point-in-time Recovery. connections.

Create user-defined database roles. Restore system databases and individual Describe how logins are authorized to Authorizing Users to Execute Code files. access databases.

Configuring Permissions at the Schema Importing ; Exporting Table Data Explain the requirements for authorization LevelLab : Authorizing Users to Access Inserting Data in BulkLab : Importing and across servers.

Resources Exporting Data Working with Fixed Database Roles Assign Schema-level Permissions Import the Excel spreadsheet Creating User-defined Database RolesLab Assign Object-level Permissions Import the CSV file : Assigning Server and Database Roles Test PermissionsAfter completing this Create and test an extraction package Assign Server Roles

module, students will be able to: Compare loading performanceAfter Assign Fixed Database Roles

Authorize user access to objects. completing this module, students will be Create and Assign User-defined Database Authorize users to execute code. able to: Roles

Configure permissions at the schema level. Transfer data to and from SQL Server. Check Role AssignmentsAfter completing Implementing SQL Server Audit Import and export table data. this module, students will be able to: Managing SQL Server AuditLab : Auditing Insert data in bulk and optimize the bulk Work with server roles.

SQL Server Environments insert process. Work with fixed database roles. Determine audit configuration and create Authorizing Logins to Access Databases Create user-defined database roles. audit Authorization Across ServersLab : Authorizing Users to Execute Code Create server audit specifications Authenticating and Authorizing Users Configuring Permissions at the Schema Create database audit specifications Create Logins LevelLab : Authorizing Users to Access Test audit functionalityAfter completing this Correct an Application Login Issue Resources

module, students will be able to: Create Database Users Assign Schema-level Permissions Describe the options for auditing data Correct Access to RestoredAfter Assign Object-level Permissions access in SQL Server. completing this module, students will be Test PermissionsAfter completing this Implement SQL Server Audit. able to: module, students will be able to: Manage SQL Server Audit. Describe how SQL Server authenticates Authorize user access to objects. Working with SQL Server Agent connections. Authorize users to execute code. Managing SQL Server Agent JobsLab : Describe how logins are authorized to Configure permissions at the schema Automating SQL Server Management access databases. level.

Create a Data Extraction Job Explain the requirements for authorization Implementing SQL Server Audit

Schedule the Data Extraction Job across servers. Managing SQL Server AuditLab : Auditing Troubleshoot a Failing JobAfter completing Working with Fixed Database Roles SQL Server Environments

this module, students will be able to: Creating User-defined Database Determine audit configuration and create Automate SQL Server Management. RolesLab : Assigning Server and audit

Work with SQL Server Agent. Database Roles Create server audit specifications Manage SQL Server Agent jobs. Assign Server Roles Create database audit specifications Configuring Credentials Assign Fixed Database Roles Test audit functionalityAfter completing this Configuring Proxy AccountsLab : Create and Assign User-defined module, students will be able to:

Configuring Security for SQL Server Agent Database Roles Describe the options for auditing data Troubleshoot job execution failure Check Role AssignmentsAfter completing access in SQL Server.

Resolve the security issue this module, students will be able to: Implement SQL Server Audit. Perform further troubleshootingAfter Work with server roles. Manage SQL Server Audit. completing this module, students will be able Work with fixed database roles. Working with SQL Server Agent to: Create user-defined database roles. Managing SQL Server Agent JobsLab : Explain SQL Server Agent security. Authorizing Users to Execute Code Automating SQL Server Management Configure credentials. Configuring Permissions at the Schema Create a Data Extraction Job Configure Proxy accounts. LevelLab : Authorizing Users to Access Schedule the Data Extraction Job Monitoring SQL Server Errors Resources Troubleshoot a Failing JobAfter completing Configuring Operators, Alerts and Assign Schema-level Permissions this module, students will be able to: NotificationsLab : Monitoring SQL Agent Assign Object-level Permissions Automate SQL Server Management. Jobs with Alerts and Notifications Test PermissionsAfter completing this Work with SQL Server Agent. Configure Database Mail module, students will be able to: Manage SQL Server Agent jobs. Implement Notifications Authorize user access to objects. Configuring Credentials Implement AlertsAfter completing this Authorize users to execute code. Configuring Proxy AccountsLab :

module, students will be able to: Configure permissions at the schema Configuring Security for SQL Server Agent Configure database mail. level. Troubleshoot job execution failure Monitor SQL Server errors. Implementing SQL Server Audit Resolve the security issue

Configure operators, alerts and notifications. Managing SQL Server AuditLab : Perform further troubleshootingAfter Maintaining Indexes Auditing SQL Server Environments completing this module, students will be Automating Routine Database Determine audit configuration and create able to:

MaintenanceLab : Performing Ongoing audit Explain SQL Server Agent security. Database Maintenance Create server audit specifications Configure credentials.

Check database integrity using DBCC Create database audit specifications Configure Proxy accounts. CHECKDB Test audit functionalityAfter completing Monitoring SQL Server Errors Correct index fragmentation this module, students will be able to: Configuring Operators, Alerts and Create a database maintenance plan Describe the options for auditing data NotificationsLab : Monitoring SQL Agent

(14)

Investigate table lock performanceAfter access in SQL Server. Jobs with Alerts and Notifications completing this module, students will be able Implement SQL Server Audit. Configure Database Mail to: Manage SQL Server Audit. Implement Notifications

Ensure database integrity. Working with SQL Server Agent Implement AlertsAfter completing this Maintain indexes. Managing SQL Server Agent JobsLab : module, students will be able to: Automate routine database maintenance. Automating SQL Server Management Configure database mail. Improving Performance with the Database Create a Data Extraction Job Monitor SQL Server errors. Engine Tuning Advisor Schedule the Data Extraction Job Configure operators, alerts and Working with Tracing OptionsLab : Tracing Troubleshoot a Failing JobAfter notifications.

Access to SQL Server 2012 completing this module, students will be Maintaining Indexes

Capture a trace using SQL Server Profiler able to: Automating Routine Database Analyze a trace using Database Engine Automate SQL Server Management. MaintenanceLab : Performing Ongoing Tuning Advisor Work with SQL Server Agent. Database Maintenance

Configure SQL TraceAfter completing this Manage SQL Server Agent jobs. Check database integrity using DBCC module, students will be able to: Configuring Credentials CHECKDB

Capture activity using SQL Server Profiler Configuring Proxy AccountsLab : Correct index fragmentation and Extended Events Profiler. Configuring Security for SQL Server Create a database maintenance plan Improve performance with the Database Agent Investigate table lock performanceAfter Engine Tuning Advisor. Troubleshoot job execution failure completing this module, students will be Work with tracing options. Resolve the security issue able to:

Capturing and Managing Performance Data Perform further troubleshootingAfter Ensure database integrity. Analyzing Collected Performance DataLab : completing this module, students will be Maintain indexes.

Monitoring SQL Server 2012 able to: Automate routine database maintenance. Investigating DMVs Explain SQL Server Agent security. Improving Performance with the Database Configure Management Data Warehouse Configure credentials. Engine Tuning Advisor

Configure Instances for Data Collection Configure Proxy accounts. Working with Tracing OptionsLab : Tracing Work with Data Collector ReportsAfter Monitoring SQL Server Errors Access to SQL Server 2012

completing this module, students will be able Configuring Operators, Alerts and Capture a trace using SQL Server Profiler to: NotificationsLab : Monitoring SQL Agent Analyze a trace using Database Engine Monitor current activity. Jobs with Alerts and Notifications Tuning Advisor

Capture and manage performance data. Configure Database Mail Configure SQL TraceAfter completing this Analyze collected performance data. Implement Notifications module, students will be able to:

Virtualizing SQL Server Implement AlertsAfter completing this Capture activity using SQL Server Profiler Deploying and Upgrading Data-Tier module, students will be able to: and Extended Events Profiler.

ApplicationsLab : Managing Multiple Servers Configure database mail. Improve performance with the Database Configure CMS and execute multi-server Monitor SQL Server errors. Engine Tuning Advisor.

queries Configure operators, alerts and Work with tracing options.

Deploy a data-tier application notifications. Capturing and Managing Performance Register and extract a data-tier application Maintaining Indexes Data

Upgrade a data-tier applicationAfter Automating Routine Database Analyzing Collected Performance DataLab completing this module, students will be able MaintenanceLab : Performing Ongoing : Monitoring SQL Server 2012

to: Database Maintenance Investigating DMVs

Work with multiple servers. Check database integrity using DBCC Configure Management Data Warehouse Describe options for virtualizing SQL Server. CHECKDB Configure Instances for Data Collection Deploy and upgrade Data-Tier Applications. Correct index fragmentation Work with Data Collector ReportsAfter Resolving Service-related Issues Create a database maintenance plan completing this module, students will be Resolving Concurrency Issues Investigate table lock performanceAfter able to:

Resolving Login and Connectivity IssuesLab completing this module, students will be Monitor current activity.

: Troubleshooting Common Issues able to: Capture and manage performance data. Troubleshoot and resolve SQL Server Ensure database integrity. Analyze collected performance data. administrative issuesAfter completing this Maintain indexes. Virtualizing SQL Server

module, students will be able to: Automate routine database maintenance. Deploying and Upgrading Data-Tier Explain SQL Server troubleshooting Improving Performance with the ApplicationsLab : Managing Multiple methodology. Database Engine Tuning Advisor Servers

Resolve service-related issues. Working with Tracing OptionsLab : Configure CMS and execute multi-server Resolve concurrency issues. Tracing Access to SQL Server 2012 queries

Resolve login and connectivity issues Capture a trace using SQL Server Profiler Deploy a data-tier application

Analyze a trace using Database Engine Register and extract a data-tier application Tuning Advisor Upgrade a data-tier applicationAfter

line Configure SQL TraceAfter completing completing this module, students will be Working with SQL Server Tools this module, students will be able to: able to:

Configuring SQL Server ServicesLab : Capture activity using SQL Server Profiler Work with multiple servers. Introduction to SQL Server and its Toolset and Extended Events Profiler. Describe options for virtualizing SQL Verifying SQL Server Component Improve performance with the Database Server.

Installation Engine Tuning Advisor. Deploy and upgrade Data-Tier Altering Service Accounts for New Instance Work with tracing options. Applications.

Figure

Updating...