A Fine Slice Of SQL Server
This is a question that’s come up twice this morning, firstly where can we find a history of database snapshots and secondly where can we find a history of restores from snapshot?
Frustratingly, SQL doesn’t make this at all easy for us and if this is something that you want to record, you’re going to have a do a little extra work.
Let’s take a look at each part in turn.
Read on for a messy solution.
Comments closedDian M. Fay has an update to pdot:
A couple years ago, I wrote about exploring a running database by plotting relevant subsets of the foreign key relationship graph in dot and piping the resulting images directly to the terminal. Things have progressed since then:
Read on to see what’s changed, including support for displaying trigger relationships.
Comments closedMohan Saraswatipura explains how database concurrency works in Postgres:
Concurrency control is an essential aspect of database systems that deals with multiple concurrent transactions. PostgreSQL employs various techniques to ensure concurrent access to the database while maintaining data consistency using atomicity and isolation of ACID (stands for Atomicity, Consistency, Isolation and Durability – https://en.wikipedia.org/wiki/ACID) properties.
The majority of the article focuses on Multi-Version Concurrency Control, which is also the concurrency option which would be least well-known to SQL Server users.
Comments closedDeepthi Goguri makes a connection:
Applicable to-
SQL Server 2022 on-prem on Windows and Linux and SQL Server 2022 hosted on Windows Virtual Machines.
Once you install the SQL Server, there are three different authentication methods that you can use to connect SQL Server along with the Windows and SQL Server authentication. They are –
- Azure Active Directory Universal with Multi-Factor Authentication
- Azure Active Directory Password
- Azure Active Directory Integrated
Read on for the pre-requisites as well as a detailed guide on how to set everything up.
Comments closedErik Darling has a new stored procedure and a fancy Scottish accent:
SQL Server has incredibly verbose error logs, and sifting through them for the highlight reel can be a pain. They’re often full of messages that you aren’t actionable.
- Failed logins
- Successful logins
- Successful backups
Making matters worse is that when you open large log files, the response time for loading all those messages can be painful, and even time out.
But Erik has a nice stored procedure to filter out the chaff. Read on to learn more about it.
Comments closedPhil Booth provides the ammo and we provide the feet:
Production outages are great at teaching you how not to cause production outages. I’ve caused plenty and hope that by sharing them publicly, it might help some people bypass part one of the production outage learning syllabus. Previously I discussed ways I’ve broken prod with PostgreSQL and with healthchecks. Now I’ll show you how I’ve done it with Redis too.
For the record, I absolutely love Redis. It works brilliantly if you use it correctly. The gotchas that follow were all occasions when I didn’t use it correctly.
My one addition here is to be really careful if you use Redis as persistent storage rather than a cache. Redis as a cache is easy: if the server goes down or you have trouble, you simply have more database calls than normal. Redis as persistent storage is a much more complicated beast which seems to fall over a lot more often and is significantly more finicky about drivers.
Comments closedErik Darling has been busy. FIrst, sp_QuickieStore:
The first thing on the list that I want to talk about is the ability to cycle through all databases that have Query Store enabled.
If you have a lot of databases with it turned on, it can be a real hassle to go through them all looking for doodads to diddle.
Now you can just do this:
I added high-level disk metrics similar to what’s available in other popular scripts to mine. Why? Sometimes it’s worth looking at, to prove you should add more memory to a server so you’re less reliant on disk.
Especially in the cloud, where everything is an absolute hellscape of garbage performance that’s really expensive.
Click through for both sets of updates and thank Erik for his willingness to give so much to the community.
Comments closedWhen you install SQL Server 2022 through the GUI, you will see an option in the features “SQL Server Extention for Azure”
This is more of a “how” than a “why.” Azure Arc-enabled SQL Server instances let you use Azure’s control plane (their graphs and some configuration options) to manage SQL Server instances, regardless of whether they’re actually in Azure or on-premises. That way, a DBA with one foot in both camps can have a consistent administrative experience for things like inventorying SQL Server instances.
Comments closedKeshav Kiran performs a migration:
One of our customers came up with a requirement where they wanted to Migrate On-prem Database to Azure SQL Managed instance. The databases had traditional column level encryption enabled.
He has restored the database on the SQL Managed instance by Backup/Restore approach. Now when he was trying to read the encrypted column on the destination database, It was showing NULL values after decryption.
Read on for the solution.
Comments closedSasa Popovic creates some clones:
Database copy and database move operations for Azure SQL Managed Instance are very convenient in various situations when you want to copy or move database from one managed instance to another in an online way. What does online mean in this context? It means that the database on destination managed instance will be identical to the source database at the moment when operation is explicitly completed by user action. Copying a database is a size of data operation, and you can expect copy will take some time, but what is important and convenient, unlike point in-time restore where database is in state from some point in time in the past, with database copy you get database in state as it was when the operation was completed.
Read on to see how you can set this up for an Azure SQL Managed Instance.
Comments closed