A Fine Slice Of SQL Server
In this video, we will run through detailed configuration recommendations for SQL Server on Linux. We will also find out where you can find your error logs.
This is a mix of show and tell, as I couldn’t show some of the recommendations on my particular hardware. Still, there are a whole boatload of links to additional resources if you want to learn more about why Microsoft chose specific things to show in their SQL Server on Linux performance tuning recommendations.
Comments closedJosephine Bush automates a deployment:
A well-structured Terraform module for Azure SQL DB typically consists of the following elements:
- Main Configuration Files:
main.tf,variables.tf,outputs.tf- Helper Files: (if necessary)
locals.tf,providers.tf, etc.If you want to learn more about the basics of Terraform, you can visit my previous blog post.
Click through to see how Josephine has put together the Azure SQL Database deployment module.
Comments closedMuhammad Ali keeps an eye on things:
In PostgreSQL, a checkpoint is an operation during which the database flushes/syncs all pending modifications(dirty buffers) inside memory to the actual data files on the disk.
This is important for two primary reasons. Firstly, it guarantees that all committed transactions are permanently stored, thereby safeguarding against data loss in the event of a system failure. Secondly, it works closely with the database recovery mechanism. If a crash occurs, PostgreSQL begins processing WAL logs starting from the last successful checkpoint(It gets this information from the pg_control file located in the PG data directory) during recovery. Additionally, this process allows for the fine-tuning of performance through a variety of parameters, adaptable to specific workload requirements which are discussed below.
Read on to learn more about how checkpoints work in Postgres, how the background writer works, and things to keep in mind.
Comments closedDavid Fitzjarrell performs some auditing:
In a world of ever-increasing access account security has become a major concern. More often than not accounts used for automated processes gather more and more privileges, on the assumption that they are used only by managed, automated processes and execute vetted code, The argument is that these automated processes can’t go rogue and do damage with these elevated privileges, which is true, sort of. In and of itself the database does not police where these account connections originate, which can pose a significant security risk should this vetted code, or passwords for these privileged accounts, become known.
Oracle has, for decades, provided after logon triggers, which can be utilized to ‘police’ accounts by reporting where they originate and, if necessary, disallowing such logons. Of course it will be necessary to record such connection ‘violations’ in a table so that they may be investigated. A “complete” script to do this is shown below:
Click through for that script, as well as additional thoughts from David.
Comments closedI have been writing automations using PowerShell for many years now. One common issue I notice with people’s code is the improper handling of passwords. In this blog, I share how to set up a Credential Manager on a SQLServer and use the same for handling passwords in automation.
To setup Credential Manager, we need to download and execute the following steps using scripts from my Github repo SQLMonitor.
One point I’d like to clarify in Ajay’s scripts is that the passwords in the database aren’t hashed. Hashing is a one-way operation, so you’d never be able to decrypt it with a passphrase. The password is encrypted and the passphrase isn’t a salt—salts are a way of making a hash unique from the plaintext to prevent multiple users with the same plaintext password from having the same salt. Encryption instead of hashing is the correct answer here because you need the plaintext of the password to perform the automated operation.
As for ENCRYPTBYPASSPHRASE(), it’s okay if you’re running SQL Server 2017 or later. For 2016 and earlier, it uses Triple DES with 128 bit key length and that’s no good.
I’d also look into the Powershell SecretStore module and possibly integrate into an existing key vault if you have one.
Comments closedIn the realm of data management, ensuring the safety and recoverability of data is paramount. Relational databases, being at the core of many business operations, require robust backup and recovery strategies. This article delves into the general concepts of backup and recovery in relational databases, followed by specific strategies for SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL.
Click through for a high-level overview of general database backup concepts and a variety of options available in the major relational database platforms.
Comments closedTom Collins answers a question:
Question: I have a SQL Agent job with a Last modified date of : 1/11/23. What activity causes the last modified date to occur ? And also is there a way to find out the change?
Read on for the answer to these two questions. Also check out Michael Bourgon’s comment for additional context around schedules.
Comments closedAjay Dwivedi has an update to SQLMonitor:
I have been using SQLMonitor extensively for monitoring & performance tuning of my SQLServers. To make it more and more useful, I have added more functionalities to the tool in version v1.6.0.
Click through for a list of enhancements and links where you can learn more about SQLMonitor.
Comments closedKiana Harris lays out some challenges:
As organizations embrace containerization and Kubernetes for their applications, the need for seamless portability across the Kubernetes ecosystem coupled with cloud object storage and local persistence has become a pressing concern. In this blog post, we will dive into the core problem and dissect the complex challenges that customers face in achieving containerized app portability.
Read on for the list of challenges, followed by what you can do to address them. This is at a really high level but can provide food for thought.
Comments closedBang Nguyen wants to see what’s going on:
Observability of a system includes Logging, Tracing and Metric. Today, we will go to detail how to implement observability’s elements on a K8S environment use Promtail, Open Telemetry Collector, Loki, Tempo and Grafana bellow is the demonstration of system we will implement
Click through for a detailed guide showing how to do this.
Comments closed