SqlServer 2016: Developer Edition is free

… as is Management Studio, which is now completely separate from the Engine ISO.

 

SSMS is here, and SqlServer is here.

There pair are respectively about 1 and 3GB.

This is a working command line to install SQL (this one assumes that the ISO is mounted as F:

PS F:\> .\setup.exe /ACTION="Install" /FEATURES=SQLEngine /UPDATEENABLED=0 /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT
AUTHORITY\NETWORK SERVICE" /SQLSYSADMINACCOUNTS="swampy@marsh.com" /Q /BROWSERSVCSTARTUPTYPE="Disabled" /SECUR
ITYMODE="SQL" /SAPWD="TheSaPassword" /SQLSVCSTARTUPTYPE="Automatic" /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS

PowerShell DSC: “A configuration is pending”

This was driving me absolutely nuts.

A helpful post pointed me at [C:\Windows\System32\Configuration], and get rid of the pending.mof file. But that didn’t help.

Then I found Test-DscConfiguration

… which then led me to this:

, and finally the answer – Remove-DscConfigurationDocument -Stage Pending

Now I had that, I could finally do the thing I wanted (content really doesn’t matter):


Now I can get on with the original goal.

PowerShell DSC: deleting a Windows Service

This is simple and it works – thanks DSC. Code is here.

I want to declaratively delete a Windows Service… I tell it what I want, it figures out how. Part of that is not involving me in interactive decisions about what to do if it is already Absent, to use the Service provider keyword. I have said the required state is Absent, just make it so.

I decided that the Fax service was one I would never need, so that is my test case. As ever with actions against Windows Services, make sure the Services dialog is closed when you perform the action, else it will just hang.

The code to achieve that is immediately below the service dialog.

That is then compiled into MOF format (the . .\ServiceManager.ps1 screenshot), and then we execute (Start-DscConfiguration…). Net result is no Fax Service in Services after this.


Chef does not currently have the ability to delete a Windows Service, as far as I can see here.

Wix: FireGiant tutorial

This is just a set of pointers to the most useful parts (for me) of the Wix tutorial. I’m calling out the FireGiant domain as these guys are the owners of Wix (It’s late – I know what I mean), and therefore to distinguish from other tutorials.

Your starting point is here.


Putting it to Use references a basic set of files (SampleFirst archive link).


First Steps goes into UI and gives a more complex structure using the same source files (SampleWixUI link). These are dummy files – don’t expect them to work.

Here is a DotNetRocks show with Rob Mensching, who I understand is the leader of the WiX project under Microsoft. (may have that wrong)

Azure RM: get an Image from an ImageFamily and use it

$family = "SQL Server 2014 SP1 Enterprise on Windows Server 2012 R2"
$image=Get-AzureVMImage | where { $_.ImageFamily -eq $family } | sort PublishedDate -Descending | select -ExpandProperty ImageName -First 1
$image

 

Returns for example:

fb83b3509582419d99629ce476bcb5c8__SqlServer-2014-Sp1-12.0.4449.0-Enterprise-ENU-WS2012R2-CY16-SU03

And now say I want to a VM that is running SQL Server 2016. This is the (or at least a) question to ask (after some initial selects to test the water):

$image2016 = Get-AzureVMImage | where { $_.ImageFamily -eq “SQL Server 2016 RTM Enterprise on Windows Server 2012 R2”}

And that returns me:

fb83b3509582419d99629ce476bcb5c8__SqlServer-2016-RTM-RServices-13.0.1601.5-Enterprise-ENU-WS2012R2-CY16-SU0310020

Below is the history give or take I used to get there (btw, only just discovered that e.g. [ Set-Clipboard] does not truncate output whereas [clip]does. For example:

Get-History | select -Unique | Set-Clipboard

azure login
cd "E:\gitrepos\AllThingsAzure\AzureCLI"
ls
.\AzureCliScripts.ps1
$global:password = "whatsthis"
cls
clear-host
$family = "SQL Server 2014 SP1 Enterprise on Windows Server 2012 R2"
$image=Get-AzureVMImage | where { $_.ImageFamily -eq $family } | sort PublishedDate -Descending | select -ExpandProperty ImageName -First 1
Get-AzureVMImage
Get-AzureVMImage | where $ $_.label -Like "*2016*"
Get-AzureVMImage | where { $_.label -Like "*2016*"}
Get-AzureVMImage | where { $_.label -Like "*sql*2016*"}
$image2016 = Get-AzureVMImage | where { $_.label -Like "*sql*2016*"}
$image2016.Count
$image2016[0]
$image2016[1]
$image2016[2]
$image2016[3]
$image2016[4]
$image2016[5]
$image2016[6]
$image2016 = Get-AzureVMImage | where { $_.label -Like "*sql*2016*"} | select {$_.Label}
$image2016
function prompt{}
$image2016 = Get-AzureVMImage | where { $_.label -Like "*sql*2016*"} | select {$_.Label, $_.ImageName}
$image2016 | Format-Table
$image2016 | Format-List
$image2016 = Get-AzureVMImage | where { $_.label -Like "*sql*2016*Enterprise*R2"} | select {$_.Label, $_.ImageName}
$image2016 = Get-AzureVMImage | where { $_.label -Like "*sql*2016*Enterprise*R2"}
$image
$image2016 = Get-AzureVMImage | where { $_.ImageFamily -Like "SQL Server 2016 RTM Enterprise on Windows Server 2012 R2"}
$image2016 = Get-AzureVMImage | where { $_.ImageFamily -eq "SQL Server 2016 RTM Enterprise on Windows Server 2012 R2"}
Get-History
Get-History | clip
Get-Help clip
alias clip
Get-History | Set-Clipboard
$x = Get-History
$x
$x = (Get-History | Set-Clipboard)
$x = Get-Clipboard
alias get-clipboard
alias get-clipboard gclip
New-Alias -Name gclip -Value Get-Clipboard
gclip
gclip | unique
$x = gclip | unique 
$x | Set-Clipboard
Get-History | select -Unique | Set-Clipboard

Azure (Classic) Images

“fb83b3509582419d99629ce476bcb5c8__SQL-Server-2014-SP1-12.0.4100.1-Std-ENU-Win2012R2-cy15su05” # Windows 2012 R2, sqlsvr2014 sp1

(8GB / 4-core)

Usage: see here.

November 2016: but these things move on, and the image you choose today may not be there tomorrow. I wrote this to allow me to a) pass in a wildcard when selecting an image, b) get back in the clipboard an arbitrary image name… which realistically means I have to pass in a maximum images of 1. Look at the comments and examples, and you will see what I mean.


Azure CLI: Creating a VM

Although I had vowed not to create a VM again using the Classic deployment model, I was finding it hard to work with Chef in the ARM model. And as it seems that the Azure CLI operates by default against the Classic model… I’m using Azure CLI.


Get the Azure CLI

npm install azure-cli -g

Switch to ASM config mode

azure config mode asm

Download and apply your account settings file

Running this takes you to a browser which prompts you to save the .settings file to your local drive (e.g. c:\temp\ in the example here)

azure account download

Once downloaded, run this:

azure account import “C:\temp\My.publishsettings”

Login

If not authenticated currently, run this, which takes you to another browser:

azure login

List your VMs

azure vm list

List the available VM images from which you can build a VM

azure vm image list

From that list, right now, I am using this VHD, and an example command line of that follows:

a699494373c04fc0bc8f2bb1389d6106__Windows-Server-2012-R2-20160812-en.us-127GB.vhd

Create a VM

azure vm create ClassicVM01 a699494373c04fc0bc8f2bb1389d6106__Windows-Server-2012-R2-20160812-en.us-127GB.vhd username password --location "East US"

Note ref the US location that I am in the UK. But variations on “West Europe” do not seem to work in  CLI. Now I failed to specify [-r] in my command line, i.e. enable RDP. So I had to go in to the Azure portal after that and set RDP on port 3389. Also note that the default VM size is “small” if you do not specify a value. Once that is all done, the RDP session includes this:

That is, it is indeed 2012 R2, and it’s Small.

Now you have at least 1 (Classic) VM, running this should return stuff:

azure vm list

Note that it does take a while to get to the ReadyRole status. Before that I got [RoleStateUnknown] followed by [Provisioning]  and [BusyRole]. I think it took about 30 minutes to get to the Ready state.

For fun, I saw there was the nano-server. Let’s see how that plays:

a699494373c04fc0bc8f2bb1389d6106__Windows-Server-2016-Nano-20160801-en.us-8GB.vhd 

azure vm create ClassicVM03 a699494373c04fc0bc8f2bb1389d6106__Windows-Server-2016-Nano-20160801-en.us-8GB.vhd administrator99 HornetsNest99! --location "East US" -r

And actually, that was a bit quicker to deploy. But it turns out it is built to be headless, so no you can’t rdp to it. See here.

Information and errors

The .azure location contains a lot about configuration detail and errors. Take a look:

Create many VMs

Ultimately, I would want to do this through PowerShell (sure, that of course has its own API for this, so you could say a bit perverse). Code is here.

Delete many VMs

And then we want to tidy up. The help extract shows you all you need for the arguments. Code.

And the set created earlier has gone:

And to complete tidy up, update the script (of course you would make this a function after playing) to remove those others:


Hit Subscription Core Limits

Later… and then I tried to get fancy, and create LOTS of VMs. No, I don’t…

BadRequest : The subscription policy limit for resource type ‘cores count’ was exceeded. The limit for resource type ‘cores count’ is 20 per subscription, the current count is 20, and the requested increment is 4.