Press "Enter" to skip to content

Month: October 2020

Azure Arc Deployment Options for SQL Server

Sasha Nosov takes us through Azure Arc deployment options for SQL Server:

As you can see, both on-Azure and off-Azure options offer you a choice between IaaS and PaaS. The IaaS category targets the applications that cannot be changed because of the SQL version dependency, ISV certification or simply because the lack of in-house expertise to modernize. The PaaS category targets the applications that will benefit from modernization by leveraging the latest SQL features, gaining a better SLA and reducing the management complexity.

Click through for a graphic, as well as further clarification on each item.

Comments closed

Find and Modify File Growth Settings Across Databases

Lori Brown has a challenge:

To some of you out there, that many databases might not be a big deal.  To me, it is a big deal.  That many databases can cause many admin tasks to take a very long time.  One such task is to review all databases to make sure that they are not growing their files by 1MB or 10 Percent, especially the transaction log files.  I find those default settings to not be a good thing generally, and have learned to set files to grow by specified amounts if auto growth needs to occur.  I also try to manually grow data files during maintenance periods but we all know that auto growth happens.

So…how do we find out if our databases have 1MB or 10 Percent file growth set up?

Click through for a script which finds default-sized databases, as well as a script to fix them.

Comments closed

Learn Powershell with PSKoans

Mikey Bronowski shows off PSKoans:

Recently I have got a question about resources to learn PowerShell. There is plenty out there in the wild, but I came across an interesting module I would like to write today – PSKoans.

I’m a big fan of the koan strategy of learning. It ramps you up slowly and gives you plenty of code to help understand syntax and flow. The F# koans are fantastic, as are Python’s.

Comments closed

Understanding MERGE Execution Plans

Hugo Kornelis walks us through the most interesting operator:

But first a word of warning. The MERGE statement, introduced in SQL Server 2008 as an easier alternative for “delete / update / insert” logic, turned out to have issues when it was released. And now, in 2020, many of those issues still exist. So I’ll just point you to Aaron Bertrand’s excellent overview, and leave you with the recommendation to be extremely wary before using MERGE in production code.

But here, we are not going to use MERGE in production. We are merely going to set up a simple test and look at how the elements in the execution plan cooperate to produce the expected results. This is interesting even if you never use MERGE, because many of the details explained below can also occur in other execution plans.

Read the whole thing, even if you avoid MERGE like the plague.

Comments closed

The SQL Server and Containers Guide

Andrew Pruski has a guide:

I’ve been blogging about running SQL Server in Docker containers for a while now and, to be honest, my blogs are scattered over a few years and some need to be archived as they’re out of date.

So what I wanted to do was have one place where I could collate all the blogs I’ve written about running SQL Server in a container. This would make it easy for people to access information and make it easy for me to keep it all up-to-date as well.

So introducing, The SQL Server and Containers Guide!

Go check it out.

Comments closed

How SQL Server Stores sql_variant Data

Randolph West answers one of the mysteries in life:

By a show of hands, who uses the sql_variant data type? In my experience the answer is “no one,” and that’s not necessarily a bad thing. There’s a general philosophy in our industry where we assume that columns are a specific data type. This predictability makes writing queries and applications against those columns easier. It’s difficult enough dealing with NULL values, so adding confusion with handling data conversions is an invitation to introduce bugs.

The sql_variant data type is a bit of a relic. I think the people who used that were also big variant data type users in Visual Basic. Nonetheless, Randolph clarifies how SQL Server stores this data.

Comments closed