Get documents about "Instructor Guide (DOC)
Shared by: jizhen1947
-
Stats
- views:
- 33
- posted:
- 9/18/2011
- language:
- English
- pages:
- 14
Document Sample
Instructor Guide for GK 6182 -
Implementing and Maintaining SQL Server
2008 (MOC 6231A and 6232A custom)
Course Director Brian D. Egler
Brian Egler Consulting LLC
4908 Linksland Drive
Holly Springs, NC, 27540
Business Phone #: 919-244-0831
Home Phone #: 919-244-0831
Fax #: 919-573-0783
E-Mail Address: begler@nc.rr.com
Mobile Number: 919-244-0831
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 1
Global Knowledge, Inc.
Course Version
Course Title: Instructor Guide for GK 6182 - Implementing and
Maintaining SQL Server 2008
Current Manual Revision: 6182-001
Release Date: 02/2009
Lab Guide: (integrated with student materials above)
Current Lab Revision: None
Release Date: None
Current Slides File: GK-6182-PPT.zip
ftp://instructor.globalknowledge.com/
instructors\Microsoft_Product_Line\GK_6182_Implementing &
Maintaining SQL Server 2008
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 2
Global Knowledge, Inc.
Course Information
Course Director Biography
Brian D. Egler
MCITP/MCSE/MCT 2009 - Microsoft Technology Specialist
Technology experience includes:
- 10+ years with SQL Server
- 10+ years in Data Modeling and Database Design
- 10+ years Application Development including IMS, DB2, Sybase
Professional experience includes:
- 16+ years as Technical Instructor
- 6+ years as Systems Consultant
- 5+ years with web-based curriculum development
begler@nc.rr.com
General Information
Course Description
This five-day instructor-led course provides you with the knowledge and skills to implement and
maintain a Microsoft SQL Server 2008 database. You will learn to use SQL Server 2008 product
features and tools related to implementing and maintaining a database.
This course incorporates materials from the Official Microsoft Learning Products:
• 6231: Maintaining a Microsoft SQL Server 2008 Database
• 6232: Implementing a Microsoft SQL Server 2008 Database
What You'll Learn
• Install and configure SQL Server 2008
• Manage database files
• Back up and restore databases
• Manage security
• Replicate data between SQL Server instances
• Maintain high availability
Monitor SQL Server
• Create data types and tables
• Implement data integrity in Microsoft SQL Server 2008 databases by using constraints
• Implement data integrity in Microsoft SQL Server 2008 by using triggers
• Implement views
• Implement stored procedures
Who Needs to Attend
• IT professionals who administer and maintain SQL Server databases
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 3
Global Knowledge, Inc.
Prerequisites
• 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
Course Outline
1. Installing and Configuring SQL Server (module 1 6231A)
Preparing to Install SQL Server
Installing SQL Server
Configuring a SQL Server Installation
2. Managing Databases and Files (module 2 6231A)
Planning Databases
Creating Databases
Using Policy-Based Management
3. Disaster Recovery (module 3 6231A)
Planning a Backup Strategy
Backing Up User Databases
Restoring User Databases
Performing Online Restore Operations
Recovering Data from Database Snapshots
System Databases and Disaster Recovery
4. Managing Security (module 4 6231A)
Overview of SQL Server Security
Protecting the Server Scope
Protecting the Database Scope
Protecting the Server Scope
Auditing Security
5. Implementing Replication (module 7 6231A)
Overview of Replication
Managing Publications and Subscriptions
Configuring Replication in Some Common Scenarios
6. Maintaining High Availability (module 8 6231A)
Introduction to High Availability
Implementing Log Shipping
Implementing Database Mirroring
Implementing Server Clustering
Using Distributed High Availability Solutions
7. Monitoring SQL Server (module 9 6231A)
Viewing Current Activity
Using SQL Server Profiler
Monitoring with DDL Triggers
Using Event Notifications
8. Troubleshooting and Performance Tuning (module 10 6231A)
Troubleshooting SQL Server
Performance Tuning in SQL Server
Using Resource Governor
Using Data Collector
9. Creating Data Types and Tables (module 2 6232A)
Creating Data Types
Creating Tables
Creating Partitioned Tables
10. Implementing Data Integrity by Using Constraints and Triggers (module 4 6232A)
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 4
Global Knowledge, Inc.
Data Integrity Overview
Implementing Constraints
Implementing Triggers
11. Implementing Views (module 6 6232A)
Introduction to Views
Creating and Managing Views
Optimizing Performance by Using Views
12. Implementing Stored Procedures (module 7 6232A)
Implementing Stored Procedures
Creating Parameterized Stored Procedures
Working With Execution Plans
Handling Errors
Labs:
Lab 1: Installing and Configuring SQL Server
• Installing SQL Server
• Configuring SQL Server
Lab 2: Managing Databases and Files
• Creating a Database
• Monitoring and Managing Filegroup Usage
• Creating a Policy
Lab 3: Disaster Recovery
• Designing a Backup Strategy
• Implementing a Backup Strategy
• Restoring and Recovering a Database
• Performing Piecemeal Backup and Restore Operations
• Restoring the master Database
Lab 4: Managing Security
• Creating Logins and Assigning Server-Scope Permissions
• Creating and Managing Users
• Using a Certificate to Protect Data
• Implementing SQL Server Audit
Lab 5: Implementing Replication
• Implementing Snapshot Replication
• Implementing Peer-to-Peer Transactional Replication
• Implementing HTTP Merge Replication
Lab 6: Maintaining High Availability
• Configuring Log Shipping
• Configuring Database Mirroring
• Implementing SQL Server Clustering
Lab 7: Monitoring SQL Server
• Monitoring SQL Server Activity
• Tracing SQL Server Activity
• Using DDL Triggers
• Using Event Notifications
Lab 8: Troubleshooting and Performance Tuning
• Troubleshooting Connectivity Problems
• Troubleshooting Concurrency Problems
• Using the Database Engine Tuning Advisor
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 5
Global Knowledge, Inc.
• Implementing Resource Governor
• Implementing Data Collector
Lab 9: Creating Data Types and Tables
• Creating Data Types
• Using New Date and Time Data Types
• Creating Tables
• Creating Partitioned Tables
Lab 10: Implementing Data Integrity by Using Constraints and Triggers
• Creating Constraints
• Disabling Constraints
• Creating Triggers
Lab 11: Implementing Views
• Creating Views
• Creating Indexed Views
• Creating Partitioned Views
Lab 12: Implementing Stored Procedures
• Creating Stored Procedures
• Working with Execution Plans
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 6
Global Knowledge, Inc.
Philosophy
This course allows a student to take a single 5-day class for Implementing and Maintaining a
Microsoft SQL Server 2008 Database instead of taking the full 10-day official curriculum courses.
The sections selected are from the official curriculum courses 6231A Maintaining a Microsoft
SQL Server 2008 Database and 6232A Implementing a Microsoft SQL Server 2008 Database. The
sections carefully selected are those that contain material that show many of the new features of
SQL Server 2008 while re-establishing pre-existing features but using the new 2008 version of the
management tools. In this way, a student may have experience with SQL Server 2005 but still get
to know the new features and tools of 2008. Also, a student who has used SQL Server 2000 could
also learn some of the new features of both SQL Server 2005 and 2008 while using the new tools.
The student guide has been customized to include only the sections selected (see outline above)
including the associated lab material. The customization is done on a section by section basis. No
customization has taken place within each section. Therefore, if an instructor is familiar with the
MOC courses 6231A and 6232A, they will be completely comfortable with the material as only the
section numbers may have changed. The customized classroom image contains all the Virtual
machines delivered for both courses so potentially an instructor could demonstrate a feature not
included in this customized course.
Preparation for This Course
Setup
GK 6182 Custom Image should be loaded onto the student machines. There is no difference between the
instructor and student images (i.e. single image). A shortcut to the custom slides is on the desktop (minor
changes like Section numbers, explicit VM names etc).
Books and Handouts
Course Materials:
GK 6182 Custom Student Guide. Content extracted from MOC courses 6231A and 6232A.
Instructors should have a copy of the Student Guide in order to prepare for the class. Lab instructions and
Lab Answer Keys for the selected sections are included in the student guide.
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 7
Global Knowledge, Inc.
Timetable
Day 1:
Start End Topic
8:30 9:30 Introduction – Section 0
9:30 9:45 BREAK
9:45 12:00 Lecture, Lab, Review – Section 1 Installing and Configuring SQL Server 2008
(Break as needed)
12:00 1:00 LUNCH
1:00 4:00 Lecture, Lab, Review – Section 2 Managing Databases and Files
(Break as needed)
4:00 4:30 Review
4:30 End of Day
Day 2:
Start End Topic
8:30 9:00 Review
9:00 12:00 Lecture, Lab, Review – Section 3 Disaster Recovery (Break as needed)
12:00 1:00 LUNCH
1:00 4:00 Lecture, Lab, Review – Section 4 Managing Security (Break as needed)
4:00 4:30 Review
4:30 End of Day
Day 3:
Start End Topic
8:30 9:00 Review
9:00 12:00 Lecture, Lab, Review – Section 5 Implementing Replication (Break as needed)
12:00 1:00 LUNCH
1:00 4:00 Lecture, Lab, Review – Section 6 Maintaining High Availability
(Break as needed)
4:00 4:30 Review
4:30 End of Day
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 8
Global Knowledge, Inc.
Day 4:
Start End Topic
8:30 9:00 Review
9:00 11:00 Lecture, Lab, Review – Section 7 Monitoring SQL Server (Break as needed)
11:00 12:00 Lecture – Section 8 Troubleshooting and Performance Tuning (Break as needed)
12:00 1:00 LUNCH
1:00 2:00 Lab, Review – Section 8 Troubleshooting and Performance Tuning (Break as
needed)
2:00 4:00 Lecture, Lab, Review – Section 9 Creating Data Types and Tables
(Break as needed)
4:00 4:30 Review
4:30 End of Day
Day 5:
Start End Topic
8:30 9:00 Review
9:00 11:00 Lecture, Lab, Review – Section 10 Implementing Data Integrity by Using
Constraints and Triggers (Break as needed)
11:00 12:00 Lecture – Section 11 Implementing Views (Break as needed)
12:00 1:00 LUNCH
1:00 2:00 Lab, Review – Section 11 Implementing Views (Break as needed)
2:00 4:00 Lecture, Lab, Review – Section 12 Implementing Stored Procedures
(Break as needed)
4:00 4:30 Review
4:30 End of Class
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 9
Global Knowledge, Inc.
Appendix - Errata
13. Hardware issues
General performance problems on the Thinkpad T61s with Virtual Server 2005:
Check this in BIOS on the T61’s
Make sure the settings are as follows:
Config
Power
Speedstep OFF
CPU Power Mgt OFF
PCI Power Mgt OFF
CPU
Core Enabled
Intel VT Enabled
14. Course Issues
Note: According to the agreement with Microsoft Learning, GK is not allowed to change the actual
course content including the labs so all issues in the GK 6182 Combo class are exactly those you
will find with the original MOC classes.
You can also refer to the MCT SQL Newsgroups for more details on the latest issues.
It is always very helpful to go through each of the labs before the students so you can warn them of
these "gotchas" ahead of time.
We always welcome feedback from the instructors to update this list.
MOC Course 6231A:
Throughout all Labs:
1. Click Continue if prompted. (Windows 2008 Server security warnings)
2. Script Action | To Clipboard feature in SSMS produces an error but does
succeed in copying the code to the clipboard. Ignore error and continue.
Module 1: Installing and Configuring SQL Server 2008: (GK 6182 Module 1)
Lab Answer Key:
Ex 1: Task1: Step 32: Requirements from the Lab instructions (page 35 in Module 1) refer to Full
Text Search so this check box should be checked during Feature Selection.
Module 2: Managing Databases and Files: (GK 6182 Module 2)
Lab Answer Key:
Ex 1: Task 2: Step 3: AW_Marketing_Data1.ndf should have extension .MDF
Add extra credit at end of lab: Test out policies created by creating a Stored Procedure with the
correct naming conventions and then trying to create one with incorrect name.
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 10
Global Knowledge, Inc.
Module 3: Disaster Recovery: (GK 6182 Module 3)
Lab Answer Key:
Ex 1: Task 1: Step 3: SQL Server Configuration Manager is under Configuration Tools.
Ex 2: Task 1: Step 5: Make sure you press Enter to commit the change.
Ex3: Task 5: Step 5 - There is a typo in the syntax. Remove the period after .bak
Module 4: Managing Security: (GK 6182 Module 4)
Lab Answer Key:
Ex 1: Task 1: Steps 2-10 are not mentioned in the Lab instructions on Page 42 in Module 4. These
steps run a script to drop a Foreign Key on the Person.Person table enabling an INSERT statement
in Ex 4: Task 4 to run successfully.
Ex 1: Task 2: Step 1: not necessary to Run as administrator.
Ex 1: Task 22: Step 3: System Tool should be System Tools
Ex 2: Task 1: Step 6: Login name must be typed in as NY-SQL-01\Holly since Holly does not
have a login in her own right. She uses the NY-SQL-01\HREmployees group login.
Ex 2: Task 1: Step 8: Login name must be typed in as NY-SQL-01\Anders since Anders does not
have a login in his own right. He uses the NY-SQL-01\ITEmployees group login.
Ex 2: Task 1: Step 10: Failure: User HRApp was already created Ex 1: Task 3: Step 34
Module 7: Implementing Replication: (GK 6182 Module 5)
General: Most errors with SQL Server Replication are down to security permissions either on the
Snapshot shared folder or with the SQL Logins accessing the other servers.
Lab Answer Key:
Ex 1: Task 1: Step 15: Windows folder and subfolder needs to be created first: Mod07\LabFiles
Ex 1: Task 3: Step 6: Wait 3 minutes (not one)
Ex 2: Task 2: Step 9: Will be prompted for a new remote Publisher password: Enter Pa$$w0rd
Ex 2: Task 5: Step 6: Windows folder needs to be created first: C:\Backup
Ex 2: Task 6: title should be: Restore the AdventureWorks2008 database to the PEER server
Ex 2: Task 6: Step 1: C:\Backup needed to be created earlier
Ex 2: Task 6: Step 5: last sentence is not true: The AdventureWorks2008 database is NOT in the
restoring state.
Ex 2: Task 7: Step 1: Look under Publications folder
Ex 2: Task 7: Step 18: Extra credit: make changes on the peer databases to see the replication. Use
Replication Monitor to Insert a Tracer to test latency.
Ex 3: Task 2: Step 14: Need to restart SSMS with “Run As Administrator” privilege. (right-click
option). Otherwise you will get a permissions error on Step 18 when Virtual Directories need to be
created in IIS via Configure Web Synchronization wizard. (If prompted to “Import settings” press
Cancel). SSMS will run under administrator rights even though it may connect to SQL Server as
Student. (In Windows Server 2008 there is a difference between the administrator and a Windows
user who is a member of the Administrators group).
Ex 3: Task 3: Step 39: Click Student (i.e. logon as Student not Administrator). In SQL Server
2008, the Administrators Windows group is not automatically given membership of the
SYSADMIN role. During install only NY-SQL-01\Student was given SYSADMIN membership.
Ex 3: Task 3: Step 43: After this step make sure SQLSERVER login on NY-SQL-01 is a member
of the SYSADMIN role. This needs to be done otherwise error during synchronization in step 47.
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 11
Global Knowledge, Inc.
Module 8: High Availability: (GK 6182 Module 6)
General: When instructions say “These tasks should be performed on HOU-SQL-02”, you can use
SSMS on HOU-SQL-01 to connect to HOU-SQL-02 and follow the steps that way. You do not
have to switch VMs although that will work too.
Error in lab instructions: Exercise 1 Task 2 should be:
EXEC sp_configure filestream_access_level,2
RECONFIGURE
(Lab Answer Key does not include this step. Not necessary as it is already set anyway)
Error in lab instructions: Exercise 2 Task 3 first bullet should be:
EXEC sp_configure filestream_access_level,2
RECONFIGURE
(Lab Answer Key does include this step. Not necessary as it is already set anyway)
Lab Answer Key:
Ex 1: Task 3: Step 32: Wait at least 2 minutes.
Ex 2: Task 3: Step 4: should be:
EXEC sp_configure filestream_access_level,2
RECONFIGURE
(HOU-SQL-02 is already set so this is technically not necessary at all but will do no harm if re-
applied)
Ex 2: Task 4 Step 13: Start Mirroring gives error on port 5022.
Solution: Ex 2: Task 4: Step 11 (Service Account page) you will need to enter
adventureworks\sqlserver explicitly for Principal, Mirror and Witness. This will force that
account to be added as a login and the connect permission assigned.
(No changes are needed to Windows Firewall settings)
Ex 2: Task 4: Step 16: Label says just “Restoring…” for Mirror database (SQL 2008 RTM bug)
Ex 2: Task 5: Step 8: Label says just “Restoring…” for Mirror database (SQL 2008 RTM bug)
Ex 2: Task 5: Step 10: Take Offline will fail due to Mirror partnership (as required)
Extra Credit: Use Database Properties/Mirroring page to invoke a manual failover using the
Failover button. (Automatic failover has already been tested).
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 12
Global Knowledge, Inc.
Module 9: Monitoring SQL Server: (GK 6182 Module 7)
Lab Answer Key:
Ex 1: Task 2: Step 3 occurs before Step 2
Ex 3: Task 1: Step 6: Tip: Use files in E:\Mod09\democode folder to use as basis to save typing
errors.
Ex 3: Task 2: Step 2: First 8 lines are the code, the rest should not be shaded.
Ex 4: Task 1: Step 2: Tip: Use files in E:\Mod09\democode folder to use as basis to save typing
errors.
Module 10: Troubleshooting and Performance Tuning: (GK 6182 Module 8)
General: Use Lab Answer Key at back of book as the Lab instructions are flawed and will only
cause frustration.
Lab Answer Key:
Ex 4: Task 1: Start on HOU-SQL-03
Ex 4: Task 4: Step 3: Must left-click Performance Monitor then right-click (otherwise Data
Collector Set is grayed out)
Ex 4: Task 6: Step 5: Windows folder needs to be created first on HOU-SQL-03: C:\Backup
Ex 4: Task 7: After Step 4: Perform steps 8-9 here instead of later (Find) otherwise HOU-SQL-03
hangs (for some reason, maybe lack of memory).
Ex 5: Task 2: After Step 5: Missing step: Choose “Create or Upgrade” option and click Next.
Ex 5: Task 2: Step 9: select NY-SQL-01\sqlserver (not BUILTIN\sqlserver)
Ex 5: Task 3: After Step 4: Extra credit: Run e:\Mod10\LabFiles\Starter\RunQuery1.cmd to create
some query activity on the server then wait 15 minutes and run the other Management
DataWarehouse reports (Query Statistics and Server Activity). If you cannot wait 15 minutes you
can also right-click on each of the System Data Collection Sets and choose “Collect and Upload”.
Then refresh the report. Make sure you are running the report for the correct time period. You can
narrow down a report down to a 15 minute period (default is 4 hours).
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 13
Global Knowledge, Inc.
MOC Course 6232A:
Throughout all Labs:
1. Click Continue if prompted. (Windows 2008 Server security warnings)
2. Script Action | To Clipboard feature in SSMS produces an error but does
succeed in copying the code to the clipboard. Ignore error and continue.
Module 2: Creating Data Types and Tables: (GK 6182 Module 9)
Use 6232A-NY-SQL-01 and login as Student
Lab Answer Key:
Ex 1: Task 1: Step 6: Make sure you use AdventureWorksDW2008...instructions are accurate but
it is easy to use AdventureWorks2008 by mistake.
Ex 4: Task 2 step 2: use e:\mod02\democode\createpartitionedtable.sql to save typing
Module 4: Implementing Data Integrity by Using Constraints: (GK 6182 Module 10)
Use 6232A-NY-SQL-01 and login as Student
Lab Answer Key:
Ex 1: Task 3: Step 3: use e:\mod04\democode\CreateConstraints.sql to save typing
Module 6: Implementing Views: (GK 6182 Module 11)
Use 6232A-NY-SQL-01 and login as Administrator
Lab Answer Key:
Ex 1: Task 2: Step 1: use e:\mod06\LabFiles\Solution\CreateEmployeeView.sql to save typing
Ex 3: Task 1 step 3: This script must be run as Administrator. It creates Linked Servers, a
Database and a Table on 3 instances. If you run it as Student the script will run but some steps will
fail silently and you will get errors later in the lab. If you are logged on as Student you will need to
open up a command window with “Run as administrator”. If you are logged on as Administrator
(as you should be from the beginning of this lab) you are OK.
Module 7: Implementing Stored Procedures: (GK 6182 Module 12)
Use 6232A-NY-SQL-01 and login as Student
Lab Answer Key:
Ex 1: Task 1: after step 5: After opening the project, go to the Solution Explorer and delete the
HOU-SQL-01 connection object since we are using NY-SQL-01. If you do not do this SSMS will
try to connect to HOU-SQL-01 every time you try to open a script and will wait a minute before
timing out. At that time you can choose NY-SQL-01 and everything will be OK but why waste the
time? With the connection object deleted it will default to the local server.
Ex 2: Task 4: Query does NOT pick up new index; change WHERE clause to WHERE name =
'blade' to force it to use new index...verify in Execution Plan.
Instructor Guide for GK 6182 - Implementing and Maintaining SQL Server 2008 14
Global Knowledge, Inc.
