07Oct 2022 by Grant Fritchey 2 Comments

PGSQL Phriday #001: Two Truths and a Lie

PostgreSQL
As a part of my own journey of learning within PostgreSQL, I've decided that I'm going to take part in PGSQL Phriday as often as I can, just as a way to continue to stretch my knowledge of this platform. Along the way, hopefully, I can help you learn a little too. The topic of this first post is Two Truths and a Lie about PostgreSQL. Now, the lie could easily be that I'm in any way qualified to talk about this topic, however, I can do a little better than that. Let's start off with simple, but important information, backups: Pg_dump can go to either a straight set of SQL, or, to a compressed digital formatPg_dumpall backups can be used to restore over existing databases with dataYou can use…
Read More
04Oct 2022 by Grant Fritchey No Comments

DevOps From Redgate

PASS
In a few weeks at the PASS Data Community Summit, I'll be joining several other Redgaters to put on an all-day precon where we take you on a database DevOps journey. Please let me tell you all about it. From Nothing The plan is simple. We're going to take you from a fully manual deployment process, to a fully automated process over the course of the day. We'll be deploying something about every 30 minutes. As the day progresses, those deployments will become more and more automatic. We'll be using a variety of tools, but the big driver will be Redgate's Flyway. The fact is, most people recognize the need for a DevOps-style approach to their database deployments. However, doing it just isn't easy. This all-day seminar is intended to…
Read More
03Oct 2022 by Grant Fritchey 2 Comments

Function Vs. Performance

SQL Server
Recently I was looking through DBA.StackExchange when I saw a pretty simple question that I decided to answer. I went off, set up a test database and some test tables and quickly wrote a query to answer the question. I got it all formatted pretty and was on my way to post it when I saw that another answer was already there. Yeah. Identical to mine. Almost line for line. Well, nuts. I know. I'll write a blog post. The Setup The original poster had two tables that, frankly, are badly designed. However, they share enough data that they are "related" if not relational. Here's the code: CREATE DATABASE Testing; GO USE Testing; GO CREATE TABLE Table_A ( ID INT IDENTITY(1, 1), Score INT ); CREATE TABLE Table_B ( FromPoint…
Read More
29Sep 2022 by Grant Fritchey No Comments

Why You Need Presentation Skills

Uncategorized
And no, the answer is not because you want to be a Microsoft MVP. Multiple surveys have been published over the decades that list "fear of public speaking" as one of people's top fears. Some are even more afraid of this than death. Yet, public speaking is a skill we all need. Allow me to take a moment to explain why. You Are On Stage All The Time You may not think so, but you are tasked with a lot of public speaking as part of your job if you're in IT. Seriously. You constantly do it. Don't believe me? Allow me to ask a few questions. Have you in the last couple of months tried to convince the boss to implement a process change or adopt a new technology?…
Read More
28Sep 2022 by Grant Fritchey No Comments

Query Tuning At PASS Data Community Summit

PASS
The all new, in-person, PASS Data Community Summit is in just a few weeks. Since I'm shortly going to be publishing a 100% rewritten book on query tuning, I decided to take a look at what kind of sessions on the topic are going to be available at the Summit. Query Tuning With The Best You can search through the published sessions here. I didn't even bother typing in "query tuning". Far too lazy. I just typed "query" and already saw some amazing stuff. In no particular order... One of the people I've ALWAYS learned from, Kimberly Trip is delivering a precon, Indexing for Performance. That's going to be a great day of good learning. Kim is insanely knowledgeable and a fantastic presenter. Well worth your time. Erin Stellato, another…
Read More
19Sep 2022 by Grant Fritchey No Comments

Extended Events for Anything but Query Tuning: bulk_insert_usage

SQL Server
Wouldn't it be great to be able to directly monitor specific behaviors within SQL Server, like, oh, I don't know, knowing exactly when, and how, someone is using BULK INSERT? Well, you can, thanks to Extended Events through the bulk_insert_usage event. Bulk_insert_usage The BULK INSERT command is extremely useful within SQL Server. It's a way to move data into the database and provide some formatting on the way, efficiently, all through T-SQL. Hard to argue with the utility. Obviously, if you're doing traditional data collection through Trace or Extended Events, you'll see BULK INSERT commands within the T-SQL. However, Extended Events provides a specific event that tracks just the behavior of BULK INSERT: bulk_insert_usage. Documentation on this is somewhat sparse. Some of the best is from a standard source, Jason…
Read More
12Sep 2022 by Grant Fritchey No Comments

Look Into Chocolatey

Professional Development
Just a suggestion, but I'd say you should look into Chocolatey. Let me explain why. Sabbatical For those who don't know I was recently on a six-week sabbatical from work (thank you Redgate) and I tacked a week of vacation to that. While I did clean out email during that time (can you imagine coming back to seven weeks worth... <shudder>), I didn't do software updates of any kind. In the meantime, Docker was updated. VSCode, SSMS, a whole slew of others. Not to mention, the busy little beavers at Redgate released umpty-million updates. My machine needed love, so I typed the following: choco upgrade all -y Then I went to get some coffee. Why? Because all that software that was out of date, it was getting updated, automatically by…
Read More
05Sep 2022 by Grant Fritchey No Comments

Database Fundamentals #33: Check Constraints from the GUI

Database Fundamentals, SQL Server
The other types of constraints are referred to as check constraints. They limit the data by defining a logical operation that checks the state of the data prior to allowing an insert or update to the table. The logical operation is not against other tables. The logic can be against multiple columns in the same table. Let’s start with a simple example. The business has determined that the transactions it’s recording will never be less than $10. As part of the business definition, they would like a constraint put in place that limits the TransactionAmount values in the Finance.FinancialTransaction table to only accept values that are greater than $10. Let’s create this constraint using the GUI and again, in the next post, using T-SQL. Check Constraint with the GUI We’ll…
Read More
29Aug 2022 by Grant Fritchey No Comments

AWS RDS Restore To A Point In Time

AWS, PostgreSQL, RDS
The single most important part of backups are not backups. The single most important part of backups are restores. It doesn't matter a lick if you have 100, flawless backups of your database if you can't restore one of them. So, let's get started and talk about how perform an RDS restore. RDS Restore I'm going to use the portal because I like how GUIs allow me to easily illustrate what I'm doing. However, I'll probably do another post soon on how to use the command line to do this. That is the better approach in most cases. If you connect up to a database within RDS, you get a menu, Actions: Right there, near the bottom is what we're looking for, Restore to point in time. Clicking on this…
Read More
22Aug 2022 by Grant Fritchey No Comments

AWS RDS Backups

AWS, PostgreSQL, RDS
One of the things I love the most about Platform as a Service when it comes to data is the fact that you get RDS backups, built in. Go to the forums. Evidently, backups are one, or more, of the following:a) insanely difficult, so no one does themb) not considered important so no one does themc) not necessary in modern systems because of X technology so no one does them Then, someone deletes a row the business wants back. Someone drops a table. Someone drops a database. Then you find that one, or more, of the following is true:a) no one has ever tried to restore from X technology and you can'tb) come to find out, X technology doesn't really do backupsc) backups are still fundamental to protecting data Enter,…
Read More