This blog post is about a situation where I  use Visual Studio Team Services (VSTS) to build/deploy my DEMOs. Those DEMOs are what I use to illustrate Continuous Integration & Continuous Delivery (important parts of that thing called DevOPs).

I use my own personal VSTS account so that I am not showing anything commercially sensitive.

One thing with using a basic account is that you only get 240 build minutes per month. These builds are utilising the hosted build agents in Azure.

This works great – up until I have 4 DEMOs that are different and require testing before I present them. In May 2017 I had 4 DEMOs to conduct:

Code Camp Christchurch on May 13th  where I was speaking twice: (VSTS and Azure DEMO and SQL Server Data Tools DEMO) both of which would be using VSTS.

A webinar for the DBA Virtual Chapter on May 18th which was on “DevOPs and the DBA”.

SQL Saturday Brisbane on May 27th – mostly the same DEMO as the DBA VC.

Whilst the DEMOs themselves would only use 12 minutes of build time – the preparation of them would push me over the limit. I also wanted to try quite a few new things related to SSDT, DACPACs and other things related to SQL Azure.

However…..

You can use your own hosted (known as ‘private ‘) build agent and connect to up to VSTS – so long as it has access and the right tools installed on it….

That is another reason to use your own hosted agent — you may have specific software required for your build(s). In my case I would be doing a lot of builds relating only to SQL Server, Azure SQL Database and associated tools.

Oh yeah — VSTS allows one free private hosted agent.

Hooray!!

So I have created a dedicated build agent just for my SQL Server activities.

Here is a step by step guide to installing a private hosted build agent. For authentication I will be using a Personal Access Token (PAT) for the agent to authenticate to VSTS.

1. Log into VSTS and from your home page, open your profile. Go to your security details.
2. Configure the PAT — for the scope select Agent Pools (read, manage) and make sure all the other boxes are cleared
3. Save the token somewhere as you will need it when installing/configuring the agent on the on-premises server.
4. Log on to the machine using the account for which you have permissions to install software and also access VSTS
5. In your web browser, sign on to VSTS, and navigate to the Agent pools tab: https://{your_account}.visualstudio.com/_admin/_AgentPool

   

6. Click Download agent.
7. On the Get agent dialog box, click Windows.
8. Click the Download button.
9. On the server – extract the ZIP file in a directory and run config.cmd
10. Fill in details as required – you will use the PAT token from step 2/3 above:
11. Go back into VSTS and you will now have a new hosted agent:
12. For the particular project — go into Settings | Agent Queues and choose the existing pool that will have the newly installed private agent in it.
13. Next we want to associate our build steps with this queue – so edit your build definition and choose the Agent Queue from above:
14. Now the REAL fun begins – let’s queue a build!!:
    
15. And of course it works the first time….
16. If  we now look in generated artifact we will have our desired result – a DACPAC file

That we can now use VSTS to deploy out to our Azure SQL Database (and also on-premises SQL Server).

Which is the basis for another blog post…..

Yip.