Azure DevOps Duet by Sander Stad and Kevin Chant || Lightup Conference

OK, you are live. Good

OK, everybody, well welcome to today's session, Azure DevOps Duet and tell about how the

development team and an operations team have to bond together to start using

Azure DevOps for SQL deployments. Before we get started, though, we'd like to cover a few things first

If you can donate to the days of, oh, yep, if you can donate to the

event. That would be fantastic. As you've probably seen, all the proceeds go to UNICEF

for the COVID-19 Relief Fund. Also, a big thanks to all the sponsors who contributed towards today's charity event

or I should say this charity event. In addition, if you want to provide speaker or event feedback, you can scan these codes here

We will share these codes again at the end of this session

So for the next 60 minutes we'll be talking about Azure DevOps, which allows us to do the

full application management lifecycle from creating a work item all the way to its release

we'll be discussing both the cloud and on premises. A version just Azure DevOps Server as well as its features

including source control, the boards, the pipelines, artifacts and test plans. And at the end of this session, you'll have a basic understanding

of the main features of Azure DevOps and how you can use it

with SQL Server. In this session one of us is from the SQL development team and the other from the SQL

operations team. Our manager has decided that both teams will merge into one DevOps team

so we're one big happy family. In reality though, this should be a really gradual process. However, we've taken it upon

ourselves to do this in 60 minutes so we've sped up this process. In addition, our manager has

decided that the development team lead will be the Scrum Master and the operations team lead will be

the product owner. Now for those of you who don't know, a product donor is responsible for translating

business needs into workable backlog items for Scrum Sprints, which means they have to speak to

stakeholders at various levels within the business. In addition, they are ultimately responsible for

prioritizing those items even though they may get input about those priorities from various stakeholders

A scrum master role is to empower the team to do those backlog items by encouraging how they work

and removing any impediments if they occur. Now before we go any further let's introduce ourselves

Yes let's do that. So my name is Sander I'm a SQL Server DBA from the Netherlands and I work for

Waypoint ytical which is a company based in the United States who do large-scale water soil

and plant sampling. I'm a cloud and data center MVP recently renewed which is really cool. I'm a

major contributor to the DBA Tools project as well. You can follow me on Twitter. I have a blog which

I try to write as much as I can but other projects mostly coming in your way. If you have any questions

after this session please send me an email. I will try to apply as fast as I can. Just to be ahead of

the questions, all the scripts that you'll be seeing today will be available on my GitHub page

under the presentations with the name of this session. You can download them, distribute them

re-teach them, whatever you want to do with them. They're free for you to use. And now we're going to go to Kevin's bio, which is slightly more complicated than mine

Okay well thanks, my name is Kevin Chan. I'm a senior database architect living in the Netherlands

However, as some of you may be able to tell from the accent, I'm actually originally from

the southwest of the UK. However, I live in the Netherlands due to the fact my wife is Dutch

So basically, I'm an Englishman living in the Netherlands working for a French based

company because he met a woman in Australia. Whenever I say this out loud, I can understand why Sandor thinks it's complicated

Now I have over 23 years experience working in IT and over that time I've worked in various

sectors including the banking, oil and health insurance industries. In fact I supported companies that are in the top 10 of the Fortune 500 list

Now I've worked with all versions of SQL Server in one form or another and I have various

certifications including both the new and the old style MCSC and more recently I get

gained a Microsoft Certified DevOps Engineer Expert certification, which is quite relevant for this session

As you can see at the bottom of the slide, I'm also on social media. I'm on Twitter at KevChant

My LinkedIn has got a user-friendly URL of Kevin-Chant. I have my own blog and my email address is available upon request

Anyway, the first thing we'd like to discuss is whether to use Azure DevOps Cloud Service or Azure DevOps Server

Now, for those of you who aren't aware, Azure DevOps Server is the new name for TFS

However, it's now been rebranded to look and feel exactly the same as the Azure DevOps Cloud Service

Personally, I'd like to use Azure DevOps Server on-premises. so that we can handle its security better

Plus it means that we get to keep this back-end SQL Server databases on premises as well

So we can take advantage of all the additional reporting functionality that provides

which you can't use with Azure DevOps Cloud Service. However, the downside is that we'd have to build

one or more servers with Azure DevOps Server installed for business continuity and maybe some

additional SQL servers as well. Plus, we'd have to wait for new features to be released

Because of the cloud first strategy. Plus, we'd have to maintain and patch

all of the servers that we built on premises. But if you use the Azure DevOps service in the cloud

instead you won't have to worry about patch, maintain anything, because it's already maintained by Microsoft in the background

So you don have to be careful about that It easier right Plus it easier to install extensions from the marketplace That true and we can still secure it in Azure anyway

Besides, whichever one we use, we still have to consider security and compliance to keep our

auditors happy. Luckily, Azure DevOps is fairly well structured and easy to manage

so the main point we want you to take away from this section is if you want to install

as your devops server you have to patch maintain everything and if you use the cloud service

or is everything is taking care of for you so just decide what's best for your situation and pitch your company Yeah

So going back to what I was saying there about Azure DevOps being well structured

Just going to quickly flip back to the home page here. As you can see here, Azure DevOps is fairly well structured and it has its own hierarchy

At the top here you have your service for your account has access to one or more organizations

which you can see here on the left hand side in Azure DevOps

For instance, an organization can be your company or an independent Azure DevOps organization

you've created for yourself. Now each organization can have one or more projects

And each project can have one or more teams which we will cover shortly

Now if you look inside one of these projects you will look on the see on the left hand

side all the features that Sander mentioned at the start of this session

Plus, if you have the right permissions, you can change things at either the

organization or the project level within Azure DevOps. Of course, like I said

it's depending on which permissions you get given. For example, if you go into project settings

which at least one person in the your team tends to have permissions to look at

you can change various things to make your project more customized. For example, you can create your teams here for your project within Azure DevOps

And of course, if you click on one of the teams, you can add your team members

In addition to that, you can click on permissions so you can add fine grained permissions for

each of your teams as well. So for example here. Very granular permissions you can set

course it's not just about creating teams and permissions in your project settings you can do

other things as well for example within your own project you can actually create service hooks to

other applications for instance if i click on service hooks and then click on create subscription

you can see that i can actually create service hooks here into a lot of applications

If I scroll down, a couple of popular ones that people tend to use are Jenkins because

then they can connect Azure DevOps to the existing Jenkins pipelines, and also Microsoft

Teams, which is very relevant for all of us because we're using Microsoft Teams today

In fact, I've used this in the past so that whenever a new pull request is raised, it will

you can actually send a message to a specific channel within Azure DevOps

within Microsoft Teams. Trust me, it's very powerful. Now you can see here there's other

things you can do as well. For instance, you can configure agent pools

which are something that we will discuss later on in today's session

which is basically how you can have service to deal with the processing

of your deployments for you. As well as parallel jobs where you can configure exactly how many of those servers can be used for each specific pipeline deployment you want to do

Now we're talking. Remember, there are other things we can figure as well

For example, we can set the boards to use Scrum, which we will meet, which you will see later on in this session

And the main point we want you to take away from this section is how Azure DevOps is structured with all the different parts, the ports and the settings and the projects

So anyway, now we've covered the main admin stuff. Let's cover version control within Azure Repos, which is a feature from Azure DevOps

But that will only be for your development though, right? Because it sounds like version control would be complicated for the ops guys to learn

Well, actually, I think you will see that the ops team, if they start using a version control, will start to perform better

At my current company, the ops team is starting to use more and more version control and all the scripting, all the automation, everything is safe in a way

in version control. If it's not in version control, it doesn't exist

And they're actually performing better because other people can look at the code too

and make improvements which makes the performance better. Well, I'm always going for performing better, so tell me more

So like I said, Azure DevOps supports multiple version control systems. We are going to use Git because it's a distributed version control system

which is going to be better for us in our situation. OK, so what about branching strategies? Because I hear they're pretty important

Yeah, yeah, they are. So we can use various ones that we can use. For example

people may know the Git flow and the GitHub flow. Well, what's the difference between those two? Because they sound pretty similar to me

So the major difference between the two is that the Git flow, with the Git flow

we don't directly merge into master unless it's a hot fix. With the GitHub flow, there is no development branch

and there are only feature branches. So as soon as a feature branch is tested and approved

it will be merged into master right away. And as devs have been using source control for many years So if we do something like pair programming between dev and ops members we may get used to it a bit quicker

Peer programming? No, pair programming. This is literally where two people sit together and work on the same code

And I understand with nowadays with Corona thing that keeping your distance that it may be a little harder to do

But research has shown it takes a bit longer to do. However, in the end, you will have more stable code and people will learn new coding practices in a faster pace

Wouldn't that mean there's a lot of overhead infos of that practice? so you will be right and there's about a 15 overhead but that overhead is compensated with

the increase of code quality which means less bug fixes and we all know bug fixes take a lot more

time than the actual development of the code and of course there are other methods you can use like

code reviews and stuff at the end of the day. It's just experiment which method works best

for your situation. If code reviews work out fine, if pair programming works out

Yeah, let's see what works. Okay, so I got a question for you then

What is to stop somebody coding their own release and pushing it straight to the master branch

Yeah, that's going to be important, right? So in Azure DevOps, we have branching policies which we can use

One of which, what we can do is that two people have to approve any update to master

which is going to be pretty important. Yeah, actually I think I've done that before

and I'll just demonstrate that for people here because. You need to have these kind of things in place, especially if

for your main branches to be socks compliant, so I just quickly

show you now what we're talking about. So if we go into Azure

repos here and select branches. I mean, select the ellipsis at the end of the branch. You can

actually select the branch policies option here. Now there's various things you can do to make sure that your SOPS compliant. For instance

if you're dealing with financial data, one of which is to set your minimum number of

reviewers to two. So you've got the four-wide principle in place. And another good practice is to automatically include reviewers, which means that when you

go to submit a pull request. One or more individuals will get an email asking them to go and have a look

So from here you can just click the add button and as you can see I can select Sandoz so he would

automatically get an email every time I submitted a pull request. Nice and these are the main points we want you to take away from this part from Azure Repos

Azure DevOps supports multiple version control systems, both Git and TFPC. However, Git has become the factor standard nowadays

And in addition, you can secure your updates using branching policies as you've seen here

If you're not sure what to use, just start using Git. And. We all know that recently Microsoft bought GitHub, so they

really put their focus on git, which means something. And get also really hooks in nice with other tools from

Microsoft like Visual Studio and Azure Data Studio. So there's no hurdle to start using it there

OK. So we better discuss Azure Boards since I'm going to be using it to organize our backlog items

Now for those who aren't there, Azure Boards can use basic, Agile, Scrum or CMMI templates for each project that you create in Azure DevOps

By the way, in Azure DevOps, templates are usually known as a process

Because we're using Scrum, we're probably going to want to use the Scrum template

For now, Sander, we can keep it as standard, but we can customize these templates if we need to

So I will just quickly go through now. What you can expect from the basics in Azure boards

So basically you have work items which is just basically every single work object that you

have at the moment in Azure DevOps will be viewed in there. And you can also view removed items in this section as well

So it can be quite useful even if you're working with Scrum. For those of you who want to work in a Kanban or Scrumban kind of way

you can actually use the boards view instead. You can actually add additional columns to this as well

And rather more interestingly, if you really want to, you can color code all these items

Now, because we're going to be using Scrum, we're probably mostly focused on backlogs and sprint boards here

Backlogs is where you view all your existing backlog items to work on

And sprint boards will show you what you're working on in your current sprint

as far as backlog items are concerned, and your outstanding tasks, which is really useful to use as a few, especially for your daily stand-ups

OK, so how are the backlog items organized for Scrum? That's a good question

In Scrum for, well, in Azure DevOps for Scrum, I should say there's a hierarchy involved

So usually in generally at the top, you would have your epics

which would have one or more features, And then each feature would have one or more backlog items and those backlog items can have many tasks that you work on during your sprint

Now, in some larger businesses, sometimes you have themes above the epics as well, but it's not very common practice if you're working with Scrum

OK. So how do you propose we use the boards? I mean, if you just have all the backlog items

in the list, it's going to be pretty cluttered, right? You mean like this? Yeah, I know. And that's why I propose that we start giving backlog items

sensible tags that we can filter on If I scroll across here you can view them How will we know when an item is ready for a sprint then Well you can actually set the backlog items to be approved within Azure DevOps

Alternatively, we can add a tag to each item to show that it's been refined

Once obviously, of course, it's met all the criteria. After it's refined, we can potentially allocate it to a sprint

which we can view in the sprint board that I showed earlier. By the way, everybody, the standard criteria for a backlog item to be classed as refined in Scrum

is that it's clear and small enough to be part of a sprint

However, businesses can have their own criteria for when an item is refined and ready for a sprint as well

Sounds good, but hang on a minute. So I've seen we have various filters on the backlog board

but you can't show items that don't have a certain tag. How will we know what items are left to be refined

Oh, yeah, I see what you mean there. Well, we do have a couple of options here

Either we can start moving backlog items directly into future sprints once they've been refined of course that means that we have to keep remembering to move all those

items to sprints every time you set that or we can use beautiful queries to show any backlog items

that do not have the refined tag of course they're not actually called beautiful queries it's just a

nickname for them that i've given so for example if i click on the queries section

here in Azure Boards. You will see that I've already created the query earlier and I can click on

there and that will show me a backlog item which is yet to have the refined tag

I must point out here this is a very basic demo of what you can do with

these queries and you can do a lot more powerful functionality with them

For example, you can actually run under these queries if you're linking to multiple boards

boards and other projects that are using a combination of Scrum and Agile

You can run a query to view both your backlog items and user stories in different projects

By the way, before I mention it now, Sandor, a British guy living over in the Netherlands

actually did put a request in at one stage to have a knot filter added to here as well

but apparently there wasn't enough interest in that so it didn't get followed through

wow bummer i guess who don't i wonder who that was so now we're talking about sprints how do you

propose we align our kanban method to the backlog items i mean our items only last a couple days right

Well, if they only last a couple of days, it can potentially be converted to a backlog

item instead to be worked on during the sprint. Fair enough. How are we going to know what our sprint goal is though

Well, what we can do is actually download an extension from the marketplace, which will

allow us to add it. For example, I actually installed the sprint goal extension beforehand

beforehand so if you click on the sprint board here and then click on go you can see you can

actually type in your sprint goal here so it's very visible for everybody that views the sprint board

nice so i'm guessing we're still going to use uh our other applications to manage the

boards in the meantime until we get a good look at this

Well, you can still use your application and manage your boards for always. You should

get used to Azure DevOps if you really want to. However, personally, I recommend ripping the

bandaid off and doing the migration as soon as possible though to take advantage of all

the integration you get in Azure DevOps. By the way, did I mention all the ytics and

some preview features we can potentially use with Azure Boards. Calm down Kevin. We only have 60 minutes will never make it

Good point. People are just have to watch something elsewhere to see it being used more within Azure Boards. Anyway, the main

point to take away from this section is that Azure Boards is

a very powerful way to manage Scrum or agile work visually. especially if you're a product donor

OK, so let's talk about Azure Pipelines. Is this the point where we'll actually

build and deploy our deliverables? OK, so how would this work with SQL Server

So. We could look to deploy using state based or migration based deployments

Well, what's the main difference between those two? So the main difference between state-based and migration-based is that with state-based

you deploy the entire thing and check for changes, whereas migration-based will script out all the changes since the last deployment

and deploy them incrementally. Migration-based can be good. It depends on the scenario

but if there is a problem you have to deploy everything in sequence again

with and with state based you just deploy the current state of your project

and it's fairly easy to build a pipeline for sql server as i will be demonstrating

right now so i'm going to share my desktop So what I did was, because I had to do like 25 to 30 databases to get them into some kind of SSTT solution, I used the PS module development PowerShell module, which enables you to create templates for basically everything from simple files to entire directories

So I could do the same thing for my projects with my S&T solutions

And this is the entire script where I will download, where this script will download

My template from my GitHub page. It will extract it, install it

and then use it to create a solution. And the most important part is this little line

This is actually the only thing that you need if you already

have the template to generate a new SSD solution. So if I run that little script, 0

It will start momentarily. There we go

So it's going to download it, it's going to extract it, and then install it and create the solution

And we're already done. This is the solution generated just with this script

If I open that, And of course, Fisher Studio opens in another window, so I'll take a couple seconds

Yes, there we go. So, and I apologize for the resolution, but there are two projects within my solution

which is the data and the tests. And the data will contain all the objects, all the stock changes, all the tables, all

the objects needed for the database itself, and the tests will only contain unit tests

and integration tests for my database. And this way I can really generate

a lot of these projects fast because I can do imports from here

Let's see if I run my second script, so I'm going to use a little database

which does create about 10 tables and like 50 store procedures. There we go

So if I go there, there it is. And I change this to object because we don't have that many objects

I'm going to import my database. Done. Now I have my store procedures and my tables

And you can do the same thing for your databases to get you started with your putting everything

in your state-based solutions that way. And one upside to this is, which I want to show you

is that that little directory that we created also has a build

and it has a Azure pipeline. YAML file and this YAML file will automatically

be detected if you say new and create new pipeline from Azure DevOps

And you select your Azure repos from that point on, or your repo that you're going to use

It will find this file and you just have to select it

and say edit. and this YAML file has all the little things

for official studio, the build, and also test for SQL Server on 2017

and same thing for 2019. So this is all generated. This is all done for you

You don't have to build it yourself. I just had to explain a lot about the pipelines, Kevin

Uh, yeah, apart from how it looks in Azure DevOps. Oh, we can demonstrate it later on

We can show that. OK, well, in that case, what about unit test

because I know we can do that for other programming languages within Azure DevOps

Yeah, so I'm a big fan of unit tests and especially with databases

which is really underrated. And I like to use the T SQL T framework

T SQL T is an open source unit testing framework, which you have to install with your database

and you can use, build really sophisticated unit tests with that One upside though because I really really really lazy when it comes to repetitive tasks I created a PowerShell module called the PS PowerShell T Test Generator

The PS T-SQLT Test Generator. And that actually takes your current state of your database

and then creates all the basic unit tests like if my table there is my

short procedure there does my function have the correct parameters for the

correct data types the same for the tables etc and if I run that so if I

write the script it will pull that database see okay I've got

I've got my database, let's do some object existence tests and it's going to generate it and push that directly into the directory of my SSDT solution because I selected it that way

And this takes like a minute or two, it generates over 120 tests

I want to emphasize you only do this once because as soon as you if you do this every

time your tests are always going to be successful because it takes the current state of your database

Very cool. View cards, we don't have any views. So if we then go back to the project and say add

And go to test basic. We have all our basic unit tests and stuff that it tests is my collation correct

Let me increase this a bit. So is my collation correct? Is my store procedure there

Does my table have the correct columns, etc., etc., etc. It all generates it for you

So you have a basic unit testing already there for you, and you can just go ahead and start your pipeline. So

Another good thing we can do is we can link our pipeline

activities to our work items. In fact, one of the advantages of Azure DevOps is that it's so

integrated we can link our repo updates to work items as well

Plus the test cases. OK, well, I've seen we can use either GUI tasks or YAML for pipelines

Which one should be used? Because I've not seen any of the pipeline set either

So, well, the GUI is good for exploring the pipelines, but YAML is easier to do

So actually, it's not that difficult. As soon as you have your Azure pipeline YAML file already there

it's also going to be in source code in your in your repo and you can just go to the marketplace

and add other sections and it will transform that little part that you want to insert into

the yaml and insert it where at that point the cursor is in your yaml file for the pipeline

and of course we can use sql deployments you can call powershell for your yaml to handle deployments if you're dealing with a large number

of servers yeah you can do that yes we can so what we'll have to do is to let powershell output

the XML format in a J unit format to get the test results and then allow Azure DevOps to use that

XML and the task which publish the test results to view the results visually. And of course we

can put other mechanisms in place for that as well. Awesome so I'm just going to take control of the

Is it sharing the right screen now Nope No your video is sharing but not just yeah no it sharing this Yeah thank you for that

So I was just looking at the pipeline that you just created from running

your PowerShell code there. And then I did notice that we see

all the tasks that you can add as well. Additionally to your pipeline

on the right hand side here. Are we limited to only those

No, no, no, there are a bunch of others you can import from the marketplace

Of course, we are an enterprise company and we may have to request the admin to install those for us

I didn't realize that was a thing. Oh yeah. But it does mean we can get help with those security

compliancy issues you were talking about earlier with the repositories. Okay, well as I mentioned earlier I've seen we can use Microsoft hosted agents

even within our own network on-premises, which is service like Microsoft hosted in Azure to do a

a lot of the processing of the build and release tasks basically your deployment pipelines

like building the various artifacts which sanders showed just now and deploying them

um however it looks like because of our infrastructure we'd have a lot of issues

internally because we're going to have to raise a lot of firewall requests just to have these

hosted agents connected into our service to do all these deployments. So maybe we're better off

building our own agents internally. Yeah, maybe. But remember, a lot of us are developers and we

don't want to build our own servers. Plus we have to figure out exactly what software we need to get

installed on the agent to maintain them, right? Oh, that's cool. So for the benefit of everybody else

is seeing it here, Microsoft actually provides a guide on how to create images with the various agents in GitHub

I understand that the title may be a bit confusing for some of you

It used to be just the repo to deploy Azure DevOps agents

However, because like Sando talked about earlier, Microsoft are now putting a lot of development into GitHub as well

They are using the same image for Azure DevOps agents and agents that you can use with GitHub Actions now

And in fact, if I right click on one of these as well, you can actually view all the software that will be installed if you follow one of these guides

Oh. So well, if we're going to do that, we might have run the agent through

the command line instead of a service, right? How come? Everybody knows that running something

for the command line gives you more results. I do that all the time

Well, OK, I actually did test it earlier, So let's see the results in the next slide

Oh, are you ready? Yeah. And the old. Nothing. Only one line

Yeah, so you don't actually get any for both results. So up to the next part, let's go to test plans

Hmm, pretty rush for you there. Yeah, so of course we can do test plans once we've done the initial build and release before

release elsewhere. And don't know you all have to be automated though because that would take a lot of effort

for some items. Not necessarily, test plans can have manual steps as well

So however our trial period has expired, so you would have to imagine it from this part right now

Yeah, so basically if you want to use test plans all the time in Azure DevOps

you need a slightly different license called Basic Plus Test Plans. You can, if you have your own organisation, sign up for a 30-day trial

has expired and we haven renewed the new license so yeah If I will quickly show you where they are here so for test plans you would just have to imagine that you can create your test plans here but like sander was just

saying you can have a lot of manual steps with this as well yep so we better discuss artifacts

which is where we share various things like Nougat packages with other people

For example, you can create a Nougat package and share it on the Azure artifacts for others to use

Yeah, but it's a bit strange because when I first went to do it earlier

I couldn't see, wasn't able to do it with the organization I created with my Free Fish

well for free my official studio subscription yeah it's and i know what it is it's probably because

you have to set them up in your official studio subscription benefits page ah okay um on that note

by the way just a reminder for all of you out there now if you have official studio subscription

you are able to have your own Azure DevOps organization to test things on

And you can also do it as well outside of having official studio subscription, but you get a few extra

bits on letter belief if you have official studio subscription in place

OK, but just in case you want to test things without the

fear of breaking anything in your company's Azure DevOps organization. Don't test those kind of things in the official things in the official parts

Please don't do that. Yeah, I mean to be honest there are other features I'm looking forward to the team using as well

in Azure DevOps. I mean for instance on the various dashboards that we could configure

So if you're doing things like a daily stand up before scrum master, these dashboards are for yourself

Some of this dashboard is going to be really good because you can use a sprint go extension

we mentioned earlier to put a ridge in place so you can remind everybody to start the daily stand up

What the sprint girl is? You can also see some stats to do with your releases, your sprint

burn down depending on. So you can see the sprint burn down go down every day when your team's

closed off various tasks or they've reduced the errors on them etc etc don't forget the wiki

that's true you can use the wiki as well to put in information for your team to use on how to do

various things standards you've agreed on etc also there is the new ytics fuse feature well it's

I say it's new but it's currently in preview and the last time I looked you're actually

after in preview for your own organization and basically all the metrics that you see here from

Azure boards you can actually use output to a Power BI dashboards trust me I've actually used

this myself to replicate a sprint review report and it is really good I find it quite useful so

So it's worth experimenting with. So. Where shall we begin

Well, I suggest that we start by getting both of our teams together and explain to them

what we're trying to achieve to get them to buy into it

And take it from there. OK, so. That's the end of our session

Are there any questions? Hi Kevin, hi Sandra. This is Pooja Lachpal and I cannot

see any questions right now. I think it's really a great session

Nobody has any doubt and we really learned a lot. So. These are the feedback codes we promised to show again

If there are no questions, all that's left to say is hope you enjoyed the session and

hope you enjoyed the rest of this event. Yeah, and thank you very much for having us again