Press "Enter" to skip to content

Author: Kevin Feasel

Extended Events in SQL Server 2022

Published 2022-05-26 by Kevin Feasel

Tom Zika checks out some extended events:

It has been announced today (2022-05-24) during the MS Build event. The blog post includes a download link. Unfortunately, the Docker container is not quite ready yet.

Anyway, because I’m a #TeamXE, I had to check out if there are any new goodies there. So, I took an XE event list from Microsoft SQL Server 2019 (RTM-CU16) and the new one from Microsoft SQL Server 2022 (CTP2.0) and compared them.

There are a lot of new events—click through to see how many.

Comments closed

Pre-Staging Log Shipping Backups with dbatools

Published 2022-05-26 by Kevin Feasel

Jess Pomfret doesn’t waste time:

Log shipping is a SQL Server feature used for disaster-recovery where the transaction log backups are ‘shipped’ from your production instance to a secondary instance. This enables you to cutover to this secondary server in the event of a disaster where you lose your primary instance. Log shipping is not a new feature but is still quite popular.

Recently I was tasked with setting up Log Shipping for a reasonably large production database. I didn’t want to initialize the secondary database with a new full backup as I was already taking full and log backups of the database. In this case we have the option of initialising the database by restoring the full & log backups up to the latest point in time and then configuring log shipping.

Read on to see how you can use dbatools to do this easily.

Comments closed

IF Branches and Updating Parameters

Published 2022-05-26 by Kevin Feasel

Erik Darling explains why NULL checks on parameters doesn’t help when trying to improve query performance:

One thing I see developers do quite a bit is try to “fix” a parameter in an IF branch.

Maybe it’s to protect against bad search values, but more often it’s to nix NULLs.

I know that the stored procedure I’m showing only has one branch in it where a query is executed, and the series is supposed to be about if branching with multiple queries.

I’m only doing that to simplify the point of this post, which is that “fixing” supplied values does not correct performance and cardinality estimation issues with queries in IF branches.

Click through for the example and a brief round-up of several things which don’t work.

Comments closed

Contained Availability Groups in SQL Server 2022

Published 2022-05-25 by Kevin Feasel

Sean Gallardy is intrigued:

SQL server 2022 (SQL Dallas) has added what is called “Contained Availability Groups”, coming as a nice surprise to many DBAs or other admins out there (incidental DBAs, small shops, etc.) which *could* severely remove a large chunk of administrative overhead and challenges. There’s quite a lot to like about them, but like all things in life there are also some drawbacks that one needs to be aware exist, while these are a giant and amazing step forward for AGs, much like any other tool it has its place and time.

Definitely read the pros and cons of this before giving it a try in the 2022 preview.

Comments closed

T-SQL Tuesday 150 Round-Up

Published 2022-05-25 by Kevin Feasel

Kenneth Fisher finishes a roundup:

This months subject for T-SQL Tuesday was Your first technical job. I have to say I really really enjoyed reading everyone’s stories and it in a lot of ways made me feel closer to all of you. Thanks for participating!

And of course, if I forgot someone please let me know so I can get them added!

Click through to see everybody’s entries.

Comments closed

Azure SQL DB REST Endpoint EAP

Published 2022-05-25 by Kevin Feasel

Davide Mauri opens up a preview:

A new, exciting capability for Azure SQL Database has been announced today at //Build: Azure SQL Database has a new built-in feature that allows native integration with external REST endpoints. This means that integration of Azure SQL Database with Azure Functions, Azure Logic Apps, Cognitive Services, Event Hubs, Event Grid, Azure Containers, API Management and in general any REST or even GraphQL endpoint is just one line of code away. The feature is one of the most requested by developers and MVPs and it enormously reduces the friction to integrate Azure services with Azure SQL Database. 

Hmm, on the one hand, I can see this being useful. On the other hand, ASMX in SQL Server 2005, anybody?

Comments closed

Azure Synapse Link for SQL

Published 2022-05-25 by Kevin Feasel

Chuck Heinzelman makes an announcement:

Azure Synapse Link for SQL is an automated system for replicating data from your transactional databases (both SQL Server 2022 and Azure SQL Database) into a dedicated SQL pool in Azure Synapse Analytics. The process of setting up a link from your SQL data to Azure Synapse takes just a few clicks and a matter of minutes rather than hours or days for traditional ETL processes. Once configured, your initial data is replicated into the target dedicated SQL pool. After the initial table seeding, changes made to your source data are replicated in near real-time. 

I’ll be interested in seeing how it performs and how efficient that change feed processor is.

Comments closed

JSON Enhancements in Azure SQL DB and SQL Server 2022

Published 2022-05-25 by Kevin Feasel

Umchandar Jayachandran has an announcement:

Today, we are announcing the public preview of JSON enhancements in Azure SQL Database and SQL Server 2022 CTP 2.0. This preview contains an enhancement to ISJSON function and three new JSON functions – JSON_PATH_EXISTS, JSON_OBJECT and JSON_ARRAY. Currently, the ISJSON function allows you to test if a string value contains a valid JSON object or array. The new optional json_type_constraint parameter in ISJSON function can now be used to test conformance of JSON documents to the IETF RFC 8259 specification. This capability allows you to test for strings that contain a JSON value, scalar, object, or array. This functionality is like the IS JSON predicate in the ANSI SQL standard. The new JSON_PATH_EXISTS function allows you to test for the existence of a specific SQL/JSON path expression in a JSON document. This functionality is like the JSON_EXISTS predicate in the ANSI SQL standard. The new ANSI SQL compatible JSON value constructors – JSON_OBJECT and JSON_ARRAY functions allow you to construct JSON object or array from SQL data.

Even if you don’t store data in JSON format, there are good reasons why you might need to accept data in JSON format (or emit data in JSON format), especially when working with languages like R and Python.

Comments closed