Voice of the DBA

Author: way0utwest

DevOps Basics–Getting Started with Git
Git is taking over the world as a Version Control System (VCS) and it’s actually fairly easy to use. This is a quick post on getting started using git.

Git is free and you can get it from: https://git-scm.com/

I use Chocolatey, and you can also download it for windows with this code:
```
choco install git
```
or
```
choco update git
```
In any case, this is a simple install on your machine. If you’re on OSX or Linux, you can download and install it in whatever way you want for those systems.

Once Installed

You can check that git is installed at the command line. There are also clients, but I like the command line, especially when getting into DevOps.

git version

This code tells you if git is installed. In this case, you can see my version (after I updated).

Now it’s installed, what do I do? Well, I first want to create a repository to track my code. A repository (or repo) is really a folder where all changes to code are tracked. I can do this with the “git init” command, but I need to do this in an empty folder.

I have some code in e:\Documents\GitHub\EndtoEndAlwaysEncrypted\SQLCode for a presentation I do. I can see lots of .sql files in here:

I want to track this code, so I’ll make a repo for it. On Windows machines, there is a Source\Repos folder under your user profile. For this machine, that is C:\Users\Steve\Source\Repos. You can see I have a few folders here:

Let me create a new folder, called EndtoEndAlwaysEncrypted. Once I do that, I’ll change to that folder and run
```
git init
```
This initializes a repository. There’s nothing in there bit a hidden .git folder, but that’s fine. I now paste in my code.

So far nothing is different with my code. However, if I check my status at the command line, I’ll see there are no files. I do this with
```
git init
```
This gives me some results.

Don’t worry about the master branch item. The thing to notice here is that all the files in red are new and aren’t being tracked by git. To add these to my git VCS, I need to track them and then commit them. To track them, I’ll “stage” them with
```
git add –all
```
This will add all the files. I could use git add with a filename after it, or use the dot (.) to add everything.

Notice now that the files are in a lighter color, as they are tracked, but not committed. A commit means that git now knows about this version of the code and will be able to return to this version if you need it.

To commit, use “git commit”. You need a message for the commit. Most GUIs make this easy, and git will pop up a text editor if you want. I prefer the command line, and use the –m parameter with a message. For the first commit, “initial commit” is usually a good message.
```
git commit –m “initial commit”
```
Once you do this, the status shows clean, which means everything is being tracked by git.

That’s it for getting started. This doesn’t seem like much, but it’s the basis for now tracking code. No more need for me to do something like “00_db_setup_old.sql” or “oo_db_setup_2.sql” for filenames. I can make changes, capture (commit) them, and then easily see what each version looks like.

I’ll cover more later.

If you want to see this as a video post, I’ve got it on my Voice of the DBA YouTube playlist here: https://youtu.be/hyyy9obHmw8
May 8, 2017
Copy-SqlJob, a handy dbatools cmdlet

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One of the things I’ve often needed to do is move jobs around between instances of SQL Server. I’ll often test a job on one instance, maybe run it for awhile to see if it’s a useful addition to my instance, and then deploy the same job to other instances.

I do this often with jobs that help each instance manage itself independently. While each new build often includes some standard settings and jobs, I do find that I’ll adjust jobs over time, fix bugs, enhance them, and I’ll need to move the job over. This has usually involved scripting the job on one instance, adding delete code, and then running the script on other instances.

No longer.

Enter Copy-SqlJob

One of the cmdlets in dbatools is Copy-SqlJob, which does what you’d expect. It copies jobs from instance to instance. If you look at the docs, but default it copies all jobs, but I rarely need that. Though I might start using that for new builds along with Copy-SqlDatabase to move my DBA database from machine to machine.

However, I can copy specific jobs, which is perfect for my purposes. Just put a list of jobs to be copied in a script and run that. Let’s see how this works. On one instance I have a number of jobs.

Another instance has fewer jobs.

Let’s move just the CommandLog Cleanup and Output File Cleanup jobs over. I can fire up a PowerShell and go from there. I’ll start with the –WhatIf parameter, which lets me test without moving anything.

That’s easy enough. Let’s just move one job for now, the CommandLog Cleanup job.

And refreshing the second instance:

That’s useful, and it works easily across these instances with Windows Auth. What if I need SQL Auth? I can use SourceSqlCredential and DestinationSqlCredential to specify accounts. I can even have this prompt me for the password:

Once I type it in, the copy works.

What if I update a job on the source server, and need to ensure the changes get copied to the destination? I can use –Force. If I don’t, I’ll get a message that the job exists. With the Force parameter, the job gets copied.

This is a simple, but handy way to move jobs between servers. Many of the dbatools cmdlets are designed for migrations, and this is no exception. You can migrate jobs easily if that’s your requirement.

I’d urge you to download dbatools and see which of these cmdlets might make your administration of SQL Server easier than you ever expected.

May 8, 2017
Three Days and a Notebook

This is a short week of work for me, really just today before heading off to attend Microsoft Build 2017. I’ll spend the rest of the week watching Microsoft show off their latest tools, ideas, technology, and more at the developer’s conference. I’m not speaking (I think) and am looking forward to three days of learning, watching, and getting excited about building software that for the most part depends on data in some way. I’m not sure what will be presented, as the schedule hadn’t been posted as of the time I wrote this, but I’m sure there will be plenty of Azure and SQL to go with all the Windows and .NET sessions. I’m especially interested in ways that DevOps is making its way into the Microsoft stack and looking forward to seeing the creative solutions out there.

I haven’t attended Build since it was the PDC many years ago. It’s been since the early 2000’s when I went to a few of these events as a software developer. I first saw Microsoft showcase XML tools in the late 90s and tablets a few years later. I always enjoyed these shows and after a few years off was looking forward to going back. Then they cancelled the event before it was reborn as Build. I’ve been trying to schedule a trip for the last 4 years, but scheduling keeps getting in the way. I know, it’s a hard life, and I’m not complaining. It’s been disappointing for me.

Last year I was actually offered the chance to go speak on behalf of Redgate at a session, and I did go, but with volleyball trips planned with my daughter, I was in San Francisco about 24 hours, saw 1/2 of the keynote and part of one session (besides my own) and then left. Not much of a Build event for me. I didn’t even have time to go through the HoloLens line and give it a try. However, I had a few days with my daughter, so I can’t complain at all.

This year I didn’t have anything scheduled for May and got the chance to register early. I plan to try and leave my computer in the hotel room and work little. Instead, I’ll walk around with my notebook, jot down ideas, and just think a bit about software, my career, and where Microsoft is going. This will be my thinking time for the week. Actually, I haven’t had much thinking time this year, so I’m looking forward to the break.

I rarely attend any events or training without having some sort of commitment or responsibility these days. I try to fit in a few sessions or a pre-con here and there, but training time and focused time on something other than work is rare. For the first time in many years, I’ll actually going as an attendee and doing the things many of you do at SQL Saturdays, SQL Bits, the Pass Summit, and other events. I’ll be learning, focusing, racing from session to session, networking, and hopefully overdosing on new technology.

I’ll try to summarize notes in a few blogs, and maybe even give a short recap as an editorial one day, but this should be a fun technology week for me.

Steve Jones

The Voice of the DBA Podcast

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

May 8, 2017
The Power BI Pivot

This week we had quite a bit of Power BI content appear on various blogs and sites. Some of this was normal traffic, with Power BI gaining popularity and more data professionals writing about the topic. There are always quite a few blogs from The Guy in a Cube, including a look at how to better use colors, but I also see more and more posts from others. We had others about R in Power BI and custom visuals.

The big news this week was the announcement of the Power BI Report Server from Microsoft. This is coming late in the second quarter of 2017, according to the SSRS blog, which I assume means in the next month. The second quarter is almost over, and with Microsoft ending their fiscal year in June, I’d expect this to release sooner than later. There is also a Power BI Premium, which apparently allows an enterprise to purchase capacity in the cloud and then concurrently deploy Power BI reports on-premises.

Many people have been looking forward to this. I’ve been hearing from some reporting authors that we were going to get Power BI rolled into SSRS as an option for SQL reporting. Apparently we are, but there are some new costs here. This may change your calculation of whether this is a good move for your organization, but certainly I think that Power BI is one of the better reporting engines I’ve seen.

The licensing is confusing, and I liked in a few other thoughts on the announcement from others, such as Dan English and J ames Serra. Both note that the free tier gets to connect to the same data sources as the Pro previously could (including on premises data), which is good. The storage quota increases to 10GB, up to 8 refreshes a day and a bit more. Pro is still $10/month, and apparently is still needed for those that spend the money on Premium if they author reports or export to Excel, even if you have Enterprise Edition. At least, that’s what Mr. English notes in the license terms. Alternatively you can spend a few thousand dollars a month for the cloud version and let all your employees access reports in the cloud or from a local report server.

Power BI has been one of those amazing products that I’ve been very impressed with in the technical sense. I can build amazing reports, and get great interactivity, allowing me to analyze data in a way that hasn’t always been possible in a visual tool. Almost every time I see a new type of report or some demo, I’m impressed with what’s possible. I love the desktop tool for my own use, since it’s a nice, responsive and easy to understand interface. However the deployment method (originally just with PowerBI.com) and the licensing have always struck me as a bit strange, and perhaps somewhat crippled. It certainly felt that many features didn’t cost me money directly, but there was a cost. With this latest change, it feels like another pivot to try and increase revenue, but not in a way that’s attractive to me.

Microsoft wants to make money, and they’ve certainly invested money in the Power BI platform, so they get to make the rules. I do like Power BI, and if the costs make sense to you, and there’s enough value, this is certainly a very powerful visualization and reporting tool that I can only imagine will become more and more useful over time.

Steve Jones

May 6, 2017