Home >> Training & Placement >> My SQL >> View

#1 SQL Server 2017 DBA/TSQL/BI Trainer in USA


02 Nov, 2018
124
Header

#1 SQL Server trainer in North America

Next batch is scheduled to Start

Call me 267 718 1533 for a free demo session... OR visit my YouTube channel for recorded videos of my live training class..

Get trained from 15 years experienced Master DBA!!!

"Hello students, I am Daniel AG, #1 SQL Server trainer in North America. I started my career as SQL Server programmer way back in 1996 and worked my way to SQL Server DBA and Business Intelligence specialist

I also do part time SQL Server 2017 training for more than 4 years. I have an alumina of more than 1000 students successfully working all across North America. My training program is supported by excellent course material, hands on lab exercise and above all, my students can record each class and keep it forever for future references. I am about providing you with the best learning opportunity possible to attain your career goals. SQL Server 2017 is the best domain for people looking for a career change or students want to land in IT job.

Please check my comprehensive course content listed below. I am the only trainer post the course content online since I am very much capable of covering all aspects of SQL Server 2017. Believe me, you will not be Disappointed"

What is my credential and work experience?

Currently working as Master DBA for a leading company in Pennsylvania.
I have 15 years solid work experience in
SQL Server 2017/2008/2005/2000/7.0/6.5 administration.
Highly availability solutions including clustering and mirroring.
TSQL Programming.
SQL Server performance tuning.
Business intelligence development using SSRS, SSIS & SSAS.
#1 Trainer in USA for SQL Server 2017.
I have a passion for teaching and training.
I do part time training for last 4 year now.
Trained more than 1000 students so far.
MS in Information Systems & BS in Telecommunication engineering.
For more info, please check my LinkedIn profilehttp://www.linkedin.com/in/danielthedba/
Please visit my website www.empiredatasystems.com for student?s reviews.

Contact me for further Information

Call me @ 267 718 1533
Email me ag.daniel@yahoo.com
Please visit my website www.empiredatasystems.com for student?s reviews.

Watch my free demo session

Please call me for a free one-on-one live demo session to evaluate my training skill, technical skill, course material and course content.

Why my training is "THE BEST" in the market?

I offer comprehensive SQL Server 2017 training covering
SQL Server 2017 Administration
TSQL Programming
Performance Tuning.
SSRS (Reporting Services)
SSAS (Analysis Services)
SSIS (Integration Service)
MDX Queries
Data Warehousing
I have 15 years SQL Server DBA & TSQL Programming experience.
My training is all about sharing real time scenarios and that is what you needed for passing interviews and survive in real time project situations.
Highly interactive, real time training using WebEx or Windows Live Meeting.
Small class size for individual attention.
Each topic will be explained based on a real time scenario.
You can record each class and play it back later.
Excellent course materials with tons of real TSQL codes.
Each session is followed up with hands-on Homework/Lab exercises.
Install SQL Server 2017 on your computer prior to start of training.
My perquisite reading material will make you better prepared for the class.
Convenient timings for working professionals and students.
Timing for the course is Monday to Friday, 9:00 PM to 11:00 PM Eastern Time Zone.
Total course duration is 80 Hours.
Interview tips and guidelines; resume preparation assistance, placement assistance & certification assistance.
Trained more than 500 students so far.
Course fee is just $500 per student.
Issue course completion certification.
I will provide professional references.
After all, I have a passion for teaching and believe me, you will not be disappointed.

Course & Course Schedule.

I offer two comprehensive courses in SQL Server 2017 domain.

Course-1: SQL Server 2017 Administration, TSQL Programming & Performance tuning

I have batches starting every 5 weeks.
Total course duration is 80 Hours.
Class schedule is Monday to Friday, 8:00 PM to 10:00 PM Eastern Time Zone.
Please check course content page to for more information.

Course-2: SQL Server 2017 Business Intelligence using SSRS, SSIS & SSAS

I have batches starting every three months.
Total course duration is 60 Hours.
Please check course content page to for more information.

Course fee & Payment.

Total course fee is just $500per student.
Mode of payment is
PayPal
Personal Check
I am a verified PayPal member with more than 400 transactions.
I will issue invoice which you can use for tax deduction or employee reimbursement purpose.

How to register for the course

Please call me @267 718 1533 or email me ag.daniel@yahoo.com to register for the course.
Please register for the course as early as possible so that I can send out all prerequisite reading material which will make you better prepared for the class.
Once you register for the class, you get access to videos from previous classes and this will also make you better prepared for the class.

Course Content -> SQL Server 2017 Administration,

TSQL Programming and Performance Tuning Course.

Part?1: TSQL Programming

SQL Server Architecture

SQL Server edition overview
Introducing the tools
SQL Server Management Studio
Managing Tables with DDL

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 2017 Installation and Features

Installing SQL Server 2017
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

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

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

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

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 Topics

Statistics Tuning

Policy Management
Database Auditing
Data Collection
End Points
Linked Server
Resource Governer

Course Content -> SQL Server 2017 Business Intelligence Development Course

Implementing and Maintaining Microsoft SQL Server 2017 Integration Services (SSIS)


Module 1: Introduction to SQL Server 2017 Integration Services
The students will be introduced to the role that Integration Services plays in extracting, transforming, and loading data. The students will also be introduced to the tools that are used to build and manage Integration Services solutions.

Lessons
Overview of SQL Server Integration Services
Using Integration Services Tools
Lab: Introduction to SQL Server Integration Services
Using the Import and Export Wizard
Running an Integration Services Package
After completing this module, students will be able to:
Describe Integration Services solutions
Use Integration Services tools

Module 2: Developing Integration Services Solutions

The students will be introduced to the development tasks that are involved in creating an Integration Services package.
Lessons
Creating an Integration Services Solution
Using Variables
Building and Running a Solution
Lab: Developing Integration Services Solutions
Creating an Integration Services Project
Implementing a Package
Building and Running an Integration Services package
After completing this module, students will be able to:
Create a SQL Server Integration Services solution.
Use variables.
Build and run a solution.

Module 3: Implementing Control Flow

The students will be introduced to the tasks and precedence constraints that can be used to implement control flow in an Integration Services package.
Lessons
Control Flow Tasks
Control Flow Precedent Constraints
Control Flow Containers
Lab: Implementing Control Flow
Creating a Simple Control Flow
Configuring Precedence Constraints
Using Containers
After completing this module, students will be able to:
Configure control flow tasks.
Configure control flow precedence constraints.
Configure control flow containers.

Module 4: Implementing Data Flow

The students will be introduced to the data flow sources, transformations, and destinations that can be used to implement a data flow task in an Integration Services control flow. It also explains how to use data flow paths to direct valid and invalid rows through the data flow.
Lessons
Data Flow Sources and Destinations
Basic Data Flow Transformations
Advanced Data Flow Transformations
Data Flow Paths
Lab: Implementing Data Flow
Transferring Data
Implementing Transformations
Using Data Viewers
Configuring Error Output
After completing this module, students will be able to:
Implement data flow sources and destinations.
Implement basic data flow transformations.
Implement advanced data flow transformations.
Implement data flow paths.

Module 5: Implementing Logging

The students will be introduced to how to use logging in an Integration Services package, and explained how to configure and use logging providers to generate information about a package's execution.
Lessons
Overview of Integration Services Logging
Enabling and Configuring Logging
Lab: Implementing Logging
Configuring Logging
Implementing Custom Logging
After completing this module, students will be able to:
Describe Integration Services logging.
Implement Integration Services logging.

Module 6: Debugging and Error Handling

The students will be introduced to how to debug Integration Services packages by using the debugging tools in Business Intelligence Development Studio. It then explains how to implement error-handling logic in an Integration Services package.
Lessons
Debugging a Package
Implementing Error Handling
Lab: Debugging and Error Handling
Debugging a Package
Implementing Error Handling
Controlling Failure Behavior
After completing this module, students will be able to:
Debug an SSIS package.
Implement transactions.

Module 8: Configuring and Deploying Packages

The students will be introduced to how to create Package Configurations and how to deploy Integration Services packages to production servers.
Lessons
Package Configurations
Deploying Packages
Lab: Configuring and Deploying Packages
Creating a Package Configuration
Preparing a Package for Deployment
Deploying a Package
After completing this module, students will be able to:
Implement package configurations.
Deploy packages.

Implementing and Maintaining Microsoft SQL Server 2017 Analysis Services (SSAS)

Module 1: Introduction to Microsoft SQL Server Analysis Services

This module introduces common analysis scenarios and describes how Analysis Services provides a powerful platform for multidimensional OLAP solutions and data mining solutions. The module then describes the main considerations for installing Analysis Services.
Lessons
Overview of OLAP Concepts.
Overview of SQL Server Analysis Services
After completing this module, students should be able to:
Describe data analysis solutions.
Describe the key features of SQL Server Analysis Services.

Module 2: Creating Multidimensional Analysis Solutions

This module introduces the development tools you can use to create an Analysis Services multidimensional analysis solution, and describes how to create data sources, data source views, and cubes.
Lessons
Developing Analysis Services Solutions
Creating Data Sources and Data Source Views
Creating a Cube
Lab: Creating Multidimensional Analysis Solutions
Creating a Data Source
Creating and Modifying a Data Source View
Creating and Modifying a Cube
After completing this module, students will be able to:
Develop Analysis Services solutions.
Create a data source and a data source view.
Create a cube.

Module 3: Working with Cubes and Dimensions

This module describes how to edit dimensions and to configure dimensions, attributes, and hierarchies.
Lessons
Configuring Dimensions
Defining Attribute Hierarchies
Sorting and Grouping Attributes
Lab: Working with Cubes and Dimensions
Configuring Dimensions
Defining Relationships and Hierarchies
Sorting and Grouping Dimension Attributes
After completing this module, students will be able to:
Configure dimensions.
Define hierarchies.
Sort and group attributes.

Module 4: Working with Measures and Measure Groups

This module explains how to edit and configure measures and measure groups.
Lessons
Working With Measures
Working with Measure Groups

Lab: Working with Measures and Measure Groups
Configuring Measures
Defining Dimension Usage and Relationships
Configuring Measure Group Storage
After completing this module, students will be able to:
Work with measures.
Work with measure groups.

Module 6: Customizing Cube Functionality

This module explains how to customize a cube by implementing key performance indicators (KPIs), actions, perspectives, and translations.
Lessons
Implementing Key Performance Indicators
Implementing Actions
Implementing Perspectives
Overview about Translations
Lab: Customizing Cube Functionality
Calculated Measures and Named Sets
Implementing a KPI
Implementing an Action
Implementing a Perspective
After completing this module, students will be able to:
Implement Key Performance Indicators (KPIs).
Implement actions.
Implement perspectives.
Understand translations.

Module 7: Deploying and Securing an Analysis Services Database

This module describes how to deploy an Analysis Services database to a production server, and how to implement security in an Analysis Services multidimensional solution.
Lessons
Deploying an Analysis Services Database
Securing an Analysis Services Database
Lab: Deploying and Securing an Analysis Services Database
Deploying an Analysis Services Database
Securing an Analysis Services Database
After completing this module, students will be able to:
Deploy an Analysis Services database.
Secure an Analysis Services database.

Module 8: Overview of the different Clients used for Reporting from Analysis Services

This module would describe the different clients which could be used for analyzing OLAP data. This would include discussions around Excel, SSRS, PPS 2010 etc.

Implementing and Maintaining Microsoft SQL Server 2017 Reporting Services (SSRS)

Module 1: Introduction to Microsoft SQL Server Reporting Services

The students will be introduced to the role that Reporting Services plays in an organization's reporting life cycle, the key features offered by Reporting Services, and the components that make up the Reporting Services architecture.
Lessons
Overview of SQL Server Reporting Services
Reporting Services Tools
Lab: Introduction to Microsoft SQL Server Reporting Services
Exploring Report Designer
Exploring Report Manager
After completing this module, students will be able to:
Describe the features of SQL Server Reporting Services.
Describe the Reporting Services tools.

Module 2: Authoring Basic Reports

The students will learn the fundamentals of report authoring, including configuring data sources and data sets, creating tabular reports, summarizing data, and applying basic formatting.
Lessons
Creating a Basic Table Report
Formatting Report Pages
Calculating Values
Lab: Authoring Basic Reports
Creating a Basic Table Report
Formatting Report Pages
Adding Calculated Values
After completing this module, students will be able to:
Create a basic table report.
Format report pages.
Calculate values for a report.

Module 3: Enhancing Basic Reports

The students will learn about navigational controls and some additional types of data regions, and how to use them to enhance a basic report.
Lessons
Interactive Navigation
Displaying Data
Lab: Enhancing Basic Reports
Using Dynamic Visibility
Using Document Maps
Initiating Actions
Using a List Data Region
Creating a Tablix Report
Adding Chart Subreport to Parent Report
After completing this module, students will be able to:
Create reports with interactive navigation.
Display data in various formats.

Module 6: Publishing and Executing Reports

The students will learn the various options you can use to publish reports to the report server and execute them.
Lessons
Publishing Reports
Executing Reports
Creating Cached Instances
Creating Snapshots and Report History
Lab: Publishing and Executing Reports
Publishing Reports
Executing Reports
Configuring and Viewing a Cached Report
Configuring and Viewing a Snapshot Report
After completing this module, students will be able to:
Publish reports.
Execute reports.
Create cached instances.
Create snapshots and report history.

Module 7: Using Subscriptions to Distribute Reports

The students will learn how to implement subscriptions so that you can distribute reports either automatically by e-mail or by publishing reports to a shared folder.
Lessons
Introduction to Report Subscriptions
Creating Report Subscriptions
Managing Report Subscriptions
Lab: Using Subscriptions to Distribute Reports
Creating a Standard Subscription
After completing this module, students will be able to:
Describe report subscriptions.
Create report subscriptions.
Manage report subscriptions.

Module 8: Administering Reporting Services


The students will learn how to administer the Reporting Services server, how to monitor Services server.

Lessons
Reporting Server Administration
Performance and Reliability Monitoring
Administering Report Server Databases
Security Administration
Upgrading to Reporting Services 2008

Lab: Administering Reporting Services
Using Reporting Services Configuration Manager
Securing a Reporting Services Site
Securing Items

After completing this module, students will be able to:
Administer the reporting server.
Monitor performance and reliability.
Administer the Report Server databases.
Administer security.
Upgrade to Reporting Services 2008.

MDX Queries

Basics of MDX
Introduction to Axis and similarity to Geometry.
How MDX is different from SQL statements and how we should approach the same.
Rows, Columns and Pages
Non empty keyword and how it helps performance
Attribute relations in SSAS and how it affects MDX syntax and why it is important to get it right.
Working with Sets
Working with Calculations
Working with Hierarchical Calculations
Working with Time bases calculations like QTD, YTD. Also would cover what is required at the OLAP layer to help these calculations.
Deploying calculations.

Get trained from MASTER DBA!!! You will not be disappointed..Call me now @267 718 1533

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

Serious enquires only please....
Submit Your Comment
Subscribe for newsletter
PA NRI's Chat (0 Users Online)