SQL Server 2012 Administration

Course Duration: 80 hours

Course Schedule: Monday to Friday 9:00PM to 11 PM Eastern Time.

Please contact SolWin to know more about registering for this batch.

In this course, we the expert trainers at SolWin will provide you with the following:

High Availability & Disaster Recovery solutions.
TSQL Programming
Database Design
Performance Tuning
Trained by MASTER DBA with 15 years SQL Server 2012/2008/2005/2000/7.0/6.0 experience.

After each class, you will get

Comprehensive course material for certification exams
Hands-On LAB exercises which you need to finish before next class.
Recorded video of the live class which you can keep it forever and review it later.

Part-1: TSQL Programming & Database Design

RDBMS Concept

DBMS Concept
RDBMS Concepts
ER Diagram Concepts
Cardinality
Ordinality
SDLC for Database design and Development
Normalization and De-Normalization

SQL Server Architecture

SQL Server edition overview
Introducing the tools
SQL Server Management Studio
Managing Tables with DDL
RDBMS Concepts
Cardinality
Ordinality

Creating schemas

Managing schemas
Referencing schemas versus using the default schema
Hiding schemas with synonyms
Building tables

Selecting appropriate data types

Constructing tables with CREATE TABLE
Different data types and what is the internal difference
Importance of selecting proper data types.
Data types and performance

Adding constraints

Not Null
Primary Key
Foreign key
Unique
Check
Default
Candidate Key
Alternate Key
Natural Key
Surrogate Key

Implementing various types of joins

Inner joins
Cross joins
Left, right and full outer joins
Equijoins
The performance implications of joins
Adding filter conditions to outer joins
Writing self joins
Join algorithms(hash join, loop join and merge join)

Joining a table to itself

Chaining self joins
Solving time-interval problems

Combining queries with set operators

UNION
UNION ALL
INTERSECT
EXCEPT

Scalar and Aggregate Functions

Taking advantage of scalar functions
Converting data types
Handling dates
Manipulating strings
Choosing the right function for the job

Summarizing data with aggregate functions

COUNT
SUM
AVG
MIN
MAX
Managing NULLs
Suppressing duplicates

Grouping data

GROUP BY and GROUPING SETS
Applying conditions with HAVING
Calculating moving averages

Extending group queries

Nesting grouped aggregates
Joins and grouping
Introducing subtotals with CUBE and ROLLUP

Building crosstab reports

Using CASE to turn rows into columns
Applying PIVOT and UNPIVOT

Declaring variables and parameters

Creating and utilizing local variables
Passing input and output parameters
Interrogating global variables

Calling built-in scalar functions

Converting data using CAST and CONVERT
Ordering data with ranking functions

Performing Extensive Analysis with Analytic Functions

The OVER clause
Specifying the ordering before applying the function
Splitting the result set into logical partitions

RANK Function

RANK and DENSE_RANK
ROW_NUMBER with ordered sets

Extending the use of aggregates

Partitioning in multiple levels
Computing running totals
Comparing row and aggregate values

Building Sub-queries

Simple sub-queries
Sub-queries in conditions and column expressions
Creating multilevel sub-queries
Avoiding problems when sub-queries return NULLs
Handling multi row sub-query results

Correlated sub-queries

Accessing values from the outer query
EXISTS vs. IN
Identifying duplicates
Avoiding accidental correlation

Common table expressions (CTE)

Reusable sub-queries
Recursive sub-queries
Traversing hierarchies

PIVOT/UNPIVOT

Importance of pivoting
How to pivot data.

Derived Tables

Derived table in FROM clause
Derived table in JOIN clause

Maintaining Data

Modifying data
Inserting, updating and deleting data

Transaction

ACID properties
Ensuring data consistency with transactions and distributed transactions
Isolation levels
Begin Transaction
Commit Transaction
Save point
Phantom rows
Non repeatable reads
Dirty Reads
Dealing with open transactions when an exception occurs

SQL Server locking fundamentals

Avoiding blocking problems with read-committed snapshot isolation
Managing locks using hints

Programming procedural statements

Implementing conditions with IF…ELSE
Looping with WHILE and GOTO
Creating code blocks with BEGIN…END
Debugging with PRINT
Returning data using RETURN
Debugging T-SQL in Management Studio

Handling errors

Communicating problems to the client with RAISERROR
Intercepting errors with TRY…CATCH
Dealing with open transactions when an exception occurs

Producing server-side result sets

Building and using temporary tables
Processing rows on the server with a cursor
Taking advantage of table variables

Views

Storing queries on the server
Concealing complexity with views
Indexed views
Partitioned views
Taking advantage of schema binding
View encryption

Functions

Scalar Function
In-Line table value function
Multi-statement table-valued function
Creating user-defined functions
Calculating values with scalar functions
Processing multiple rows returned from a table-valued function
Taking advantage of schema binding.
Function encryption

Triggers

INSTEAD OF vs. AFTER triggers
Detecting row changes using the inserted and deleted tables
Tracking metadata changes with DDL triggers
Auditing user access using a LOGON trigger
Tracking data changes with the OUTPUT clause
Track column changes using UPDATE function.

Stored Procedures

Batch and stored procedure processing
Minimizing network traffic using batches and procedures
Stored procedure compilation and execution
Using scalar functions
Table value parameters.
Querying Multiple Tables

Temporary Tables

Create local temporary tables
Create global temporary tables
Table value parameter
Table variables.
Common table expression (CTE)
Derived Tables

Part–2: Database Administration

SQL Server 2012 Installation and Features

Installing SQL Server 2012
Choosing installation options
Upgrading from previous versions
Applying a service pack

Storage Architecture & data redundancy

LAN
SAN
NAS
LUN
RAID 0
RAID 1
RAID 5
RAID 10
Fiber Channel Network
iSCSI

Essential tools

SQL Server Management Studio
Transact-SQL
SQL Server Configuration Manager

Creating and Managing Databases

Storage structures
Examining disk structures
Creating databases and transaction logs
Defining file groups

Managing database space

Permitting automatic database growth
Adding database files to expand databases
Specifying database options
Pages
Extends

Moving databases

Scripting objects and moving data with Transact-SQL
Detaching and attaching databases

Implementing Server and Database Security

Creating logins
Contrasting Windows and SQL Server authentication
Authorizing logins
Making logins members of server roles
Enforcing password policy

Authorizing database access

Adding users
Defining new roles
Delegating privileges with predefined roles
Assigning users to roles
Handling miss-mapped logins

Managing Permissions

Granting database-scoped privileges
Permitting object creation
Granting blanket permissions

Schemas

Designing schemas
Assigning a default schema

Catalog Information

DMV
DMF
SYS tables

Handling object-level permissions

Limiting object access
Meeting complex permission requirements with roles
Examining permission hierarchies

Creating and managing indexes

Clustered Index
Non Clustered Index
Unique Index
Filtered Index
Partitioned Index
Covered Index
Defining indexed views
Analyzing and repairing fragmentation

Creating and managing Statistics

Density of data
Selectivity of data
Rebuild Statistics
Histogram

Transaction Log

Importance of transaction log
Internal architecture of transaction log
Truncate transaction log
Shrink transaction log
Transaction log size and performance tuning
UNDO-REDO Transactions

System databases

Master
MSDB
Model
TempDB
Importance of TempDB with respect to performance

Recovering from Disasters

Backing up databases
Choosing a recovery model
Transaction log architecture
Full backup
Transaction log backup
Differential backup
File and File Group Backup
Copy Only backup
Partial backup
Log tail backup
Reclaiming transaction log space

Restoring databases

Restore a full backup
Restore a differential backup
Restore a log backup
Restore with NO RECOVERY
Restore with RECOVERY
Restore with RESTART
Point in time restore
MARK restore
Recovering user databases
Testing recovery scenarios

Export/Import data/database

Import wizard
Export wizard
Copy database wizard

Automating Tasks with Jobs and Alerts

The SQL Server Agent
Configuring the agent
Setting up Database Mail

Multistep jobs

Defining jobs to handle routine tasks
Creating alerts and operators
Associating alerts with jobs

Performing Database Maintenance

Database Maintenance Plan Wizard
Choosing maintenance tasks
Scheduling plan execution
Monitoring SQL Server

Ad hoc monitoring

Querying Dynamic Management Objects
DBCC statements

Database Availability

Mirroring Concepts
Clustering Concepts
Log Shipping
Replication
Attach/Detach
Online/Offline Mode
Hot/Cold/Stand by servers

SQL Server logs

Importance of SQL Server log file.
How to read SQL Server log

Dead Locks

What is a dead lock?
What is a wait lock?
Impact of dead lock and wait lock
Victim of dead lock
Identify dead lock using profiler.
How to avoid dead locks
SET DEADLOCK_PRIORITY
SET LOCK_TIMEOUT

Latest Topics

Database auditing
Resource governor
Policy management
Database End points(both TCP & HTTP)

Part–3: Performance Tuning

Executing queries

Analyzing query plans
Enhancing query performance
Testing queries
Selecting the best alternatives
Avoiding errors and pitfalls

Performance tuning tools

DB Engine tuning Adviser
SQL Profiler
DBCC statement.
Perfmon Counters

Memory Management

DBCC PROCCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FLUSHPROCINDB(db_id)
DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
Tick Count

L-L-W Issues

Lock Issues
Latch Issues
Wait Issues

Lock Mode

Shared locks (S)
Update locks (U)
Exclusive locks (X)
Intent locks (I)
Schema locks (Sch)
Schema stability lock (Sch-S)
Schema modification lock (Sch-M)
Bulk Update locks (BU)
Key – Range locks

Isolotion Level

Read uncommitted
Read committed
Repeatable read
Snapshot
Serializable

Lock Granularity and Hierarchies

RID
KEY
PAGE
EXTENT
HoBT
TABLE
FILE
APPLICATION
METADATA
ALLOCATION_UNIT
DATABASE

Analyzing performance using

SET FORCEPLAN
SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT
SET SHOWPLAN_XML
SET STATISTICS IO
SET STATISTICS XML
SET STATISTICS PROFILE
SET STATISTICS TIME
SET ANSI_DEFAULTS
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
SET ARITHABORT
SET ARITHIGNORE
SET FMTONLY
SET NOCOUNT
SET NOEXEC
SET NUMERIC_ROUNDABORT
SET PARSEONLY
SET QUERY_GOVERNOR_COST_LIMIT
SET ROWCOUNT
SET TEXTSIZE
SET DEADLOCK_PRIORITY
SET LOCK_TIMEOUT
SET TRANSACTION ISOLATION LEVEL
Design effective SQL statements.
Relating indexes to where condition
Order of condition in where clause
Query Hints
Table Hints
Join Hints
Increasing sort efficiency
Reducing I/O with covering indexes
Implementing sparse indexes
Getting design advice from built-in tuning tools

Partitioning strategies for tables

Horizontal partitioning
Vertical partitioning

Indexes tuning

Internal fragmentation
External Fragmentation
Rebuild Index
Re organize Index
Portioned Index
Filtered Index
Covered Index
Compressed Index

Statistics Tuning

Density of data
Selectivity of data
Rebuild Statistics
Histogram
Auto create statistics
Auto update statistics
Sync/Asyn update of statistics

Part–4: Advanced DBA Topics

Resource Governor
Data collection
Policy Based management
Data Auditing (optional)
End Points(optional)
Linked Servers(optional)

About Our Expert Trainer

Mr. Daniel AG (MASTER DBA), has 15 years experience as SQL Server DBA, TSQL Programmer and business intelligence developer. He is currently working as SQL Server 2012/2008 Production DBA & Team Lead for a custom application development team of SSRS report developer, .NET developer and SharePoint 2010 administrator. Work with management, developers, end users and software vendors to implement best practice based production promotion processes. Responsible for 24×7 technical support of 132 instances of 64/32 bit SQL Server 2012/2008 R2/2008/2005/2000 Enterprise/Standard infrastructure (Physical & Hyper-V) with Multi-Node Clusters (Active/Passive),SQL Server Replication, Database Mirroring, Log Shipping, IBM SAN solutions, DPM backup, SCOM Monitoring.

Business intelligence development using SSRS, SSIS, SSAS & MDX

More than 500 SSRS reports in SharePoint 2010 integration mode – SSAS solution for data analysis – More than 100 SSIS data feeds between internal and client application databases.

He has completed several large scale T-SQL programming projects for various clients using stored procedures, views, triggers.

He has also successfully upgraded (in place & side by side) SQL Server 2000/2005 to 2008 R2, SSRS 2005 to SSRS 2008 R2, DTS to SSIS.

Performance monitoring/tuning SQL Server, TSQL, ETL process, & SSRS. – Analysis Disk, CPU & Memory bottle neck using SCOM, SQL Profiler, Perfmon, Resmon, DMV, DMF & PAL. – Totally automated SQL Server index maintenance and statistics update process through SQL Agent.

Implementation of comprehensive disaster recovery solution. – Totally automated SQL Server native backup through SQL Agent & tape backup using DPM. – Full/differential/Transactional backup for critical systems.

Testimonials

“It was a both fun and fruitful experience here at SolWin. The training sessions are to-the-point without wasting much time. The materials provided as another plus point during the training process.” Jeevan V K

“SolWin is one of the most reliable training places I would recommend to someone who wishes to learn all about SQL. Their trainers, the ambiance and the facilities provided are state-of-the-art. Kudos!” Varun Thomas