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…
Read More
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…
Read More
17Aug 2020 by Grant Fritchey 1 Comment

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…
Read More
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…
Read More
03Aug 2020 by Grant Fritchey 2 Comments

Capturing Queries Can Be a Pain

Uncategorized
The moment you decide you want to monitor your SQL Server instance for query behavior, you're going to be forced to make a bunch of choices. Further, all these choices lead to problems that you're going to have to deal with. Some of the problems are obvious. For example, you decided to capture all the statements run against the system. That's a lot of data you'll have to be prepared to manage. However, other problems are extremely subtle and can really be a pain. For example, how do you differentiate between a query run by a person in SQL Server Management Studio and the queries that SSMS itself runs against the system? Queries From SSMS Let's say I'm interested in capturing both the batch completed event and the statement completed…
Read More
30Jul 2020 by Grant Fritchey No Comments

Announcing: AWS Community Builders

AWS
I'm excited to be able to announce that the AWS Community Builders, a new program for those who help out with AWS technologies, has been launched. You can read the details here. I'm also excited to announce that I'm one of the first set of Builders in this new community. Building AWS Community Builders As I've worked to add AWS to my toolbox, both in the DevOps area and in the data management area, I've been impressed with the technology. I've also been impressed with the help you can get on it out on the interwebs. However, there wasn't any kind of organizing force behind the community. Different people were posting various things, but it was unclear how to tell who had a real finger on the pulse. AWS reached…
Read More
28Jul 2020 by Grant Fritchey No Comments

Database Fundamentals #28: Creating a Primary Key Using T-SQL

Database Fundamentals
There are actually a couple of ways to create a primary key with T-SQL. You can use the ALTER TABLE script to add a primary key to an existing table, or you can create a primary key as part of the table definition directly. There’s very little difference in the outcome. I’ll show you both methods and you can decided for yourself which one works better for your style of coding. ALTER TABLE for a Primary Key This T-SQL statement will alter the table Management.Address to create a primary key. Notice that I’m supplying most things. There are slightly fewer defaults for you to take advantage of when compared to the GUI. ALTER TABLE Management.Address ADD CONSTRAINT PK_Address PRIMARY KEY (AddressID); You have to tell it which table you’re altering,…
Read More
20Jul 2020 by Grant Fritchey No Comments

AWS Deployment Pipelines

AWS, Deployment Pipelines
I'm at the just barely scratching the surface level of getting started with AWS Deployment Pipelines. Of course, the first thing I want to do with them is get a database deployed. A couple of web searches and I find this bit of documentation from the AWS team. Perfect. Not only is this using AWS tools all the way from Commit (source control) to Build (automation) to Deploy (pipelines), but it's using Flyway for the magic sauce of the database deployment (database deployments need magic sauce). Because I'm just learning, it actually took me two days to get to the point where this code was working. Or rather, where it was supposed to work. There's one small bit missing or changed since that article was published. If you're attempting this,…
Read More
16Jul 2020 by Grant Fritchey No Comments

Do More, With Less

Redgate Software
Next week, July 22 and 23, I'll be presenting at Redgate Summit. Summt is our smaller, focused sessions. This one is about the concept of how DevOps, or, more accurately, a DevOps mindset, can enable you to do more with less. The idea that someone will automate themselves out of a job is something I've always found humorous. I say this because, when I first started figuring out how to automate database deployments, I was only actively supporting 1-2 teams of developers. When I finally had a mostly automated deployment methodology, I was supporting 5-7 teams. In short, I didn't have less work because of the automation, I had more. The trick is, you need to engender a mindset that does two fundamental things. First, focus on automation. If you're…
Read More
14Jul 2020 by Grant Fritchey 2 Comments

Database Fundamentals #27: Creating a Primary Key in the Table Designer

Database Fundamentals
Defining primary keys is the hardest part of the operation. You will need to work very closely with the business in order to define exactly what column or columns make a row unique. Often, this will be difficult for a business to define, but you will need to persist in order to be sure that you can properly maintain the integrity of the data being stored. It’s hard for a business to define partly because people just don’t think in terms of “unique values.” They tend to think in terms of pointing at a thing and saying “that’s the one I want.” But invariably there’s a way to uniquely identify almost any concept that business can come up with. You just have to work with the business people to find…
Read More