Instructor Guide (DOC) by jizhen1947

VIEWS: 33 PAGES: 14

									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.

								
To top