SQL Training

Microsoft SQL Server Training

DP-080 Querying Data with Microsoft Transact-SQL

Module 1: Getting Started with Transact-SQL

In this module you will learn about the basics of the Transact-SQL (T-SQL) language, as well as general properties and terminology of relational databases. This module will also introduce the basic SELECT statement for retrieving data from a table.

Lessons

Lab : Get started with SQL Server query tools and writing queries in T-SQL

After completing this module, students will be able to:

(Please click here to to see/hide full course outline) 

Module 2: Sorting and Filtering Query Results

In the module you will learn how to control what data is returned, the order in which it is returned. You will use the ORDER BY clause, with and without paging. You will learn about various kinds of filters that can be used in the WHERE clause to control which data rows are returned. You will also learn how to manage the results by removing duplicates with DISTINCT.

Lessons

Lab : Sort and filter data returned by SELECT queries

After completing this module, students will be able to:

Module 3: Using Joins and Subqueries

In this module, you will explore T-SQL queries which access data from multiple tables with various kinds of JOIN operations and simple subqueries.

Lessons

Lab : Write queries with JOIN operations

Lab : Write SELECT statements using subqueries

After completing this module, students will be able to:

Module 4: Using Built-in Functions

In the module you will explore the use of built-in functions for returning computed or special values in the SELECT list or in the WHERE clause. Functions include math functions, string functions and system functions. There are other types of functions that will be mentioned, but not discussed in detail. You will also learn how to combine rows of data into a single group, providing summary information for the group such as SUM, MIN or MAX.

Lessons

Lab : Built-in functions

After completing this module, students will be able to:

Module 5: Modifying Data

In this module, you will learn the T-SQL statements for modifying table data including UPDATE, DELETE and MERGE as well as various options for INSERT including creating a new table with data from an existing table. You will also look at how to have the system automatically supply values for columns as the data is inserted.

Lessons

Lab : Modify data

After completing this module, students will be able to:

Next steps:

Please contact us for dates. 

2 days online or at one of our locations.

Europe: €1390 excluding VAT.

 North America: US$1.390 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

DP-203 Data Engineering on Microsoft Azure

Module 1: Explore compute and storage options for data engineering workloads

This module provides an overview of the Azure compute and storage technology options that are available to data engineers building analytical workloads. This module teaches ways to structure the data lake, and to optimize the files for exploration, streaming, and batch workloads. The student will learn how to organize the data lake into levels of data refinement as they transform files through batch and stream processing. Then they will learn how to create indexes on their datasets, such as CSV, JSON, and Parquet files, and use them for potential query and workload acceleration.

Lessons

Lab : Explore compute and storage options for data engineering workloads

After completing this module, students will be able to:

(Please click here to to see/hide full course outline) 

Module 2: Run interactive queries using Azure Synapse Analytics serverless SQL pools

In this module, students will learn how to work with files stored in the data lake and external file sources, through T-SQL statements executed by a serverless SQL pool in Azure Synapse Analytics. Students will query Parquet files stored in a data lake, as well as CSV files stored in an external data store. Next, they will create Azure Active Directory security groups and enforce access to files in the data lake through Role-Based Access Control (RBAC) and Access Control Lists (ACLs).

Lessons

Lab : Run interactive queries using serverless SQL pools

After completing this module, students will be able to:

Module 3: Data exploration and transformation in Azure Databricks

This module teaches how to use various Apache Spark DataFrame methods to explore and transform data in Azure Databricks. The student will learn how to perform standard DataFrame methods to explore and transform data. They will also learn how to perform more advanced tasks, such as removing duplicate data, manipulate date/time values, rename columns, and aggregate data.

Lessons

Lab : Data Exploration and Transformation in Azure Databricks

After completing this module, students will be able to:

Module 4: Explore, transform, and load data into the Data Warehouse using Apache Spark

This module teaches how to explore data stored in a data lake, transform the data, and load data into a relational data store. The student will explore Parquet and JSON files and use techniques to query and transform JSON files with hierarchical structures. Then the student will use Apache Spark to load data into the data warehouse and join Parquet data in the data lake with data in the dedicated SQL pool.

Lessons

Lab : Explore, transform, and load data into the Data Warehouse using Apache Spark

After completing this module, students will be able to:

Module 5: Ingest and load data into the data warehouse

This module teaches students how to ingest data into the data warehouse through T-SQL scripts and Synapse Analytics integration pipelines. The student will learn how to load data into Synapse dedicated SQL pools with PolyBase and COPY using T-SQL. The student will also learn how to use workload management along with a Copy activity in a Azure Synapse pipeline for petabyte-scale data ingestion.

Lessons

Lab : Ingest and load Data into the Data Warehouse

After completing this module, students will be able to:

Module 6: Transform data with Azure Data Factory or Azure Synapse Pipelines

This module teaches students how to build data integration pipelines to ingest from multiple data sources, transform data using mapping data flowss, and perform data movement into one or more data sinks.

Lessons

Lab : Transform Data with Azure Data Factory or Azure Synapse Pipelines

After completing this module, students will be able to:

Module 7: Orchestrate data movement and transformation in Azure Synapse Pipelines

In this module, you will learn how to create linked services, and orchestrate data movement and transformation using notebooks in Azure Synapse Pipelines.

Lessons

Lab : Orchestrate data movement and transformation in Azure Synapse Pipelines

After completing this module, students will be able to:

Module 8: End-to-end security with Azure Synapse Analytics

In this module, students will learn how to secure a Synapse Analytics workspace and its supporting infrastructure. The student will observe the SQL Active Directory Admin, manage IP firewall rules, manage secrets with Azure Key Vault and access those secrets through a Key Vault linked service and pipeline activities. The student will understand how to implement column-level security, row-level security, and dynamic data masking when using dedicated SQL pools.

Lessons

Lab : End-to-end security with Azure Synapse Analytics

After completing this module, students will be able to:

Module 9: Support Hybrid Transactional Analytical Processing (HTAP) with Azure Synapse Link

In this module, students will learn how Azure Synapse Link enables seamless connectivity of an Azure Cosmos DB account to a Synapse workspace. The student will understand how to enable and configure Synapse link, then how to query the Azure Cosmos DB analytical store using Apache Spark and SQL serverless.

Lessons

Lab : Support Hybrid Transactional Analytical Processing (HTAP) with Azure Synapse Link

After completing this module, students will be able to:

Module 10: Real-time Stream Processing with Stream Analytics

In this module, students will learn how to process streaming data with Azure Stream Analytics. The student will ingest vehicle telemetry data into Event Hubs, then process that data in real time, using various windowing functions in Azure Stream Analytics. They will output the data to Azure Synapse Analytics. Finally, the student will learn how to scale the Stream Analytics job to increase throughput.

Lessons

Lab : Real-time Stream Processing with Stream Analytics

After completing this module, students will be able to:

Module 11: Create a Stream Processing Solution with Event Hubs and Azure Databricks

In this module, students will learn how to ingest and process streaming data at scale with Event Hubs and Spark Structured Streaming in Azure Databricks. The student will learn the key features and uses of Structured Streaming. The student will implement sliding windows to aggregate over chunks of data and apply watermarking to remove stale data. Finally, the student will connect to Event Hubs to read and write streams.

Lessons

Lab : Create a Stream Processing Solution with Event Hubs and Azure Databricks

After completing this module, students will be able to:

Next steps:

Please contact us for dates.

4 days online or at one of our locations.

Europe: €2,780 Excluding VAT. 

North America: US$2,780 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

DP-300 Administering Relational Databases on Microsoft Azure

Module 1: The Role of the Azure Database Administrator

This module explores the role of a database administrator in the world of Azure. It also provides some foundational information relevant to the overall content. This includes a review of the various SQL Server-based options (SQL Server in a VM, Managed Instances, and Azure SQL Database.) Students will learn why compatibility level is a crucial concept when working with SQL databases in Azure. Students are also introduced to other database platforms available on Azure in addition to those based on SQL Server, in particular PostgreSQL and MySQL

Lessons

Lab : Using the Azure Portal and SQL Server Management Studio

After completing this module, students will be able to:

(Please click here to to see/hide full course outline) 

Module 2: Plan and Implement Data Platform Resources

This module introduces methods for deploying data platform resources in Azure. You will learn about options for both upgrading and migrating existing SQL databases to Azure. You will learn how to set up Azure resources to host SQL Server on a Virtual Machine, a Managed Instance, Azure SQL Database and either PostgreSQL or MySQL. You will learn how to determine which options are best based on specific requirements including the High Availability and Disaster Recovery (HADR) needs. They will learn to calculate resource requirements and create templates for their deployments.

Lessons

Lab : Deploying Azure SQL Database

After completing this module, students will be able to:

Module 3: Implement a Secure Environment

This module explores the practices of securing your SQL Server Database as well as an Azure SQL database. This includes a review of the various SQL Server-based options as well as the various Azure options for securing Azure SQL Database as well as the databases with reside within Azure SQL Database. . Students will lean why security is crucial when working with databases. . Students are also introduced to other database platforms available on Azure in addition to those based on SQL Server, in particular, Azure Database for MariaDB/MySQL/PostgreSQL

Lessons

Lab : Implement a Secure Environment

After completing this module, students will be able to:

Module 4: Monitor and Optimize Operational Resources

This module will teach you about resource optimization for your databases created using either IaaS or PaaS services. The module also covers monitoring server and hardware resources. It will familiarize you with the various tools available for monitoring performance and establishing a baseline. You will learn how to interpret performance metrics for the most critical resources. You will also learn how to troubleshoot database performance using Azure SQL Database Intelligent Insights.

Lessons

Lab : Monitor and Optimize Resources

After completing this module, students will be able to:

Module 5: Optimize Query Performance

Query execution plans are potentially the most important aspect of database performance. Improving bad plans is certainly an area where a small amount of effort can bring huge improvements. While hardware issues can limit query performance, improving hardware usually yields performance improvements in the 10-20% range, at most. More commonly database administrators encounter queries that are not optimized, have stale or missing statistics, have missing indexes, or poor database design choices that lead to the database engine doing more work than is necessary to return results for a given query. Improving the plans can sometimes yield performance improvements in the 100-200% range or even more, meaning that after improving a plan with better indexes or statistics, a query could run twice or three times as fast! This module provides details on how to analyze individual query performance and determine where improvements can be made.

Lessons

Lab : Query Performance Troubleshooting

After completing this module, students will be able to:

Module 6: Automation of Tasks

A common goal for database administrators in many environments is to automate as many of their repetitive tasks. This can be as simple as using scripting to automate a backup process, and as complex as building a fully automated alerting system. This module provides details of automating tasks to simplify the DBA’s job. Methods include scheduling tasks for regular maintenance jobs, as well as multi-instance administration and configuration of notifications for task success or failure or non-completion.

Lessons

Lab : Automating Tasks

After completing this module, students will be able to:

Module 7: Plan and Implement a High Availability and Disaster Recovery Environment

Data must be available when the business needs it. That means the solutions hosting the data must be designed with availability and recoverability in mind. Suppose you work for a company that sells widgets both in stores and online. Your main application uses a highly transactional database for orders. What would happen if the server or platform hosting the transactional database had a problem that made it unavailable or inaccessible for some reason? What impact would it have on the business? If the right solution is put in place, the database would come online in a reasonable timeframe with minimal effort, thus allowing business to continue with little-to-no impact. This module and its associated lab cover configuring, testing, and managing a solution for high availability and disaster recovery (HADR) in Azure, for both Infrastructure-as-a-Service (IaaS) and Platform-as-a-Service (PaaS) deployments. This module will not only cover basic requirements, but also the various options available to achieve HADR.

Lessons

Lab : Plan and Implement a High Availability and Disaster Recovery Environment

After completing this module, students will be able to:

Next steps:

Please contact us for dates.

4 days online or at one of our locations.

Europe: €2,780 Excluding VAT. 

North America: US$2,780 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

DP-500: Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI


Module 1 Introduction to data analytics on Azure

Explore Azure data services for modern analytics

Understand concepts of data analytics

Explore data analytics at scale

Module 2 govern-data-across-enterprise

Introduction to Azure Purview

Discover trusted data using Microsoft Purview

Catalog data artifacts by using Microsoft Purview

Manage Power BI assets by using Microsoft Purview

Integrate Microsoft Purview and Azure Synapse Analytics

Module 3 model-query-explore-data-for-azure-synapse

Introduction to Azure Synapse Analytics

Use Azure Synapse serverless SQL pool to query files in a data lake

Analyze data with Apache Spark in Azure Synapse Analytics

Analyze data in a relational data warehouse

Module 4 prepare-data-for-tabular-models-power-bi

Choose a Power BI model framework

Understand scalability in Power BI

Create and manage scalable Power BI dataflows

Module 5 design-build-tabular-models

Create Power BI model relationships

Use DAX time intelligence functions in Power BI Desktop models

Create calculation groups

Enforce Power BI model security

Use tools to optimize Power BI performance

Module 6 implement-advanced-data-visualization-techniques

Understand advanced data visualization concepts

Monitor data in real-time with Power BI

create-distribute-paginated-reports-power-bi-report-builder

Module 7 implement-manage-analytics-environment

Provide governance in a Power BI environment

Facilitate collaboration and sharing in Power BI

Monitor and audit usage

Provision Premium capacity in Power BI

Establish a data access infrastructure in Power BI

Broaden the reach of Power BI

automate-power-bi-administration

Build reports using Power BI within Azure Synapse Analytics

Module 8 manage-analytics-development-lifecycle

Design a Power BI application lifecycle management strategy

Create and manage a Power BI deployment pipeline

Create and manage Power BI assets

Next steps:

Please contact us for dates.

4 days online or at one of our locations.

Europe: €2,780 Excluding VAT. 

North America: US$2,780 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

DP-900 Microsoft Azure Data Fundamentals

Module 1: Explore core data concepts

Students will learn about core data concepts such as relational, non-relational, big data, and analytics, and build their foundational knowledge of cloud data services within Microsoft Azure.

Lessons

After completing this module, students will be able to:

(Please click here to to see/hide full course outline) 

Module 2: Explore fundamentals of relational data in Azure

Students will explore fundamental relational data concepts and relational database services in Azure.

Lessons

Lab : Provision Azure relational database services

After completing this module, students will be able to:

Module 3: Explore fundamentals of non-relational data in Azure

Students will explore Azure storage for non-relational data and the fundamentals of Azure Cosmos DB.

Lessons

Lab : Explore Azure Storage

Lab : Explore Azure Cosmos DB

After completing this module, students will be able to:

Module 4: Explore fundamentals of data analytics

Students will learn about modern data warehousing, real-time analytics, and data visualization.

Lessons

Lab : Analyze streaming data

Lab : Visualize data with Power BI

Lab : Explore Azure Synapse Analytics

After completing this module, students will be able to:

Next steps:

Please contact us for dates.

1 day online or at one of our locations.

Europe: €695 excluding VAT. 

North America: US$695 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

20764 Administering a SQL Database Infrastructure

Module 1: SQL Server Security

Protection of data within your Microsoft SQL Server databases is essential and requires a working knowledge of the issues and SQL Server security features.

This module describes SQL Server security models, logins, users, partially contained databases, and cross-server authorization.

Lessons

Lab : Authenticating Users

After completing this module, you will be able to:


(Please click here to to see/hide full course outline) 

Module 2: Assigning Server and Database Roles

Using roles simplifies the management of user permissions. With roles, you can control authenticated users’ access to system resources based on each user’s job function—rather than assigning permissions user-by-user, you can grant permissions to a role, then make users members of roles. Microsoft SQL Server includes support for security roles defined at server level and at database level.

Lessons

Lab : Assigning server and database roles

After completing this module, you will be able to:

Module 3: Authorizing Users to Access Resources

In the previous modules, you have seen how Microsoft SQL Server security is organized and how sets of permissions can be assigned at the server and database level by using fixed server roles, user-defined server roles, fixed database roles, and application roles. The final step in authorizing users to access SQL Server resources is the authorization of users and roles to access server and database objects.

In this module, you will see how these object permissions are managed. In addition to access permissions on database objects, SQL Server provides the ability to determine which users are allowed to execute code, such as stored procedures and functions. In many cases, these permissions and the permissions on the database objects are best configured at the schema level rather than at the level of the individual object. Schema-based permission grants can simplify your security architecture. You will explore the granting of permissions at the schema level in the final lesson of this module.

Lessons

Lab : Authorizing users to access resources

After completing this module, you will be able to:

Module 4: Protecting Data with Encryption and Auditing

When configuring security for your Microsoft SQL Server systems, you should ensure that you meet any of your organization’s compliance requirements for data protection. Organizations often need to adhere to industry-specific compliance policies, which mandate auditing of all data access. To address this requirement, SQL Server provides a range of options for implementing auditing.

Another common compliance requirement is the encryption of data to protect against unauthorized access in the event that access to the database files is compromised. SQL Server supports this requirement by providing transparent data encryption (TDE). To reduce the risk of information leakage by users with administrative access to a database, columns containing sensitive data—such as credit card numbers or national identity numbers—can be encrypted using the Always Encrypted feature.

This module describes the available options for auditing in SQL Server, how to use and manage the SQL Server Audit feature, and how to implement encryption.

Lessons

Lab : Using Auditing and Encryption

After completing this module, you will be able to:

Module 5: Recovery Models and Backup Strategies

One of the most important aspects of a database administrator's role is ensuring that organizational data is reliably backed up so that, if a failure occurs, you can recover the data. Even though the computing industry has known about the need for reliable backup strategies for decades—and discussed this at great length—unfortunate stories regarding data loss are still commonplace. A further problem is that, even when the strategies in place work as they were designed, the outcomes still regularly fail to meet an organization’s operational requirements.

In this module, you will consider how to create a strategy that is aligned with organizational needs, based on the available backup models, and the role of the transaction logs in maintaining database consistency.

Lessons

Lab : Understanding SQL Server recovery models

After completing this module, you will be able to:

Module 6: Backing Up SQL Server Databases

In the previous module, you learned how to plan a backup strategy for a SQL Server system. You can now learn how to perform SQL Server backups, including full and differential database backups, transaction log backups, and partial backups.

In this module, you will learn how to apply various backup strategies.

Lessons

Lab : Backing Up Databases

After completing this module, you will be able to:

Module 7: Restoring SQL Server 2016 Databases

In the previous module, you learned how to create backups of Microsoft SQL Server 2016 databases. A backup strategy might involve many different types of backup, so it is essential that you can effectively restore them.

You will often be restoring a database in an urgent situation. You must, however, ensure that you have a clear plan of how to proceed and successfully recover the database to the required state. A good plan and understanding of the restore process can help avoid making the situation worse.

Some database restores are related to system failure. In these cases, you will want to return the system as close as possible to the state it was in before the failure. Some failures, though, are related to human error and you might wish to recover the system to a point before that error. The point-in-time recovery features of SQL Server 2016 can help you to achieve this.

Because they are typically much larger, user databases are more likely to be affected by system failures than system databases. However, system databases can be affected by failures, and special care should be taken when recovering them. In particular, you need to understand how to recover each system database because you cannot use the same process for all system databases.

In this module, you will see how to restore user and system databases and how to implement point-in-time recovery.

Lessons

Lab : Restoring SQL Server Databases

After completing this module, you will be able to:

Module 8: Automating SQL Server Management

The tools provided by Microsoft SQL Server make administration easy when compared to some other database engines. However, even when tasks are easy to perform, it is common to have to repeat a task many times. Efficient database administrators learn to automate repetitive tasks. This can help to avoid situations where an administrator forgets to execute a task at the required time. Perhaps more importantly, the automation of tasks helps to ensure that they are performed consistently, each time they are executed.

This module describes how to use SQL Server Agent to automate jobs, how to configure security contexts for jobs, and how to implement multiserver jobs.

Lessons

Lab : Automating SQL Server Management

After completing this module, you will be able to:

Module 9: Configuring Security for SQL Server Agent

Other modules in this course have demonstrated the need to minimize the permissions that are granted to users, following the principle of “least privilege.” This means that users have only the permissions that they need to perform their tasks. The same logic applies to the granting of permissions to SQL Server Agent. Although it is easy to execute all jobs in the context of the SQL Server Agent service account, and to configure that account as an administrative account, a poor security environment would result from doing this. It is important to understand how to create a minimal privilege security environment for jobs that run in SQL Server Agent.

Lessons

Lab : Configuring Security for SQL Server Agent

After completing this module, you will be able to:

Module 10: Monitoring SQL Server with Alerts and Notifications

One key aspect of managing Microsoft SQL Server in a proactive manner is to make sure you are aware of problems and events that occur in the server, as they happen. SQL Server logs a wealth of information about issues. You can configure it to advise you automatically when these issues occur, by using alerts and notifications. The most common way that SQL Server database administrators receive details of events of interest is by email message. This module covers the configuration of Database Mail, alerts, and notifications for a SQL Server instance, and the configuration of alerts for Microsoft Azure SQL Database.

Lessons

Lab : Monitoring SQL Server with Alerts and Notifications

After completing this module, you will be able to:

Module 11: Introduction to Managing SQL Server by using PowerShell

This module looks at how to use Windows PowerShell with Microsoft SQL Server. Businesses are constantly having to increase the efficiency and reliability of maintaining their IT infrastructure; with PowerShell, you can improve this efficiency and reliability by creating scripts to carry out tasks. PowerShell scripts can be tested and applied multiple times to multiple servers, saving your organization both time and money.

Lessons

Lab : Using PowerShell to Manage SQL Server

After completing this module, you will be able to:

Module 12: Tracing Access to SQL Server with Extended events

Monitoring performance metrics provides a great way to assess the overall performance of a database solution. However, there are occasions when you need to perform more detailed analysis of the activity occurring within a Microsoft SQL Server instance—to troubleshoot problems and identify ways to optimize workload performance.

SQL Server Extended Events is a flexible, lightweight event-handling system built into the Microsoft SQL Server Database Engine. This module focuses on the architectural concepts, troubleshooting strategies and usage scenarios of Extended Events.

Lessons

Lab : Extended Events

After completing this module, you will be able to:

Module 13: Monitoring SQL Server

The Microsoft SQL Server Database Engine can run for long periods without the need for administrative attention. However, if you regularly monitor the activity that occurs on the database server, you can deal with potential issues before they arise. SQL Server provides a number of tools that you can use to monitor current activity and record details of previous activity. You need to become familiar with what each of the tools does and how to use them. It is easy to become overwhelmed by the volume of output that monitoring tools can provide, so you also need to learn techniques for analyzing their output.

Lessons

Lab : Monitoring SQL Server

After completing this module, you will be able to:

Module 14: Troubleshooting SQL Server

Database administrators working with Microsoft SQL Server need to adopt the important role of troubleshooter when issues arise—particularly if users of business-critical applications that rely on SQL Server databases are being prevented from working. It is important to have a solid methodology for resolving issues in general, and to be familiar with the most common issues that can arise when working with SQL Server systems.

Lessons

Lab : Troubleshooting Common Issues

After completing this module, you will be able to:

Module 15: Importing and Exporting Data

While a great deal of data residing in a Microsoft SQL Server system is entered directly by users who are running application programs, there is often a need to move data in other locations, to and from SQL Server.

SQL Server provides a set of tools you can use to transfer data in and out. Some of these tools, such as the bcp (Bulk Copy Program) utility and SQL Server Integration Services, are external to the database engine. Other tools, such as the BULK INSERT statement and the OPENROWSET function, are implemented in the database engine. With SQL Server, you can also create data-tier applications that package all the tables, views, and instance objects associated with a user database into a single unit of deployment.

In this module, you will explore these tools and techniques so that you can import and export data to and from SQL Server.

Lessons

Lab : Importing and Exporting Data

After completing this module, you will be able to:

Next steps:

Please contact us for dates.

5 days online or at one of our locations.

Europe: €2.760 Excluding VAT. 

North America: US$2.760 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

20762 Developing SQL Databases

Module 1: Introduction to Database Development

Before beginning to work with Microsoft SQL Server in either a development or an administration role, it is important to understand the scope of the SQL Server platform. In particular, it is useful to understand that SQL Server is not just a database engine—it is a complete platform for managing enterprise data.

SQL Server provides a strong data platform for all sizes of organizations, in addition to a comprehensive set of tools to make development easier, and more robust.

Lessons

After completing this module, you will be able to:


(Please click here to to see/hide full course outline) 

Module 2: Designing and Implementing Tables

In a relational database management system (RDBMS), user and system data is stored in tables. Each table consists of a set of rows that describe entities and a set of columns that hold the attributes of an entity. For example, a Customer table might have columns such as CustomerName and CreditLimit, and a row for each customer. In Microsoft SQL Server data management software tables are contained within schemas that are very similar in concept to folders that contain files in the operating system. Designing tables is one of the most important tasks that a database developer undertakes, because incorrect table design leads to the inability to query the data efficiently.

After an appropriate design has been created, it is important to know how to correctly implement the design.

Lessons

Lab : Designing and Implementing Tables

After completing this module, you will be able to:

Module 3: Advanced Table Designs

The physical design of a database can have a significant impact on the ability of the database to meet the storage and performance requirements set out by the stakeholders. Designing a physical database implementation includes planning the file groups, how to use partitioning to manage large tables, and using compression to improve storage and performance. Temporal tables are a new feature in SQL Server and offer a straightforward solution to collecting changes to your data.

Lessons

Lab : Using Advanced Table Designs

After completing this module, you will be able to:

Module 4: Ensuring Data Integrity through Constraints

The quality of data in your database largely determines the usefulness and effectiveness of applications that rely on it—the success or failure of an organization or a business venture could depend on it. Ensuring data integrity is a critical step in maintaining high-quality data.

You should enforce data integrity at all levels of an application from first entry or collection through storage. Microsoft SQL Server data management software provides a range of features to simplify the job.

Lessons

Lab : Using Data Integrity Through Constraints

After completing this module, you will be able to:

Module 5: Introduction to Indexes

An index is a collection of pages associated with a table. Indexes are used to improve the performance of queries or enforce uniqueness. Before learning to implement indexes, it is helpful to understand how they work, how effective different data types are when used within indexes, and how indexes can be constructed from multiple columns. This module discusses table structures that do not have indexes, and the different index types available in Microsoft SQL Server.

Lessons

Lab : Implementing Indexes

After completing this module, you will be able to:

Module 6: Designing Optimized Index Strategies

Indexes play an important role in enabling SQL Server to retrieve data from a database quickly and efficiently. This module discusses advanced index topics including covering indexes, the INCLUDE clause, query hints, padding and fill factor, statistics, using DMOs, the Database Tuning Advisor, and Query Store.

Lessons

Lab : Optimizing Indexes

After completing this module, you will be able to:

Module 7: Columnstore Indexes

Introduced in Microsoft SQL Server 2012, columnstore indexes are used in large data warehouse solutions by many organizations. This module highlights the benefits of using these indexes on large datasets; the improvements made to columnstore indexes in SQL Server 2016; and the considerations needed to use columnstore indexes effectively in your solutions.

Lessons

Lab : Using Columnstore Indexes

After completing this module, you will be able to:

Module 8: Designing and Implementing Views

This module describes the design and implementation of views. A view is a special type of query—one that is stored and can be used in other queries—just like a table. With a view, only the query definition is stored on disk; not the result set. The only exception to this is indexed views, when the result set is also stored on disk, just like a table.

Views simplify the design of a database by providing a layer of abstraction, and hiding the complexity of table joins. Views are also a way of securing your data by giving users permissions to use a view, without giving them permissions to the underlying objects. This means data can be kept private, and can only be viewed by appropriate users.

Lessons

Lab : Designing and Implementing Views

After completing this module, you will be able to:

Module 9: Designing and Implementing Stored Procedures

This module describes the design and implementation of stored procedures.

Lessons

Lab : Designing and Implementing Stored Procedures

After completing this module, you will be able to:

Module 10: Designing and Implementing User-Defined Functions

Functions are routines that you use to encapsulate frequently performed logic. Rather than having to repeat the function logic in many places, code can call the function. This makes code more maintainable, and easier to debug.

In this module, you will learn to design and implement user-defined functions (UDFs) that enforce business rules or data consistency. You will also learn how to modify and maintain existing functions.

Lessons

Lab : Designing and Implementing User-Defined Functions

After completing this module, you will be able to:

Module 11: Responding to Data Manipulation via Triggers

Data Manipulation Language (DML) triggers are powerful tools that you can use to enforce domain, entity, referential data integrity and business logic. The enforcement of integrity helps you to build reliable applications. In this module, you will learn what DML triggers are, how they enforce data integrity, the different types of trigger that are available to you, and how to define them in your database.

Lessons

Lab : Responding to Data Manipulation by Using Triggers

After completing this module, you will be able to:

Module 12: Using In-Memory Tables

Microsoft SQL Server 2014 data management software introduced in-memory online transaction processing (OLTP) functionality features to improve the performance of OLTP workloads. SQL Server adds several enhancements, such as the ability to alter a memory-optimized table without recreating it. Memory-optimized tables are primarily stored in memory, which provides the improved performance by reducing hard disk access.

Natively compiled stored procedures further improve performance over traditional interpreted Transact-SQL.

Lessons

Lab : Using In-Memory Database Capabilities

After completing this module, you will be able to:

Module 13: Implementing Managed Code in SQL Server

As a SQL Server professional, you are likely to be asked to create databases that meet business needs. Most requirements can be met using Transact-SQL. However, occasionally you may need additional capabilities that can only be met by using common language runtime (CLR) code.

As functionality is added to SQL Server with each new release, the necessity to use managed code decreases. However, there are times when you might need to create aggregates, stored procedures, triggers, user-defined functions, or user-defined types. You can use any .NET Framework language to develop these objects.

In this module, you will learn how to use CLR managed code to create user-defined database objects for SQL Server.

Lessons

Lab : Implementing Managed Code in SQL Server

After completing this module, you will be able to:

Module 14: Storing and Querying XML Data in SQL Server

XML provides rules for encoding documents in a machine-readable form. It has become a widely adopted standard for representing data structures, rather than sending unstructured documents. Servers that are running Microsoft SQL Server data management software often need to use XML to interchange data with other systems; many SQL Server tools provide an XML-based interface. SQL Server offers extensive handling of XML, both for storage and querying. This module introduces XML, shows how to store XML data within SQL Server, and shows how to query the XML data. The ability to query XML data directly avoids the need to extract data into a relational format before executing Structured Query Language (SQL) queries. To effectively process XML, you need to be able to query XML data in several ways: returning existing relational data as XML, and querying data that is already XML.

Lessons

Lab : Storing and Querying XML Data in SQL Server

After completing this module, you will be able to:

Module 15: Storing and Querying Spatial Data in SQL Server

This module describes spatial data and how this data can be implemented within SQL Server.

Lessons

Lab : Working with SQL Server Spatial Data

After completing this module, you will be able to:

Module 16: Storing and Querying BLOBs and Text Documents in SQL Server

Traditionally, databases have been used to store information in the form of simple values—such as integers, dates, and strings—that contrast with more complex data formats, such as documents, spreadsheets, image files, and video files. As the systems that databases support have become more complex, administrators have found it necessary to integrate this more complex file data with the structured data in database tables. For example, in a product database, it can be helpful to associate a product record with the service manual or instructional videos for that product. SQL Server provides several ways to integrate these files—that are often known as Binary Large Objects (BLOBs)—and enable their content to be indexed and included in search results. In this module, you will learn how to design and optimize a database that includes BLOBs.

Lessons

Lab : Storing and Querying BLOBs and Text Documents in SQL Server

After completing this module, you will be able to:

Module 17: SQL Server Concurrency

This module explains how to name, declare, assign values to, and use variables. It also describes how to store data in an array. Concurrency control is a critical feature of multiuser database systems; it allows data to remain consistent when many users are modifying data at the same time. This module covers the implementation of concurrency in Microsoft SQL Server. You will learn about how SQL Server implements concurrency controls, and the different ways you can configure and work with concurrency settings.

Lessons

Lab : SQL Server Concurrency

After completing this module, you will be able to:

Module 18: Performance and Monitoring

This module explains how to name, declare, assign values to, and use variables. It also describes how to store data in an array. This module looks at how to measure and monitor the performance of your SQL Server databases. The first two lessons look at SQL Server Extended Events, a flexible, lightweight event-handling system built into the Microsoft SQL Server Database Engine. These lessons focus on the architectural concepts, troubleshooting strategies and usage scenarios.

Lessons

Lab : Monitoring, Tracing, and Baselining

After completing this module, you will be able to:

Next steps:

Please contact us for dates.

5 days online or at one of our locations.

Europe: €2.760 excluding VAT. 

 North America: US$2.760 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

20767 Implementing a SQL Data Warehouse

Module 1: Introduction to Data Warehousing

This module describes data warehouse concepts and architecture consideration.

Lessons

Lab : Exploring a Data Warehouse Solution

After completing this module, you will be able to:


(Please click here to to see/hide full course outline) 

Module 2: Planning Data Warehouse Infrastructure

This module describes the main hardware considerations for building a data warehouse.

Lessons

Lab : Planning Data Warehouse Infrastructure

After completing this module, you will be able to:

Module 3: Designing and Implementing a Data Warehouse

This module describes how you go about designing and implementing a schema for a data warehouse.

Lessons

Lab : Implementing a Data Warehouse Schema

After completing this module, you will be able to:

Module 4: Columnstore Indexes

This module introduces Columnstore Indexes.

Lessons

Lab : Using Columnstore Indexes

After completing this module, you will be able to:

Module 5: Implementing an Azure SQL Data Warehouse

This module describes Azure SQL Data Warehouses and how to implement them.

Lessons

Lab : Implementing an Azure SQL Data Warehouse

After completing this module, you will be able to:

Module 6: Creating an ETL Solution

At the end of this module you will be able to implement data flow in a SSIS package.

Lessons

Lab : Implementing Data Flow in an SSIS Package

After completing this module, you will be able to:

Module 7: Implementing Control Flow in an SSIS Package

This module describes implementing control flow in an SSIS package.

Lessons

Lab : Implementing Control Flow in an SSIS Package

Lab : Using Transactions and Checkpoints

After completing this module, you will be able to:

Module 8: Debugging and Troubleshooting SSIS Packages

This module describes how to debug and troubleshoot SSIS packages.

Lessons

Lab : Debugging and Troubleshooting an SSIS Package

After completing this module, you will be able to:

Module 9: Implementing a Data Extraction Solution

This module describes how to implement an SSIS solution that supports incremental DW loads and changing data.

Lessons

Lab : Extracting Modified Data

Lab : Loading a data warehouse

After completing this module, you will be able to:

Module 10: Enforcing Data Quality

This module describes how to implement data cleansing by using Microsoft Data Quality services.

Lessons

Lab : Cleansing Data

Lab : De-duplicating Data

After completing this module, you will be able to:

Module 11: Using Master Data Services

This module describes how to implement master data services to enforce data integrity at source.

Lessons

Lab : Implementing Master Data Services

After completing this module, you will be able to:

Module 12: Extending SQL Server Integration Services (SSIS)

This module describes how to extend SSIS with custom scripts and components.

Lessons

Lab : Using scripts

After completing this module, you will be able to:

Module 13: Deploying and Configuring SSIS Packages

This module describes how to deploy and configure SSIS packages.

Lessons

Lab : Deploying and Configuring SSIS Packages

After completing this module, you will be able to:

Module 14: Consuming Data in a Data Warehouse

This module describes how to debug and troubleshoot SSIS packages.

Lessons

Lab : Using a data warehouse

After completing this module, you will be able to:

Next steps:

Please contact us for dates.

5 days online or at one of our locations.

Europe: €2.760 excluding VAT. 

 North America: US$2.760 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

10987 Performance Tuning and Optimizing SQL Databases

Module 1: SQL Server Architecture, Scheduling, and Waits

This module covers high level architectural overview of SQL Server and its various components. It dives deep into SQL Server execution model, waits and queues.

Lessons

Lab : SQL Server Architecture, Scheduling, and Waits

After completing this module, you will be able to:


(Please click here to to see/hide full course outline)

Module 2: SQL Server I/O

This module covers core I/O concepts, Storage Area Networks and performance testing. It focuses on SQL Server I/O operations and how to test storage performance.

Lessons

Lab : Testing Storage Performance

After completing this module, you will be able to:

Module 3: Database Structures

This module covers Database Structures, Data File and TempDB Internals. It focuses on architectural concepts and best practices related to data files for user databases and TempDB.

Lessons

Lab : Database Structures

After completing this module, you will be able to:

Module 4: SQL Server Memory

This module covers Windows and SQL Server Memory internals. It focuses on architectural concepts and best practices related to SQL Server Memory Configuration.

Lessons

Lab : SQL Server Memory

After completing this module, you will be able to:

Module 5: SQL Server Concurrency

This module covers Transactions and Locking Internals. It focuses on architectural concepts and best practices related to Concurrency, Transactions, Isolation Levels and Locking.

Lessons

Lab : SQL Server Concurrency

After completing this module, you will be able to:

Module 6: Statistics and Index Internals

This module covers Statistics and Index Internals. It focuses on architectural concepts and best practices related to Statistics and Indexes.

Lessons

Lab : Statistics and index Internals

After completing this module, you will be able to:

Module 7: Query Execution and Query Plan Analysis

This module covers Query Execution and Query Plan Analysis. It focuses on architectural concepts of the Optimizer and how to identify and fix query plan issues.

Lessons

Lab : Query execution and query plan analysis

After completing this module, you will be able to:

Module 8: Plan Caching and Recompilation

This module covers Plan Caching and Recompilation. It focuses on architectural concepts, troubleshooting scenarios and best practices related to Plan Cache.

Lessons

Lab : Plan caching and recompilation

After completing this module, you will be able to:

Module 9: Extended Events

This module covers Extended Events. It focuses on architectural concepts, troubleshooting strategy and usage scenarios for Extended Events.

Lessons

Lab : Extended events

After completing this module, you will be able to:

Module 10: Monitoring, Tracing, and Baselining

This module covers tools and techniques to monitor, trace and baseline SQL Server performance data. It focuses on data collection strategy and techniques to analyze collected data.

Lessons

Lab : Monitoring, Tracing and Baselining

After completing this module, you will be able to:

Next steps:

5 days online or at one of our locations.

Europe: €2760  excluding VAT. 

North America: US$2760 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

10985 Introduction to SQL Databases

Module 1: Introduction to databases

This module introduces key database concepts in the context of SQL Server 2016.

Lessons

Lab: Querying SQL Server

After completing this module, you will be able to:

(Please click here to to see/hide full course outline)

Module 2: Data Modelling

This module describes data modelling techniques.

Lessons

Lab: Entity relationship modelling

After completing this module, you will be able to:

Module 3: Normalization

This module describes normalization and denormalization techniques.

Lessons

Lab: Normalizing raw data

After completing this module, you will be able to:

Module 4: Relationships

This module describes relationship types and effects in database design.

Lessons

Lab: Designing relationships

After completing this module, you will be able to:

Module 5: Performance

This module introduces the effects of database design on performance.

Lessons

Lab: Query performance

After completing this module, you will be able to:

Module 6: Database Objects

This module introduces commonly used database objects.

Lessons

Lab: Using SQL Server in a hybrid cloud

After completing this module, you will be able to:

Next steps:

3 days online or at one of our locations.

Europe: €1.730  excluding VAT. 

North America: US$1.730 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

20761 Querying Microsoft SQL Server

Module 1: Introduction to Microsoft SQL Server

This module introduces SQL Server, the versions of SQL Server, including cloud versions, and how to connect to SQL Server using SQL Server Management Studio.

Lessons

Lab : Working with SQL Server Tools

After completing this module, you will be able to:


(Please click here to to see/hide full course outline) 

Module 2: Introduction to T-SQL Querying

This module describes the elements of T-SQL and their role in writing queries. Describe the use of sets in SQL Server. Describe the use of predicate logic in SQL Server. Describe the logical order of operations in SELECT statements.

Lessons

Lab : Introduction to T-SQL Querying

After completing this module, you will be able to:

Module 3: Writing SELECT Queries

This module introduces the fundamentals of the SELECT statement, focusing on queries against a single table.

Lessons

Lab : Writing Basic SELECT Statements

After completing this module, you will be able to:

Module 4: Querying Multiple Tables

This module describes how to write queries that combine data from multiple sources in Microsoft SQL Server.

Lessons

Lab : Querying Multiple Tables

After completing this module, you will be able to:

Module 5: Sorting and Filtering Data

This module describes how to implement sorting and filtering.

Lessons

Lab : Sorting and Filtering Data

After completing this module, you will be able to:

Module 6: Working with SQL Server Data Types

This module introduces the data types SQL Server uses to store data.

Lessons

Lab : Working with SQL Server Data Types

After completing this module, you will be able to:

Module 7: Using DML to Modify Data

This module describes how to create DML queries, and why you would want to.

Lessons

Lab : Using DML to Modify Data

After completing this module, you will be able to:

Module 8: Using Built-In Functions

This module introduces some of the many built in functions in SQL Server.

Lessons

Lab : Using Built-In Functions

After completing this module, you will be able to:

Module 9: Grouping and Aggregating Data

This module describes how to use aggregate functions.

Lessons

Lab : Grouping and Aggregating Data

After completing this module, you will be able to:

Module 10: Using Subqueries

This module describes several types of subquery and how and when to use them.

Lessons

Lab : Using Subqueries

After completing this module, you will be able to:

Module 11: Using Table Expressions

Previously in this course, you learned about using subqueries as an expression that returned results to an outer calling query. Like subqueries, table expressions are query expressions, but table expressions extend this idea by allowing you to name them and to work with their results as you would work with data in any valid relational table. Microsoft SQL Server supports four types of table expressions: derived tables, common table expression (CTEs), views, and inline table-valued functions (TVFs). In this module, you will learn to work with these forms of table expressions and learn how to use them to help create a modular approach to writing queries.

Lessons

Lab : Using Table Expressions

After completing this module, you will be able to:

Module 12: Using Set Operators

This module introduces how to use the set operators UNION, INTERSECT, and EXCEPT to compare rows between two input sets.

Lessons

Lab : Using Set Operators

After completing this module, students will be able to:

Module 13: Using Windows Ranking, Offset, and Aggregate Functions

This module describes the benefits to using window functions. Restrict window functions to rows defined in an OVER clause, including partitions and frames. Write queries that use window functions to operate on a window of rows and return ranking, aggregation, and offset comparison results.

Lessons

Lab : Using Windows Ranking, Offset, and Aggregate Functions

After completing this module, students will be able to:

Module 14: Pivoting and Grouping Sets

This module describes write queries that pivot and unpivot result sets. Write queries that specify multiple groupings with grouping sets

Lessons

Lab : Pivoting and Grouping Sets

After completing this module, students will be able to:

Module 15: Executing Stored Procedures

This module describes how to return results by executing stored procedures. Pass parameters to procedures. Create simple stored procedures that encapsulate a SELECT statement. Construct and execute dynamic SQL with EXEC and sp_executesql.

Lessons

Lab : Executing Stored Procedures

After completing this module, students will be able to:

Module 16: Programming with T-SQL

This module describes how to enhance your T-SQL code with programming elements.

Lessons

Lab : Programming with T-SQL

After completing this module, students will be able to:

Module 17: Implementing Error Handling

This module introduces error handling for T-SQL.

Lessons

Lab : Implementing Error Handling

After completing this module, students will be able to:

Module 18: Implementing Transactions

This module describes how to implement transactions.

Lessons

Lab : Implementing Transactions

After completing this module, students will be able to:

Next steps:

Please contact us for dates.

5 days online or at one of our locations.

Europe: €2.760 excluding VAT. 

 North America: US$2.760 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

MOC 55153 SQL AlwaysOn High Availability and Windows Failover Clustering 

Course outline

Module 1: AlwaysOn and High Availibility Concepts and Terminology

This module will introduce the participants to the concepts and terminology used in the course.

Lessons

After completing this module, students will be able to:


(Please click here to to see/hide full course outline)

Module 2: Windows Server 2016 Failover Clustering

Failover clustering is covered in this module and is a critical feature of AlwaysOn High Availability.

Lessons

Lab : Set up iSCSI Server

Lab : Install the iSCSI VMS

Lab : Add Servers to Server Manager for Ease of Management

Lab : Add the Windows Cluster Feature to SQL1, SQL2 And SQL3

Lab : Create the iSCSI Initators to add the shared storage

Lab : Create the Windows Cluster

Lab : Add a Clustered Service

Lab : Test The Failover Of The Windows Service

Lab : Delete Role

Lab : Examine the Quorum Settings

After completing this module, students will be able to:

Module 3: SQL 2016 Failover Cluster Instances

In this module we move from the generic failover clustering to the specifics involving SQL.

Lessons

Lab : Create a Configuration File by Running the Advanced Cluster Preparation Wizard

Lab : Complete the SQL Cluster Installation on SQL1

Lab : Install the Cluster on SQL2 And SQL3

Lab : Test the SQL Cluster

After completing this module, students will be able to:

Module 4: SQL 2016 Always On Availability Groups

Within the failover clusters of SQL are the concept of Availability groups and their enhancements with the release of SQL 2016 which is the focus of this module.

Lessons

Lab : Create a SQL Instance for The Availability Group

Lab : Enable the SQL Server AlwaysOn Availability Group Feature

Lab : Set Up for Availability Groups

Lab : The Availability Group Wizard

Lab : SSMS and Availability Groups

After completing this module, students will be able to:

Module 5: The Dashboard

Managing AlwaysOn High Availability groups wit SQL 2016 is accomplished with the Dashboard. This module will demonstrate the skills necessary for the accomplishment of the management tasks.

Lessons

Lab : The Dashboard

Lab : Replicating Logins and Jobs

After completing this module, students will be able to:

Module 6: Active Secondary Availability Group Actions

Within Availability groups you may have Active secondary SQL which is covered and demonstrated in this module.

Lessons

Lab : Configure a Read-Only secondary

Lab : Database Backup Using Secondary Replica

After completing this module, students will be able to:

Module 7: Maintenance

In this module you explore maintenance procedures for AlwaysOn High Availability Groups.

Lessons

Lab : Add a Database

Lab : Remove a Database

Lab : Add a Replica

Lab : Remove a Replica

After completing this module, students will be able to:

Module 8: Monitoring and Troubleshooting Availability Groups

In this the final module you will learn how to monitor the clusters and Availability groups and various common troubleshooting procedures.

Lessons

Lab : Dashboard Wizards

Lab : Create an Extended Event session

Lab : Using T-SQL

Lab : Policy Based Management for Availability Groups

Lab : Observe a policy in action

Lab : Create three conditions to be used in the RTO and RPO policies

Lab : Create two policies RTO and RPO

Lab : Test the policies

Lab : Change endpoint owner

After completing this module, students will be able to:

Next steps:

Please contact us for dates.

3 days online or at one of our locations.

Europe: €1.730  excluding VAT. 

North America: US$1.730 (Course runs on Eastern Standard Time)

Please contact using the button or send a request to: 

We will send you a booking form and further information.

Feedback from our students:

"I have never had a better technical instructor and I have taken a LOT of technical courses"JB, US

"This was the best training I have ever attended"WG, Germany

"I had the opportunity to attend the perfect training. Organized with excellence and led magnificantly!"LD, Bulgaria

"It was a great and perfect training for me. The instructor was one of the best ones I had for technical training. The training was well-organized and covered a lot. Thank you."AK, US