Voice of the DBA

Author: way0utwest

Building locally from VSTS

One of the things that you need with a Continuous Integration server is that ability to build your software on some computer system and verify things work. With Visual Studio Team Services, using Visual Studio online, this seems to be a challenge.

The VSTS team has thought of this and includes the ability to target your builds on a hosted system in the VS cloud, where you don’t need anything installed. However the hosted build agents and servers have a limited amount of software that’s supported. SQL Server isn’t on that list.

However, there is another option. If you go to the control panel for your account, and click the Agent Pools tab, you’ll see something like this.

Notice the “Download agent” link. That’s what you want. As you can see, I’ve been testing and I have agents actually setup and registered on 4 machines. Here I’m going to add a fifth.

Once I download the file, I’ll extract it to see a list of files and folders.

What I first want to do is configure the agent, so I’ll enter ConfigureAgent from a command prompt. Note, I need to do this as an administrator level command prompt.

In my case there are some settings, but I’m overwriting them as I rebuilt my machine. Once I hit Enter, I then get the chance to Authenticate.

After this, the old Agent appears. However, since I’ve rebuilt and rename this machine, I’ll change it. I answer a few more questions about configuring the agent properties. At the end I’ll also authenticate to the Azure cloud once again.

Now that things are configured, I can run the agent. I could set this as a service, but I prefer to know the agent is (or isn’t running) and see the output. I have set this as a service before, and it works fine.

All I have left to do is RunAgent.cmd and I have an agent running locally that takes instructions from VSTS.

If I go back to my control panel, I see a new agent.

I can also trigger a build. I happen to have one setup for a project that points to local instances. Here’s the build definition, which uses the VSTS Extension from Redgate to build a database.

I can click “Queue Build” and a build will start.

I see the build running in the console:

And online in VSTS if I want. The Build Agent sends logs back to the VSTS service as it’s working.

This is a basic way to get a VSTS build working on your local machine with an agent. There is a lot more to configure if you need to, and if you need multiple agents, you can certainly pay for them with a different VSTS plan.

July 18, 2016
MeasureUp 2016

I’m heading down to Austin next weekend for MeasureUp 2016. This is a Microsoft stack conference that looks to help people build custom business software. There are over 20 sessions, most of them devoted to .NET topics. I think I’m more excited to be in some nice company and have the chance to sit in on some interesting talks.

I’ve got the one database talk, Bringing DevOps to the Database. I’ll be looking at some of the ways in which we can change database development to be more agile, to fit in more with the way that most application developers build code.

This builds on work I’ve done in the past, as well as a lot of work that Redgate has done across the last 4-5 years.

If you’re in the Austin area, and you develop software, maybe you want to come down on Saturday for the event. It’s low cost, and the chance to meet with other software developers (and get out of the heat).

Register today for MeasureUp 2016 and come shake my hand.

July 15, 2016
The Remote DBA

I’ll start this week with a question, which I hope some of you answer in the discussion: would you like to, or do you, work at home the majority of the time?

I remember when I worked in a company and needed to leave my desk and walk to a room somewhere to get some work done on a server console. Some of those rooms were cold rooms, which necessitated me keeping a jacket at my desk. I still remember going to a large company that had cables run from the data center to a couple specific workstations near the administrators’ cubes. At one point we installed a remote IP device allowing us to get to the console of any server without having to walk downstairs, or even use RDP, which was just becoming to Windows machines.

That was the end of my visiting servers in person, and since then, the only times I’ve ever really needed to look at a server was when a critical error prevented me from connecting remotely. Even then, at many of the co-location facilities I’ve contracted with, I could call and have an individual go press a power button. These days, with cloud providers and virtual machines, even that is unnecessary.

Those of us that have worked with SQL Server typically understand that we always make a network connection to work with the server. Even when we’re working on the server console, SSMS, SQLCMD, and more all make a “connection” to the database server. Therefore, is it really necessary that we ever work near a particular system?

I’ve been working from home as a telecommuter for about 8 years. My wife worked in technology from home for nearly 20 years. More and more people are doing so, in fact, there was a piece on Fast Company recently that noted more people work from home than ever before. Unfortunately, this study shows that most people end up working more hours, adding a few from home to the 40 or more they spend at work.

That’s not good, but if we can get some work done at home, why not more? I know meetings and face to face time matter, especially in some jobs, but more and more I find that lots of people that need time working alone could do a portion, perhaps a significant portion, of their work away from the office.

So this week, would you like to do more work from home (or elsewhere)? Do you want more virtual meetings, more communication over email, Slack, Skype, or some other tool? Let me know.

Steve Jones

The Voice of the DBA Podcast

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

July 15, 2016
Getting Table Change Scripts–#SQLNewBlogger

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

One of the really basic things I think everyone should understand is how to get scripts from Management Studio (SSMS) and saving them. In fact, I’ve written that everyone should use this button and really not ever execute their GUI changes. Capture the script, save that, and automate things.

However, that’s not what this post is about. This post is about how you get a script to look at changes, or better understand how SSMS might implement your changes.

Editing a Table

Let’s say that you want to redesign a table, so you Edit it in the SSMS Table Designer. Here, you can see I have small table with a few fields.

I want to rename the field with incorrect casing as well as insert an OrderDate column in the middle. I have made those changes below.

Now, I’m not sure how these changes will be made in SSMS, and I certainly want to be careful in production. We want a script we can examine and approve.

Certainly, I could use something like SQL Compare to generate a script between two databases. That would include transactions and error handling and more. That’s my preferred method. However, since not everyone has SQL Compare (a mistake! ), let’s just use SSMS.

Instead of saving, I’ll click this button.

Or I’ll go to this menu item.

Once I do that, after a warning, I get a script dialog.

I can now save the script and then open it back up in SSMS. I can see all the changes that the scripting engine thinks we should make.

This allows me to learn about one way to make these changes, as well as see things that might concern me, such as poorly named constraints and indexes.

SQLNewBlogger

This is a great productivity and learning technique, but also a core thing I’d hope most DBAs knew. You could certainly write about how you use this, or how this might have been helpful in a situation. Showcase your knowledge on this topic with the #SQLNewBlogger hashtag.

July 14, 2016