The Hidden Power of SSMS - Advanced configuration for faster work and better results

Any DBA that manages SQL Server or Azure SQL works with SQL Server Management Studio (SSMS), but are you really familiar with the capabilities of SSMS?

In this session we will go over the built-in and lesser-known features and configurations in the SSMS software. We will build the most comfortable work environment by adding and managing the toolbars and expanding the capabilities of the software.

During this session we will use SSMS 19.x and we'll present how to control the tollbars, add and remove commands, Manage the Editor and View Mode, Manage Code Formatting, go over tricks and tip to best Navigate the Code and Text, Setting Editor Options, control the windows, work with projects, and a lot more.

This meeting is light, friendly and practical. The session is 100% demonstration which suitable for anyone who is just getting started with the software and for all the experts who have been working with the software for years and have not yet discovered all the hidden capabilities and power of the SSMS

SQL Server 2022 - New and improved features for better queries

SQL Server 2022 brings multiple enhancements to Transact-SQL language.

Some extensions in the T-SQL are implementing new features at the SQL Server engine behind the scenes (like AUTO_DROP statistics). Other enhancements provide options to store and analyze data that changes over time, using time-windowing, aggregation, and filtering capabilities (like DATE_BUCKET, GENERATE_SERIES, FIRST_VALUE and LAST_VALUE).

In addition SQL Server 2022 presents new and improve JSON functions, a new window function clause and new functions like GREATEST, LEAST and STRING_SPLIT.

In this session we will dive into the Transact-SQL enhancements in SQL Server 2022 and we'll present some tricks and tips on how to improved our queries in SQL Server 2022 and how to implement the same solutions in older versions.

This session is 100% practical demonstration!

SQL Internals - Physical Table Structure under the hood, and implementation on real case scenarios

Understanding what we have under the hood is not only done for the sake of learning theory, but it directly impacts your daily work, and it can help us to improve performance and reduce resources dramatically.

During this session, we will go over the structure of the tables behind the scenes. I will show the changes in the table structure behind the scenes, related to actions which we execute on the table. Using this information, I will demonstrate several real case scenarios and the dramatic impact of understanding the internals in choosing our solution.

Is the order of the columns in the "Create Table" statement important? Can a specific order of columns affect performance or lead to an error in the future use of the table? Is dropping a column that is no longer needed is the right solution? Questions like these are very common in forums, but usually do not get the right answers even from experts.

Note: This session is a live demo, level 500 internals. It based on undocumented tools.

This session is level 500 internals. It based on undocumented tools.

SQL Server Internals - Who need SQL Server when we have Notepad?

In this session, we will explore how SQL Server actually stores the values of the data behind the scene. We will read SQL Server data file directly using simple binary editor (notepad++), and we will explore the structure of several data types.

We will focus on exploring how UTF-8 is supported in SQL Server 2019 behind the scene, and we will explore how SQL Server stores other types of data (according to the time), starting with DateTime2 which is probably one of the most common complex data type structure and moving to other data types.

Note! During the demo, we are going to show and use several undocumented tools from different versions of SQL Server. This is advance topic!

Azure Data Studio: Dive into Microsoft new Database Management studio

Azure Data Studio is a Lightweight open-source cross-platform database tool, which does not require any installation and can run directly from external device. It supports Microsoft family of on-premises and cloud data platforms and has built-in versions for Windows, MacOS, and Linux. It offers rich T-SQL editor, Intellisense, code snippets, source control integration (Git), and an integrated terminal.

Azure Data Studio allows the users to manage built-in and custom Dashboards, add and edit "Insight Widgets", which present information on the server or database level. We can convert any user query into a Widget in less than 20 seconds, and we can present the result of any query in different formats including complex graphs with one click of the mouse

In this session, I will present the new tool and its main capabilities. I'll show you why I almost completely stopped using SSMS (SQL Server Management Studio) on daily basis, and I found my new best friend - Azure Data Studio (ADS)

Advanced data security (from 2016 to 2019 new features)

Data security is an extremely important topic today! Adding rules like the General Data Protection Regulation (GDPR) cannot be useful if we don't know how to implements these. In recent years Microsoft has focused on security.

Securing your data includes three main levels of tasks: Discover, Protect, and Detect. During the meeting we will review new tools and features for Discovery and classification of sensitive data, and vulnerability assessment of the data. We will go over Advanced Threat Protection using new features added to SQL Server from version 2016 to the new version of SQL Server 2019. And we will review "real-time threat detection" capabilities according to the time.

Let's see how you can lower the maintenance cost dramatically, strengthen data security significantly, and improve the procedure of configure, monitor, and administer the security of SQL Server and Azure SQL Database.

SQL Server 2019 New and improved features - fast review

Microsoft introduced SQL Server 2019 preview at Ignite 2018 conference in September. There are tens of new or improved features which come with the new version and it is time to review these features. During this session we will explore the top new features and functionality in SQL Server 2019 including UTF-8 support, Inline Scalar Functions, new System Tables, Adaptive Memory Grants, APPROX_COUNT_DISTINCT, enhancements in Security and Availability, and more...

Let's crack (bypass) some new security features!

SQL Server 2016 provides several new data security features like Dynamic Data Masking (DDM) and Row-Level Security (RLS). Implementing DDM and RLS internally in the SQL Server, aims to bring us better security.

DDM based on user level rules, is aims to prevent users without granted permission "UNMASK" to expose the original value of a column. RLS based on security policy which uses inline function in order to implement filter predicate on rows.

Are those features really keep your data secure as expected?

During this session we will discuss the limitations of those features, and we will show how simple it is to bypass(crack) the security features and expose the data, by any user that has simple select permission.

The ability to implement security features gives nothing without recognizing the limitations and its vulnerability. This is a must session for anyone who uses or intend to use the new security features!

SQL Server Internals - Binary (Stored) format of the data

There is a common confusion regarding "displayed format" which is related to the client side, and "stored format" which is the way SQL Server stores the data on disk in binary level (zeros and ones).

Let's go briefly over SQL Server internal architecture from the server instance, databases, and files and we'll dive into the internal structure of the file, focusing on the way SQL Server stores data values on disk, in binary level.

who need SQL Server when we can open and read SQL Server Data file using notepad?

In this session, we will explore how SQL Server actually stores the data behind the scene. We will read SQL Server data file directly using simple binary editor (notepad++), and we will explore the structure of several data types. We will focus on exploring how DateTime2 data type is stored, which is probably the most common complex data type structure, and we will discuss other types in general (according to our time). During the demo, we are going to show and use several undocumented tools from different versions of SQL Server.

Understanding the internals gives us the power to get the best decisions, improve performance, and deal with advanced issues like corrupted files.

* This is advance session which focus on undocumented information.

Improving performance and compatibility by using UTF-8 (New in Azure Database and SQL Server 2019)

In order to support UNICODE, special data types like NVARCHAR and NCHAR, were added in earlier versions of SQL Server. Originally, those data types used UCS-2 encoding, which support 65536 code points using 2 bytes. UTF-16 encoding was added to support 1114112 code points using 2 or 4 bytes.

Azure Database and SQL Server 2019 introduced new Supplementary Character "_UTF8" to support UTF-8 encoding using non-"National Character" Data types like CHAR and VARCHAR.

UTF-8 encoding provides full compatibility to other data types, to external applications, and to different Operating Systems. Using UTF-8 can reduce data size dramatically up to 50% and potentially improve performance accordingly.

In this session we will start with theory and best practice of textual data types and different types of encoding (ASCII, UCS-2, UTF-16, and UTF-8), and will dive to "SQL Server internals" behind the scenes.

* Note! The meeting fits developers, ‎Database administrators, and Architects. We'll review the theory (level 200) as well as the internals (level 400).

Azure SQL: What to use and when - overview and real case scenarios

Come learn about the Azure SQL family and how to lower expenses, improve performance, and reduce daily work!

Choose the solution best suited for you, starting with (1) deployment options: single database, elastic pool, managed instances, managed instance pool, SQL on Virtual machine; (2) architectural models: Bundled packages, General Purpose, Hyperscale, and Business Critical; (3) pricing models: DTU or eDTU Pricing tiers, Provisioned Compute resources based on vCore and size, serverless compute tier based on auto-scaling of the compute resources; (4) generation of hardware.

We will review the theory and the main features, we will discuss real case scenarios and how to choose the service suits you best, and we will present tips and tricks to lower expenses while improving performance and reducing maintenance.

Level 300

Spatial Data Type for the rescue! hierarchy, ranges, gaps and islands, and more

SQL Server provides powerful Spatial Indexes efficiently perform certain operations on geometry or geography data type.

It's pretty obvious that scenarios related to geometry or geography are usually best solved using spatial data types, but did you ever thought about harness the power of geometry data type to solve other scenarios?

There are a great number of scenarios that are not related to spatial data, which we can simulate as a geometric structure. For example, why not to present Hierarchical data as a geometric structure where the children are Polygons inside "a father Polygon", or present Ranges of times as line segments. We can even presents known scenarios of "gaps and islands" as actual islands in a map.

In this session I will introduce original advanced algorithms that use a geometric data type to solve scenarios such as hierarchy, ranges, gaps and islands, and more.

This session is a 100% practical demonstration!

Session level is 400; Target audience includes architects and DBA; Session duration is 60 minutes.

SQL Server Internals – tricks to fix corruptions

Understanding SQL Server internals provides us the power to do more and get more. Using undocumented features and the knowledge of the binary structure of the data file and transaction log file under the scenes, we will examine corrupted databases, and I will present different possible solutions to to fix the database without (or with minimum) data loss.

This is session level 500 internals. We will focus only on optional corruptions in databases.

level 500 internals. Session duration 60/90 minutes.

The Internals of the External Sqlpackage Tool - Inside a Bacpac File

Using bacpac file is the most common procedure to migrate databases to/from Azure SQL Database, Azure Managed Instance, and SQL Server. A bacpac file can be created using tools like SSMS, SQL Azure Migration Wizard, and SSDT using Visual Studio, or directly executing the command-line utility SqlPackage.exe.

In many cases, problems arise while creating the new database from the bacpac file, even when the file was created without any error or alert. This can be related to implementing different criteria when we create a new bacpac file from these which are used during the import procedure, and it can be a result of incompatibility between the source and the target databases.

If an error is raised during the import procedure, which is related to a missing or not supported entity, then we can go back to the source database and re-create the bacpac without that entity. Unfortunately, if we have multiple issues with the bacpac file, then we might get alert only about one of these. Therefore, we might need to go over the issues one-by-one and fix the issues each time after we get information about another issue. This can lead to tens of iterations between attempting to create a new bacpac file (export) and attempting to create the database from the file (import).

In this session, we will examine the internals of the bacpac file, and we will go over some advanced troubleshooting. The bacpac file is a simple compressed ZIP file with the extension of "bacpac". It contains multiple files, which can store the information about schema and data of the SQL Server database.

Understanding the internals of the bacpac file allows us to edit the file manually, bypass incompatibility of the bacpac file to the target environment, change the target database imported entities, and prevent errors while migrating the database, as we will demonstrate in this session.

level 400

Choices, choices: Using Unicode in Azure SQL Database

As businesses and applications reach global scale, you know that Azure SQL Database can support international applications using Unicode. And you’ve been working with SQL Server for long, so handling and storing strings is not a mystery. Or is it? It depends – it’s all about knowing what’s there, and making informed choices when you design the database.

In order to support UNICODE, special data types like NVARCHAR and NCHAR, were added in earlier versions of SQL Server. Originally, those data types used UCS-2 encoding, which support 65536 code points using 2 bytes. UTF-16 encoding was added to support 1114112 code points using 2 or 4 bytes.

Azure SQL Database and SQL Server 2019 introduce support for UTF-8 and with that, deliver on a decade old request.

In this session we will fix some misconceptions about concepts like characters, SET characters, code points, coding characters, SET code, coding values, collection. Learn what encoding you would like to use for your scenario and how to apply it, and the relationship between collection, data type, and encoding. Learn what the differences are in performance and even start some SQL Server myths along the way.

Level 300-400

Unstructured Data in Structured databases (theory, In-depth discussion, and practical tricks)

Structured data is data that adheres to a pre-defined data model and conforms to a tabular Relational Database Management System (RDBMS). SQL Server is known as an RDBMS server, and as such when we design our database and our queries, we treat the data as values stored in a table structure of columns and rows.

In fact, the boundaries of what we can do directly using SQL Server are beyond the relative database model query. These boundaries are gradually blurring from the early days of SQL Server to the latest version of SQL Server 2022, with new types of data and new features being added to new versions.

In this session, we will review how SQL Server supports Unstructured Data (different data types and features), and we will get into an in-depth discussion on the question what makes SQL Server a Tabular Database behind the scenes (Is SQL Server really stores tables on the disk?) and what is the difference between managing data in Tabular model to managing same data in NoSQL models.

Once we break the psychological boundaries between tabular and unstructured data, we will be able to think outside the box and take our daily work to the next level. In the second part of the session, I will present tricks you didn't think of to improve performance dramatically using unstructured data types to solve needs related to simple structured data types. For example, did you thought using Documents data types to avoid loops, or using Geometry Data type to solve dates ranges issues or "Gaps and Islands" issues, and what about designing the optimal solution for hierarchical data?

This session is for 90+ minuts

Break the boundaries of the traditional (T)SQL language

While the traditional DBA role that works with RDBMS databases focuses on running queries against tables, in practice, SQL Server provides powerful tools that allow to connect and to control the host environment and to process and analyze the data using embedded external tools/languages.

I will open this session with a review of some of the documented tools and features, followed by a deep dive into undocumented tools and new features added in recent years up to SQL Server 2019.

This session is an overview that is suitable for anyone who wants to confirm that he is aware of the options available or for those who just want to fill in the blanks. We will review tools like xp_cmdshell, SQLCLR, Python, R, Java, Undocumented Registry extended Stored Procedures, working with Component Object Model (COM), and more...

level 300-400

Unstructured Data in Structured databases - practical tricks

SQL Servers provide features to support non-Relational Model, like: JSON and XML documents, Geometry and Geography data, Key-value parameters, queues and messages, complex relations using graph model, and many more.

For example, if we need to calculate the surface area of a geometry shape, then we will use a built-in Geometry functions like STArea; If we need to process the content of XML document then we will use XML Methods like query(), value(), exist(), modify(), nodes(), and XQuery.

But what about using the power of these features for working with simple tabular data.

In this session I will present tricks you can use to improve performance. For example, using the power of Geometry data type and special Indexes for "Gaps and Islands" issues, finding gaps between dates, working with ranges, or designing the optimal hierarchy structure. We will go over tricks for splitting a string or concatenation of strings (with guaranteed order) using XML or using JSON, trick for scheduling jobs without SQL Agent, and many more daily tasks, which we will solve using Unstructured Data features in Structured databases.

It's time to think outside the box!

SQL Server Internals - Binary (Stored) format of data types

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies how the data is used (in the client layer) and how the data is stored behind the scenes in the form of Zeros and Ones (storage characteristics and storage format - in memory and on disk).

In many cases people confuse between these layers and speak about the "Data Storing format" while they mean "Data Displaying format" or vice versa. Another misconception is that the data in table is stored on the disk in the exact same format as the data on-the-fly (for example binary value in a column type DATETIME2 and a variable of Data type DATETIME2 are not exactly the same).

This session focuses on the internal layer of SQL servers. We will explore the "Data Storing format" of different Data Types, and we'll learn how SQL Server actually stores the data behind the scene (cover as many data types as the time will allow us).

This knowledge opens powerful options to improve performance by selecting the right best practice, deal with corrupted data, working with detached data files, and more.

Note! During the demo, we are going to show and use several undocumented tools from different versions of SQL Server. This is advance topic (level 500 internals)!

Nondeterministic Elements, Tips and tricks

Deterministic elements always return the same result any time they are called, with the same input. SQL Server include built-in system functions, stored procedure and other non-deterministic elements. The use of those might result in unexpected behavior. For example, Non-deterministic functions may return different results each time they are called with a specific set of input values.

In this session we will demonstrate several examples, we will identify the determinism of built-in system functions, and the effect on the deterministic behavior of user-defined elements and T-SQL scripts. We will focus on showing tricks and tips on how to work around those behaviors in order to make our system more consistent.

hierarchyid, graph, and Spatial Data types for the rescue!

Managing complex relationships in tabular database can be challenged. A relational database provides a basic solution by creating Single-to-Single or Single-to-Many relationships directly using primary key and foreign key.
This session is NOT about the theory but about practical solutions. In this session, I will demonstrate various methods and original tricks to improve working with complex data structures and complex queries.
We will discuss how we can use the built-in hierarchyid data type, Spatial Data types, and graph database capabilities to solve Many-to-Many relationship and other complex queries. We will show how we can use Spatial Data to solve “Hierarchical structure”, “gaps and islands”, and “ranges” problems.
This session is 100% practical solutions!

Choices, choices, choices: Using Unicode in 2020

As businesses and applications reach global scale, you know that SQL Server and Azure SQL Database can support international applications using Unicode. And you’ve been working with SQL Server for long, so handling and storing strings is not a mystery. Or is it? It depends – it’s all about knowing what’s there, and making informed choices when you design the database.

SQL Server 2019 and Azure SQL Database introduce support for UTF-8 and with that, deliver on a decade old request – in this session, learn about what Unicode encoding you want to use for your scenario, what are the performance differences, and even debunk a few SQL Server myths along the way.

The Power of Power Query in a Real Case Scenario - using Query Editor and Excel

Learn how to leverage the diverse abilities of Power Query to acquire, discover, and reshape complex JSON structure data into Microsoft Excel.

Power Query is a tool to discover, define, reshape, and import the data from a wide variety of data sources. There are tens of connectors currently available for Power Query which we can use to load data into either Power BI Desktop or Excel. The core capability of Power Query is to mash-up data (filter and combine) using the Power Query M Formula Language.

Our journey will begin with simple example and a bit of theory, moving to a real case scenario where we will use a complex JSON downloaded from web API and include unknown multi-levels hierarchy structure.

We will use "Power Query Editor" to discover and define the data and we'll combine elements of the M language in Power Query to reshape the data, before presenting the visualized data in Microsoft Excel.

Come learn to use the power of Power Query with Microsoft Excel, Query Editor, and some tricks to be better prepared to tackle any size or shape of data that comes your way.

level 300-400

Azure Logic apps for Azure Database DBAs

The work of database administrators does not stop at the database level. There are many tasks that require the use of external tools to maintain and improve our databases. These tasks can be very challenging when we use a fully managed platform as a service (PaaS) such as Azure Database or Azure Elastic Pool.

Azure Logic Apps for the rescue! Azure logic apps and the Azure Database can interact in a two-way way. The database can trigger execution of logic apps and a logic app can trigger read and manipulate of the data stored in the database. For example, Azure logic Apps allow us to send email notifications triggered by DDL and DML actions in the database, it allows us to monitor database usage and database events, and it allows us to read the data stored in the database and use it in the content of the email.

For whom? Azure SQL Database is a fully "serverless" managed platform as a service (PaaS). This makes it very simple to be used without any need to maintain the host. It has a fully functional GUI using the Azure Portal, which means that we can use it without writing any code. If you are a DBA and you manage Azure databases, then this session is for you. No previous experience in developing applications is needed.

In this session I will show you how to send emails using Logic apps and interact with Azure SQL databases. I will go over common scenarios, and present practical examples with step-by-step guidance for implementing the solution. According to the time, I will show some tricks, tips, troubleshooting and automated maintenance tasks using Azure Logic Apps and the new Azure Logic App (Preview) services...

This session is a practical demonstration!

Session level 300-400; Target audience: DBA and developers. Time: 60 Min

Parsing SQL Server transaction log file for practical solutions

Each SQL Server database has a transaction log that records all transactions and database changes made by each transaction. Transaction log files are intended for internal uses of the server and not be accessible to users.

This is not 'another session' about the SQL Server transaction log Architecture or how SQL Server uses the transaction log! This session is about how we can read and parse the contents of the transaction log files for practical solutions in real-case scenarios! In this session, we will go over questions that arise in forums from time to time and solutions that are based on thinking outside the box. We will utilize the information that is inaccessible in conventional ways and is intended for internal use, utilizing undocumented tools.

Case scenarios that we will investigate include parsing the log file and the backup files; finding the OS user which created a specific table; knowing who deleted data from a table; a solution for the error "Online index operations can only be performed in Enterprise edition of SQL Server" while running Transactional Replication from SQL Server Enterprise to SQL Server Standard; finding unused tables; restoring data manually from the transaction log file; and more...

This session is level 500