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
06Jul 2020 by Grant Fritchey 3 Comments

Extended Events Capturing the T-SQL of Prepared Statements

SQL Server, T-SQL, Tools
I asked this question myself: Is there a way to use Extended Events to capture the T-SQL of a prepared statement? Why would I be concerned with prepared statements? Wouldn't sql_batch_completed and rpc_completed cover us? Well, no. What happens when you use sp_prepare? What happens when you're using an ORM tool that's using prepared statements? You may see queries that look like this: EXEC sp_execute 5, 48766; What the heck code is that executing? Let's find out. sp_statement_completed Here's a set of sample code that I swiped from Microsoft (they don't mind, but, full attribution like a good citizen, you'll find it here): DECLARE @P1 int; EXEC sp_prepare @P1 output, N'@Param int', N'SELECT * FROM Sales.SalesOrderDetail AS sod INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID WHERE SalesOrderID =…
Read More
29Jun 2020 by Grant Fritchey 3 Comments

SQL Server Backups on AWS RDS

RDS
One of the things I love the most about Platform as a Service offerings is the fact that it makes it so I don't have to do silly things backup SQL Server databases on RDS. However! I'm also a paranoid control freak, aka, a DBA. While I appreciate that AWS has a good backup process and I can test it through recovery of my databases, I still want to do my own backups under some circumstances. Can I backup SQL Server databases on RDS? No and yes. Let's talk about it. Backup SQL Server Databases on RDS I have an RDS SQL Server instance running right now on AWS. I can connect up to it and run the following command: BACKUP DATABASE HamShackRadio; Which results in the following: Msg 262,…
Read More
22Jun 2020 by Grant Fritchey No Comments

State of Database Monitoring

Redgate Software
Redgate published a report that many of you helped with by providing information. First, thanks! Your info really helped. Second, do you want to see it? You can. Go here and get a copy. However, want to talk about it? Anthony Nocentino and I are hosting a little chat this week on Wednesday at 4-5 BST, 10-11 Central. You can click on this to get registered. Please do. It's going to be great information and, since it'll be live, you can ask questions. It will be recorded and you can watch it later. However, where's the fun in that? Join in. Be there live.
Read More
22Jun 2020 by Grant Fritchey 7 Comments

Database Fundamentals #26: The Primary Key War

Database Fundamentals
There is a war about primary keys in the database world. There are two camps. The first camp believes that primary keys should only ever be created on meaningful information. For example, there is an ISO standard for the abbreviation of state names in the United States. You could create a table for looking up state names and make the primary key that abbreviation because it is guaranteed to be unique. The other camp believes that primary keys should never be created on meaningful information because, meaningful information is subject to change and you don’t want your primary keys to be changing. A changing primary key means changing all the tables that are related to that value. This camp believes that all primary keys should be artificial. As far as…
Read More
18Jun 2020 by Grant Fritchey No Comments

Combining DMVs, Query Store and Extended Events Is Challenging

Uncategorized
I was recently asked a question on a forum by a person who was frustrated with all the tool choices we have for measuring performance. Moreover, they were frustrated that a simple and clear combination of the tools to achieve synergy was extremely challenging. In fact, they said that, just using the query_hash as an example, they never saw a single match between the DMVs, Query Store and Extended Events. Now, that's pretty unlikely and I'm sure we could talk about why that might be the case. However, this idea of combining the tools, I shared a bunch of thoughts on it. I decided, maybe it's worth sharing here too. Achieving Synergy Honestly, this is tough. I work for a company that makes a monitoring tool. We are trying to…
Read More
08Jun 2020 by Grant Fritchey 3 Comments

AWS RDS and SQL Server Deadlocks

AWS, RDS
What's the story with AWS RDS and SQL Server deadlocks? I'm approaching AWS RDS like I was taking on a new role at a new organization. Do we have backups in place? Yes, great. Can I test them? Yes. Do they meet our RTO & RPO? Yes. Moving on. What have we got for monitoring? AWS RDS has a good percentage of the fundamentals. Now, it's laid out a little oddly. You have the stuff going through CloudWatch which is largely OS oriented. Then you have enhanced monitoring, which you have to turn on, which covers eight key metrics for SQL Server. Finally, you can enable Performance Insights which gives you metrics on query behaviors (and yeah, any or all of these may be the subject of upcoming blog posts).…
Read More