Geeks 4 Data

So with MS Ignite behind us with and with some great new revelations, I thought I'd take a look at the newly announced "Project Honolulu".   Project Honolulu (which can be downloaded here , is going to be the replacement to Microsoft's administrative MMC snap-in tools like Device Manager, Disk Manager etc.   Its not replacing RSAT tools or the system center suit but to compliment them and fill the void in simply and basic administration of server management particularly for Windows Server Core.   Before I begin, please remember that any thoughts/opinions in this blog are my own and are not influenced in any way but feel free to comment - I love "nerd talk", and any code posted comes "AS-IS" and you, the executor are responsible for your own stuff. Project Honolulu can be installed on Windows 10 as an application. This will then install as running process and rely on the application to be running in the backgroud. The alternative method is...

I have been using SSDT for years, through its various incarnations, and I am a huge fan. I can say I have fond memories of Gert the Data Dude posting his way to blogger awesomeness and me being extremely grateful that he did. Gert has moved on to other parts of the Microsoft universe but the product has survived and seems to be a fully-fledged senior citizen in the Visual Studio landscape. Worryingly, Visual Studio has also started to entertain a new suitor, Red-Gate, and their devops offering is quite different from the model-based SSDT project...we shall see what happens there. Anyway, the reason for the post is that I have just learned something rather interesting about how SSDT, VS, MSBuild and SqlPackage.exe co-operate to get scripts added to the beginning and end of a database change script. The Requirement: I have just started using tSQLt to write database unit tests after years of using the SQL Server Unit Test in the Visual Studio Test Project and my plan is to integrate ...

If you have worked with PowerShell remoting then you will have seen this error before: : Connecting to remote server BLAH failed with the following error message : The WinRM client received an HTTP status code of 403 from the remote WS-Management service. For more information... It is not a happy message, especially when you have been using PowerShell to remotely manage the particular server for ages! So then you try remoting from another client and it works! You go back to your original client and try remoting to anything else and it fails...dohh! "But this worked just yesturday!" you scream. Ahh, little things can make a big difference and in my case the issue was related to a VSTS Agent update that I did the day before. In order for the new version of the agent to communicate with VSTS in the cloud I needed to set a WinHTTP proxy. Once the agent was configured I could use a .proxy file in the agent directory instead...but I forgot to remove the WinHTTP proxy and in s...

Simple one this... How can we enable inbound transactions in MSDTC on a core machine? Well we use PowerShell of course! :-) PS C:\>Set-DtcNetworkSetting -InboundTransactionsEnabled $True Nice one.

We sometimes use the Biztalk WCF adapter to talk to SQL Server and for that we need there to be an endpoint stored procedure in the target database for BTS to execute. When the adapter needs to execute the procedure to get or post data to the database, it will first execute a metadata query to confirm the stored procedure's existence - this is unusual behavior and it can lead to some head scratching if the lights are not all on :-) This is an example of the query that BTS executes: exec sp_executesql N'SELECT sp.type AS [ObjectType], modify_date AS [LastModified]FROM sys.all_objects AS sp WHERE (sp.name=@ORIGINALOBJECTNAME and SCHEMA_NAME(sp.schema_id)=@ORIGINALSCHEMANAME);SELECT [param].name AS [ParameterName], usrt.name AS [DataType], SCHEMA_NAME(usrt.schema_id) AS DataTypeSchemaName, baset.name AS [SystemType], usrt.is_table_type as IsTableType, usrt.is_assembly_type as IsAssemblyType, CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar''...

If you use SQL Server Data Tools and deploy your database code using the SQL Server Database Project in Visual Studio, then read on. If you need to deploy a database to multiple instances and have some objects only deploy to certain instances, read on. If you didn't know that you can reference a database project within another database project and have the reference database schema included as part of the database, read on. If you didn't know that you need to specifically configure your deployment to include composite objects when you have a 'composite' database project, you do now :-) Later dudes!

We use SQL dacpacs for database references in our SQL Data Tools projects. Sometimes the database we need to reference is a vendor database with loads of issues in it, so if we try and extract a dacpac using management studio, it just bombs out with an ugly exception. I have seen all kinds of issues when doing this. Your first though may be to re-create the database using scripts and only include the objects that you need. That works but it's a bit of a pain to look after. What we really need is way to extract the dacpac, warts and all, and the only way I know how to do that is by using the SqlPackage.exe command line tool. Use the following to extract a simple dacpac for a reference: C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe /Action:Extract /SourceServerName:SQLCLUSTER3 /SourceDatabaseName:$(DbName) /TargetFile:"C:\Temp\$(DbName) .dacpac" Simples dudes!

The scenario: GIVEN a SQL 2000 data source AND a SQL 2016 destination AND a metadata driven, BIML generated SSIS package to move data from source to destination WHEN you try build the SSIS package using an OLEDB Connection THEN SSIS says it can't connect because SQL 2000 is not supported So, what do you do? Well obviously ODBC comes to mind and so you try that avenue (ala ODBC Driver for SQL Server) only to find you're presented with another unfriendly message: Now what? Well, simply use the SQL Server provider and that will work. SSIS is able to use it with an ODBC connection, so all is good, until you execute your package and get the most excellent of exceptions, the dreaded VS_NEEDSNEWMETADATA ! Or, in my words "AAAAHHHHGGGRRRRHHH!!!" What just happened? My package built without any issues and didn't throw any warnings so why does this happen when I run it? Weird ODBC behavior I guess. Turns out that the ODBC connection defaults to UNICODE (whe...

I have been doing Scrum for years and have had some great teachers ( Richard Hundhausen , Derek Davidson and even Jeff Sutherland  - well, I read his book :-)) but one very important aspect of of the framework has continued to haunt me until now. I have always struggled to explain the difference between effort and work required and no matter how many times I suggested that effort was related to complexity I was never able to come up with an analogy or explanation that would suffice. The team would constantly revert to the assumption that effort meant how much of the sprint (therefore time) would be needed to complete the work; I would say, "no, that is what we use work remaining for" and the team would invariably reply "what's the point of effort then?". The other day while explaining the importance of doing design in the planning meeting to a bunch of colleagues, one very astute colleague asked the question "won't we run out of time in the planni...

Just provisioned a bunch of nice shiny new VMs with Windows Server 2016 Core, joined to the domain, proxy set and Windows Updates applied. All ready for DSC except that I can't connect remotely! The following red text annoys me: So I try a few things... 1) Try remoting from a VM on the same host - nada 2) Try removing the proxy - nada (just got a different exception) 3) Reboots - nada So then I decided to talk to a network\infrastructure dude to get help ( caus I is a dev ). A huff and puff and some jiggery pokery later and it turns out DNS was holding static records for the previous versions of the machines. You see, the VMs were shiny and new but their names weren't. Once the static DNS records were deleted I could remote! Adds weight to the argument that VMs should be named generically. Shout out to Si for helping me with this :-)

This one is just for me :-) Using git via command line will often (but not always??) change the context of the command window to Vim which is lovely and all but it is not that obvious how to get back to the normal command prompt. Sadly, 'esc' does not do it, you have to type wq at the colon prompt. Not that intuitive but hey...

Ever had that feeling that gremlins are at work? Keep going round in circles and start contemplating a career change? Well... I have been struggling with getting Reporting Services 2016 configured to use Windows Authentication, ala kerberos ( as my previous post alluded). In the past this has been a relatively trivial task but this last week I have found new depths of despair and frustration. So let me explain the scenario: GIVEN a reporting services server that is built by DSC AND the ReportServer service is running on port 80 AND the ReportServer.config file has been modified (see docs ) WHEN the HTTP/mymachine.mydomain.com SPN is added for myaccount THEN Windows Authentication is enabled for SSRS :-) AND PowerShell remote sessions (WinRM) is broken :-( So, me thinking I was clever, simply added port 80 to the SPN definition which fixed WinRM and broke SSRS. That sucks! It seems you can only have one or the other.

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 ...