Press "Enter" to skip to content

Curated SQL Posts

Azure Data Studio Database Projects

Published 2020-11-11 by Kevin Feasel

Warwick Rudd takes us through database projects in Azure Data Studio:

For a long time source control for Database Code, has been difficult or costly to implement and use.

With the ever expanding list of resources available for Azure Data Studio, we can now do even more while staying inside of a single tool allow us to be more productive and take advantage of implementing and using source control in our environments.

In the September 2020, release we have a new extension – Database Projects that I recommend you install and have approved if needed in your environment making your life easier with your database development being incorporated into source control.

I’ll stick to the ones in Visual Studio for now, but will check in on this in a couple releases to see if there’s enough value in here to make the switch.

1 Comment

String Concatenation in SQL Server

Published 2020-11-11 by Kevin Feasel

Guy Glantser hits on a pain point in SQL Server when dealing with long strings:

Now, let’s talk about concatenation. What do you think would be the data type of the following expression?

N’ABCD’ + N’EFG’

Correct! It’s NVARCHAR(7). Everything is making sense. Isn’t it great?

Now, let’s complicate things just a little bit. Suppose you have an expression that is a concatenation of two string literals – one of them contains 3,000 characters and the other contains 2,000 characters.

Guy also has a function to print beyond 4000 Unicode characters:

Sometimes, you want to print a long string. For example, you might want to print the definition of a long stored procedure. Or you might have a very long dynamic batch that you are going to execute, but you want to print it first for debug purposes.

The problem with the PRINT statement is not only that it prints up to the first 8,000 bytes. It also truncates your text without even generating a warning.

This is a long-running frustration of mine, especially when writing out complicated dynamic SQL. I think PRINT should have been changed 15 years ago to handle MAX types.

Comments closed

Infrastructure Notes for RMDBS on Azure VMs

Published 2020-11-11 by Kevin Feasel

Kellyn Pot’vin-Gorman takes a look at some of the hardware choices you have in Azure, focusing on what works for relational database management systems:

The truth is, its often a combination of database and infrastructure issues that are the cause.  Although many of you may want me to dig into database performance data, I’m actually going to first focus on infrastructure, as it’s the area that most aren’t privy to for Oracle, or for that matter, any database on Azure IaaS.

The topic of infrastructure is an essential one for any database running in IaaS and even more so VMs on Linux, which can be a bit foreign for the Microsoft data specialist.  Yes, this may be intimidating when doing the shift to Linux and understanding some of the nuances to running a database on Linux, but understanding the infrastructure is a key to removing it from the scenario.  Hopefully these tips will assist you, no matter if you’re running Oracle, (MySQL, PostgreSQL or SQL Server) on Linux VMs on Azure IaaS.

Click through for some guidance on the topic.

Comments closed

Renaming a Power Query Column Based on Position

Published 2020-11-11 by Kevin Feasel

Ed Hansberry shows how to deal with renaming columns whose names regularly change:

The easiest way to rename a column in Power Query is to do it the same way you do in Excel – just double-click it and rename it. Sometimes though the column you are renaming can have different names with each refresh, so this method won’t work. We can use a List in Power Query to make this dynamic. Consider this very simple example:

You receive this file every day, and the column name starting in Row 6, Column B, constantly changes. We are going to extract that date and make it a column, as it should be, and then rename both columns regardless of what they are called.

Read on for the process.

Comments closed

Exporting SQL Server Configurations

Published 2020-11-11 by Kevin Feasel

Garry Bargsley shows how you can export configuration settings for your SQL Server instances:

Have you ever deleted a login by mistake from a hastily typed TSQL script or dropped a list of logins because the “Business” said they are not used anymore? Have you ever made a change to a SQL Server Agent job and then it failed on the next execution. What about that time you changed the Database Mail profile on all of your servers and left your personal account in the script instead of the DBA distribution list.

While each of these examples is not life-threatening, they will strike fear in you depending on how prepared you are to recover the items in question.

This is the type of thing you’d want to store in source control, too. That way, you have a record of changes over time.

Comments closed

Understanding Skewness and Kurtosis

Published 2020-11-10 by Kevin Feasel

George Pipis explains two key concepts of a distribution:

Most commonly a distribution is described by its mean and variance which are the first and second moments respectively. Another less common measures are the skewness (third moment) and the kurtosis (fourth moment). Today, we will try to give a brief explanation of these measures and we will show how we can calculate them in R.

Click through for an explanation. H/T R-Bloggers

Comments closed

An Intro to Backup Strategy with SQL Server

Published 2020-11-10 by Kevin Feasel

Pamela Mooney takes us through backup and restoration as part of a DBA in training series:

A DBA’s job is getting the right data to the right people as quickly as possible.

Consider that first part – getting the right data. How do you do that? By having the data in the first place. The best way to ensure that you have the data is to look at your company’s needs, recommend availability options, and most importantly, do backups. Few things will get a DBA fired more quickly than not having backups available when they are needed. It may be years before you ever have to restore a database from a backup, but the backups had better be there when you need to do one.

Conference sessions and books have been written on the subjects of backup/restores, SLAs, and availability options, and they are so good that I’ll just give you a brief overview here. You’ll gravitate to your need-to-learns soon enough.

This is a nice overview for a beginner. One semi-related piece of advice that I picked up from Sean McCown: know that backup and restore syntax cold. Take backups manually (even if they’re just on a test server intended for this purpose) every day until you feel comfortable typing out backup and restore syntax from memory. There will come a time when three levels of management are standing over your shoulder waiting for a database to restore and SSMS’s GUI is crawling. At that point, knowing the syntax cold will be completely worthwhile.

Comments closed

Policy-Based Management for Availability Groups

Published 2020-11-10 by Kevin Feasel

Rajendra Gupta takes us through the policies in Policy-Based Management which relate to Availability Groups:

SQL Server Always On uses the Policy-Based Management(PBM) for determining its health. In the earlier articles, we discovered AG dashboard features to monitor synchronization status, data loss, replica states. It executes the PBM policies on availability replicas (primary and secondary), availability group database and organizes the results in a dashboard.

The primary replica contains information for all replicas, their synchronization states. It has sufficient information to compute the health for all availability groups. If we launch the AG dashboard from the primary and secondary replica, we can note the difference in monitoring.

This is perhaps the most under-developed and under-utilized feature in SQL Server. It is conceptually so powerful and what’s in there shows some of that power, but to this day, so much is still lacking in PBM.

Comments closed

Alternatives to an Agentless Azure SQL DB

Published 2020-11-10 by Kevin Feasel

Reitse Eskens gives us a few alternatives to use when we need something like SQL Agent but are running in Azure SQL Database:

What i got into was the following. For a project we’re loading an Azure sql database (serverless) with a lot of data (think billions of rows) that has to come from an on-premises Oracle server. We’re using a vpn connection with network peering to connect to the on-premises server and using a VM with a third-party tool to load the data.

Normally we’re delta-loading the database but because it’s a new project we need to perform an initial load. Nothing really weird, just a huge number of records that needs to pass through. And every now and then the application freezes and refuses to thaw. Because it’s hard to find out when the freezing will start, we want to monitor some processes on the database.

Now on a normal SQL Server i’d create a job in the Agent and be done with that part. But not on Azure. Because the agent doesn’t exist there. In SSMS you’ll see a huge empty space where the agent ought to be.

Reitse lists five separate options. A sixth would be to spin up SQL Server in a VM and use its agent for scheduling. And there are a few more alternatives as well in the ‘outside scheduler’ realm.

Comments closed