No More SQL Server Installs

This occurred to me recently, and I’m wondering what some of you think of as the answer to this question: when will you expect to no longer install SQL Server?

It’s not that I think many of you are retiring from SQL Server work. It’s not that your organization won’t use SQL Server. It’s that I wonder if we’ll stop installing SQL Server on the host OS. We’ll make use of SQL Server in new ways, which won’t require an installation.

Lately I’ve seen a lot of tweets, post, and more about SQL Server in containers or Kubernetes. Anthony Nocentino and Andrew Pruski are doing lots of work here, and I’m moving my work to containers for demos. With a container, you don’t really install SQL Server. Instead you install the Docker engine and then “start” a pre-installed SQL Server image inside a container. You might modify this before starting it, but you aren’t installing a version and upgrades are pulling a new image and then restarting the new image. With the better support for containers on Linux, this means I’m also starting to think Linux first.

Plenty of you are also looking to do more work in Azure, AWS, or some other cloud service where you likewise don’t need to install SQL Server. In those cases, you’re really working with a service that implements a database, not an instance. There are some variations, such as the Azure Managed Instance, but you still don’t need to install SQL Server. You apply your database code, point your application, and off you go.

SQL Server 2019 is starting to feel like the first version where I’ll be looking to ignore the SQL Server instance and think in terms of containers most of the time. I’ll probably still install it, as I need to test and check things, plus, I’m still learning how to work containers into my workload, but I’m guessing that with SQL Server 2020, or 2021, I’ll just stop installing SQL Server.

Unless I need to document the installation for some of you, but in that case, I’ll try to get you to stop installing the binaries and just use a container.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | 1 Comment

What’s Worse than Announcing a Data Breach?

What’s Worse than Announcing a Data Breach? You might think that’s the worst thing that you could tell your boss these days. Imagine discovering that sensitive data has been exposed and you need to go inform an executive. Hopefully no one is looking to punish the messenger, but it will still be a very uncomfortable conversation.

Now imagine that you need to go back and have a second discussion a day or two later. Why? More data was exposed, and potentially lost. I’m not sure which conversation is worse, though if you don’t have a list of things you’ve checked, changed, or fixed between the two meetings, I would argue the second one is worse.

That happened to a mortgage loan company. The data breach was already bad, with an Elastisearch server out there without any security. Data had been converted from paper documents through an OCR process, which resulted in no shortage of mistakes, but there was still plenty of sensitive information out there. Things got worse when security researchers discovered the source of the OCR process was an Amazon S3 bucket that container the original images, also without a password.

Before I comment on anything else, there should be NO shares, buckets, containers, databases, or any sort of server without a password. None, nada, zip, zilch, no excuses. At least protect everything with a password that meets your organizations password requirements. No “12345” or “asdf” or default passwords. Before you do anything else, go set passwords. If you have S3 buckets or Azure storage, write a script to check them all. Set. A. Password. On. Everything.

We will all make mistakes in configuration, and there might be security issues at times. These ought to be rare, with today’s vulnerabilities scanners, static code analysis, configuration as code, global policies, etc. There really isn’t any excuse why we don’t set things up at the beginning, but if things change and mistakes are made, we ought to detect them quickly. And then fix them. That’s why we should use automation, configuration as code, and regular evaluation of our systems.

One of the greatest strengths of the DevOps philosophies is that we can deploy changes quickly to fix things. We’ll make mistakes, we’ll have issues, but when we find them, there is no long waiting period to get the fix into our client’s hands. That ought to be true for both software and infrastructure.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.3MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | Leave a comment

SQL Source Control and Bitbucket–Getting Started

This is in response to someone asking about getting started with their database in SQL  Source Control and then hosting at BitBucket. I’m going to assume people can set up an account at Bitbucket and won’t cover that. Instead, this is part of a series that looks at getting a CI build working.

I’m going to start showing how to get a git repo set up locally and connect a database to it with SQL Source Control in this post. I’ll then connect that to Bitbucket and move changes around. In a future post, we’ll see a CI build taking place from our Bitbucket repo.

Linking SQL Source Control

I’ve got a test database set up on a SQL Server 2017 instance, called SQLSourceControlPoC. The script for that database is here: SOCPoC_Create.sql.

2019-03-14 09_54_39-SQLQuery1.sql - Plato_SQL2017.SQLSourceControlPoC (PLATO_Steve (60)) - Microsoft

The first step is to get a repository set up. You need to install git, which is easy. You can get a client tool, like SourceTree or Github Desktop for Windows, but I like the command line and I’ll use that.

On Windows machines, under your use account, you have a Source, and then a Repos set of folders. I’ll change to those in the command line. I’ll then create a folder, called “SQLSourceControlPoC”. I find it easy to keep the folder name the same as my project, which in this case is the database. I’ll create a folder under this to store my actual code.

2019-03-14 10_00_50-cmd

Now I’ll set up a git repo, which I do with “git init” in the folder.

2019-03-14 10_01_39-cmd

That’s it. Now let’s start with SQL Source Control.

I’m going to assume you have SQL Source Control installed already. If you don’t have that, download an eval and run the setup. From there, I can right click on the database name and select “Link database to source control”.

2019-03-14 09_56_10-SQLQuery1.sql - Plato_SQL2017.SQLSourceControlPoC (PLATO_Steve (60)) - Microsoft

This will open the SQL Source Control tab in SSMS. This shows my database name at the top, and since this database hasn’t been linked, we’ll start with the wizard for linking.

2019-03-14 10_03_26-SQL Source Control - Microsoft SQL Server Management Studio

We set up a git repo already, so we’ll leave the top item checked. We click Next and get a dialog that asks which VCS and where is our repo. I’ll select git and browse to the location where I created the repo.

Note, I’ve specified the subdirectory. I like a subdirectory as this allows me to place other code in the repo if I need it (like notes, readme, etc.) and keep the database code from SQL Source Control clean.

2019-03-14 10_04_41-Link to source control

When I click link, I get a progress bar.

2019-03-14 10_06_15-Link to source control

When that finishes, I get the Setup tab, which shows me the configuration and gives me some options.

2019-03-14 10_07_47-SQL Source Control - Microsoft SQL Server Management Studio

We can ignore this for now and select the “Commit” tab instead (top left). This will switch to that tab and look for changes in the database that aren’t stored in our version control system. Here’s our current VCS view:

2019-03-14 10_09_10-DatabaseRepo

We only have our SQL Source Control file. There are many more objects in the Commit tab, as we see below. SQL Source Control assumes the database is the source of truth here and tries to capture all changes.

2019-03-14 10_12_35-SQL Source Control - Microsoft SQL Server Management Studio

There’s a lot to see here, but we won’t dive into what’s here. There are other articles and posts on this. For this article, I’ll enter a commit message and click “Commit”. Once I do that, my VCS view changes.

2019-03-14 10_17_19-DatabaseRepo

SQL Source Control has created folders for my objects, with a separate file for each object below the folder. For example, the Tables folder looks like this:

2019-03-14 10_19_28-Tables

The contents of the dbo.Blogs.sql file are shown here in Azure Data Studio.

2019-03-14 10_19_57-dbo.Blogs.sql - disconnected - Tables - Azure Data Studio

And my git status:

2019-03-14 10_21_01-cmd

We’ve gotten our code into a git repo, now let’s move on.

Connecting to BitBucket

I’m going to assume you have a Bitbucket account. If not, go do that. When you do, click your Repositories menu item, and you will get a list of repos. I have two already.

2019-03-14 10_22_12-way0utwest _ home — Bitbucket

In the mid left, there’s a plus (+) sign. Click that to get the add dialog.

2019-03-14 10_22_22-way0utwest _ home — Bitbucket

Pick repository and then you’ll enter some data. I chose a name that’s the same as my local repo to ensure some easy tracking. I made this public, so anyone can download my repo if they want to play with the code.

Note: I’m not likely to accept and PRs.

2019-03-14 10_22_58-Create a repository — Bitbucket

Once I click Create repository, I get a welcome screen. In this case, I get some instructions, and the important ones are moving my local git repo here.

2019-03-14 10_23_13-way0utwest _ sqlsourcecontrolpoc — Bitbucket

Let’s do that. I’ll go back to my command line and enter the git remote command (from above) and then the git push. Note the authentication popup.

2019-03-14 10_26_34-cmd - git  push -u origin master

That failed for me, but when I went back to the command line, I entered my password again and it worked.

2019-03-14 10_26_57-cmd

Going back to Bitbucket and refreshing the Source tab, I see code.

2019-03-14 10_28_34-way0utwest _ sqlsourcecontrolpoc _ DatabaseRepo — Bitbucket

Right now I have code in a SQL Server database. This is linked to a local git repo on my desktop, which is linked to a remote git repo at Bitbucket.

Making Changes

One last thing is to make a change on the local database and get that to Bitbucket. Let’s do that. I’ll enter this code in SSMS:

2019-03-14 10_31_58-SQLQuery1.sql - Plato_SQL2017.SQLSourceControlPoC (PLATO_Steve (60))_ - Microsof

I execute this and I have a proc in my database, but this isn’t in git.

2019-03-14 10_32_37-Stored Procedures

If I go to the Commit tab in SQL Source Control, I see one change. I’ll check this in the lower windows to verify the code, select the item in the middle and enter a Commit message.

2019-03-14 10_33_39-SQL Source Control - Microsoft SQL Server Management Studio

Once the commit completes, the change is in my local repo, but not in Bitbucket. However, SQL Source Control gives me a “Push” button. If I click this, a git push will execute.

2019-03-14 10_35_17-SQL Source Control - Microsoft SQL Server Management Studio

Note: I had some authentication issues here. The push may or may not work, depending on how you have authorization set up for Bitbucket. I had to enter a username and password, which sometimes worked, sometimes didn’t. Performing a “git push” from the command line worked.

In Bitbucket, I now see my procedure.

2019-03-14 10_56_42-way0utwest _ sqlsourcecontrolpoc _ DatabaseRepo _ Stored Procedures — Bitbucket

Summary

This is a quick look at how to get my database code in to Bitbucket via SQL Source Control and git. This should help you begin to understand how to start enabling database development to follow what application developers do.

I’ll work on getting a CI build in my next post. If you want to see a particular CI system, let me know.

Posted in Blog | Tagged , , , , , | Leave a comment

Thinking about Setup and Installation

I’m working the first section of a basic administration course, and the first part of this is installing SQL Server 2019. I built a similar course a few years back for SQL Server 2016, but the world has changed. As I review the requirements for installation, it’s interesting to note that we now have Linux and Docker installs in addition to Windows.

As I went through the list for SQL Server 2019, I found a few things that stood out for me. First, the requirements for the platforms are different. I’ll note a couple:

  • CPU – a 2.0GHz processor needed on Linux, but recommended on Windows. You can get by with a 1.4GHz one.
  • RAM – 1GB on Windows, but 2GB on Linux.
  • No .NET requirement on Linux, though presumably the package manager installs this.
  • Windows is 2012+, but Linux has specific versions listed as supported.

The first two items probably don’t matter. Most of us aren’t going to run SQL Server on a slow, single core machine with just a few GB of RAM. Even laptops tend to be 8GB these days, so who cares. Maybe a few people, but if you’re that hardware constrained, this is likely an embedded type installation. In that case, I’m not sure what to tell you.

The .NET items is interesting. I have had to update .NET at times on VMs, so I’m surprised Linux doesn’t require anything. I’m sure something is required, but if this is embedded in the package manager, I’d still like to know.

Overall I do find Linux easier to use and if I’m going to consider installing SQL Server, I am leaning towards Linux as the platform. There are still some security and AD integration items to work out, but overall, I think I prefer running SQL Server on a Linux host, and likely in a container, moving forward.

Posted in Blog | Tagged , | Leave a comment