DBM635 - Database Administration using SQL Server

Outline info
Semester
School
Last revision date 2017-05-29 00:34:33.686
Last review date 2017-07-17 00:15:35.569


Subject Title
Database Administration using SQL Server

Subject Description
This subject reviews the role of the Database Administrator using Microsoft's SQL Server in a Windows environment. The features, utilities and operations of a Database Management System(DBMS) are examined. Through hands on Database Administration, students will learn how the DBMS manages the data and controls such as recovery, backup, and security for a SQL Server database.

Credit Status
1 Credit for CPA/CPD Diploma Students

Learning Outcomes
Upon successful completion of this subject the student will be able to:

1. Describe in detail the functions of a Database Administrator

2. Demonstrate a working knowledge of the basic features and utilities of the DMS in use

3. Demonstrate and understand the architecture and functions of the DBM(s) in use

4. Construct and maintain security controls

5. Create backup and recovery procedures

6. Demonstrate an ability to load, import, export, and replicate data for a SQL Server database

7. Perform DBA scheduling tasks

Cheating and Plagiarism
Each student should be aware of the College's policy regarding Cheating and Plagiarism. Seneca's Academic Policy will be strictly enforced.

To support academic honesty at Seneca College, all work submitted by students may be reviewed for authenticity and originality, utilizing software tools and third party services. Please visit the Academic Honesty site on http://library.senecacollege.ca for further information regarding cheating and plagiarism policies and procedures.

Discrimination/Harassment
All students and employees have the right to study and work in an environment that is free from discrimination and/or harassment. Language or activities that defeat this objective violate the College Policy on Discrimination/Harassment and shall not be tolerated. Information and assistance are available from the Student Conduct Office at student.conduct@senecacollege.ca.

Accommodation for Students with Disabilities
The College will provide reasonable accommodation to students with disabilities in order to promote academic success. If you require accommodation, contact the Counselling and Disabilities Services Office at ext. 22900 to initiate the process for documenting, assessing and implementing your individual accommodation needs.

Prerequisite(s)
DBS301

Topic Outline
Overview of Database Management System                                                      30%

  • Functions and responsibilities of the Database Administrator
  • DBMS architectures (Distributed, Client/Server, Storage Area Network)
  • DBMS data (metadata and application data), features (logging, locking), utilities, tools
  • Identify and design backup and recovery requirements for an application
  • Design of Database application security and controls
  • Identify data replication requirements 

SQL Server using Windows                                                                               70%
  • Introduction to SQL Server
  • Review of available SQL Server versions
  • Create, delete and manage SQL Server objects using Enterprise Manager
  • Access application data
    • SQL Server's Query Analyzer,
    • Osql,
    • OLE-DB and ODBC

Importing and exporting data
  • DTS (Data Transformation Services)
  • bcp (bulk copy utility program)
  • T-SQL (Transact-SQL) commands
    • INSERT INTO
    • BULK INSERT

Task Automation
  • SQL Server Agent
    • messaging 
    • jobs 
    • operators 
    • alerts

Maintenance
  • Develop a maintenance plan using Maintenance Wizard and query maintenance history
  • Use DBCC (Database Consistency Check) commands to maintain databases

Backup and Recovery
  • Backup and restore databases

Performance Monitoring of Queries, indexes, logging and events
  • SQL Profiler
  • Query Analyzer
  • Performance Monitor

Security administration and monitoring
  • server logins and roles,
  • database users and roles
  • object and statement permissions

Data Replication
  • snapshot method
  • transactional method
  • merge method 

Mode of Instruction
Classroom, Lectures and discussion reinforced by exercises, Lab Sessions, and Independent Learning.  

It is the student's responsibility to save documents, articles and notes that the instructor has provided on BlackBoard or in class. Students will not be able to access BlackBoard as of the last day of the student’s class.


Prescribed Texts
Professional Microsoft SQL Server 2012 Administration
By: Adam Jorgensen, Steven Wort, Ross  Publisher: Wrox Press (Wiley)
ISBN: 978-1118106884

Reference Material

Promotion Policy

Grading Policy
A+ 90%  to  100%
A 80%  to  89%
B+ 75%  to  79%
B 70%  to  74%
C+ 65%  to  69%
C 60%  to  64%
D+ 55%  to  59%
D 50%  to  54%
F 0%    to  49% (Not a Pass)
OR
EXC Excellent
SAT Satisfactory
UNSAT Unsatisfactory

For further information, see a copy of the Academic Policy, available online (http://www.senecacollege.ca/academic-policy) or at Seneca's Registrar's Offices.


Modes of Evaluation
Since this is a professional credit subject, marking standards reinforce professional practice by demanding legible, tidy work. Written materials should be well organized and grammatically correct, with proper spelling and punctuation.

Assignments

  • Students must retain a duplicate of all assignments.
  • Computer assignments should be documented to the instructor's standards.
  • Assignments must be handed in on the scheduled due date. Late assignments are penalized.
  • For particulars, please obtain standards, dates, etc. from your instructor.

Absenteeism and Tests
  • Students should be aware that absenteeism will impact on their ability to achieve satisfactory grades.
  • If you miss a test, you must provide the reason in writing to the instructor prior to the next scheduled class. If your reason is accepted, you will be permitted to write a make-up test. Otherwise, you will be given a zero for the test. You must submit an original doctor’s certificate identifying the date, length of time of expected absence and the specific reason for your absence, or other appropriate documentation.
Term Work and Final Exam
  • Students must attain a combined grade of at least 50% on term work and the final exam. Students must pass the final exam in order to pass the subject. 
  • For further information on evaluation and academic standing, see a copy of the Academic Policy available at Seneca registration offices.

Grading is based on the following marking scheme:

Assignments (Minimum 2) 35%
Midterm test (Written and Practical) 35%
Final Exam (Written and Practical) 30%

Approved by: Denis Gravelle