SQL Server DBA

SQL Server DBA

About The Course


This course provides students with the knowledge and skills to capitalize on their skills and experience as a DBA to manage a Microsoft SQL Server system.

Intended Audience:

This course is intended for those who want to work as a SQL Server Database Administrator

Prerequisites:

Knowledge of Computer Basics like A+, Network Basics and Microsoft office.
Students are required to have at least High School Diploma or GED along with a good knowledge of computer.
To be eligible for the PMP® Exam, you need to fulfill the following certification criteria:

MODULE - 1: SQL & PL/SQL

SQL & PL/SQL - (12 hours)

  • Getting started with SQL
  • SQL language to create and manipulate SQL database objects.
  • Database creation and management
  • Stored Procedures
  • SQL QUERIES: UNIONS, JOINS, QUERIES, SUBQUERIES
  • Character String Functions: LPAD, ASCII, RPAD, CHR, LTRIM, CONCAT, RTRIM, INITCAP, TRIM, INSTR,REPLACE, LENGTH, SOUNDEX, LOWER, SUBSTR, UPPER, VSIZE
  • Aggregate/ Group functions: STDDEV, AVG, SUM, COUNT, VARIANCE, MAX, Group By, MEDIAN, Incorrect Usage, MIN
  • Date Timestamp Functions: ADD_MONTHS, ROUND, CURRENT DATE, SESSIONTIMEZONE, CURRENT TIMESTAMP, SYSDATE, DBTIMEZONE, SYSTIMESTAMP, EXTRACT, TO_CHAR, LAST_DAY, TO_DATE, LOCALTIMESTAMP, TO_TIMESTAMP, MONTHS_BETWEEN, NEW_TIME, TRUNC, NEXT_DAY
  • Numerical Math Functions: Log, ABS, MOD, POWER, ROUND, CEIL, SQRT, EXP, FLOOR, TRUNC
  • Conversion Functions: ROWIDTONCHAR, DECODE, TO_CHAR, NVL, TO_NUMBER, NVL2, TRANSLATE, ROWIDTOCHAR
  • Analytical Functions: ROWNUM, CUBE, Group by, HAVING
  • Miscellaneous Functions: GREATEST, LEAST
  • Intro to DBMS, RDBMS, Normalization, SQL, PL-SQL
  • Inserting, Deleting, and Updating Data
  • Commit and Rollback
  • DM, DDL, DCL
  • SELECT, WHERE clauses, Sorting, Wildcards, Distinct
  • Create, Alter, Drop Tables
  • Different data types for fields
  • Constraints: Primary keys, Foreign keys, Unique, Not Null
  • Group and Have clause
  • Joins: inner join, outer joins, self join
  • Introduction to Functions, Procedures, Triggers, Cursor, Sequence
  • Interview questions and answers discussion
  • Written test
  • Oral presentation test

MODULE - 2: An Overview of SQL Server 2000, 2005, 2008-(6 hours)

  • SQL Server 2008 Architecture
  • Available Features and Tools
  • New Capabilities
  • SQL Services
  • Product Licensing & Editions
  • Preparing for Installation
  • Creating Users for Service Accounts
  • Choosing Appropriate File Locations
  • Choosing Instance Names
  • Installing SQL Server 2008
  • Choosing Appropriate Features
  • Specifying Service User Accounts
  • Choosing Licensing Mode
  • Choosing Appropriate Collation
  • Installing Multiple Instances
  • Upgrading to SQL Server 2008
  • SQL Server Management Studio
  • SQL Server Configuration Manager
  • SQL Server Books Online
  • SQLCMD
  • Practice Test
  • Interview questions and answers discussion
  • Written test
  • Oral presentation test

MODULE - 3: Configuring SQL Server 2008 - (6 hours)

  • Registering Servers
  • Using Metadata Views to Obtain Object Properties
  • Using Object Explorer
  • Configuring a SQL Server Instance
  • Configuring Object Properties
  • Using the Query Window
  • Creating Transact SQL Projects
  • Managing Services
  • Configuring Server Network Connections
  • Configuring Client Connections and Aliases
  • Configuring the Surface Area of SQL Server
  • Configuring Policy-Based Management
  • Creating a Central Management Server
  • Using the Dedicated Administrator Connection
  • Configuring Data Replication
  • Creation and Maintenance of Full-Text Catalogs
  • Describing Full-Text Search
  • Practice Test
  • Interview questions and answers discussion
  • Written test
  • Oral presentation

MODULE - 4: Creating and Configuring a Database- (12 hours)

  • Understating database Schema and instance
  • Understating the SAN configuration
  • Creating Databases
  • Creating Tables
  • Specifying Data Types
  • Creating Indexes
  • Describing Data File Structure
  • Describing Log File Structure and Function
  • Creating File groups
  • Configuring File stream Storage
  • Creating and Using Database Snapshots
  • Moving a Database to Another SQL Server
  • Wrap up and Closure
  • Interview questions and answers discussion
  • Written test
  • Oral presentation test

MODULE - 5: Monitoring/Tuning SQL Server Performance-(12 hours)

  • Why to Monitor SQL Server
  • Tools for Monitoring SQL Server
  • Common Monitoring and Tuning Tasks
  • Using the Windows Performance Tool to Detect the Cause of a Bottleneck
  • Using SQL Server Profiler
  • Using Dynamic Management Functions
  • Interpreting Management Studio Reports
  • Using Activity Monitor
  • Creating DDL Triggers
  • Creating Event Notifications
  • Configuring SQL Server Audit
  • T-SQL Tuning
  • SQL Tuning
  • Diagnosing Storage and System Problems
  • Diagnosing Session and O/S Issues
  • Interview questions and answers discussion
  • Written test
  • Oral presentation test

MODULE - 6: Optimizing SQL Server Performance- (6 hours)

  • Describing Index Structures
  • Clustered Indexes
  • Nonclustered (NC) Indexes
  • Choosing Appropriate Clustered Index
  • Choosing Appropriate NC Indexes
  • Detecting Index Fragmentation
  • Rebuilding and Reorganizing Indexes
  • Using the Database Engine Tuning Advisor
  • Generating and Interpreting Query Plans
  • Configuring the Resource Governor
  • Interview questions and answers discussion
  • Written test
  • Oral presentation test

MODULE - 7: Securing SQL Server- (6 hours)

  • SQL Server Security Architecture
  • Defining Principals and Securables
  • Using the Security Hierarchy
  • Creating Logins and Users
  • Applying Password Policies to SQL Logins
  • Creating Roles
  • Granting Permissions
  • Using SQLCMD to Test Permissions
  • Creating and Using Certificates
  • Setting Execution Context
  • Encrypting Data
  • Encrypting Databases
  • Interview questions and answers discussion
  • Written test
  • Oral presentation test
 

MODULE - 8: Automating Tasks with Jobs and Alerts- - (6 hours)

  • Creating Maintenance Plans
  • Job Creation and Execution
  • Defining jobs to handle routine tasks
  • Creating and Configuring Alerts
  • Associating alerts with jobs
  • Creating Schedules for Jobs
  • Creating and Using Proxies
  • Setting SQL Server and Server Alert thresholds
  • Creating customized error conditions
  • Scheduling Multi-step job streams
  • Creating and Using Operators
  • Configuring and Setting up SQL Database Mail
  • Interview questions and answers discussion
  • Written test
  • Oral presentation test

MODULE - 9: Importing and Exporting Data - (6 hours)

  • Using bcp and BULK INSERT
  • Using the Import/Export Wizard
  • Using Integration Services to Import Data Interview questions and answers discussion
  • Written test
  • Oral presentation test

MODULE - 10: Introducing Replication - (6 hours)

  • Introduction to Distributed Data
  • Introduction to SQL Server Replication
  • SQL Server Replication Agents
  • SQL Server Replication Types
  • Physical Replication Models
  • Creating a Replication Topology
  • Configuring SQL Server Replication

MODULE - 11: Backing Up and Restoring Databases - (12 hours)

  • Choosing Appropriate Database Recovery Model Option
  • Describing Automatic Recovery
  • Choosing an Appropriate Backup Strategy
  • Determining Backup File Contents
  • Designing a Database Recovery Plan
  • Performing full, log and differential backups
  • Recovering system and user databases
  • Using Backup Compression
  • Use of File groups in a Backup Strategy
  • SQL Server Recovery Process
  • Preparing to Restore a Database
  • Restoring Backups
  • Restoring Databases from Different Backup Types
  • Restoring the Master Database
  • Restoring Damaged System Databases
  • Rebuilding System Databases
  • Implementing database mirroring
  • Selecting a mirroring architecture
  • Configuring the mirror and witness servers
  • Monitoring database mirroring
  • Taking a snapshot of the mirror

MODULE - 12: Maintaining Data Reliability- (6 hours)

  • Maintaining Data Integrity
  • Creating Constraints
  • Describing and Using DML Triggers
  • Configure Data Compression
  • Setting Transaction Isolation Levels Capturing Changed Data

MODULE - 13: Maintaining High Availability of Data - (6 hours)

  • Introduction to Availability
  • Purpose of Windows Clustering
  • Increasing Availability Using Failover Clustering
  • Standby Servers and Use of Log Shipping

MODULE - 14: Real-Time Project Based Workshop and Presentation

Each student will be required to complete a real-time project that covers the entire course curriculum as listed below. When their project is successfully completed, each student will then make a formal presentation of their entire work over 6 hours to an audience of fellow students under the supervision of the course instructor. The presentation will be conducted in three separate four-hour sessions. Each student is also required to attend an additional 18 hours participating as an audience member while their fellow student(s) are presenting their projects.

Course Instructor