Voice of the DBA

Author: way0utwest

PoSh Everywhere

This week we had an announcement that PowerShell has been released as an open source project and is available on some Linux platforms as well as OSX. I was actually talking with someone else that had been informed prior to the MS announcement, and they told me to go to github.com/powershell/powershell. I first I thought I was getting some inside information. However this is a public site, obviously updated and released in time for the announcement, so no NDAs were harmed this week.

I have an Ubuntu VM I use at times, and I decided to take a few minutes to try out PoSh on Ubuntu 16.04. Installation was easy, as are most things on Linux. Not quite as simple as Chocolatey, but close. This is certainly one of the ways that Linux is a bit easier to use than Windows. Or maybe we’re just more used to the command line in Linux. In any case, it’s a very simple process, which I documented in a blog. For those of you that use PoSh, this will be easy, and for those of you used to Linux and shell scripting, you’ll feel right at home.

I wondered how much stuff would work, but my first few tests, grabbing some generic, non SQL Server, scripts worked fine. The .Net CORE project has already moved some of the namespaces and functionality over, and this means that a lot of the management of your infrastructure systems is there. I don’t see the SQL Server libraries ported over (if I’m wrong, someone let me know), but there certainly are some other items, including Azure modules. I thought the demos in this Channel 9 webinar, while simple, were pretty impressive.

Do most of us care about this? No, not really. If you’re a person that runs Windows on laptops and desktops, this doesn’t really matter to you. I run Linux for fun, but not for daily work and my use of these tools will likely be limited to testing. However, if I were to move to an Android or Linux machine in the future, it’s nice to know that I could run the same packages across various platforms without changing any code. That’s useful for collaboration with people that might want to work together on different platforms.

This is where this makes the world easier. If you have friends running Macs, or using Linux, then you can share code. Or they can write PoSh scripts and share them with you. While I love virtualization, sometimes it’s a pain, and this is why I keep an installation of SQL Server on my host. It’s simpler and easier to work with in some situations. This is also why I look forward to a SQL Server developer edition on Linux (and hopefully OSX). It’s just a smoother world when the OS becomes a matter of choice, rather than a requirement to run some code.

August 20, 2016
PoSh on Ubuntu

I heard about the PowerShell announcement recently where the platform is porting over to Linux and OSX. I think this is part of the SQL Server on Linux project, where I assume we’ll start to see the functionality that we have on Windows appear on Linux platforms. OSX is a surprise, but since more and more people run Macs, this makes sense. Hopefully we’ll see SQL Server Developer on OSX at some point.

I’ve got an Ubuntu VM, so I decided to see how well this works, so I picked the Ubuntu 16.04 package and found the instructions I ran on my VM. You can see how this worked in the image below.

Now that I’ve got the package downloaded, I can start PowerShell pretty simply:

What can I do? Well, let me grab a short PoSh script I have. This is from the Advent of Code, and it’s a .ps1 for me. Here it is interactively.

If I make a .PS1, then I can paste in the original code:

and if I execute this:

That’s pretty cool.

I haven’t explorer the extent of the PoSh support, and I don’t think there is SQL Server support yet for the SMO namespaces, but I’m sure it’s coming.

If you’re a Linux person, or an OSX person, I’d encourage you to play a bit and see what you think of the port.

August 19, 2016
What’s Your Smoke Test?

Many DBAs and operational staff regularly stress over software deployments to production systems. Even when the administrator has built and tested the deployment scripts, there is still a nagging fear that something will be missed, incurring downtime for systems. I think automation, building an ALM And DLM process, as well practicing deployments in other environments is the way to alleviate concerns, but that’s a discussion for another day.

However, I had a question this week that is related to the deployment process. I’m curious, do you smoke test your production deployments?

Perhaps the first question is do you know what a smoke test is? If you don’t, maybe you want to read a bit about smoke tests. If you know about smoke tests, do you have anything more than testing if a server or service is running?

If you don’t, that’s fine. In fact, throughout most of my career our smoke tests consisted of checking that we could connect to a server or database. In fact, in most cases, we did this manually. We’d deploy some change or patch, then see if we could connect to the server. Sometimes a person would connect with an application, but not much more than logging in. Since many apps were fairly simple client-server applications, this was usually sufficient, but there were times we had problems with not all changes being deployed. A few times we didn’t realize this for days.

If you deploy changes, there should be some small things that you check as an method of validating that your systems are working correctly. There may be initial checks that machines are actually running. If those checks fail, there’s no point in going further. However, if the basic connections work, perhaps you should have a few critical functions that can be called and ensure that basic functionality of the application is there. Or that your new changes actually are deployed. If you have complex, interconnected systems, maybe you want a few smoke tests that you can automate and quickly ensure everything is running as expected and communicating.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Mevio

August 19, 2016
Rounding Challenges–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Rounding is an interesting activity, and one that I think people sometimes don’t pay enough attention to. Recently I saw a problem that intrigued me and I spent a few minutes looking at the issues.

Let’s suppose you have data like this for pricing.

0.3

0.7

1.2

1.6

How would you round this data? If you use a simple ROUND(n,0) function, you get this:

0.0

1.0

1.2

2.0

Now, that’s fine, but what if you want this:

0.3

1.0

1.2

2.0

That’s a bit more challenging. I’ll leave the solution out, but note that if I ROUND(n, 1), I get this.

0.3

0.7

1.2

1.6

That’s not quite right either. ROUND() is using the number of decimals I’ve given it, but in some cases, I might want to round up. In this case, I only round up when we’re at .5 or higher, but don’t round down. In those cases, I need to limit rounding. This could be done in a WHERE clause, or with a function (be really careful of scalar functions).

The important part is knowing that I really want this:

ROUND(0.3, 1)

ROUND(0.7, 0)

ROUND(1.2, 1)

ROUND(1.6, 0)

How I get these is up to me, and there are a few ways, but really I want to be sure that I understand how ROUND() works and then apply it appropriately for my situation.

SQLNewBlogger

This is a quick one, literally about 6 minutes to write. About a minute of that was playing with formatting. Understanding functions, and knowing how they affect data is important to show your knowledge.

Also, make sure that you know how to solve something like this if you write about it. I’d encourage you to write the solution as well as remember it. Someone might ask you in an interview

August 18, 2016