MS SQL Server 2016 Training Course

This course is tailored for system administrators, software developers, and database professionals.

Training Objectives:

• Acquire and enhance the skills necessary to create and manage databases.
• Gain proficiency in SQL syntax for retrieving and modifying data.
• Implement security protocols within the database environment.
• Utilize advanced features such as replication, automation, and business intelligence (BI).
• Leverage Microsoft SQL Server capabilities to develop complex reports and solutions for developers.

This comprehensive training program provides participants with the essential theoretical knowledge and hands-on skills necessary to construct, manage, and analyze business intelligence (BI) solutions utilizing contemporary Microsoft technologies. The curriculum emphasizes SQL Server (covering 2017, 2019, and 2022 features) and Azure Analysis Services (AAS), addressing the complete data analytics lifecycle from data ingestion through to visualization.

Attendees will acquire the ability to design and deploy modern data warehousing architectures, construct resilient ETL/ELT pipelines via SQL Server Integration Services (SSIS), develop sophisticated analytical models using SQL Server Analysis Services (SSAS - Tabular) and Azure Analysis Services, and generate insightful reports and visualizations. The course places significant emphasis on leveraging in-memory computing, cloud integration capabilities, and modern query languages.

Designed for participants who already possess working knowledge of SQL within the Microsoft SQL Server 2008/2012 environment, this course centers on set-based querying techniques and query optimization. Key topics include the effective use of indexes and the detailed analysis of execution plans.

Additionally, the training delves into table expressions, ranking functions, and strategies for managing partitioned tables.

This instructor-led, live training in the UAE (online or onsite) is designed for beginner to intermediate-level developers and database administrators who wish to become proficient in SQL Server 2012 Integration Services.

By the end of this training, participants will be able to:

• Understand the principles of ETL and the role of SSIS.
• Create, deploy, and manage SSIS packages.
• Design and implement Control Flow to define the flow of tasks in a package.
• Implement Workflow and set up constraints for effective task execution.
• Construct Data Flows to extract, transform, and load data.
• Work with Variables and Containers to enhance package flexibility.
• Manage Transactions to ensure data consistency.
• Implement effective error handling and debugging techniques.
• Set up Logging for tracking and monitoring.
• Handle Slowly Changing Dimensions for managing changing data.
• Deploy a Package for production use.
• Implement Security measures to protect sensitive data.
• Utilize Scripting to extend SSIS functionality.
• Apply Best Practices for efficient and maintainable SSIS solutions.

This course is designed to deliver a comprehensive understanding of advanced SQL techniques and Transact-SQL for Microsoft SQL Server. For a more thorough exploration of these subjects, the course can be extended to a three-day format.

This course covers the fundamentals of Microsoft's proprietary extension to the standard SQL language, known as Transact-SQL. Key topics encompass both retrieving and altering data within relational databases hosted on Microsoft SQL Server-based platforms, such as Microsoft SQL Server, Azure SQL Database, and Azure Synapse Analytics.

The DP-300T00: Administering Microsoft Azure SQL Solutions certification demonstrates an IT professional’s ability to manage and implement cloud-based database solutions on Microsoft Azure. It confirms expertise in administering Azure SQL Server instances, databases, and utilizing T-SQL. This credential is essential for organizations driving digital transformation, as it ensures certified professionals can effectively oversee data management, migration, and performance optimization within Azure ecosystems. Earning the DP-300T00 certification boosts professional credibility, positioning individuals as key assets in the IT sector, especially for roles focused on database administration and management.

Target Audience

• Database Administrators
• Software Developers

This three-day, instructor-led course is designed for students transitioning from previous versions of SQL Server, offering an introduction to the new features available in SQL Server 2016.

Skills Gained

• Outline the key capabilities and components of SQL Server 2016
• Detail the new and enhanced performance features in SQL Server
• Explain the new and enhanced security features in SQL Server
• Describe the advancements in SQL Server Availability and Scalability
• Highlight the new and enhanced reporting and PowerBI features in SQL Server 2016
• Outline the new and enhanced data access features
• Describe the new and enhanced features in SQL Server OLAP
• Explain the new SQL Server cloud functionality

The SQL Server 2016 Querying course (20761) aims to impart skills on writing queries for the Transact-SQL language.

The MOC 20764-C course, Administering a SQL Database Infrastructure, provides the necessary skills and knowledge to manage and maintain SQL Server databases in a SQL database environment.

This SQL training program is designed for individuals seeking to develop foundational skills for managing SQL Server databases. Participants will gain expertise in the following areas:

• Operational use of SQL Server and SQL Server Management Studio.
• Core concepts regarding databases, SQL, and RDBMS architecture.
• Techniques for table creation and the application of DDL, DML, and DAL.
• Overview of prevalent RDBMS market solutions and comparative analysis.
• Introduction to NoSQL technologies and the industry shift toward hybrid database environments.

SSAS (SQL Server Analysis Services) is a Microsoft SQL Server tool designed for online analytical processing (OLAP) and data mining, enabling the analysis of data across multiple databases, tables, or files. The semantic data models offered by SSAS are utilized by client applications such as Power BI, Excel, Reporting Services, and various other data visualization tools.

In this instructor-led live training (available onsite or remote), participants will learn how to leverage SSAS to analyze large volumes of data within databases and data warehouses.

By the end of this training, participants will be able to:

• Install and configure SSAS
• Understand the relationship between SSAS, SSIS, and SSRS
• Apply multidimensional data modeling to extract business insights from data
• Design OLAP (Online Analytical Processing) cubes
• Query and manipulate multidimensional data using the MDX (Multidimensional Expressions) query language
• Deploy real-world BI solutions using SSAS

Audience

• BI (Business Intelligence) professionals
• Data Analysts
• Database and data warehousing professionals

Format of the Course

• Interactive lecture and discussion
• Lots of exercises and practice
• Hands-on implementation in a live-lab environment

Course Customization Options

• This training is based on the latest version of Microsoft SQL Server and SSAS.
• To request a customized training for this course, please contact us to arrange.

This instructor-led, live training in the UAE (online or onsite) is aimed at beginner-level data professionals who wish to learn how to design, implement, and manage SSIS packages to perform data integration and transformation tasks.

By the end of this training, participants will be able to:

• Understand the architecture and components of SSIS.
• Design and implement ETL processes using SSIS.
• Use SSIS tools to develop, deploy, and manage data integration solutions.
• Troubleshoot and optimize SSIS packages for performance and reliability.

SQL Server Integration Services (SSIS) is a core component of Microsoft SQL Server that offers a robust platform for data integration and workflow applications. It is extensively utilized for data migration, warehousing, and complex transformation tasks.

This instructor-led live training, available either online or onsite, is tailored for intermediate-level data professionals and developers seeking to specialize in designing advanced SSIS data flows, implementing sophisticated transformations, and managing Slowly Changing Dimensions (SCD) for data warehousing scenarios.

Upon completion of this training, participants will be equipped to:

• Design and implement resilient data flows using SSIS.
• Effectively work with diverse data sources, destinations, and transformation components.
• Implement Slowly Changing Dimensions (SCD) to manage historical data changes.
• Deploy, execute, and troubleshoot SSIS packages with confidence.

Course Format

• Interactive lectures and discussions.
• Extensive exercises and practical practice.
• Hands-on implementation within a live-lab environment.

Course Customization Options

• To request a customized training session for this course, please contact us to arrange your requirements.