Geeks 4 Data

Setting up a VSTS Build Server to do automated builds and run unit tests. Life is good, I have provisioned the VM and installed all the necessary tools required to build and test my solution. I queue a new build and watch helplessly as my build fails on the step to execute my lovely unit tests (SSDT unit tests if you must know). This is the exception that I get: Microsoft.Build.Exceptions.InvalidProjectFileException: The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.  ... Aborting test execution. But wait, I have logged on to the build server vm and built and tested my solution manually so I know all the bits are on the machine, so what's up dude, you're ruining my day!! The key word in the exception is "<Import>". My test project is configured to d...

Well, not quite fire and damnation but quite a lot of grief. Let me start by saying how much I appreciate Visual Studio Team Services (aka VSTS) - it is a fantastic product and although stuff keeps changing (and you need to remember that) it is usually for the better. For example: the build agent that you can download and install on a local build server is often changing. I can tell this because the folder structure changes almost every time I set up a new build server. I think this is great! It means that the VSTS team are continually improving the product. However, one issue relating to agents that can be quite annoying and time consuming is trying to figure out how to tell it to use a proxy server to connect to VSTS. In the old days an agent would use the default IE proxy settings, then came the 'modify the agent config file' approach which meant adding the following to the VsoAgent.exe.config and VsoAgentService.exe.config files: But now there is a different ...

This is new to me... Apparently, you can't use Reporting Services to connect to an incorrectly matched SQL Database SKU:  https://blogs.msdn.microsoft.com/psssql/2013/02/20/rs-database-engine-does-not-meet-edition-requirements/ If you try to, you get this lovely exception in SSRS: The interesting thing is I got this exception trying to setup a SQL Express Reporting Service data source connecting to a Developer edition of the database server. I thought Express was free so why would they put restrictions on what database server it connects to? Ahh well...time to install SSRS Developer edition I guess :-)

Ever had a situation where you need to execute a cmdlet in PowerShell but also needed to pass in different parameters depending on what parameters had been passed to your code? So the first thing you try is using a whole lot of if else statements...but that is butt ugly and verbose right? Well, PowerShell uses a technique called Splatting to resolve this. Here is a simple enough post to explain it:  https://ramblingcookiemonster.wordpress.com/2014/12/01/powershell-splatting-build-parameters-dynamically/ Thanks rambling cookie monster!

Ok, so I want to migrate my TFS source code to a Git repository but I don't want to lose any history - how do I do it? Git-tf will do the job quite nicely and this blog post does a decent job of explaining it:  https://chriskirby.net/blog/migrate-an-existing-project-from-tfs-to-github-with-changeset-history-intact Only thing to keep in mind is that Git-tf uses Java so that will need to be installed first. Happy days!

If you need to know how and why and what when it comes to MDS model deployment then this article is the best I have read to help you understand the basics:  http://www.sqlchick.com/entries/2015/3/16/how-to-deploy-master-data-services-models-between-environments However, what it doesn't cover is how to get a Build or Release engine like VSTS to deploy the models remotely. Fortunately VSTS provides a PowerShell on Target Machines Build Task that enables us to execute the MDSModelDeploy.exe command remotely. I have a PowerShell script in my TFS repository that I copy to the target machine using a Copy Files Build Task and then invoke that script using the PowerShell on Target Machines task. Now all I need to do is figure out why PS-Remoting is not working on my MDS servers...oh well, next blog post dudes :-)

Just watched a demo of a Windows Server 2016 Nano vm deployment using PowerShell by one of our very own DevOps Dudes (Si - you da man)...and it rocked! I absolutely love the idea of Nano and to see it up an running within a few minutes with a few lines of Posh code...well, let's just say the brain was on fire! If you haven't looked into Nano yet, you might want to take a look at Channel 9's dedicated channel:  https://channel9.msdn.com/Series/Nano-Server-Team This is the future dudes and dudettes!

I'm working on a DSC configuration for SQL Server Reporting Services and it is quite a simple configuration with very few required resources. However, one of the resources (xSQLServerRSConfig) does something rather odd in it's Set-TargetResource function - it uses Invoke-Command to loop back to the same server and specifies the -Authentication parameter as CredSSP. Now up until the other day I had no idea what CredSSP was and the exception that was raised by the LCM was rather new to me: After spending ages trying to learn what the CredSSP protocol is and why someone would use it (see  https://4sysops.com/archives/using-credssp-for-second-hop-powershell-remoting/  and  https://technet.microsoft.com/en-us/library/hh849872.aspx  ) I decided to check for a DSC resource to enable it...and there is one, xCredSSP . Happy days! Then came the joy and agony of seeing DSC in action. Joy because the resource is so simple to configure: xCredSSP Client { E...

If you are new to version control or have grown up using something other than Git (like TFS) then this video Build Conference video is going to help a lot! Be warned, there are some very important conceptual differences between Git and TFS, so watch it dude! (or dudette :-)

This post is for newbie BizTalk users who have installed their environment using the Next, Next, Finish approach. Today you have your shiny new development sandbox* with a nice chunk of disk space. Tomorrow you will have slightly less disk space. Next week a little less still. In a month, no disk space. This will be a bad place to be. There are a number of things that eat up disk space in the world of BizTalk: BizTalk uses a number of SQL Server databases at its heart. These databases are NOT backed up by default and you should NOT use database maintenance plans or third party backup software to back them up. You'll see why in a minute. BizTalk normally reads messages, processes them, delivers them and then deletes them. It keeps the databases surprisingly small. However if your BizTalk solutions have issues the messages will be "Suspended" which means "saved to the database until the problem is fixed". At this point your database is now growing. BizTalk...

Revisiting previous code projects can be an exercise in hair-pulling. For example, in my previous blog post I mentioned creating a VM deployment script. Due to some issues with binaries, the need to revisit the code has come up recently, to add an option for creating VMs with a GUI, rather than as Core. The code worked when it was just dealing with one choice, but adding a very simple -InstallGui switch turned out to be a little more complex than originally thought. Constant errors stating the following were the main contention: New-SCVMTemplate : VMM is unable to process one or more of the provided cmdlet parameters. (Error ID: 1600) Needless to say, this is a bit non-specific! After a lot of frantic searching of blog posts and whatnot, and complete inability to find a relevant fix, I decided to scrape through the code and remove parameters one by one. Eventually, the Template parameter seemed to show up as a potential problem. Hmm. Inspecting the template I was intending...

When you want to know the current configuration of a DSC node you use the very aptly named Get-DscConfiguration cmdlet like so: PS C:\>Get-DscConfiguration -CimSession $target_node This cmdlet will execute the Get-TargetResource function for every configured resource on the node and return details on its current state like so: And life is good until you add a new resource to your configuration, push it to the target node and run the cmdlet again, only to be faced with an ugly exception like this: I know what you're thinking, "say whaaaat!!". You just want to see what the current config is and because of one dodgy resource you can't see anything - that sucks. However, the solution is quite simple. The words that stand out are key is not a valid property in the corresponding DSC resource schema file . The preceding word (AvailabilityGroupNameDatabase in this case) is the property that the resource Get-TargetResource function is ...

I'm your typical infrastructure guy - design the platform, run through the installers, use PowerShell to do post-setup and admin tasks, maybe script something to make a boring job easier - you know the drill. This DevOps thing is kinda cool to see working, but how does that fit in with the way I'm trying to do in my projects? As it happens, it fits in really, really well. We already use Agile methods in our project delivery team, and the Ops team have just adopted Scrum as well. The DevOps mindset isn't terribly difficult to adopt when you're already used to delivering small improvements often. What is different about all of this is, for me anyway, is the Dev part of DevOps. I've had my PowerShell knowledge tested and expanded whilst working with this - first, by writing a deployment function to take what System Center Virtual Machine Manager does and making it fit into our deployment requirements and under source control; secondly, working on the SharePoint...

Well, it'll take you 5 seconds to google it but here you go: Paradigm Shift: a fundamental change in approach or underlying assumptions. The first time I encountered that phrase was with the release of Visual Studio Team Edition for Database Professionals. This excellent VS add-in was the very first tool that enabled SQL developers and DBAs to put their database schemas under source control using an integrated development environment and to validate the schema before deploying it to a server! This was something that application developers had been working with for ages and provided the opportunity for the database to join all stages of the software development life-cycle. The problem however, was that SQL developers and DBAs were not accustomed to using source control, to 'compiling' their code or to 'kicking off a build'. What was needed was a radical some significant upskilling and a complete change of mindset - they needed to start thinking like application de...

Listen up dudes! We need to write some unit tests for all these PowerShell functions we have been writing. Why? Because we need to make sure our code does what we think it does and only what we think it does. Yeah, I know we test the functions manually when we use them but we are pretty limited in our ability to monitor what our code is doing under-the-hood unless we get some kind of verbose output. Even then, we don't know what we don't know. With the use of a testing framework (like Pester) we can test each function and isolate its inputs and outputs to make sure that thing does only what it is told! If you have got this far, like I did a while ago, and you have watched a few intro videos (like this one  https://www.youtube.com/watch?v=gssAtCeMOoo  and this one  https://www.youtube.com/watch?v=0fFrWIxVDl0  ) and you have downloaded Pester from GitHub ( https://github.com/pester/Pester ) and you have copied the extracted folder to the PowerShell modules directory...

Ok, let me just say up front, the Pull Server rocks! The centralised management and scale that it delivers is great. It is all good dude, but... When learning how to use a Pull Server to distribute node configuration you have to keep a few things in mind in order to make things work. This is what I have learnt so far: The event log is your friend, get to know it well There is a resource available to help diagnose DSC issues called xDscDiagnostics - it is a good place to start. Remember that publishing a new configuration to the pull server does not mean it is just going to get applied on the target the next time the target node LCM fires up, it depends what the state of the Pending Configuration is. Remove-DscConfigurationDocument  can be used to pull the plug on a misbehaving config Removing a misbehaving config will not remove a misbehaving Resource (ermm, deleting the resource folder on the target node seems an easy way to fix this but not necessarily the right w...

I love source control (not agape love or anything like it but I really do dig it). It gives me great peace of mind knowing that if I make a stupid mistake in my code (and let's face it, it's gonna happen) I can compare the current version of the code with any previous version and revert back if I need to. Thing is, I don't like having to do that - I want my code to be sharp, readable, cool but most of all right. And I want this to be the case before I check it in (commit). The problem is that there is not always a bud available who has the time and is in the frame of mind to review my code when I need him\her to. Don't even think about scheduling code reviews, that is just a pain in the rectum - it messes with my mojo when someone interrupts me when I am in the zone just because 4:30 has arrived on their watch. Then there is the problem of dudes who haven't read Code Complete and like to write 2000 line functions because they haven't learnt abstraction. Who ...

One of the first things I have learnt working with DSC is that the Local Configuration Manager (LCM) does things on a schedule and that it is quite a patient little service. This is most obvious in a Pull server setup. I copy a config (and checksum file) to the Pull server and then wonder why nothing is happening on the target node. The first thing that comes to mind is "my Pull server ain't configured right". So, naturally I have a look at the LCM on the target node: PS   C:\>Get-DscLocalConfigurationManager -CimSession $target_node The LCM says it is configured for Pull and looks happy so why the heck is my configuration not being applied? Well, the answer is in the RefreshFrequencyMins property of the LCM (which is 30 by default). I could end up waiting for 30 minutes for anything to happen, or I could just tell the LCM to get on with it: PS C:\>Update-DscConfiguration -ComputerName $target_node That will output some details on the job that is carrying ...

Four years ago I sat in a conference room in Barcelona and listened to a Gartner analyst talk about the emerging practice of DevOps. I was excited because the conflict between developers and the infrastructure\operations guys was not new to me. I was sitting next to my manager, the Group IT Manager, and fully expected to have a really positive discussion about it after. We both walked away scratching our heads. The problem with paradigm shifts in IT is that the success or uptake of the change really depends on who delivers the message. If it couched in too much highfalutin mumbo-jumbo then the people who really make it happen (the folks doing the daily grind) just miss it and carry on as normal, or they get it wrong. Today I believe I understand what DevOps is (at least from one perspective) and the thing that has helped me to understand it, is Powershell Desired State Configuration. DSC is the embodiment of declarative configuration automation for IT infrastructure. In learning ho...