Press "Enter" to skip to content

Curated SQL Posts

Creating an Azure Redis Cache

Arun Sirpal continues a series on Azure Redis:

Remember – basic should never be used for production. Also, if you need dedicated service then you will not want C0 because this is based on shared infrastructure. Redis can get expensive but could be cost – effective especially if you design to use a multi app approach per cache.

I select P1 – Premium with 6GB cache just to talk a couple things through.

As a note, 6GB of cache is a lot in most environments. That’s because your average cached element size in Redis should be measured in single-digit or double-digit bytes, not kilobytes. You’re typically caching individual values, not entire documents, so if you average 64 bytes per cached key-value combo, you can get somewhere around 90 million values in cache at a time. The database call savings add up quickly, considering a really simplistic estimation: if the average number of queries before expiration for a cached item is 3, a single “cycle” of caching saves you about 270 million database calls. That can allow you to downscale your relational databases considerably, saving a lot of money in the process. There’s a lot of hand-waving I’m doing in the math and a lot of complexity I’m wiping away, but both of those tend on average to make the cache more effective, not less.

Comments closed

Thoughts on CI/CD in the Serverless SQL Pool

Kevin Chant answers a question:

I got asked if I thought we were going backwards with CI/CD for serverless SQL Pools. Mostly due to the fact that we had to include defensive logic in the SQL scripts.

My answer was that we did this purely because of the limitations of the SchemaVersions table. Because the DBOps PowerShell module usually creates this table in the target location if it does not exist. Which it then uses it to log which scripts have already run.

However, you cannot easily create and update this table in a serverless SQL Pool.

Read the whole thing. This sounds like a case in which having a central schema versioning location rather than using the destination system could be advantageous. Of course, now you have a dependency on that central schema versioning location, so life is full of trade-offs.

Comments closed

Time Zones and Extended Events

Tomas Zika answers a question:

I’ve helped answer another question that appeared on the SQL Server Slack:

Are timestamps in XE event files you view in SSMS local or server time?

To test this, I need a server in a different timezone than the client (SSMS). I find the quickest and most easy tool for that to be containers – more specifically, Docker.

Click through for the answer, as well as a few Docker-related incidentals.

Comments closed

Homoglyphs and Code Oddities

Paul Harrison explains how homoglyphs can cause potential issues:

This article will walk through homoglyphs and a proposed type of attack that I have not yet seen in the wild, but I presume has occurred. Every programming language I’m aware of is impacted but I don’t know every programming language, so I’ll stick to PowerShell for the proofs of concepts below. I’ll also show code that I wrote to detect this vulnerability in PowerShell code which can be built upon to create scanners for other languages. The problems I present here can be detected if proper unit testing is in place. I don’t like writing unit tests either, but this is me Pestering you to consider adding unit testing to your pipeline.

Homoglyphs can definitely make it harder to perform code reviews and analyses, particularly when dealing with a malicious actor.

Comments closed

Power BI Hybrid Table Q&A

Shabnam Watson shares some questions and answers:

Are Hybrid Tables tied to a developer license type?

No. Incremental Refresh and Hybrid tables are tied to workspace (dataset) type. They are set up in Power BI Desktop. A developer must have Pro or Premium Per User (PPU) license to publish the report to the service. See the next two paragraphs for workspace (dataset) limitations.

Click through for the FAQs and answers.

Comments closed

SQL Server and Daylight Savings Time

Joe Pollock has to turn the clocks forward:

At the start of Daylight Saving Time (DST), which is this weekend in the UK, the clocks will move forward by one hour at 1am. Apart from the fact that we all lose an hour of our night’s sleep, what effect will this have on your scheduled jobs in SQL Server?

As we saw in my last blog post, we know that the SQL Server engine always carries on regardless of when the clocks change, it knows that this has happened, but nothing unusual occurs in the engine itself. However, SQL Server Agent, which runs scheduled tasks, is not the same, as this directly impacts how it works. 

Read on to see what can happen. Also, this can be relevant when you have unique key constraints on datetime values.

Comments closed

Power Apps Building Blocks

Elayne Jones gives us an introduction to Power Apps:

The starting point for working with Power Apps is an environment. Environments house your business’ apps, data, chatbots, and flows. By building apps in a single environment, users can isolate content aimed at a specific use case or target their content towards a specific team or department. A common practice is to build separate environments for Development, Test, and Production stages. Power Apps Environments can even connect to GitHub, streamlining source control within an organization.

An Azure Active Directory tenant is required to create an environment, and only users within that tenant can access the content within the environment. After the environment is created, users deploy data sources to that environment. Thereafter, the content created can only connect to the data sources within the same environment. You can create a database in each environment, but there can only be one database in each environment.

Read on for a lot more.

Comments closed