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.

Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech