Press "Enter" to skip to content

Month: April 2020

CHECKDB Matters in the Cloud Too

Published 2020-04-16 by Kevin Feasel

Daniel Janik takes us through an ordeal related to CHECKDB on an Azure Managed Instance:

This is crazy! What now? Open at ticket with MSFT? This seemed the only choice and what was the root cause? Apparently in Azure Managed Instances, Microsoft will check databases for corruption and will take the database offline if detected.

When in this special offline state there’s no way to access the database and Microsoft must be contacted. You can’t set the DB in recovery mode or change it to ONLINE. Microsoft does “contact” someone to notify that the database was taken offline due to corruption but if you work at a larger company this notification may never reach the right people.

Read on to see what Daniel ended up doing and some tips on making the process smoother.

Comments closed

Installing Power BI Management Cmdlets for Powershell

Published 2020-04-16 by Kevin Feasel

Nicky van Vroenhoven takes us through installation of the Power BI Management commands for Powershell:

The first hurdle I encountered was the fact that there was already an older version installed. After some fiddling, trying to uninstall the old modules and going through the fixed described below, I figured the documentation was probably the best start, and it was. 🙂

Nicky documents several issues but was ultimately able to pull it in.

Comments closed

Avoiding the sp_ Prefix in Stored Procedures

Published 2020-04-16 by Kevin Feasel

Randolph West takes an Erik Darling video one step further:

Erik Darling, of Erik Darling Data (blog | Twitter) recently posted a very interesting video (my YouTube playlist is all Erik Darling Data videos and Honest Trailers). In this video he demonstrates an easily repeatable issue about naming weirdness and the CREATE OR ALTER PROCEDURE syntax, with stored procedures starting with sp_. The video is 141 seconds long, so go watch it. I’ll wait.

Cool. As many of my readers know — because you’re intelligent and attractive in equal measure — Microsoft does not want us using sp_ for stored procedure prefixes for general use, because it’s kind of reserved:

Read on for some fun with system-like objects.

Comments closed

The Observer Effect when Troubleshooting

Published 2020-04-16 by Kevin Feasel

Dmitri Korotkevitch reminds us that monitoring has a cost:

It is relatively easy with the tools – you can detect those inefficiencies just by looking at the expensive queries in the system. This is not always the case, however, with other technologies, like with monitoring done by Extended Events. They can stay almost invisible in the system, especially if targets are keeping up and don’t generate waits.

Let me show you an example and create xEvent session that captures queries that were executed in the system. This is very common one, is not it? 

Click through for the example and what happens under heavy load.

Comments closed

Serialization in Apache Flink

Published 2020-04-15 by Kevin Feasel

Nico Kruber walks us through the viable set of serializers in Apache Flink:

Flink handles data types and serialization with its own type descriptors, generic type extraction, and type serialization framework. We recommend reading through the documentation first in order to be able to follow the arguments we present below. In essence, Flink tries to infer information about your job’s data types for wire and state serialization, and to be able to use grouping, joining, and aggregation operations by referring to individual field names, e.g. stream.keyBy(“ruleId”) or dataSet.join(another).where("name").equalTo("personName"). It also allows optimizations in the serialization format as well as reducing unnecessary de/serializations (mainly in certain Batch operations as well as in the SQL/Table APIs).

Click through for notes on each serializer and a graph which shows how the choice of a serializer can make a huge difference.

Comments closed

Time Series Forecasting Best Practices

Published 2020-04-15 by Kevin Feasel

David Smith talks about a new GitHub repo:

The repository includes detailed examples of various time series modeling techniques, as Jupyter Notebooks for Python, and R Markdown documents for R. It also includes Python notebooks to fit time series models in the Azure Machine Learning service, and then operationalize the forecasts as a web service.

The R examples demonstrate several techniques for forecasting time series, specifically data on refrigerated orange juice sales from 83 stores (sourced from the the bayesm package). The forecasting techniques vary (mean forecasting with interpolation, ARIMA, exponential smoothing, and additive models), but all make extensive use of the tidyverts suite of packages, which provides “tidy time series forecasting for R“. The forecasting methods themselves are explained in detail in the book (readable online) Forecasting: Principles and Practice by Rob J Hyndman and George Athanasopoulos (Monash University).

This looks really cool.

Comments closed

Finding the xp_cmdshell Running User

Published 2020-04-15 by Kevin Feasel

Steve Stedman shows which user account actually runs commands when you use xp_cmdshell:

When you execute xp_cmdshell SQL Server runs a command at the operating system level similar to the old DOS prompt, or CMD shell. There are many security concerns and misconceptions about xp_cmdshell as documented in an earlier post.

The purpose of this post is not to debate whether xp_cmdshell is safe or not, but instead to show what user commands sent to xp_cmdshell are being run as.

Read on for a sample script.

Comments closed

Find and Replace Database Objects in SSMS

Published 2020-04-15 by Kevin Feasel

Daniel Hutmacher has a fun trick for us:

Here’s a quick tip that touches on one of the powerful SSMS tricks in my “Management Studio Level-Up” presentation. Say you have a potentially large number of database objects (procedures, functions, views, what have you), and you need to make a search-and-replace kind of change to all of those objects.

You could, of course, put the database in source control and use a proper IDE to replace everything, then check your code back into source control and commit it to the database. That’s obviously the grown-up solution. Thanks for reading this post.

But let’s say for the sake of argument that you haven’t put your database in version control. What’s the lazy option here?

Even if you do have everything in source control, the first part is still quite useful just in case there’s schema drift.

Comments closed

Using Trello as a Power BI Data Source

Published 2020-04-15 by Kevin Feasel

Gilbert Quevauvilliers shows how we can use Trello as an input for Power BI:

Where I am consulting, they use Trello boards which enables them to keep track of what tasks are being done, getting done and how things are progressing.

An interesting question came up asking how much work has been done. And I thought this could be done via Trello and looking at the number of tasks in the boards that have gone from To Do to Completed.

Below are the steps of how I completed this.

It’s a mashable world.

Comments closed

The Basics of tSQLt

Published 2020-04-15 by Kevin Feasel

Jess Pomfret walks us through the basics of tSQLt:

Getting started with tSQLt is really easy- you download a zip file, unzip the contents and then run the tSQLt.class.sql script against your development database.

There are a couple of requirements, including CLR integration must be enabled and your database must be set to trustworthy.  These could open up some security considerations, but for my development environment it’s no issue.

This is where I’d say putting the database in a container would be extremely helpful, as then you can destroy it afterward. I’m not sure if that’d work, as SQL Server on Linux doesn’t support unsafe or external access assemblies (and I’m not sure what tSQLt requires there).

Comments closed