How To Become A Sql Server Dba

[D2trainings]

SQL Server 2012 Administration, TSQL Programming & Performance Tuning Training (80 Hours)

By

15+ Years experienced MASTER DBA in USA!!!

 
This is a comprehensive course on

* SQL Server 2012 Administration

* 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.  

* My Profile http://www.linkedin.com/in/danielthedba/

* My Blog  www.datadude.info

* My Website  www.empiredatasystems.com

* My  YouTube Channel www.youtube.com/user/empiredatasystems

* Please make contact for career discussion and my direct number is  267 718 1533

* Total duration of the course is 80 hours

* Course fee is $500 per student.
* Please contact to me on how to register for this batch.
* Course schedule is Monday to Friday 9:00PM to 11 PM Eastern Time.
 

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.

 

My Credentials:

• Mr. Daniel AG (MASTER DBA): Daniel 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 2010administrator. Work with management, developers, end users and software vendors to implement best practice based production promotion processes. Responsible for 24x7 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.
• Completed several large scale T-SQL programming projects for various clients using stored procedures, views, triggers.
• 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.

 

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 Availably
·         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)
 

Get trained from the MASTER!!!!! You will not be disappointed.

 

Please make contact for career discussion and my direct number is 267 718 1533

 

Join my course only if you are SERIOUS about learning SQL Server 2012

 

This is a course run by a professional for other professionals.