A Fine Slice Of SQL Server
Matthew McGiffen shares some advice:
Database backups continue to work without change when you have TDE enabled. The only difference is that the backups contain encrypted data that cannot be read without the certificate and private key. There are a couple of points that are worth discussing though.
Click through for several notes, including a warning to those still on SQL Server 2016 and woefully under-patched.
Comments closedAaron Bertrand tries something new:
When I first saw a bullet item stating SQL Server 2022 would support instant file initialization for log file growth, I was excited. When I later learned it only applies to automatic growths, and only those of 64 MB or less, I was a little less excited. With those limitations, I was skeptical this enhancement could supplant my long-standing practice of using 1 GB autogrowth for log files – at least ever since SSDs and other modern storage became more commonplace.
But after playing with it, I’m a believer.
I happened to be chatting with Thomas Grohser as he presented for our user group last week, and he also mentioned how good this feature is, as well as how VLF rules changed a bit so you don’t get as heavily penalized by growing in 64MB increments. Aaron does some testing to give us an idea of how much of a benefit you might achieve in making this change.
Comments closedHenrietta Dombrovskaya covers a couple of background processes:
Welcome to the second blog of the “magic of parameters” series. In the first entry, I covered memory parameters, and in this article. In this article will talk about PostgreSQL configuration parameters which manage the (auto)vacuum and (auto)analyze background processes.
Read on for a deeper dive into vacuuming and a bit on auto-analyze as well.
Comments closedGrant Fritchey does some explaining:
I’ve had people come up to me and say “PostgreSQL is open source and therefore license free. Why on earth would I put PostgreSQL in Azure?”
Honestly, I think that’s a very fair question. The shortest possible answer is, of course, you don’t have to. You can host your own PostgreSQL instances on local hardware, or build out VMs in Azure and put PostgreSQL out there, some other VM host, or maybe in Kubernetes containers, I mean, yeah, you have tons of options. So why PostgreSQL in Azure, and specifically, I mean the Platform as a Service offering? Let’s talk about it.
The biggest issue I’ve historically had with PostgreSQL or MySQL platform-as-a-service offerings in Azure is that Microsoft is always behind the release curve. With PostgreSQL, it’s not so bad—flexible server offers version 14.7, which is one major version behind Postgres itself (15) but at least the latest minor version. They’ve caught up on MySQL, but for a while, they were way behind.
Comments closedAndreas Scherbaum polls the audience:
For anyone who doesn’t know, I’m running a weekly interview series with people from the PostgreSQL community. It’s called “PostgreSQL Person of the Week“. One of the questions in the default set I give everyone is:
What is your favorite PostgreSQL extension?And guess what the answer is: by far everyone’s favorite is pg_stat_statements!
Read on to learn a bit more about what the extension does, why people like it, and what other extensions interviewees prefer.
Comments closedSteve Steadman reminds us of a SQL Server feature:
The Scan For Startup Procs feature in SQL Server allows you to specify a list of stored procedures that will be automatically executed whenever the database engine starts. This can be useful in certain scenarios, such as when you want to perform tasks such as restoring a database or performing maintenance tasks when the database engine starts.
“Scan for startup procs” is a configuration option in Microsoft SQL Server that determines whether the server should scan for and execute stored procedures that are marked as “startup procedures” when the server starts up.
I’ve used this to good effect in the past, but there is a fundamental problem with this approach: it’s easy to forget about these, potentially leading to a difficult search for why some action took place. If you only let sysadmins add or change startup stored procedures, then I’d consider this just as little a security risk as xp_cmdshell: if the attacker already has sysadmin, the attacker can simple enable the feature, so there’s no real value to denying yourself the capability if it makes sense in your environment.
Comments closedRyan Lambert shows off a Postgres extension:
This is my entry for PgSQL Phriday #008. It’s Saturday, so I guess this is a day late! This month’s topic, chosen by Michael from pgMustard, is on the excellent
pg_stat_statementsextension. When I saw Michael was the host this month I knew he’d pick a topic I would want to contribute on! Michael’s post for his own topic provides helpful queries and good reminders about changes to columns between Postgres version 12 and 13.In this post I show one way I like using
pg_stat_statements: tracking the impact of configuration changes to a specific workload. I used a contrived change to configuration to quickly make an obvious impact.
Read on for the example.
Comments closedYou have probably seen the recommendation to turn on the “optimize for ad-hoc workloads” setting. You might even have seen a more recent recommendation to set the database setting parameterization to forced (instead of the default which is simple). The aim of this post is to briefly describe each and then do some test with various settings.
Click through for that test. This is a good example of how we need to temper guidance with context. In Tibor’s scenario, forced parameterization is a no-brainer and optimize for ad hoc workloads gives a pretty nice reduction in plan cache utilization. But then, with optimize for ad hoc workloads on, you lose the ability to see the first run of a query in Query Store and lose the opportunity to tune the different variations of a query which only ran once. Pretty much every setting in SQL Server exists because there is a scenario in which that is the most appropriate setting. Except auto-shrink. Auto-shrink delenda est.
Comments closedThe Big Data in Real World team spams the delete button:
Shutting down a node abruptly is not the right way to decommission or remove a node from the Elasticsearch cluster. Doing so will cause your shards which are replicated to go down in replication and it could cause disruption to the clients who are currently consuming data from Elasticsearch.
Proper way to decommission or remove a node from Elasticsearch is to add the host to the exclusion list.
Click through to learn how to do this.
Comments closedPhil Booth categorizes various mistakes as learning experiences:
Previously on Extreme Learning, I discussed all the ways I’ve broken production using healthchecks. In this post I’ll do the same for PostgreSQL.
The common thread linking most of these gotchas is scalability. They’re things that won’t affect you while your database is small. But if one day you want your database not to be small, it pays to think about them in advance. Otherwise they’ll came back and bite you later, potentially when it’s least convenient. Plus in many cases it’s less work to do the right thing from the start, than it is to change a working system to do the right thing later on.
Click through for the nine lessons learned, eight of which are still relevant as of PostgreSQL version 12. Many of these also have analogues in the SQL Server world, e.g., don’t overuse triggers, use non-recursive methods for path traversal when possible, do add indexes on foreign keys.
Comments closed