I had set the Network security rules to accept connections only from my static IP using variables in the Build Pipeline. I use MobaXterm as my SSH client. Its a free download. I click on sessions
Choose a SSH session and fill in the remote host address from the portal
fill in the password and
Configuring SQL
The next task is to configure the SQL installation. Following the instructions on the Microsoft docs site I run
Luckily, you can 😉 You can use Azure DevOps Job Templates to achieve this. There is a limitation at present, you can only use them for Build Pipelines and not Release Pipelines.
The aim of this little blog series was to have a single Build Pipeline stored as code which I can use to build any infrastructure that I want with Terraform in Azure and be able to use it anywhere
Creating a Build Pipeline Template
I created a GitHub repository to hold my Build Templates, feel free to use them as a base for your own but please don’t try and use the repo for your own builds.
There is a View YAML button. I can click this to view the YAML definition of the Build Pipeline
I copy that and paste it into a new file in my BuildTemplates repository. (I have replaced my Azure Subscription information in the public repository)
jobs:
- job: Build
pool:
name: Hosted VS2017
demands: azureps
steps:
- task: AzureCLI@1
displayName: 'Azure CLI to deploy azure storage for backend'
inputs:
azureSubscription: 'PUTYOURAZURESUBNAMEHERE'
scriptLocation: inlineScript
inlineScript: |
# the following script will create Azure resource group, Storage account and a Storage container which will be used to store terraform state
call az group create --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)
- task: AzurePowerShell@3
displayName: 'Azure PowerShell script to get the storage key'
inputs:
azureSubscription: 'PUTYOURAZURESUBNAMEHERE'
ScriptType: InlineScript
Inline: |
# Using this script we will fetch storage key which is required in terraform file to authenticate backend stoarge account
$key=(Get-AzureRmStorageAccountKey -ResourceGroupName $(TerraformStorageRG) -AccountName $(TerraformStorageAccount)).Value[0]
Write-Host "##vso[task.setvariable variable=TerraformStorageKey]$key"
azurePowerShellVersion: LatestVersion
- task: qetza.replacetokens.replacetokens-task.replacetokens@3
displayName: 'Replace tokens in terraform file'
inputs:
rootDirectory: Build
targetFiles: |
**/*.tf
**/*.tfvars
tokenPrefix: '__'
tokenSuffix: '__'
- powershell: |
Get-ChildItem .\Build -Recurse
Get-Content .\Build\*.tf
Get-Content .\Build\*.tfvars
Get-ChildItem Env: | select Name
displayName: 'Check values in files'
enabled: false
- task: petergroenewegen.PeterGroenewegen-Xpirit-Vsts-Release-Terraform.Xpirit-Vsts-Release-Terraform.Terraform@2
displayName: 'Initialise Terraform'
inputs:
TemplatePath: Build
Arguments: 'init -backend-config="0-backend-config.tfvars"'
InstallTerraform: true
UseAzureSub: true
ConnectedServiceNameARM: 'PUTYOURAZURESUBNAMEHERE'
- task: petergroenewegen.PeterGroenewegen-Xpirit-Vsts-Release-Terraform.Xpirit-Vsts-Release-Terraform.Terraform@2
displayName: 'Plan Terraform execution'
inputs:
TemplatePath: Build
Arguments: plan
InstallTerraform: true
UseAzureSub: true
ConnectedServiceNameARM: 'PUTYOURAZURESUBNAMEHERE'
- task: petergroenewegen.PeterGroenewegen-Xpirit-Vsts-Release-Terraform.Xpirit-Vsts-Release-Terraform.Terraform@2
displayName: 'Apply Terraform'
inputs:
TemplatePath: Build
Arguments: 'apply -auto-approve'
InstallTerraform: true
UseAzureSub: true
ConnectedServiceNameARM: 'PUTYOURAZURESUBNAMEHERE'
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
If you are doing this with a more complicated existing build pipeline it is important that you read the Before You Create A Task Group on the docs page. This will save you time when trying to understand why variables are not available (Another grey hair on my beard!)
Creating A Task Group
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
$(VariableName)
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.
State
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 group create --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.
Access Key
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
$key=(Get-AzureRmStorageAccountKey -ResourceGroupName $(TerraformStorageRG) -AccountName $(TerraformStorageAccount)).Value[0]
Write-Host "##vso[task.setvariable variable=TerraformStorageKey]$key"
variable "presentation" {
description = "The name of the presentation - used for tagging Azure resources so I know what they belong to"
default = "__Presentation__"
}
variable "ResourceGroupName" {
description = "The Prefix used for all resources in this example"
default = "__ResourceGroupName__"
}
variable "location" {
description = "The Azure Region in which the resources in this example should exist"
default = "__location__"
}
variable "SqlServerName" {
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"
default = "__SqlServerName__"
}
variable "SQLServerAdminUser" {
description = "The name of the Azure SQL Server Admin user for the Azure SQL Database"
default = "__SQLServerAdminUser__"
}
variable "SQLServerAdminPassword" {
description = "The Azure SQL Database users password"
default = "__SQLServerAdminPassword__"
}
variable "SqlDatabaseName" {
description = "The name of the Azure SQL database on - needs to be unique, lowercase between 3 and 24 characters including the prefix"
default = "__SqlDatabaseName__"
}
variable "Edition" {
description = "The Edition of the Database - Basic, Standard, Premium, or DataWarehouse"
default = "__Edition__"
}
variable "ServiceObjective" {
description = "The Service Tier S0, S1, S2, S3, P1, P2, P4, P6, P11 and ElasticPool"
default = "__ServiceObjective__"
}
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.
Replace the Tokens
I installed the Replace Tokens Task from the marketplace and added that to the build.
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
init -backend-config="0-backend-config.tfvars"
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
The arguments are
apply -auto-approve
This will negate the requirement for the “Only “yes” will be accepted to approve” from the manual steps post!
Build a Thing
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.
Last weekend I was thinking about how to save the tweets for PowerShell Conference Europe. This annual event occurs in Hanover and this year it is on April 17-20, 2018. The agenda has just been released and you can find it on the website http://www.psconf.eu/
I ended up creating an interactive PowerBi report to which my good friend and Data Platform MVP Paul Andrew b | t added a bit of magic and I published it. The magnificent Tobias Weltner b | t who organises PSConfEU pointed the domain name http://powershell.cool at the link. It looks like this.
Details about the tweet and the sentiment are saved in Azure SQL database
A PowerBi report uses that data and provides the report
You will find all of the resources and the scripts to do all of the below in the GitHub repo. So clone it and navigate to the filepath
Create Database
First lets create a database. Connect to your Azure subscription
## Log in to your Azure subscription using the Add-AzureRmAccount command and follow the on-screen directions.
Add-AzureRmAccount
## Select the subscription
Set-AzureRmContext -SubscriptionId YourSubscriptionIDHere
Then set some variables
# The data center and resource name for your resources
$resourcegroupname = "twitterresource"
$location = "WestEurope"
# The logical server name: Use a random value or replace with your own value (do not capitalize)
$servername = "server-$(Get-Random)"
# Set an admin login and password for your database
# The login information for the server You need to set these and uncomment them - Dont use these values
# $adminlogin = "ServerAdmin"
# $password = "ChangeYourAdminPassword1"
# The ip address range that you want to allow to access your server - change as appropriate
# $startip = "0.0.0.0"
# $endip = "0.0.0.0"
# To just add your own IP Address
$startip = $endip = (Invoke-WebRequest 'http://myexternalip.com/raw').Content -replace "`n"
# The database name
$databasename = "tweets"
$AzureSQLServer = "$servername.database.windows.net,1433"
$Table = "table.sql"
$Proc = "InsertTweets.sql"
They should all make sense, take note that you need to set and uncomment the login and password and choose which IPs to allow through the firewall
Create a Resource Group
## Create a resource group
New-AzureRmResourceGroup -Name $resourcegroupname -Location $location
This will have created the following in Azure, you can see it in the portal
You can connect to the database in SSMS and you will see
Create Cognitive Services
Now you can create the Text Analysis Cognitive Services API
First login (if you need to) and set some variables
## This creates cognitive services for analysing the tweets
## Log in to your Azure subscription using the Add-AzureRmAccount command and follow the on-screen directions.
Add-AzureRmAccount
## Select the subscription
Set-AzureRmContext -SubscriptionId YOUR SUBSCRIPTION ID HERE
#region variables
# The data center and resource name for your resources
$resourcegroupname = "twitterresource"
$location = "WestEurope"
$APIName = 'TweetAnalysis'
#endregion
Then create the API and get the key
#Create the cognitive services
$newAzureRmCognitiveServicesAccountSplat = @{
ResourceGroupName = $resourcegroupname
Location = $location
SkuName = 'F0'
Name = $APIName
Type = 'TextAnalytics'
}
New-AzureRmCognitiveServicesAccount @newAzureRmCognitiveServicesAccountSplat
# Get the Key
$getAzureRmCognitiveServicesAccountKeySplat = @{
Name = $APIName
ResourceGroupName = $resourcegroupname
}
Get-AzureRmCognitiveServicesAccountKey @getAzureRmCognitiveServicesAccountKeySplat
You will need to accept the prompt
Copy the Endpoint URL as you will need it.Then save one of the keys for the next step!
Create the Flow
I have exported the Flow to a zip file and also the json for a PowerApp (no details about that in this post). Both are available in the Github repo. I have submitted a template but it is not available yet.
Click select during import next to Sentiment and choose the Sentiment connection
Select during import for the SQL Server Connection and choose the SQL Server Connection and do the same for the Twitter Connection
Then click import
Create the flow without import
If you do not have a premium account you can still create the flow using these steps. I have created a template but it is not available at the moment. Create the connections as above and then click Create from blank.
Choose the trigger When a New Tweet is posted and add a search term. You may need to choose the connection to twitter by clicking the three dots
Click Add an action
search for detect and choose the Text Analytics Detect Sentiment
Enter the name for the connection, the account key and the URL from the creation of the API above. If you forgot to copy them
#region Forgot the details
# Copy the URL if you forget to save it
$getAzureRmCognitiveServicesAccountSplat = @{
Name = $APIName
ResourceGroupName = $resourcegroupname
}
(Get-AzureRmCognitiveServicesAccount @getAzureRmCognitiveServicesAccountSplat).Endpoint | Clip
# Copy the Key if you forgot
$getAzureRmCognitiveServicesAccountKeySplat = @{
Name = $APIName
ResourceGroupName = $resourcegroupname
}
(Get-AzureRmCognitiveServicesAccountKey @getAzureRmCognitiveServicesAccountKeySplat).Key1 | Clip
#endregion
Click in the text box and choose Tweet Text
Click New Step and add an action. Search for SQL Server and choose SQL Server – Execute Stored Procedure
Choose the stored procedure [dbo].[InsertTweet]
Fill in as follows
__PowerAppsID__ 0
Date Created At
Sentiment Score
Tweet Tweet Text
UserLocation Location
UserName Tweeted By
as shown below
Give the flow a name at the top and click save flow
Connect PowerBi
Open the PSConfEU Twitter Analysis Direct.pbix from the GitHub repo in PowerBi Desktop. Click the arrow next to Edit Queries and then change data source settings
Click Change source and enter the server (value of $AzureSQLServer) and the database name. It will alert you to apply changes
It will then pop-up with a prompt for the credentials. Choose Database and enter your credentials and click connect
and your PowerBi will be populated from the Azure SQL Database 🙂 This will fail if there are no records in the table because your flow hasn’t run yet. If it does just wait until you see some tweets and then click apply changes again.
You will probably want to alter the pictures and links etc and then yo can publish the report
Happy Twitter Analysis
Dont forget to keep an eye on your flow runs to make sure they have succeeded.
This weekend (10 December 2016), I went to Slovenia for a SQL Saturday. As always, it was an amazing event well organised by Mladen Prajdic, Dejan Sarka, and Matija Lah in a fabulous setting amongst fabulous scenery. I highly recommend it and, also, Ljubljana is a wonderful place to be in December with all the lights and markets.
Whilst I was there I was asked by someone if you could deploy data science virtual machines in Azure with PowerShell. I said I was sure that it could be done and agreed I would write a blog post, so here it is.
The Data Science Virtual Machine running on a Windows Server 2012 contains popular tools for data exploration, modeling and development activities. The main tools include Microsoft R Server Developer Edition (An enterprise ready scalable R framework) , Anaconda Python distribution, Jupyter notebooks for Python and R, Visual Studio Community Edition with Python, R and node.js tools, Power BI desktop, SQL Server 2016 Developer edition including support In-Database analytics using Microsoft R Server. It also includes open source deep learning tools like Microsoft Cognitive Toolkit (CNTK 2.0) and mxnet; ML algorithms like xgboost, Vowpal Wabbit. The Azure SDK and libraries on the VM allows you to build your applications using various services in the cloud that are part of the Cortana Analytics Suite which includes Azure Machine Learning, Azure data factory, Stream Analytics and SQL Datawarehouse, Hadoop, Data Lake, Spark and more.
I have created a function to wrap around the process to make it easier for none PowerShell people to do this. There are a series of steps to follow below and you should be able to create a machine in about 10 minutes once you have completed the pre-requisites.
Enable Programmatically Deployment
First, an annoyance. To be able to deploy Data Science virtual machines in Azure programmatically you first have to login to the portal and click some buttons.
In the Portal click new and then marketplace and then search for data science. Choose the Windows Data Science Machine and under the blue Create button you will see a link which says “Want to deploy programmatically? Get started” Clicking this will lead to the following blade.
Click Enable and then save and you then move to PowerShell 🙂
Azure PowerShell Cmdlets
Follow the instructions here to install the Azure PowerShell modules. In the examples you see here I am using Windows 10 and PowerShell version 5.1.14393.479 and I installed the Azure modules using the Install-Module method
Get the script
To install a data science VM, we’ll use the New-WindowsDataScienceVM.ps1 script. In this script, I’m using version 1.2, but any version of this script published in PowerShell Gallery is fine.
To install the New-WindowsDataScienceVM script from the PowerShell gallery, type:
Install-Script New-WindowsDataScienceVM
For more information about using the PowerShellGet cmdlets to install scripts and modules from PowerShell Gallery, read this page. The PowerShellGet modules is included in PowerShell 5.0 and later on Windows 10, but you can install PowerShellGet for PowerShell 3.0 and 4.0. If you cannot connect to the gallery or prefer not to install the module, you can also find the script on GitHub.
Login to Azure
You can login to Azure using the command
Login-AzureRMAccount
which will pop-up a prompt for you to log into Azure
Enable Simple Mode
The New-WindowsDataScienceVM function comes with a Simple switch parameter.
If you use -Simple, the function prompts you only for the admin username and password for the virtual machine. It creates a randomly-named, standard_DS1_v2-sized machine in the ukwest data centre with standard, locally redundant storage in a randomly named Resource Group. All of the required objects have random names, too. If that is not what you want, there is more information at the end of this post. I am considering offering a pop-up to choose location in Simple Mode. Let me know here if that would be something you would like
To create a simple data science VM, run:
New-WindowsDataScienceVM -Simple
Enter Local Admin Password
When you run the function, it prompts for a local admin username and password to log into the virtual machine. The password must have 3 of the following 1 Upper case, 1 lower case, I special character and 1 number. Don’t lose it, you will need it.
Grab a cuppa, creating your VM and its resources will take 5 – 10 minutes. (In my testing it reliably took between 7 and 8 minutes) The screen will look like this
When the script has finished running you will have deployed a set of resources like this
Login to the Virtual Machine
Copy and paste the correct code from the output at the end of the script to launch the RDP session and save the RDP file to your documents folder for later use.
Or you can find the Virtual machine name in the portal or by running
You will need to login with the local admin account you set up previously, which means that you will need to click on more choices and then the machinename\Username. In this case the machine name is DSVMZIAgd
You can copy the correct Virtual Machine name and Username from the output at the end of the script.
If you have forgotten your password, you can reset it in the Portal.
Enjoy the Data Science Virtual Machine
You are then logged in and can carry on. Once the Azure PowerShell modules and script are installed you would be able to have a machine up and running within 10 minutes.
Cleaning Up
To remove the resource group and ALL resources in the resource group, including the data science VM, run:
If you use SQL Backup to URL to backup your databases to Azure blob storage remember that for the container name case is important
So
BACKUP LOG [DatabaseName]
TO URL = N'https://storageaccountname.blob.core.windows.net/containername/databasename_log_dmmyyhhss.trn'
WITH CHECKSUM, NO_COMPRESSION, CREDENTIAL = N'credential'
will work but
BACKUP LOG [DatabaseName]
TO URL = N'https://storageaccountname.blob.core.windows.net/CONTAINERNAME/databasename_log_dmmyyhhss.trn'</span>
WITH CHECKSUM, NO_COMPRESSION, CREDENTIAL = N'credential'
will give an (400) Bad Request Error which may not be easy to diagnose
Msg 3271, Level 16, State 1, Line 1
A nonrecoverable I/O error occurred on file "https://storageacccountname.blob.core.windows.net/CONTAINERNAME/databasename_log_dmmyyhhss.trn':" Backup to URL received an exception from the remote endpoint.
Exception Message: The remote server returned an error: (400) Bad Request..
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
If you are using Ola Hallengrens jobs to perform your backup then your job step will look like this
Note the @ChangeBackupType = ‘Y’ parameter which is not created by default but I think is very useful. If you have just created a database and take log backups every 15 minutes but differential (or full) every night the log backup will fail until a full backup has been taken. This parameter will check if a log backup is possible and if not take a full backup meaning that you still can keep to your RTO/RPO requirements even for newly created databases
There are some other considerations too. It is only supported for SQL Server 2014 and Windows Server 2012 and 2012R2 at present and you can set a retention period to a maximum of 30 days but it is automated. You do not have to decide upon the backup strategy Azure will decide the frequency and type of backups dependent upon the workload of the database and some other factors such as
A full backup is taken
○ when an instance is added to use Managed backup
○ When transaction log growth is 1Gb or more
○ At least once a week
○ If the log chain is broken
○ When a database is created
A transaction log backup is taken
– If no log backup is found
– Transaction log space used is 5Mb or larger
– At least once every two hours
– Any time the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.
There are some restrictions
– Only database backups are supported
– System databases are not supported so you need to back those up yourself
– You can only back up to Azure storage
– Maximum backup size is 1Tb as this is the maximum size for a blob in Azure storage
– Simple recovery is not supported
– Maximum retention is 30 days – if you are required to keep your backups for longer than 30 days for regulatory or other reasons you could simply use Azure Automation to copy the files to another storage account in Azure)
How to set it up.
If you are using the GUI then you will find SQL Automated Backup in the optional config blade of the set up. You can follow the steps here to set it up. If (like me) you want to use Powershell then use the following code after you have created your Virtual Machine
Once you have run the code, Azure will take care of the rest. Add a couple of databases to your instance and look in the storage account and you will see this
And in the automaticbackup container you will find the Certificates and master key backups
It will also create a credential
You can use the same credential to back up your system databases. If like me you use Ola Hallengrens excellent Maintenance Solution then simply change your systems backup job as follows
If you need to restore your database then you can use the GUI and when you choose restore you will see this screen
Enter your storage account and the key which you can get from the Azure portal. You will notice that the credential has already been selected, click connect and
There are all of your backups ready to restore to any point in time that you choose. By clicking script the T-SQL is generated which looks like this
USE [master]
BACKUP LOG [Test] TO URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_LogBackup_2015-07-16_06-21-26.bak'
WITH CREDENTIAL = N'AutoBackup_Credential' ,
NOFORMAT, NOINIT, NAME = N'Test_LogBackup_2015-07-16_06-21-26',
NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [Test] FROM URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150714201240+00.bak'
WITH CREDENTIAL = N'AutoBackup_Credential' , FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Test] FROM URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150714202740+00.log'
WITH CREDENTIAL = N'AutoBackup_Credential' , FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Test] FROM URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150714224241+00.log'
WITH CREDENTIAL = N'AutoBackup_Credential' , FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Test] FROM URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715005741+00.log'
WITH CREDENTIAL = N'AutoBackup_Credential' , FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Test] FROM URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715031242+00.log'
WITH CREDENTIAL = N'AutoBackup_Credential' , FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Test] FROM URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715052742+00.log'
WITH CREDENTIAL = N'AutoBackup_Credential' , FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Test] FROM URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715074243+00.log'
WITH CREDENTIAL = N'AutoBackup_Credential' , FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Test] FROM URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715095743+00.log'
WITH CREDENTIAL = N'AutoBackup_Credential' , FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Test] FROM URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150715121243+00.log'
WITH CREDENTIAL = N'AutoBackup_Credential' , FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [Test] FROM URL = N'https://sqlbackupstoragebeard.blob.core.windows.net/asqlvm9-mssqlserver/Test_b8bb98d7a235487d9789b3ee8759cf3e_20150716060004+00.log'
WITH CREDENTIAL = N'AutoBackup_Credential' , FILE = 1, NOUNLOAD, STATS = 5
GO
There is an important note. Remember this when you have just set it up so that you don’t think that you have done it wrong (which is what I did!)
When you enable Automated Patching for the first time, Azure configures the SQL Server IaaS Agent in the background. During this time, the portal will not show that Automated Patching is configured. Wait several minutes for the agent to be installed, configured. After that the portal will reflect the new settings.
The password I had chosen was not complex enough but the Powershell script had succeeded and not given me the warning
To set up SQL Automated Patching you follow a similar steps. The setting is again on the OS Config blade and click enable and then you can choose the frequency and duration of the patching.
It is important to remember to choose your maintenance window correctly. If you have set up your SQL VMs correctly you will have them in an availability set and be using either mirroring or Availability Groups and have the VMs set up in the same availability set to ensure availability during the underlying host patching but I had it confirmed by Principal Software Engineering Manager Sethu Srinivasan t via Microsoft PFE Arvind Shyamsundar b | t that the SQL Automated Patching is not HA aware so you will need to ensure that you set the maintenance windows on each VM to ensure that they do not overlap
Once you have created your Azure File Storage Account and connected your Azure Virtual Machines to it, you may need to upload data from your premises into the storage to enable it to be accessed by the Virtual Machines
To accomplish this I wrote a function and called it Upload-ToAzureFileStorage
I started by creating a source folder and files to test
For the sake only of doing it a different way we can check for existence of the directory in Azure File Storage that we are going to upload the files to like this
$d = Get-AzureStorageFile -Share $s -ErrorAction SilentlyContinue|select Name
if ($d.Name -notcontains $AzureDirectory)
but to create new folders we need to remove the “C:\temp\TestUpload” and replace it with the Directory name in Azure. I chose to do this as follows using the substring method and the length of the source folder path.
and then created the files using Set-AzureStorageFileContent this has a -Force and a -Confirm switch and I added those into my function by using a [switch] Parameter
#upload the files to the storage
if($Confirm)
{
Set-AzureStorageFileContent -Share $s -Source $File.FullName -Path $Path -Confirm
}
else
{
Set-AzureStorageFileContent -Share $s -Source $File.FullName -Path $Path -Force
}
You can download the function from the Script Center
As also, any comments or queries are welcome and obviously the internet lies so please understand and test all code you find before using it in production
Operational Insights is a service that has been added in preview to Azure. It enables you to collect, combine, correlate and visualize all your machine data in one place. It can collect data from all of your machines either via SCOM or by using an agent. Once the data is collected Operational Insights has a number of Intelligence Packs which have pre-configured rules and algorithms to provide analysis in various areas including for SQL Server
I thought I would take a look. I have an installation of SCOM in my lab on my laptop and I read the instructions to see how to connect it to Operational Insights. (You don’t have to have a SCOM installation to use Operational insights you can make use of an agent as well just follow the steps from the page below)
If you have an Azure subscription already you can sign into the portal and join the preview program by clicking
New –> App Services –> Operational Insights
and create a new Operational Insights Workspace.
Once you have done that, if you have an installation of SCOM 2012 you need to be running Service Pack 1 and download and install the System Center Operational Insights Connector for Operations Manager and import the MPB files into SCOM.
If you have SCOM 2012R2 the connector is already installed and to connect your SCOM to Operational Insights is very very easy as you can see on
In the Operations Manager Console, click Administration.
Under Administration, select System Center Advisor, and then click Advisor Connection.
Click Register to Advisor Service.
Sign in with your Microsoft or Organizational account.
Choose an existing Operational Insights workspace from the drop down menu
Confirm your changes.
In the System Center Advisor Overview page, Under Actions, click Add a Computer/Group.
Under Options, select Windows Server or All Instance Groups, and then search and add servers that you want data
That is it. No really, that is it. I was amazed how quickly I was able to get this done in my lab and it would not take very long in a large implementation of SCOM either as you will have your groups of computers defined which will make it easy to decide which groups to use. You could use a separate workspace for each type of server or split up the information per service. It really is very customisable.
Once you have done that, go and add some of the Intelligence Packs. Each intelligence pack will change the amount and type of data that is collected. At November 23rd there are
Alert Management – for your SCOM Alerts
Change Tracking – Tracking Configuration Changes
Log Management – for event log collection and interrogation
System Update Assessment – Missing Security Updates
Malware Assessment – Status of Anti-Malware and Anti-Virus scans
Capacity Planning – Identify Capacity and Utilisation bottlenecks
SQL Assessment – The risk and health of SQL Server Environment
There are also two ‘coming soon’ Intelligence packs
AD Assessment – Risk and health of Active Directory
Security – Explore security related data and help identify security breaches
You then (if you are like me) have a period of frustration whilst you wait for all of the data to be uploaded and aggregated but once it is you sign into the Operational Insights Portal
There is a lot of information there. As it is on my laptop and the lab is not running all of the time and is not connected to the internet most of the time I am not surprised that there are some red parts to my assessment!!
Obviously I was interested in the SQL Assessment and I explored it a bit further
Clicking on the SQL Assessment tile takes you to a screen which shows the SQL Assessment broken down into 6 Focus areas
Security and Compliance, Availability and Business Continuity, Performance and Scalability, Upgrade, Migration and Deployment, Operations and Monitoring and Change and Configuration Management. MSDN http://msdn.microsoft.com/en-us/library/azure/dn873967.aspx gives some more information about each one
Security and Compliance – Safeguard the reputation of your organization by defending yourself from security threats and breaches, enforcing corporate policies, and meeting technical, legal and regulatory compliance requirements.
Availability and Business Continuity – Keep your services available and your business profitable by ensuring the resiliency of your infrastructure and by having the right level of business protection in the event of a disaster.
Performance and Scalability – Help your organization to grow and innovate by ensuring that your IT environment can meet current performance requirements and can respond quickly to changing business needs.
Upgrade, Migration and Deployment – Position your IT department to be the key driver of change and innovation, by taking full advantage of new enabling technologies to unlock more business value for organizational units, workforce and customers.
Operations and Monitoring – Lower your IT maintenance budget by streamlining your IT operations and implementing a comprehensive preventative maintenance program to maximize business performance.
Change and Configuration Management – Protect the day-to-day operations of your organization and ensure that changes won’t negatively affect the business by establishing change control procedures and by tracking and auditing system configurations.
You will be able to see some dials showing you how well you are doing in each area for the servers whose data has been collected.
Each area will have the High Priority Recommendations shown below the dial and you can click on them to see more information about those recommendations
You can also click the dial or the see all link to enter the search area where you can customise how you wish to see the data that has been collected, this looks a bit confusing at first
The top bar contains the search , the timescale and some buttons to save the search, view the saved searches and view the search history, all of which will be shown in the right hand column below
The left column contains a bar graph for the search and all of the filters. The middle column contains the results of the search and can be viewed in list or tabular format and exported to CSV using the button below. A little bit of experimentation will give you a better understanding of how the filtering works and how you can make use of that for your environment
By looking at the search for the Operations and Monitoring Focus Area shown above
I saw that RecommendationResult=Failed and changed it to RecommendationResult=Passed. This enabled me to see all of the Recommendations that had been passed in the Focus Area and clicking the export button downloaded a csv file. I deleted RecommendationResult=Passed from the search and that gave me all of the recommendations that made up that Focus Area
Operations and Monitoring Focus Area
Recommendation
Enable Remote Desktop on servers.
Enable Remote Desktop on virtual machines.
Ensure computers are able to download updates.
Configure event logs to overwrite or archive old events automatically.
Review event log configuration to ensure event data is retained automatically. This relates to System Logs
Review event log configuration to ensure event data is retained automatically. This relates to Application Logs
I decided then to do the same for each of the Focus Areas for the SQL Assessment Intelligence Pack
Security and Compliance Focus Area
Recommendation
Change passwords that are the same as the login name.
Remove logins with blank passwords.
LAN Manager Hash for Passwords Stored
Investigate why unsigned kernel modules were loaded.
Apply security best practices to contained databases.
Enable User Account control on all computers.
Consider disabling the xp_cmdshell extended stored procedure.
Implement Windows authentication on Microsoft Azure-hosted SQL Server deployments.
Avoid using the Local System account to run the SQL Server service.
Avoid adding users to the db_owner database role.
Ensure only essential users are added to the SQL Server sysadmin server role.
Disable SQL Server guest user in all user databases.
Avoid running SQL Server Agent jobs using highly-privileged accounts.
Configure the SQL Server Agent service to use a recommended account.
Apply Windows password policies to SQL Server logins.
Investigate failures to validate the integrity of protected files.
Investigate failures to validate kernel modules.
Availability and Business Continuity Focus Area
Recommendation
Schedule full database backups at least weekly.
Optimize your backup strategy with Microsoft Azure Blob Storage.
Avoid using the Simple database recovery model.
Ensure all installations of Windows are activated.
Investigate logical disk errors.
Reduce the maximum Kerberos access token size.
Investigate connection failures due to SSPI context errors.
Set the PAGE_VERIFY database option to CHECKSUM.
Increase free space on system drives.
Investigate a write error on a disk.
Check the network access to Active Directory domain controllers.
Review DNS configuration on non-DNS servers.
Increase free space on system drives.
Investigate memory dumps.
Increase free space on system drives.
Investigate why the computer shut down unexpectedly.
Enable dynamic DNS registration for domain-joined servers.
Performance and Scalability Focus Area
Recommendation
Increase the number of tempdb database files.
Configure the tempdb database to reduce page allocation contention.
Ensure all tempdb database files have identical initial sizes and growth increments.
Set autogrowth increments for database files and log files to fixed values rather than percentage values.
Set autogrowth increments for transaction log files to less than 1GB.
Modify auto-grow settings to use a fixed size growth increment of less than 1GB and consider enabling Instant File Initialization.
Change your Affinity Mask and Affinity I/O MASK settings to prevent conflicts.
Resolve issues caused by excessive virtual log files.
Modify the database file layout for databases larger than 1TB.
Set the AUTO_CLOSE option to OFF for frequently accessed databases.
Review memory requirements on servers with less than 4GB of physical memory installed.
Configure system SiteName properties to be dynamic.
Align the Max Degree of Parallelism option to the number of logical processors.
Align the Max Degree of Parallelism option to the number of logical processors.
Consider disabling the AUTO_SHRINK database option.
Review memory requirements on computers with high paging file use.
Ensure SQL Server does not consume memory required by other applications and system components.
Consider changing your power saving settings to optimize performance.
Increase the initial size of the tempdb database.
Review the configuration of Maximum Transfer Unit (MTU) size.
Review your paging file settings.
Review and optimize memory cache configuration.
Review the configuration of Maximum Transfer Unit (MTU) size.
Review the system processor scheduling mode.
Review network provider ordering settings.
Remove invalid entries from the PATH environment variable.
Remove network entries from the PATH environment variable.
Investigate processes that use a large number of threads.
Avoid hosting user database files on the same disk volume as tempdb database files.
Review processes with large working set sizes.
Reduce the length of the PATH environment variable.
Reduce the number of entries in the PATH environment variable.
Ensure SQL Server does not consume memory required by other applications and system components.
Enable the backup compression default configuration option.
Ensure the DNS Client service is running and is set to start automatically.
Consider compressing database tables and indexes.
Upgrade, Migration and Deployment Focus Area
Recommendation
Ensure all devices run supported operating system versions.
Ensure that the guest user is enabled in the msdb database.
Avoid using the Affinity64 Mask configuration setting in new development work.
Avoid using the Affinity Mask configuration setting in new development work.
Avoid using the Affinity I/O Mask configuration setting in new development work.
Avoid using the Allow Updates configuration option in SQL Server.
Avoid using the Allow Updates configuration option in SQL Server.
Avoid using the Affinity64 I/O Mask configuration setting in new development work.
Configure SQL Server to accept incoming connections.
Configure SQL Server instances and firewalls to allow communication over TCP/IP.
As I have no data for Change and Configuration Management I was not able to see the recommendations in my Operation Insights Workspace.
Edit: Daniele Muscetta has said in the comments that this is a bug which is being tracked
As you can see from the type and description of the recommendations above these are all areas that a DBA will be concerned about and the benefit of having all of this information gathered, pre-sorted, prioritised and presented to you in this manner will enable you to work towards a better SQL environment and track your progress. You can read more about the SQL Assessment Intelligence Pack here
As well as the pre-determined queries that are built into the Intelligence pack you can search your data in any way that you require enabling you to present information about the health and risk of your SQL Environment to your team or your management with ease. The “with ease” bit is dependent on you understanding the language and structure of the search queries.
You will need to put this page into your bookmarks
by Daniele Muscetta which has a list of useful Operational Insights search queries such as
SQL Recommendation by Computer
Type=SQLAssessmentRecommendation IsRollup=false RecommendationResult=Failed | measure count() by Computer
If you click the star to the right of the search box you will find the saved searches. For the SQL Assessment Intelligence Pack there are
Did the agent pass the prerequisite check (if not, SQL Assessment data won’t be complete)
Focus Areas
How many SQL Recommendation are affecting a Computer a SQL Instance or a Database?
How many times did each unique SQL Recommendation trigger?
SQL Assesments passed by Server
SQL Recommendation by Computer
SQL Recommendation by Database
SQL Recommendation by Instance
You can use these and you can save your own searches which show the data in a way that is valuable to you.
Overall I am impressed with this tool and can see how it can be beneficial for a DBA as well as for System Administrators. I was amazed how easy it was to set up and how quickly I was able to start manipulating the data once it had been uploaded.