Press "Enter" to skip to content

Day: February 24, 2020

High-Throughput REST APIs with Dapper and Azure SQL DB

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

Dealing with Large SQL Scripts

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

Using Pester with .NET Powershell Notebooks

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

Writeback in Power BI Using Power Apps

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

SQL Server 2019 and sys.syslogins Changes

Taryn Pratt goes into a change in the sys.syslogins system view in SQL Server 2019:

Sigh ok, something is really broken because this was working before we failed over.

The code for the login replication basically does the following via a cursor (yeah, I know, but it works…normally):

1. Select from the primary via OPENQUERY to query the logins and passwords
2. Using sp_hexadecimal convert the varbinary password to a string value
3. Create a string to be executed, i.e. dynamic SQL that runs a CREATE LOGIN

Read on for the whole story and how you can protect yourself as you upgrade to SQL Server 2019.

Comments closed

Security Changes in ML Services

Dennes Torres goes over some of the security changes with Machine Learning Services in SQL Server 2019:

I have a confession to make. Why, in my last article about shortest_path in SQL Server 2019, have I used Gephi in order to illustrate the relationships, instead of using a script in for the same purpose and demonstrate Machine Learning Services as well?

The initial plan was to use an R script; however, the R script which works perfectly in SQL Server 2017 doesn’t work in SQL Server 2019.

The change is a positive one from the standpoint of security, but it also makes life more difficult. I found this particularly tricky when installing TensorFlow and Keras in R via ML Services.

Comments closed