Grant Fritchey - Intimidating Databases and Code

23Nov 2020 by Grant Fritchey 6 Comments

Why Aren’t You Automating Database Deployments?

DevOps

Building out processes and mechanisms for automated code deployments and testing can be quite a lot of work and isn't easy. Now, try the same thing with data, and the challenges just shot through the roof. Anything from the simple fact that you must maintain the persistence of the data to data size to up time, and you have real problems in front of you. However, adopting database deployment automation and testing has enormous benefits. Faster, safer, production deployment enhances the protection built around your production systems. Whether we want to use the loaded term of DevOps or not, the benefits of this style of development and deployment are easily documented and measured. So, why are so few people doing it? Conservation of Momentum If we were talking about a…

16Nov 2020 by Grant Fritchey No Comments

The Learning Curve for DevOps

AWS, Azure, DevOps

If you're attempting to implement automation in and around your deployments, you're going to find there is quite a steep learning curve for DevOps and DevOps-style implementations. Since adopting a DevOps-style release cycle does, at least in theory, speed your ability to deliver better code safely, why would it be hard? Why is there a Learning Curve for DevOps? I recently did a presentation on a simple Continuous Integration process. Here are the tools that I used in the demo: Local Git repositoryVS CodeAzure Data StudioAWS CodeCommitAWS CodePipelineAWS CodeBuildAWS RDS PostgreSQLShell commandsYAMLDockerFlyway Also, I regularly present using Azure DevOps Pipelines too. Here's the list of tools that might be in a given demo: Local Git RepositoryVS CodeAzure Data StudioAzure Git RepositoryAzure DevOps PipelineAzure DevOps AgentRedgate DeployAzure SQL DatabaseYAMLPowerShelltSQLt While there…

02Nov 2020 by Grant Fritchey 2 Comments

Database Fundamentals #29: Create Foreign Keys With Table Designer

Uncategorized

The purpose of a foreign key is to ensure data integrity by making sure that data added to a child table actually exists in the parent table and preventing data from being removed in the parent table if it’s in the child table. The rules for these relationships are not terribly complex: The columns in the two tables must be the same data type, although, if SQL Server can automatically, and correctly, convert the data you can get away with different data types. But don’t do that. It’s begging for an issue. Keep them the same and you won’t have any problems.The child values can be nullable, which means that any child data is unknown.The child data can also be required, meaning that you have to have the relationship, no…

12Oct 2020 by Grant Fritchey 2 Comments

Most Costly Statement in a Stored Procedure

SQL Server

A lot of stored procedures have multiple statements and determining the most costly statement in a given proc is a very common task. After all, you want to focus your time and efforts on fixing the things that cause you the most pain. You simply don't have the time to tune every single statement in every single procedure. So, identifying the most costly statement is vital. Happily, Extended Events are here to help. Finding a Costly Statement Query tuning is initially an act of discovery. Which queries, batches, procedures are inflicting the most pain on us. That pain could be measured a bunch of ways. The three most common, in particular order, are: Frequency with with a given query/batch/procedure is called.Resources used by that query.Length of time that it takes…

05Oct 2020 by Grant Fritchey 9 Comments

Assuming Good Intentions

Professional Development

With all the stuff going on around the world over the last year, maintaining some degree of mental stability can be quite a challenge. Speaking only for myself, while my family and I have been fine through all this, the changes to my situation, while mostly benign, have still had their challenges. Again, speaking only for myself, I've found those challenges do lead to negativity on my part. Further, that negativity can bleed into my communication with others, hurting that communication. I have seen others suffering from this as well. Whatever stresses they're going through clearly leads to negativity in their communications. Now, when we're looking at this situation, I'm negative, someone else is negative, we're going to communicate really poorly. So, how to address this? Well, there are a…

28Sep 2020 by Grant Fritchey 2 Comments

Capture Execution Plans Only For Long Running Queries

SQL Server

I love questions. Most of all, I love questions I can answer. I spotted this question recently: How can I use Profiler to capture execution plans for queries over a certain duration? Oh, that's easy. You don't use Profiler. You use Extended Events. Query_post_execution_showplan Extended events are just better than Profiler. Period. One of many things that is superior is the way in which the events are configured. Take for example query_post_execution_showplan. Here are the fields it captures: This event will capture execution plans plus runtime metrics. It can easily be filtered on any of the fields listed, and you can even add the database_name field if you want. So, to filter by duration is pretty simple: CREATE EVENT SESSION ExecPlansDuration ON SERVER ADD EVENT sqlserver.query_post_execution_showplan (WHERE ([duration] > (1000000)))…

23Sep 2020 by Grant Fritchey 7 Comments

Review: Stellar Repair for SQL Server

SQL Server

I was contacted by Stellar Info quite a while ago. They asked me to try out their software. I said yes, but I was really bad about getting it done. Well, I finally got off my bottom and did the job. So, let's talk about Stellar Repair for SQL Server. Stellar Repair Let's start with the most important piece of information you need: it works. The software itself is really simple to use and just does what you need, repairs your corrupted SQL Server instance. On that alone, I can recommend the tool. However, there are a few gotchas I ran into along the way. Mostly, little stuff. It's things a little polish in the UI and some clean up around language could help out. Don't get me wrong, I'm…

24Aug 2020 by Grant Fritchey 2 Comments

Extended Events: Filter on Stored Procedure Name

SQL Server, You Can't Do That In Profiler

I just received a question about Extended Events: What about filtering on the stored procedure name. You know I love writing and talking about Extended Events. The answer is, well, sure, we can do that. However, as with all things, there may be wrinkles worth being aware of. Let's examine this. Filter on Stored Procedure Name Let's create an Extended Event session that captures rpc_starting and rpc_completed: CREATE EVENT SESSION StoredProcedureName ON SERVER ADD EVENT sqlserver.rpc_completed (ACTION ( sqlserver.database_name ) WHERE (object_name = N'AddressByCity') ), ADD EVENT sqlserver.rpc_starting (SET collect_statement = (1) ACTION ( sqlserver.database_name ) WHERE (object_name = N'AddressByCity') ); I've added a WHERE clause to the Extended Event to capture only those procedures that have an object_name equal to 'AddressByCity'. If we look at the output from these…

17Aug 2020 by Grant Fritchey No Comments

Azure SQL Database Automatic Tuning

Azure

I really like the automatic tuning aspect of Azure SQL Database. This post is not a full explanation and demo. Instead, I want to point out a small point of management in the portal and your ability to configure Automatic Tuning. Configure Automatic Tuning You can configure Automatic Tuning within the Azure portal one of two ways. First, you can configure it with your server: You can inherit the values from Azure, or manually define them. Regardless of your choice, if you look at the server, you can see that your automatic tuning is configured: This is true on the server, whether I make changes to the configuration, or, just take the defaults as I did. My confusion is on the databases. Let's look at one: If we look at…

10Aug 2020 by Grant Fritchey No Comments

My First Hand-built AWS CodePipeline

AWS, Deployment Pipelines

I've just completed my first, successful, AWS CodePipeline. I'm sorry to say it took me weeks to figure out just how simple this was. Frankly, it's embarrassing. I already posted how I used the example code supplied by AWS to build a full blown CI/CD process. However, I went back into all the tools and tore it all apart so that I could build it, myself, by hand, in order to better understand it. Code All The Things Let's first talk about the tools I used. I have my code sourced in CodeCommit. It's Git. I'm using AWS CodePipeline to manage the flow control. It's triggered by a commit to the main branch. It sets up an Ubuntu instance that I can use to do things with (you can use…