Approved 12/2013
Credit:
3 semester credit hours (2 hours lecture, 4 hours lab)Prerequisite/Co-requisite:
NoneCourse Description
In-depth coverage of the knowledge and skills required to install, configure, administer, and troubleshoot the client-server database management system of Microsoft SQL Server databases.
Required Textbook and Materials
1. Microsoft SQL Server 2012 Administration: Real-World Skills for MCSA Certification and Beyond (Exams 70-461, 70-462, and 70-463) by Tom Carpenter, Wiley, 2013.
a. ISBN for print book is 978-0-118-48716-7 b. ISBN for E-book is 978-1-118-65490-3
2. Computer Networking and Troubleshooting Technology students are required to have one portable external Hard Drive with a capacity of 500GB or larger to be used for the duration of the time to complete their respective degree.
Course Objectives
Upon completion of this course, the student will be able to: 1. Describe SQL Server architecture.
2. Install SQL Server.
3. Manage Files and Databases. 4. Configure login security.
5. Plan and implement database permissions. 6. Implement a backup strategy.
Course Outline
A. Understanding SQL Server’s Role 1. What is Information Technology? 2. Introduction to Databases
3. Database Servers and Applications 4. SQL Server’s Role
B. Installing Microsoft SQL Server 2012 1. Installation Planning
2. Installing a Default Instance
3. Installing Named Instances 4. Installing to a Cluster 5. Installing Extra Features 6. Upgrading Previous Versions 7. Validating an Installation 8. Removing and Installation C. Working with the Administration Tools
2 2. SQL Server Management Studio
3. SQL Server Data Tools 4. SQL Server Profiler
5. Windows Server Administration for the DBA
D. SQL Server Command-Line Administration
1. Introducing the Command Prompt 2. General Commands
3. Batch Files
4. Mastering SQLCMD
5. Introducing Windows PowerShell 6. Using SQL Server PowerShell
Extensions E. Querying SQL Server
1. Understanding the SQL Language 2. SQL Statement Types
3. SQL Syntactical Elements 4. Coding Recommendations 5. Using SELECT Statements 6. Advanced Query Techniques 7. Using DDL Statements 8. Modifying Data
9. Tuning and optimizing Queries F. Database Concepts and Terminology
1. Relational Database Theory 2. Database Design Processes 3. Project Management for the
DBA
G. ERD and Capacity Planning 1. Planning a Database
2. Understanding Entity Relationship Diagramming
3. Building an ERD 4. Capacity Planning
H. Normalization and Other Design Issues 1. Designing for Normalization 2. Designing for Performance 3. Designing for Availability 4. Designing for Security I. Creating SQL Server Databases
1. SQL Server Databases 2. Database Storage
3. Database Options and Properties
4. Creating Databases in the GUI 5. Creating Databases with T-SQL 6. Creating Databases with PowerShell 7. Attaching and Detaching Databases 8. Database Snapshots
J. Creating Tables 1. Data Types 2. Collations
3. Table Creation Process 4. Data Partitioning K. Indexes and Views
1. Understanding Indexes 2. Creating Basic Indexes 3. Creating Advanced Indexes 4. Managing Indexes
5. Understanding Views 6. Creating Views
L. Triggers and Stored Procedures 1. Triggers Defined
2. Using Triggers 3. Creating Triggers
4. Understanding Stored Procedures 5. Creating Stored Procedures M. Implementing Advanced Features 1. Understanding and Installing
Analysis Services
2. Understanding Integration Services 3. Understanding and Installing
Reporting Services
4. Implementing Database Mail 5. Data Compression
N. Creating Jobs, Operators, and Alerts 1. Standardize, Automate, and Update 2. Understanding SQL Server Jobs 3. Creating and Using Operators 4. Creating and Using Alerts 5. Using WSUS for SQL Server
2012
O. Performance Monitoring and Tuning 1. Performance Tuning Principles 2. Performance and Troubleshooting
Tools
3 4. Performance Monitoring with
System Monitoring 5. Performance Studio
6. Advanced Monitoring Tools P. Backup and Restoration
1. Backing Up a Database
2. Backing Up System Databases 3. Restoring a Database
4. Backing Up the Environment
Grade Scale
90 – 100 A 80 – 89 B 70 – 79 C 60 – 69 D 0 – 59 FCourse Evaluation
Final grades will be calculated according to the following criteria:
1. Labs 30%
2. Study Guides 10%
3. Chapter Tests 30%
4. Final Exam 30%
Course Requirements
1. Demonstrate proficiency through hands-on labs as assigned. 2. Complete Study Guides or work sheets as assigned.
Course Policies
1. No food, drinks, or use of tobacco products in class.
2. Beepers, telephones, headphones, and any other electronic devices must be turned off while in class.
3. Do not bring children to class. 4. No late assignments will be accepted.
5. Certification. If a student passes the certification test that is associated with this class, you will receive an “A” on the final exam and credit for 25% of your labs. If you have missed a previous test, you must still take the final exam to substitute for that grade.
6. Attendance Policy. Three absences are allowed. If a student is tardy to class or departs early three (3) times, it will be equal to one (1) absence. Each absence beyond three absences will result in a 2 point deduction from your final grade. 7. If you wish to drop a course, the student is responsible for initiating and
completing the drop process. If you stop coming to class and fail to drop the course, you will earn an ‘F’ in the course.
4
8. Labs. Due dates will be announced by the instructor.
9. Tools. Return all tools and/or software to their designated place.
10. A grade of ‘C’ or better must be earned in this course for credit toward degree requirement.
11. Additional class policies as defined by the individual course instructor.
Disabilities Statement
The Americans with Disabilities Act of 1992 and Section 504 of the Rehabilitation Act of 1973 are federal anti-discrimination statutes that provide comprehensive civil rights for persons with disabilities. Among other things, these statutes require that all students with documented disabilities be guaranteed a learning environment that provides for reasonable accommodations for their disabilities. If you believe you have a disability requiring an accommodation, please contact the Special Populations Coordinator at (409) 880-1737 or visit the office in Student Services, Cecil Beeson Building.
Course Schedule
Week of Topic Reference
Week 1 Syllabus and policies
Chapter 1: Understanding SQL Server’s Role Chapter 2: Installing SQL Server 2012
pp. 3-44 pp. 45-82 Week 2 Chapter 3: Working with the Administration
Tools
Lab – Introduce Project
pp. 83-128
Week 3 Chapter 4: SQL Server Command-Line Administration
Lab – Project
pp. 129-170
Week 4 Chapter 5: Querying SQL Server Lab – Project
pp. 171-222 Week 5 Chapter 6: Database Concepts and
Terminology Lab – Project
pp. 225-248
Week 6 Chapter 7: ERD and Capacity Planning Lab – Project
pp. 249-278 Week 7 Chapter 8: Normalization and Other Design
Issues
Lab – Project
pp. 279-298
Week 8 Chapter 9: Creating SQL Server Databases Lab – Project
5
Week of Topic Reference
Week 9 Chapter 10: Creating Tables Lab – Project
pp. 337-362 Week 10 Chapter 11: Indexes and Views
Lab – Project
pp. 363-394 Week 11 Chapter 12: Triggers and Stored Procedure
Lab – Project
pp. 395-410 Week 12 Chapter 13: Implementing Advanced
Features Lab – Project
pp. 411-446
Week 13 Chapter 14: Creating Jobs, Operators, and Alerts
Lab – Project
pp. 449-490
Week 14 Chapter 15: Performance Monitoring and Tuning
Lab – Project
pp. 491-532
Week 15 Chapter 17: Backup and Restoration Lab – Project
pp. 569-596 Week 16 Submit Project
Final Exam
Contact Information:
Program Director: Lauri Arnold Program Director
Computer Networking and Troubleshooting Technology
Office: Office 103C, TA-4
Telephone: (409) 839-2050
E-mail: lauri.arnold@lit.edu
Additional Course Policies
Additional policies may be determined by individual course instructors. These policies will be indicated in the syllabus that is issued at the start of the course.
