ENABLED BUSINESS SOLUTIONS

Enabled Business Solutions

Need Help ?




Courses Delivered

Course Outline

MODULE 1 : Introduction to PowerBI

This module covers the basics of PowerBI with an introduction to PowerBI Cloud Service and comparison of PowerBI with other similar Reporting technologies provided by Microsoft.

We cover the usage, cost and general layout of the PowerBI Cloud Service as well as advantages and disadvantages of PowerBI. In this module we provide an overall architecture of typical BI implementations in large organizations and where PowerBI fits within the landscape.

MODULE 2 : Granting permissions and Access

This module covers the basics of creating groups and granting access permissions within PowerBI, We cover how access can be granted to users within the organization as well as outside the organization. We also over how to create public reports which can be accessed without authentication.

MODULE 3 : Working with Data source

In this module participants will be exposed to different data sources than can be integrated with PowerBI and the Pros and Cons on using such data sources and working with them. We will cover how to upload datasets to PowerBI cloud Service as well as bringing data into PowerBI from Enterprise database solutions like MS SQL Server.

MODULE 4 : Create a Report

In this module we start working with our first PowerBI Report. We explore how to create dashboard and explain how interactive dashboards are configured. We also cover configuration of properties of common PowerBI components and considerations for effective reporting. We cover aspects such as data modelling and the role it plays in effective reporting.

MODULE 5 : Data Refresh

This module will cover how users can create reports with near real time data refresh scenarios using PowerBI gateway. We show users how to setup and configure the PowerBI gateway to integrate on premise database solutions with Cloud Service of PowerBI.

We explain the different scenarios under which this approach might be practical and other considerations. Performance implications of refreshing PowerBI reports.

MODULE 6 : Working with PowerPivot and DAX

In this module we explain how users can enrich data by adding custom calculation using the powerful analytics functions within DAX. We cover how to add custom measures and add a date table to do time based analytics using DAX functions. We explain the advantages and disadvantages of the same. We explain how users will be able to leverage existing data models to develop insight. Understanding how formula engine and Storage engine work in PowerBI and the performance implications of using them in reports.

MODULE 7 : Using the PowerBI Desktop Application

In this module users will be introduced to the more advanced PowerBI desktop application and its basic layout and properties. Users will create reports using the tools and then shown how to deploy, share and secure dashboards within PowerBI when using the application. Users will be introduces to concepts revolving around High density reports.

MODULE 8 : Using Content Packs

In this module we shows users how to create content packs to be shared across the organization. We also show users how to consume content packs created by others as well as using custom datasets. Once done users will be taken through the process of updating existing content packs.

This module will also cover the use of Insights provided by AI programs from within PowerBI to help users understand outliers in their data as well as pattern analysis

MODULE 9 : Using Row level security and Custom visualizations

This module covers the implementation of row level security within PowerBI and shows users how to leverage custom visualizations within their report. We explore some common and popular custom visualizations that are used with the PowerBI community. As well how to embed PowerBI within applications. We also cover how to integrate PowerBI with Excel as well as connect to Analysis Services Data warehouse to perform powerful analytics on very large datasets.

MODULE 10 : What’s new in PowerBI

This module covers the new features until the latest version that was released along with more details on Performance tuning and troubleshooting Power BI reports. Comparison with SSRS and use of Advanced Analytics and machine learning with PowerBI built-in tools.

Course Outline

MODULE 1 : Evolution of SQL 2016

This module will cover a high level outline of the new features in MS SQL Server 2016 and the installation and configuration of SQL Server instance.

  • Installing SQL Server
  • Pre and Post Install Checklist
  • Best Practice when installing SQL
  • Hardware Sizing
  • New Features in SQL 2016
  • Migration Checklist
  • Configuring Server Properties

MODULE 2 : Always Encrypted

With Always Encrypted, SQL Server can perform operations on encrypted data, and best of all the encryption key resides with the application inside the customer’s trusted environment and not on the server.

  • Understanding the difference between always Encrypted and TDE, cell level encryption etc.
  • How does Always Encrypted work
  • When to use Always encrypted.
  • Configuring Always Encrypted with Azure Vault.
  • Limitations of Always Encrypted
  • Implementation of Always Encrypted

MODULE 3 : PolyBase

PolyBase allows you to use T-SQL statements to access data stored in Hadoop or Azure Blob Storage and query it in an ad-hoc fashion. It also lets you query semi-structured data and join the results with relational data sets stored in SQL Server

  • Overview of Hadoop and azure Blob Storage
  • What PolyBase can and cannot do
  • Setting up Azure blob storage
  • Configuring PolyBase
  • Querying using PolyBase
  • Design considerations for when to use PolyBase
  • Troubleshooting PolyBase

MODULE 4 : Stretch Database

Stretch Database is a new feature in SQL Server 2016 that migrates your historical data transparently and securely to the Microsoft Azure cloud.

  • Understanding how stretch databases tables work
  • Setting up Azure infrastructure to support stretch databases
  • Configuring Stretch databases
  • Considerations and drawbacks of using stretch database.

MODULE 5 : In Memory improvements

In this module we explore the architecture of In-Memory OLTP and how it impacts other features of MS SQL Server. We cover how indexes behave in In-Memory OLTP as opposed to other traditional Indexes and also the storage and Durability aspects of In-Memory OLTP.

  • Indexes on In-Memory tables
  • Durability in In-Memory tables
  • Storage models in In-Memory tables
  • Natively Complied Procedures in SQL Server
  • Monitoring and trouble shooting In-Memory OLTP

MODULE 6 : Dynamic Data masking

Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer

  • Difference between masking encryption and hashing
  • When to use Masking
  • How Dynamic Data Masking works
  • Limitations of Dynamic Data Masking
  • Configuring and Implementing Dynamic Data masking

MODULE 7 : Row level Security

Row-level security introduces predicate based access control. It features a flexible, centralized, predicate-based evaluation that can take into consideration metadata (such as labels) or any other criteria the administrator determines as appropriate.

  • What is role level security
  • Where RLS features in the security landscape for an enterprise
  • Implementing RLS
  • RLS Best practices
  • Loop holes in RLS

MODULE 8 : Mobile BI

Reporting Services mobile reports are dedicated reports optimized for a wide variety of form factors and provide an optimal experience for users accessing reports on mobile devices.

An overview of SSRS configuration to support integration with mobile reports and PowerBI will be demonstrated. Hands on for creating a report will not be provided.

  • Setting up and configuring SSRS for Mobile Reports
  • Authentication mechanism for Mobile Reports

MODULE 9 : JSON Support

SQL Server 2016 adds built-in support for importing and exporting JSON and working with JSON strings. This built-in support includes the following statements and functions.

  • String JSON data into MS SQL Server
  • Retrieving JSON data from MS SQL Server using T- SQL Functions
  • When to use JSON and when not to
  • Indexing JSON data for performance

MODULE 10 : Query Store

Query store is a new feature that provides DBAs with insight on query plan choice and performance. It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans.

  • Enabling Query Store
  • Understanding what information is available in Query store
  • Using Query store to Troubleshoot poor performing Queries
  • Query Store best practices
  • What Query store can and cannot do.

MODULE 11 : Temporal tables

SQL Server 2016 now supports system-versioned temporal tables. A temporal table is a new type of table that provides correct information about stored facts at any point in time

  • Understanding what temporal data means
  • How is this different some stretch DB
  • Creating system versions temporal tables
  • Modifying existing tables to temporal tables
  • Indexing consideration for temporal tables
  • Querying data from temporal tables

MODULE 12 : Real time operational analytics

SQL Server 2016 introduces real-time operational analytics, the ability to run both analytics and OLTP workloads on the same database tables at the same time

  • What is Columnstore index
  • What is in-memory table
  • Bringing Updateable Columnstore indexes into OLTP systems to expose transactional data to analytics
  • Considerations for In Memory OLTP
  • How is this different from Tabular Model in SSAS
  • Performance considerations

MODULE 13 : Miscellaneous Features

In this module we cover a number of additional minor features like improvement in T-SQL query constructs. AlwaysOn synchronization throughput improvements. Changes to key DMVs etc. Changes to how statistics are updated, TempDB and Bulk insert operation optimization, Compatibility level changes.

Course Outline

MODULE 1 : Evolution of SQL 2017

This module will cover a high level outline of the new features in MS SQL Server 2016 and the installation and configuration of SQL Server instance.

  • Installing SQL Server
  • Pre and Post Install Checklist
  • Best Practice when installing SQL
  • Hardware Sizing
  • New Features in SQL 2017
  • Migration Checklist
  • Configuring Server Properties

MODULE 2 : Resumable Online Index

DBA have always had trouble estimating the duration of an index rebuild operation and often are forced to kill the process in order to avoid its impact on production queries. This features offers the DBA an alternate method that bridges the gap

  • Why use Resumable Online indexes
  • How does Resumable Online Index Work
  • When to use Always encrypted.
  • When you should not use Resumable Online Indexes
  • Performance Impact of using the indexes
  • Setting Priority

MODULE 3 : Running SQL on Linux

With SQL 2017 MS wants to offer clients the option of running the SQL database engine on Both Windows and Linux OS. This Module covers the installation and configuration of the same.

  • OS perquisites for installing SQL on Linux
  • Installing SQL Server on Linux on Red Hat
  • Configuring SQL CMD
  • Configuring PORT numbers
  • Working with SSMS
  • Troubleshooting Installation issues

MODULE 4 : Graph Database

Graph databases are special format of storing related data that doesn’t strictly follow ER diagram layout. In this module we cover how to create nodes and edge tables

  • What are Graph databases
  • Why use Graph databases
  • Setup Node and Edge tables in graph database
  • Query graph data and reporting from these tables.

MODULE 5 : CLR Security Model Changes

Microsoft has changed the security model for CLRs. They did this because the Code Access Security (CAS) in the .NET Framework is no longer supported as a security boundary, which means an assembly marked as SAFE may be able to run code that is unsafe, or accesses external system resources.

  • Understanding the OLD Security model for CLR Assemblies
  • Create CLR assemblies
  • Storage models in In-Memory tables
  • Setup and Configure the CLR Assembly on the Server
  • Implement CLR Whitelist

MODULE 6 : Adaptive Query processing

Previously SQL Server had to stick with a query plan once it was generated with this feature SQL server can dynamically tune the procedure during runtime to improve performance in a query by query perspective

  • What is Adaptive Query processing
  • Is it a good thing for all cases?
  • Adaptive Joins
  • Adaptive memory Grants
  • Interleaved Execution

MODULE 7 : Automatic Database Tuning

MS now provide automatic tuning of the database based on the feedback from the query during execution. This is a ported version of the feature available in Azure SQL database.

  • What is the query store?
  • How does Query store affect Automatic DB Tuning
  • Forcing Execution Plans
  • Auto Create indexes
  • Drawbacks for Automatic Tuning

MODULE 8 : TSQL enhancements

In this module we cover a number of additional enhancements that have been added with this version of SQL from a Developer Perspective.

  • Database scoped Credentials
  • In Memory Optimized improvements
  • String Manipulation Commands such as TRIM , WITHIN GROUP etc.
  • IDENTITY_CACHE and reseeding Identity value after restarts
  • Smart Backups

MODULE 9 : Scaling SSIS

Starting with SQL 2016, SSIS now support a scalable architecture which distribute SSIS package execution more easily across multiple worker computers, and manage executions and workers from a single master computer.

With SQL 2017 significant improvements to behaviour and high availability of SSIS have been added, such as support for AlwaysON on SSISDB, handling in the execution log.

MODULE 10 : SQL Server Stand Alone Machine Learning

In this module we explore the stand alone installation of MS SQL 2017 Machine Learning Service. We explore the configuration of the server as well as compare the Machine learning server with other topologies such as cloud etc. We also try various experiments with sample data to understand how to use python to perform analysis on data. We explore training models and scoring.

Participants will understand the use cases for the Machine learning services its advantages and disadvantages and when this particular option will be preferred.

Course Outline

MODULE 1 : Installing and Configuring MS SQL Server SSAS

This module covers the installation, minimum software and hardware requirements, disk and memory sizing of the data warehouse server. Participants will setup a new installation of analysis services from scratch and change server properties based on the use case.

The module covers new features in SSAS and typical architecture of MS SQL Server Business Intelligence projects.

MODULE 2 : Design a data warehouse solution

This module covers the basics of OLAP cube design. It introduces concepts like Dimensions and Facts, Kimball Dimensional modelling and Star and snowflake schema. In this module, participants will review best practices for data warehouse design.

We introduce the process of incremental loads and staging table upload best practices, the basic architecture of Analysis services and the different storage models like MOLAP, ROLAP and HOLAP.

MODULE 3 : Working with Datasource and Datasource views

Datasource and Datasource views act as the foundation for a scalable cube design. In the module, we cover how to fetch data from heterogeneous sources. How to accept change without considerable rewrite and how to enrich the cube from source data are also covered in this module.

Improving cube processing and indexing strategies are also covered in this module.

MODULE 4 : Create a Cube

The Module covers the first step in creating a MOLAP cube by using the Datasource and views from the previous module. It covers the different steps involved in setting a basic cube that contains only default entities and needs to be refined by participants in remaining modules. It covers how to create a new cube from scratch as well as importing and modifying an existing cube. Data validation and error checking will also be covered in this module.

MODULE 5 : Dimensions- Hierarchies , Mapping, Processing

The dimensions module covers the best practices in working with cube and defining appropriate dimensions. It covers in depth the different types of Mappings between the dimension and the facts as well as the creation of hierarchies. The module then goes on to cover the different ways the dimensions can be processed. The module ends with coverage on how to handle special dimensions like bridge tables and late arriving dimensions.

MODULE 6 : Measures- Fact less tables, Calculated Measures

Facts play a critical role in the accuracy of the data from the BI project. Arriving at the correct fact, understanding semi-additive measure and fact-less fact table are covered in this module. Enriching the cube with measure groups and calculated measure are also covered in this module.

MODULE 7 : Calculations , MDX Basics

In this module, we cover the basic syntax of MDX queries and introduce some common keywords used when querying the cube. We also cover how to create calculated measures, Named Sets and scripts in this module. The module then proceeds to cover some common calculations using the lag, lead, rank, ancestor, parent and child syntax for MDX queries. The participants will then be querying different datasets that will be run against the cube and validate the same.

MODULE 8 : Partitions, Perspectives , Translations

Here we continue to explore the different tabs available within visual studio when creating a cube. We explore the use and benefits of Partitions in cube processing, performance and data management. The module also covers the implementation of Perspectives as a means to abstract the cube by business segment and how to enforce translations that show cube metadata in regional language format by the client geography.

MODULE 9 : Actions , Aggregations , KPIs

This module covers the design of aggregates to improve performance and explains the advantages and disadvantages of using them. It also covers the implementation of usage based optimization. The module then proceeds to explain the use of actions and covers Reporting actions and drill through actions. This will conclude with the introduction of Key performance indicators or KPIs and how they are implemented within the cube and viewed within Excel or Reporting Services.

MODULE 10 : MDX

Here the participants are introduced to additional MDX querying concepts and a new set of functions to query and create more complicated reports. The participants are familiarized with common functions used in accounting, reporting, cube navigation and custom coding that are required as part of a BI project. We introduce the date dimension and navigate a time series using Time-based functions as well.

MODULE 11 : Securing the Cube, permissions

Securing the package will conclude the developer aspect of the cube with how to implement permissions at dimensions, Facts and rows within SQL Server Analysis Services. Users will create different permissions for a variety of business roles and understand how to layer the security setup when working with the cube.

MODULE 12 : Deploying the cube

Developers and DBAs will learn how to prepare a cube for fresh deployment as well as incremental deployments in this module. Developers will go over the different ways that the cube can be deployed to the server and the advantages and disadvantages of each.

MODULE 13 : Modify and Automate Cube maintenance

Participants are introduced to best practices when working with Cube and how to ensure the cube is always in good health. Modify Server properties to keep up with changing business needs as well as automating cube data load and processing; routine maintenance of the environment, are covered in this module.

MODULE 14 : Administer the cube, Backups , Profiling , Partitions

This Module cover concepts like warm caching, proactive caching, Taking backups, securing backups, managing and maintaining partitioning etc. It introduces the DBA to XMLA or XML for Analysis services which are used by administrators to manage SSAS installations. Additionally, it covers common mistakes made by DBA when working SSAS.

MODULE 15 : Performance Tuning the Cube

This module covers common performance issues that are encountered in SSAS. It explains how to improve cube processing times and query performance for ad hoc queries.

MODULE 16 : Introduction to mining structures

The last module shows participants how to extend SSAS to perform data mining and forecasting using a few of the inbuilt Mining algorithms available within SQL Server Analysis Services. It covers the basic tenants of data mining and how it can be used in different business scenarios such as market basket analysis etc.

Course Outline

MODULE 1 : Installing and Configuring MS SQL Server SSRS

This module covers the installation, minimum software and hardware requirements, disk and memory sizing of the datawarehouse server. Participants will setup a new installation of analysis services from scratch and change server properties based on the use case.

The module covers new features in SSRS and typical architecture of MS SQL Server Business Intelligence projects.

MODULE 2 : Datasets , Tabular and Matrix Reports

This module covers the creation and implementation of Datasets and their use within Tabular and Matrix Reports. It covers fetching data from OLTP and OLAP systems and common features used to create reports. The module will give the participants hands on experience creating basic reports which acts as the foundation for the remaining module.

MODULE 3 : Drill Down, Drill Through and Sub Reports

Participants are guided on the different types of reports and when and where to use them in this module. They will also be introduced to creating groups and nested hierarchies in this module. The module then moves on to cover embedding reports within each other. In this module, the participants will create dashboards and navigation structures to guide users within the site.

MODULE 4 : Dynamic Reports and List Reports

In this module, the participants are introduced to linked datasets and how they are used to create dependant parameters. Users are shown how to create parameters at the report level to dynamically change the behaviour of the report as needed. The module also covers the use of list control to create brochure and catalogue type reports as well.

MODULE 5 : Working with Charts

This module will cover in extensive detail the use of the different charting controls available within SSRS. It will explain the use and behaviour of each control and the pros and cons of using them within reports. Participants will be shown examples of how choosing the correct chart control increases the readability of the report.

MODULE 6 : Deploying Reports

With a basic set of report created, participants are guided on how to deploy reports to SSRS servers in this module. We cover the three different methods available and explain the advantages and disadvantages of them. We cover automated deployments and how to deploy reports in a scaled out environment.

MODULE 7 : Performance Tuning Reports

This module covers how to improve load time and rendering times for reports. It helps the participants to go over basic steps of troubleshooting performance issues and explains where to correct the problems. It also introduces tools available to the participants in order to improve execution times.

MODULE 8 : Best Practices for building Reports

Reports are all about presentation and in this module we explain key design concepts that participants can use to ensure reports meet the business need and are able to answer a variety of user queries efficiently. We cover creating reports that are consistent and reusable. The module will also cover how to make reports scalable as well as best practices around security and performance.

MODULE 9 : Working with Maps

This module explains how to work with the unique chart control called Map, we explain when it can be used and how to use it to replace older tabular reports. We also explain how to use this control to depict GPS data or regional sales data in new ways. The Map module will also explain how to work with Geometry and Geography data types within MS SQL Server.

MODULE 10 : Working with Report Manager

In this module, we explain how to implement security within Reporting Services and the use of the SSRS API to work with Report Server. The module then explains the different features available in Report Manager to administer Reporting Services. We cover Server properties and Report properties in this module.

MODULE 11 : Working with Report Builder

In this module, we explain how to modify existing reports or build new reports within the Reporting Services URL using the Report Builder functionality. We cover how to use the same features as available in Visual Studio and user workspaces in this Module.

MODULE 12 : Integrating SSRS with ASP.Net

In this module, we cover how to embed SSRS Reports into visual studio aspx pages using Report viewer control and explain how to programmatically control SSRS Report within the website. We explain how to pass values to hidden parameters and the use of RDLC reports.

MODULE 13 : Scripting within Reports

In this module, we explain how to use scripting languages to improve the usability or functionality of the reports within report manger. This includes customizing the report for different layout hiding and displaying report parts, implementing complicated calculations that are not readily available within the expressions of SSRS.

MODULE 14 : Subscriptions , Snapshots and Caches

In the module, we cover topics like creating Snapshots to improve report performance. We explain the advantages and disadvantages of the same. We also cover creation of scheduled and data driven subscriptions and common business cases where they are implemented. We then cover the use of caches to speed up data fetch and fine tune report execution times.

Course Outline

MODULE 1 : What is ETL?

This module covers the installation, minimum software and hardware requirements, disk and memory sizing of the SQL Server integration services. Participants will setup a new installation of integration services from scratch and change server properties based on the use case.

The module covers new features in SSIS and typical architecture of MS SQL Server Business Intelligence projects. It establishes the basic principles that govern ETL processes.

MODULE 2 : Navigating Visual Studio

This module familiarizes the participants with the Visual Studio interface used for building and working with SSIS packages. It shows users where they can find the different options that are required for creating packages. The participants will learn how to create a package from scratch or modify an existing package. This module covers new features available in the version of SQL Server being used as well as best practices on how to code and build an MSBI solution.

MODULE 3 : Control Flow tasks

This module introduces the participants to some of the common control flow tasks they will encounter in SSIS and how to use and configure them. Tasks such as Execute SQL, Bulk Insert, Execute Process task etc. are covered in this module. Participants are shown how to achieve different program flows using the same tasks or subset of tasks. New components such as Change Data capture are also introduced in this module.

MODULE 4 : Advanced Control Flow tasks

In this module, the participants are introduced to linked datasets and how they are used to create dependant parameters. Users are shown how to create parameters at the report level to dynamically change the behaviour of the report as needed. The module also covers the use of list control to create brochure and catalogue type reports as well.

MODULE 5 : Data Flow tasks

This module introduces working with data in SSIS. It explains the common tasks used to transform and enrich the data while moving from a source to destination. Participants will be shown to create summary tables perform joins and other standard ETL operations like sorting, converting data and performing calculations on the data.

MODULE 6 : Advanced Data flow tasks

Extending on what was covered in the previous module this section covers advanced Data flow tasks such as pivot, unpivot, Import and export data as well as DQS cleansing and CDC splitter. It also will explain redirect rows feature for handling issues with the data and best practices implementing data flow task for fast ETL of very large number of rows. We also cover all the different data sources and destinations.

MODULE 7 : Debugging SSIS Packages

Debugging the SSIS package is a very important skill set and this module explains the different options available for troubleshooting and fixes logic issues as well as functionality of the package. We cover breakpoint at the package level and task level as well as control flow error handling and failure constraints. We introduce debugging code in SSMS and Visual Studio as well as watch and locals ad monitoring performance counters associated with SSIS.

MODULE 8 : Performance Tuning SSIS Packages

In this module, we show participants how to fine tune the ETL process and some tips and tricks for speeding up the ETL process. This module covers best practices when loading very large datasets as well as efficient ways to implement SSIS packages that are reusable and fault tolerant. Activities like batching data, memory management and threading are covered in this module. We also cover some fundamentals in T-SQL coding to ensure faster data manipulation using SSIS.

MODULE 9 : Deploying SSIS Packages and Scheduling

With the SSIS package ready we cover deployment options and their behavior on High availability systems in this module. We cover the pros and cons of the different deployment options as well as the deployment manifest file and its use in SSIS.

MODULE 10 : Securing the package

With the package deployed we cover how to ensure the package and the connection strings and other metadata within it are secure from misuse. We cover encrypting the package as well as permissions issues when scheduling the package using SQL Server Agent including the concept of credentials and proxies within MS SQL Server. We also cover common issues when working with cloud based servers in Windows azure.

MODULE 11 : Package Store

As part of deploying packages in SQL 2012 and above, we introduce the participants to the package store functionality and explain the architecture of the feature. We cover the benefits and disadvantages of the package store with respect to packages. We explain the difference between the package deployment model and Project deployment model.

MODULE 12 : Error handling SSIS packages

In this module, we recap some core concepts and explain how errors can be handled and managed in the Control, Data, and precedence tasks as needed. We cover how to perform conditional logic based on a different stage of the package and the use of checkpoint files etc.

MODULE 13 : Datawarehouse tasks

In this module, we introduce the participants to some additional tasks used when working with SQL Server Analysis Services such as the SSAS processing task as well as Slowly Changing Dimensions tasks. We also show participants how to use Analysis Services Execute DDL task to create new partitions etc.

MODULE 14 : Administration tasks

Here we cover in detail some of the more commonly used Control flow tasks used by DBAs as part of regular maintenance for the server. These tools are useful for developers to be able to automate a lot of activities that are performed on Dev servers as well. We cover shrinking, backups, rebuild indexed and more as well as alerts.

MODULE 15 : Parameters and Package Configuration

We prepare the package for deployment using Parameters and package Configuration files in this module. We explain the best practices and pros and cons of each of these tasks. Parameters and their knowledge are essential for the DBA and Developer to ensure a smooth deployment and we explain how to create generic package configuration which can be seamlessly used in DEV, TEST and production environments.

MODULE 16 : Data Quality Services and Master Data Services

Data quality Services and master data Services are additional features introduced or extended in the latest version of MS SQL Server. In this module, we cover a few common scenarios where they can be used to streamline the business process along with a quick demo.

Course Outline

MODULE 1 : Installing SQL Server , Baseline , Hardware sizing

In this module we cover aspects of SQL Server Database administration such as Installing SQL Server and configuring it to best utilize the hardware, we also cover base line of the hardware and implementing best practices such as TempDB optimization, PBM, RAID Configurations and Disk and Memory Sizing for database servers. We will also cover aspects such as different architectures followed when designing a database. Using SQLIOSIM etc will also be covered in this module.

MODULE 2 : Performance Monitoring Tools

In this module we explore the different tools available to monitor performance of the sql server instance.

SQL Server Activity Monitor, PerfMon, Data Collectors, RML, PAL, Profiler, DMVs, Extended Events, DRC

MODULE 3 : SQL Internals

In this module we cover a quick introduction to SQL Server internals and the main components and behaviours of SQL OS with regard to IO, CPU and MEMORY, we explain the roles played by the execution engine and the storage engine as well as the way they affect performance based on recovery model, isolation levels and server settings.

MODULE 4 : Fixing CPU issues

In this module we cover common CPU issues faced by DBAs and Developers when working with SQL Server, we explore how to identify the correct root cause using the tools mentioned in module 2 and explore how to fix the root cause. Some key topics that will be covered in this module include.

Parallelism, Hyper Threading and Multi Core, NUMA, Wait and Queues, Scheduler, Thread Counts, indexes, Resource Governor, Server Side trace etc.

MODULE 5 : Fixing Memory Issues

In this Module we cover memory architecture of SQL server, covering in detail the processes and way memory is allocated and de-allocated, we explore the buffer pool as well as the difference between 32 and 64 bit architecture. We work with DMVs used to identify memory pressure and performance counter used to measure memory utilization. We explore the use of indexes in improving memory utilization and some database design tips and tricks that can improve memory footprint such as filtered indexes, compression, partitioning, in memory structures, columnstore indexes, rewriting the query etc.

MODULE 6 : Fixing IO Issues

In this module we cover the most common root cause of SQL Server performance issues, the Disk IO. We explore files and file groups , the internals of mdf and ldf file management , VLFs , Auto growth and Shrink , impact of TDE on disk IO, Partitioning, Storage Engine Internals , Locking and Blocking, Latches , Trouble shooting tempdb bottlenecks, improving write throughput , performance counters to measure disk latency and more.

MODULE 7 : Locks and Latches

In this module we explore the execution engine and learn how to understand the execution plan we cover locking, blocking and deadlocks. In addition we explore isolation levels and recovery models impacts on data durability, consistency and performance. We will also explore row versioning, snapshots and locking hints.

MODULE 8 : Indexing

In this module we cover internals of indexes, B-Trees , how to identify the right index for the job, how to implement indexed views, reusing indexes, working with statistics, the different types of indexes can when they are most useful, fragmentation in indexes , Scalar UDF , CLR assemblies , Plan Guides and Hints, Joins and Join Algorithms.

MODULE 9 : Everything else

In this module we cover some other not so common ways to approach performance issues, we explore how developers and DBA can achieve performance benefits by using hints , rewriting the query , identify problem queries , Parameter sniffing , SQL plan recompile , dynamic queries , VAS , other common performance issues , QnA .

Course Outline

MODULE 1 : Installing and Configuring MSSQL 2014

This module will cover a high level outline of the new features in MS SQL Server 2014 and the installation and configuration of SQL Server instance.

  • Installing SQL Server
  • Pre and post install checklist
  • Best Practices when installing SQL server
  • Hardware Sizing SQL Server
  • New Features list in SQL 2014
  • Deprecated Features list in SQL 2014
  • Configuring Server Properties
  • Migrations and Upgrades of SQL Server

MODULE 2 : Improving Loads Times and Read Performance

In this module we explore some of the new features in SQL Server that can be used to improve concurrency, read and write performance. We explore features that were newly introduced and compare their performance with older features and evaluate the advantages and disadvantages of each.

  • Delayed Durability
  • Optimistic Concurrency
  • Non Clustered Column Store Indexes
  • Clustered Column Store Indexes
  • Online Partition Rebuild
  • Indexing Strategies

MODULE 3 : Introduction to Always ON and Availability groups

In this Module we cover the implementation and configuration of Always ON Availability Groups. We explore the prerequisites and key steps involved in setting up the HADR Solution and compare it with other solutions currently available.

  • Setting Up Always ON
  • Configuring the database for AlwaysON
  • Setting up Listener
  • Creating the Availability Groups
  • Performing Failover
  • Cloud AG
  • Monitoring the AG

MODULE 4 : Other New features in SQL 2014

In this module we explore some additional features available in MS SQL Server such as Buffer pool Extensions and Resource Governor Enhancements and Managed Lock priority

  • Architecture of Buffer Pool Extensions
  • Improving performance using SSD
  • Resource Governor setup to throttle IO
  • Implementing Managed Lock priority and its impact during Index Rebuild and Partition Switch
  • Cardinality Estimates
  • Cloud Backups
  • Cloud Database files
  • Filestream management
  • Azure deployments

MODULE 5 : Implementing In-Memory OLTP

In this module we cover the key concepts around In-memory OLTP. We explore the trends that resulted in the creation of in Memory OLTP database platforms and how it impacts the current OLTP landscape. We explore the impact of this feature with regard to memory.

  • Advantages and Disadvantages of In-Memory OLTP
  • When to use In-Memory OLTP
  • Implementing In-Memory OLTP
  • Comparing performance between In-Memory OLTP and traditional tables

MODULE 6 : More on In-Memory OLTP

In this module we explore the architecture of In-Memory OLTP and how it impacts other features of MS SQL Server. We cover how indexes behave in In-Memory OLTP as opposed to other traditional Indexes and also the storage and Durability aspects of In-Memory OLTP.

  • Indexes on In-Memory tables
  • Durability in In-Memory tables
  • Storage models in In-Memory tables
  • Natively Complied Procedures in SQL Server
  • Monitoring and trouble shooting In-Memory OLTP

Course Outline

MODULE 1 : Setting up Azure AD and granting permissions

This module will cover a creating a Azure AD and adding users and granting permissions within Azure. We also cover the layout and common features within the old and new portal.

  • Adding Domain
  • Adding Users
  • Setting up DNS
  • Granting Permissions
  • Setting up Multifactor Authentication

MODULE 2 : Setting up and configuring Azure Virtual Machines

In this module we cover the different service tiers for virtual machines and how to decide which configuration is best suited to a task

  • Pricing VMs
  • Deploying Read made images vs Custom Images
  • Scaling and configuring VMs
  • Troubleshooting VMs
  • Deploying to VM
  • Assiging NIC cards and static IPs for Virtual machines

MODULE 3 : Understanding Azure pricing model

In this Module we cover how to monitor and manage costs within Azure by taking advantage of different features or services within the Azure stack. We cover the pricing model in detail.

  • Understanding the Azure Pricing model
  • Working with the pricing calculator
  • Streamlining costs by effectively managing resources
  • Tips to manage Azure using PowerShell

MODULE 4 : Exploring the new azure portal

The new azure portal introduces a number of new services and we walk thru the most common services that will be used as part of deploying IT infrastructure.

  • Exploring new features in Azure Portal
  • Migrating to V12 database using Azure Portal
  • Navigating the New portal
  • Common features in New portal
  • Assigning admins
  • Transfering subscriptions
  • Resource Groups

MODULE 5 : Working with Azure databases

In this module we cover working with traditional relational database like MS SQL Server which are provided within Azure as PaaS

  • Working with Azure database
  • Understanding limitations and design practices for Azure database
  • Deciding when to use Azure DB
  • Deploying databases to Azure DB
  • Migrating databases to Azure DB
  • Troubleshooting Azure database
  • Securing Azure database
  • HADR for Azure database

MODULE 6 : Working with Web and Worker Roles

In this module we explore the differences between a web role and a worker role. we cover the aspects needs to deploy websites and web services into Azure.

  • What is a web role?
  • What is a worker role?
  • Configuring a web roles & worker role
  • Setting up IIS
  • Debugging Code in web role

MODULE 7 : Setting up and configuring Azure Automation

In this module we cover how to automate and schedule routine administrations tasks within Windows Azure.

  • What is Automation?
  • Difference between Automation and Scheduler
  • Using PowerShell scripts
  • Runbooks
  • Testing Automation

MODULE 8 : Working with BLOBs, Queues, Tables and Files

In this module we explore the differences between a web role and a worker role. we cover the aspects needs to deploy websites and web services into Azure.

  • Using Files
  • Using Queues
  • Using Tables
  • Using BLOBS
  • Scalability and limits of Azure Storage media
  • Securing Azure Storage

MODULE 8 : Working with BLOBs, Queues, Tables and Files

In this module we explore the differences between a web role and a worker role. we cover the aspects needs to deploy websites and web services into Azure.

  • Using Files
  • Using Queues
  • Using Tables
  • Using BLOBS
  • Scalability and limits of Azure Storage media
  • Securing Azure Storage

MODULE 9 : Deploying an application to Azure

We combine everything we have learned into this module to create and deploy a secure azure website with a database backend.

  • Deploying a website
  • Deploying a Database
  • Linked resources
  • TP deployment
  • Publish profile
  • Web Matrix
  • Azure Data Sync

MODULE 10 : Monitoring Azure applications

In this module we cover how to monitor the entire Azue stack and setup alerts for performance and cost.

  • Azure Management console
  • Setting up alerts
  • Different types of Alerts
  • Elastic Scaling

MODULE 11 : Scaling Azure Applications with traffic manager

In this module we explain traffic management using Azure services to allow routing of user connections effectively so that scaled VMs or databases are used evenly.

  • Using traffic manager
  • Different modes of traffic manager
  • Scalability achieved using traffic manager
  • Load balancing traffic manager
  • Round Robin

MODULE 12 : Setting up Virtual networks and VPNs

In this module we establish a hybrid cloud environment where secure data transfer can happen rom cloud to on premise and vice versa.

  • Creating credentials
  • Certificates
  • Configuring firewalls and VPNs

MODULE 13 : Additional Services

In this module we cover other services that might be useful in atypical IT stacks

  • Azure CDN
  • Azure Search
  • Document DB
  • Visual Studio
  • Remote App