Microsoft SQL Server 2016/2012 eLearning Bundle Course

Course Overview

This eLearning Bundle includes these 6 courses: Exploring SQL Server 2016, SQL Server 2016 Core, Microsoft 70-461: Querying SQL Server 2012, Microsoft 70-462: Administering SQL Server 2012 Databases, Microsoft 70-463: Implementing a Data Warehouse with SQL Server 2012 and SSRS 2014.

Course Topics

Exploring SQL Server 2016 – 12:35 hours

Begin your exploration of SQL Server 2016 with an overview of what’s new and what’s been updated. Following an installation example, discover the changes in R services and configuring tempdb, then take a close-up look at the various ways SQL Server Management Studio has been updated. Gain an understanding of the enhancements that have been made to ETL packages, integration services, and reporting services for something like mobile reporting and analytical databases for cubes and tabular models, then take a peek at enhancements in TSQL, temp tables, CHECKDB, and the online altering of tables and store procedures and functions. Enhancements in performance are an important reason to upgrade to SQL Server 2016. Learn about changes in manageability features and execution, as well as query plans with live query statistics before taking a look at PowerShell and integration with automation, and discovering 3 new groups of commandlets. Continue your exploration with a focus on in-memory table improvements, columns store index changes, and the new temporal tables, then learn about some additional improvements including enhancements of optimizer, cloud first development, and testing in the cloud.

Continue your exploration of SQL Server 2016 with a review of row level security, including a look at Always Encrypted and dynamic data masking, high availability, and clusters, and discover a wealth of enhancements in the areas of security and encryption. Dive deeper into your SQL Server 2016 exploration, starting with a review of new improvements to integration services including how to use catalogue and project parameters, as well as improvements related to deploying packages. Explore the new data sources for cubes, changes in bidirectional filtering, new functions that improve direct query translation to DAX, and improvements to analysis and reporting services. Take a look at the R language, which has been more deeply integrated into SQL Server 2016 via the SQL Server database engine, and how to run stand-alone R services for development. Examine new client tools to interact with R services and learn how to integrate R services with T-SQL to enable DBAs to write their own R scripts. In addition, discover Master Data Services, or MDS, the SQL Server solution for master data management.

Exploring SQL Server 2016, Part 1 of 5: First Look
Exploring SQL Server 2016, Part 2 of 5: Performance Enhancements
Exploring SQL Server 2016, Part 3 of 5: Security and Availability
Exploring SQL Server 2016, Part 4 of 5: Business Intelligence
Exploring SQL Server 2016, Part 5 of 5: Advanced Integration

Inline Activities
Supplemental Resources


SQL Server 2016 Core – 7:15 hours

In this course, you’ll get an overview of what SQL Server is all about, and tour some of its more prominent features. We’ll start by looking at the product itself, and its various editions, and talk about how Microsoft is constantly shifting features around the various editions. Then we’ll explore some of the tools you get in the product, as well as the extensive documentation and sample applications available to help you learn SQL Server’s many features. One of the main tools you’re likely to use with SQL Server is Management Studio, a nearly all-encompassing set of tools that let you administer and develop code in just about every nook and cranny of the product. Then you’ll see how you can work with data tables, views, and queries, where you really can start getting things done with SQL Server. You’ll see how to create and edit tables and views, generate scripts, and view data from one or more tables. We’ll wrap up with a short discussion about the Business Intelligence features, or BI, in SQL Server.

Modern versions of SQL Server are very easy products to install-the setup program walks you through all of the necessary steps to installing the features you need. There are a number of decisions you’ll have to make along the way, which can seem a bit overwhelming, particularly if you’re new to SQL Server. The good news is that Microsoft carefully considered most of the default settings so that they’ll work in many scenarios. And for most installation options, you can change the setting later, even after using the server in production for a while. But in order to ensure that you are able to perform a successful installation with a resulting server that will serve your needs, you should do some careful planning and preparation long before you start up the SQL Server installation program. In this course, you’ll learn about the basic hardware and software requirements for a successful installation, and learn about some of the decisions you’ll need to make about security and instances of SQL Server. Then we’ll explore some of the issues involved in upgrading an existing instance of and older version of SQL Server. You’ll see a tool from Microsoft that makes the process way easier, because it identifies issues that you might face during the update process. Then we’ll run through an entire installation of SQL Server, as I explore the process and some of the options and decisions you’ll face along the way. Then we’ll wrap up by looking at how you can configure SQL Server after installation, so that it perfectly suits your needs with the features it needs.

SQL Server Management Studio (SSMS) is an integrated development environment for writing ransact-SQL and other types of SQL Server code and commands. It is the principal tool that you can use to administer SQL Server and write code, and is hosted in the Visual Studio shell. Management Studio, which integrates capabilities that previously were available in a umber of separate tools, provides a huge number of tools that touch nearly every nook and cranny of all of the features of SQL Server, most certainly including the core database engine.In this course, you’ll learn how to connect to an instance of SQL Server with Management Studio, and explore the rich user interface and development environment, so that you can customize it to look how you like and support the way you like to work. One size doesn’t fit all, but with all the configuration options you can really personalize Management Studio! One of the core features of Management Studio is Object Explorer, a treeview that exposes pretty much every database and server object in SQL Server. You’ll learn how to navigate the hierarchy to find what you need, and how to access the many tools, editors, and designers packed into Management Studio. When you write code using Management Studio, you’re likely to do it using the Query Editor. This is a rich environment that understands both SQL Server and Transact-SQL code, with many features to help you write clean, maintainable code that performs efficiently. We’ll explore the Query Editor and use it to perform many common tasks with SQL Server.

SQL Server 2016 Core, Part 1 of 3: Getting Started
SQL Server 2016 Core, Part 2 of 3: Install and Configure
SQL Server 2016 Core, Part 3 of 3: Interface and Querying

Inline Activities
Supplemental Resources


Microsoft 70-461: Querying SQL Server 2012 – 12:20 hours

Develop the technical skills for successful querying of Microsoft SQL Server 2012 in this detailed course, designed to get you up-to-speed quickly and ready for the certification exam. We’ll begin with an overview of SQL Server 2012, its components and the various versions. We’ll delve into the built-in SQL tools, including SQL tools such as SQL Server Management Studio (SSMS) and SQL Profiler. Importantly, you’ll learn how to write Transact-SQL (T-SQL) queries for SQL. You’ll also master the fundamentals of working with SQL data types, sorting and filtering data, querying and modifying data, writing select queries, implementing stored procedures, and more. By completing this in-depth course, you’ll be equipped to successfully complete the Querying Microsoft SQL Server 2012 (70-461) exam. You’ll gain a solid understanding of how to utilize the SQL database and query with confidence.

Module 1: Getting Started with SQL Server 2012
Module 2: Working with T-SQL
Module 3: Writing SELECT Queries
Module 4: Working with SQL Data Types
Module 5: Sorting and Filtering Data
Module 6: Querying Data from Multiple Tables
Module 7: Modifying Data
Module 8: Working with SQL Server Built-in Functions
Module 9: Programming in T-SQL
Module 10: Implementing Stored Procedures
Module 11: Working with Subqueries and Table Expressions
Module 12: Working with Set Operators, Conditional Operators and Window Functions
Module 13: Working with PIVOT, UNPIVOT and Grouping Sets
Module 14: Managing Error Handling and Transactions
Module 15: Querying SQL Server System
Module 16: Optimizing Query Performance

Inline Activities
Supplemental Resources


Microsoft 70-462: Administering SQL Server 2012 Databases – 8:25 hours

The tips and tricks covered in this course will enable you to improve your organization’s SQL Server 2012 database performance and prepare you to pass the certification exam. To begin, we’ll delve into the various components of SQL 2012, giving you the tools you need to administer the database and optimize critical organizational information. We’ll cover the database engine itself, along with features and functions including integration services and reporting, analytical, and master data services. These help you to maintain data integrity across an enterprise. Additional topics covered include SQL Server Management Studio, along with and SQL Server Profiler and Database Engine Tuning Advisor that help to improve overall performance. Geared to database professionals, the knowledge you’ll gain from this course will prepare you for the 70-462 Administering SQL Server 2012 Databases exam.

Module 1: Identifying the SQL Platform
Module 2: Deploying SQL Server
Module 3: IP Configuring SQL Server
Module 4: Managing Databases in SQL Server 2012
Module 5: Managing SQL Server Security
Module 6: Implementing Advanced Security Settings
Module 7: Applying Encryption and Compression
Module 8: Working with Indexes and Log Files
Module 9: Working with Backup and Restore
Module 10: Implementing High Availability
Module 11: Optimizing Sever Performance
Module 12: Troubleshooting Issues and Recovering Databases
Module 13: Performing Advanced Database Management Tasks

SQL Server 2016 Core, Part 1 of 3: Getting Started
SQL Server 2016 Core, Part 2 of 3: Install and Configure
SQL Server 2016 Core, Part 3 of 3: Interface and Querying

Inline Activities
Supplemental Resources


Microsoft 70-463: Implementing a Data Warehouse with SQL Server 2012 – 7:00 hours

With this course you’ll acquire the know-how to effectively store large amounts of data and make better business decisions through implementation and management of a data warehouse with SQL Server 2012. We’ll start with an overview of data warehousing and concepts. You’ll learn how to use the tools within SQL Server 2012 to create fact tables and indexes, execute SSIS (SQL Server Integrating Services) packages, and manage data flow. With data warehousing, you’ll have the ability to perform business intelligence analysis and create reports, in order to better understand your company’s strengths and weaknesses. The curriculum highlights key performance needs such as implementing ETL with SQL Server Integration Services, validating data, and using SQL Server Master Data Services (MDS). This course will get you up to speed on data warehousing tools and services, and prepare you for the Implementing a Data Warehouse with Microsoft SQL Server 2012 (70-463) exam.

Module 1: Introduction to Data Warehouse
Module 2: Creating Dimensions and Changing Granularity of Dimensions
Module 3: Creating Fact Tables and ColumnStore Indexes
Module 4: Implementing Data Warehouse inSQL Server 2012
Module 5: Working with Integration Services
Module 6: Managing Control Flow
Module 7: Working with Dynamic Variables
Module 8: Implementing Data Flow
Module 9: Managing Data Flow
Module 10: Managing SSIS Package Execution
Module 11: Debugging and Troubleshooting
Module 12: Deploying Packages
Module 13: Securing Packages and Databases
Module 14: Working with MDS and Windows

Inline Activities
Supplemental Resources


SSRS 2014 – 18:45 hours

In this course you’ll get a high-level view of the parts and pieces of Reporting Services so that you know what you’re working with. I’ll start by looking at the two modes you can install and operate a report server in. Then I’ll show you some of the extensive configuration options you can use to fine tune reporting features. Next you’ll get a solid introduction to Report Builder, and learn how to install either the standalone version or the ClickOnce version accessible from Report Manager. Then you’ll learn about using Data Tools with Reporting Services to create report server projects. Next you’ll learn about the two report server project types, as well as the report development environment you get with the Report Designer. Then you’ll see what report project properties are available and how they work, as well as how to use them with Visual Studio project configurations. Next in this course we’ll start by taking a look at the tablix, and how it is able to morph into a table, matrix, and list at your whim, then we’ll put it to use and build a report using the component, exploring its features. Then you’ll learn the essentials of expressions, how to write and use them within reports. Then you’ll learn about the extensive set of built-in Reporting Services functions that you can use in your own expressions. To finish off the course you’ll learn about these aspects of report design so that your reports have an effective layout and online versions provide a level of interactivity that will make reports more useful for users. One of the key ways to add this kind of functionality is by creating dynamic reports with parameters. This way, you can very flexible reports, customized for what the user needs or something in the environment. Then we’ll cover Groups. Groups are a great way to organize data in a report into a more manageable assemblage of information. If you need to create subtotals or other statistics you will likely need to create groups.

Next you’ll learn about some of the issues you’re likely to face starting Report Manager and connecting to a report server. Next you’ll learn about the My Reports folder and then the course explores the various export formats supported by Reporting Services. Next we’ll look at accessing reports using a custom URL, and then you’ll learn about the ReportViewer control that is available in both Web and Windows Forms .NET applications. Next you’ll learn how to use the Report Server Web Service, and you’ll learn about the two endpoints that Reporting Services exposes, the management and execution endpoints. You’ll also see how to build an application using the Web services to display a list of reports and render format options. You’ll learn about reporting tools, starting with seeing how you can use Management Studio for some tasks. Then you’ll learn the three primary kinds of tasks you’ll need to use Management Studio for, as well as how to access those features. Then you’ll explore the management features of Report Manager. Next you’ll learn how to work with the security features of Reporting Services. You’ll start by learning how to authorize users to perform actions at both the server and folder level, as well as how to use Management Studio to manage report server roles. Then you’ll learn how to enable Basic authentication, necessary to allow report access via non-IE browsers and outside the local network. Next you’ll see how you can enable and optionally require the use of SSL (really TLS, or Transport Layer Security) anytime a user or application accesses Reporting Services. Then you’ll learn about how report data security hooks into the data security features of the core database engine of SQL Server. You’ll see how to require different kinds of credential for any data source, both in SQL Server Data Tools and in Report Manager. You’ll also learn about the permissions required to access data for a report.

SSRS 2014, Part 01 of 10: Introduction and Report Builder
SSRS 2014, Part 02 of 10: Creating Reports
SSRS 2014, Part 03 of 10: Tablix Report Type
SSRS 2014, Part 04 of 10: Expressions and Functions
SSRS 2014, Part 05 of 10: Layout, Parameters, and Visualizing
SSRS 2014, Part 06 of 10: Accessing and Subscribing to Reports
SSRS 2014, Part 07 of 10: Programming Report Access
SSRS 2014, Part 08 of 10: Web Service Programming
SSRS 2014, Part 09 of 10: Managing Reporting Services
SSRS 2014, Part 10 of 10: Reporting Services Security

Inline Activities
Supplemental Resources