Today was my presentation in the April GroupBy conference lineup. I presented on DevOps and changing your database development to include more agile, DevOps, flexible development practices.
If you want to get the deck, the slides are here: BringingDevOpstotheDatabase.pptx.
The video is also up on the GroupBy Youtube channel
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
This continues my series on git, because I think it’s good to know what the command line is and how to use it.
Once you have git running, the thing I’ve often wanted to do is go get code from somewhere. Certainly the creation of a repo and new code is something you might do, but often you’ll be looking to get code from somewhere else, so let’s look at how we can do this.
Most of the time I find a repo somewhere in the company or on the Internet. Fro example, I have a simple database structure that I’ve used for demos at Github. This is my ASimpleTalkDB Repo, which is at: https://github.com/way0utwest/ASimpleTalkDB. You can see it here:
Off to the right is a “clone” button, which is what we want to do. We want to perform a git clone. If you click the button, a URL is in an edit box. The URL is: https://github.com/way0utwest/ASimpleTalkDB, the same as the repo above.
Let’s clone this. First, get a folder to store code in. I started with a Documents\Github folder on my various machines. I’ll use that, and as you can see, this is a place I have a number of folders, each one a repo. I don’t have this repo set up yet.
I’ll clone this from the command line. The various tools do this, but now you’ll understand how this works. First, open a command line in this folder and then type “git clone https://github.com/way0utwest/ASimpleTalkDB”. This will clone the code, as shown below:
By default, the name of the repo becomes a folder name, with all the code below that. If I look in Explorer, I can see this.
However, I can control this. I’ll delete the folder and do this again. That’s the power of a VCS. I don’t need to worry about this code, because I’ll go get it from another repo.
Once I delete the folder, I’ll re-run the git clone command, but with the name of a folder added to the end.
These objects, 264 of them, copy to my machine in a few seconds over hotel wi-fi. This is code, just text, and it’s quick. If I change to the folder and check the status, I see it is a real repo:
What if I have code inside the company, and not in Github, Gitlab, BitBucket, VSTS, etc. Can I clone things? Yep, you can, but don’t use this to get a copy of your own code. That’s what branches are for. We’ll talk branches later, but for now, we can assume you might have a repo in your company.
For example, let’s assume for a moment that my C:\users\%username%\source\repos folder is on the network. I can clone one of these repos like this:
Again, this isn’t the way to get a copy of my own code to work on. This is for getting a repo that I want to work on for myself, where I’ll then merge changes back to the original repo on another machine. On my own machine, I’d just use branches.
This will help you get some code, and I’d encourage you to copy some code down and see how it works. Go get some code from my repo and build a db if you have SQL Source Control (point this to your cloned repo), or grab something from Microsoft and play.
That’s it for this post. There are lots of places to go. I’ll talk about how to now push your code elsewhere once you’ve changed it in another post as well as how to branch and accomplish a few other things.
A few resources (more boring, but will help you learn if you want):
git clone (git)
Create a Repo (Channel 9)
VSTS (Visual Studio Team Services) continues to grow and change over time. It seems every few months I see some new features and changes, which are good and bad. Good in that usually things improve. Bad in that I get confused and lost at times, and sometimes have to re-acquaint myself with the system.
One of the changes I noticed recently was the Deployment phases, which come as Server and Agent phases. I won’t cover server here, but I wanted to make a quick note on Agent Phases because I think they’re handy and a nice packaging concept.
There are agents that run for VSTS to do work. I’ve written about how to download an agent so tasks run inside of your network (on a server or client). This post will look at the other side, how do I control an agent from the release process.
Once I get a new release definition, I’ll see something like this:
This is a blank deployment definition. To the left I see the environment I’m working with, in this case, just the generic Environment 1 name. For each environment, I can set up tasks. On the right I have the Add Tasks button, but also the “Run on Agent” item. This is the container where I will add tasks.
I used to just add tasks with VSTS 2015 here, expecting that everything would run on an agent. VSTS has hosted agents that contain a set list of software, and also downloadable agents that you run on your servers or workstations. Things changed recently to include phases.
The phases are place of execution, which allow a series of tasks to be grouped together. VSTS and TFS 2017 give you two choices. We now have Agent or Server phases. The difference between these is that I can choose where items execute, on an agent or on the server (VSTS or TFS). In addition, I can set these to execute in parallel if need be.
If I click the down arrow next to the Add Tasks, I’ll get this dialog. This allows me to set up a process that can run on the VSTS server, or one that can run on the Agent.
I haven’t played with server phases, but when I add an agent phase, this gives me a section under which I add tasks. Note that when I click the “Agent phase” item, I get a new blade to the right. Note that the pool for these agents is the default.
This threw me early on because I have a separate pool of agents for mobile development. As I upgraded some deployment definitions, I couldn’t figure out why I didn’t have any agents available to execute my tasks. It turns out I needed to change the agent pool here.
There are options here, but I haven’t really played with parallelism or demands since I typically deploy to one system at a time.
Once I’ve selected an agent, I can click Add Tasks and then add a series of tasks to that agent. If you look below, I have added a couple tasks to each of the two agent phases. These will run one phase, then the other.
Each of these phases is independent, and as of the time I wrote this, there’s no way to copy a phase and duplicate it.
I’ve used this to upgrade my release definitions by adding new tasks, and then copying settings between one phase and the other. This is a manual, copy/paste operation, but I can easily see if I’ve duplicated everything by going back and forth quickly.
Once I’m done, I can just click the “x” to the right of the agent phase and delete the entire phase. I have to confirm this action, and then I’ve removed a complete phase.
Right now I haven’t see any benefit outside of the upgrade scenario, but that’s useful. I will duplicate my tasks, disable all the ones in one phase and then see if the other one runs. If it doesn’t, I can always enable the old tasks and disable the new ones.
I expect as I look to perform more complex deployments I will use these to logically separate things, and also perhaps use the server to do some things when I don’t need an agent.
One of the things that I do a lot is demo changes to databases with CI/CD in a DevOps fashion. However, I also want to make some application changes to my sample app without breaking things. As a result, I’ve built a few ideas that work well for both situations. I found recently these ideas can help me when I need to actually upgrade or change my CI/CD pipeline.
Note: DevOps isn’t a thing, it’s a set of principles, ideas, and culture that produces results. I will endeavor to ensure I call out the specific principles I use to adhere to DevOps ideas. In this case, automation and CD.
One of the principles of DevOps is that we use feedback loops to ensure information moves from right (operations) to left (development). One way of doing this is to release more often, though this isn’t required. What we really mean is release on your schedule, when you want, and allow developers to get feedback on their work quickly.
If a developer takes a month to build a feature, they don’t need hourly or daily releases. They need a release after the month is over (assuming testing, review, etc. has taken place). If releases occur on the 5th of the month, and the developer finishes on the 6th, they must wait a month before they get feedback. What I want to ensure is that we can release on the 5th, and also on the 6th if I need to.
I wrote in another post that I planned on upgrading my Redgate DLM Automation tasks in VSTS. I was doing this on a trip to a conference, and I didn’t want to start making the demo changes I’d make as I’d have to undo them, and it’s possible that I’d forget to clean something up. I hate making that silly mistakes, so I needed a way of testing my build and release without breaking demos.
I decided to use a technique that I use in presentations when I’m talking process and not code. In those cases, what I deploy doesn’t matter, but if I change random objects, sometimes I break the application using the database. As you can see here, I needed to do a bunch of tests, and repeat some.
When I talk with Redgate clients, and they are starting to get comfortable with the idea of deploying on their own schedule, they will sometimes make innocuous changes that trigger a deployment they can test, without breaking things. For example, a user might take this stored procedure (partially shown):
ALTER PROCEDURE [dbo].[GetEmailErrorsByDay]
@date DATE = null
/*
Description:
Changes:
Date Who Notes
———- — —————————————————
2/14/2017 WAY0UTWESTVAIO\way0u
*/
AS
BEGIN
IF @date IS NULL
SELECT @date = DATEADD(DAY, -1, GETDATE())
SELECT Errcount = COUNT(*)
FROM dbo.EmailErrorLog
WHERE EmailDate = @date
and make a small change. Perhaps they add SET NOCOUNT ON, or maybe they’ll add a comment. I’ve even see someone add this code:
…
AS
BEGIN
SELECT 2 = 2
…
These aren’t big changes, and certainly choose which stored procedure to change (one that isn’t used a lot or is critical). These are changes to test your process, gates, approvals, deployments, etc.
I decided to try something else. I tend to write this code when I want to test changes, since I can be additive with a procedure like this:
CREATE PROCEDURE Get7
as
SELECT 7
Or I can modify things with this:
ALTER PROCEDURE Get7
@plus int
as
SELECT 7 + @plus
In either case, I can see if my changes go through. Since I often deploy to multiple environments (QA, Staging, UAT, production, etc.), and I don’t always have deployments go all the way through (see my image above), I will usually end up creating Get7, Get8, Get9 as subsequent procedures. This way I can continue to commit new changes to the VCS, get new builds, and get new releases.
I can also do this with tables. My favorite is MyTable, MyTable2, etc. I usually just have an integer MyID column, but I can add other ones to test the ALTER process. I can even use this to test data movement, static (reference/lookup) data, or anything else to do with tables.
Eventually (hopefully) I get clean deployments all the way through to all environments.
I sometimes get collissions, where a test will return the error that “Get10 exists”, and I’ll move on to Get11. However, I don’t want to leave those objects in all environments. After all, likely I’ll find a way to improve things in the future and I’ll want to repeat this testing.
This usually is one last deployment for me. I’ll delete these objects in dev, and then deploy the changes all the way through to production. This allows me to test my checks to prevent data loss, if I have any. Including if approvers actually read scripts 
Voice of the DBA 