Voice of the DBA

Author: way0utwest

The Prompt Actions Menu

A quick post on SQL Prompt here. Someone is asking about the Prompt Actions menu. This is the menu that appears when you highlight code. I’ve got a quick animation of this working.

In the gif, I highlight code, click the Actions icon, and then I can add a BEGIN..END around my code. I also have a snippet that I use to surround code with comments.

Any snippet that has the $SELECTEDTEXT$ token in it is eligible for the Actions list.

Carly also has her own video on this on the Redgate Videos channel.

SQL Prompt is amazing, so give it a try today.

April 19, 2018
The Right Connection

Travis-CI had some staffers connect to the wrong database and truncate production tables. Needless to say this caused an outage and disrupted their business. Hopefully they didn’t lose too many customers, but they certainly did not help their reputation. I’m sure there are more than a few customers trying to decide if they continue to trust the company or move their Continuous Integration (CI) processes to another platform.

I’ve done this before. Not shut down a company, but I have actually truncated a production table by mistake. Well, not TRUNCATE, I mean, who runs that. But I have run a DELETE without a WHERE clause and killed a lookup table in a production database. Fortunately I had a copy of the table elsewhere and could rebuild it in minutes. Only a few customers had their work interrupted and only for a portion of our system. The point is that I’ve been a very good DBA, with a lot of success and experience, and I still make mistakes.

Often this type of mistakes comes about because we get busy, and we keep connections open to different systems. When we might be developing code against a schema that is close to production, it’s easy to forget which database we’re working on. Someone calls with a problem or we fight a fire, and we run some code. We fix the issue, stress bleeds away and we go back to work, but forget to switch connections or tabs. Then we run some code that would be fine in development, but causes issues in production.

SSMS has colors for a connection. SQL Prompt has tab coloring by system and database, as do some other products., which can help, but it isn’t perfect. One thing I’ve found with colors is that if I use them constantly, my mind starts to filter out the color. I don’t always realize the outline of the tab is a different color. This is especially true if I have the need to switch back and forth between both production and non-production systems. I’ve tried running two instances of SSMS, which helps, but at times I’ll forget which one I’m working with and make a connection to a production server from a non-production instance of SSMS.

Ultimately, we need to be careful. I know one friend that has no access to production and must hop through an RDP session and connect to a production database. However, if you run your RDP session in full screen, how often would you forget that you’re in the SSMS on the hop system and not in SSMS on your local machine.

I don’t know if there’s a good solution. Many of the convenience features that make life easier, like reconnecting tabs when I restart SSMS are great, however, they can compromise security and safety. I don’t know if there is a good solution, but I’d certainly like more checks against ad hoc issues occurring in production systems. Maybe some sort of lock against certain instances that prevents destructive execution on certain instances or databases without some confirmation. I love SQL Prompt preventing me from running code without WHERE clauses, but that isn’t always enough. At least not for me.

Steve Jones

The Voice of the DBA Podcast

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

April 19, 2018
Python Jupyter Notebooks in Azure

There’s a new feature in Azure, and I stumbled on it when someone posted a link on Twitter. Apologies, I can’t remember who, but I did click on the Azure Notebooks link and was intrigued. I’ve gotten Jupyter notebooks running on my local laptop, but these are often just on one machine. Having a place to share a notebook in the cloud is cool.

Once I clicked on the link, I found these are both R and Python notebooks, as well as F#. These allow you to essentially build a page of code and share it. It’s kind of like a REPL, kind of like a story. It’s a neat way of working through a problem. I clicked the Get Started link to get going and was prompted for a User ID.

Once I had my moniker setup, the next step was to edit my profile. That’s more important than a library, right?

Of course, I needed to fill out the profile with my avatar and some information.

Next I need to create a library, which I’m guessing is a collection of notebooks. I clicked the link and had to enter a name. I decided on the classic HelloWorld name. I decided to keep this public, as I might want to share this with others.

I’ve got a library, now let’s add something. I clicked the Readme.md, but it didn’t load. There was nothing there, as this is a blank file.

I discovered I could right click the file in the list. This lets me edit it. Strange UX, but whatever. The file uses markdown as editing, which is fairly simple, but consists of a few characters to designate titles, lists, etc.

I entered some text, and then my readme appeared below my notebook list, much like it goes on Github. My screenshot got taken after I’d experimented a bit, so you see a couple python notebooks as well.

From there, I could add a notebook. I have choices. I started with Python, since that’s one of my learning goals.

I give the notebook a name and create it.

Once this is created, it appears as a Jupyter notebook. Essentially I have a repl-like command area, and once I enter code, I can click “Run” to execute it. You can see that my Hello, World program ran.

I can enter other code, and I’ve done a few things, just to practice some basics in Python. I’m working through some courses, and I’ll enter code in here to practice concepts.

Jupyter notebooks are a good way of working through a problem and showing flow. They’re especially useful for sharing information with others and letting them follow your thought process.

April 18, 2018
Good Security Needs Layers

How many of you have wanted to know who started or stopped a SQL instance? Probably a few of you, as disruption to the service can affect customers. Most of us are concerned with the changes made inside SQL Server to objects and data, and that’s what the auditing features inside SQL Server are watching. The problem is that the database platform is dependent on the host OS, and as such, some actions take place at that level. Auditing inside SQL Server isn’t setup to capture this information.

Should you care? Well, restarts, or the stopping or a service are one way that a malicious actor could alter files, change the error log without you realizing it, or even copy files to other systems. All these actions might be outside of any auditing or event tracing you’ve set up. Good security needs multiple layers because the system you need to protect is often dependent on some other part of our infrastructure.

Databases depend on the host OS and perhaps directory services. Your OS may depend on a hypervisor, and certainly needs patching, so it depends on human administrators. Many of our systems depend on networking and firewall configurations. There are other layers, but the more that we can ensure each layer is secure, the better off we are. Certainly our systems always depend on humans not giving away credentials or installing malware, but that is often something many of us can’t control.

I ran across an article that explains how to use auditing at the Windows level to track this down, and ensure that there aren’t more unexplained restarts. You can implement this, but if you don’t have Windows administrative privileges, you’ll need to get help from someone that does. Likely a couple of you have been glad that there isn’t a great way to audit this from the OS, as you were the one performing a restart without permission. If that’s your MO, I expect you might not want to pass this piece along to your security staff or auditors. If that’s the way you work, though, I would advise you to change your habits.

Steve Jones

The Voice of the DBA Podcast

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

April 18, 2018