Roger's BI Blog

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 best" that was p

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 comments

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