Press "Enter" to skip to content

Author: Kevin Feasel

Monitoring Availability Groups

Published 2020-02-25 by Kevin Feasel

Nisarg Upadhyay gives us some of the low-down on monitoring availability groups:

In my previous articles, I have explained the step-by-step process of deploying an AlwaysOn Availability group on SQL Server 2017. In this article, I am going to explain how to monitor AlwaysOn availability groups.

First, let’s review the configuration of the availability group we had deployed previously. To do that, open SQL Server Management Studio  Expand database engine from the object explorer  Expand “AlwaysOn High Availability”  Expand “Availability Groups.” You can see the availability group named SQLAAG. Under this availability group (SQLAAG), you can see the list of availability replicas, availability databases, and availability group listeners.

Click through for some tooling built into SQL Server Management Studio, as well as relevant Perfmon counters.

Comments closed

Understanding Azure SQL Database Elastic Jobs

Published 2020-02-25 by Kevin Feasel

Kate Smith takes us through some important concepts around Elastic Jobs in Azure SQL Database:

It is very important that the T-SQL scripts being executed by Elastic Jobs be idempotent.  This means that if they are run multiple times (by accident or intentionally) they won’t fail and won’t produce unintended results. If an elastic job has some side effects, and gets run more than once, it could fail or cause other unintended consequences (like consuming double the resources needed for a large statistics update).  One way to ensure idempotence is to make sure that you check if something already exists before trying to create it.

This takes some getting used to, but once you’re in the habit, you are much better off. Read on for more details on other key concepts.

Comments closed

Reading CPU Measures from the Ring Buffer

Published 2020-02-25 by Kevin Feasel

Taiob Ali explains what the CPU and memory measures are from the scheduler monitor ring buffer:

Here is a sample output of XML from sys.dm_os_ring_buffers where WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’. What do those XML elements mean? In order to monitor CPU usages, you need to understand what each element means so you can use the values. I will explain each one in this blog post.

Read on for the list and what each means.

Comments closed

Entering Data into Power Query from Excel

Published 2020-02-25 by Kevin Feasel

Ed Hansberry shows a quick way to hand-enter some data into Power Query from Excel:

One of the cool things about Power BI is you have a nice “Enter Data” button on the home ribbon where you can quickly add data to your model. The table isn’t dynamic, but sometimes you just need a small table to finish your model to add descriptions or some other bit of data without creating another table in a source that needs to be connected to. Enter Data is perfect for this.

It did take a little bit of trickery to accomplish, but it’s pretty easy to do.

Comments closed

Dealing with Large SQL Scripts

Published 2020-02-24 by Kevin Feasel

Kevin Chant has some advice when you have to deal with a giant SQL script:

If you have been given a script that is thousands of lines long from a developer, the first thing I would ask is if they can split it up.

I say this because a lot of developers who write long scripts tend to have come from various backgrounds. Hence, some of them are used to developing on other programming languages.

So, they do not always appreciate that SQL is a set-based language. In addition, they do not always appreciate SQL Server is optimised for set based queries.

Sometimes you can break these scripts down, though there are of course good ways and bad ways to do so.

Comments closed

High-Throughput REST APIs with Dapper and Azure SQL DB

Published 2020-02-24 by Kevin Feasel

Davide Mauri builds out an example of a WebAPI project using Dapper to query Azure SQL Database:

I was able to execute 1100 Requests Per Seconds with a median response time of 20msec. If you can accept a bit higher latency, you can also reach 1500 RPS but the median response time becomes 40msec and the 95 percentile is set at 95msec. Database usage never goes above 20% in such cases…and in fact the bottleneck is the Web App (better, the Web App Plan) and more specifically the CPU. Time to scale up or out the Web App Plan.

By scaling up and out a bit, I was able to reach almost 10.000 request per second with just an HS_Gen5_4. Quite impressive.

I like Dapper as a micro-ORM. Products like it and FSharp.Data.SqlClient are good examples of how you can remove a lot of middleware goop without taking on the performance burdens of Entity Framework and Hibernate.

Comments closed

Writeback in Power BI Using Power Apps

Published 2020-02-24 by Kevin Feasel

Shabnam Watson shows how you can use Power Apps to write back to data sources in Power BI:

The Power Apps visual first became available as a custom visual in 2018 and then as one of the default visuals as of the October 2019 release of Power BI Desktop.

The Power Apps visual provides an important functionality to refresh a Power BI report page automatically which eliminates the need for the end user to manually refresh the page by clicking on the Refresh option from the Power BI menu to see changes in the data.

In this post, I will show you how to add a simple app to a Power BI report to update the data in the report and have the app automatically refresh the page. All of this can be done with a few lines of code thanks to all the work that has been done in Power Apps to make the app creation experience extremely user friendly and relatively easy to learn.

Click through for the demo.

Comments closed

Using Pester with .NET Powershell Notebooks

Published 2020-02-24 by Kevin Feasel

Rob Sewell has Powershell in notebooks, so of course Rob is going to write tests:

Using Pester to validate that an environment is as you expect it is a good resource for incident resolution, potentially enabling you to quickly establish an area to concentrate on for the issue. However, if you try to run Pester in a .NET Notebook you will receive an error

Click through for the reason why this error appears and a workaround until it’s fixed for real.

Comments closed