How you get the instances in your estate is going to be different per reader but here is an example using Registered Servers from my local registered servers list, you can also use a Central Management Server
Get-DbaRegisteredServer -Group local
So we can gather those instances into a variable and pass that to Get-DbaInstanceInstallDate
$SqlInstances = Get-DbaRegisteredServer -Group local
Get-DbaInstanceInstallDate -SqlInstance $SqlInstances
Add to database
To add the results of any PowerShell command to a database, you can pipe the results to Write-DbaDbTableData
As well as many other things, the fantastical BDFL of dbatools Chrissy Lemaire @cl and myself have written enough of a chunk of Learn dbatools in a Month of Lunches that our publisher Manning Publications have agreed to release it as a MEAP. Not a text book, this book is written in a fun conversational style and split up into chapters that you can read in a lunch-time.
It is impossible for me to hear MEAP and not think of this 🙂
What is MEAP? A book can take a year or more to write, so how do you learn that hot new technology today? The answer is MEAP, the Manning Early Access Program. In MEAP, you read a book chapter-by-chapter while it’s being written and get the final eBook as soon as it’s finished. If you pre-order the pBook, you’ll get it long before it’s available in stores.
Basically, to make it easy to get and for those that like to get in early, you can order the book and get the first 4 chapters (three in reality) RIGHT NOW!! (It also means that Chrissy and I have to write the rest of book – dang still going to be busy!)
The live book is fantastic, you can read the whole book from within your browser. See the three icons that appear to the right of the book?
The left hand one enables you to bookmark an important part so that you can come back to it easily using the bookmarks link in the top right
The middle icon enables you to write notes for yourself, maybe ways that you can use the information or maybe comments about an awesome Italian.
The last one is the way that you can make comments and engage us , the authors in conversation, ask questions, request clarification or wonder about Dutch data manglers
If you select a piece of text, another menu opens up
The first icon lets you highlight the text, to make it easier to find later
Hover over the highlight and you can choose different colours for different things.
or even create pretty pictures for Mathias
You can choose to annotate, which is sort of like highlighting and writing a note with the next icon
When you want to share a link to a particular part of the book with someone else, you can highlight part of it and click the link icon
Which will highlight the paragraph and open a dialogue at the bottom where you can create and copy the link.
By far the most important part for Chrissy and I is the last link. When you find something wrong you can mark it for our attention. Yes, even with Chrissy and I proof reading each others words, the fabulous proof reader Cláudio Silva (b | t) and awesome tech editor Mike Shepard (b | t) as well as many community reviewers there are still, and will continue to be, issues. So when you find them, highlight them and click the right hand most link
This will open up as shown so that you can fill in what was wrong (Please don’t report this error again Shane b | t has beaten you to it!)
You will have noticed on social media and elsewhere that we have left some easter eggs in the book
Whenever you find them or whenever you want to talk about the book on social media, please use the hashtag #dbatoolsMoL – you never know what goodies may end up in your inbox.
Oh and if you have got this far and don’t know what dbatools in a Month of Lunches is, listen to the hair and read more https://dbatools.io/meap/
I have done a lot of writing in the last few months but you see no blog posts! My wonderful friend Chrissy and I are writing “dbatools in a Month of Lunches” to be published by Manning. That has taken up a lot of my writing mojo. We have hit a little break whilst we have some reviews done ready for the MEAP (For everyone who asks, the answer is the unfulfilling ‘soon’) so it’s time for a blog post!
SQL Notebooks are cool
I have had a lot of fun with SQL Notebooks recently. I have presented a session about them at a couple of events this month DataGrillen and SQL Saturday Cork. Here is a little snippet
Yes, you can run PowerShell in a SQL Notebook in Azure Data Studio just by clicking a link in the markdown cell. This opens up a lot of excellent possibilities.
I have had several discussions about how SQL Notebooks can be used by SQL DBAs within their normal everyday roles. (Mainly because I don’t really understand what the sorcerers of data science do with notebooks!). I have helped clients to look at some of their processes and use SQL Notebooks to help with them. Creating Disaster Recovery or Change Run-books or Incident Response Templates or using them for product demonstrations. Of course, I needed to use PowerShell in that 🙂
I have really enjoyed working out how to run PowerShell in the markdown in a SQL Notebook in Azure Data Studio and I think Anthony the kubernetes magician did too!
First, before I go any further, I must say this. I was at the European PowerShell Conference when I was working this out and creating my sessions and I said the words
“Cool, I can click a link and run PowerShell, this is neat”
A Beardy fellow in Hannover
This stopped some red team friends of mine in their tracks and they said “Show me”. One of them was rubbing their hands with glee! You can imagine the sort of wicked, devious things that they were immediately considering doing.
Yes, it’s funny but also it carries a serious warning. Without understanding what it is doing, please don’t enable PowerShell to be run in a SQL Notebook that someone sent you in an email or you find on a GitHub. In the same way as you don’t open the word document attachment which will get a thousand million trillion pounddollars into your bank account or run code you copy from the internet on production without understanding what it does, this could be a very dangerous thing to do.
With that warning out of the way, there are loads of really useful and fantastic use cases for this. SQL Notebooks make great run-books or incident response recorders and PowerShell is an obvious tool for this. (If only we could save the PowerShell output in a SQL Notebook, this would be even better)
How on earth did you work this out?
Someone asked me how I worked it out. I didn’t! It began with Vicky Harp PM lead for the SQL Tools team at Microsoft
I then went and looked at Kevin Cunnane‘s notebook. Kevin is a member of the tools team working on Azure Data Studio. With SQL Notebooks, you can double click the markdown cell and see the code that is behind it. To understand how it is working, lets deviate a little.
IF you click the cog at the bottom left of Azure Data Studio and choose Keyboard Shortcuts
you can make Azure Data Studio (and Visual Studio Code) work exactly how you want it to. Typing in the top box will find a command and you can then set the shortcuts that you want to use to save yourself time.
This also enables you to see the command that is called when you use a keyboard shortcut. For example, you can see that for the focus terminal command it says workbench.action.terminal.focus.
It turns out that you can call this as a link in a Markdown document using HTML with <a href=""> and adding command: prior to the command text. When the link is clicked the command will run. Cool 🙂
For this to be able to work (you read the warning above?) you need to set the Notebook to be trusted by clicking this button.
This will allow any command to be run. Of course, people with beards will helpfully advise when this is required for a SQL Notebook. (Safe to say people attempting nefarious actions will try the same with your users)
Now that we know how to run an Azure Data Studio command using a link in a markdown cell the next step is to run a PowerShell command. I headed to the Visual Studio Code documentation and found
Send text from a keybinding The workbench.action.terminal.sendSequence command can be used to send a specific sequence of text to the terminal, including escape sequence
That’s the command we need, however, we still need to craft the command so that it will work as a link. It needs to be converted into a URL.
This will take a PowerShell command and turn it into a link that will work in an Azure Data Studio markdown. It’s not magic, it’s PowerShell. There is a –ToClipboard parameter which will copy the code to the clipboard ready for you to paste into the cell (On Windows machines only)
There are many uses for this but here’s one I think is cool.
The link below will go to a notebook, which will show how you the giants upon whose shoulders I stand
Now I can use this yaml as configuration as code for my Build Pipeline 🙂 It can be used from any Azure DevOps project. Once you start looking at the code and the documentation for the yaml schema you can begin to write your pipelines as YAML, but sometimes it is easier to just create build pipeline or even just a job step in the browser and click the view yaml button!
Create an AKS Cluster with a SQL 2019 container using Terraform and Build templates
This time I am going to choose the Configuration as code template
I am going to give it a name and it will show me that it needs the path to the yaml file containing the build definition in the current repository.
Clicking the 3 ellipses will pop-up a file chooser and I pick the build.yaml file
The build.yaml file looks like this. The name is the USER/Repository Name and the endpoint is the name of the endpoint for the GitHub service connection in Azure DevOps. The template value is the name of the build yaml file @ the name given for the repository value.
You can find (and change) your GitHub service connection name by clicking on the cog bottom left in Azure DevOps and clicking service connections
I still need to create my variables for my Terraform template (perhaps I can now just leave those in my code?) For the AKS Cluster build right now I have to add presentation, location, ResourceGroupName, AgentPoolName, ServiceName, VMSize, agent_count
Then I click save and queue and the job starts running
If I want to edit the pipeline it looks a little different
The variables and triggers can be found under the 3 ellipses on the top right
It also defaults the trigger to automatic deployment.
It takes a bit longer to build
and when I get the Terraform code wrong and the build fails, I can just alter the code, commit it, push and a new build will start and the Terraform will work out what is built and what needs to be built!
but eventually the job finishes successfully
and the resources are built
and in Visual Studio Code with the Kubernetes extension installed I can connect to the cluster by clicking the 3 ellipses and Add Existing Cluster
I choose Azure Kubernetes Services and click next
Choose my subscription and then add the cluster
and then I can explore my cluster
I can also see the dashboard by right clicking on the cluster name and Open Dashboard
Right clicking on the service name and choosing describe
shows the external IP address, which I can put into Azure Data Studio and connect to my container
So I now I can source control my Build Job Steps and hold them in a central repository. I can make use of them in any project. This gives me much more control and saves me from repeating myself repeating myself. The disadvantage is that there is no handy warning when I change the underlying Build Repository that I will be affecting other Build Pipelines and there is no easy method to see which Build Pipelines are dependent on the build yaml file
In my last post I showed how to build an Azure DevOps Pipeline for a Terraform build of an Azure SQLDB. This will take the terraform code and build the required infrastructure.
The plan all along has been to enable me to build different environments depending on the requirement. Obviously I can repeat the steps from the last post for a new repository containing a Terraform code for a different environment but
If you are going to do something more than once Automate It
who first said this? Anyone know?
The build steps for building the Terraform are the same each time (if I keep a standard folder and naming structure) so it would be much more beneficial if I could keep them in a single place and any alterations to the process only need to be made in the one place 🙂
A task group allows you to encapsulate a sequence of tasks, already defined in a build or a release pipeline, into a single reusable task that can be added to a build or release pipeline, just like any other tas
Here’s the thing, creating a task group is so easy it should be the default way you create Azure DevOps Pipelines. Let me walk you through it
I will use the Build Pipeline from the previous post. Click edit from the build page
Then CTRL and click to select all of the steps
Right Click and theres a Create Task Group button to click !
You can see that it has helpfully added the values for the parameters it requires for the location, Storage Account and the Resource Group.
Remember the grey beard hair above? We need to change those values to use the variables that we will add to the Build Pipeline using
Once you have done that click Create
This will also alter the current Build Pipeline to use the Task Group. Now we have a Task Group that we can use in any build pipeline in this project.
Using the Task Group with a new Build Pipeline to build an Azure Linux SQL VM
Lets re-use the build steps to create an Azure SQL Linux VM. First I created a new GitHub Repository for my Terraform code. Using the docs I created the Terraform to create a resource group, a Linux SQL VM, a virtual network, a subnet, a NIC for the VM, a public IP for the VM, a netwwork security group with two rules, one for SQL and one for SSH. It will look like this
The next step is to choose the repository
again we are going to select Empty job (although the next post will be about the Configuration as Code 🙂
As before we will name the Build Pipeline and the Agent Job Step and click the + to add a new task. This time we will search for the Task Group name that we created
I need to add in the variables from the variable.tf in the code and also for the Task Group
and when I click save and queue
It runs for less than 7 minutes
and when I look in the Azure portal
and I can connect in Azure Data Studio
Altering The Task Group
You can find the Task Groups under Pipelines in your Azure DevOps project
Click on the Task Group that you have created and then you can alter, edit it if required and click save
This will warn you that any changes will affect all pipelines and task groups that are using this task group. To find out what will be affected click on references
which will show you what will be affected.
Now I can run the same build steps for any Build Pipeline and alter them all in a single place using Task Groups simplifying the administration of the Build Pipelines.
In my last post I showed how to create a Resource Group and an Azure SQLDB with Terraform using Visual Studio Code to deploy.
Of course, I havent stopped there, who wants to manually run code to create things. There was a lot of install this and set up that. I would rather give the code to a build system and get it to run it. I can then even set it to automatically deploy new infrastructure when I commit some code to alter the configuration.
This scenario though is to build environments for presentations. Last time I created an Azure SQL DB and tagged it with DataInDevon (By the way you can get tickets for Data In Devon here – It is in Exeter on April 26th and 27th)
If I want to create the same environment but give it tags for a different event (This way I know when I can delete resources in Azure!) or name it differently, I can use Azure DevOps and alter the variables. I could just alter the code and commit the change and trigger a build or I could create variables and enable them to be set at the time the job is run. I use the former in “work” situations and the second for my presentations environment.
I have created a project in Azure DevOps for my Presentation Builds. I will be using GitHub to share the code that I have used. Once I clicked on pipelines, this is the page I saw
Clicking new pipeline, Azure DevOps asked me where my code was
I chose GitHub, authorised and chose the repository.
I then chose Empty Job on the next page. See the Configuration as code choice? We will come back to that later and our infrastructure as code will be deployed with a configuration as code 🙂
The next page allows us to give the build a good name and choose the Agent Pool that we want to use. Azure DevOps gives 7 different hosted agents running Linux, Mac, Windows or you can download an agent and run it on your own cpus. We will use the default agent for this process.
Clicking on Agent Job 1 enables me to change the name of the Agent Job. I could also choose a different type of Agent for different jobs within the same pipeline. This would be useful for testing different OS’s for example but for right now I shall just name it properly.
First we need somewhere to store the state of our build so that if we re-run it the Terraform plan step will be able to work out what it needs to do. (This is not absolutely required just for building my presentation environments and this might not be the best way to achieve this but for right now this is what I do and it works.)
I click on the + and search for Azure CLI.
and click on the Add button which gives me some boxes to fill in.
I choose my Azure subscription from the first drop down and choose Inline Script from the second
Inside the script block I put the following code
# the following script will create Azure resource group, Storage account and a Storage container which will be used to store terraform state
call az groupcreate--location$(location)--name$(TerraformStorageRG)
call az storage account create--name$(TerraformStorageAccount)--resource-group$(TerraformStorageRG)--location$(location)--sku Standard_LRS
call az storage container create--name terraform--account-name$(TerraformStorageAccount)
This will create a Resource Group, a storage account and a container and use some variables to provide the values, we will come back to the variables later.
The next thing that we need to do is to to enable the job to be able to access the storage account. We don’t want to store that key anywhere but we can use our Azure DevOps variables and some PowerShell to gather the access key and write it to the variable when the job is running . To create the variables I clicked on the variables tab
and then added the variables with the following names TerraformStorageRG, TerraformStorageAccount and location from the previous task and TerraformStorageKey for the next task.
With those created, I go back to Tasks and add an Azure PowerShell task
I then add this code to get the access key and overwrite the variable.
# Using this script we will fetch storage key which is required in terraform file to authenticate backend stoarge account
description="The name of the Azure SQL database on - needs to be unique, lowercase between 3 and 24 characters including the prefix"
description="The Edition of the Database - Basic, Standard, Premium, or DataWarehouse"
description="The Service Tier S0, S1, S2, S3, P1, P2, P4, P6, P11 and ElasticPool"
It is exactly the same except that the values have been replaced by the value name prefixed and suffixed with __. This will enable me to replace the values with the variables in my Azure DevOps Build job.
The backend-config.tf file will store the details of the state that will be created by the first step and use the access key that has been retrieved in the second step.
I need to add the following variables to my Azure DevOps Build – Presentation, ResourceGroupName, SqlServerName, SQLServerAdminUser, SQLServerAdminPassword, SqlDatabaseName, Edition, ServiceObjective . Personally I would advise setting the password or any other sensitive values to sensitive by clicking the padlock for that variable. This will stop the value being written to the log as well as hiding it behind *’s
Because I have tagged the variables with Settable at queue time , I can set the values whenever I run a build, so if I am at a different event I can change the name.
But the build job hasn’t been set up yet. First we need to replace the values in the variables file.
I am going to use a standard naming convention for my infrastructure code files so I add Build to the Root Directory. You can also click the ellipses and navigate to a folder in your repo. In the Target Files I add *”*/*.tf” and “**/*.tfvars” which will search all of the folders (**) and only work on files with a .tf or .tfvars extension (/*.tfvars) The next step is to make sure that the replacement prefix and suffix are correct. It is hidden under Advanced
Because I often forget this step and to aid in troubleshooting I add another step to read the contents of the files and place them in the logs. I do this by adding a PowerShell step which uses
Under control options there is a check box to enable or disable the steps so once I know that everything is ok with the build I will disable this step. The output in the log of a build will look like this showing the actual values in the files. This is really useful for finding spaces :-).
Running the Terraform in Azure DevOps
With everything set up we can now run the Terraform. I installed the Terraform task from the marketplace and added a task. We are going to follow the same process as the last blog post, init, plan, apply but this time we are going to automate it 🙂
First we will initialise
I put Build in the Terraform Template path. The Terraform arguments are
which will tell the Terraform to use the backend-config.tfvars file for the state. It is important to tick the Install terraform checkbox to ensure that terraform is available on the agent and to add the Azure Subscription (or Service Endpoint in a corporate environment
After the Initialise, I add the Terraform task again add Build to the target path and this time the argument is plan
Again, tick the install terraform checkbox and also the Use Azure Service Endpoint and choose the Azure Subscription.
We also need to tell the Terraform where to find the tfstate file by specifying the variables for the resource group and storage account and the container
Finally, add another Terraform task for the apply remembering to tick the install Terraform and Use Azure checkboxes
Now we can build the environment – Clicking Save and Queue
opens this dialogue
where the variables can be filled in.
The build will be queued and clicking on the build number will open the logs
6 minutes later the job has finished
and the resources have been created.
If I want to look in the logs of the job I can click on one of the steps and take a look. This is the apply step
Do it Again For Another Presentation
So that is good, I can create my environment as I want it. Once my presentation has finished I can delete the Resource Groups. When I need to do the presentation again, I can queue another build and change the variables
The job will run
and the new resource group will be created
all ready for my next presentation 🙂
This is brilliant, I can set up the same solution for different repositories for different presentations (infrastructure) and recreate the above steps.
I have been using Terraform for the last week or so to create some infrastructure and decided to bring that knowledge back to a problem that I and others suffer from – building environments for presentations, all for the sake of doing some learning.
What is Terraform?
According to the website
HashiCorp Terraform enables you to safely and predictably create, change, and improve infrastructure. It is an open source tool that codifies APIs into declarative configuration files that can be shared amongst team members, treated as code, edited, reviewed, and versioned
This means that I can define my infrastructure as code. If I can do that then I can reliably do the same thing again and again, at work to create environments that have the same configuration or outside of work to repeatedly build the environment I need.
refers to the name property in the azure_resource_group block called test (or the name of the resource group 🙂 )
Infrastructure As Code
So I can put that code into a file (name it main.tf) and alter it with the values and “run Terraform” and what I want will be created. Lets take it a step further though because I want to be able to reuse this code. Instead of hard-coding all of the values I am going to use variables. I can do this by creating another file called variables.tf which looks like
description="The name of the presentation - used for tagging Azure resources so I know what they belong to"
description="The Resource Group Name"
description="The Azure Region in which the resources in this example should exist"
description="The name of the Azure SQL Server to be created or to have the database on - needs to be unique, lowercase between 3 and 24 characters including the prefix"
description="The name of the Azure SQL Server Admin user for the Azure SQL Database"