Course: Starting with SQL Server and Azure SQL Database (SQL-A)

Summary

No matter how much ado is currently about big data, semi and unstructured data, the appropriate place for the most important data is still the relational database management system (RDBMS). If you need to take care about the data integrity, then you should use the relational model as the logical model for your data and database schema. You have to learn the standard language for working with data in a RDBMS, the Structured Query Language (SQL).

In this course, you will learn the basics about the relational model, including normalization and constraints. You will start using the language that SQL Server and Azure SQL Database understand, the Transact-SQL (T-SQL) language. You will learn how to write basic and advanced queries. You will see how to create tables and insert, update, and delete data. You will learn how to handle errors and define user transactions. You will also understand the programmatic objects in a SQL database, including views, functions, stored procedures, and triggers.

In this introduction to the course, you will also get the information on the products and demo database used for the course, how to obtain, and how to install them.

Abstract

You will start your journey with the mighty SELECT statement. In the first module, you will learn how to write simple queries. Then you will go in depth with the SELECT statement and learn how to write more advanced queries.

In order to store your data, you need to create objects and populate them. You will learn how to create schemas, tables, and other objects. The SQL Server system data types overview is a part of this module as well. INSERT, UPDATE, DELETE, and MERGE statements are the data modification elements of the T-SQL language.

The seminar then switches to the theoretical background. You will learn about the relational model and how to design a normalized database following the relational model. You will also learn how to enforce data integrity. For analytical applications, you typically model your data differently. A module in the seminar introduces the dimensional model, the most widely used model for centralized and self-service reporting and analyzing your data.

Going back to practical database development. You will get an initial knowledge about the programmatic objects inside a database, why to use them, and how to create them. In the final module, you will learn how to handle errors and define your own transactions.

Modules:

1. Core Transact-SQL SELECT statement elements
2. Advanced SELECT techniques
3. Data Definition Language statements
4. Data Modification Language statements
5. Introducing the relational model
6. Normalization and constraints
7. Dimensional modeling
8. Programming views, functions, stored procedures, and triggers
9. Error Handling and Transactions

Course: SQL Database Internals, Query Optimization, and Transactions (SQL-OT)

Summary

Even the best logical model can’t help when the physical implementation is bad. Therefore, it is important to know how SQL Server, Azure SQL Database, and partially also Azure Synapse Analytics, store data in row and columnar format and how the engines access this data. You will learn about heaps, balanced trees, and columnar indexes. In addition, this course introduces query optimization for both, transactional and analytical queries.

In order to achieve a good compromise between data integrity and performance suitable for the business needs, it is essential to select the appropriate transaction isolation level. A developer needs to thoroughly understand the possible problems by selecting an inappropriate isolation level. Fortunately, SQL Server offers all possible isolation levels with both, optimistic and pessimistic locking. Memory-optimized tables do not use locking, but introduce their own set of possible issues. With locking hints, it is possible to control the locking on a lower granularity than with the session-level options. The seminar explains the possible problems and shows how the problems are solved with different transaction isolation levels.

Basic familiarity with SQL Server is recommended for this course. If you are a complete beginner in this area, then we recommend that you watch the Starting with SQL Server and Azure SQL Database course first.

Abstract

You will learn about physical structures in a SQL Server database, including pages, extents, heaps, and balanced trees. By knowing the internals, you will be able to use the appropriate structure and design optimal indices. The seminar will guide you how to write efficient queries and support them with indices for a transactional system. Optimizing storage and queries for analytical applications is many times quite different than for transactional applications. In this module, you will learn about the columnar storage inside SQL Server and how to write efficient analytical queries.

In the pessimistic concurrency approach, locks are used to prevent users from modifying data in a way that affects other users. Once a lock has been applied, other users cannot perform actions that would conflict with the lock until the owner releases it. Locks are used for reading and writing. Only one copy of data elements at any time. Update conflicts can’t occur.

In the optimistic concurrency approach, multiple copies (with multiple versions) of data elements are created. The copies are from the state before updates for on-disk tables and show the new data for in-memory tables. Locks might be used for writing only for on-disk tables. Readers read stored old versions of data elements. Update conflicts can occur. Memory-optimized tables always use the optimistic approach. There is no locking at all. The module starts by introducing the memory-optimized tables. The worst issue with the locks is deadlocks. The module explains how a deadlock can occur, and shows how to solve it. Then the module introduces table-level locking hints.

Modules:

1. Table and index structures
2. Optimizing queries for a transactional system
3. Columnar storage and optimizing analytical queries
4. Pessimistic isolation levels
5. Optimistic isolation levels
6. Memory-optimized tables and isolation levels
7. Deadlocks and locking hints

Course: SQL Server Security (SQL-S)

Summary

Security is maybe the single most important area of contemporary IT operations. SQL Server can help you securing your data in many ways. This course teaches database administrators and database developers about all of the SQL Server security features available.

Abstract

SQL Server has numerous options for tightening the security in all stages of a database lifecycle, starting with preparing a secure environment. SQL Server helps you configuring minimal surface area and assess the vulnerabilities of your database. Then you continue with defining the core permissions in your database – who can see and who can modify which part of it. You can enhance the security by using the programmable objects. SQL Server also enables very detailed column-level and row-level predicate permissions. Encrypting the data can be done in different ways, including the Always Encrypted option, where even the database administrator (DBA) cannot see the data. Instead of encrypting, you can also dynamically mask data.

Preventing data leaks is one part of a secure system. Auditing access to the data might be equally important. SQL Server gives you auditing capabilities out of the box. You can also track all changes of the data and get the state of the data in any time point in the past with system versioned tables. Finally, you can also classify your data in order to inform users that they are dealing with sensitive data.

Modules:

1. Introduction to SQL Server security
2. Preparing a secure environment
   • Surface area configuration
   • Vulnerability assessment
   • Policy-based management
3. Classical permissions model
   • Defining principals and securables
   • Managing schemas
   • Object and statement permissions
4. Row-level security
   • Using programmable objects
   • Code injection issues
   • Predicate-based RLS
5. Encryption
   • Backup encryption
   • Encrypting on a column level
   • Transparent data encryption
   • Always encrypted
6. Auditing
   • Using SQL Audit
   • System versioned (temporal) tables
7. Soft methods
   • Dynamic data masking
   • Data discovery and classification