Course Details

SQL Server DBA


If you are seriously interested in either starting out on the path to becoming a SQL Server Database Administrator or perhaps wish to make a lateral move from an alternative IT discipline, this revelation can be quite disheartening.


Syllabus Content:


SQL Server 2008R2 & 2012 DBA:

Introduction to SQL Server: 

1.     Overview on RDBMS and Beyond Relational

2.     Responsibilities of Database Administrator

3.     History of SQL server – versions

4.     History of SQL server – Editions

5.     Difference between Enterprise and standard Editions

6.     What are system Databases and explain about system databases

a.     Master

b.     Model

c.     MSDB

d.     Temp db

e.     Resources

7.     What is difference between SQL2000,2005,2008,2008R2 & 2012

Installation of SQL Server Versions:

1.     Requirements 

a.     Hardware

b.     Software

2.     How to install the client tools

3.     Installation of SQL2000,2005,2008,2008R2 & 2012

a.     Pre- Installation steps

b.     Client-Installation steps

c.     Viewing installation processes with Log files

4.     Installation of SQL Server Service packs

5.     What is Instances and types of instance

a.     Default Instance

b.     Named Instance

6.     Difference between User Databases and System databases

7.     Real-time Issues: When installation is failed how to check summary of  installation

Configuring SQL Server

1.     What is protocol and types of protocols?

a.     Shared Memory

b.     Named pipes

c.     TCP / IP 

d.     VIA

2.     Configuring Network protocols from SQL Server Manager.

3.     How to change the SQL server port no.

4.     Configuring features by using SQL Server surface area Manager.

5.     Configuring other settings through sp_configure.

6.     How to enable/disable the advanced features.

7.     Dedicated Administrator Connection (DAC).

8.     Connecting to DAC.

9.     Remote Connections.

10.   Configure the Error logs in SQL Server

11.   Real-time Issues: How to connect remote server through third party tools

12.   How to configure Error logs in SQL server agent

Managing Services

1.     Starting and Stopping Services through Configure Manger

2.     Net Command

3.     Start Up Parameter

4.     Starting SQL Server in single user mode

5.     Default Parameter and Advanced Parameter

6.     Real-time Issues: How to create auditing server level and database level

7.     How to stop SQL services in different ways

Database Architecture

1.     What is database?

2.     How to create database GUI based and script based

3.     How to rename the database

4.     Primary Data files

5.     How to add Secondary Data files

6.     Log files

7.     Moving system databases from one location to another location

8.     Database File & file groups (Primary & User-defined)

9.     How to rename the data files and log files

10.   How to change the collation settings

11.   How to change the database from multi user to single user mode

12.   How to change the database to read-only mode

13.   How to add .ndf (next data file) file while data file disk is full

14.   How to monitoring the Disk spaces and failed connected server 

15.   How to change the database from full to simple recovery model

16.   How to connect the server from Remote level

17.   How to connect the server terminal / console from remote level

18.   Real-time Issue: Moving System databases

19.   Recovering Suspect databases

20.   When Log file is full how to troubleshooting

Architecture

1.     SQL Server Architecture

a.     Relational Engine

b.     Storage Engine

c.     Pages

d.     Page architecture

e.     Types of Pages

f.      Extents

g.     Types of Extents

h.     Buffer pool

2.     Transaction Log architecture

3.     Understanding Checkpoints & Lazy writer

4.     Memory Architecture

5.     Real-time Issues:  How to restore page level when page was damaged.

6.     How to check page level data.

Security, Automation

1.     How to create logins

2.     How to assign databases for logins

3.     How to mapping the logins from production server to Standby server

4.     What is difference between server roles and database roles?

5.     What is schema? 

6.     How to create schema and how to assign schema for tables

7.     How give the column level permission particular tables

8.     What is an Orphan user?

9.     How to check the orphan user report

10.   How to fix the orphan users

11.   Granting object level privilege

12.   Working with Users

13.   Understanding Server roles

14.   How to give the permission either server level and database level

15.   Setting authentication modes

16.   Real-time Issue: How Resolving Orphan users

17.   How to move Orphan users from server to server

Encryption

1.     Introduction to TDE

2.     How implement the TDE

3.     Enable the TDE

4.     Advantage of TDE

5.     Removing the TDE

Automating &Administrative Tasks

1.     About SQL Server Agent

2.     Creating jobs, Alert and Operators

3.     Managing jobs and resolving errors

4.     Monitoring jobs and alerts

5.     Configure the alerts

6.     Scheduling the job

7.     Creating Maintenance Plans

8.     Working with job activity monitor

9.     Working with Database mail

a.     Mail Architecture

b.     Configuration profile and Accounts

c.     Sending Mails

10.   Real-time Issue:  When Disk growth is full enable another Disk growth

11.   Configure all DB growth Disk wise

Backup & Restore

1.     Recovery Models (Simple, Full & Bulk – Logged)

2.     Setting Recovery models

3.     How Backups works

4.     Types of Backups

5.     Full Backup

6.     Diff Backup

7.     T-log Backup

8.     Copy Only

9.     Tail – Log

10.   Compressed Backups

a.     Row compression

b.     Page compression

11.   What is Restoring and types of Restoring Modes

a.     With Recovery 

b.     No Recovery

c.     Stand by

12.   Performing Restore (Point –in – time Recovery)

13.   Backup system databases.

14.   Real-time Issue: Recovering a crashed databases (System and user)

15.   Explain Third-party tools in backups

16.   How to take table backup

17.   High Availability and Replication

Log Shipping

1.     Log Shipping Architecture

2.     Building DRS for Log – Shipping

3.     Pre – requisites/log-shipping Process

4.     Deploying Log Shipping

5.     Working with Log Shipping Monitor

6.     Log shipping role changing 

7.     Removing Log Shipping

8.     How run the script when Dr server is out of sync

9.     Advantage & Disadvantage of Log shipping

10.   Real-time Issue:  how to monitoring sync status when server is out of sync

11.   How to change retention period server wise

12.   How to Generation sync reports in log shipping

Database Mirroring (HOT Standby)

1.     Overview of Database Mirroring

2.     Architecture of Mirroring

3.     Operating Modes in Database Miring

4.     Pre – Requisites for Database Mirroring

5.     Deploying Database Mirroring

6.     Fail – over from principle to Mirror

7.     Working with Database Mirroring Monitor

8.     Advantage & Disadvantage of Database Mirroring

9.     Real-time Issue:  how to monitoring sync status when server is out of sync

10.   How to change role when server is down (manually &  Automatic)

11.   How to set the threshold values server side 

Replication

1.     Replication Overview

2.     Replication Models (snapshot/Transactional/Merge)

3.     Replication agents

4.     Configuration Distributor

5.     Deploying Transaction Replication for High Availability

6.     Creating Subscriptions (Homogeneous / Heterogeneous)

7.     Monitoring replication by using replication monitor

8.     Scripting Replication

9.     Real-time Issue:  How to monitoring sync status when server is out of sync

10.   How to set latency time on server side

Clustering SQL Server

1.     What is a cluster 

2.     Types of clustering.

a.     Active / Active (Load Balancing)

b.     Active / Passive(Failover Cluster)

3.     What are the cluster Networks and how to it works.

a.     Private

b.     Public

4.     How to add the cluster node.

5.     What is MSDTC and how it works.

6.     Installing and Configuring SQL Server 2005 clustering.

7.     What is Quorum and types Quorums. 

8.     What is difference Is Alive and Look Alive

Performance Tuning & Indexing 

1.     Index Architecture.

2.     How to optimally take advantage of indexes.

3.     Clustered & Non – clustered indexes.

4.     Index fragmentation.

5.     Indexing Defragmentation options \update Statistics.

6.     How to rebuild the indexes.

7.     How to reorganize the indexes.

8.     What is Blocking and architecture

9.     How to troubleshoot the blocking when server performance is down

10.   What is deadlock and architecture

11.   How to check deadlock in different ways.

12.   How to terminating(KILL)processes

13.   SQL Profile.

14.   How to collect the data from database using pssdiag tool

15.   How to check the performance when the CPU got 100% utilization.

16.   How to provide the top 10 CPU quires to clients.

17.   How to identify the CPU usage, Memory usage and I/O usage.

18.   What is MDW and explain about the MDW features.

a.     Disk usage

b.     Server Activity

c.     Query Statistics

19.   How to configure the MDW

20.   How to enable the performance monitor.

21.   How to check the performance Dash board architecture.

22.   DTA(Database Engine Tuning Advisor)

23.   How to analysis data in DTA 

24.   Real-Time Issues:  Generation Top 10 Quires report

25.   Monitoring Slow Running quires In Profiler

26.   Run PSSDIAG tool when CPU 100% utilization 

27.   Configure MDW for improve performance Server level.

Monitoring Tools

1.     Activity Monitoring

2.     Job activity monitoring

3.     Performance Monitoring

4.     SQL Profiler

5.     DTA (Database Engine tuning Advisor)

6.     Event Viewer

Upgrading to SQL Server 2005/2008

1.     Upgrading the server by applying service packs

2.     Difference between in-place & Side by Side

3.     Upgrading the server from lower version to higher version

4.     In-place up gradation from SQL Server 2005 to 2008

5.     Upgrade Advisor

6.     What is advantage of side by side up gradation

7.     What is disadvantage of in-place up gradation

Migrating SQL Server

1.     Migrating by using back and restore method

2.     Migrating logins

3.     Migrating Jobs

4.     Migrating Linked servers

5.     Migrating DTS Packages to SSIS

6.     Migration between SQL TO Excel

7.     Migrating by using detach / attach

8.     Real-time Issues: How to migrate from server to flat files

9.     How to import & Export data from one server to another  server

10.   How to Migrate servers from one Data centre to another  DC

SQL Server 2012 DBA Concepts

1.     Introduction to SQL server 2012 environment

2.     How to install SQL server 2012

3.     New Features of SQL server 2012

4.     What is Always on Features?

5.     How to Enable Always on Features

6.     Resource governor

7.     What is data compression and types of compressions?

a.     Row level compression

b.     Page level compression

Practical troubleshooting of in SQL DBA

1.     How to Detach and Attach Database from one server to anther server

2.     How to Copy Database from One Server to Anther Server

3.     Blocking and Dead Locks

4.     DR Drills (When primary server down then how to up secondary server as a primary

5.     How to switch over and switch backup of servers

6.     DBCC Commands

7.     How to shrinking Primary file, Secondary file and log file(.mdf, ndf and ldf)

8.     How to Create linked server from sqla server to oracle server

9.     How to Generate the script for each databases

10.   Explain about Database sizes and overall databases of project

11.   When database getting suspect mode how to troubleshoot the database

Features of Our Training 

1.     Real Time Methodologies for each concept

2.     Example Case Studies

3.     24*7 availability for doubt clarification

4.     Recorded version of missing sessions

5.     Resume Preparation and Guidance

6.     Interview Tips, Questions and Guidance

7.     A Real-time project Explanation

8.     Microsoft Certification Assistance

Note: Regular, Fast rack, Weekend Batches as per student convenience

We are providing free introduction of SQL Basics, SSIS & SSRS

 

 


Course Timing Details:


Demo Start Date & Time : Feb-27-2014 09:00

Class Start Date & Time : Jan-28-2014 09:00

Class End Date & Time : Feb-27-2014 09:00


Price : $ 150.00 Purchase Now

Demo Start Date & Time : Mar-31-2014 (8:0)
Class Start Date & Time : Mar-31-2014 (8:0)
Pay Pal

Our Technologies