Roger's BI Blog

Unaware that LinkedIn has such a strong filtering policy that it does not allow me posting a completely innocent post on a Chinese extreme photography website I tried to post the following: "As an Mpx lover I was suprised to find out that the M from Million is now replaced by the B from Billion. This picture is 24 Bpx! Yes you read this well, 24 billion pixels.  Searching on the picture I stumbled on a fellow Nikon lover. If you want to search for him yourself you can find him here: http://www.bigpixel.cn/t/5834170785f26b37002af46a " In my eyes nothing is wrong with this post, but LinkedIn considers it as offending. I changed the lover words, but I could not post it.  Even taking a picture and post it will not let this pass:  Or my critical post on LinkedIn crazy posting policy: it will not pass and I cannot post it.  The technology LinkedIn shows here is an example what to expect in the near future.  Newspapers will have a unified re...

Today I ran into a very strange problem. I was doing an audit because a client lost 32 billion records in a table and he wanted to know "Who did it!". They did not use auditing because it had a too large impact on performance. The server and instance was not restarted so the DMV's were still available with data.  I presumed there were two options how the data could have been lost and so quickly: run a TRUNCATE TABLE. Very fast and and in less than a second your table is unrecoverable empty  Switch a partition out into a temporary table and just truncate the original table. Yes it happens and the effect is the same: nothing!  So I ran this query to collect the last queries: SELECT t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t WHERE t.TE...

Microsoft Azure's popularity is growing especially in the small and mid sized markets. Pay-as-you-go and the 0 investment makes these companies consider moving to the cloud attractive. Microsoft engineers working in Azure guarantee a SLA of 99.9 % or more (depending on the type of business).  The easiness of building infrastructures in Azure introduces a problem that has not been there before when IT was incorporated in the organisation: budget management. Before you had these endless and boring meetings about budget and spendings with the internal accountants to get a go and every dime was turned twice of not three times before a go was given.  In Azure you can command a server that is capable to perform at 2000 DTU and it will cost you € 31.370,76 per month . Oeps! No kidding! "Sorry, I was just looking for something powerful" the developer smiled nervously in the meeting after the reception of the Azure invoice. But everybody will starts really screaming if t...

One of the new options in SQL Server 2016 is to store cold data in Microsoft Azure. The idea behind it is that doing so could be far less expensive than storing the data locally. It sounds great but the reality is a bit different. in this blog post I will share my experiences with this feature.   Stretch database or the first steps versus "Distributed database" Looking closer at the feature of Stretch Database it incorporates actually the separation of data in a table over multiple servers. There is a main server and a back-end server that both contain a part of the data in your table. The back end server has currently to be in the Azure cloud. The front end can be in the Azure cloud to. When a query is fired on the table both front end and back end servers work together to get the data.  The MSDN Library shares an interesting picture: What this picture shows is that in the Azure cloud a SQL Server Database is created that stores all or a part of the cold data. The da...

I have been quiet busy getting my certifications for my Microsoft vTSP and P-Seller membership. What helped me enormously is the information available in the Microsoft organisation. Microsoft colleagues are struggling with the same problems and have to be certified like all of us.  Unfortunately Microsoft Corporation has decided that the vTSP / P-Sellers will no longer be part of the Microsoft organisation by the end of this month. That will certainly make the front runner job as a P-Seller / vTSP more complicated.  So this blogpost is a bit of goodbye to my Microsoft integration.  It was great because I had direct contact to Microsoft colleagues like Ned Pyle, Ryan Sokolowski, Conor Cunningham, Henk van der Valk, Brian Smith, Chris Buehler, Michael Rys, Vivek Gupta and others. It was a privilege to work with you all.    Since Satya Nadella has become CEO of Microsoft a wind of change has taken over Microsoft. Away is the arrogance and the "we are the be...

I am on Facebook because everybody is there. From friends to people who share the same hobbies and interests, they are all there. I tried Google Plus for a while because it is technically superior to Facebook and does not have any build in commercials (but of cause that can change over night).  The thing is Google Plus does not evolute. It is like Apple: this is what you get and after a few years it just starts to bore. I can not understand people that are still confident with their 10 years old icons ordered in the same way for 10 years. It must be for all people who do not understand the basics of information technology: evolution and sometimes even revolution. For Apple it worked out with their 17% global market share, for Google Plus it did not because it is almost gone. I am just waiting for Google to shut it down. So I move to Facebook. On great thing about Facebook is that they have the like button. People can visit your page and say what they think about it or give comm...

For those who like me tried to run SQL Server 2016 with In-Database R might have run into the same problem as me: In-Database R or the LaunchPad service gives a timeout and won't start. I did several clean installations with different configuration options - for instance I like to put my data on another disk than the system disk - but in the end I tried to do the next, next, next, finish install to see if it something in the setup options is hard coded in there (yes, it happens developers!). For some reason this problem is related to Windows 2016 and not on Windows 2012R2 and I hope the SQL Server team will soon resolve these issues because they are in one word a bit sloppy.  There are 2 issues (maybe even 3 so I give this one also):  The R setup does not create the ExtensibilityLog directory in the "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log" directory The R setup sets the number of users in the SQL Server Launchpad service to 0 it is pos...

Microsoft must have been thinking that the live of a Windows administrator gets so boring that they need a distraction and they have integrated the XBox Live extensions standard in Windows Server 2016.  No kidding: take a look. I did not select it as a feature and it is there. As you can see the XBox Live service is started automatically even in mode manual (see error log).  To my opinion these 3 XBox Live services should never be available on a server. Unless the server is part of the XBox Live platform of cause but honestly I don't think that Microsoft will allow that.  Every IT manager with a serious Windows production environment would fire any administrator playing XBox Live on the production servers. So though it might be tempting don't do it Windows administrator. It is a trap! :-)

Storage Spaces is a component available in Windows 2012. Microsoft has used this technique in their Microsoft Analytics Platform System or previously named SQL Server 2012 Parallel Data Warehouse.  But as I will show they got stuck in the 'old way of thinking' which will only work (slightly?) better if we live in a perfect world, were all data is requested at the same time. In a lot of cases this is not so.... The hardware behind Microsoft Analytics Platform System When looking closer at the hardware the Hewlett and Packard (HP) solution based on the DL360 Gen8, which has 2 PCIe slots, is equipped with one H221 SAS controller with two 4x6 Gb/s ports and one Melanox Infini Band 56 Gb/s controller. In the HP solution the JBOD is divided in two: every node manages 32 disks.  Dell addresses the solution with the PowerEdge R620 which has maximum 3 PCIe slots but 2 JBODs so in order to address all 102 disks in the JBOD's they need to do the same as HP namely one SAS...

This post is based on the tests I did to see if storage spaces in Windows 2012R2 can serve as a platform for our Fast Track environments. When Microsoft developed the Fast Track Data Warehouse architecture, which was at first very limited in hardware choice and for version SQL Server 2012 became a reference guide, Storage Spaces as a functionality in Windows did not exist.That has changed with the release of Windows 2012 and later on with version 2012R2 and soon 2016. Why is Storage Spaces as a storage technology so interesting for SQL Server?  Anyone who is a pro in SQL Server knows that parallelism - adding more disks - can greatly improve performance. Adding an hard disk for the tempdb and another one for the LOG files will do the job if the hard disks perform sufficiently (that might be another issue!). To my opinion (and also to others) SQL Server does not do a great job in using the available hardware. Before and after the installation it is mandatory to tune a SQL Se...

Working with Management Data Warehouse, a reporting tool for Data Warehouses introduced in SQL Server 2008 and never changed since, makes a lot of limitations visible and also the lack of in-depth documentation by Microsoft. They tried to make the documentation better accessible in SQL Server 2012 but the product itself did not change.  What are the main limitations? MDW uses snapshots, which is actually a 'easy' way to handle all the issues involved in collecting ongoing activity. It is like taking 10 pictures per second of a dancing girl: you see the changes in every movement but if you want to know what is the total distance the girl covered then it can get rather complex. So you have for 1 minute of pictures and I bet you will break your teeth on that one. If you want to see the distance the girl danced in a week, month or year go for it! This gets us into the basic question of BI. Why are we collecting data? What do we want to do with it? Apparently taking 10 pictu...

I have been using pure Android now since the Nexus 4. I am on a Nexus 5 now and before I was using Samsung Galaxy S3 and S both on various versions of CyanogenMod, the latest was 11.1. So I  have quiet some experience with the platform and it evolution. To make things very clear: pure Android on the Nexus 5 does not have a longer battery life than 3 hours!  Using your phone as a route planner with mobile data on and the GPS on makes it live barely 1 hour! These are from my real every day experiences.  That is when you watch a video or listen to music (!). The latter I find quiet amazing because processing a video and show it on screen is for sure not the same thing than having the device screen of and listening to music only. Strangely it does. And I used Google Play. Maybe that is the reason. If you put of WiFi and mobile data my Nexus 4 on Android 4 last for 7 days and the Nexus 5 several days only. The reason why their performance on battery life when activ...