Blog

Containers: Creating a Container

Containers
In yesterday's blog post we pulled SQL Server images in preparation for today's blog post where we create containers from those images. If you haven't already, get Docker installed and follow the instructions here to get at least one image on your machine. If you're interested in why I'm talking about containers all week, read this. I'm using all PowerShell commands to control Docker. Docker Run You can use 'docker create' to create an image and then start it up. However, we can just get started running a container from one of the images we downloaded yesterday. We can just simultaneously create and start the container using 'docker run': docker run -e 'ACCEPT_EULA=Y' ` -e 'SA_PASSWORD=$cthulhu1988' ` -p 1433:1433 ` --name dockerdemo ` -d mcr.microsoft.com/mssql/server:2017-latest Let's break this down a…
Read More

Containers: Getting an Image

Containers
I'm working with Docker running on Windows or Linux. There are other ways to do this, but Docker seems to be a pretty strong standard. I'll leave it to you to get Docker installed on your system. Go here to get the appropriate installation. I explain why I'm learning Docker and containers here if you're interested. Docker Pull The first command you have to learn is 'docker pull'. You then have to supply something for it to pull, an image that will be used to create your containers. I'm using Powershell for the commands I'm posting this week. Here's how you get an image with SQL Server 2017: docker pull mcr.microsoft.com/mssql/server:2017-latest Assuming you have Docker installed and running, you should get an image downloaded. Depending on your network bandwidth, this…
Read More

Learning Containers

DevOps
I find that I'm using containers more and more to get things done with SQL Server. They're so easy to set up for testing, spin 'em up, do stuff, turn 'em off, done. So, as I learn more and more about them, I figured it was time to start to share that learning here on the blog. First up, I'm NOT an expert on this topic. The two best people I know currently on this are Anthony Nocentino and Andrew Pruski. Those are the people you really should be learning the details from. I'm going to try to start to cover the introductory level of containers, Docker, and, at some point in the future, Kubernetes (maybe) and other orchestrators. However, I know that as my knowledge of how to work…
Read More

DevOps Is About Communication

DevOps
I spend a lot of time showing how to use tools to automate database deployments in support of DevOps. However, the one message that I always try to deliver with DevOps is that it's fundamentally not about the tools. No, the single most important thing in DevOps is communication. Therefore, the single most important thing in DevOps is people. People Are Good Are there evil people in the world? Unfortunately, yes. Can even good and decent people do evil? Again, unfortunately, yes. However, most people, most of the time, are trying to do the right thing. I would say that you need to arrive at this position first in order to implement a DevOps solution effectively. You can't be all "Developers are EVIL" or "DBAs are mean" or "My SAN…
Read More

Query Store, Plan Forcing and Table Variables

SQL Server, T-SQL
This weekend I was in Stockholm in Sweden, talking Query Store and plan forcing with Steinar Anderson, when he mentioned the problems he had while forcing plans that had table variables in them. Don't panic. Of course you can force a plan with a table variable, most of the time. Steinar had a fairly focused problem. Before I go on to explain the issue, let me be really clear, Steinar figured out the issue all on his own. When he outlined the problem, I saw immediately what his conclusion was going to be. What's spurring this blog post is that Steinar said he'd searched on the internet and no one had talked about the issue yet. So, let's talk about it. Plan Forcing With Table Variables First up, let's show…
Read More

Data Breaches: All Your Fault

DevOps, Redgate Software
One part of my job is to understand the compliance landscape. This means that I read a lot about the GDPR and related similar laws. I also have to read a lot about data breaches in order to understand how and where laws like the GDPR apply to them, and how they happened so that I can better prepare people through good DevOps practices to prevent them. The more I read about data breaches, the more I realize: It's You. It's your fault. Don't believe me? Let's walk through a few recent data breaches together. Passwords? We Don't Need Stinking Passwords. The Collection #1 data that represents 21 million unique email addresses and passwords for a combination of up to more than 700 million, was found by Troy Hunt... on…
Read More

Database Fundamentals #21: Using the JOIN Operator, OUTER JOIN

Database Fundamentals, SQL Server
The OUTER JOIN returns one complete set of data and then the matching values from the other set. The syntax is basically the same as INNER JOIN but you have to include whether or not you’re dealing with a RIGHT or a LEFT JOIN. The OUTER word, just like the INNER key word, is not required. OUTER JOIN Imagine a situation where you have a list of people. Some of those people have financial transactions, but some do not. If you want a query that lists all people in the system, including those with financial transactions, the query might look like this: SELECT p.LastName, ft.TransactionAmount, ft.TransactionDate, ft.TransactionTime FROM Personnel.Person AS p LEFT JOIN Finance.FinancialTransaction AS ft ON p.PersonID = ft.PersonID; Except for the addition of the LEFT key word, this…
Read More

Learning Jupyter Notebooks

Azure, Professional Development, Tools
I'm starting the process of learning how to use Jupyter Notebooks. Notebooks are documents that contain live code, commentary, results, pictures and more. Jupyter Notebooks are used for presentations, documentation, run books, troubleshooting guides and lots more. Their support within Azure Data Studio opens up lots of opportunities. Azure Data Studio If you're interested in learning about notebooks yourself, or, as I publish the notebooks that I put together and you want to consume them, you need to have a mechanism. There are any number of third party or open source solutions to read notebooks. However, since I'm focused primarily on the Microsoft data platform, I'm using Azure Data Studio to do this work. I've written in the past about using Azure Data Studio (ADS). I also have a bunch…
Read More

Jobs That Beat The Caring Out Of You

Professional Development
I was inspired by Jen McCown's story here. Read that first. It's WAY better than mine. This is not an April Fools post. Fools are involved, but none were harmed. Oprah-Level SA YOU GET SA! AND YOU GET SA! I'm working for a DOTCOM startup as a developer. I have SA privs. I have SA privs because EVERYONE has SA privs. We're developing a system (this was a long time ago) in SQL Server 6.5. The database originally was designed in Paradox (where I started my career). To be as kind as possible, the database design was a living nightmare, like the worst possible black, ichorous, Lovecraftian, tentacled monstrosity to walk the earth database. It was bad. On top of that, the code we were writing was always barely functional.…
Read More

Can You Force A Parallel Plan in Query Store?

SQL Server 2016, SQL Server 2017
I love the questions I get when presenting: Can You Force a Parallel Plan in Query Store. I haven't a clue. The trick I think is going to be in setting up the test. Let's try it out. Reliably Getting a Parallel Plan Because this is for testing, rather than try to build some crazy query that may or may not go parallel, I've decided to cheat. I'll take advantage of a little functionality that ensures I see a parallel plan when I want to. Here's my code: DBCC TRACEON(8649); GO SELECT soh.OrderDate, soh.ShipDate, sod.OrderQty FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.SalesOrderID = 43705; GO DBCC TRACEOFF(8649); Traceflag 8649 will force all plans to go parallel by effectively making the Cost Threshold for…
Read More