Community

#SqlSaturdayDenmark Re-Cap

Posted by Jens Vestergaard on in Community, Programming 0 Comment

Finally able to sit down, in quiet peace and write about my experience this weekend. Since Friday morning, the last couple of days have been rather hectic.
It started out with me moderating an excellent session by Margarita Naumova (b|l) on Top 8 Reasons for Your Transaction Performance Problems in the PASS Summit Preview of 24 Hours of PASS. Rest of the day do normal work chores, only to set up the family for the weekend, so I could go with peace at mind.

Friday evening I attended the Speaker Dinner at Copenhagen Street Food, which was quite a unique experience. Although I didn’t find that the food matched the prices, there was a distinct buzz to the place, that I doubt will be found anywhere else (at least not in Copenhagen). The company was brilliant, and that was what mattered. I met a good handful of people I knew from Twitter in advance, so meeting face to face was long overdue.

 

Just home in time for som hours of sleep, before heading to the venue, early Saturday morning. Having dealt with all of the attendees, we sent them off to the first batches of sessions – enjoying a moment of tranquility. Lesson for next year: Reconsider speed pas.

During the morning hours there was only some minor issues that needed attention, so all in all I had good time to allow for preparation for my own session on MDX. Slides and Code examples are available from the SQLSaturday site. The session went well, at least according to my own sence, but I am hoping the attendees will be evaluating the session, if they attended. Evaluations are really important, in order to grow as a speaker.

After my session, things began to happen quite rapidly. Due to logistic challenges in the house hold, I had to pick up the #sqlbrew at home. I also had to be present, because the guy with the hotdog stand, expected me to be there. Kenneth M. Nielsen (b|l|t) allowed me to use his car, and there I was, for the first time with automatic drive. Maybe this could get ugly, with all the beers and all. Somewhere in my mind I began piecing together, the story for the Cops, if they should find me crashed with 400 bottles of beer… I must say, my left foot treated the brakes as clutch on the way out, to much amusement of pedestrians… But the beer arrived safely. After the full schedule of sessions was over, everyone gathered for the big sponsor raffle – great prices in the pool: Several drones, licenses, books etc.

In the end, I think everyone was happy – the volunteers and organisers had done as smashing job on creating a great event, yet another one. According to leaked info on session feedback, we have quite a high average rating – that is really exiting.

Late Saturday we sent everyone home with hotdogs and our own #sqlbrew

 

 

SqlSaturday Cambridge, #SqlSat411, re-cap

Posted by Jens Vestergaard on in Community, Programming 0 Comment

Early Friday morning, I left Copenhagen for my second SQL Saturday in Cambridge. Two years ago I had a blast at same event, as documented here. My expectations were high, but I was completely blown away by the work and preparation that had gone into the event. I apologize in advance to those who are attending the SQL Saturday in Copenhagen next weekend, we cannot provide the same level of experience I am afraid. A huge effort was made by the team, so hat tip to them!

After arriving at the DoubleTree, down town Cambridge I hooked up with Johan L. Brattås (b|l|t) and Boris Hristov (b|l|t) to walk the streets and see the impressive sights (mostly colleges). Spending the afternoon in good company was topped of with the most amazing dinner settings at the Gonville & Caius College, where a team of servants had a dinner lined up, with millimeter precision.

Up early on Saturday  morning, to get to the venue, I even had to wait for the hotel to have breakfast ready – I knew I had to have something to eat, because normally the lunch bags at these events are…erhh…scanty, to last for a whole day at least. Speaking of the venue, I also need to mention that it was absolutely fabulous for events such as this – kudos to the team for snagging this. On another note, it was not until I saw the picture on the wall, that I made the connection – it has Maersk written all over it 🙂

I didn’t get to see much of the other sessions, as I was preparing for my own. Introduction to MDX was executed way better than Scaling out Analysis Services, but I guess you learn as you go – apologies to the two (2) attendees for the latter one – I hope you enjoyed the ten minute early break 😉

After the almost unbearable exitement at the raffle – volunteers, organisers and speakers went punting, as depicted below:

If ever in Cambridge, you should definately do this – get a guided tour, as they know all kinds of odd stories that add to the image of Cambridge. Cambridge, where I certainly hope I have not set foot for the last time. Well done Mark and Team.

T-SQL Tuesday #69: Encryption

Posted by Jens Vestergaard on in Community, Programming, tsql2sday 0 Comment

tsql2sday150x150This months T-SQL Tuesday is hosted by Ken Wilson (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. This time, the topic is Encryption.

The only requirement I have seen in my work as Business Intelligence Consultant, is that of the platform itself. No customer has ever requested any data encrypted, except for when data is to be exported to a movable device such as a USB stick or the like. And even then, it’s usually handled by some hard- or software solution in place on the stick.

Reporting Services is one of the tools that touches Encryption, and this becomes very relevant when you do migrations, which you will eventually have to do. In the current version of SQL Server, you have the following options to manage encryption keys in Reporting Services:

  • Back up a copy of the symmetric key so that you can use it to recover a report server installation or as part of a planned migration.
  • Restore a previously saved symmetric key to a report server database, allowing a new report server instance to access existing data that it did not originally encrypt.
  • Delete the encrypted data in a report server database in the unlikely event that you can no longer access encrypted data.
  • Re-create symmetric keys and re-encrypt data in the unlikely event that the symmetric key is compromised. As a security best practice, you should recreate the symmetric key periodically (for example, every few months) to protect the report server database from cyber attacks that attempt to decipher the key.
  • Add or remove a report server instance from a report server scale-out deployment where multiple report servers share both a single report server database and the symmetric key that provides reversible encryption for that database.

If you are running your Reporting Services in Sharepoint Mode, you should not that backup process does not backup encryption keys and credentials for unattended execution accounts (UEA) or windows authentication to the Reporting Services database.

Periodically changing the Reporting Services encryption key is a security best practice. A recommended time to change the key is immediately following a major version upgrade of Reporting Services. Changing the key after an upgrade minimizes additional service interruption caused by changing the Reporting Services encryption key outside of the upgrade cycle. And since every release of Reporting Services over the last decade has all been major, breaking changes, deprecating and almost no backwards compability, this will make good sense.

Luckily I haven’t been challenged on any Reporting Server installation, in terms of Encryption, yet. We are about to kick off a big migration of our environment here in Maersk Line IT, moving from Sharepoint 2010 to Sharepoint 2013, with Reporting Services in Sharepoint Integrated Mode. I have already made sure that keys have been backed up and we know all the password we need to know. Now only the easy part of migrating the platform remains…

Another product of the BI stack that makes use of Encryption is Integration Services. Koen Vereeck (b|t|l) has written on this in another TSQL2SDAY blog post, which can be found here. I am not going to write a copy of what Koen has written, It’ll suffice with a link to his blog post and a note not to use the default setting.
The most frequent issue I have met with this ridiculous default setting, is that when developers deploy their packages into a new environment, such as test, the packages fail – The Developers then yells, screams and pulls their hair, some even cry sobbing “It’s working on my machine”, until someone shows them the fine art of changing the ProtectionLevel property.

The current version supports the following settings (src):

Protection level Description
DontSaveSensitive No data on sensitive properties is saved
EncryptAllWithPassword Uses a password to encrypt the whole package.
EncryptAllWithUserKey Uses a key that is based on the current user profile to encrypt the whole package. Only the user who created or exported the package can open the package in SSIS
EncryptSensitiveWithPassword Uses a password to encrypt only the values of sensitive properties in the package.
EncryptSensitiveWithUserKey Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package.
ServerStorage Protects the whole package using SQL Server database roles. This option is supported when a package is saved to the SQL Server msdb database. In addition, the SSISDB catalog uses the ServerStorage protection level.

This was my 50 cents on the topic of Encryption – Thanks for hosting Ken!

Microsoft Release Mania

Posted by Jens Vestergaard on in Community, Programming 0 Comment

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

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

applause

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

Posted by Jens Vestergaard on in Community, Programming, tsql2sday 1 Comment

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

IMPORTANT NOTE
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:
Source: http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-85-24-metablogapi/1374.image_5F00_22AB5572.png

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!