Blog

15Jul 2019 by Grant Fritchey 2 Comments

Implement DevOps One Step At a Time

DevOps
In preparation for my upcoming DevOps training days (see the bottom of this post for details) and for some articles I'm working on, I've been building all new automation processes for database deployments. In the past, I've been using a fairly simple (and far too simplistic) example to do most of my demos. I haven't built a full process in a little while. OH MY GOD!!! IT'S PAINFUL!!!!! Automation Ain't Easy The easy part of getting your DevOps done is the tooling. I say that all the time because it's true. The hard part is changing your organizations culture to support the level of communication necessary for a successful DevOps implementation. However, easy by comparison doesn't mean just simply easy. There's a lot of work involved and making mistakes early…
Read More
08Jul 2019 by Grant Fritchey 3 Comments

sp_execute_external_script and SQL Injection

SQL Server
In order to take advantage of R and Python (and Java in SQL Server 2019) directly from your SQL Server scripts, you'll be using the function sp_execute_external_script. When you see this code in use for the first time, it's going to remind you of sp_execute_sql. The very first thing I thought about was, "Oh no. Another SQL Injection vector." I have a little good news and a little bad news. It's Not SQL The first and most important thing to understand is, we're not talking about SQL. Let's start with looking at some code. This is straight from the examples in the Microsoft documentation linked above: DROP PROC IF EXISTS generate_iris_model; GO CREATE PROC generate_iris_model AS BEGIN EXEC sp_execute_external_script @language = N'R' , @script = N' library(e1071); irismodel <-naiveBayes(iris_data[,1:4], iris_data[,5]);…
Read More
01Jul 2019 by Grant Fritchey 3 Comments

Continuous Learning

Professional Development
In case you can't tell from some of my blog posts, I'm a bit of an advocate for DevOps. I'm extremely fortunate in my employer, Redgate Software, that they are also huge advocates for DevOps. We not only teach it and promote it, and, oh yeah, make AWESOME tools for it, we practice DevOps in what we do. However, this post is not about DevOps. Instead, I'm trying to leverage some of the concepts of DevOps, Continuous Integration, Continuous Deployment, to arrive at some ideas around learning that I want to share. Pi-Hole Yesterday I spent several hours getting the software Pi-Hole set up for my home network. Now, this software really doesn't take several hours to set up. The reason it took me that long is because I hadn't…
Read More
25Jun 2019 by Grant Fritchey 2 Comments

From Evangelist to Advocate

Redgate Software
It's time for a little bit of change to occur. I am no longer a Product Evangelist for Redgate Software. No, they haven't fired me and I haven't quit. We're changing the role, just a little. I am now a Product Advocate for Redgate Software. Why Advocate? It's simple enough. If you look up the definitions for evangelist and advocate, it's pretty clear that my job has always been more of the advocate role than the evangelist. After all, I'm not trying to convert you to use Redgate. Instead, I want to recommend and support the position that Redgate offers you a superior product. The core role that I fill, teaching, writing, recording videos, testing and training, using Redgate tools in support of your data and data estate will be…
Read More
24Jun 2019 by Grant Fritchey No Comments

The Constant and Constantly Changing Role of the DBA

DevOps, Redgate Software, SQL Server
I've been working in and around data for over 30 years now. My title has changed a number of times and is poised to change again. My responsibilities have also shifted fairly continuously over that time. Even though it has been more than 20 years since I first took on the title of DBA, some aspects of the job are the same. However, over that 20 years, a stack of new technologies and processes have fundamentally changed a whole swath of what I do. The DBA Song Remains the Same Are your servers online? Are all databases available? Did the data load process run successfully last night? When was the last time a backup was run on this database? I honestly don't care if you're in the cloud, on-premises, or…
Read More
17Jun 2019 by Grant Fritchey 1 Comment

SQL Server Containers Are Boring

Containers
Not really, but sort of. The beauty of containers, at least in a dev/test environment, is the ability to spin them up while you need them and then throw them away when you're done. Containers give you a bunch of functionality not otherwise available through a VM. However, once you've spun up a container, they're so dull. Why Are Containers Boring Grant? I'm so glad you asked. Last week I was presenting at SQLIntersection (great show, you should consider attending). I was talking about Query Store in SQL Server 2019. One person in the audience asked, "Can Query Store run inside a container?" I responded, "Great question, let's check." I then switched over to VS code to show this: docker run ` --name DemoSharedVol ` -p 1460:1433 ` -e "ACCEPT_EULA=Y"…
Read More
03Jun 2019 by Grant Fritchey 1 Comment

Which Query Used the Most CPU? Implementing Extended Events

SQL Server, T-SQL, Tools
A question that comes up on the forums all the time: Which query used the most CPU. You may see variations on, memory, I/O, or just resources in general. However, people want to know this information, and it's not readily apparent how to get it. While you can look at what's in cache through the DMVs to see the queries there, you don't get any real history and you don't get any detail of when the executions occurred. You can certainly take advantage of the Query Store for this kind of information. However, even that data is aggregated by hour. If you really want a detailed analysis of which query used the most CPU, you need to first set up an Extended Events session and then consume that data. A…
Read More
28May 2019 by Grant Fritchey No Comments

Containers: More on Volumes

Containers
In my last post I showed how you can create a volume with your container. I then showed a few things you can with a container using a volume. I want to explore volumes just a little bit more. Locate Your Volume To have a little more fun with volumes, first, let's share a drive. You do this in the Settings in Docker Desktop (assuming that's what you're using): While this should just work, it didn't for me until I restarted Docker. So you may need to do that. Go to the drive and create a directory. I'm putting one in at C:\Docker\SQL. Once I've done that, let's create a new container: docker run ` --name SQL19 ` -p 1433:1433 ` -e "ACCEPT_EULA=Y" ` -e 'SA_PASSWORD=$cthulhu1988' ` -v C:\Docker\SQL:/sql `…
Read More
24May 2019 by Grant Fritchey 3 Comments

Containers: Create a Custom Container

Containers
Creating a custom container is where things get truly exciting. There's actually a ton of work and knowledge around this. To start with, I'm going to keep it simple. I'm going to create a container with a database & some data and a couple of general customizations. From that, we'll create our own container. To understand why I've got a series on containers, read here. Setting Up a Custom Container To start with, I'm going to spin up a container with nothing fancy: docker run -e 'ACCEPT_EULA=Y' ` -e 'SA_PASSWORD=$cthulhu1988' ` -p 1433:1433 ` --name DockerDemoTemplate ` -d mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu With this running, let's connect up and make some changes: USE master; GO CREATE DATABASE CustomContainer; GO USE CustomContainer GO CREATE TABLE dbo.CustomTable (ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,…
Read More