Microsoft Release Mania

Lately Microsoft has surpassed my expectations to how much software a vendor can release, at the same time.
Not only releasing Power BI for the general availability and commiting to,

We will continue to release weekly updates for the Service and monthly updates for the Desktop.

which in itself is absolutely amazing. But also Office 2016 for Mac (which has seen huge improvements), Visual Studio 2015, .Net Framework 4.6, SQL Server 2016 CTP 2 and not least Windows 10 was release within the same month.

To me that is a realease manifesto I have not yet seen anyone else even dare dream of… Bravo Microsoft (and with Microsoft I of course mean all the cards of the house, not just management :) )


Posted in Community, Programming | Tagged , , | Leave a comment

T-SQL Tuesday #68: Go with your own Defaults

tsql2sday150x150This months T-SQL Tuesday is hosted by Andy Yun (b|t|l) and the invitation is found following this link.

T-SQL Tuesday was started by Adam Machanic (b|t), and this is the SQL Server community’s monthly blog party, where everyone is invited to write about a single common topic.

When I first read through the “assignment” a cold sensation ran down my spine. Of course! – all the cool guys (m/f) have checklists and maybe even scripts they run, when taking on a new setup. I don’t have any of that, yet. Although I have been working with SQL Server for more than a decade, I have not been cool, calm and collected enough to actually keep a goodie bag of “must apply changes”. Most of the cases I worked on as a consultant, always seemed to have a sense of urgency to them. Such urgency, that there really wasn’t time to collect such checklist of what worked and when. This to much regret. A good part of my tricks have been incorporated into Effektor (self-service Data Warehouse), which I was fortunate enough to work on for more than three years. But, since I no longer hold a license for Effektor, that quick win is not an option.

In my current position, most of my work is to advice on platform settings across Windows Server, SQL Server (incl. Analysis Services, Integration Service & Reporting Services), Team Foundation Server and Sharepoint 2010 (currently upgrading/migrating to 2013) within the BI division of Maersk Line IT. I am very much looking forward to seeing the checklists of all the others doing a post for this TSQL2SDAY. Actually I get the feeling of free loading all the cool scripts, tips and tricks that are to come from this blog party.

As I am writing this blog post, I am half way through an excellent book by Ravikanth Chaganti (b|t|l) called Windows PowerShell Desired State Configuration (DSC). As the title gives away, the topic is PowerShell and Desired State Configuration which enables a declarative way of scripting how you want your service environment setup. The only thing that puts me off in this book, is the parts where you yourself have to explore options that are not described in detail. To me it’s an odd half baked way of writing. Other than that it a great resource. Cudos to Ravikanth.
Find other, free, training resources here, here, here and of course on channel 9.
Update: Mike Fal (b|t|l) has this exellent blog post on the topic as well.

Even though I am only half-way through the book I see a wide range of areas where I can apply this technology. In my current situation, getting changes through to our production environment has to be declared nine (9) days in advance, and only single or very very simple changes can occur – otherwise you are doomed to fail. The quality of our service provider is apparently inversely proportional with the number of letters in their name, as we went from one with two (2) letters to one with three (3) letters, and the service got a lot worse. This is why I still go with some Defaults. Not because I want to, but because the hassle is way to much – my longest running support ticket (which is still open) was filed 08-08-2014. I smell anniversary coming up.

I dream myself in a position, where I declaratively can specify which changes I want to have applied, and also with DSC have the ability to enforce these settings. Yes, that is something that is part of this technology, that you can have the configuration “monitored”, to revert any changes back (or file an event in the Application Event Log if you’re the more forgiving type). Having this ability enables you to at least monitor changes to your environment, to have a dialog about why somethings was changed. Maybe the changes are for the better. In order to allow your environment to grow with new requirements, you should be open to change.

There are a lot of areas covered by DSC, and just to give you a brief overview, this is some of the use cases:

  • Enabling or disabling server roles and features
  • Managing registry settings
  • Managing files and directories
  • Starting, stopping, and managing processes and services
  • Managing groups and user accounts
  • Deploying new software
  • Managing environment variables
  • Running Windows PowerShell scripts
  • Fixing a configuration that has drifted away from the desired state
  • Discovering the actual configuration state on a given node

In addition, you can create custom resources to configure the state of any application or system setting. So in fact, I can hardly come up with anything I cannot manage through this technology.

PowerShell DSC is an OS feature of Windows Server 2012 R2 and Windows 8.1, so if you are running any of the following versions of Windows, you are required to download the Windows Management Framework 4.0 (WMF). This goes for Windows 7, Windows Embedded Standard 7, Windows Server 2008 R2, Windows Server 2012

Systems that are running the following server applications should not run Windows Management Framework 4.0 at this time.

    • System Center 2012 Configuration Manager (not including SP1)
    • System Center Virtual Machine Manager 2008 R2 (including SP1)
    • Microsoft Exchange Server 2007
    • Windows Small Business Server 2011 Standard

WMF is not supported on Windows 8 and be sure to download and read the WMF 4.0 Release Notes for important information about changes in behavior from Windows PowerShell 3.0, and a list of known issues with this release.

DSC builds on a push/pull model – where you choose to apply which ever suits your current situation best. See illustration below:

Push is the default, but may not be suitable if you find yourself having many targets or a lot of data to push – Then go for the Pull option, which you can do either using SMB or OData feed.

I know this blog post may not be entirely true to the topic set by Andy, but I hope that my few words on DSC will have spread the awareness of the technology even further. This is really powerful folks! Combine this with all the other tips and tricks from the trenches and you are hopefully off to a way better start.

Thanks for hosting Andy!

Posted in Community, Programming | Tagged , , | 1 Comment

PASS Summit 2015 Session Catalog

PASS 2015

And so it came to be the day, when the Session Election Committee publicly announced this years speaker selection for the PASS Summit 2015 in Seattle, US.

I see a long list of veterans, like Aaron Bertrand, Thomas LeBlanc, Argenis Fernandez, Bill Anton, Bob Ward, Chris Webb, Dejan Sarka, Glenn Berry, Itzik Ben-Gan, the list is endless, almost. I know there are a lot of first time speakers selected this year as well, so a big part of the presenter team will, no doubt, benefit from scrutinizing David Peter Hansen’s (b|t|l) advice:

Actually, regardless of the event, the above recommendations will apply across many tech conventions.

Getting my head around all of the 180 sessions available will take some time. Luckily I don’t have to schedule which sessions I want to attend right away – And usually the process of eliminating the least interesting of all almost equally attractive sessions will go through some iterations. Once I am at two to three sessions per slot, I fix the schedule and let game day decide which of the sessions I will attend. Some game changing factors has to do with company on the day, as I am sometimes swayed into attending sessions that I dismissed. Some with topic, presenter and finally curiosity.
Sure, I could just get the recordings and watch them afterwards – But, I always find myself de-prioritizing this over apparent and urgent stuff at work. I shouldn’t do that, I know…

This year however, could very well be a significantly different experience. I woke up this very morning to the news, that my abstract was accepted into the Lightning Talk track. “My session was accepted for PASS Summit!” was actually uttered before “Good morning” – I was very exited.

My session will be on Custom Assemblies in Analysis Services Multi Dimensional (SSAS MD) – this is a topic that I have worked a lot with, just not lately. There are some scenarios where this approach is very powerful and has no equal on SSAS MD.
I terms of preparation, I will be double working the topic, as I am preparing this as my audition to become a Pluralsight author.

Leading up to the PASS Summit, I will be speaking at both SQLSaturday Cambridge, on Introduction to MDX and Scaling out Analysis Services, as well as our local SQLSaturday Denmark speaking on Introduction to MDX. This fall will be the most busy speaking schedule so far :)

Thank you so much #SQLFamily for allowing these opportunities

Posted in Programming | Tagged , , , | Leave a comment

How to Change the Service Account for Analysis Services

As part of a routine check, I found that one of our servers had an instance of Analysis Services (SSAS) running under a local service account. As many of our solution pull data from various sources, on other servers, there is almost always a need to do a double hop. To enable that, the service needs to run under a domain service account.

This being a simple task and only a small part of the bigger Kerberos puzzle, I filed a ticket with our support, for them to change the service account to one already existing. The reply I got is the cause of this blog post. I needed to provide the individual steps for the change. A quick googlebing turned up rather empty on specific SSAS guides, to my surprise, so I decided to create on myself (I had to anyhow).


As Patrice Truong (b|t|l) correctly called out, the recommended way of doing this change, is to do it through the SQL Server Configuration Manager. To do so follow these steps:

Type [Windows] + [r], in the promt type ‘SQLServerManagerXX.msc’ (depending on the edition XX, 12 for 2014, 11 for 2012, 10 for 2008) – i.e ‘SQLServerManager12.msc’ for SQL Server 2014

Depending on your security settings, you may encounter this next dialog:









Clicking ‘Yes’ will bring you to the SQL Server Configuration Manager:

SQL Server Configuration Manager

Here you can select (double click) the particular instance you would like to re-configure. Doing so will open this Dialog, where you can edit the login information:

SSAS Configuration Log On Dialog














Change the login information and provide the corresponding password:

SSAS Configuration Log On Dialog Edit














Click OK/Apply and the Service will need a restart for the changes to be applied.


This guide will be for Windows 2012 R2, but will be applicable on other Windows versions.

Type [Windows] + [r], in the prompt type ‘services.msc’

Services Prompt







This will open up the Services Dialog, where you can scroll to the SQL Server services listed

Services SQLServer

Double click the Analysis Services Service, and the following Dialog will appear:

SSAS Service













Click the ‘Log on’ tab

SSAS Service LogOn












Change the account to the desired domain account and type the corresponding password

SSAS Service LogOn Edit













Click OK/Apply and the Service will need a restart, before the changes are applied.
Happy hopping :)

Posted in Programming | Tagged , , | Leave a comment

New York Re-Visit

Please allow me to express my gratitude towards the team behind the upcoming SQLSaturday in New York for allowing me to speak on Security in Analysis Services (SSAS). I am really looking forward to get back to the Big Apple, and also very exited about delivering my first session on another Continent.
I have only visited New York once before, on the way home from PASS Summit 2014 I made a stop over to watch The New York Jets (1-8) host my favorite NFL team The Pittsburgh Steelers (6-3). In the weeks leading up to that game, Big Ben had just set an NFL record of throwing 12 touchdowns in two consecutive games, so you can imagine, expectations were very high. And you can imagine my disappointment, as The Steelers lost 13-20 by poor performance (M. Vick had as great 1st qtr). I left with 8 minutes on the clock, in order to get from MetLife to JFK in time for my flight, via public transportation (so if you want to know how it’s done, I did it).


My View from the Top of MetLife Stadium

Enough with the Fottball alright – This time in New York, my 2nd time, I will be speaking at the SQLSaturday event about Analysis Services Security (and as the entire NFL is in trainingcamp, except maybe The Patriots who will be in court, there’s no Football this time around).

My session will go from a brief introduction to the SSAS way of dealing with security (which is quite opposite to std. Windows way), over a quick guided tour through the GUI in SQL Server Management Studio. Most of the session will however cover how to set security through the use of PowerShell and the Analysis Services Management Objects (AMO). I hope to leave the attendees with a high level of confidence and insight into the ways of the SSAS security model. I also hope to broaden the toolbox and discuss the challenges of those attending my session. For me, I hope to be inspired by some of the great work that is done out there. So please join me on a great May morning in New York to discuss on SSAS Security.

PS: My wife still has no clue, so please keep it quiet :)

Posted in Community, Programming | Tagged , , | Leave a comment