Monthly Archives: September 2022

T-SQL Tuesday #154 Invitation – SQL Server 2022

Posted on by

Introduction

This is my first time participating in T-SQL Tuesday. The Invitation from Glenn Berry asks to write about what you’ve been doing with SQL 2022.

Getting DBA Dash ready for SQL 2022

I was eager to test DBA Dash with SQL 2022 and started testing with the first public release (CTP 2.0). I added a SQL 2022 instance to my lab environment, created using AutomatedLab.

Unlike my other lab machines, I ran through the installer for 2022 manually to get up and running quickly and test the new installer.

DBA Dash only needed some minor modifications to work with SQL 2022. The first and most important step was to add version 16 to the hardcoded list of supported versions and also update some static data for SQL Server 2022. I hardcode the list of supported versions to give me the opportunity to test new versions first and fix any issues.

This initial support for SQL 2022 was added in May. I’ve been running DBA Dash in my lab environment since then and it works well.

DBA Dash – support for new features in SQL 2022

QAT_DEFLATE Backup Compression

Version 2.22.0 adds support for capturing the compression algorithm used on SQL 2022.

Glenn Berry has some good articles on this new backup compression algorithm here and here. I’m getting similar results to Glenn with my own testing using QAT_DEFLATE (software mode) – faster backups, higher compression and lower resource utilization.

sys.databases

SQL 2022 adds some new columns to sys.databases:

  • is_data_retention_on
  • is_ledger_on
  • is_change_feed_enabled

I’m planning to add these to the DBA Dash collection. Only is_ledger_on is currently documented.

Other

On the to-do list is to test DBA Dash with contained availability groups. DBA Dash has monitoring for agent jobs and availability groups so it’s likely some changes will be required in this area.

What I’m excited about in SQL 2022

The intelligent query processing features in SQL 2022 and tempdb concurrency enhancements are very interesting. There is also some really useful language enhancements like GENERATE_SERIES, DATE_BUCKET, GREATEST and LEAST that I can see myself using. Null handling also gets easier with IS [NOT] DISTINCT FROM. I can’t use any of this for DBA Dash as I want to keep the repository compatible with SQL 2016 for now. The language enhancements will be useful for some of the SaaS databases I manage though once we upgrade.

The ability to failover back and forth between Azure managed instance and SQL 2022 is also a game changer. It certainly takes some of the risk out of the process if you are considering using managed instances.

The feature I’m most excited about? Backup/Restore to S3. If you host SQL instances on AWS you have to figure out how to get your backups to S3. There isn’t a standard way to do this. Many people backup to an EBS volume first then push the files to S3 which isn’t ideal. EBS storage is expensive and the two step process to get the backups to S3 adds time and complexity to the process. The restore process is also slower and more complex as you need to pull the backups from S3 first before you can restore them.

The ability to backup directly to S3 is a huge win. It also has value outside of AWS as there are a number of other S3 compatible storage providers.

September Free Community Tools Awareness Month

Posted on by

I’m writing this post to join in with September Free Community Tools Awareness Month. There are many awesome free community tools. I’ve chosen to blog about 3 of them that I think are interesting any maybe less well known in the database community. I’m also taking the opportunity to shamelessly plug my own tool, DBA Dash.

DBA Dash

DBA Dash is a monitoring tool for SQL Server. It’s something I use every day and it provides value in several different ways. It supports everything from SQL 2005 to SQL 2022 and Azure DB.

🏎️If you have a performance issue, DBA Dash can help you get to the root cause. You can troubleshoot an issue that is occurring right now or do a postmortem for an issue that occurred weeks ago.

🏥It’s also a tool for health checks. Do you have missing backups, agent job failures, or corruption? Is log shipping, availability groups, or mirroring broken? Are your servers running out of disk space? DBA Dash checks all of these and more. Recently added is a check for identity columns running out of values.

⚙️DBA Dash captures configuration data for your whole SQL Server estate (config settings, trace flags, tempdb). Use the configuration data to check for configuration settings that are different between your SQL instances – great for validating the config of new SQL instances. Check the patch level of your SQL instances. Keep track of when updates were installed and configuration changes made.

l created this tool and I’m also it’s #1 user. The tool is totally free without restrictions or limitations – even better it’s open source. Get it on GitHub.

This video will give you a quick overview of the tool.

Note: This video was created in January when the tool first launched. Many new features and enhancements have been made since the video was created.

AutomatedLab

AutomatedLab enables you to quickly set up lab environments on Hyper-V and Azure. I use this to create lab environments for DBA Dash on Hyper-V.

The lab environments enable me to test DBA Dash with SQL versions from 2005 to 2022 as well as availability groups, and different configurations. I also use the lab environment as a general playground for learning and experimentation – something useful to have for any DBA.

The power of AutomatedLab is that I can re-create my lab by running a PowerShell script and I can tear it down just as easily. Infrastructure as code!

dbatools is also a key component to setting up my lab environment and it’s one of my favourite community tools. Use this for automating anything relating to SQL Server.

Pssdiag/SqlNexus

This is a monitoring tool for SQL Server, but unlike DBA Dash it’s not a tool for 24/7 monitoring. I don’t use this tool very often but it can be useful if you are dealing with a particularly tricky SQL issue.

I’ve been a DBA for 17 years and I’ve had a small number of edge case issues that required a phone call to Microsoft product support. During these engagements I’ve been asked to run a pssdiag session and upload it for their offline analysis. You can create your own pssdiag session with SqlDiag Manager and analyse it with SqlNexus.

I would run this tool for short periods of time while you are experiencing particularly tricky issues and use other tools like DBA Dash for your regular monitoring.

This is a Microsoft tool, but like the other tools on the list it’s also open source!

DBA Dash – What’s new in 2.22.0? 🎉

Posted on by

Identity Columns collection

If your table runs out of identity values, you might see an error message similar to the one below:

Msg 8115, Level 16, State 1, Line 7
Arithmetic overflow error converting IDENTITY to data type int.

DBA Dash now has monitoring for identity values. You can see when you are about to run out of identity values which allows you time to plan and fix these problems before they cause errors in your database.

Identity Columns on DBA Dash Summary page
Identity Columns drilldown

More information here.

Backup Compression Algorithms for SQL 2022

SQL 2022 has the option to use Intel QAT backup compression. In software mode this is supported on standard and enterprise editions. It’s significantly faster, produces higher levels of compression and uses less resources. Hardware mode is just supported on enterprise edition and can significantly offload the CPU cost of compressing your backups.

Glenn Berry has some good articles on this here and here.

So what’s this got to do with DBA Dash? DBA Dash now captures the compression algorithm used from msdb.dbo.backupset for your latest backups. You can now where QAT_DEFLATE compression is in use.

New columns added to capture compression algorithm used for last backup

DBA Dash also tracks sys.configurations settings in use across all your SQL instances, including the new configuration options used to enable QAT_DEFLATE compression. You can see the configuration setting in use and also track when settings have been changed.

‘#Tracking changes to sys.configurations

Download

Download Now