Geeks 4 Data

I thought I knew how SQL Server manages statistics and started writing some maintenance procedures to augment the built in stats maintenance. All was well until I sat down to test my code and applied the principles of black box testing. This made me think about what I needed happen rather than what I was expecting to write in the implementation of the code. I wrote the following acceptance criteria: Table statistics are updated whenever the number of records in a table increases by a percentage defined at the instance level. Simple enough, so I started by investigating how SQL does things by default and I came across this excellent SQLBits presentation by Maciej Pilecki: https://sqlbits.com/Sessions/Event7/Lies_Damned_Lies_And_Statistics_Making_The_Most_Out_of_SQL_Server_Statistics After watching this I realised that what my code needed to do was provide a mechanism for overriding the default threshold for updating stats and not to simple schedule routine stats update statements...

I wonder if there is a word that invokes more feelings of frustration for an IT dude than  kerberos? This shouldn't be the case because there is a great number of useful resources out there to help you understand and troubleshoot the issues that occur in kerberos land. However, none of these troubleshooting guides remind you of one very important thing, be patient ! This is because kerberos is a security protocol that deals with distributed objects and services that involve synchronization and expiration and unless your name is Mark Russinovich and you have every Windows related command at your disposal, you are sometimes just going to have to wait a bit for certain things to synchronize or expire. Don't assume  that adding an SPN will immediately cause kerberos authentication to start working and read the documentation  (that last bit was for me). Yesterday I was working on a custom DSC resource for adding SPNs and my code ended up generating a couple of really w...

I recently wrote some help comments for a PowerShell function and needed to show an example directory structure and because I have, ermm, issues, I wanted a way to do it exactly the same in all my help comments. So, the inevitable Google search began and I soon discovered tree.exe, which did the trick. I am blogging about this because tree.exe comes with a nasty little issue that I want to remember (and you can benefit - how nice of me ;-)). Running the following produces a tree structure with garbage encoding: PS:\>Tree $Path /F | Out-File C:\Temp\tree.txt And adding the -Encoding parameter doesn't help. The only way I managed to make this useful is to pipe the tree output to the clipboard and paste it into notepad, like so: PS:\>Tree $Path /F | Clip Result! This stackoverflow thread helps explain the issue nicely:  http://stackoverflow.com/a/139302

This is a new one to me... I set up an SMB Share on the Backup directory for a SQL Server 2016 instance (using DSC of course) and assigned the SQL Server AD account full access, only to find that I was getting an "Access Denied" exception when I tried backing up using the share but not when backing up using the local path. This made no sense because the AD account had full access to the folder and the share (or so I thought). However, Microsoft has been doing stuff with security accounts and promoting Managed Service Accounts as a best practice so I figured "what the heck" and tried replacing the AD account with the SQL Server Service (i.e. NT Service\MSSQLSERVER) on the share...and what do you know, it worked. Turns out that the SID for the AD account and the the SID for the SQL Server Service are not the same so even though the account does have access to both the folder and the share it only has access to the folder via the SQL Server Service. To sort this ...

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 :-)