Azure SQL DB Backups - Isn't Microsoft taking care of that?

Most people think they no longer have to worry about backups once they move to the cloud. However, there’s a good chance the default settings aren’t enough and may violate company policy or government regulations. In this session, we will cover why and how you can manage backups in Azure.

Do you know what the default backup settings for Azure SQL Database are? What is the maximum point-in-time backup retention you can set, and what happens if you need to retain the backup for years due to regulatory requirements? What kind of redundancies do you have for backup files? What restore capabilities do you have? How do you monitor backup costs? How do you test your restore to ensure a good backup, since "Your backup is as good as your restore."

If you need to know the answers to these questions, this session is for you. I will answer all these questions and more with a live demo of setting up retention, restoring cross-region from Geo-Redundant backup, and querying backup consumption while calculating cost.

Prerequisite: Basic understanding of backup and restore of SQL Server Databases

Goals:
1. Understand the default Azure SQL Database backup settings for retention and redundancy.
2. How to change those settings to comply with your business and regulatory requirements
3. How to restore cross region using Geo-Redundant backup

(Lightning talk) Break five common myths about SQL Server backup

In this session, I will discuss five common myths about SQL Server backup and demo each of them to show you the correct answer.

1. Does Full and Differential backup break the log chain?
2. Are Differential backups incremental?
3. What backups are allowed on system databases?
4 Is transactional backup necessary during full backup?
5. Does backup use a buffer pool to read data pages?

(Lightning talk) Notebook Jobs - end to end demo

As database professionals, we all know how to manage SQL Server Agent jobs using SQL Server Management Studio (SSMS), TSQL, and PowerShell. With Azure Data Studio and SQL Server Agent extension, we have new tools to create, maintain and manage SQL Agent jobs using Notebook. These are called Notebook Jobs. Once you successfully execute a Notebook job manually or via schedule, each run's results are saved into a separate Notebook, which you can easily share with others.

In this 100% demo session, I will walk you through every step of Notebook Jobs.

(Lightning Talk) Analyzing Azure Monitor Log data in Azure Data Studio

How do you consume the data once you enable event logging (auditing and diagnostic) for the Azure SQL database? How do you find anomalies to alert on, establish a baseline, look at trends?
In this 100% demo session, I will show you kql magic, kql kernel, and the recently released Azure Monitor Logs extension for Azure Data Studio to consume event logs of the Azure SQL database.

Think like the Cardinality Estimator

SQL Server uses a phase during query optimization, called cardinality estimation (CE). This process makes estimates based on the statistics as to how many rows flow from one query plan iterator to the next. Knowing how CE generates these numbers will enable you to write better TSQL code and in turn, influence the type of physical operations during query execution. Based on that estimated rows, the query processor decides how to access an object, which physical join to use, and how to sort the data. Do you know how the CE generates these numbers? If your query has only one predicate, the query optimizer will use the histogram to estimate how many rows will be qualified. What happens when you have multiple predicates, range predicates, variable values that are “NOT KNOWN” to the optimizer, or you have predicate values increasing in ascending order? Do you know what will happen if your predicate is using an amount that is outside of the histogram range?

In this session, I will show you how the cardinality estimator estimates in all of these scenarios. You will walk out of this session with a clear understanding of how the CE generates its numbers and is ready to tackle those nasty, hard-to-solve query plans.

What the heck is a checkpoint, and why should I care?

In SQL Server, a checkpoint is an internal process that writes dirty pages and transaction log records from memory to disk and marks a point in the transaction log. An 8K page is the fundamental data storage unit in SQL Server.

SQL Server performs every data modification operation in memory (buffer pool) for performance reasons and does not immediately write it back to disk.

This is where checkpoints come into play. There are four types of checkpoints, automatic, indirect, manual, and internal. The Database Engine periodically issues a checkpoint on each database based on the current setting to help reduce the recovery time of a given database from unexpected shutdown to system failure.

This session will explain why you should care and know about the checkpoint process and the different checkpoints that SQL Server does. I will show you exactly what happens during a checkpoint, how you can influence the interval of checkpoints, and changes made with checkpoint settings in SQL 2014 and SQL 2016+.

Considerations for migrating SQL databases to Azure

Many tools are available to migrate your on-premises database to an Azure SQL database. Are you familiar with all those tools, and how do you choose the best tool? How do you analyze and identify what objects are incompatible with migrating to Azure? Answer: It depends (of course) on the database type, size, and complexity you will be relocating.

This session will explore considerations before migration, appropriate targets, migration tools available, and the pros and cons of each tool. I will demo four tools you can use to analyze/migrate your on-premises SQL Server Database to Azure SQL.

At the end of this session, you will know the various techniques available to analyze and migrate SQL Database to Azure and choose the best fitting one for your database.

Azure SQL Database-Business Continuity During Disaster

Azure SQL Database is a relational database-as-a-service (DBaaS) based on the latest version of the Microsoft SQL Server Database Engine. You can build data-driven applications and websites in the programming language of your choice without needing to manage infrastructure.

While considering Azure SQL Database, two of the most critical questions you need to answer are my options for business continuity and how to implement these options?

In this demo intensive session, I will discuss five specific options and show you how to implement those. You will walk out of this session equipped with knowledge and scripts to implement appropriate High Availability and Disaster Recovery solutions for your Azure SQL Database.

Azure SQL Database - Where is my SQL Agent?

You migrate your on-premises SQL Database to the cloud, taking advantage of the PaaS offering of Azure SQL Database because SQL Managed Instance seems a bit too much for what you require. You heard the promise of spinning up databases on-demand, scaling up resources during high peaks, and scaling down when unused. You also want to ensure you perform integrity checks, index defragmentation, and statistics updates when necessary. But you opted for an offering with no SQL Agent, so how do you automate your jobs?

Do you have time to do this manually each time? No. Different options are available to automate these long-running, manual, error-prone, and frequently repeated tasks to increase efficiency.
In this session, which is full of demos, I will show you six ways to automate these tasks. Some of these solutions use your own infrastructure or Azure services that you can access from the Azure portal experience.
At the end of this session, you will have a good understanding of how to automate Azure SQL Database Maintenance tasks, including replacing SQL Agent functionality with multiple options.

Prerequisites:
Familiarity with Azure SQL Database (PaaS). Understand what SQL Server Agent does for on-premises SQL Servers.

Goals:
1. You will learn six options to automate maintenance tasks against Azure SQL Server (PaaS).
2. Once you familiarize yourself with the different options, you can compare them and decide which suits your environment.

Need to Monitor Any Azure Resource? Must Learn Kusto Query Language

Kusto is a service for storing and running interactive analytics over Big Data. Kusto was designed from scratch to be a “big data” repository for Azure and easy to query using Kusto Query Language (KQL). As we progress in our migration to the cloud, we learn new ways to monitor and alert resources and services. Microsoft has consolidated many services under the umbrella of ‘Azure Monitor.’ Whether you are detecting and diagnosing issues across applications and dependencies,’ ‘correlating infrastructure issues with Azure Monitor for VMs,’ ‘or drilling into your SQL database monitoring data with Log Analytics, you will be using ‘Azure Monitor.’ Azure Monitor uses a version of the KQL used by Azure Data Explorer that is suitable for simple log queries and includes advanced functionality such as aggregations, joins, and intelligent analytics. As we advance, the KQL must be your primary resource for querying the Azure Monitor log.

This 95% demo session will show you some ‘getting started’ tips and a few sophisticated queries using KQL. I will do a live demo, generating an alert using KQL. I will dive into Kqlmagic, which brings you the benefit of notebooks, data analysis, and rich Python capabilities in the same location against Azure Log Analytics workspace. At the end of this session, beginners will have a solid knowledge of KQL that they can build upon by themselves; others will also learn many advanced operators, including machine learning ones. Come and learn about the future of monitoring and investigations of Azure services.

The magnificent seven and beyond- Intelligent Query Processing in SQL Server

Can we fix query performance issues without any code changes? Making application changes can be an expensive undertaking or entirely out of your control. Hence, developers and DBAs want the query processor to adapt to their workload needs vs. using options and trace flags to gain performance. Adaptation is the idea behind Intelligent Query Processing (IQP) in the newer versions of SQL Server. In this demo-heavy presentation, I will walk you through seven intelligent query processing features introduced in SQL Server 2022, 2019, and 2017. I will help you identify features built on top of the Query Store. We will look at the pros and cons of using these features and control them if you are not ready to utilize them.

Attending this session will teach you the new capabilities of intelligent query processing and equip you with powerful tools to convince your peers to upgrade SQL Server and databases to the latest build on-premises and in the cloud.

Prerequisites:
A basic understanding of query processing, familiarity with estimated and actual execution plans, and knowledge of measuring query execution performance are required.

Goals:
1. Learn about the members of Intelligent Query Processing that have developed since SQL Server 2017
2. Understand what kind of issue each feature solves and how it does so.
3. How to enable or disable these features at the server, database, session, and query level

Analyzing Azure Monitor Log data for Azure Resources

Kusto is a service for storing and running interactive analytics over Big Data. Kusto was designed from scratch to be a “big data” repository for Azure and easy to query using Kusto Query Language (KQL). We learn new ways to monitor and alert resources and services as we migrate to the cloud. Microsoft has consolidated many services under the umbrella of ‘Azure Monitor.’ Whether you are detecting and diagnosing issues across applications and dependencies,’ ‘correlating infrastructure issues with Azure Monitor for VMs,’ ‘or drilling into your SQL database monitoring data with Log Analytics, you will be using ‘Azure Monitor.’ Azure Monitor uses a version of the KQL used by Azure Data Explorer.

In this 100% demo session, I will show an example with Azure SQL Database, which you can use for any Azure resource:
1. How to enable event logging (auditing and diagnostic) for the Azure SQL database?
2. How do you consume the data once you enable event logging?
3. How do you find anomalies to alert, establish a baseline, and look at trends?
4. How do you consume this data with python and kql notebook from Azure Data Studio using kql magic, kql kernel, and the recently released Azure Monitor Logs extension?

No slide. All demo. Will need an internet connection.

360-degree Overview of Backup and Restore

If you are the database steward, your most critical task is to guarantee that all committed transactions are always recoverable during a disaster within acceptable limits for data loss and downtimes.

Achieving this can be simple by taking a full backup or complex, which might include filegroup backups based on the size and criticality of your application data.

Whatever your situation is, being well-prepared and practicing with your tools, scripts, and strategy will ensure you can respond quickly and efficiently when a disaster happens.

In this session, I will teach you all the basic types of backups and how to create backups and restores using SSMS and TSQL. Then we will move to advanced techniques, discussing file and filegroup backups, partial database restore, and T-SQL snapshot backups introduced with SQL Server 2022.

At the end of the session, you'll be able to create a solid Backup and Restore strategy around the agreed service level agreement with your business counterpart.

Lifting Your Data Skills to the Cloud

94% of enterprises use cloud services. 67% of enterprise infrastructure is now cloud-based. 92% of businesses have a multi-cloud strategy in place or in the works. What does that mean for data professionals working with on-premises technology for years? Do we have to relearn everything from ground zero? How can we leverage the knowledge and experience we have acquired over the years and apply it to the cloud?

If you are thinking about these questions, this session is for you. Many responsibilities have become shared between database administrators and the cloud provider. For example, in the PaaS model, the cloud provider will take a database backup, and setting up long-term retention is the responsibility of the database administrators.

We will discuss these critical areas of database administration (System provisioning, Data Migration, Data Security, Performance tuning and monitoring, Disaster Recovery, High Availability, Backup and Recovery, and Cost management). I will show/explain what existing skills we will need and what new ones you will need to learn. I will also cover tools provided by cloud providers that you will need to learn and use.

This session will equip attendees with the information they need to successfully administer databases in the cloud.

An attendee will walk away knowing three things:
1. List of on-premises skills they can retain and apply to the cloud.
2. For each of the critical areas of database administration, what new skills will they need to learn to be a successful "Cloud DBA"?
3. Tools provided by cloud providers (Microsoft) that they will need to learn and use.

Build Multiple Fences Around Your Azure SQL Database

Guarding a resource against known threats may seem manageable, but the unknown attack patterns instill fear. Cloud computing, essentially entrusting data to "someone else's computer," necessitates a vigilant partnership between you and your cloud provider to ensure resource security, especially concerning customer data.
In this session, I'll explain a comprehensive layered defense strategy, starting from the outermost layer and working inward. This strategy will empower you to safeguard sensitive data and uphold your database system's confidentiality, availability, and integrity, even if one or more outer layers are breached.

The layers of defense encompass:
• Network Security
• Access Management
• Threat Protection
• Information Protection

For each layer, I will demonstrate some of the tools and resources you can implement to ensure the safety of your customer data, such as IP and Virtual network firewall rules, Microsoft Entra, and Microsoft Defender for Cloud.
We will also discuss mechanisms for detecting unusual behavior and potentially harmful attempts to access or exploit databases. This includes identifying suspicious activities such as SQL injection, potential data infiltration, brute force attacks, or anomalies in access patterns and setting up alerts to catch privilege escalations and breached credentials.

Prerequisite:
You need beginner-level skills in Azure SQL Database, database authentication, authorization, and access control.

Goals:
1. Compare database security responsibility in traditional and Cloud-Enabled methods.
2. Tools for each of the four levels of security.
3. How to set up and use these tools and prevent data loss, revenue reduction, and harm to business and brand reputation.
 

SQL Server Detective: Investigating Logs and Traces for Optimal Performance

As a Microsoft SQL Server DBA, monitoring and analyzing your server’s performance and troubleshooting issues is essential. One of the most effective ways to do this is by utilizing the built-in data collection, which records a wealth of information.
In this session, we will explore six collections (Logs and traces) that can provide valuable insights into your server’s configuration, health, and performance.

• Default trace
• SQL Error log
• SQL Agent Error log
• Cluster log Always On availability group
• AlwaysOn_health
• System_health

I will show you each one's default location, retention, and content captured. We will discuss how to access and interpret these logs and demonstrate how they can be used to identify and diagnose issues such as failed logins and deadlocks, the health of the Windows cluster and availability group, and schema changes. By effectively utilizing the SQL Blackbox, you can ensure the smooth operation of your server and maintain the highest performance and reliability.

Prerequisite: Familiar with SQL Server administration

Goals:
1. Know the default location, retention, and content of the six built-in SQL Server logs/collections.
2. Know how information from these collections can be extracted for different troubleshooting scenarios and ensure reliable and highly available database systems.

(Lightning talk) go-sqlcmd: A CLI for SQL Server and Azure SQL

go-sqlcmd is a powerful open-source, cross-platform, command-line tool with no ODBC dependency. It maintains all the features of the traditional SQLCMD utility while introducing enhancements. For example, it allows you to create a local SQL Server instance with a sample database with one line of code.

The Go package (go-mssqldb driver) is designed to seamlessly interact with Microsoft SQL Server, Azure SQL Database, and Azure Synapse, enabling users to enter Transact-SQL statements, system procedures, and script files at the command prompt12. Whether you’re a seasoned database administrator or a developer, go-sqlcmd streamlines your SQL-related tasks, making it an essential addition to your toolkit.

Join me in this lightning talk to discover essential features and how go-sqlcmd simplifies SQL interactions and empowers your database workflows!

Prerequisite: None

Goals:
1. Learn about the essential functions of go-sqlcmd
2. How to install and query SQL instance using go-sqlcmd
3. How to manage context