What Gets Measure Gets Done: The Dangers of Checks and Balances

Hello! Let’s start with a story: I’m extending into my house’s loft space to create an extra few rooms, and make better use of a space that until recently stored Christmas decorations. This is a major piece of work; we’re changing the skyline of our house, and in the UK this kind of thing requires Planning Permission from the Local Authority. Planning Permissions requires submission of forms and drawings to enusre that the building is safe.

Build and Publish VS Code Themes From Azure DevOps

Hello! Let’s not mince words: automating builds/releases/deployments makes development an awful lot simpler. All of the VS Code theme repos have the same yaml pipeline to publish them to the Visual Studio Gallery. Once you have gone through the process of setting up a publisher in the Visual Studio Gallery it’s straightforward to automate publishing packages. In the code below you’ll see that there is a variable called $(POSTMAN) (no, idk why I called it this either), which is a PAT created specficially for publishing.

New Visual Studio Code Theme, and It's So Fetch

Hello! I’m on holiday right now, and what better way to relax than to create a new theme for VS Code! Clearly I need to add screenshots and update the readme to make it pertinent. I genuinely enjoy making new themes; something different to focus on that doesn’t mean much as I’ll be the only one to use these themes, but fun nonetheless. This one is a light theme with mainly green as the accent.

Display Custom Build Number in Azure DevOps Environments

Hello! I’ve been trying to wrap my head around how to get the Build Number to display in the environments status information in Azure DevOps. Environments use the Build Number to show which pipeline was last deployed. And so there is a distinction between a build number, which can include info like app name and branch name, and the semver number, which can be included with the build number. However the semver can be used when publishing artefacts from a pipeline, which doesn’t need all the info included in the build number: PowerShellGallery has strict versioning rules, and I don’t know about Pypi, but the convention is to use semantic versioning.

Managing ADF Trigger Runtime State From Source Control

Hello! In a previous post, I wrote about how the runtime state of ADF triggers is ignored even if it is set in the ARM template. This means we have a couple of options when manging runtime state acorss different deployments of the same ADF. The first one is to use the UI of the ADF to set the trigger state. Even as someone who is quite vociferous against using UI’s to manage configuration, I think this is far from the worst idea.

ARM Template for ADF Trigger Runtime State is Always Ignored

Hello! So, continuing the saga of deploying the ARM templates of ADFs that are generated by the npm package made avilable by Microsoft, I came across something that I was not aware of, and honestly I really should have been. Although runtimestate can be included in an ARM Template to deploy a Trigger, the runtimestate on the deployed ADF will not be altered to match what is in the ARM template.

Check for Failed ADF Linked Template Deployments in Azure DevOps

Hello! This series on ADF deployment management seems to be never ending and always evolving. In a previous post I shared some code that could be added to an Azure DevOps pipelines that would check for failed deployment messages. This was necessary as typically we’d not get a useful error back, and in some environments we did not have access to the Resource Group to check for deployment errors. Now that we have moved to making deployments using linked templates for ADF this code is no longer helpful, as it returns the same top error: each linked template has its own entry in the Deployment registry.

PR Raised To Update PrePostDeploymentScript.Ver2.ps1

Hello! I’ve spent the day parallelising the entire PrePostDeploymentScript.Ver2.ps1, as well as merely the deleting deployments. I’ve also fixed a casing issue where the parameters file was not being found on the ubuntu-latest Azure DevOps Agent. This is the link for the Pull Request, and this is the link for the copy of the file in my forked repo. I have no idea if the changes will be accepted or not, but hopefully it will as it’s a good script it just needs a few tweaks to it.

Parallelising Delete Linked Templates

Hello! The saga continues with deleting deployments for linked templates… come to find out that deleting linked templates serially can be extremely slow. So I updated my script to parallelise the deletion of the templates. Fortunately ForEach-Object has a parallel feature and can throttle the number of threads. It’s quite neat that each deplyoyment has all the info in it so the script block is able to reference the object for all it needs.

More Woes with ADF Deployments

Hello! In a previous post, I wrote about some of the issues with the PowerShell script used to delete deployments. This is packaged as part of the npm package provided by Microsoft. We recently hit an issue with the number of resources in the ARM template generated by this npm package. Helpfully the package also creates the ARM templates as linkedtemplates. This was all fine, however the delete deployments section of this ill-fated PowerSHell script does not account for multiple deployments, which is exactly what happens when you use linked templates.

The ADF PrePostDeploymentScript Has Some Issues

Hello! As part of the npm package provided by Microsoft, there is a PowerShell script called PrePostDeployment script. This script very helpfully sorts out the ADF prior to the ARM tempalte being deployed. This script however is not without its problems. Firstly when it attempts to stop triggers it does so by getting a list of all those that are not stopped. This line used to not include the -and $_.

Check for Failed ADF Deployments in Azure DevOps

Hello! We’ve been using the npm package to build the ADF source code. The npm package, which is officially from Microsoft, will generate an ARM tempalte, which can then be deployed. As we’re using Azure DevOps it makes sense to use the AzureResourceManagerTemplateDeployment@3 task to deploy the ARM Template. All very nice, but we have found that owing to the sheer number of objects being deployed we get a very unhelpful error message back when it fails:

SQL Bits 2023 Round-Up

Hello! SQLBits 2023 has been and gone, and what a lot of fun it was. I only attended two days there, however I learned a lot, and got to catchup with some people who I had not seen since 2019, when SQLBits was in Manchester. I got to Newport on the Tuesday evening, and spent Wednesday working and finishing off my presentation. I came prepared with snacks and Pepsi as I saw that the hotel/ICC was some way away from a shop.

Access Service Connection Values in Azure DevOps With This One Neat Trick

Hello! A popular way of connecting to Azure Subscriptions in Azure Devops is to make use of Service Connections. These allow steps to execute Azure Devops tasks remotely. However, there are a few issues with them; the most notable being that you cannot store the values in somewhere like a KeyVault and update them when they expire. So once they’re entered, the cannot be retrieved. Or can they? If you use the task azurecli@2 there is an option to access SPN details.

SQL Bits 2023 Preview

Hello! It’s that time of year again: SQLBits is here! I love SQLBits. One of my favourite conferences and I always look forward to going back. I say “back” however the conference tends to move around the UK, and this year is the first time it will be in Newport, Wales. But to further complicate thsi paragraph, this is the second time I’ve been to Newport, having seen Newport County vs Sutton United way back in 2003.

Free PowerPoint Infinite Process Templates

Hello! As part of my SQLBits presentation I wanted to have the DevOps Infinite loop cycle as one of the slides. There’s a few examples online, but I wanted to make my own as I didn’t want to use somebody elses work, and also make the colours match my slide theme. I thought initially that this would be quite easy, but come to find out that is not the case. Not in the slightest.

Pin Azure DevOps Tasks To Minor Versions

Hello! Got caught out with a nasty bug yesterday; an Azure DevOps task written by Microsoft that transforms files was failing to update json files, yet it was not reporting an error. This meant that pipelines using this task were failing to update but not failing to deploy. It looked like it had been failing for a few days before we saw it, and the bug was confirmed on GitHub.

New VSCode Themes

Hello! Sometime back in 2020 I created my own theme called Parisienne and pushed it to the VSCode marketplace. I have no idea if it is used by anyone else other than me, but I’m not too bothered because frankly the best theme available for VSCode is Night Owl. Having said that I wanted to play around with making more themes because it’s not very stressful to do, and in fact I find it quite fun to make themes.

Are Built-In Azure DevOps Tasks Evil - The Good(ish)

Hello! In the previous post on Azure DevOps tasks I spoke about my issues with Azure DevOps Tasks and promised to write about some of the good things about them. And one of those positives is the fact that someone has figured out how to do X and feels that they can contribute to the community by publishing it to the marketplace. If inputs are required (and the most likely are) then they can use things like drop-downs or define the data types to help make it easier to setup.

PSConfEU 2022 Round-Up

Hello! I’m sat here in a hotel just off the Stadt park in what is quite possibly the most beautiful city in the world: Vienna! And I’m here because I was fortunate enough to be invited to speak at PSConfEU 2022. It was the first time I have ever attended this fantastic conference, and it was great finally meet some people who’s blog posts I had read over the years.

Use Source Control and PowerShell To Manage Access To Azure Data lake Gen 2 - The Links

Hello! Links from my presentation at PSConfEU 2022 on “Use Source Control and PowerShell To Manage Access To Azure Data lake Gen 2” adls2.folder.access.tools ms docs how-permissions-are-evaluated credit ms docs Role-based access control (Azure RBAC) Access Control Lists (ACL) Hitchhikers Guide to the Datalake

Are Built-In Azure DevOps Tasks Evil?

Hello! This is the first in what will probably be an on-going topic around how to “Do The DevOps” right. And it’s going to be on-going because there is no ‘right way’ of doing things. I’m going to focus initially on what people most likely first think about when they hear the phrase DevOps, and that is pipelines. More specifically, I want to talk about tasks that are available out of the box or via the marketplace.

ParameterSets Script

Hello! Someone earlier asked me about ParameterSets, and so I wrote this script to help demonstrate how they work. In short, they help give us options to call a set of parameters. We can also use Mandatory to ensure that all the parameters within that ParameterSets are set. This is optional, but is useful as the manadatory property is only active when you are using the ParameterSets. You can also use PSBoundParameters to dewtermine which PartameterSet has been used and execute one path or another.

Passing Variables between Triggered Azure DevOps Pipelines

Hello! I have a pipeline that builds and publishes a wheel to Azure Artifacts in Azure DevOps, but I also wanted to take the wheel and publish it to some Azure Databricks workspaces. I could have made this one pipeline, but as there could be potentially a large number of workspaces to publish to I thought it best to write the terrafrom that will publish to the DBFS in a separate repo and make the publishing to workspaces a separate pipeline.

Limitations with Terraform and ADF Delimited texts

Hello! For some time now Microsoft have been investing pretty heavily into terraform, and I don’t mean financially; the AzureRM provider is very closely aligned with what you can do using ARM templates and other methods to create Azure resources. There will however always be an inevitable lag between what you can do with terraform. This is somewhat of an unpopular opinion to hold, because those that don’t agree with it have perhaps nailed their colours to the mast on terraform being the best tool ever.

The Many Ways to Authenticate to Azure from Python In One Simple Class

Hello! I’ve been working on a script to login to Azure to interract with some resources that have been deployed by terraform. I want this script to be able to run locally on my machine and as well as part of a yaml pipeline. Initially I thought this was going to be quite tricky, in that I’d need to determine when to run the ClientSecretCredential or when to run one of the other authentication classes, like the AzureCliCredential or even the UsernamePasswordCredential.

How To Download Latest PyPi Azure Artifact

Hello! Since the time that Microsoft declared that they heart open source, and also the move the Azure, there has been a massive increase in the what and how to do something. For example, off the top of my head, Azure Functions support PowerShell, C#, Python, and many more languages. This increase in choice is great; far better to have many resources/services that are specifically designed to do one thing really well rather than add a feature onto an already existing product.

Run Terraform Locally and In Azure DevOps Pipeline

Hello! I’ve been working with Terraform on and off for about 3 years now. In fact I first heard about terraform way back in 2016, whilst I was busy cursing ARM templates and their verboseness. It was around this time that Microsoft and Terraform announced their working partnership (this was also around the time that Microsoft announced that they hearted Open Source). I’m not going to go into ARM template bashing though; all Infrastructure as Code solutions have their advantages and drawbacks.

Create And Publish To Static Website With These Two Simple Tricks

Hello! I’ve been looking at publishing some html files to an Azure Static Web Site to be shared internally across the teams. A static website is great for this because I can integrate AAD into it and give out access as and when people need to have access to the site. Another bonus is that using jinja2 the html files can be generated in an Azure DevOps pipeline and then the results published to the website.

PowerShell Object Property Strangeness Between 5.1 and 7.1

Hello! Got caught out with a bug that had me confused for quite some time earlier this week; it seemed that on some peoples machines we’d get an error about a file not existing, when we knew the file had to exist. I say we knew it existed because we ran a ``Get-ChildItem``` on a folder to get the paths, and then loop through each file and process them. A pithy version of the script is below:

Building Terraform Stuff on Darrwin/ARM Macbooks

Building Terraform Stuff I went and bought myself one of those new Macbook Air laptops that have the Darwin/ARM64 processros in them. Not done too much devving on it other than a bit of terraform. And recently I was doing some farily basic stuff from the book “Terraform Up And Running” when I came across an error which was both confusing and annoying for hte same reason: I had changed seemingly nothing when it occured:

Permissions Required For Accessing Databricks Workspace with Data Factory Managed Identity

Hello! Let’s not mince words; managing secrets via Key Vault is useful but can be painful to refresh, espeicially if you’re unable to automate the process of refreshing and updating secret values. And in deference to this I like to use Managed Identities where I can. Managed Identities are an entry in the Enterprise Application Registry in Azure Active Directory that is linked to a trusted Azure Resource. This resource could be a VM, or in the case that I am writing about today, a Data Factory.

Stop Making Personal Access Tokens for Azure DevOps Pipelines

Hello! I’ve been writing some terraform to create VMs in Azure and then install the Azure DevOps Build Agent service on it. Actually, I’ve not been writing it, I’ve taken this useful repo and made some modifications. Anyway, I want to run the terraform in an Azure Pipeline itself, written in yaml. However, to register the agent to Azure DevOps you need to specify a PAT, and storing it in the script would be bad, obviously.

Be Careful when Creating Service Connections in Azure DevOps

Hello! I got caught out by something this week: looks like updating a password in an Azure DevOps service connection is not quite as straightforward as I had expected. As a project administrator I went to update the secret of a service connection as the service principals key was close to expiring. I entered the new value, hit ‘verify’, and once that was confirmed I went to hit save. However, I got a strange error message:

Managing Stored Access Policies

Hello! There are a couple of ways to secure access to Azure Storage, and one of the mechanisms I’ve used in an app recently was SAS or shared access signatures. One of the reasons I chose this over Storage Access Keys is because their granularity in terms of what they can control access to and how much they can do with that access (ie read/write/delete). This is all very useful, however they are temporary and have no way of extending the expiry date.

Get Size of Folder on Azure Gen 2 Data Lake

Hello! Here is a quick script to check the total folder size of a specific folder in an Azure Gen 2 Data Lake. This can take some time to run. The maxsize is set to 100000 items, however chances are this could be increased to make the process faster. I’ve not really had the opportunity to find the optimal value, and anyway it seems to be working OK on my machine (Intel i7 7th Gen, 32GB of RAM) but depending on your machine your mileage may vary.

On Software Testing, By A Failed Software Tester

Hello! It is my birthday, and so I’ve decided to celebrate it by treating myself and talk about software testing. I’ve spoken about testing before, but I’ve decided to revisit the subject as I give it a lot of thought and effort in my role as a consultant brought in to talk about “doing the DevOps”. Testing is definitely one of those things that people want to do more of. And yet conversely it is one of the things that people do least of, or put the least amount of effort into doing.

Using Terraform Outputs Connect To Azure Databricks Workspace via Azure DevOps

Hello! I have been using the Terraform Provider written by databrickslabs to manage resources on an Azure Databricks Workspace. It is very complete and offers most of the functionality needed. However, occasionally it is necessary to execute a job to do some maintenance on the workspace. And the provider can only deploy jobs, not run them. This is fine, as terraform is concerned with deploy infrastructure as opposed to executing jobs.

Managing ACLs on Gen 2 Data Lake

Hello! Authorising access to data can become a very difficult task to manage if not enough thought is given to how to manage authorization at the beginning of using data, and this is especially true of something like Azure Data Lake, because it’s going to store a large amount of data across a lot of directories. The access control model to data presents a number of options, the most straightforward to use is RBAC.

Using Az in an Azure DevOps Powershell Task

Hello! Last month I posted how to run the Azure PowerShell Task on a Self-Hosted Build Agent but because using self-hosted agents is something I’m not that interested in anymore here is an alternative to use the Hosted Build Agents. There’s two steps to this: first you need to use the AzureCLI@2 task. This has an option to include the spn details, or in other words the login details of the Azure Resource Manager subscription you have specified in the azureSubscription parameter.

Azure DevOps and Running The Azure PowerShell Task on a Self-Hosted Build Agent

Hello! And Happy New Year! I’m going to preface this post with the fact that I am not sure if this is the intended way to get different versions of the Azure Powershell module running on a self-hosted nuild agent. It just feels wrong and contradicts the best practice of having modules in the $Env:PSModulePath. I’m sure there’s a very good reason that the hosted build agents have been set up this way, I’m just not privy to it.

Install Terraform 0.14 beta2 on AzDo Ubuntu Hosted Agent

Hello! The terraform 0.14 release includes a long awaited feature: the ability to mark input and output variables as sensitive. This is big news, and I want to try doing this ASAP. But as it is in beta I will have to install it on the ubuntu hosted agents, which come to find out is very straightforward: - task: CmdLine@2 inputs: script: | sudo apt-get install wget unzip -y sudo wget https://releases.

November 2020 Musings

Hello! I’m very busy! So here’s what I’ve been up to. Spiny Some years ago I wrote a VSTS Task that would update the version number of a module prior to it being published to PowerShell Gallery (and more on this site later.) I spent a lot of time on it and wrote a bunch of very awkward tests around the PowerShell, but had the test pipeline running on different agents to make sure the plugin would work on those agents.

Authenticating with Azure Datbricks API as a Service Principal

Hello! Back when I first started working with Azure Databricks I used bearertokens to authenticate both on my dev machine and in Azure DevOps pipelines. It wasn’t especially challenging to add a masked variable containing a bearertoken that never expired to a pipeline and then pass that in. However over the past 12 months or so things have matured to the point where we really should use premium workspaces, because on the standard tier workspaces everyone is an admin and that’s the only permissions level.

Update Azure DevOps YAML Build Name With Pipeline Variables

Hello! Argh! Yaml! OK, let’s get something totally clear: I really don’t care for YAML pipelines. I get that the idea behind them is so that the pipelines to take the code from source and deploy up to produciton is stored with the source control. And as someone who places a great deal of importance in source-controlling as much as possible, this dislike of yaml-based pipelines might seem something of a surprise.

Check Environment Variables Exist in Azure DevOps PowerShell Tasks

Hello! I recently published a new PowerShell module to help check that environment variables have been created for variables in an Azure DevOps Variable Group. And I called it Dreepy. How Does It Work? Dreepy is a PowerShell module to verify Environment variables exist for all variables in a variable group that are attached to running build. It can be run outside of a build pipeline, providing you specify the organisation, project, and build variables.

PoshSSDTBuildDeploy Updated - Now With Proper VS2019 Support

Hello! I’ve finally gotten around to adding proper support for VS2019-based projects. Back in May I added a variable to Invoke-MSBuildSSDT so that the path of msbuild could be passed in directly, instead of trying to find MSBuild. This was because at the point of me writing the function VS 2017 was the latest version. So this should work for all subsequent builds going forward. And today I finally added ‘16’ as an option on the MSBuildVersionNumber, which makes things a little easier, and also added the function Install-VsBuildTools2019 that can be used to download and install MSBuildTools version 16, which is compatable with VS 2019.

Getting Scripts from Release Pipelines in Azure DevOps

Hello! Artefacts play a very important part of build and release pipelines. For builds they contribute to the “build once, deploy many” process as an artefact is published at the end of the build and then consumed by a release at each satage/environment deployment. So in the context of an SSDT project, you compile the sqlproj and grab the resulting dacpac as a build artefact. Then for each release to dev/preprod/prod etc, you use the same dacpac that generates the deployment.

Why is My PyTest Passing When It Should Be Failing?

Hello! Quick one today: I was showing someone some pytests I had written when I noticed that my test was wrong: that is the expected and actual did not match. This seemed odd to me as I had written the tess some weeks ago and it ran successfully both on the build and also locally. This is where my test was asserting actual as expected… expected_test_add_all_failed_test_cases == actual_test_add_all_failed_test_cases … except of course, it wasn’t!

PyPI Service Connections Uploads in Azure DevOps

Hello! I’ve been using Azure DevOps to build a library I have sourced in GitHub. I really should learn about GitHub Actions, but my main focus has been 1) learning Python and 2) how to build a pipeline to publish to PyPi, and so the quickest way for me to learn the second point was to stick to the tool I know the best. Anyway, in Azure DevOps you need to create a service conection to an external source in able to communicate with it in a pipeline.

Mocking Exceptions in Pester

Hello! So if you know me you know I like testing, and that when it comes to testing in PowerShell the de-facto testing framework to use is Pester. And it’s claim that it is ubiquitous can be backed-up by the fact that it is now packaged in PowerShell. One of the great things about Pester is that you can mock a function; that is, if you call a function within another function you can mock the internal funciton.

Azure Data Lake Mounting Error

Hello!

Export External Tables from Azure Databricks Workspace

Hello! I recently needed to export the “CREATE” statements for any hive tables on an Azure Databricks Instance whose paths were set externally to an Azure Datalake. Other htan changing the output_folder and hte name(s) of your mount points below this script should run on any Workspace. There is a little work to be done with regards to the order of objects saved to the csv files, as views require the tables to be created first before they can run, but seeing as I only had to run this once I manually re-ran the small number of views that failed.

PoshSSDTBuildDeploy Updated - Now Less Clever

Hello! Updated PoshSSDTBuildDeploy to take in a path of MSBuild instead of the function Invoke-MsBuildSSDT trying to be clever and figure out which version to use. Got to the point where I was making a rod for my own back with the initial choice; trying to be all things to all people has it’s drawbacks. Oh! And the version is 3.1.643.

Export-DatabricksFolder - Now With Threading

Hello! Last month I posted about the challenges of duration of releases getting tedious and what you can do about that. In a very similar theme, running a process like a build can also get tedious, and so you have to go looking for making improvements to a process before it really does start impeding people developing at a reasonable pace. I’m not going to repeat what I mentioned about three weeks ago, so go and have a read if you like, because what motivated me to make this change was getting bored of waiting for something to complete.

Pokémon ASCII Art Maker

Hello! If you know me you will know that I like Pokémon a lot; just ask my daughter Eevee (kidding.) And you will also know that I enjoy doing silly things with computers. And last year I got into adding ASCII art to PowerShell Modules what I’ve written, and one piece of art was adding an ASCII forme of Aegislash to PoshSSDTBuildDeploy. I sourced the image from this amazing python module.

Get All Users of Nested AAD Groups

Hello! I’ve spent the morning retrieving all of the users from a bunch of AAD Groups. The trouble was that groups were also members of the groups, so it was necessary to get the member of those groups as well. Actually, this was no trouble at all; it was a case of looping through and writing the users to a csv and looping through the sub-groups to get the users and groups etc etc.

Azure DevOps Sample Repo and Pipelines for Artifacts

Hello! Last week I published a post about publishing a PowerShell module to the artifacts section of Azure DevOps. It was basically an amendment to a script that someone else had written. But what I really want to do is write a bit more of an in-depth post on the pipelines I have used to publish a PowerShell module as an artifact. I have created a repo on GitHub so that people can follow from home if they really want to or take scripts and stick them in their own repos.

Publish PowerShell Module to AzDo Artifacts

Hello! I’ve been writing a PowerShell module that I want other developers to be able to use, so I decided to use the artifacts part of Azure DevOps as I’ve set up a build to run some tests and run PsScriptAnalyzer prior to publishing the module at the end of the build as an artifact. I figured that using the Artifacts to store my artifacts was sensible instead of leaving them hanging around old builds.

Az-Connect on a Windows Server

Hello! I recently had to create a VM in Azure that was in the same region as a Data Lake I was working on as the network delay in running some Az PowerShell cmdlets from my laptop here in the UK was causing some issues. Logging in should be simple enough - if ($null -eq (Get-AzContext)){ Write-Host "No Azure Context... logging in. Check for login window behind this one, especially if you are using VSCode!

Import-DatabricksFolder - Now With Threading

Hello! One of the big challenges of running a release pipeline is the duration of a release and when is a good time for doing it. OK, that’s two challenges really, but it’s the first point about duration that I want to talk about. For example, in the world of SSDT and publishing changes it can take a very long time indeed if any of the changes result in a diff script that creates a new table and moves the data over, and then dropping the old table and renaming the new table.

What's Going On In Deploy-AzureResourceGroup? - Part Zero

Hello! When creating Azure Resource Group projects, a PowerShell file is added to the project. The intended use of this file is to be used to deploy the Resource Group both locally and in a pipeline. This file is called Deploy-AzureResourceGroup. But at over 200 lines long, there seems to be quite a lot going on. And what exactly is going on? In short, it’s a helpful file, but tries to be all things to all people, with very little documentation.

Databricks InferSchema Performance Revisted Hello Again

Hello! I wrote last month about inferschema performance when using Databricks to read a csv into a dataframe. Long story short: it’s better to declare the schema of the dataframe prior to reading the csv as if you let Databricks to determine the schema then it has to pass the entire contents of the csv once to verify the schema. All very good, but this can become problematic when you are reading a particularly wide csv, especially if you do not need all of the columns, only a subset of them.

Unexpected Changes to Image Locations with Hugo

Hello! I’ve been using Hugo asa static website builder for over 18 months now and I’m really happy with it: I have backups of the website thank to using git, I cna create posts offline, it’s free, GitLab has great hosting capabilities, I can make changes to the look of the site and review them on my desktop prior to publishing them, and despite my limited knowledge of Go and general skills required to build a website I’ve been able to maintain the look and feel with any changes that occur as Hugo continues to be developed upon.

Pin The Azure Portal Menu

Hello! Recently the behaviour of the portal menu has changed from being docked to being flyout by default. Given that I have lovely wide screens to work on I’m rarely bothered by losing a bit of space to a docked menu, and so I imagine that a lot of people are in the same position. Fortunately it is possible to set your preferred default mode for the menu by going to the setting in the portal and from the option “Choose your default mode for the portal menu” you can choose “docked”.

End Of year Review 2019

Hello! I’ve often thought of writing an end of year review but to be honest I cannot be bothered to write it, and much less I can imagine people would want to read it. If you want introspection go read a blog on medium.com. Suffice to say it’s been a good year and I look forward to the next one. So in the words of Krusty the Clown, “have a Merry Christmas, a Happy Chanukah, a Krazy Kwanzaa, a Tip Top Tet, and a solemn, eventful Ramadan.

Databricks InferSchema Performance

Hello! One of the really handy features when reading a csv in Databricks is being able to infer a schema from the contents of the file; that way you do not need to declare a schema up front, so if the contents of the file changes you still inherit what is in there. There is however an inevitable tradeoff, because the file has to be read for the datatypes of each column to be ascertained.

Unexpected Modifications to Notebooks Azure Databricks

Hello! I’ve been working a lot of Azure Databricks, and one of the things I’m really impressed by is how straightforward it has been to use source control to store not just notebooks, but other artifacts such as jobs and clusters. This is because the API is well documented, and it’s a case of pushing either a file (in the case of notebooks) or a bit of json config (in the case of everything else).

Migrating from AzureRM to Az Module In Azure DevOps Pipelines

Hello! I don’t recall when it was, but recently Microsoft released a new version of the the Azure PowerShell module. This is to work with PowerShell Core, and htey chose to call it Az, which was already a name used by Microsoft as short-hand for the az cli. Irrespective of this muddying the waters, AzureRM is no longer being worked on. So new scripts should be developed using Az instead of AzureRM.

Set Azure DataBricks BearerToken to Never Expire

Hello! Found another trick with setting the expiry date to access tokens in Azure Databricks to never expire, and I don’t recall seeing it in the documentation but it is there as a tool-tip, and who ever reads those! In Azure Databricks these Personal Access Tokens allow users to access the Rest API; however a token must first be created manually after a Workspace has been created. Note that tokens can be created and revoked via the API, however the fact is that a token must first be created manually prior to using the API.

Installing Pester on Linux Build Agents!

Hello! The past few months I’ve been improving the pipeline I use to build and publish a Visual Studio Extension that basically updates the module version number and publishes it to the PowerShell gallery. You can see the status of the pipeline here! The build and release pipeline are publicly available to view here. I use a single pipeline because once I’ve run the tests there’s no need to fret about whether or not I hold off from pushing a later version.

Help! My Build Badge Showing Never Built!

Hello! One of my Visual Studio plugins uses the build badge on it’s overview page in the Marketplace. I quite like using these status badges because it shows there’s some form of automation in place to deliver the extension. However the other day I was looking at the page in the marketplace and was surprised to see that the status of the build was “never built.” So looking into it it is because the build retention policy is set to a number of days, and after that everything is deleted.

Printing Out Secret in Azure Databricks

Hello! back in August I posted a “how to expose secrets in your VSTS pipelines” post that promised to do exactly that. Now funnily enough I have been playing around with Azure Databricks lately and looking into using both Azure Keyvault and Databricks-backed Secrets and Scoping and remembering my post from last month, I wondered if it was possible. Come to find out it is! I added secret to called bob to a scope stored in KeyVault and another called phoebe to a DataBricks-backed scope and I was able to print them out easily enough.

Some Thoughts on LocalDB

Hello! Today I’m going to write a little bit about localdb, partly because I don’t think it is used enough and partly because the other day I was accused of having “a thing” about localdb. I’m not sure I have “a thing” about any particular piece of technology, apart from maybe anything made by Nintendo. But because I do use it a lot I thought it worth sharing some thoughts on this great tool.

Catching Non-Terminating Errors in PowerShell

Hello! I’m sure I’ve learnt this before somewhere, but now I’m writing it down to make sure I don’t forget: PowerShell will not catch non-terminating errors. This means you cannot use try/catch when attempting some retry logic. This caught me out today in some Azure RM PowerShell scripts I’ve been working on: I create an ADF, and because I’m going to add a shared self-hosted Integration Runtime to it I need to assign the ADF contributor permissions.

Publish Modules to PowerShell Gallery VSTS Task Is Updated

Hello! Another day, another random burst of activity on GitHub: I’ve updated the PowerShell Gallery Publisher Azure DevOps task that I published last year. I’ve added some tests and added those tests to the build. These tests are now run across 5 of the hosted agent types: Hosted 2019 Hosted 2017 Windows Containers Linux Ubuntu 1604 mac OS High Sierra I need to write some more tests that use Pester, and I intend to add a test that publishes a PowerShell Module as part of the builds.

Expose Secret Variables in VSTS With This One Simple Trick

Hello! This post will, if for nothing else, encourage teams to seriously consider using KeyVault for storing secrets that you want to use in a build (this walkthrough is a good guide.) Despite the fact that despite that they are encrypted, it is possible to get the value of a secret variable with one simple trick. This means it is very important who has permissions to edit your pipelines. So, how do we go about getting the value of a secret parameter?

Why Job Schedules Suck in ARM Templates Part 2

Last week I wrote about some of the fun I have had with job schedules and ARM Templates. In short ARM cannot re-deploy job schedules and so you have to delete them prior to re-deploying, or do a sort of merge statement. But because time is short deleting them seems to work well enough. Something like this: $automationAccountJobSchedules = Get-AzureRmAutomationScheduledRunbook -AutomationAccountName $AutomationAccountName -ResourceGroupName $resourceGroupName -ErrorAction SilentlyContinue Write-Host "Removing any job schedules off automation account $($AutomationAccountName): this is because of this bug https://feedback.

Selecting TFS Branch At Build Time

Hello! For the fast few years I have been using git as source control. But I’m sure like most people who work with Microsoft technologies, I’ve had to use TFVC in hte past and (if you’re a little bit unfortunate) the present. One of the drawbacks of TFS is that branches become long lasting objects and all too often someone somewhere has made the well-meaning but misjudged attempt at using lots of branches as like boundaries to prevent bugs accessing code.

Depending on Copy Completion in ARM Templates (and why Job Schedules Suck in ARM Templates)

Hello! In the world of Azure, resources move fast. And sometimes too fast for my liking: no sooner had I automated a runbook to start some VM’s that were Self-Hosted Integration Runtimes and invoke an ADF Pipeline did I discover that Microsoft announced in December that Azure Scheduler will retire on 30th of September 2019. Of course this sort of announcement was completely missed as I borrowed heavily from someone elses piece of work, because why reinvent the wheel.

Azure RM Powershell Snippet #1

Hello! Got a couple of longer posts that need a bit more research into them, but for now here is some random Azure RM PowerShell scripts I’ve been using: Been using Bamboo lately, and there’s unlike VSTS where you can select a task that you can configure to use a specific service principal and select the subscription, you have to log in yourself. However it’s important to logout of the principal when you have finished as sessions seem to persist across build steps in completely different builds, which is a bit of a pain.

Get Last Good Deployment To Specific Environment for TFS Release Revisited

Hello! Yesterday I posted a bit of PowerShell that uses the TFS API to return the last good deployment for a given release and release environment. It was all good until I realised that do part will continue to take longer and longer, and the $result get bigger and bigger as more releases are created. This is because I retrieve ALL the releases before querying them as the until ends when there are no more continuationtoken returned from the Invoke-WebRequest.

Get Last Good Deployment To Specific Environment for TFS Release

Hello! I can’t quite believe it, but I’m working with TFS-on prem again. Thankfully it is TFS 2017, so there’s an awful lot of familiarity as it is basically VSTS from not even two years ago (thanks to updates). And one of those things is the API! I’ve been needing to get the last good deployment to a specific environment, so that then I can kick off a deployment to another environment.

Stop SQLCLR Always Being Redeployed With SSDT

Hello! I’ve been working with a bunch of database projects that have a huge amount of interdependency amongst them, and pretty awkward it is to. In addition to this awkwardness is SQLCLR, which is not something I’ve encountered before in a SQL Database. There was something similar in a SSAS project I worked on, but that was long ago. At any rate, automating the deployments just got a little more complicated thanks to this dll which is transformed into a string of seemingly random bits in a SSDT Publish script.

Homer Simpson Birthday Musings

Hello! Happy Birthday Me. Real life has got in the way of blogging. I hope normal service will be resumed shortly.

PoshSSDTBuildDeploy 2.0.519: Now With Dynamic Deploy Options

Hello! And Happy Friday! Right, so PoshSSDTBuildDeploy got another minor tweak today. I’ve had a couple of requests in the past for Dac Deploy Options to be configured at deploy time. Generally these are loaded from the publish xml file and whatever the defaults are, or if the publish file has been set to something else. And so if you want things different for different environments you need to have separate publish files for different environments.

Salt Moves to 2.0

Hello! Earlier today i started work on deploying a SQL Agent Job, and so it made sense to eat my own dog food and have a go at deploying the SQL Agent job using saLt. It all worked great! Until I realised that there is a bug when deploying T-SQL-based job steps. You see when I wrote saLt it was primarily for SSIS Execution and T-SQL and PowerShell jos step types were thrown in for good measure.

Salt 2 - Now With SMO Nuget Download Built In

Hello! No time like the present: I have added two new functions to salt - Install-SaltNuget and Install-SaltSmo. These can be used to download Nuget and SMO respectively. So in your deploy pipeline for salt a script like the one below can be used to deploy a SQL Agent Job. It is worth noting that Install-SaltNuget is executed by Install-SaltSmo, but there is no reason why it could not be used externally.

AssistDeploy 2.0.0 Released

Hello! Quick post: amazingly I got a pull request on AssistDeploy. Slightly less amazingly because it was a work colleague but they fixed an issue where AssistDeploy relied on global params instead of environment params for replacing vars in the json file. Because there’s some extra functions and switches included I decided to bump up the semver to version 2, meaning that it’s most likely a breaking change. But at any rate, it’ll work with variables in VSTS far better now, and will be more flexible for different tools.

Moving Files And Updating Relative Paths To Images

Hello! At some point last year I moved from Wordpress to Hugo. It was a bit of convoluted process, taking the xml file from Wordpress and munging the data to create markdown files with the content intact, and largely it was a successful process. Some of the older posts on this here blog are a bit broken, but the older the post the less I cared. However, with over 430 posts here the posts folder in my source control was a bit messy as I had one file per post directly under the “post” directory.

ADF Activity Fails But Pipeline Run Succeeds - WTF

Hello! Since August last year I’ve had to pick up a number of new technologies, one of these being Azure Data Factory (Version 2 to be clear). And there’s a clear correlation between how much I enjoy using a technology compared to how long I’ve used it. And when it comes to ADF I’d say that I’ve hit a point of not enjoying it at all. Aside from the fact that it is practically impossible to test, the developer experience is a web page/drag and drop affair, and that the source control integration is rubbish (oh wow a blob of json to compare manually, what fun), the thing that’s really got me all angsty today is something totally unexpected, yet undoubtedly something someone somewhere would call “a feature.

PoshSSDTBuildDeploy: Fix for File Paths

Hello! I got a pr from Peter Schott wrt PoshSSDTBuildDeploy not quite working as expected. Turns out there was a few bugs with a couple of the functions. And I noticed another one myself so there’s been a relative flurry of activity over on the GitHub. What bothered me though was that I was sure that I had tests to cover these bugs. Turns out I do have tests to cover the issue of spaces in folder names when passing a Nuget path, it was to do with some weird concatenation.

Fix For Using Azure Active Directory to Connect to SQL

Hello! The other day someone was kind enough to get in touch with me to thank me for some post I wrote back last year. For the sake of brevity, there’s all sorts of nastiness in trying to authenticate using Azure Active Directory when connecting to an Azure SQL Instance/Managed Instance. And the function what I wrote aimed to automate uninstalling and installing the “correct” version of the adalsql.dll required. Thing was I admitted it was a hack in the original post, and the worst thing about a hack is that they tend to be a bit….

SQLBits 2019 Round-Up

Hello! I’m writing this from the train on the way home from SQLBits 2019. This year it took place in Manchester, and I have to say that I think it has taken the top spot from Liverpool as my favourite venue, but not necessarily my favourite city. You see typically I like to take the chance to visit some of the sites the cities that bits is in. And as beautiful as the Manchester City Hall is, and it really is a stunning building, nothing can quite compare to the epic 10 mile round run I did to visit Strawberry Fields and Penny Lane.

SQLBits 2019

Hello! I’m off to SQLBits this week: going for the full conference. One of the perks when you work for a company that has somewhat of a close relationship with the conference. I’m also doing a talk on Saturday at 8 in the morning. So that means I’ll be quaffing the diet coke on the Friday party and having an early night. It’ll be my first time speaking at Bits as well, although back in 2016 I wrote a lot of the demos on our precon.

Running Pester Tests In Terraform

Over the last 6 months or so I’ve been using terraform to deploy infrastructure to Azure. Prior to using terraform I was using ARM Templates and publishing them using Azure PowerShell, again for about 6 months. So I reckon I’ve got a reasonable grasp on both technologies and their similarities/differences and their advantages/disadvantages. But one thing I have done for both is run Pester tests post-deploy to verify the deployment was correct.

ARM Deployments and Nested Templates: A Real Hot Mess

Hello! (For this post I’m going to be referencing a project that I uploaded last week to GitHub. It creates some VM’s and installs the self-hosted integration runtime used by ADF activities. The last bit is not really relevant, but to get the most out of this post some experience with ARM and terraform would be handy. But you should be able to look at that project and follow it, hopefully!

Dynamic Annotations for ADF Pipelines

Hello! ADF is one otf those Azure tools that seems to be getting updated constantly. And to keep up with the changes I like to watch the videos on Channel 9. And last Friday there was another video which spoke about being able to tag pipelines. It’s talked about in the first few minutes of this video. Great! But we have pipelines that are executed by other pipelines. Wouldn’t it be even more great if these annotations accepted dynamic content, like most of the rest of ADF does?

Mocking With Pester

Hello! Last week we spent some time in the office here getting to grips with Pester. I’ve written some Pester tests in a few projects I’ve got in GitHub, most notably PoshSSDTBuildDeploy (henceforth known as PBD) and have them running in the VSTS CI build here and the Publish build here. So I felt I was quite familiar with Pester testing. But one thing I had missing in my tests was mocking.

T-SQLTuesday #110: Automate All The Things

Hello! Time for another T-SQL Tuesday, and this month Garry Bargsley invites us to write on two tasks for this months topic: • What do you want to automate or what automation are you proud of completing? • What is your go-to technology for automation? Go-To Technology For Automation Second part first: for the automation I have done, which has largely been around building and deploying SQL/SSIS/SSAS, and more recently Azure tech like ADF’s and Infrastructure in Azure, my go-to tool has been PowerShell.

New Year New Me 2019 Musings

Hello! And Happy New Year! Another year finishes, and no sooner than that, another one starts! Amazing! PoshSSDTBuildDeploy Changes Appropriately I start this year the way I finished last year: once again the Publish-DatabaseDeployment function gets a small but useful change. I’ve been deploying a database to a cheap VM using Standard HDD in Azure, and the deploy times out on post-deploy scripts. I think it only happens on the initial deploy as I’m not setting the size of the data/log files (tsk).

Christmas 2018 Musings

Hello! Another year draws to a close; 6 years blogging and counting… but forget looking too far dwelling on the past, what has happened recently??? PoshSSDTBuildDeploy Changes Of course the PowerShell module which has taken most of my time has had more updates. I’ve added some documentation to both the readme and a couple of the functions that end users will use most frequently. But I’ve also added a switch to Publish-DatabaseDeployment to stop the deployment summary being generated.

November 2018 Musings

Hello! Changes to dbatools Functions What I Wrote As the saying goes, it never rains but it pours. And there’s been a flurry of activity on a few of the functions that I initially contributed late last December to dbatools. Up to this point, there have been very few changes pushed and issues raised. However fom the middle of October to the beginning of November there’s been about 15 issues/pr’s. Extraordinary!

Enable and Disable ADF Triggers

Hello! I’ve been working quite a bit lately with Azure Data Factory (V2) and Ive been deploying them using ARm Templates. Well technically, I’m using terraform. But terraform doesn’t support ADF and so it’s a case of using terraform to deploy an ARm Template. Anyway, I digress… I’ve included the triggers in the ARM Template to be deployed, yet after the first deployment of the triggers i got this error below -

Hacktober 2018 Musings #1

Hello! A quick note on a few things - Azure Data Studio Last post I mused on the fact that VSTS is now Azure DevOps. I also largely ignored Ignite. Funnily enough it turned out that Azure Data Studio was released, an update on Ops Studio. Bare in mind you can connect to on-prem SQL instances still… Hacktoberfest 2018 is Live hacktoberfest 2018 is now finishing up, so you can now go and get a free t-shirt if you want that sort of thing.

September 2018 Musings

Hello!

I’m somewhat short on time wrt writing new blog posts. I have a long post in the making, and I have some Thoughts on Things, so I’m jotting down some bulletins in the guise of a blog post in the effort of keeping some momentum going. This year looks like it’ll be the least productive year in terms of number of blog posts, but I’ve been pretty busy behind the scenes both here and on random GiHub projects, helping organise SQL Supper meetups, helping and attending Hackathons, and just generally working and finding time to do fun things, like spending time with my family, running and of course play Pokémon.

This post was scheduled to go live on The Boy’s 3rd Birthday. A LOT has happened between now and then, including another baby, who is now walking. And yet I can hardly believe that the time really has flown by that quickly. But thanks to More Things, this post only just eeks it into September, keeping my momentum of one post permonth just about intact.

Migrating From Wordpress To Hugo

Hello!

This site has existed in some form since 2012. Way back then, I started the site with a view to sharing PowerShell scripts I was using to manage some very large analysis services cubes. And because I just wanted to get up and running with content and not worry about the look or the site, I decided to go with a Wordpress site. And because I wanted a custom domain I promptly chose to have a premium Wordpress site.

Deploying Logins With Different Domains Via SSDT

Hello!

Unlike certain objects like tables, views etc. in SSDT projects, windows logins can be more difficult to deploy, mainly because the logins will most likely be across different domains. In one place where I used to work they implemented a solution of having many different configurations in the Configuration Manager, and a pre build task would switch in and out certain files to be built. This is quite a messy way to do things, because you have to build the same project relative to the number of environments you have.

Salt and AssistDeploy Get Updated

Hello!

Having already randomly mused this month, here’s some more!

July 2018 Musings

Hello!

Some random musings on some things that aren’t worth a blog post on their own, but I want to talk about anyway.

New PowerShell Module - Release In Build

Hello!

I’m currently working on a project where we are using git as our source control, and so we have created branch policies on master that specify a specific VSTS build must be green before a pull request can be accepted. This is great, because what I like to do with any pull request is pull the branch and run the build, deploy and test scripts to make sure that at the very least, all this passes. So by adding the builds as a branch policy at least gives a thumbs up, at least in the sense that the code compiles.

PoshSSDTBuildDeploy 2.0.271.0 - Now With Better Exception Handling!

Hello!

Another quick bulletin about the changes I made to PoshSSDTBuildDeploy today -

PoshSSDTBuildDeploy 2.0.268.0 - Now With Issue Management!

Hello!

Concluding this weeks work on PoshSSDTBuildDeploy is adding one of the features I spoke about yesterday, which was joining the OperationSummary and Alerts objects to produce a new table that will make it easier to figure out which Operation is creating which Issue.

Let’s revisit the xml file that I running my tests with -

PoshSSDTBuildDeploy 2.0.262.0 - Now With Warnings!

Hello!

Continuing on with my efforts to make the SSDT Deployment Report more useful, I’m pleased to say that I’ve added reporting on any alerts that appear in the Deployment Report. Let’s take a look at a report that has warnings in it -

More Changes to PoshSSDTBuildDeploy - DeployReport Information

Hello!

This is going to be a short one because I’ve been working hard all evening on this, and frankly I’m a little tired. But I’ve made a change to PoshSSDTBuildDeploy which is going to be the start of more changes. Intrigued? Well, read on…

Birthday Musings and Deploy Using PoshSSDTBuildDeploy

Hello!

It is my birthday: another year older, another year not necessarily wiser. I’m planning on spending the evening watching the BBC and eating pizza. There are worse ways to spend a birthday…

Fix For Using Azure Active Directory and DacFX

Update:

PoshSSDTBuildDeploy Updated - Now With Pester Tests

Hello! Some weeks ago I got the tests working on the CI build for PoshSSDTBuildDeploy. The tests were a bit of a mish mash, however they sort of did the trick. Thankfully Eugene Niemand came along and very kindly offered to update the tests into Pester tests, and also just generallytidy up the mess. It’s always great to have a second pair of eyes on any code, as it helps it from not being quite so auteured…

Pre/Post Deployment Scripts in Composite Projects & The Expanding Brain Meme

Hello! So, I’ve been working on a project recently where we’ve been using an Azure SQL Database that is stored in source control as an SSDT Project. We’ve also been making use of the excellent TSQLT to run unit tests as part of the build. And typically I set up a Unit Test database to reference the main database so that the tests can be deployed separately from the main code.

Making Use of Demands and Capabilties on VSTS Build Agents

Hello! I have recently been creating a pipeline in VSTS that will check if certain software/PowerShell Modules are installed on the box, and if it isn’t then install it. It’s a sort of a DSC/Chef/Puppet process done via VSTS so that I don’t have to configure the aforementioned software. But I needed a way to target a specific build agent to run this build on; specifying an agent pool is no good as it will deploy on any in that pool.

PoshSSDTBuildDeploy Updated - Now With Install .NET!

Hello! I’m currently working on a project that requires building and deploying SSDT-based projects. So it’s been a great opportunity of making use of PoshSSDTBuildDeploy and work through any issues. And there have been a few minor updates to the module over the past fortnight which I’ll go through now! As I type the latest version is 2.196.0. Return Path of MSBuildDataTools - Just so much easier to have location returned so that when you execute Invoke-MSBuildSSDT and Publish-DatabaseDeployment you can just pass the outputted variable in.

PowerShell Snippet 13 - Skip Logging Into Azure Everytime With This One Simple Trick

Hello! I have been working an awful lot with ARM Templates and publishing resources using PowerShell. As part ofhte process we’re using a Service Endpoint to publish resoruces to Azure. So the login and subscription context are set by the endpoint itself. However, when running the scripts locally, it is necessary to login first. so you have two choices - login before running the script, which isn’t always possible, or include Login-AzureRmAccount in every script, and then obviously forget to remove it prior to checking in and wondering why releases takes forever…

How Not To Create Your Service Principals for VSTS/ARM Connections

Hello! I’ve recently started a new project, and we’re going to use VSTS for the build and release pipeline. This includes deploying infrastructure from ARM templates. This is going to provide its own set of challenges, in that the VSTS Team is new and there is no authentication between VSTS and the Subscription the objects are deploying to. So it really is a “green field” project. But this is a good thing, as we get to deploy as much as we can from source control and limit the interaction with the Azure Portal.

I Still Have No Idea What I Am Doing...

…but crucially I now have https on my static website. And all I had to do was migrate to GitLab! Next post will be about how this site is built. Stay tuned!

Musings March 2017 - Project Updates

Hello! I’m due to sit an exam this week, the excitingly titled “Designing and Implementing Cloud Data Platform Solutions”. Revision is hard going, as my ability to concentrate on reading pretty much anything seems to be less an less potent as I get older. And so by way of a distraction/procrastination, I thought I’d write a brief summary of some of the changes I’ve made to projects in GitHub this month.

Syncing SSAS Databases Confusion

Hello! I’ve been playing around with a PowerShell module called RunSyncRun that I’ve never quite got around to completing. What it aims to do is to execute the sync function between SSAS Databases. Part of the problem is that it takes a while to get a test environment set up, but I finally got the WideWorldImporters sample project up and running as a good place to start. The crux of the whole module is to run this relatively small piece of xmla found in StartDatabaseSync -

The Mystery Of The Failing Azure Cmdlet

Hello! I’ve been helping out a team with setting up a deployment pipeline that uses Azure Data Lake Store, and so the obvious way to do this is to make use of the AzureRM.DataLakeStorePowerShell module. For those of you not ITK, Microsoft provide a set of PowerShell cmdlets that make use of the Azure Resource Managermodel for managing Azure resources. These can be downloaded via the PowerShell Gallery. And because there are a fairly large amount of cmdlets, these are split into subsets, such as the aforementioned AzureRM.

On The Move

This is the first post in earnest as I begin the arduous task of migrating away from BlueHost/WordPress and their paid services to a free static web site. Write-Host "I have no idea what I am doing."

PowerShell Snippet #12 - Creating Arrays of HashTables in PowerShell

Hello! As part of SQLDWSchemaMigratethere is a function called Compare-Rows, which finds if there is a difference between the number of columns for a given table that exists on both source and target tables. And I need to return a hash table of the schema/table where there is a mismatch wrt column count. What makes this a challenge is that hash tables cannot store duplicate keys. I was setting the key to the schema name, so theoretically I could just set the value to the schema and the key to the table.

Execute Multiple Files Using SQLCMD or SQLClient - Which Is Faster?

Hello! I’ve been working on a PowerShell module that will migrate the schemaof an Azure Data Warehousefrom one database to another. The process has through quite a few iterations, and at one point I was heavily using sqlcmd utility to execute each “CREATE [OBJECT]” statement. Whilst this proved effective when playing around with a small database, when I tested the module against a real ADW it became clear that this process was far too slow.

PowerShell Snippet #11 - PsBoundParams

Hello! There are many things I like about PowerShell, one of which is the automatic variables that are created. For example, $PsScriptRoot is set to the location of the running script (and if you run $PsScriptRoot in a .psm1 file, it sets $PSScriptRoot as the path of the file as opposed to the script.) And one of the automatic variables that I recently discovered is “PsBoundParams”. This handy little variable is a hash table that keeps track of what parameters have had arguements passed to them in either a script of a function.

Deploy Dacpacs with dbatools The Script

Hello! Not so long ago, I wrote about how dbatools now has functions that can be used to deploy dacpacs. What I didn’t actually include was a script to do same, so without further ado, below is a script that makes use of Publish-DbaDacpac - There is also a -Verbose option. But even without it a successful deployment will spit out quite a bit of information - And of course, if you run Get-Help Publish-DbaDacpac, you get the built in help -

SSISMSBuild Updated

Hello! 3 updates in one week?! It appears that though I took some holiday and didn’t even so much as touch a PC over the Christmas/New Years season, other people were still working hard. SSISMSBuildis a project that has had a long life. In short, you can build ispac projects without needing Visual Studio, however you do need Integration Services installed. Broadly speaking the project is not mine, I’m just hosting it.

Alter PowerShell ModuleVersion Number - More Bug Fixes!

Hello! Another day, another bug. This time in a VSTS extension I wrote that would alter the ModuleVersion Numberin a psd1 file. Sadly I didn’t test this against psd1 files that have required modules, so the ModuleVersion numbers were updated for those also! This is helpful to precisely no one, so I made a minor change in how the ModuleVersion is found. Link to GitHub Issue. The bug itself is fixed as of 1.

salt Release 1.1.2562 - With Bug Fix!

Hello! Very recently a user of salt got in touch with me about a bug when setting the job schedules up- when creating a schedule an owner is created. And only the owner (or sysadmin) can modify the schedule. So if the deployment account is not sysadmin then it needs to be the owner of the schedule. Problem An owner is identified by a SID, which is stored in a varbinary column in SQL Server, so when we set the SID variables in PowerShell they are stored as byte arrays.

vstsBuildWatcher Updated

Hello! As is always the case, I was about the furthest away from thinking about computers when I realised that I had made some stupid decision WRT the logic in vstsBuildWatcher. So in between kicking a ball around my living room with The Boy (and causing very little damage in the process) I spent the afternoon uodating the vstsBuildWatcher Module. I’ve pushed the changes out to GitHuband PowerShell Gallery. The major change is that by default both successful and failed builds are monitored, and you have to explicitly filter only failed builds by including the “-showFailedOnly” flag.

Deploy Dacpacs With DBATools

Hello! Recently I’ve been working on a PowerShell module called PoshSSDTBuildDeploythat makes use of Microsoft.Data.Tools.MSBuildto build and deploy SSDT-baseddatabase projects. And then not long after the fact I started working on functions within the dbatoolsPowerShell module to deploy dacpacs. In fact here’s the tweet of the announcement - https://twitter.com/psdbatools/status/940650316235706369 And here is a link to the source code on GitHub. This is all good stuff because I’ve spoken to a number of people who have written, rewritten and re-rewritten the PowerShell to deploy dacpacs numerous times before, and we’ve all agreed that once is enough.

Fix Leaky Battery on Ubuntu Touch With This One Weird Trick

Hello! With a spare Nexus 5 knocking about, and somehow too much time on my hands, I decided to flash the phone with Ubuntu Touch. having tweeted said decision I got a response from someone about an annoying bug which will drain your data - Seems to have an annoying bug that makes repeated requests to "https://t.co/xT6GOIi3KY". Needs blocking in localhost or you will eat mobile data. It's actually pretty good but needs lots more people to support it.

PowerShell Snippet #10 Check Version Number is Valid

Hello! Lately I’ve been working on PowerShell Modules that are published to PowerShell Gallery as part of a build process in VSTS. And so I’ve been creating version numbers and passing them as variables. However I wanted to validate them before they were applied. Below is a sample of how to verify that the version number you’re pushing conforms to semver

PowerShell Snippet #8 Build SSDT With MicrosoftDataToolsMSbuild

Hello! Below the “how to” script, there are two functions for you; the first will download MicrosoftDataToolsMSBuild. The second will compile an SSDT project/solution using downloaded package. There are no other prerequisites other than MSBuild Tools being downloaded/installed on the box.

PowerShell Snippet #7 Alter PowerShell Module Version

Hello! Wow, day 7 already! And for today’s post, we getting meta. I have a few builds on VSTS that run PSScriptAnalyzer on some PowerShell modules, and also runs some tests. After these steps have run successfully I publish to both NuGet and PowerShell Gallery (yes that’s right, Continuous Development FTW). Packaging via NuGet is simple enough: there’s several out-of-the-box NuGet steps in VSTS, including even a Custom step that allows you to submit version number/prefix.

PowerShell Snippet #6 Test DotNet Version Installed

Hello! Continuing on with our daily PowerShell Advent Calendar Extravaganza, this script here will return the current .NET version installed on the machine. You can either choose to add the target .NET version or not. If you add a .NET version number and at least that version isn’t on the machine, the script will throw an error, but will continue to run and return the PSObject, unless you set the “ErrorAction” to “Stop”.

PowerShell Snippet #5 RunPsScriptAnalyzer on VSTS Hosted Build

Hello! As someone who has some PowerShell modules knocking about, it’s important to get some checks running prior to publishing. As these modules are in source control, it makes sense to launch builds that run PSScriptAnalyzer as soon as a pull request is made on the branch. But running PSScriptAnalyzer on the hosted build agent is a little difficult as not only does it not come with it installed, the service account running the builds don’t have permissions to install it!

PowerShell Snippet #4 Deploy Cubes with Powershell New and Improved

Hello! Not too much to say here except that here is an improved script on deploying cubes with PowerShell!

PowerShell Snippet #3 Install Nuget Packages Using PowerShell

Hello! When I was recently putting together a repo I needed to get a few packages from Nuget. I wanted also wanted to automate getting the packages for when other people downloaded the repo and ran the scripts. So it made sense to create a function that would download the package and test that the download was successful. For info, read the synopsis!

PowerShell Snippet #2 Check and Install a Module

Hello! Today’s PowerShell Snippet will check if a module is installed, and if it isn’t will install it. Finally it will import the module.

PowerShell Snippet #1 - Trimming The String

Hello! When building up urls from different parameters in something like TeamCity, or Octopus, it’s simple enough to get double “//” in urls if the parameters are not consistent. So little helper functions are always useful to have imported to manage such things. Below is an example of such a thing!

PoshSSDTBuildDeploy 2.0 Released Generate Scripts

Hello! Today I’ve finally got around to altering PoshSSDTBuildDeployto generate the deployment scripts/report. These can be generated with or without Publishing the changes. Run-Testfile has examples of how this works. It is however, a breaking change, hence the update to version 2. This is because I moved from using the Deploy method to the Publish method. You can read about this method on the SSDT Blog Post. This method was introduced way back in October 2016.

PoshSSDTBuildDeploy Update - Now With Dynamic SqlCmdVar Updates

One of the big challenges in the whole “build once/deploy many” ideology is the need for environment configuration. And this is true with SSDT-based database deployments. Happily the variables are stored in the publish.xml file. And so today’s release of PoshSSDTBuildDeployadded a switch on the Publish-DatabaseDeployment function. When included, this switch (getsqlcmdvars) will attempt to resolve SqlCmd variables via matching PowerShell variables explicitly defined in the current context. The drawback here is that all SqlCmdVars have to exist in the current context.

PoshSSDTBuildDeploy Update - Now With LocalDB

Hello! Late on Friday I published a PowerShell Module that gives working examples of using DataToolsMSBuild. All very interesting,go and have a read of my previous post. One of the things I really want the module to do is to set up all software required for building/deploying. And so from version 1.0.99.0 onwards there are three new Functions that will check if LocalDb is installed, and if it isn’t then it will download and install.

PoshSSDTBuildDeploy Released

Hello! Over the recent past I’ve been working on a variety of build/deploy PowerShell scripts that make use of Microsoft.Data.Tools.MSBuild. And I’ve been wanting to get like a standardized “this is how I would do it” set of scripts together. And so I’m happy to say that I’ve got around to it. The source code is on GitHub, and the releases are on both NuGet and PowerShell Gallery. There is a test file to to show the expected workflow.

DevOps Teams Maturity Levels

You can tell a lot about how much someone has embraced continuous delivery by how they react when either a build or an automated deployment fails. I have worked with quite a few teams who are at different levels of working with continuous delivery, but irrespective of whether they familiar with the practice or not, you can tell how adaptive a team member is when something goes wrong, like a failed build or deployment.

SQL Server 2012 SP4 Released - Clone Database Added

Hello! One of the most interesting features of SQL Server 2012 Service Pack 4is the availability of the management command DBCC CLONEDATABASE. The idea of it is to create an “empty” copy of the database; all the metadata and statistics of the original and clone are identical, but the clone contains no data. The syntax of the command is very straightforwardwith only 4 arguments required. So I ran this on a very small (50mb) database, and within a few seconds it was completed.

Updating XML With PowerShell Now Including Elements

Hello! So, last month I posted an article about changing XML values in memory. All very useful for when you have to manage configuration in the modern world of “build once, deploy many.” I’m not going to repeat myself on what I said on that blog, it’s all very interesting to for people who like that sort of thing, go and have a read. Yet it only focused on a somewhat simple example, and so as XML can get very complex, I wanted to show a similar method to find elements where they are not unique by making use of attributes that are.

AssistDeploy Samples Repo Released

Hello! So AssistDeploy has been out for a little while now and so I decided to release the project I was using for testing during development. At the moment it uses just one scenario I used for testing as opposed to the several that I ran through. This is because initially I just want to provide a straightforward working sample. What became quite a big challenge was the fact that you need more than just an Integration Services project.

Kill a Connection, Session, or Command in Analysis Services

One of the useful features of the SSAS Activity Monitoris the ability to cancel sessions and queries. Though there is no obvious way to do this through SSMS you can in fact write an xmlaquery to kill either the session, connection or command: If you specify the connection ID it will kill all sessions, if you kill a session it will cancel all SPIDs that pertain to that session, and if you kill the SPID it will kill that one command.

Failed SSIS Build DtsRuntimeException

Hello! I was helping a team debug a failed build today. Despite the fact that the build compiled on the devs box - something that’s become almost cliché to hear these days - it would fail using devenv.com via cmdline. Annoyingly, there was no errors outputted. So i cloned the repo and took a look myself - This is helpful to approximately no one. But based on experience we figured out that the Azure Feature Pack for SSIS needed to be installed on the build agent.

What Do You Get if You Unpack an Ispac

I’ve spent what feels like a lifetime working on automating SSIS deployments, more specifically SSIS packages that have been created in Visual Studio that use the project deployment model. If your SSIS Project is using this model you’ll know that the output of a build is an .ispac file. But what is in an ispac? Funnily enough, someone forwarded me a link to a page that covers some details about the ispac file format on MSDN.

Deploy SSAS Cubes With PowerShell

Hello! Recently someone got in touch with me about one of my earliest posts on my old blog. I realised I had made a mistake on that post and had updated. But I also realised that the script itself is actually part of a PowerShell module. So wanting to make things as easy as possible, and being the helpful guy that I am, I have altered that example into a script which I am posting here.

Some Notes on AssistDeploy

Hello! I’ve spent a large chunk of the past few months putting together a PowerShell Module that will automate the deployment of SSIS solutions. Recently it went open source, and because I have a poor sense of humour I called it AssistDeploy. Aside from the readme, each function has it’s own documentation in the header. I’ve also written a rather large post on it at my companies blog. All very detailed, go and have a read.

SSIS Package Execution in Azure is Out Now

It’s been a long time coming, but now SQL Server Integration Servicesis in Public Preview on Azure! I’ve written about it elsewhere in greater depth, but here are the headlines: If I haven’t already made it clear - go and read my in-depth post!

How To Use MSBuild Arguments in Visual Studio Builds

Restore BACPAC to SQL Azure

[Hello! 2 posts in 1 day? When you’re hot, you’re hot! I’m actually writing this one whilst I’m waiting for a BACPAC to restore to Azure. It’s actually incredibly easy: use SQLPackage! Setthe /SourceFile to your bacpac, and use the connection string published on Azure, and you’re done! Only a short post yes, but the precursor to some much longer and more interesting posts…

Updating XML Using PowerShell

Hello! One of the patterns in Continuous Deliveryis to “build once, deploy many”. Another is to “deploy the same way to each environment”. This is certainly easier said than done: The 3 biggest challenges in creating a Continuous Delivery pipeline can be summed up as follows: Below is such an effort at achieving this. The xml I have (lines 1 - 7) are a representation of the notification methods of a SQL Agent Job.

Get Dll VersionInfo Using PowerShell

Hello! Let’s be brief and not mince words: PowerShell is great. And so recently I needed to get the VersionInfo of a given DLL, and so was able to write up a module that did exactly that.

Create or Update Octopus Variables Through The API

Hello! As I have mentioned many times before, I have conflicting opinions when it comes to Octopus Deploy. On the one hand I think it does things that are a bit lousy, like variables only being strings for PowerShell (so it is not possible to include HashTables scoped easily). And whilst we’re on the subject of variables, the page to enter/edit variables is a tedious process. Firstly it has an autocomplete feature for the scoping, which is fine for tiny installs but is painfully slow on larger servers.

T-SQL Tuesday - Deleting SSAS Cube Partitions Using PowerShell and AMO

This month’s T-SQL Tuesday topic is about automating with PowerShell. I’ve been using PowerShell to help automate deployments for all sorts of SQL solutions; SSDT-based database projects, ISC-based SSIS projects, SQL Agent Jobs, multi-dimensional SSAS projects, to name a few. And when I worked as a DBA I wrote many ad-hoc scripts to automate many tedious processes I was required to do. And so when it came to pick a topic for today’s post I decided to go for something that not so much offers a complete, out-of-the-box, one-size-fits-all solution to everyone’s automation woes, but a bit of a left field example of how you can make use of .

Truncate vs Delete in SQL Server Whats the Difference

I’ve been using TRUNCATE TABLE to clear out some temporary tables in a database. It’s a very simple statement to run, but I never really knew why it was so much quicker than a delete statement. So let’s look at some facts: But how fast is “faster”? The below script creates a table, inserts some random data and then truncates the table with IO and TIME stats switched on. It then repeats the process but this time executes a DELETE instead of TRUNCATE.

Mega Melee Competition

Hello! Ive yet to write up in any detail on the No Holds Barred Competition, but frankly seeing as this write-up is going to be a lot simpler and shorter I’m going to start here. The competition for September is called “Mega Melee”. This Single Battle competition allows for you to use only Pokémon that are capable of Mega Evolution. To be clear, despite a team of 3-6 being added, only 1 Pokémon will be used during each match.

New Baby Musings (AKA Things I've Thought Upon On Sleepless Nights)

ello! One month ago I became a dad for the 3rd time. And so my spare time, which has always been a premium, has shrunk even further. But here’s some thoughts on some stuff: SQL Stuff

Deleting SQLCMD SetVar The Brute Force Way

Hello! Security has always been an important issue, and this is more true today than it ever has been. And one of the cardinal sins of IT Security is storing sensitive data as clear text stored in files. So with the case of sql scripts generated by SSDT, this sets an issue, as the variables are stored as clear text. I know of no way to mask variables, and so there are three options:

Seven Things I Learned From The Weakness Cup

Hello! About 2 weeks ago I had just come off the back of a very busy weekend where some way or another I was still able to find time to take part in The Weakness Cup. I didn’t play the full quota of matches, but at any rate something was far better than nothing [1]. I’ll post my team review sometime. But for now I thought I’d share some things I learned whilst taking part in this competition.

Using PowerShell To Find All Possible Combinations of A Sum

Aloha! (If you want to cut to the chase and just get the generic PowerShell that will find all possible combinations, this gist here is what you’re looking for.If however you want a bit of context then read on…) So, I’ve recently been working on a solution for automating SQL Agent Job deployments[1], and I came across a problem. In my solution, the constituent parts of a SQL Agent Job would be stored in an XML file.

Pi-Hole AKA At Last! A Use For A Raspberry PI!

Hello! The titles is only half serious, but recently someone told me about Pi-hole, an ad blocker that runs on Raspberry PI. This meant that I am able to change my DNS Server settings on each of my devices that use my home network, and now all ads are blocked. Neat! So I installed Raspbian Jessie Lite on a Pi I had spare from when it was running XBMC/Kodi/whatever it is called now, set up SSH and plugged it into a switch that runs off my Router.

My Evening With SQL Tool Service

[Hello! I’ve been writing a PowerShell Module that will handle deploying SQL Server Agent Jobs by using the Agent namespace in SMO. But outside of work I’ve been very much into playing around with Linux: I’m slowly making my way through Linux From Scratch; I’ve got Pi-hole setup on a Raspberry Pi; and my laptop dual boots into Windows and Ubuntu. And the last time I booted into Windows was May.

LocalDB DSC Script

Hello! So, there are many different product SKUs of SQL Server available, and generally when developing it’s probably best to use SQL Server Development Edition, even when it used to cost you money to get a product key. The disadvantage of using it though is that it is a large install. The team behind SSDT recognised this and so LocalDB was created, with the intention of using it as a very lightweight version of the database engine to develop against.

SSISDBs Questionable Rollback Strategy

Lately I’ve been working on a deployment process for Integration Services Projects that makes use of the views and stored procedures in SSISDB to deploy folders, variables, ispacs etc etc. The reason why I’ve gone this route instead of any of the othersis so that I can just use Sql Data Client dll which is part of .NET. So no extra moving parts required when deploying. And as part of the deployment I want to run the validate projectstored procedure.

What Playing Minecraft has Taught me About DevOps

I’ll admit this post is a little off-piste, so let’s see how this works out. I’ve ruminated over this topic as a blog post that I have had in the back of my head for a couple of years now. And what with it being T-SQL-Tuesday, and having just off the back of playing Minecraftfor the first time in a while with my nephew, now is as good a time as any to write this post.

Testing That Your Deployments Aren't Lousy

Aloha! It’s my birthday, and I’m also on holiday, so what better way to spend it than writing a blog post. In any Software Development Lifecycle Methodology, be it Scrum or Waterfall, there is a beginning and an end to the process, no matter how frequent those iterations are. That is to say, there is a process which relies on input from an upstream process before it can proceed. Generally it goes like this: the customer wants, BA’s spec, Devs code, Testers, umm, test, it gets released and in between that whole dev/test thingy is the non-trivial matter of deployment.

Simple Sql Connection in PowerShell

Hello! This here is more for the benefit of me than anyone else, but this is a super simple PowerShell script that checks connection to a SQL instance, as well as switching to a database. It references standard ADO.NET classes so nothing else needs to be installed on the box other than .NET. So This will even work on Linux, providing .NET Core is installed of course.

DevEnv And The Case Of No Projects Building

Aloha! Someone emailed me with a problem they were having on compiling SSIS projects. Or rather, it’s a case of the projects notbuilding. This was there output: “Rebuild All: 0 succeeded, 0 failed, 0 skipped Build succeeded. 0 Warning(s) 0 Error(s)” You see, when it comes to Visual Studio Project files, they’re essentially just a MSBuild file. And so when they’re built they’re compiled using MSBuild engine. Even those through Visual Studio.

How To Check Variable Exists in PowerShell Session

Hello! Recently I needed to check that a variable exists in the PowerShell session currently running. This is actually far easier than it sounds. So here is a simple demo for how it works. The magic here is the “Test-Path variable:my_variable” on lines 4. It tests that a variable of that name exists. If it does, great, let’s print out the value. If not, let’s alert that it doesn’t. The second example of this on line 11 will do exactly that.

Do Not Trust My GitHub Projects

Aloha! Recently I’ve revisited one of my GitHub projects, TurboLogShip. I’ve been wanted to write a presentation around what it does and why it’s actually really important and super useful for people using log shipping in SQL Server. Essentially it speeds up log ship restoring to a readable secondary to the fastest it can be without upgrading hardware or software. And the idea of trying to do something for free when your hardware/software may not even be the problem is a pretty compelling reason to use it.

SQL Server on Linux Musings

Hello! In case you missed it, a fortnight ago SQLBits was held at the Telford International Centre. Believe it or not this was in Telford. For those of you not in the know SQLBits is Europe’s largest community SQL conference. I suppose the name would suggest that the conference focuses on SQL Server. In fact it’s far more than SQL, it’s really more of the Microsoft Data Platform. Of course whilst there’s plenty of talks on SQL Server subjects such as Clustered Columnstore, SQL Azure and SQL Server on Linux, there’s plenty of talks on tools such as Power BI, Document DB, R Server….

Post Easter 2017 Musings

Hello! I’ve returned from the dead: not literally, but I was so ill over the past week that I thought that as opposed to calling for a doctor I was going to have to call for an undertaker. But now I am better and ready to prod buttock. Anyway, some random musings over the Easter time. Zesty Zapu Is Glorious Ubuntu is still installed on my Surface Pro, and I’m enjoying it very much.

Dropping Foreign Keys The Lazy Way

Hello! So, on another blog a long time ago, I wrote a couple of posts about foreign keys and what step can be taken to suspend them when we are trying to load/delete data into them. There’s the elegant wayand the brute forceway. Hang On… “But aren’t they there to stop this sort of thing?!” I hear the sensible people cry. And yes, this is true. BUT! Say we are inserting static data into a database at the point of creation using a tool like SSDT, we may not care about what order the data is loaded in, just that it is in.

April Fools 2017 Musings

Hello! Non Fools Disclaimer I will preface this post with the following: despite the fact that this is a post on April Fools, and that the name of the post does say April Fools, there is no fooling in this post. There’s enough ways to waste time on the internet without reading made up stuff on my patch of it. My Number One SQL Server Feature Request If you were to ask me what new feature I would like to see most in SQL Server, I would immediately say “logless tables”, and maybe after a slight hesitation I’d say “logless databases”.

How To Rollback an Octopus Step Template to Previous Version

Aloha! If you’re here because you desperately need to go back a step template version and don’t have a copy saved anywhere except within Octopus, and you haven’t got time to read up on my two cents, scroll down to the “I Didn’t Come Here For A Lecture On Communism” section. But be warned; this is not a tried and trusted method, but a hack. I accept no responsibility for this not working or breaking your Octopus Deployment application.

When NOLOCK LOCKS!

This is hardly breaking news, but it’s important to know: when using NOLOCK table hints in SQL Server, it can cause blocking. Yep, you read that right. Given the fact that the table hint is called NOLOCK it’s counter intuitive to what you’d instinctively think, but it’s fact and by design and also, there’s nothing wrong with it. Part of what’s wrong with NOLOCK can be summed up below: [tweet 842078448524881921 ]

Configuring Available Memory for SQL Server Reporting Services

If you need to configure the amount of memory available to an instance of SSRS you have to get your hands dirty and edit the RsReportServer.config file. The RsReportServer.config file stores settings that are used by Report Manager, the Report Server Web service, and background processing. The location of the rsconfile file is generally “\Program Files\Microsoft SQL Server\MSRS1111.MSSQLSERVER\Reporting Services\ReportServer” Within the RsReportServer.config file, configuration settings that control memory allocation for the report server include WorkingSetMaximum, WorkingSetMinimum, MemorySafetyMargin, and MemoryThreshold.

Setting Up Ubuntu in Azure

Aloha! Late last week I decided to set up an Ubuntu box in Azure with the intention of installing SQL Server for Linux and VS Code on the same box. VS Code is necessary because there is no Management Studio shipped with SQL Server for Linux (command line only, see?) I know, I could use the command line, but I’m lazy and there’s an extension for VS Code called mssql, which funnily enough is exactly what you think it is.

Pokémon Focus: Drapion

I’m hoping to critique Pokémon in terms of their suitability for competitive battling at random blog posts. I’ve made a rod for my own back by having a blog, and I don’t want to feel compelled to post every week if i did a “Pokémon of the Week” series. So I thought that random entries into my misadventures of losing way more than I win in Battle Spot with Pokémon I like would perhaps be interesting, if not informative…

Veekun Pokedex Now Available For SQL Server

Hello! If you know me you know that I love Pokémon. It is my favourite video game series going today. It’s been going on for 21 years now, an within the game there is a lot of data, that clearly sits within a database. And being a database guy, I’ve always wondered how the database would be structured, and how the changes to the game would be incorporated. Long ago, a guy called Veekun created a copy of this databaseand posted it on GitHub.

3rd March 17 Musings

Hello! Sitting here waiting for a Runbook in Azure to run whilst waiting for a deployment to run in Octopus. So might as well go meta and post some thoughts. So yes, not really sure what the direction of the series is going to be, or how long it’ll be. The fact that I’m starting to look into Octopus.Client, which seems to have some good functionality baked into it, means I might change the direction the posts are heading.

Achievement Unlocked Completed Duolingo French Course

Well, it only took me on and off 8 months, but I finally completed the Duolingo French course. For those of you not ITK, Duolingo is a very cool and very free (advertising not withstanding) tool that aims to gamify learning a new language.It also aims to get people to translate simple statements in documents. Amazingly, Duolingo is valued at nearly half a billion dollars! I can’t say that I am now 40% fluent, despite what the app tells me otherwise, but I’m certainly better at reading and writing French, and I can listen OK.

SQLBits Training Days and Agenda Announced

Hello! Earlier this week the Training Daysand Agendas for Friday and Saturdaywere released for SQLBits. I will be there on the Friday and Saturday, which means I’ll get to enjoy the party! Last years party was nuts in that there was a lot to do and lots of free food, and even free alcohol! Take a look on the media page of the SQLBits Twitter account to get an idea of the conference.

Configuring Available Memory for SQL Server Reporting Services

This post has now moved to my new site. Why don’t you check it out?

Blog Examples: How Much is Too Much TMI?

Hello! So, recently at work I have been working on automating SSIS Builds using DevEnv, but called through MSBuild. Fortunately I wrote a blog post about this on another blog a long time ago, so was able to retrieve the info easily enough. Trouble is, the sample code I had on the script had some formatting issues. See all those $quot; entities? You see sometimes, double quotes are formatted as the html " entity.

Extract Dlls From GAC

Hello! And a belated Happy New Year! Occasionally there is a need to get a dll out of the GAC and onto the file system: sometimes its useful to load directly into PowerShell using Get-Type from a folder rather than loading. And I needed to get a way of getting A LOT of dlls out of the GAC, so here is the command line to extract from the GAC using cmdline.

Delusions Of Testing

I love Christmas, and I love Christmas films. One of my favourites is “Elf” with Will Ferrell. I know that the world and his wife have seen it, but for those of you that haven’t: The plot revolves around Will Ferrell’s character Buddy coming to terms with the fact that he is not an Elf, but is a human adopted by his Elfish father, played brilliantly by Bob Newhart, and goes on a journey in New York at Christmas time to find his biological dad.

Prior to him discovering he is human, Buddy’s apparent incompetence in the toy-making department sees him transferred from there to the testing department, where the “special” elves go. And this I always felt was a good analogy to how the relationship between testers and developers is seen in the IT Industry.

SQLBits Precons Announced

Today the pre-cons for SQL Bits 2017 were made public, and what a diverse list there is. Increasing from 24 last year to 32, there’s of sessions to choose from on the Training Days. Naturally I will be there for the full week. If you’ve never been, and you live in the UK, or even Europe, be sure not to miss out. The cost is very reasonably, there is a party on the Friday night, and the Saturday is free to attend.

What Has 2 Thumbs and is Presenting At The UK Cloud Infrastructure UserGroup This Week?

..This guy! If the party season has you all partied out, and the train strikes have not put you off coming into London, why don’t you join me at the Impact Hub King’s Crosswhere I will be talking about NuGet packages and their uses, specifically pertaining to the Microsoft.Data.Tools.MsbuildNuGet package released by Microsoft, and the PowerShell.SqlServer.Modulespackage published my yours truly.

More From Yesterdays Connect 2016 KeyNote

Microsoft’s announcements during the Keynote yesterday was like waiting for buses: you wait for one, then 17 appear all at once! In case you don’t have 2 1/2 hours spare to watch yesterdays Keynote, I made some notes:

Microsoft Goes Open Source

Although in reality the notion that “Microsoft have gone open source” is hardly a news bulletin, today on the Connection(); keynote Microsoft have shown just how far they have come by announcing that they are now an Official Platinum Partner of the Linux Foundation. This is another major milestone for a company that, according to Rob Mensching, never really “understood what the Open Source community was really about”. In other news, Visual Studio Code (Microsoft’s open source code editor) continues to grow apace with more users (1 million active users) and more plugins written, including one for SQL.

Happy Birthday PowerShell

PowerShell is ten years old today. If you work with Microsoft products you’ll be familiar with PowerShell. It’s almost impossible notto use PowerShell. You’d have to be pretty belligerent to ignore PowerShell. If you know me you know I love PowerShell. I truly believe that anything can be achieved by using PowerShell and a well-written API. Look at how well it integrates with tools like TeamCity and Octopus and you know it’s vital to work with DevOps.

Don't Be A Jerk - Use Approved Verbs In PowerShell

Today I’m going on a mini rant about people abusing PowerShell, specifically to do with naming PowerShell functions. If you’re going to take the trouble to write PowerShell functions and add them to a module for others to have access to, please, don’t be a jerk, use a verb from the approved verbs list that is freely and readily available on the MSDN Developers site, or even by typing “get-verb” in the console.

Pickup A Book

Earlier this year, I had a wood burning stove installed in my house. I’ve always wanted a real fire, but growing up we only ever had the electric type. So I was really pleased to finally get a proper fire. They’re pretty to look at, but wood burning stoves also generate a lot of heat. You can burn coal, but I’m a bit of a environmentalist and so I’ll stick to burning wood.

Hello World

OK, so here we are again, writing my 4th ever “Hello World” type blog post. TL;DR - last blogs content was merged with another blog, and I had the opportunity to restart making content again. And so this blog is that aim realised. In the coming days I will post more and get into why I started yet another blog. But for now, hello, good evening, and welcome.

Remove the Limitations Of LocalDB With This One Simple Trick**

**(not a trick) If you are developing your databases in SSDT, then chances are you are familiar with LocalDB. LocalDB is essentially as stripped-down version of the database engine that runs within Visual Studio, and whenever you hit F5 the database is deployed to LocalDB… sometimes. You see, LocalDB is in fact pretty much SQL Express, the free version of SQL Server. Not that there’s anything wrong with that, except that SQL Server Express does not support all features, like say for example, full text search.

SQL Server Compression 101

The data compression feature in SQL Server can help reduce the size of the database as well as improve the performance of intensive I/O intensive workloads, especially on data warehouses. This performance boost in I/O is offset against the extra CPU resource that is required to compress/decompress the data whilst data is exchanged with the application. With data warehouses, the guideline is to compress all objects as there is typically CPU capacity whilst the data storage and memory capacity is at a premium, so Microsoft’s recommendation is to compress all objects to the highest level. However, if there are no compressed objects at all, it is better to take the cautious approach and to evaluate each database object individually and the effects on the workload, particularly if the CPU headroom is limited.

When To Use Octopus Deploy Script Modules

Hello!

Lately I’ve been thinking a lot about script modules in Octopus Deploy. Script Modules, for those of you not ITK, are a collection of PowerShell cmdlets or functions that exist outside of a script step that can be used across multiple steps across different projects/environments. They don’t have to be used to contain modules exclusively; they can just contain that will get executed upon the initial import of the module. The issue here is that these script modules are imported or every step in a process. So unless the code you write is idempotent, you’ll probably cause an error in the process. This is by design.

So at first I thought “put nothing in script modules” because they’re pretty pointless, right? Anything you write will pertain to a step exclusively, so why do you need to share code across steps. Turns out quite a bit of code is shared across steps, particularly checking and validating parameters.

Then, as step templates got more sophisticated they got harder to read, so then I thought “put everything in script modules!” It aided in legibility of the step templates and allowed them to focus mainly on setting up a process and using script modules to execute the function.

There is however an issue to this: script modules are the “magic” of a step template. If you’re not aware that a step template requires a script module, how can you communicate this implicitly? You could mention that a script module is required in a step template, but this is still a manual process that everyone remembers, or even buys into.

There is also another issue: I had to make a change to a step template that required a change to the script module. The step template in question was in heavy use, and so careful planning was required to make sure that the change in the step template and the script module was not a breaking change: basically what I mean is that whereas a step template has to be explicitly updated for a given step in an deployment pipeline, the same cannot be said for a script module. So whatever is the latest version of the script template is what a user gets. And if you make a few changes to the step template in an iterative manner, you have to be sure that if anyone updates the step template then all version have to match one another.

SSDT And Pre-Model Scripts Part 2: Execute Pre-Model Scripts Using Octopus Deploy

Hello! Part one of this post concerned the notion of SSDT and Pre-Model Scripts. This post relates to an actual implementation of executing a SQL script using Octopus Deploy. My pull request on Github was approved, so my step template can be exported from the Octopus Library. The only part that I feel requires some explanation is the SQL Scripts and the option to select a previous step. If you’ve read part one of this post you’ll know that you keep the pre-model scripts in a folder in the database solution.

The Alternative to Pre-Model Scripts

Hello!

Two of my most recent posts have concerned pre-model scripts; those scripts that need to be run before the dacpac is compared against the production database. These are sometimes necessary. And this necessity is usually because SSDT may not produce a script that is optimal. One such example is an index creation: no index is created “ONLINE”, and this can be a problem if the database being deployed is online during this operation. And can be even worse if the table is particularly large.

In respect to the SSDT team, I can see why this is the case: some versions of SQL have the online index feature, some don’t. So one solution may be to write a pre-model script that will create an index with the online operation included. And while there’s nothing wrong with this, there is an alternative: deployment contributors.

SSDT and Pre-Model Scripts

Hello! Earlier this week I posted about the need to create the database as a pre-model tasks: that is creating a database before we run sqlpackage/dacfx against a database/server so that the script itself does not create the database. Going a step further than this, pre-deploy scripts in SSDT scripts may be executed before the main diff script is executed, but not before the diff script is generated. This is an important fact to understand.

Dude, Where's My Gold Code?

Let me tell you about a conversation I had yesterday with some developers in a team. They needed help with their database CI/CD process, and so I wanted to see the database solutions they were using. Dev1: We don’t have solutions at the moment.Me: OK, how come?Dev1: Because we are making so many changes right now, there seemed to be little point in creating solutions, so we’re just using scripts.[At this point, I bite my tongue and choose not to reply with ‘If you’re continuously making changes, wouldn’t it make sense to continuously integrate and continuously deploy the changes.

Where Will SSDT Create my Database

SSDT is great for many things, but one thing it’s not great at is giving control over to the user with regards to data and log file creation. It uses prefixed values of DefaultDataPath and DefaultLogPath. These variables are system defined and cannot be overridden. Instead it uses the default locations that are set on the instance we are deploying to. Humph. And I’d wager that if these are null then it will just use where the system database settings are.

PowerShell Snippet: Trimming The String!

Hello! When building up urls from different parameters in something like TeamCity, or Octopus, it’s simple enough to get double “//” in urls if the parameters are not consistent. So little helper functions are always useful to have imported to manage such things. Below is an example of such a thing! function Update-Parameter { param ( $param, $character, [bool] $trimstart = $false, [bool] $trimend = $false ) if ($trimstart) { $StringChecker = $param.

SqlServer PowerShell Modules NuGet Package Now Available

Howdy. Back in the July Update of SSMS 2016, a bunch of new SQL PowerShell functions were added, plus two neat additions toInvoke-Sqlcmd: -outputas, which allowed you to output the result set into a data object (eg, data row, data table etc), and -ConnectionString, which allows you to pass in a connection string instead of using the pre-defined parameters. All very useful stuff, go and have a read. However, this update has two issues: firstly, it’s not updating the classic sqlps module, but rather has created a new module: sqlserver.

PowerShell Snippet: Verify Minimum Version of .NET

Occasionally it’s useful to verify the minimum version of .NET installed on a box. This PowerShell does exactly that. $Netver = (Get-ItemProperty ‘HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full’ -Name Release).Release write-host ".NET Version : $netver" ElseIf ($Netver -le 394271) { throw "At least .NET Framework 4.6.1 needs to be installed on this tentacle to use Microsoft.Data.Tools.Msbuild." }

PowerShell Snippet #7: Retrieve SQL Server Licence Key

Copied from somewhere else on the internet, this PowerShell script will return the product key used for a SQL instance Install. Super useful when changing licenses on temporary VM’s I spin up and play around with to SQL Developer whose instances have passed the Enterprise evaluation use-by date. Putting this here for my own benefit. I claim no kudos!  function GetSqlServerProductKey { ## function to retrieve the license key of a SQL 2014 Server.

PowerShell Snippet #6

Recently I needed to run Invoke-Sqlcmd that would use a connection string of a variety of parameters. Below is the script I used to accept whatever parameters would be passed in to the “Invoke-sqlcmd”. The input file contained the same text as the $query parameter.  $ServerInstance = ".\instance" $database = "Northgale" $uname = "user" $pword = "password123" $query = "'SELECT GETDATE() AS TimeOfQuery;'" $inputFile = "C:\Users\Richard\Documents\inputfile.sql" $connectionString = "-ServerInstance $ServerInstance -UserName $uname -Password $pword -Database $database -verbose" $connectionString = $connectionString -split '(?

VSTS Hosted Build Agent Specs

I was interested to know just what the hardware specifications of the hosted build agent is. So I added some PowerShell to read out the info below:  2016-06-29T09:23:31.3935358Z systemname Name DeviceID NumberOfCores NumberOfLogicalProcessors Addresswidth 2016-06-29T09:23:31.3935358Z ---------- ---- -------- ------------- ------------------------- ------------ 2016-06-29T09:23:31.3935358Z TASKAGENT5-0010 Intel(R) Xeon(R) CPU E5-2673 v3 @ 2.40GHz CPU0 2 2 64 2016-06-29T09:23:31.4095356Z Total memory: 7167.55078125 What piqued my interest greater was that this is the exact same spec for a D2 v2 box that is available via Azure.

Deploying SSDT Scripts (With My Good Eye Closed)

(If anyone actually gets the reference: kudos. If not, then it’s just that you’re not a Soundgarden fan)

So, there’s something that has been troubling me about deploying to databases with SSDT for some time. Actually, it’s not a SSDT-exclusive problem per se, it’s more of a database/release management process.

What it pertains to is that most people like to create the deployment script for review prior to deploying to production. This makes sense if you are new to the Continuous Deployment process, and that maybe sometimes SSDT creates scripts that are not quite expected. You may skip this because you feel rather more confident in your deployments, or the database sufficiently simple enough to just deploy without having to review. But generating the script as a deploy artefact is still a good idea, because auditing.

At any rate, the script is generated and maybe reviewed….. so then what? In SSDT there is no way to create and deploy script in one step; they are two distinct steps. And even if they were one step, this would still not resolve the issue that troubles me. So what is this issue?

The issue is that by creating a script, and then running the deploy, you cannot be sure that the database is in the exact same state that it was when the initial script was generated. If you don’t already know, SSDT runs a deploy entirely in memory, so as mentioned there is no script created. You have to explicitly create the script as part of the process. Or, if you have already created one, you have to re-create the script.

PowerShell Snippet#5: Running MSBuild

Script on running MSBuild via PowerShell. This ties up neatly with my previous post. $msbuild = "C:\Windows\Microsoft.Net\Framework\v4.0.30319\MSBuild.exe" $MsBuilExists = Test-Path $msbuild If ($MsBuilExists -ne $true) {write-host "msbuild does not exist at this location. Install Visual Studio 2015 (Community Edition should be adequate)"} $buildFile = $PSScriptRoot+"\BuildAllDBProjects.targets.xml" & $msbuild $buildFile

MSBuild Snippet#1 : Ignore Solutions

How do we ignore solutions in a build? We could specify only the ones we want to build, but depending on your weighting of include/exclude solutions, this could be costly. So here’s how to ignore solutions using MSBuild:  <Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Default"> <ItemGroup> <ProjectReference Include=".\**\AV2014.sln" Exclude=".\**\DBSolutionToIgnore.sln"/> </ItemGroup> <Target Name="Default"> <MSBuild Projects="@(ProjectReference)" Targets="Rebuild"/> </Target> </Project>

Turbo LogShip 1.0 Released

Earlier this year, I wrote a long post about log shipping. One of the key points I mentioned was how slow restoring can be for a read only log shipped database. If going and reading the whole thing is too much effort for you now, I’ll save you the effort (you child of the internet you) and tell you it’s because the database needs to be kept transactionally consistent in between restores when making it available for read-only. It creates a .tuf file (transaction undo file) to keep the progress of all the pages that have uncommitted transactions saved to them. The checking, creation, applying and re-applying of this can take some time where there is a significant amount of uncommitted transactions within a log backup.

PowerShell Snippet #4

Hello!  Below is a function that will search in a string and replace a character in a variable. Simple enough. It generally saves time in calling this from a helper file in source control for tidying up variables that are passed in to a Powershell function that aren’t quite formatted as required. A good example of this is upstream build variables with spaces in TeamCity. Function FormatReplaceVariable { [CmdletBinding()] param([parameter(Mandatory=$true)] [string]$param, [string]$find, [string]$substitute) Write-Host "Replacing characters $find with $substitute in $param" -Verbose $param = $param -replace "$find", "$substitute" Write-Host "Now parameter looks like $param" -Verbose return $param } $bob = FormatReplaceVariable -param "as'lfjksaj'JASD;oasdjf'asd aasd asd; ghas'fkasdf;aksd" -find ";" -substitute "!

PowerShell Snippet #3

Hello! Today’s snippet is a two-for-one type deal: connecting to SQL Server and running a query to get datetime. Really, it’s just useful for checking connectivity issues. $dataSource = “[serverInstance]” $database = “[database]” $connectionString = “Server=$dataSource;Integrated Security=True;Database=$database;” write-host $connectionString -Verbose $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $connection.Open() $query = “SELECT GETDATE() AS TimeOfQuery;” $command = $connection.CreateCommand() $command.CommandText = $query $result = $command.ExecuteReader() $table = new-object “System.Data.DataTable” $table.Load($result) $table Alternative to use InvokeSqlCmd:

SSAS Activity Monitor Available

So it’s been a while since I’ve worked on any SSAS, but today I am happy to announce that there is an up-to-date version of the SSAS Activity Monitor. The solution has had couple of changes to it.  

Deploy Cubes Using Powershell: The Script

Hello!

Recently someone got in touch with me about one of my earliest posts. I realised I had made a mistake on that post and had updated. But I also realised that the script itself is actually part of a PowerShell module. So wanting to make things as easy as possible, and being the helpful guy that I am, I have altered that example into a script which I am posting here.

It’s worth stressing that the errors that output to the console are fairly generic, and that there is a log file that will contain all the information if there are any errors.

Using TeamCity To Raise Octopus Defects Updated

Hello! I recently updated the script that uses both the TeamCity and Octopus API’s to raise defects if a TeamCity build has failed (the reasoning here is tat a deployment has passed on Octopus, but subsequent automated testing has failed.) This script uses functions and is a little neater than the previous version. You will have to create a snapshot dependency between the build that this step runs in and the build that deploys/runs the tests.

Free UK Visual Studio/ALM Events in 2016

Throughout the second half of this year, Microsoft are hosting three free events on the popular topics of Application Lifestyle Management & DevOps with Visual Studio. These are full day events, and yes to answer the most important question snacks and lunch is free! It’s not entirely clear if the event is going to include Visual Studio Team Services, however there is the promise of showing off new features. So presumably another update for Visual Studio 2015 is coming soon, or new features are going to be added to VSTS.

Free Microsoft eBooks

Yep, no sooner had Microsoft decided to lay-off their editorial staff do they decide to giveaway for free a considerable amount of eBook content. Obviously there is probably not a link between the two events, as clearly Microsoft have been giving away free eBooks for years, but it would be remiss for me to ignore Microsoft’s decision whilst distributing free content. The first link is a very good read and I encourage you to peruse.

Cumulative Updates - SQL Server 2016 CU1

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2016 Cumulative Update #1 for SQL Server 2016 RTM.

Into The [Void]

Recently I’ve been wondering how I can suppress the output in my PowerShell scripts when loading assemblies into them. I used to find them useful; but now I find them annoying and they are no substitute for error handling ( I used to find them handy as a way of telling me that the script had got thisfar in the script). There is more than one way to suppress output to the console, but for assembly loading, I prefer to use [void] because it looks neater than the alternatives:

Using TeamCity To Raise Octopus Defects

In my previous post, I shared a script that used the Octopus API to create a defect, with the aim of it being added to a TeamCity build and chaining the build to a deploy/test build step in TeamCity: the aim being to raise an Octopus defect if a test fails whilst the deployment to the environment succeeded. You can read more about it here.

What makes this a challenge is that there is no way to have a chained build that runs if, and only if, a build has failed. So as with Octopus you have to use the TeamCity API. In this script I get the the status of the last build that deployed/ran the tests, and if this build succeeded I do nothing. So yes this chained build has to always run post deploy/test phase.

Where it gets interesting though is if the build failed. Here we raise a defect, but not before checking to see if there are any defects raised that are still unresolved, as only one Octopus defect can be unresolved at any one time.

SQL Server 2016 Sample Databases Available

SQL Server 2016 is barely 6 weeks old, and so but the AdventureWorks sample databases have yet to be released. However, one thing that is available is the CTP3 databases from back in December 2015. The sample scripts cover many of the new features, including JSON objects, Row Level Security and StretchDB, amongst others. But Microsoft have released a new sample database called WorldWideImportersavailable on GitHub. There’s bacpacs for SQL Azure, an Ispac for SSIS, and baks for SQL Server 2016.

July Musings

As we’re all aware, SQL Server will one day be available on Linux. But when? And is it soon? And what features will be available? If you’re looking for answers now…. you’re out of luck, other than the fact that it will be built on SQL Server 2016 and released sometime in 2017. But what you can do is sign up for an early preview of SQL Server on Linux. I’ve already signed up myself.

SQL Server 2014 Service Pack 2

Today Microsoft have released Service Pack 2 for SQL Server 2014. In addition o some new fixes, SQL Server 2014 SP2 includes hotfixes that were included in SQL Server 2014 SP1 CU1to SQL Server 2014 SP1 CU7. So this is a new branch of releases, and we will see cumulative updates separately for each of the branches of SQL Server 2014 (RTM, SP1 and SP2). In addition to the CU fixes, There are 2 noticeable improvements to the release:

Azure KeyVault and Extensible Key Management

I’ve been reading up on Azure KeyVault, and the more I read about it the more I like it. There is a Technet blog that posts news and other pieces of information. There’s even a forum for feedback and discussions. Utilising Azure KeyVault in SQL Server means that for Extensible Key Managementyou can now replace the need for a certificate stored in master with a key stored in the Azure Key Vault.

30 Days of Testing Challenge

Though the days of being a tester are long behind me, testing is still very much an important part of my role. Working as a tester has given me a perspective on getting things done that other people who haven’t worked as a tester might not appreciate, and it is a skill I’d like to keep up. With that in mind, I am going to take part in the 30 Days Of Testingchallenge.

PowerShell Snippet #2

Hello! Today’s PowerShell snippet is getting the MyDocumentsfolder path of the current user: $myDocuments = [Environment]::GetFolderPath("MyDocuments") Write-Host $myDocuments -ForegroundColor Yellow -BackgroundColor Magenta }

Get Stored Procedure Last Run Time

Hello! Quick script to get the last time a stored procedure was executed in the database. The reason for the seemingly over-engineered script is that different query plans can be generated, meaning that stored procedures can appear more than once in the list. SELECT OBJECT_NAME(qt.objectid) AS [StoredProcedure] ,qs.execution_count AS [Execution Count] ,qs.creation_time AS [Age in Cache] FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt INNER JOIN ( SELECT OBJECT_NAME(qt.objectid) AS [sproc] ,qs.

Cumulative Updates - SQL Server 2014 SP1 CU7

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2014 Cumulative Update #7 for SQL Server 2014 SP1.

Viewing Undistributed Command in Replication

It’s been very nearly a year since I have worked or indeed posted a blog about replication. But today I was sat in front of the Replication Monitor with some failing publications. Despite my years worth of activity in between, I picked things back up pretty quick. I needed to see the command that was failing, so that meant using sp_browsereplcmds. And I put together a script that will help me to do this. Generally the transaction sequence number can be picked up from the error message in the details window for the subscriptions. Mercifully the replication topology is quite small, so I am able not use the “@xact_seqno_END” parameter. Your mileage may vary.

PowerShell Snippet #1

Hello! Often I write a small piece of PowerShell that I will create and then lose and forget about before needing it again sometime in the future. So to that end I’ve decided to start sharing here for myself as much as anyone else. Today’s snippet is iterating over a DateTime and adding a time span to the base DateTime. $iterate = 24 [datetime]$baseDateTime = "2016-06-09 10:00:00.000" for($i=0 $i -le $iterate $i++) { $ts = New-TimeSpan -Hours 1 $baseDateTime = $baseDateTime + $ts $baseDateTime }

Writing Output from MSTest

Unit testing can sometimes throw up unexpected issues that are entirely concerned with the unit tests and not the application under test. Recently I’ve been having issues with DateTime formats affecting the results of the unit tests: the format of the DateTime was different on the build server than it was on my local machine. The stored procedure could not be altered (because reasons*).

Import-Module SqlPs Takes Forever To Load

Hello! I’ve been working on some pre-deployment scripts to SQL Server using PowerShell, and I chose to use Invoke-Sqlcmd as opposed to creating a sql connection*, however the first time I ran the script on my local box to test, it seemed to hang. I thought it may be my local instance not running, but that was running fine. So I created the simplest command possible and tried it: Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance ".

Preventing Plan Regressions During Testing Slides

Hello!

SSISMSBuild Updated and Moved

You will need client tools installed as part of your SQL Server installation or the dlls won’t be on your box. Either way just search for those dll names in “C:\Program Files (x86)\Microsoft SQL Server" and they will probably be found.  

Enabling Log Files for TFS Builds

It’s nota member of the build service accounts. In fact, no one was; 

RECOVERY vs NORECOVERY vs STANDBY

When restoring a database you can specify one of three recovery options; RECOVERY, NORECOVERY and STANDBY. Today I’m going to touch briefly on the RECOVERY and NORECOVERY options before delving deeper into the STANDBY mode as it has a few features that separate it from the either two options that are worth explaining.

Connect VSTS Package Management to Octopus

Pruning Back the Branches

Sup Dawg, I Heard You Like NULLS...

Quick note on NULLS: NULL means “Unknown Value”, not “0”. So adding a value to NULL results in NULL: 1 + NULL = NULL Because NULL means “I Don’t Know”, adding two NULLS together does not equate to the same value; it’s still two indeterminate values. NULL + NULL != NULL You can work around this in T-SQL by using COALESCE. declare @i VARCHAR(8) = NULL declare @j VARCHAR(8) = NULL SELECT @i SELECT @i + 'bob' SELECT COALESCE (@i, 'bob') if @i = @j select 'yes' else select 'no'

Returning Values From Query Plans Using C#

Easter Musings 2016

I’m going a bit meta in this blog post; I’m talking about blogs and blogging.

How To Compile SQLProj Files Using Cmdline MSBuild... Errors Included!

<Project ToolsVersion="4.0"
xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
DefaultTargets="Default">

<ItemGroup>
<ProjectReference Include=".\**\*.sln" Exclude=".\**\DBSolutionToIgnore.sln"/>
</ItemGroup>

<Target Name="Default">
<MSBuild Projects="@(ProjectReference)" Targets="Rebuild"/>
</Target>

</Project>

Specifying Multiple Properties in SQLPackage

The MSDN page for SQLPackage is great for all the information you could possibly need to use SQLPackage via command line. And there are a lot of options, especially with the Properties: although these can be set in a publish.xml file, you can override whatever is specified within the publish file by specifyingthe property name and setting the value. This is especially useful when you don’t want to have many publish.

PowerShell Cmdlet Quickie: New-TimeSpan

As is the case with most things, when I find a way for getting something done in a script that is “good enough”, I’ll tend to stick with that method until that method no longer becomes fit for purpose. One such method is printing out the time that something took in PowerShell: many of the scripts on my site use this method to get the duration of a task, and I’ve been using this since PowerShell 1.

Restore Database Using PowerShell

$SQLConn = New-Object System.Data.SQLClient.SQLConnection $SQLConn.ConnectionString = "Server=$SQLServer; Trusted_Connection=True" try { $SQLConn.Open() write-host "success" -ForegroundColor Green $SqlConn.Close() } catch { Write-warning "An exception was caught while attempting to open the SQL connection" Break } $SQLConn.Open() $Command = New-Object System.Data.SQLClient.SQLCommand $Command = $SQlconn.CreateCommand() $Command.CommandTimeout =0 $Command.CommandText = "select top 1 physical_name from master.sys.master_files where database_id = 1" try{ $Reader = $Command.ExecuteReader() while ($Reader.Read()) { $r = $Reader.GetValue($1) $r = $r.ToString() $r = $r.

Cumulative Updates - SQL Server 2014 SP1 CU5

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2014 Cumulative Update #5 for SQL Server 2014 SP1.

Running Clean Builds On Visual Studio Team Services

Happy Mardi Gras

Happy Mardi Gras! My wife and I met on Mardi Gras, and we had our honeymoon in New Orleans, some years back. In fact it’s ten years today since I met my wife. So it is a holiday we love to celebrate. May your slice of king cake contain a trinket, because it’s good to be the king!

SQL Server Database File Size and Free Space Revisited

I recently had a comment posted to one of my first articles I wrote way back in 2012 concerning adding a column that detailed the used space as a %age. So without any further ado, here is an updated version of a script originally blogged about here. select sf.FILEID AS [File ID], [File Size in MB] = convert(decimal(12,2),round(sf.size/128.000,2)), [Max Size in MB] = convert(decimal(12,2),round(sf.maxsize/128.000,2)), [Space Used in MB] = convert(decimal(12,2),round(fileproperty(sf.name,'SpaceUsed')/128.000,2)), [Free Space in MB] = convert(decimal(12,2),round((sf.

Enterprise Schema Big-Ish Data Scripts

Hello! Last week I presented for the first time at a SQL User Group (SQL Supper.) There was a lot of content, and plenty of demos, and a few people came up to me afterwards and asked for the scripts. In case you were too shy to ask, or just want a simple database that uses partitioning and Clustered Columnstore Indexes, the scripts attached will provide you with such a thing.

Forcing SQL Server To Order Operations

The subject of my last post was to do with returning DBCC CHECKDB results from a temp table, which was populated by running DBCC DBINFO against each database hosted on the SQL instance. I shared a query that returned any databases that had not had a CHECKDB run against it for over 7 days. The query is below:

 
select dbi.Value from #db dbi
WHERE field LIKE '%lastknow%'
AND (CASE WHEN isDate(dbi.value) = 1 THEN CAST (dbi.Value AS DATETIME2(3))END) < DATEADD (DAY,-7, GETDATE())

That “CASE” statement stands out quite a bit doesn’t it. Let’s explore why this query needs a CASE expression. Begin by creating a temp table that contains a sample of the data stored in the temp table.

Copy and Delete Data Across Tables When There is no Space

Hello!

Recently I needed to apply compression data on a particularly large table. One of the main reasons for applying compression was because the database was extremely low on space, in both the data and the log files. To make matters worse, the data and log files were nowhere near big enough to accommodate compressing the entire table in one go. If the able was partitioned then I could have done one partition at a time and all my problems would go away. No such luck.

So I had to think quite outside of the box to compressing the data. The solution I came up with was to create a new empty table, and copy 50 rows at a time (that was the largest I could move at any one time, yes I really was THAT low on space) and then delete the 50 rows copied. But instead of COPY/DELETE, which would seem the logical way, I ran the DELETE first and then run an INSERT from the output table. This way I would be certain that the same rows that were deleted were copied into the new table, and meant that I was only scanning the original table the once.

I had to run further checks into the script: the database was set to simple mode, but the log file would still grow because of ETL processes etc running on other parts of the db, and so I needed to check to make sure that I wasn’t saturating the log file with each delete. So the logic here will work for both SIMPLE, BULK and FULL recovery models.

Cumulative Updates - SQL Server 2014 SP1 CU4

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2014 Cumulative Update #4 for SQL Server 2014 SP1.

Christmas Musings

Log Shipping: It's Better Than Bad It's Good!

Useful Log Shipping Script

SELECT ls.primary_server AS [primaryServer] ,ls.primary_database AS [primaryDB] ,lsd.secondary_database AS [secondaryDB] ,lsd.restore_delay AS [restoreDelay] ,CONVERT(VARCHAR, DATEDIFF(SECOND, lms.last_restored_date, GETDATE()) / (60 * 60 * 24)) + '_' + CONVERT(VARCHAR, DATEADD(s, DATEDIFF(SECOND, lms.last_restored_date, GETDATE()), CONVERT(DATETIME2, '0001-01-01')), 108) AS [timeSinceLastRestore dd_hh:mm:ss] ,CONVERT (VARCHAR (20), lms.last_copied_date, 120) AS [lastCopiedDate] ,CONVERT (VARCHAR (20), lms.last_restored_date, 120) AS [lastRestoredDate] ,lms.last_copied_file AS [lastCopiedFile] ,lms.last_restored_file AS [lastRestoredFile] ,lsd.disconnect_users AS [disconnectUsers] ,ls.backup_source_directory AS [backupSourceDirectory] ,ls.backup_destination_directory AS [backupDestinationDirectory] ,ls.monitor_server AS [monitorServer] FROM msdb.

Moving TempDB Script

SET NOCOUNT ON DECLARE @SQL NVARCHAR(MAX) = '' DECLARE @EXEC NVARCHAR(MAX) = '' DECLARE @RUN BIT = 0 DECLARE @newdatalocation NVARCHAR(256) = ‘L:\temptempdb' DECLARE @newLogLocation NVARCHAR(256) = ‘M:\tempdb_log’ DECLARE @fileName NVARCHAR(64) DECLARE @fileLocation NVARCHAR(256) DECLARE cur CURSOR FOR SELECT f.NAME ,f.filename FROM sys.sysfiles f OPEN cur FETCH NEXT FROM cur INTO @fileName ,@fileLocation PRINT @fileName PRINT @fileLocation WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = ‘ALTER DATABASE ' + DB_NAME() + ' modify FILE ( NAME = ' + f.

Find All Compressed Tables on SQL Server Database

Super quick script to find all objects that are compressed. This script is grouped by table and compression type as partitioned tables may have different types of compression enabled per partition. If we do not include the “group by"then all partitions will be listed regardless of whether the compression type is different or not, and that looks really messy. SELECT SCHEMA_NAME(sys.objects.schema_id) AS [Schema] ,OBJECT_NAME(sys.objects.object_id) AS [Object] ,[data_compression_desc] AS [COmpressionType] FROM sys.

Get Average Backup Growth and Time Taken

SELECT [database_name] AS [Database] ,[backup_start_date] AS [Date] ,AVG([backup_size] / 1024 / 1024) AS [BackupSize_MB] ,AVG([backup_size] / 1024 / 1024) OVER ( PARTITION BY DATEPART(mm, [backup_start_date]) ORDER BY DATEPART(mm, [backup_start_date]) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS [movingAvg] ,AVG([backup_size] / 1024 / 1024) OVER ( PARTITION BY DATEPART(mm, [backup_start_date]) ORDER BY DATEPART(mm, [backup_start_date]) RANGE UNBOUNDED PRECEDING ) AS [Avg] ,CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, backup_start_date, backup_finish_date), 0), 108) AS TimeTaken FROM msdb.

Check on Long Running SQL Agent Job

Although SQL Agent Jobs can be configured to alert users when a job has passed on failed, there is no mechanism to alert users if the job has been running a long time. So I wrote a stored procedure that can be executed using a SQL Agent Job (ironically.) I’ve written it so that you can check individual jobs as opposed to all jobs on a server, as undoubtedly there are some job that I’m not too bothered about if they run longer than others (e.g. log backup durations generally alter depending on the load of the server.)

SQL Agent Jobs by Average Run Duration

 

Average Backups and Their Growth

Hello! (There is an updated version of this script herethat includes duration of the backup) Departing from the scripts I have been sharing all week about the SQL Agent job, the script below gives you the day average of your backup and both the average and the rolling average of the size of the database backups throughout the month. This script will only work on 2012 and later. If you try it on 2008 R2 and earlier you get a syntax error I’ve not seen before: “The Parallel Data Warehouse (PDW) features are not enabled.

SQL Agent Jobs by Average Run Duration and Number of Runs

This is the third post this week on querying the SQL Agent Job tables to return data not included in the GUI. SQL Server Agent has a lot of great information stored away in the system tables, and though the GUI provides some interesting views of the data, it is necessary to roll your sleeves up and get querying the tables yourself. All pertinent tables are in the dboschema in the msdbdatabase, so querying for the data quite straightforward.

SQL Agent Jobs by Average Run Duration and Date

Following on from yesterdays post, this is the second query that covers the SQL Agents job duration. SQL Server Agent has a lot of great information stored away in the system tables, and though the GUI provides some interesting views of the data, it is necessary to roll your sleeves up and get querying the tables yourself. All pertinent tables are in the dboschema in the msdbdatabase, so querying for the data quite straightforward.

SQL Agent Jobs by Average Run Duration

SQL Server Agent has a lot of great information stored away in the system tables, and though the GUI provides some interesting views of the data, it is necessary to roll your sleeves up and get querying the tables yourself. All pertinent tables are in the dboschema in the msdbdatabase, so querying for the data quite straightforward. This week I am posting a query a day that I have written to get the average duration of the jobs run.

Database Mail Exception 5.7.1

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-08-11T17:20:13). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for [email protected]). ) <p style="text-align:justify;">Although it looks like that there is an issue with the account that we're trying to send the message to, this is not actually the case: The Exchange Server requires a new Receiver Connector to be created so that the Exchange server can relay messages from the server.

Cumulative Updates - SQL Server 2014 SP1 CU3

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2014 Cumulative Update #3 for SQL Server 2014 SP1.

Custom SQL Monitor Metrics

Hello! I’ve been working quite a bit lately with the RedGate SQL Monitortool, and the more I work with it the more I like it. The analysis tab is very useful for monitoring any changes made and what impact they have on the system in total. But one of the things I really like about SQL Monitor is the ability to create custom metrics. Although there’s something like 33 alerts you can opt in/out of for monitoring, the ability to create your own really adds value in that you don’t have to go down the route of creating SQL Agent jobs to run on each server and to have to email you when an alert could be raised via SQL Monitor.

Install .Net using PowerShell

I was installing SQL Server 2014 on an Azure server the other day when I remembered that I had not installed the .NET Framework 3.5. Going through the UI is always a pain, so I wondered if it is possible to install using PowerShell… well, I knew it would be, I just wondered how straight forward it would be. Turns out it’s very straightforward! Install-WindowsFeature -name NET-Framework-Core It’s also possible to check the whether .

3 Things About Missing Index Hints In Query Plans You Need To Know

Disclaimer: I’m not saying that these three things are the only things you need to know about missing index hints in Execution Plans, I’m sure whole chapters in SQL Books can be dedicated to the topic! And I’m not advocating the use of creating new indexes indiscriminately to a database.

Hello!

Missing index hints in query plans are a potentially quick and fantastic way of speeding up a slow running query. However, there are a few things that are vital before adding a new index recommended via the Missing Index Hint. Let’s setup a test database to give some working examples.

CREATE DATABASE [IX]
GO
ALTER DATABASE [IX] SET RECOVERY SIMPLE
GO
USE IX
GO
CREATE TABLE Me_Heap
(
Me_Heap_ID BIGINT IDENTITY (1,1),
FK_Id_BIG INT,
DateChange DATETIME2 DEFAULT ( CURRENT_TIMESTAMP )NOT NULL ,
DataCol NCHAR (8) DEFAULT ( 'IX' )
)
GO
BEGIN TRANSACTION
DECLARE @i INT = 1
WHILE @i <= 30000
BEGIN
INSERT INTO Me_Heap
( FK_Id_BIG )
VALUES ( @i % 10 )
SET @i = @i + 1
END
COMMIT TRANSACTION
CHECKPOINT
GO 40 

CREATE TABLE Me_Heap_2
(
Me_Heap_2_ID INT IDENTITY (1,1),
A_N_Other_Val INT,
DateChange DATETIME2 DEFAULT ( CURRENT_TIMESTAMP )NOT NULL ,
DataCol NCHAR (8) DEFAULT ( 'IX' )
)

BEGIN TRANSACTION
DECLARE @i INT = 1
WHILE @i <= 30
BEGIN
INSERT INTO Me_Heap_2
( A_N_Other_Val )
VALUES ( @i % 10 )
SET @i = @i + 1
END
COMMIT TRANSACTION
CHECKPOINT
GO

You will notice that there are no indexes, and this is intentional.

T-SQL To Create Database Mail

I was wondering if there was a way to script out Database Mail so that the configuration was identical across multiple SQL Server instances. Unlike most other objects in SQL, there’s no obvious way to do this (typically I expect to see the option through the SSMS UI when right clicking on an object).

Detailed SQL Server Partition Script

I’ve been working with data warehouses again recently and needed to help out the rest of the team who were not familiar with partitioning in SQL Server. It’s one of those subjects that really requires hands on experience with to truly appreciate the mechanics of partitioning, and for me it especially helps if I can visualize a concept.

I’ve always thought that the best way to explain partitioning as imagining that any one table in SQL Server has a partition count of exactly one. When we actually partition the table on a range value on a column then we introduce multiple copies of that table, and that it is the meta data that is replicated for the table. And it is from this copying of the table behind the scenes that helps with archiving and query performance. This is probably not far off from how partitioning actually works.

The Subtleties of COPY_ONLY Backups

Recently I have been looking at the COPY_ONLY feature for taking backups of a database. COPY_ONLY was introduced in SQL Server 2005, and allows you to take ad-hoc backups of either the log or a full backup without breaking the backup chain for differential backups. The COPY_ONLY backup is not part of the restore log: so when restoring from a full backup you can ignore it. The same here can also be said for log COPY_ONLY backups; it’s an ad-hoc backup that does not alter the transaction log at all; there’s no clearing down of the log.

The 5 Do's and 10,000 Don'ts of CV Writing

Disclaimer: there isn’t really 10,000 don’ts, in fact there is only one. But it is pretty darn important. I’ve written and read enough CV’s to have a pretty good idea what constitutes a good CV and what constitutes good bin liner and so I thought I’d share my thoughts. Let’s start with the Do’s:

Do’s

Get Run Duration of a SQL Agent Job

The MSDB system database holds all the information for any SQL Agent jobs set up on the server. But it is not straightforward to query the system tables to get information like the duration of jobs out of the tables in the correct format. With that in mind, here’s a little script that will return the total duration of the SQL Agent jobs. There are a couple of “WHERE” statements you can include or exclude if you want to include a specific job, or if you want to include all the steps of the jobs.

Cumulative Updates - SQL Server 2014 SP1 CU2

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2014 Cumulative Update #2 for SQL Server 2014 SP1.

Get Last Backup Date of Database

I’ve been working hard on my backup/restore skills lately, getting to grips with the fundamentals and making sure that I understand backup/restore best practices and running examples to make sure I’m ready in the event of a disaster. It’s a cliche to say that you don’t want to be doing this in a real life disaster when everyone is huddled around your desk, but it’s true! Tasks like tail log backups, a type of backup that backs up the log file even if it is offline or damaged, is something that you may need to run in some scenarios.

Get Database Restore Progress

A couple of weeks back I posted a scriptthat checks the progress of any backups running on and instance. A few days later I needed a script to check the progress of a restore. Fortunately enough, the backup script can be re-used with a very simple change. This simple script to check if any restores are running, how long they have been running and what the anticipated complete time will be.

SQL Server 2016 CTP 2.2 Build Has been Replaced

I received an email from the TechNet evaluation Centre this morning, stating that the CTP for SQL Server 2016 (v2.2) has been replaced as of yesterday. Apparently the previous release caused downtime with AlwaysOn Availability Groups during rolling upgrades. Though I have SQL Server 2016 installed on an Azure box, I’ve not played around with AlwaysOn in 2016. An upgrade is recommended for any users who wish to upgrade to SQL Server 2016 CTP 2.

MSBuildSsis2012 Now Available (...or Rather SSIS 2012 Deployment Fail Revisited...Again)

Edit: MSSISBuild has been updated and moved to GitHub. Read about it here. The rest of this post is still relevant, it’s just where you download the code form that has changed. this has proven to be a popular post, and the software has been downloaded a few times. I will post an update for SQL Server 2016 soon, though I think it is just references in the solution that needs updating.

Today I am pleased to announce the release of MSBuildSsis2012 on Codeplex. But before i get into what it does first, a bit of background:

Back in May, and subsequently a few more times since, I’ve posted about an error I get occasionally in one of our custom tasks that run in our builds. This custom task is a special case: As the SSIS 2012 project extension is dtproj, it cannot be compile using MSBuild. The typical solution would be to use DevEnv In MSBuild. And to automate deployment the solution (that would work for most people) is to use the SSIS Deployment Wizard in silent mode. However, we don’t use the SSIS Deployment Wizard in our automated builds as it does not work in Silent Mode when you have assembly references in the SSIS packages: it destroys those references and the dtsx packages fail whenever they run.

Get Database Backup Progress

Hello! If you have backups running through jobs you may want to know the progress of the backup and where the backup media is. Script below returns this information for you. Happy scripting! SELECT A.NAME ,B.TOTAL_ELAPSED_TIME / 60000 AS [Running Time] ,B.ESTIMATED_COMPLETION_TIME / 60000 AS [Remaining] ,B.PERCENT_COMPLETE AS [%] ,( SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE) ) AS COMMAND FROM MASTER..SYSDATABASES A ,sys.dm_exec_requests B WHERE A.DBID = B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%' ORDER BY percent_complete DESC ,B.

Altering SQL Job Status

Hello! As an environment grows bigger and you have many jobs across many server instances, it’s important to automate as much as you can. And starting with the small, tedious but time consuming jobs is never a bad idea! A good example is having to enable/disable SQL Agent jobs during maintenance, or when a snapshot for replication is being pushed out you may not want certain jobs to run to prevent failures.

Access Denied When Initializing Subscriber (OS 5)

Today I needed to initialize a pull subscription from a server that had been in the pool of subscribers for a published database, and had been out for sometime. In that time the distribution database had moved, so it was a case of re-creating the subscriptions all over. Once the pull subscription and distribution jobs created, I ran the snapshot agent, which uses a file share. When the snapshot agent had completed, I stared up the agent job on the subscriber.

Collecting Baselines for High Undistributed Commands

In my previous post I shared some SQL scripts which would give you the number of undistributed commands by publication, by publication and article, and by distribution database. So you can measure undistributed commands at a very high granularity (articles), or at a very low granularity (the entire distribution database.) How granular you go depends entirely on where your pain is: you could of course filter out to only a few articles, or a particular publication.

Queries to Help Baseline Undistributed Transactions In Distribution Database

When it comes to monitoring replication, using the Replication Monitor.exe gives you a good view of what is happening now. But unless you have someone watching Replication Monitor 24/7, it’s generally not a good idea to rely on it. In fact, its best to avoid running Replication Monitor too often. Instead, you can use the information stored in your distribution database to get information such as agents hitting latency thresholds and a raise in undistributed transactions. Using the information stored in the distribution database ensures that monitoring is centralised. (I’m aware that there is a built in check for replication exceeding a latency threshold, however I have seen a high number of undistributed commands where in fact there were none, and false positives can be frustrating.)

FizzBuzz In SQL

This has been done many times before by many other people, but I set this challenge to a friend of mine who was interested in programming, and then realised that I had no solution. So I spent the train journey home writing my effort below. Like a lot of things in computer programming, there’s more than one correct way, but many more incorrect ways! I like this way because it’s quite small and readable.

Checking for High Identity Counts Across A Database

Recently we had an issue with some 3rd part databases that had hit the max INT on some of the identity columns. Potentially the fix for this is easy enough, but you have to be able to anticipate how the application will deal with resetting the identity value back down.

Check For Non Running Distribution Agents

Today I am going to share with you a SQL Script that can help monitor the status on your distribution agents in a snapshot/transactional replication topology.

Despite their being some built-in monitoring for replication agents in the Replication Monitor, one that seems to be missing is checking for the agents updating the distributor. Sure there’s ones for agents shutting down for whatever reason, but sometimes latency can be reported as “excellent” for a subscriber despite not having updated the distributor for some time. And because it has not updated the distributor, there’s no calcs for transactional latency, which means that whilst on the surface everything is fine, you are slowly heading towards a real headache with replication.

Insert or Update Approaches in SQL Server

Recently I needed to write a stored procedure that would update a row if it existed in a queue table, and if it did not then to insert the row. Thinking from a pseudo code thought process, you’d run a “IF EXISTS (SELECT Id FROM TABLE) UPDATE, ELSE INSERT INTO TABLE..” Whilst this approach does work, it would be inefficient. This will do a table/index scan twice: once for the SELECT statement and the UPDATE statement.

Depending on your version of SQL Server, there are more efficient ways to do this:

Finding Locks on Tables

Recently I needed to find a stored procedure that was creating a lock on a table and updating the table regularly. So my starting point was the table name, but I needed to find where the commad was coming from that was locking the table, and what stored procedure was doing the update, if any. As I could not be sure that it was a sproc, I wanted to make sure that I also got the program name.

SQL Server Replication: Finding Duplicate Articles in Publications

I’ve been working with SQL Server Replication recently, and there are plenty of resources available online to help you get started, including

Denying Access To TempDB

So before we get into this I suppose the question is “why would you even want to do this?!” I’m not advocating this idea, I’m just saying it is possible, and I suppose it prevents users from creating temp tables in tempDB and writing to disk from other databases, risking killing the server. This is just an academic exercise: A developer in the training class I recently attended actually has had their permissions to tempDB removed and so I wanted to check how to do this. Turns out it’s not that hard. See the script below for comments.

Cumulative Updates - SQL Server 2014 RTM CU7

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2014 Cumulative Update #7.

Easter Musings

Hello! Easter is here already; we’re 1/4 of the way through 2015, the clocks have gone forward, and for the first time in a long time it was warm enough for me to venture outside without a hat.

Clustered Columnstore Updates

Recently the SQLCAT team published a post on their blog titled Data Loading performance considerations with Clustered Columnstore indexes. In it they talk of loading data into partitioned tables where the load is aligned. Interestingly, their recommended process is to load into a staging table with the clustered columnstore index created first.

This is still based on the fact that you can hit the magic number of 1,048,576, or at the very least 102,400, per bulk insert, to create compressed segments. If you cannot, then I still feel that the method of loading into a heap, and then creating the clustered columnstore index, is preferential. This is because if you trickle insert into a CCI, you will end up with a mixture of open/closed delta stores and come compressed segments. You could leave it to the tuple mover to compress these, and you would still be left with some open delta stores. However, as Remus Rusanu points out, this is not recommended. To complete the process quickly, and to remove any open delta stores, you would need to rebuild the partition.

Defaulting Data Compression on Creating Indexes

Here’s a theoretical situation;

Cumulative Updates - SQL Server 2014 RTM CU6

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2014 Cumulative Update #6.

Tidying Up Ugly Tab Titles in SSMS

Ever get tired of the ugly looking tabs at the top of SSMS, full of impertinent information? I was recently told of a method to tidy up the info displayed on the tabs, making them more legible: Go to Tools > Options Then navigate to Text Editor > Editor Tab and Status Bar Those options underneath “Tab Text” determine what is displayed on the tab. Here you can set them to be on or off.

Microsoft's Lousy Responses To More Connect Issues

I was catching up on some blogs yesterday, and one I really liked was this post on Spaghetti DBA. The authors main complaint was that he had grown disillusioned with Connect and the way in which so many issues are dealt with (ie, closed as “Won’t” Fix"). All the author wanted was better feedback from the engineers when responding to issues. I’m adding my voice to this request because in the past year I have raised a couple of issues myself, only to be disappointed with the feedback:

New Year Musings

Hello! And Happy New Year!

I’ve spent much of the past month off work looking after my daughter whilst my wife is at work. It seems that while this time of year is quiet for me at the office, in the NHS its the busiest period of the year. So it has been great to spend time with Phoebe at home, which has resembled a building site since the end of October. Indeed, as I work from home I have had to move the computer from into 5 different times whilst work was completed. During that time I’ve learnt more things about plumbing than I’ve ever wanted to know, and surprised myself when I kept a remarkably cool head when I noticed water leaking out the ceiling (from the room I had just removed the radiator from successfully (I thought) and whose pipes I had capped) into our living room. And here is some advice which is as unrelated to technology as you’ll ever read on this site, but invaluable nonetheless: try not to reuse caps to cap off radiator pipes, as you have to turn them so tight they tend to break up when you try to use them again. Which is exactly what I had done. I thought they were screwed on well enough until I turned the heating on and water got flowing around the system, which was when the water started to leak out of the busted cap. Fortunately for me no damage was done and I was able to drain the entire heating system, which unfortunately coincided with us living without heating during the coldest days of 2014, until the plastering was done. It’s all part of us paying our dues until the house is done. Currently we are without a shower/bath, though mercifully we are not far away from friends who are kind enough to let us use their bathroom.

What I Talk About When I Talk About Blogging

This post has nearly been a year in the making. When I hit the 100 post mark, which was roughly a year into writing this blog, I wanted to share some of my thoughts about blogging and what it meant to me and how someone can start up a blog and still be actively posting a year later. But I decided not to, as I felt a year and 100 posts was not nearly enough time to post anything with any real authority. But 1 year and 100 posts later I still wanted to share my thoughts on blogging. If for nothing else, it’ll be interesting to read this post in 2/3/4 years time and see just how much of what I wrote I still agree with. This post is not definitive; rather, it’s like viewing a junk shop; I’m sure there’ll be something for someone to take home from this collection of thoughts.

Processing Cubes After a Data Refresh: Batching Processing Using AMO

Recently I needed to update some cubes in a test environment after the underlying data warehouse had been refreshed. Typically I’d sync the cubes from prod, but as sync causes locking, and I needed to get this done during the day, I needed to reprocess all of the cubes in the test environment, and there were a lot of them, all ranging in sizes from hundreds of gb to a few mb. The simplest way to update the cubes was to run a process default on the dimensions and then run a process default on the measure groups. Some of the measure groups were partitioned, so I could’ve got clever and just processed fully any unprocessed partitions, but I felt a default would do most of the hard work for me.

Don't Be A Jerk, Use Approved Verbs in Powershell

Today I’m going on a mini rant about people abusing Powershell, specifically to do with naming Powershell functions. If you’re going to take the trouble to write Powershell functions and add them to a module for others to have access to, please, don’t be a jerk, use a verb from the approved verbs list that is freely and readily available on the MSDN Developers site, or even by typing “get-verb” in the console.

Update to vw_all_temp_db_usage View

Last month I posted a view that looked at TempDB usage. I use it primarily as a monitor for TempDB usage on instances that have databases that have Read Committed Snapshot isolation enabled. Recently I made a change to the view; it includes the snapshot isolation level of the session. If it is using snapshots then would expect TempDB to be greater for this session/task. if exists (select * from sys.

Automate SSAS Syncing Part 5: Syncing from Start to Finish

Hello! And welcome to the final part of this series. This script combines the first 4 parts of this series to create an unabridged process of synchronising a ssas database from one server to another. Even though I’ve used this script fora while, it still gets tweaks and I’d still consider it a work in progress, however it’s as complete now as it ever has been. No doubt when I make some more changes I’ll post an update.

The script so far includes:

Automate SSAS Syncing Part 4: Synchronise Databases Across Servers

Hello, and welcome to part 4 of the series. In this post, I’m going to do a straight up sync with none of the disk checks from yesterdays post. The full script at the end of the series will include the disk checks.

Automate SSAS Syncing Part 3: Using PowerShell to Check for Disk Space on SSAS Instances

Hello, and welcome to part 3 of the series on syncing databases across servers. The first two parts focused on ensuring that the role exists on the source server with the appropriate permissions to sync. We then check and add the service account of the target instance to ensure that the sync can occur.

Automate SSAS Syncing Part 2: Create SSAS Role And Add Member

Part two of this series expands upon yesterdays post, which was creating a database role and assigning permissions, by adding a member to that role. The script adds just one member to the role, but if you need more than one member you can either create a domain group and add all the users required into that group, then use the group name in place of the user name, or do something clever with arrays.

Tomorrow’s post will look at some disk checks that are included to ensure there is enough space to sync cubes.

Here are the links for the other parts of the series Automate SSAS Syncing Part 1: Create SSAS Database Role Automate SSAS Syncing Part 2: Create SSAS Role And Add Member

Automate SSAS Syncing Part 1: Create SSAS Database Role

Hello!

Every day this week I am going to be posting part of a script that I recently wrote, but broken down into separate components that are useful in their own right. The full script, which I will be sharing at the end of the week, automates the synchronisation of SSAS cubes from one instance to another.

So to make this an automated process that was robust to any deviation I had to make sure that the destination service account has full admin access to the source database, that the destination disk could stand the anticipated growth as well as the copy of the sync’d cube, and that the datasource’s connection string on the destination database was reset to it’s original value. Typically the datasource does not need updating, but the destination databases are used in a different environment with their own datasource. And as the sync also syncs the datasource it is necessary to update the datasource back to its original value

Get CubeInfo

Hello!

Here’s a simple Powershell script here to get the top level info of the cubes on an instance. This is a good script to understand how to use the AMO namespace to get info on the cubes via Powershell. You can add many other properties to this list to expand the info that you want. I’ve often mused about moving a lot of the logic that I constantly put in these scripts into higher tier functions in Powershell, or even create a ssas library, but time is always a premium…

Change Connection for xmla Query Window

One feature that has always irritated me greatly is how you cannot alter the connection for a xmla query window via the right click content menu. For .sql, .mdx and .dmx it is an option via the content menu, but not xmla!

Script for TempDB Sessions, The Queries, and (Nearly) Everything: The View

At the beginning of last month I posted an old query that showed the usage of TempDB. Since then I’ve modified it and added the query plan and have created a view out of the query. I find using this view has helped me since Read Committed Snapshot isolation (RCSI) was turned on for a few of our databases as TempDB usage increases when RCSI is enabled. CREATE VIEW vw_all_temp_db_usage AS SELECT u.

Setting Aggregation Designs on SSAS Partitions Part Two

Earlier this year I posted a Powershell script that will set the set the aggregation designs on your cube partitions. I recently created another script that contains a “Fix” flag to set the aggregation design, so you can just run it without the flag to get the info, or set the flag and fix the aggregation designs. I also added a “process” part that will run a process index on any partition whose aggregations are not fully processed.

The advantages of using this over the older version include the aforementioned process and checks, but also it means you don’t have to specify cube, measuregroup and the aggregation designs name. The disadvantage to this is that it assumes you want the latest aggregation design applied and processed. Somewhere along the way there will probably be a script which combines all of this nicely!

Script for TempDB Sessions, The Queries, and (Nearly) Everything

Hello, I’ve been sifting through a few of my older sql files on my hard drive and found this one I wrote a few years back. For each connected session to the SQL instance this script shows the session id’s pages allocated in tempDB and the queries that were last run. There’s quite a lot of variations of this script available on the web. but I like this one because it does not go into too much detail, but is rather useful for quick troublshooting.

Unexpected behaviour when using columnstore index | Microsoft Connect

An item has very recently been published to the Microsoft Connect website with regards to an issue relating to Clustered Columnstore Indexes. Unexpected behaviour when using columnstore index | Microsoft Connect. Put simply there is inconsistent behaviour when returning the data on a table that is stored using a Clustered Columnstore Index and a table that is not. There is also an issue when using the “Include Actual Execution Plan” from Managment Studio.

Deprecated Features of SQL

I was looking over the deprecated features of SQL Server 2014, and what strikes me is how little features were actually discontinued in SQL Server 2014. In fact the only feature discontinued was the removal of support of SQL Server 2005 (90) compatibility level. And that is sorted soon enough. And according to books online, there are no breaking changes when upgrading from SQL Server 2012 to SQL Server 2014. All this contrasts directly with the previous versions of SQL Server where there were numerous discontinued and breaking features. And in the next version of SQL Server (2015??) there are far more features removed than in SQL Server 2014 (10 in fact), but still far less than previous versions. I can’t help but wonder what is in store for the next version of SQL Server…

Deleting Data The Slow Way

Rare is the time do we have to delete the data row by row. But…. Recently I needed to delete some xml fields in a database as they were no longer required. It was a massive amount of xml; the table itself was 50,000 rows and nearly 300gb, and I needed to delete roughly 9000 rows.

I decided to delete a row at a time. Whilst this contradicts how sql works, I did not want to fill up the log with one massive delete. And as the database has its log backed up every 15 minutes, this will ensure that there is free space in the log for new deletes. So we’ll delete a row every 2 minutes and check that there is space in the log before deleting. If the log is looking full we’ll check again in 5 minutes until there is space to run another delete. So I can run the script and leave it to run knowing that the script won’t kill the database.

The script is below with an example using AdventureWorksDW2014.

Pick up a Book... & Throw it Out The Window!

When I learned to play the guitar, the first thing I did was obviously buy a guitar. Then I got a guitar teacher. And then I practised, and then practised, and when I had finished practising I practised some more. I made many mistakes. I got frustrated. But I never gave up. And over time I got better, made less mistakes, picked up some tips and tricks to make playing easier. I developed an ear for notes. I understood scales and keys and relative minors. And now I’m good. not great, but I can play a bunch of Smashing Pumpkins songs and really have a lot of fun playing the guitar without really putting too much effort into practising because I put so much effort in the beginning.

90% of my learning was based on practice. 10% was based on reading books and putting it into practice. The books came some time after buying a guitar. To be clear: I did not learn the pentatonic scale and it’s different position on the fretboard by reading books. I put the theory into practice. To really hammer the point home: I did not learn how to play the guitar by picking up a book and reading it. It was merely a guide to point me in the right direction.

So how does this relate to databases?

EXEC sp_executeSQL

Today I am going to talk about the stored proc sp_executesql and the benefits it has over EXEC @SQL. Microsoft actually recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. This is because of something called parameter substitution, which makes Sp_executesql a far more efficient method of executing dynamic SQL.

UK SQL Relay 2014 Resumes Next Week

SQL Relay 2014 resumes in the UK next week. SQL Relay is a series of 1 day events that are run throughout the UK.

Cumulative Updates - SQL Server 2014 RTM CU4

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2014 Cumulative Update #4.

October Musings

Hey all, it’s been a busy few weeks and though I’ve had plenty of blog posts scheduled I thought I’d make some updates with what’s been going on.

Script Batch XMLA Commands Using T-SQL

Very recently I had to delete a 600 partitions from a single measure group in a cube. This is a high number of partitions, but to make matters worse, it was 600 random partitions out of 1,000 or so partitions already there. So, I could’ve spent the best part of a day picking through which partitions to delete, or I could use SQL to script out the xmla for me and then execute the xmla manually. Ideally I would have done this in PowerShell. And I probably will if I have to do something like this again. But seeing as it is a one shot, I decided to write it in T-SQL. At any rate, I’ve posted the T-SQL below.

Drop and Create all Foreign Keys The Elegant Way

Yesterday I posted a script that will drop all of the foreign keys in your database in a very inelegant, but super effective way. Today’s script is slightly more sophisticated in that you can print out the CREATE statements for the foreign keys before running the drop. The script will print out the commands rather than running them, so you can script them out and run them whenever you want.

Delete all Foreign Keys The Brute Force Way

Before I get into this, I’m hoping that you’re aware that deleting foreign key constraints is a bad idea, and unless you have a good reason to do so, or at least have the ability to re-apply them, then leave them be.

Changing Passwords On Nested RDP Sessions

Been working from home lately, and this morning I needed to alter my password to a RDP session inside another RDP session. So I needed to find a way to press CTRL+ALT+DELETE without the first session receiving it. I had hoped that the control panel might help, but sadly not:  One suggestion that worked for me is to use the on screen keyboard (OSK, osk.exe). But don’t press CTRL+ALT+DEL on the OSK exclusively.

Going Hardway: HardMemoryLimits in SSAS Properties

When I first started administering SSAS, the HardMemoryLimit is a property on SSAS instances that I never used to really take much notice of. After all, there’s the LowerMemoryLimit and the UpperMemoryLimit, and once these are set, they are set in stone right?

Right?

Service Pack - SQL Server 2008 SP4

Despite the fact that end of mainstream support ended for SQL Server 2008 earlier this year, Microsoft have released SQL Server 2008 Service Pack 4. This arrives a few days after Service Pack 3 for SQL Server 2008 R2, but three years since the last one for this version of SQL. That’s a lot of updates!

Clustered Columnstore Index Hotfix Released

A little under a week ago on the 25th of September a hotfix (2999809) was released that pertained to poor query performance when a query contains table joins in SQL Server 2014.

The poor performance may show up if all the following apply:

Service Pack - SQL Server 2008 R2 SP3

Despite the fact that end of mainstream support ended for SQl Server 2008 R2 earlier this year, Microsoft have released SQL Server 2008 R2 Service Pack 3.

DBCC CHECKIDENT and how it works with DELETE/TRUNCATE

Some weeks back I wrote about the differences between truncating and deleting data from a table. Today I’m going to look into the behaviour of an identity column on a table when you use either a delete or a truncate to clear the data out of the table.

SQL Server 2014 Sample Databases Now Available

SQL Server 2014 has been available for some time now, yet until now we have not had any sample databases available. I say until now because on August 27th Microsoft released the AdventureWorks 2014 sample databases, available on Codeplex. Read up on Jimmy May’s blogon how they (eventually) got to us.

Compressing Open Row Groups

If you’re using, or at least read up on Clustered Columnstore Indexes then you’ll know that the data is stored in segments. You’ll also know that there are 4 states the segments can be in at any one time:

Cumulative Updates - SQL Server 2012 SP1 CU12

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #12.

Is Your Server Virtual or Physical?

Ever wondered whether an instance of SQL is hosted on a virtual or physical server? This query will tell you: SELECT CASE virtual_machine_type WHEN 1 THEN 'VIRTUAL' ELSE 'PHYSICAL' END as [ServerType] from sys.dm_os_sys_info You can also determine whether your server is clustered or not via T-SQL: SELECT CASE SERVERPROPERTY ('IsClustered') WHEN 1 THEN 'YES' WHEN 0 THEN 'NO' END as [CLUSTERED] These are just a few of the queries I run when I am handed over a server that I have not been responsible for.

Cumulative Updates - SQL Server 2012 SP2 CU2

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 2 Cumulative Update 2!

SQL In Literally The City

October this year is shaping up to be a month of learning, especially for those of us who live in London. Because not only is SQL Relay taking place on the 30th, there’s also RedGate’s SQL In The City happening a week earlier on the 24th.

SQL Functions: QUOTENAME

Hey all, today I am going to show a quick demo on QUOTENAME. When I first discovered this string function, it was one I wished I had learnt long ago as it helps simplify writing out dynamic SQL.

To quote books online, QUOTENAME “Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.” So basically it formats an identifier with delimiters so that in the event that an identifier does not comply with formatting.

The example below returns all the tables in AdventureWorks 2014 with the different types of delimiters that can be added using QUOTENAME.

UK SQL Relay 2014 Plus Travel Info for London and Southampton

Throughout October, SQL Relay 2014 is taking place in the UK. If you’re unfamiliar with SQL Relay, it is a series of 1 day events that are run throughout the UK. Each event is a single track (or more in the case of London) conference, with between 50 and 200 attendees and 6-8 hour long talks by SQL Server professionals, MVPs, authors, technical experts etc.

SQL Server Summary Scripts

When I am given a new SQL Server to look after, other than using excellent tools like sp_Blitz, I like to record the details of the instance in a spreadsheet I use to document our SQL estate. Part of what I document I can get from running a few SQL queries. I have shared these below. Glenn Berry has a diagnostics script that goes into far greater detail per server, which is also a great tool to run in order to ascertain the state of a server.

When NOLOCK Locks

A real quickie this evening, and it’s something I discovered yesterday the hard way: using NOLOCK table hints can cause blocking. Yep, you read that right. Given the fact that the table hint is called NOLOCK it’s counter intuitive to what you’d instinctively think, but it’s fact and by design and also, there’s nothing wrong with it.

Paid In Full

Before I get into what exactly it is that I have paid in full, let’s have Eric B and Rakim drop a beat for you this morning: [youtube https://www.youtube.com/watch?v=E7t8eoA_1jQ&w=420&h=315] So, that it’s then, my student debt is paid off! A mere 7 years after leaving university, I have fulfilled my side of the deal. I was going to write a large piece about the state of education today, but I think I’ll just put a link to a sobering news story on the BBCwebsite about the state of the student loan system in the UK.

Cumulative Updates - SQL Server 2014 RTM CU3

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2014 Cumulative Update #3.

How To Split Non-Empty Partitions When A Clustered Columnstore Index Exists on The Table

Another pithily titled blog post, but this one revisits the feature of SQL Server 2014 that piqued my interest the most: Clustered Columnstore Indexes. I’ve been working with Clustered Columnstore Indexes for the past few months and recently a colleague had to split a non empty partition, only for the error message below to appear.

Msg 35346, Level 15, State 1, Line 390 SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

Ok, so it looks like we cannot split non empty partitions with a Clustered Columnstore Index on the table, or at least an enabled clustered columnstore index. So the topic for todays post is to find out how can we split a non empty partition when a clustered columnstore index exists on the table. We can create a test database/tables/Clustered Columnstore Indexes and disable the indexes before we split to see if that is the solution, as suggested in the error that it might be. Let’s start at the beginning using the database I created for my previous post on SQL Server 2014 Clustered Columnstore Indexes and Partitioning. The script below will create the database and the objects necessary. I’m going to state the obvious, but read through the scripts first before you run them; things like file locations on boxes are always different:

Attach and Detach Cubes Using AMO

Before I start this post, I want to mention a few things:

Free Books and Training Kits

To paraphrase a saying; learning is priceless, but education must be cheap. And if you’re like me then your training budget is usually whatever you find down the back of the couch!

Cumulative Updates - SQL Server 2012 SP2 CU1

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 2 Cumulative Update 1!

Range Values of DATETIME vs DATETIME2

Came across an error today generated by Entity Framework:

‘The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.'

Typically a datetime2 type can be inserted into a datetime type column provided the value of the datetime2 is between the minimum and maximum values of the datetime type.

Database Project Entropy

With every new release of a software product, some features are altered at the request of the majority of users with the aim to improve the product. And generally these changes are correct. Most, but not all. Some are short sighted at best. And if there was one feature change which Microsoft acquiesced to which I just cannot abide by was to remove the automatic creation of the folder structure in Visual Studio database projects.

PRINT and SELECT Character Limits

trying something a little different today… writing a post that is the examples. A simple one to start off this method of blogging. Enjoy! SET NOCOUNT ON DECLARE @SQL NVARCHAR (max) SET @SQL = 'There is a character limit set when outputting the query results to text to 256 character. This message will not print out fully. So the question is: &quot;How do i change this?&quot; Well it is quite simplereally: what you need to do is go to go to the menus at the top of the screen, and click on tools ---&gt; options.

What You Need to Know About SSAS Processor Affinity

I’ve been looking into using processor affinity on our SSAS instances to help control the workload on our servers. Sometimes a process can run which will grab as much CPU resource as it can, which means that queries on the cubes cannot run, grinding the front end to a halt. Unlike the db engine, there is no resource governor for Analysis Services. Sure you can set the min/max memory simple enough, but setting limits on IO and CPU is a more complex task.

Cumulative Updates - SQL Server 2014 CU2

As part of their Incremental Service Modelling, no sooner have they released SQL 2014 RTM, Microsoft have now released SQL Server 2014 Cumulative Update #2.

Creating Snapshots of a Database Script

Creating snapshots for databases is one of those features that is reserved for the Enterprise edition of SQL Server. A database snapshot is a read only transactionally-consistent copy of a database taken from a point-in-time. To create a snapshot of a database you need to execute a T-SQL statement; there is not an option through the UI. It is much the same as a CREATE DATABASE statement, but with a few differences:

Worst. Script. Ever.

Recently, I discovered a job running on an instance of SQL Server (not a production server, test server mercifully!) that ran this:

Microsoft's Lousy Response to a Connect Issue

I’ve been going through the process of upgrading from SQL Server 2012 to SQL Server 2014 and I’ve hit a bug when running snapshots. Any snapshot that was in place from SQL Server 2012 cannot be dropped in SQL Server 2014. There’s a Connect article already open.

Usage Based Optimisation: Zero Value in Estimated Count

Today I needed to update our cube aggregation designs using the Usage-Based Optimisation Wizard but I got stuck on an error I;d not seen before “Estimated Count or Partition Count must be specified for cube objects that require counting.” Ok, not too helpful. I highlighted each dimension and clicked the “count” button for each one, but it still did not fix. I then looked a little closer and noticed that one of the dimensions had a red squiggle underneath it.

How Big Are My Data Warehouse Partitions?

I recently wanted to find out how big each of my partitions are in a data warehouse. It’s seriously useful for estimating size requirements. The script below is the result. It might require a bit of tweaking to suit the layout of your partitions (see comments in the script) but for the most part it’s good to go. It took a few seconds to run on a database with many partition schemas/functions and many thousands of partitions, however I advise in testing it out in a test environment as it will cause locking on system tables.

SQL Server 2014 News

Apologies for lack of truly original content lately, moving house + work + family are taking up all my spare time. Here’s some updates from around the web.

Cumulative Updates - SQL Server 2012 SP2

UPDATE: You should probably hold off installing this until the first CU is available (see linked post)

SQL Server 2012 Service Pack 2 is available - but there’s a catch!

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 2!

Cumulative Updates - SQL Server 2012 SP1 CU10

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #10.

SQL Reverse String Function

Sometime last year I posted some examples of string functions in SQLand Powershell. Today I am posting about the REVERSE function. The REVERSE function returns the reverse order of a string value. Just like most functions the syntax is straightforward, but when combined with other string functions can be very powerful. [code langauge=“sql”] REVERSE ( string) This example reverse the characters in a variable: ```sql DECLARE @myvar varchar(10) SET @myvar = 'sdrawroF' SELECT REVERSE(@myvar) AS Reversed ; GO Using REVERSE and CHARINDEX together implements a LastIndexOf string function.

Setting Aggregation Designs on SSAS Partitions

Recently I deployed updates to our cubes that resulted in the aggregation designs being removed from our partitions. This was because in the Visual Studio solution we had more than one aggregation design for each measure group, so that re-processing and adding new partitions caused the aggregation designs to be dropped. D’oh!

xp_regRead Cheat Sheet

Xp_regread is an undocumented stored procedure. It enables you to read the literal registry path that you specify via T-SQL. EXEC [master].[dbo].[xp_regread] @rootkey='HKEY_LOCAL_MACHINE', @key='Software\Microsoft\MSSQLServer\Setup', @value_name='SQLPath' [code] You can use the OUTPUT parameter with xp_regread to return the value back. [code language=&quot;sql&quot;] declare @outputValue nvarchar (128) EXEC [master].[dbo].[xp_regread] @rootkey='HKEY_LOCAL_MACHINE', @key='Software\Microsoft\MSSQLServer\Setup', @value_name='SQLPath', @value = @outputValue OUTPUT SELECT @outputValue You can check who has access to xp_regread by running the following query; SELECT OBJECT_NAME(major_id) AS [Extended Stored Procedure], USER_NAME(grantee_principal_id) AS [User] FROM sys.

SQL Server 2014 Clustered Columnstore Indexes and Partitioning

(If you attended my talk at SQL Supper in January 2016 and want a copy of my scripts, then this post here has the full details)

One of the features of SQL 2014 which got less column inches (pun intended) than Hekaton/ in memory OLTP but was of more interest to me was the clustered columnstore indexes, or CCI for brevity.

Use Powershell to Get SSAS Partition Details

The Powershell script below returns information on all the partitions within the Measure Groups of a given cube. I prefer to use Powershell to get this data as there’s no way to get he info out of the UI this quickly. If, like me, you have lots of partitions within a Measure Group and want to exit the script you can press “X” when the list reaches the bottom of the screen.

SQL Server Partitioning 101

I’ve worked with partitioning for quite some time now, and though it has been around for quite some time and lots has been written about the concept I recently presented an example of partitioning to a few people within a team and felt it was worth sharing on this blog. In today’s post I am going to demonstrate how to create partitions for a table, what happens when you insert data, how to split data out, how to switch partitions and finally merging partitions.

Cumulative Updates - SQL Server 2014 CU1

As part of their Incremental Service Modelling, no sooner have they released SQL 2014 RTM, Microsoft have now released SQL Server 2014 Cumulative Update #1.Cumulative Update 1 includes all hotfixes which were released in SQL Server 2012 SP1 CU 6, 7, 8, and 9. The build number of the cumulative update package is 12.0.2342.0. You may have to restart the computer after you apply this cumulative update package. This CU contains 111-121 fixes, depending on your sources.

A Few Things I Didn't Know Last Week

Clustered Columnstore Index Catalog Views Split By Partition

If you are using partitions with the new Clustered Columnstore Indexes then it’s helpful to know the number of dictionaries/segments each partition has. This is because partitions have their dictionaries and segments isolated so that switching of partition is still kept as a meta data task only. Below are two queries for checking the catalog views for both dictionaries and segments and grouping them by partition SELECT i.name, p.object_id, p.index_id, i.

How I was Completely Outwitted By SQL Today

Over the weekend a SQL Server went live in one of our environments. There’s a permission script I needed to run this morning. This script I’ve used a fair few times on different servers, and not had a problem with it. However, today it failed with the following error message.

“Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in add operator.”

For Each Database

Today I was writing a script that would go through all the databases on a server and add a user as a member of readonly and denywriter. It’s a script that I’ll probably need again, and so I discovered the “EXEC sp_MSForEachDB” stored procedure. This undocumented and clearly powerful sproc needs to be respected, however it’s certainly useful for batching a lot of commands quickly. sql execute master.sys.sp_msforeachdb 'select ''?'' '

SSAS Server Properties: AllowedBrowsingFolders

Way back when I started this blog I wrote about a few of the SSAS server properties, and today I’m revisiting this subject tot talk about the AllowedBrowsingFolders property.

Cumulative Updates - SQL Server 2012 SP1 CU9

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #9.

Managing Build Agents via Visual Studio

Things have been quiet on the blogging front, the reason being that house buying/selling has become a major distraction. Plus any time I do get I’ve been looking at Clustered Columnstore Indexes. The more I read about them the more I’m impressed by what Microsoft and the bods on the SQL DB Engine team have achieved here. If you want to read more about clustered columnstore indexes I suggest you check out my previuous posts as there are plenty of links to pique your interest.

Six of the Best: Clustered Columnstore Special

Here’s a roundup of 6 links and updates from the past month I recommend as good reading. I decided that I needed to post a list of Blogs posts, technology info and other random-but-related stuff. So I decided to limit myself to a monthly post of sharing no more than 6 articles across the web. I’m massively busy getting to grips with Clustered Columnstore indexes, as well as day to day tasks, raising a family, studying for exams etc.

An Alternative Method of Creating a ResultSet in SQL

Happy Mardi Gras! I’ve got a few ideas for posts that take more time than the 5 minutes I can spare each day. However today’s a quick one as I’m snowed under with work and the joys of house hunting. I came across this in an old stored proc: and after a bit of Googling I found out it's a way of creating a duplicate of the columns in the table.

Notes on Management Pack for SQL Server Analysis Services 2012

Apologies for the lack of snappy title! Some weeks back I posted about the Management Pack for SQL Server Analysis Services 2008 and 2012 that was recently released. If you do not know anything about the management pack I recommend a read of my older post before reading this one. We recently installed the Management Pack for SQL Server Analysis Services 2012 and as promised I am posting some notes on the whole experience and install;

20% Discount on Microsoft SQL Exams? Oh Go On Then...

As my more regular readers will know, I am working towards my MCSA SQL Server 2012. So I was pretty pleased to see this tweet the other day. https://twitter.com/MSLearning/status/435877633067593729 I signed up and booked my exam for March 28th. I’ve booked the “Administering Microsoft SQL Server 2012 Databases”. This is my day job after all! A few caveats with the voucher:

Snapshots on Databases with Filestream

Today I am going to try to write a blog in 10 minutes, based on an issue that occured yesterday.

We use SQL Server Enterprise which gives us access to taking snapshots of databases which we use during releases. Recently we added a filestream filegroup to one of our databases. We did not know this at the time, but you cannot take a snapshot of a database that has a filestream filegroup… at least that is what we thought. Turns out all you need to do is not reference the file when you are taking a snapshot. An example for you to try is below.

I Resent Your Email

I remember a story one of my old bosses told me, she sent out an email to her team and some peers about a new process, and just before she was about to go home she noticed a response from her peers was “I resent your email”! This angered my boss, so she fired off a diatribe to her colleague, telling him what she really thought of him and “how dare he respond in such a way” etc.

SQL Supper Notes

Yesterday evening there was a very special SQL Supper hosted at the Microsoft offices in London Victoria. It was an open session with some of the Microsoft Data Platform group, Mark Souza, Nigel Ellis, Conor Cunningham, Hatay Tuna and Ewan Fairweather. So I made my way by foot from the offices here in Covent Garden to London Victoria. Although February in London is rather chilly, with a brisk walk you can make it in half an hour, plus you pass several landmarks such as the piazza at Covent Garden, Trafalgar Square (and it’s controversial 4th plinth sculpture, the Blue Hahn Cock) the Mall, Buckingham Palace and finally, Westminster Cathedral.

What happens When We Truncate a Table?

I’ve been using TRUNCATE TABLE to clear out some temporary tables in a database. It’s a very simple statement to run, but I never really knew why it was so much quicker than a delete statement. So let’s look at some facts:

Last Name Change I Promise... Again...

A lesson learnt from thinking that Red Phoenix was a good site title (always Google a website name before you decide to take it on).

Getting Info for a Windows Login on SQL Server

When using windows login for access to SQL instances, you can add Active Directory groups as logins and apply the permissions. However, this means that the individual users access is hidden from the DBA. This can cause confusion as a user can be a member of more than one group, and overlapping permissions can prevent users from working (for example: an explicit deny on an object in one permission set will always override an allow permissions.)

You could then have access to Active Directory, but chances are your Domain Admins will refuse access, and quite rightly, because I sure wouldn’t want someone poking around the security for my SQL Instances, let alone domain wide security! Fortunately, there is a solution for us DBA’s. By using General Extended Stored Procedures, which provide an interface from an instance of SQL Server to external programs for various maintenance activities we can access Active Directory for all of the info we need.

Info for Each Database File

Some of the sys tables are great at getting the minimum amount of data needed from them to get to the point. A good example is the sys.master_files, which contains a row per file of each databse on an instance.

CPU Tools

Like most IT Professionals I’ve built my own machine and overclocked/undervolted machines. When making changes like this it’s important to get good monitoring software. Even as a DBA it’s important to have a good understanding of the hardware your database instances are running on. The tools below are not only great, they’re free!

Management Pack for SQL Server Analysis Services 2008 and 2012 Released

I was browsing Twitter last Sunday night and a tweet from Chris Webb caught my eye:

Microsoft Exams: 2 down 2 To Go

Wow, a new years resolution I’m following through with! This year I’ve already passed 2 Microsoft Exams. The first exam was “Querying Microsoft SQL Server 2012”, and some of the skills I learnt from studying for this exam has helped me write better scripts, 1 of which was posted yesterday. So I’m 1/3 of the way towards achieving the Microsoft Certified Solutions Associate (MCSA) SQL Server Solutions Associate certification.

The second exam I passed is the “Delivering Continuous Value with Visual Studio 2012 Application Lifecycle Management”, which (along with the testing and TFS exams) is necessary for achieving the Microsoft Certified Solutions Developer Certification.

Find Partitioned Tables on Filegroups

Recently I needed to discover which tables are on which filegroups, but with the extra complexity of accounting for the partitions: partitioned tables can exist across multiple filegroups. The script below will show the tables name, schema, partition schema and partition function, and which filegroup it is stored on. Tables that are split across multiple filegroups will appear twice. ;with ranges ( schemaId, dataspace_id, table_name, partition_Function_Name, Partition_Schema_Name) as ( SELECT obj.

Firefox OS First Impressions

I’ve spent the past couple of weeks using the ZTE Open, which runs the fledgling Firefox OS, as my main phone. The phone came with version 1.0.1 installed, so not long after I got it I built an Ubuntu VM and flashed my phone to run 1.3. The improvements were noticeable immediately; the screen was far zippier, the keyboard was better laid out, and I was able to use push notifications. The ZTE Open really is an entry level phone, and I’ve read plenty of negative reviews w/r/t it’s specs, but for £60 I think it’s pretty good. Don’t get me wrong, it can’t handle multi-tasking very well, and sometimes apps take a little too long to open, but for what it is, it’s good.

Dev Tools

I love finding tools that make my life easier and are either free or so cheap they may as well be. So here’s a few dev-based tools I’ve used in the past that are just that.

Six of the Best: January 2014

Here’s a roundup of 6 links and updates from the past month I recommend as good reading. I decided that I needed to post a list of Blogs posts, technology info and other random-but-related stuff. So I decided to limit myself to a monthly post of sharing no more than 6 articles across the web. Script CREATE and DROPindexes for all indexes in tables. BIDS Helper 1.6.5was released on 7th January with new features and bug fixes.

Looping Through Deletes on Multiple Tables

Not long a go I got a request to bring one of the databases from the production environment to the test environment. This database is

Cumulative Updates - SQL Server 2012 SP1 CU8

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #8.

Back in the Game

Happy New Year! I started the blog as a new-years-resolution-but-not-really-because-I-don’t-make-new-years-resolutions late last year, and with that in mind I’m posting my new-years-resolution-but-not-really-because-I-don’t-make-new-years-resolutions. These are as much related to work as well as my blog as well as some personal posts:

Delusions of Testing

Cube Processing Timeout Errors

I’m on holiday now until the beginning of January, getting some well earned rest. That is until I remember I have a 4 month old daughter who is currently unwell and wishes to vocalise her displeasure of this rather than getting some sleep.

Moving Tables that use Partitioning and Compression Across File Groups

Six of the Best: November 2013

Here’s a roundup of 6 links and updates from the past month I recommend as good reading. I decided that I needed to post a list of Blogs posts, technology info and other random-but-related stuff. So I decided to limit myself to a monthly post of sharing no more than 6 articles across the web.

SSIS 2012 Deployment Fail Revisited

( Part 2 focuses on clearing out the SSISDB by creating new stored proc based on the one used by the maintenance job. If your Maintenance Job is taking hours to run, you need to check this out.)

When deploying our Ispacs through my builds, I’ve still been getting the intermittent “timeout” issues I first wrote about back in May, and this has been exacerbated by extra SSIS Projects added to the SSISDB catalog.

Kill a Connection, Session, or Command in Analysis Services

One of the useful features of the SSAS Activity Viewer 2012 is the ability to cancel sessions and queries.

UK SQL Relay 2013 R2 Resumes Next Week

SQL Relay R2 2013 resumes in the UK next week. SQL Relay is a series of 1 day events that are run throughout the UK.

Cumulative Updates - SQL Server 2012 SP1 CU7

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #7.

Associating changesets and Work Items Since the Last Successful Build

Way back in 2008, a post on the blogs.msdn site showed how to create a custom MSBuild task to associate changesets and work items only since the last successful build. Recently I needed to write this custom task myself, but for builds that were “PartiallySuccessful” as well as “Successful”, and was pleased to find such a complete target available. However as it is written for TFS 2008 it won’t work with TFS 2010 onwards. As the blog appears to be inactive now I’ve made the changes and put the code below. Hopefully anyone who needs it for TFS 2010 onwards can use the pingback to get here for the up to date code. The rest of the solution works fine.

Analysis Services Server Activity Monitor 2014 0.0.0.1 Alpha Released

This morning I am happy to announce the release of an alpha version of the SSAS Activity Viewer for SQL Server 2014 CTP2. The name has had a slight change and is now called Analysis Services Server Activity Monitor (ASSAM) 2014.

Extract DacPacs Using PowerShell Via Sqlpackage Revisited

One of my first posts was a simple PowerShell module that used a bat file to run SqlPackage in cmdline mode to extract dacpacs. I’ve recently made some changes that make it more robust and allow other users to run it. The PowerShell module is just a wrapper around a cmd file that executes sqlpackage.exe to extract the dacpac. Check out my September Six of The Best for another solution provided by Gianluca Sartori.

UK SQL Relay 2013 R2 Plus Travel Info for London and Southampton

Throughout November, SQL Relay R2 2013 is taking place in the UK. This is the 2nd series of events this year. SQL Relay is a series of 1 day events that are run throughout the UK. Each event is a single track (or more in the case of London) conference, with between 50 and 200 attendees and 6-8 hour long talks by SQL Server professionals, MVPs, authors, technical experts etc.

Six of the Best: October 2013

Here’s a roundup of 6 links and updates from the past month I recommend as good reading. I decided that I needed to post a list of Blogs posts, technology info and other random-but-related stuff. So I decided to limit myself to a monthly post of sharing no more than 6 articles across the web. Comodo Ice Dragonis a web browser that integrates with Comodo’s SecureDNS, and is very good indeed.

Enter the (Ice)Dragon: Comodo IceDragon Review

Bit of a departure from typical typical subjects today as I provide a quick review of a new web browser I’ve been using for the past few months, Comodo’s IceDragon.

Microsoft are Keeping Me Busy

A few thoughts on SQL Server 2014 and other musings of the past week

SQL Server 2014 CTP2 Available to Download

At the PASS Summit keynote today Quentin Clark announced that SQL Server CTP2 (which is public and the last public review) and is available right now to download here. It is also available as a VM on Azure. According to a tweet from Glenn Berry, the version number in the demo is 12.0.1524.

Before what you download and install, here’s what you ought to know;

SQL Server Analysis Services Activity Viewer 2012 1.1 Stable Released

This morning I am happy to announce the release of a stable version of SSAS Activity Viewer 2012. The build number is 1.1.0.0. The new features have been highlighted below in the release notes section.

PowerShell: Programming with Hand Grenades

SQL Server Analysis Services Activity Viewer 2012 1.0.0.1 Beta Released

(Worth noting that there is a more recent version available here!)

Hi all, yesterday morning I created a project based on an old blog post Upgrade Analysis Services Activity Viewer 2008 to SQL Server 2012. Over the course of the day I made a few changes that I wanted to see myself and this afternoon I have released a beta version, SQL Server Analysis Services Activity Viewer 2012 1.0.0.1, or SSASAV 2012 1.0.0.1 for short.

Get The Backup History for Databases

Below is a script that I use to find the backup history for all the databases on a single instance. There is an inline ‘WHERE’ clause that is currently commented out that you can add in to filter by database name.

Get-Wmi Objects in PowerShell

Today is a quick run through of some ways you can use the Get-WmiObject cmdlet. Get List of WMI_Objects Get-WmiObject Win32_OperatingSystem There are many more WMi_Objects for the Operating system than this. To get the full list run: Get-WmiObject Win32_OperatingSystem | Get-Member From here you cna find much more info about your machine such as get the Install Date of Operating System: ([WMI]'').ConvertToDateTime((Get-WmiObject Win32_OperatingSystem).InstallDate) Find up-time of machine ([WMI]'').ConvertToDateTime((Get-WmiObject Win32_OperatingSystem).LastBootUpTime) To get the properties that are not included by default, you need to pipe the Get-WmiObject Win32_OperatingSystem into the Format cmdlets, such as Format-Table:

Six of the Best: September 2013

Here’s a roundup of 6 links and updates from the past month I recommend as good reading. I decided that I needed to post a list of Blogs posts, technology info and other random-but-related stuff. So I decided to limit myself to a monthly post of sharing no more than 6 articles across the web. The Full Story of Nokia and Microsoft - How we got here, and why Microsoft will fail with Nokia handsetsCollateral-Damage: My $.

SQL Server Hardware and Storage Links

Being a DBA is more than just knowing about SQL: it’s about the technology which SQL runs on, which includes servers and storage. I’m naturally interested in hardware, so I enjoy keeping up-to-date. Becoming acquainted and staying current with new technologies will not take up too much time, plus it will make you a better DBA. Below is a few resources that I have used to keep up-to-date with hardware and storage.

Extract DacPac Using Command Line

When extracting Dacpacs it’s quite straightforward to go through the UI and extract, however what if you want to do this for more than one database, or automate the process for a build? The sqlpackage.exe enables us to extract a dacpac in silent mode. Below is the command, and I will go through each parameter: &quot;C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe&quot; /action:Extract /OverwriteFiles:False /tf:&quot;C:\Users\richard.lee\Documents\dacpacs\dbname_Baseline.dacpac&quot; /SourceConnectionString:&quot;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=SQLA&quot; Some you need to be wary of:

Cumulative Updates - SQL Server 2012 SP1 CU6

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #6.To apply this cumulative update package, you must be running SQL Server 2012 SP1. You may have to restart the computer after you apply this cumulative update package. This CU contains 23 fixes and is pertinent for versions 11.0.3000 – 11.0.3373.0 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999). There seem to be quite a few SSAS updates in this release.

SQL Server Management Studio Tips

Introduction

SQL Server Management Studio (or SSMS) has a lot of depth to it that most of us are probably not even aware of. Typically we get into a habit of doing things on the basis of what we know. Also, I believe that as a DBA my primary concern is the databases and making sure my scripts are correct, and I’m sure I’m not alone here.

My Two Cents: Goodbye Nokia

Last week Nokia were purchased by Microsoft for something in the region of $5 billion (shocking how vague the figures are when the number is THAT big). For me this was a momentous occasion, as since 1999 I have owned nothing but Nokia’s, save for 18 months on an iPhone, which I traded in for another Nokia. I’m a big Nokia fan and the news that there will be no more Nokia mobiles(at least until 2016) is a massive disappointment.

Unknown Unknowns: Analysis Services Failing to Start

Cumulative Updates - SQL Server 2012 RTM CU9

A 9th Cumulative Update is available for those users still on the RTM branch of SQL Server 2012. This is in keeping with Microsoft’s Incremental Service Modelling. http://support.microsoft.com/kb/2867319You may have to restart your machine before the update is applied. Please check that the hotfixes which are included in this cumulative update package are relevant to you, though some of these seem fairly important, such as poor performance when you run a query that uses the LIKE operator in the WHERE clause.

Turn On Lazy Aggregations Through PowerShell

Not long ago we were experiencing growing pains with our cubes: We have over 20 SSAS databases with 3 MOLAP cubes on each database, and we had aggregations applied to one of the 3 cubes. To quote Jen Stirrup, aggregations are a “data structure that contains a pre-calculated summary of data. The aggregation size is smaller than the fact data it is based on, so it is expected that this will be quicker to query.

Processing SSAS Databases Using PowerShell

Late night maintenance on databases also means I get to write a blog post during the downtime. Tonight’s blog post is more PowerShell/SSAS: following on from calculating the size of dimensionsand Finding Unprocessed Objects,here’s a PowerShell script that will process all the databases on a given instance that match the current process state you input: If you’ve deployed all the databases to an instance, entering Unprocessed for $CurrentProcessStateand ProcessFull for $TypeOfProcesswill process all the objects.

Six of the Best: August 2013

Here’s a roundup of 6 links and updates from the past month I recommend as good reading. I decided that I needed to post a list of Blogs posts, technology info and other random-but-related stuff. So I decided to limit myself to a monthly post of sharing no more than 6 articles across the web. Determine if a computer is virtual with powershellSSD RAID Load Testing Results from a Dell PowerEdge R720Subselects And Formula Engine Caching In SSAS 2012SQL SERVER – Finding Last Backup Time for All DatabasesBug: DBCC CHECKDB data purity checks are skipped for master and modelRidiculously good acapella versions of video game songs

List Cmdlets Available in Powershell 3

I’ve been using Windows 8 and Powershell v3 for about 3 months now. I’m liking it very much and wanted to know of all the cmdlets available and group by the noun as they usually describe what a command acts upon. Here’s the command to run it yourself

Get-Command -Type Cmdlet | Sort-Object -Property Noun | Format-Table -GroupBy Noun

Here is the alias version

Upgrade Analysis Services Activity Viewer 2008 to SQL Server 2012

Update: I have uploaded the changes to my own Codeplex project. Now you can run the Activity Viewer on a box that only has SSAS 2012 installed. https://asactivityviewer2012.codeplex.com/ Hey folks, it’s been a massively busy week post-release, but I thought I’d share a tip on upgrading the Activity Viewer to 2012. First, a bit of background: Activity Viewer, or Analysis Services Activity Viewer 2008 to give it its full name, is a solution available to download on the Codeplex website.

Find Unprocessed Analysis Services Objects Using PowerShell

[CmdletBinding()] param( [Parameter(Position=0,mandatory=$true)] [string] $ssasInstance) [System.Reflection.Assembly]::LoadWithPartialName(&quot;Microsoft.AnalysisServices&quot;) $svr = new-Object Microsoft.AnalysisServices.Server $svr.Connect($ssasInstance) foreach ($db in $svr.Databases) { if ($db.State -ne &quot;Processed&quot;) { write-host &quot;-------------------------------------------------------------------------------------------------------------------------&quot; -foregroundcolor yellow -backgroundcolor darkcyan write-host write-host &quot;Database&quot; $svr $db &quot;is&quot; $db.State write-host $Cub=New-object Microsoft.AnalysisServices.Cube foreach ($cub in $db.Cubes) { if ($cub.State -ne &quot;Processed&quot;) { write-host $db &quot;Cube&quot; $cub &quot;is&quot; $Cub.State write-host foreach ($mg in $cub.MeasureGroups) { if ($mg.State -ne &quot;Processed&quot;) { write-host $db $Cub &quot;Measure Group&quot;$mg &quot;is&quot; $mg.

Date and Start Up Time using T-SQL

I recently had to do some investigating on SQL w/r/t start-up times. Some of this stuff is probably frequently asked on the internet, and the last query where i want the up-time of the instance in hours minutes and seconds took a bit of trial and error: --Q. How Do I Get the Current date time on my SQL Server using SSMS/T-SQL? --A: Select the getdate function SELECT GETDATE() GO --Q.

Calculate Size of Dimensions for a SSAS Database

I posted this Powershell script in my previous post, however I feel it’s worth sharing on it’s own. For full details read my previous post. Unlike SQL Server databases, there are no DMV’s or sys tables in SSAS that will provide us with size metrics. I have written about sys tables for SQL before hereand here. Instead, you need to get the size of the dimension from the folder that resides within the database folder on the disk.

Who Got The Func Part 4: String Manipulations using PowerShell

Introduction

I’m still using my newly acquired string function skills to aid me in writing better scripts. This post I’m focusing on PowerShell. As an added bonus, I’m also providing a script that will get the total size of the dimensions within an analysis services database, although it can be altered quite easily. Unlike SQL Databases, it’s not possible to determine the size using built in DMV’s. Instead, you need to get the size of the dimension from the folder that resides within the database folder on the disk.

Who Got The Func Part 3: Generating a GUID in MSBuild

I’m in a bit of a Property Function mood at the moment with our builds. We generate a GUID within our builds for an Id required in one of our config file and some more for our WiX installers. There’s two ways to create GUIDs. Which one you use really depends on what version of MSBuild you are using.

Who Got The Func Part 2: Property Functions in MSBuild

Not so long ago I posted about string manipulations in SQL. Seeing as I recently had to use the MSBuild property functions I thought it best to post a fairly advanced example of using the .Net property functions in MSBuild as most of the examples I have found online are useful to no one. The kind of example I am talking about is found on the MSDN page:

[code langauge=“csharp”]

$(ProjectOutputFolder.Substring(0,3))

The Phoenix Rises: It's a Girl!

With the exception of the time I went on my honeymoon, I’ve rarely posted about my personal life on this blog. This is because this blog is about the work “me” and not the personal “me”. The only reason I did post about my honeymoon was to explain an extended period of absence from posting. With that in mind, and also some things are too significant not to post about, Phoebe Ann Lee was born on the 1st of August 2013, at 0552.

Six of the Best: July 2013

Here’s a roundup of 6 links and updates from the past month I recommend as good reading. I decided that I needed to post a list of Blogs posts, technology info and other random-but-related stuff. So I decided to limit myself to a monthly post of sharing no more than 6 articles across the web. Enabling OpenSource Software using Windows Server 2012 R2Architectural overview of SQL Server 2014 In Memory OLTPThe 1 Skill You Need to Succeed in ITLike it or not, Self-Service BI and Cloud BI are HereLight Completely Stopped for One MinuteFeatures Supported by the Editions of SQL Server 2014

Start Me Up! Get the Start Time of a Process Using PowerShell

(As soon as I though of this post name, I had this song stuck in my head. I’m sure (hopeful!) most of you are probably old enough to remember the Windows 95 Commercial. It’s a great song and a short post so if you want a bit of background music while you’re reading then open this in a new tab and enjoy.)

Recently I wanted to get the start times for a few SQL instances I have running. I immediately thought of the Get-Process cmdlet, so I checked out the members of get-process:

Who Got the Func Part 1: String Functions Using T-SQL

Back in May I posted a SQL query that coalesced the RangeValues of a partition schema into one column: lines 49 - 57

Excluding Analysis Services files from Anti Virus scanning

I’ve been on New Orleans time all this working week and have been up late at night, not doing much, just hitting the ‘random’ button on DBA Reactions (and posting them on Twitter) when I came across this one on Anti Virus running on SQL file-types.

“Good one” I thought to myself. And then I wondered if any of the files that make up the cubes on our SSAS boxes were being scanned or whether they were excluded. We have some big cubes in our system, and by big i mean TB’s worth of cubes, some of them over 700GB. So I fired up the System Center 2012 Endpoint Protection on one of the SSAS boxes, and sure enough, all files were being scanned. But should they? This Microsoft KB article confirmed my suspicions that they can be exempt from scanning: How to choose antivirus software to run on computers that are running SQL Server

Cumulative Updates - SQL Server 2012 SP1 CU5

As part of their Incremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #5.To apply this cumulative update package, you must be running SQL Server 2012 SP1. You may have to restart the computer after you apply this cumulative update package. This CU contains 30 fixes and is pertinent for versions 11.0.3000 – 11.0.3368 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999)

How to: Use the Same Target in Multiple Project Files

How to: Use the Same Target in Multiple Project Files.

Further to my post yesterday about Importing projects in MSBuild, it’s worth having a quick read over the “Order of Evaluation” section to understand further that it is important where you import a project if you wish to set the properties and items of the imported project.(TL;DR -

Limitations in Importing Projects in MSBuild

Hello, you can probably tell from the title of this blog that I’ve had a frustrating morning trying to overcome a limitation in MSBuild when importing a project.

The Story of Synchronized Settings - The Visual Studio Blog - Site Home - MSDN Blogs

The Story of Synchronized Settings - The Visual Studio Blog - Site Home - MSDN Blogs.

This blog from the Visual Studio team provides an insight into how and why synchronized settings came about.

Using DevEnv In MSBuild

So new week, new post, and getting back to what I really started this blog. I like sharing knowledge, but part of the reason I blog about new versions of software is so that it motivates me to read up on what’s coming up, but the post today is typical of what I enjoy writing about the most. This post is about how to get projects that are unsupported by MSBuild built in an automated build.

Visual Studio 2013 Ultimate Preview Released

Barely a month has gone by since the announcement at Tech Ed North America 2013 yet Visual Studio 2013 Preview is available to download. TFS 2013 Preview is also available, however as I use the Team Foundation Services hosted by Microsoft, I’m just going to focus on VS 2013. .NET 4.5.1 preview is also available, which is installed when you install the VS 2013 preview. .NET 4.5.1 is also included in all installations of the Windows 8.1 preview. These products are classified as “go-live” by Microsoft, so they will support them in production.

SQL Server 2014 CTP1 Released

This week has been the week of the release: We’ve had the Visual Studio 2013 Preview and now the SQL Server 2014 CTP1. And that’s just the ones I’m focusing on! There’s the Windows 8.1 Preview as well as Windows Server 2012 R2. What with all the keynotes from Build and both TechEd’s this month, it is clear that Microsoft have certainly accelerated the release cycle and embraced a cloud-first development model. For me this is most noticeable in Team Foundation Services, the Azure based Source Control solution. Features were turned on regularly before they were released in the three updates we’ve had since the release of TFS 2012 back in August. This is a big change from the Microsoft’s previous strategy of developing for its products hosted on the premises first and then being pushed to them to the cloud afterwards.

At a later date I’ll dig more into features of SQL 2014, as this week my focus has been on Visual Studio 2013, which is out sometime this year, as well as doing my job in real life. The duties of blogging…

Visual Studio 2012Update 3 Released

The TFS Team at Microsoft certainly have been been busy this year. Visual Studio 2012 Update 3, presumalby the final update for VS 2012 before VS 2013 goes live, is avilable to download. It’s ben said for quite some time that this update is consideraby smaller than either of the previous Updates, as it includes bug fixes. In addition, Update 3 is a cumulative release that also includes all of the benefits delivered in Update 1and Update 2.

Visual Studio 2012 Update 3 Released

The TFS Team at Microsoft certainly have been been busy this year. Visual Studio 2012 Update 3, presumalby the final update for VS 2012 before VS 2013 goes live, is avilable to download. It’s ben said for quite some time that this update is consideraby smaller than either of the previous Updates, as it includes bug fixes. In addition, Update 3 is a cumulative release that also includes all of the benefits delivered in Update 1and Update 2.

Visual Studio 2013 Announced

Well, that was unexpected: the team behind The Microsoft product family of Visual Studio have announced that a new version will be released this year! Seeing as we tend to upgrade our version of TFS in December, there goes my hopes of a quiet Christmas. This post is going to explore the change in the cadence of releases from the ALM Team at Microsoft and discuss some of the features that have piqued my interest, and my frustrations at the lack of info surrounding the most significant changes at the tail end of the LifeCycle. I’m also going to do a short demo of one of the features that was switched on when the announcement was made for TFS 2013.

WiX Wednesday 1.5 Increase/Decrease the size of Event Log on Install

Introduction

In my final WiX Wednesday, I’m going to talk about how we can set the size of our custom event log file on install.

Automate Database Builds Part Three: Create A Database Publish Profile

Automate Database Builds Part One: Extract DacPacs Using PowerShell Via sqlpackage.exe Automate Database Builds Part Two: Extract Database Structure for Visual Studio Solution Automate Database Build Part Three: Create a Database Publish Profile

SQL Server 2014 Announced

Introduction

A week late and more than a few dollars short, this blog post is focusing on a few of the features announced for SQL Sever 2014. I’ve watched the Keynote and a few of the videos from the sessions at TechEd North America 2013, and read the whitepapers and datasheets. Information is still limited though, like which versions the features will be added to, so it’s certainly not a deep dive into the features. Nor is it an exhaustive list of the features; moreover, it’s a look at those ones that immediately piqued my interest in the Mission Critical Performance category of improvements.

Cumulative Updates - SQL Server 2012 SP1 CU4

As part of theirIncremental Service Modelling, Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #4.http://support.microsoft.com/kb/2833645 To apply this cumulative update package, you must be running SQL Server 2012 SP1. You may have to restart the computer after you apply this cumulative update package. This CU contains 39 fixes and is pertinent for versions 11.0.3000 – 11.0.3367 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999)The build number of the cumulative update package is 11.

My Week In Yonkoma Forme

WiX Wednesday 1.4 Create a Custom Event Log in the Event Viewer

Intro

Oh man, have I been dreading this blog post. I wrote about 3-4 of these Custom Event Log Components last year, and basically I’m having to re-learn it in order to try to describe it, so apologies if the detail is a little light…

Sane Planning, Sensible Tomorrow: Managing Sprints

SSIS 2012 Deployment Fail Part Two: SSIS Server Maintenance Job

Recently I post about a timeout issue I was getting when deploying SSIS projects. It was mainly to do with too much log and previous versions being kept in the SSISDB on our test environments. My solution was to run the SSIS Server Maintenance Job prior to every deployment to mitigate the timeout. By and large this has been super effective. However that job can take a considerable amount of time to run.

WiX Wednesday 1.3 Update Config Files During Deployment

Intro

There are many ways to run a find and replace in a config file to environmentalize it: you can update files in the build before they are compiled into the MSI, and even here there are multiple choices: SlowCheetah being one of them. This only works if you know at runtime the servers you are deploying to, or you only want a direct 1 to 1 connection. So on those occasions that you want to deploy to more than one box, updating at runtime does not work. Fortunately there is a way to update using WiX, and is surprisingly straightforward.

SSIS 2012 Deployment Fail

(Update: Part 2 focuses on clearing out the SSISDB by creating new stored proc based on the one used by the maintenance job. If your Maintenance Job is taking hours to run, you need to check this out.)

No WiX Wednesday this week, owing to commitments in real life. Instead, here is something regarding SSIS 2012 Deployment. Enjoy!

As part of our CI and Test Builds we have automated the deployment of two SSIS Projects. One is fairly large and the other one contains only two dtsx packages. Recently we have been getting timeout issues with the deployment of the solutions.

View Partitions in Database Partition Schema

I feel like I haven’t posted in a while despite posting only yesterday. This is because I wrote all the WiX posts in a batch and have them on auto release (definitely a good idea to write all the posts for s a series and have them done before anything else.)

WiX Wednesday Part 1.2: Running More Than One CAQuietExec Custom Action

Update: edited final example thanks to comments (changed first “after” to “before”)

Sometimes it is necessary to run a command line action from WiX in order to complete the configuration of the deployment. And though there is an extension for IIS called the WebAppPool Element, the features available do not completely cover all the functionality that you may want to configure in the installer. For example, you can set how often you want the app recycled in minutes, but you can’t define when you want it done (say at 5am). Defining the app to recycle every 1440 minutes isn’t the same, as you’re constricted to installing at the time you want the 24 hour refresh to occur.

WiX Wednesday Part 1.1: Using Features To Call Components

Feature tags are used when we want the WiX project to actually do something. It’s usually a step after we have defined the file location of whatever it is we want to drop. Let’s say we have declared the folder location “bin” already. Typically we’d want to declare dropping any dlls at the end of the product.wxs file, after we have created the drop location. The reason I like to do it this way is that it creates a logical organisation to the WiX file.

Guest Blog: Testing my Patience

(Guest blog by Testing The Waterhouse)

Firstly, let me introduce myself, my name is Gareth (author of Testing The Waterhouse), I’m a Senior QA Engineer and started working in QA the same time as Richard for the same company. I got into QA for similar reasons to Rich, I’d graduated and was finding it hard to get a job so ended up working for a small consultancy firm as a QA analyst.

When Richard got in touch with me a couple of weeks ago about guest blogging on his blog, I wasn’t really sure what to write about, seeing as he used to be a tester, but has moved into the DevOps world. I started reading The Phoenix Project, so I could try and gain an idea of what his new world is like, unfortunately, I’m only half way through, so didn’t really want to write a blog post about that…

WiX Wednesday Part 1.0: Oh, You Mean THAT WiX!

Hello, I thought I’d make the coming month of May all about WiX. And I don’t mean the free website builder. In the past I’ve gone looking for WiX tutorials and began reading some stuff before realizing that they are referring to Wix and not WiX, so I thought it best that I get that out of the way rather than waste peoples time. Over the Wednesday’s of May, I’m going to post a short example of WiX, including:

WiX Wednesday Part 1.0.5: Creating a WiX Project in Visual Studio

In a lot of my examples I am going to assume that you are using an IDE like Visual Studio. As of version 3, WiX would automatically install the WiX project template for use in Visual Studio. This enables us to leverage the capabilities of WiX with our solutions as well as use features such as Intellisense when creating WiX projects. The Visual Studio package is known as Voltive, an is available to Visual Studio 2005 and later. WiX appears to be perfectly capable when being scaled up to deploy enterprise-level solutions. If you are deploying many dlls with WiX you may want to to unload the project whilst running a large number of local F5 builds as it will re-compile the dlls all over again, meaning that you waste time compiling, then compiling again!

Choosing the Right Processor for your SQL Server Workload

Last year, we planned to move our data warehouse from a 2 socket server to a 4 socket server that was left in the data centre from an abandoned project to virtualize our desktops.

Keeping Microsoft Products Up-to-Date

I posed a question to one of our new starters the other day: Would you rather have 6 months experience in SQL Server 2012 or 8 years experience in SQL Server 2005? I asked him this in response to his surprise that we seem to use the latest versions of all the Microsoft products: Windows 8, TFS 2012, Visual Studio 2012 (including the new BIDS version), SQL Server, Windows Server 2012… the list goes on. Certainly it benefits us employees as we get exposure to the latest software. But the policy to upgrade to the latest SKU’s of Microsoft products isn’t for the benefit of the employees,it’s just a bonus. And, though it may look like the company is on the bleeding edge of IT, that ’s also not really the case either. It may be up to date, but certainly not bleeding edge. Other than access to the latest features, there are two main reasons why it makes sense to upgrade the latest version:

Automate Database Builds Part Two: Extract Database Structure for Visual Studio Solution

Automate Database Builds Part One: Extract DacPacs Using PowerShell Via sqlpackage.exe Automate Database Builds Part Two: Extract Database Structure for Visual Studio Solution Automate Database Build Part Three: Create a Database Publish Profile

Find Largest Tables To Compress

In this post I’m gong to show some T-SQL that will show you the compression state of tables and how to quickly find objects that are worth compressing.

Last week brentozar.com ran one of their Technology Triage Tuesdays on Compression in SQL Server. The video does not appear to be up yet, however it provided good insight, certainly better than the Technet pages on the same subject. As we have a multi-terabyte data warehouse at work, which is on an Enterprise licensed instance of SQL Server 2012, I’m familiar with the subject of data compression in SQL. Until recently however, another one of our other SQL Servers was on a Standard license. Recently this instance was upgraded to Enterprise, and so I was able to compress the database. Although not as large as our data warehouse, it was well worth considering compressing some of the larger tables.

Cumulative Updates - SQL Server 2012 RTM CU7

A 7th Cumulative Update is available for those users still on the RTM branch of SQL Server 2012. This is in keeping with Microsoft’s Incremental Service Modelling. http://support.microsoft.com/kb/2823247You may have to restart your machine before the update is applied. Please check that the hotfixes which are included in this cumulative update package are relevant to you, though some of these seem fairly important. This CU has 17 fixes, and the version number will now be 11.

Unknown Unknowns: SSAS Version Number and Default Admins

With Service Packs and Cumulative Updates rapidly released for SQL, it can be hard to keep up with what version you have installed where. It’s easy enough to find out which version of SQL Server you have installed just running the following T-SQL and you can find out all the info you need. But what about SSAS?

Visual Studio 2012 – Update 2 RTM Released

Today has seen the release of Visual Studio 2012 – Update 2 RTM. There have been severaliterations of Update 2 in the form of CTP. IN fact I think if I recall the CTP 4 was RTM for TFS 2012 but not for Visual Studio 2012. Regardless, the RTM is ready to download. You can get it via this link below, or when you boot up VS 2012 a new download should be available. If you missed out on Update 1 then it is installed as part of Update 2…

Visual Studio 2012 Update 2 RTM Released

Today has seen the release of Visual Studio 2012 – Update 2 RTM. There have been severaliterations of Update 2 in the form of CTP. IN fact I think if I recall the CTP 4 was RTM for TFS 2012 but not for Visual Studio 2012. Regardless, the RTM is ready to download. You can get it via this link below, or when you boot up VS 2012 a new download should be available. If you missed out on Update 1 then it is installed as part of Update 2…

TFS Build Server Stack

Based on a previous post about TFS Build Server Best Practices,I thought I’d share the stack I install and a few of the steps that I go through to get the build server working. Obviously your experiences will be different to mine, and I don’t recommend that you follow this to the letter unless you have a similar set of solutions to build. I’m also going to assume you have Windows Server installed and your build service up and running, there are plenty of blogs and MSDN articles that show you how to set this up.

Unknown Unknowns: Windows Server 2012 Power Saving

So, you’ve got your brand new Windows Server 2012 configured, you’ve got all your power saving methods switched off through the UI, and all the BIOS power saving features are turned off, powering your new box to the best it can get. There’s only one problem; you keep on losing network connectivity! Why is this? We were recently working on a new cluster, and after doing the customary checks to ensure all saving features were switched off I spoke to one of the sys admins to verify that everything had been done.

My Two Cents On TFS Build Server Best Practices

Update August 2016: If you can, use the VSTS Hosted Build Service. If you can’t, then you’d be surprised to know that the Hosted Build Service uses the D2_V2. Which is 2 core, 7GB of RAM. For 1 build agent. So you know…. something to consider there….

(Update 2014: below I mention that 2GB per agent is a good starting point. This is probably not enough if you’re publishing/scripting database changes using sqlpackage.exe, particularly if one of the databases is a multipartitioned data warehouse and you plan on having multiple builds running simultaneously. I’d say 4GB, if you can afford it, per agent for this scenario).

It’s amazing how important build servers are to a team working in Scrum; the team relies on a fast build server that can build and deploy code quickly. A CI Build gives the devs feedback that the code is good to be deployed to the test environment. And the testers rely that the build deploys to the environment rapidly. If it takes 10 minutes to run a CI build and 20 minutes to deploy to a test environment, that’s half hour spent on waiting to test a new feature. Over the period of a sprint, many builds are run, which leads up to a significant amount of time waiting on builds. Invariably the developers and testers whinge that the builds are taking too long, and the boss comes up and asks you to find out why they’re taking so long and speed them up. So based on my experiences here are my thoughts on how you can check that you’ve optimized your builds and build servers as much as you can.

Why I Use WriteHost In PowerShell

I’m very aware that using Write-Host cmdlet in PowerShell is generally frowned upon by PowerShell experts, mainly because it bypasses the pipeline, so it limits what you can do with that info. The reason I like to use it in my scripts is that generally I plan to write to the console in a variety of colours in order to convey the information and to highlight that there has been a change. If a cube is only partially processed it generally means that there is a partition within a measure group that is not processed, and when you have hundreds of partitions stored away it really can become a mammoth task to find out which one is unprocessed. So I have a script that searches all partitions with a status of unprocessed and write it to the screen in red-on-black writing. Little did I know that Write-Error existed that does the exact same thing!

F# PowerPack with F# Compiler Source Drops - Download: PowerPack for FSharp 3.0 + .NET 4.x + VS2012

This is a release of the old FSharp Power Pack binaries recompiled for F# 3.0, .NET 4.0/4.5 and Silveright 5. via F# PowerPack with F# Compiler Source Drops - Download: PowerPack for FSharp 3.0 + .NET 4.x + VS2012. http://fsharppowerpack.codeplex.com/releases/view/99231 We use f# for some calcs in our software. However an old bug that once occurred in a previous version has reared it’s ugly head again. When you compile you will see this error

Cumulative Updates - SQL Server 2012 SP1 CU3

As part of theirIncremental Service Modelling , Microsoft have released SQL Server 2012 Service Pack 1 Cumulative Update #3.http://support.microsoft.com/kb/2812412 This CU contains 38 fixes and is pertinent for versions 11.0.3000 – 11.0.3348 but is not for SQL Server 2012 RTM (11.0.2100 – 11.0.2999)Please check that the hotfixes which are included in this cumulative update package are relevant to you, unlike the last CU2,which fixed a major bug , it is generally recommended that you only install a CU if one of the fixes is pertinent to you.

When The Server Room Is About To Melt, We'll Let You Know

Microsoft has blamed an overheating data centre for a 16-hour shutdown of its Outlook and Hotmail systems. Microsoft said a “rapid and substantial temperature spike” and that the spike " was significant enough before it was mitigated that it caused our safeguards to come in to place for a large number of servers in this part of the datacenter" BBC News - Microsoft: Overheating caused Outlook and Hotmail shutdown They blogged about it here, but don’t give away too much of the details other than a firmware update screwed it all up

BIDS for Visual Studio 2012 Released

Microsoft have released SQL Server Data Tools for Analysis Services, Integration Services and Reporting Services projects with Visual Studio 2012. Finally, it looks like the BIDS suite is going to be usable with the most up-to-date version of Visual Studio; after we upgraded to SQL Server 2012, for about 3 months last year we were able to use Visual Studio 2010 as our sole IDE, before the update to Visual Studio 2012 rolled out at our office.

My Two Cents On Working From Home

So it seems that Working From Home has become a hot topic for discussion this week. The catalyst for putting the home office into the spotlight has been Yahoo’s decision to abolish the companies work from home policy. This is coincidental for me as I started a post earlier this week about my personal experiences of remotely working with other people.

Get Cube Status From PowerShell

Here is a neat little script; it gets all the status info of an Analysis Service database instance and the cubes within that database. You should see that status ought to be “Processed” and if not, you know that something has gone wrong in your database. This script can be quite easily expanded into including dimensions, measure groups, partitions, but I personally split that out into different scripts otherwise the screen looks too full of info and you cannot see the forest for the trees.

Last Name Change I Promise

I didn’t realize I had a free domain with my premium WordPress account and have finally settled on something I am happy with. So no more name changes and I plan to blog on getting SQL Server 2012 to work with Workflow Builds in TFS 2012 this weekend. In the meantime, check out a few of the new pages I have added today. And be a pal and follow me on Twitter.

The Honeymoon Period is Over...

.. with this blog. So far, I’ve really enjoyed posting things. I really hope that people have found things of use here, I know I have found it useful to challenge myself w/r/t making sure that I am posting something that is correct, and that my scripts actually work! I hope for many posts to continue. Speaking of Honeymoons, I’m off on my Honeymoon to New Orleans this week. We got married back in 2011, but we wanted to wait before we went on our Honeymoon, and what better way to celebrate than to attend Mardi Gras!

THE PHOENIX PROJECT

(Apologies for the name change whilst I try to settle on something I like. All the good names on Twitter and Wordpress have gone…) One name I wanted to settle on was the “Agile Administrator”. Nope, taken: that blog already exists. But whilst reading the aforementioned blog they were reviewing a book called “THE PHOENIX PROJECT: A NOVEL ABOUT IT, DEVOPS, AND HELPING YOUR BUSINESS WIN”. So I bought the book and read it.

SQL Server 2012 Unknown Unknowns

Last year saw the release of both SQL Server 2012 and Windows Server 2012. Both bought significant updates, including changes to High Availability and Disaster Recovery for SQL Server 2012, and a focus on PowerShell and Server Core features for Windows Server 2012, not to mention improvements to Hyper V, which could lead my company to virtualize the only boxes in our company that are still physical, namely the database and SSAS boxes. So before we make this leap it’s important to hear from those who have experience and can pass it on. Apart from reading MSDN and trying out a few labs, there are a couple of user groups that I will be attending:

Automate Database Builds Part One: Extract DacPacs Using PowerShell Via sqlpackage.exe

This topic has several parts -

Object Explorer Details Window

Occasionally, there is a need for us to manually delete a large number of staging tables from some of our databases. This can also occur to our partitioned tables that sometimes leave the switched partition on the list of tables in the database. But through the Object Explorer window in SSMS, it’s not possible to select more than one table at anyone time. You could script it out using T-SQL, but if there is also the Object Explorer Details window in SSMS, which you can open by pressing F7 or going to View -> Object Explorer Details.

Important Update for SQL Server 2012 SP1

One of the challenges we faced this week was a high CPU usage on one of our SSAS Cluster boxes when there was very little activity, plus some errors in the event viewer being logged repeatedly: We escalated it to the documentation team* and found a KB article on Microsoft. Be warned though, we did have to restart the server in order for this hotfix to apply. But it fixed our problem.

SQL Bits XI Info and Other Courses

The Internet is a brilliant resource to gain information from: There’s so many eminent IT Pros with blogs who impart their hard earned knowledge for free through blogs and forums and webinars. But occasionally we have to tear ourselves away from the PC and venture out to conferences and seminars, even if it is just to get out of the house once in a while. I’ve blogged a bit about SQL Bits, and I have just registered for the event.

Using PowerShell to Deploy Cubes

When releasing a new version of our software there are typically cube changes that have to be deployed. We have only one SSAS Solution but we deploy it many times for reasons I won’t go into here. Suffice to say that cube deployment and processing is by far the biggest task of any release due to the number of databases we deploy and size of the cubes. We have been able to automate much of the deployment thanks to the use of Powershell and MSBuild.

SQL Bits XI Registration is Now Open

Hi all, registration to attend SQL Bits is now open. http://www.regonline.com/Register/Checkin.aspx?EventID=1163518 There’s a 600 capacity limit and they anticipate it to fill rather quickly. More details can be found here: http://sqlbits.com/ Hope to see you there!

Pre-Release Preperation

Working in an Agile environment means that we have many releases throughout the year. Despite our sprints being of the same length, there generally seems to be releases where there is a lot to of functionality, and some where not so many changes are going in. Releases tend to be a team effort here, but the overall management of the process falls to me. But no matter how regularly we deploy, or how big an impact the release will be, it always makes common sense to be diligent when preparing for a release.

SQLBits XI Session Voting

SQLBits XI is now open for voting for which sessions you would like to see. There are an awful lot to trawl through, and you can only vote for 10. Quite frankly I could’ve voted for 20, the range is that diverse and the sessions that good. You have to register to vote, but as the page says it’s not registering for attending, it is just registering on the site.

Reports on Disk Space and Space in Files

Previously I have blogged about how to check for free space in a database at a file level; a file for a database can be very large, but the amount of data stored within the file can be very little. When initializing a file for SQL, it is generally best practice to create it to the size you think it is going to be so that auto-grow is not initialized as this can cause a loss in performance.

List of Disks and Free Space

Recently I have been supporting our move from TFS from 2010 to TFS 2012. The database instances is stored on a server that I do not have log on rights to. During the upgrade, it became clear that one of the tables was growing at a rapid rate. This is because rows were being moved from one table to another, and there were a lot of rows to move: I came back from my Christmas break on the 31st and apparently it had been running from the 28th!

SQLBits XI Dates Announced

SQLBits XI has been announced for the 2-4th of May 2013. Having taken place in London this year, it is moving back North to the Midlands at the East Midlands Conference Centre. Apparently there are great transport links, and being smack in the centre of England I guess it makes it convenient for everyone to get to. If you work with SQL Server in any way, and you’ve never been to SQLBits I’d highly recommend it, because

Introduction to Hekaton Part One: MVCC

Hello folks, welcome to a post focusing on one of the future features of SQL Server. SQL Server 2012 is barely a year old, yet there is already talk on a feature that is to ship in the next major release of SQL Server. The current name of this feature is Hekaton, which is Greek for 100. I believe the reason for this name is because of the performance boost that one online gaming company experienced when working with Microsoft in implementing the technology preview of Hekaton.

Disabling Flight Recorder Logging in SSAS

Hello folks, I hope you all had a relaxing holiday break. Welcome to a quick blog on SSAS optimisation. There’s a few quick wins that you can do w/r/t SSAS that can optimise the instance. One of these has to do with a feature called the FlightRecorder log. This log is by default set to ON, but really, unless you are trying to diagnose an issue, it doesn’t really provide any benefit to just having it switched on writing to the log location, particularly if the log location is on the same drive as the data location.

SQL Server Database File Size and Free Space

Hello world.

Despite possibly the lamest title ever, this blog is about my experiences as an IT Professional working for a Microsoft workshop in London. Longer terms there is going to be a series on managing Analysis Services through PowerShell, but as my skills range from SSMS/SSIS/SSAS/MSBuild/WiX…. the list goes on, I plan to post whatever obscure things I come across that may help others who turn to Google for the answers.