Press "Enter" to skip to content

Author: Kevin Feasel

Using WinDocks

Published 2016-12-08 by Kevin Feasel

Andrew Pruski demonstrates WinDocks, for people without Windows Server 2016 available:

So the first thing to do is get a new server with Windows Server 2012 R2 installed. Then once that’s up and running, you need to install SQL Server…

…wait, what??

The WinDocks software is different from the previous docker software that we’ve worked with in that it needs an instance of SQL installed on the host in order to use it’s binaries to create SQL within the containers. The instance won’t need to be running, it just needs to be installed.

Check out WinDocks; it’s focused around Dockerizing older versions of SQL Server.

Comments closed

Backup Encryption

Published 2016-12-08 by Kevin Feasel

Daniel Jones shows how to use backup encryption in SQL Server:

The backup encryption in SQL server is needed due to following reasons:

  • Way to Keep Database File Secure: Users need to encrypt SQL server database backup files because this procedure provides complete security to copy of SQL server data. This security measure will keep transaction logs, tables, and other server data safe from any person, who wants to make use of these data in wrong manner.

  • Accessed Only By Authorized Person: It is impossible to restore an encrypted backup file, if a person is not having certificate or asymmetric key for decryption. Therefore, it means that only authorized persons who are knowing credentials of encrypted backup file can restore data with its full access.

Encrypting backups (and storing the encryption key somewhere independent of the backups themselves) can help prevent a very bad day.

Comments closed

Debugging LINQ

Published 2016-12-07 by Kevin Feasel

Michael Sorens has an article on using OzCode to debug LINQ statements:

OzCode’s new LINQ debugging capability is tremendous, no doubt about it. But it is not a panacea; it is still constrained by Visual Studio’s own modeling capability. As a case in point, Figure 17 shows another example from my earlier article. This code comes from an open-source application I wrote called HostSwitcher. In a nutshell, HostSwitcher lets you re-route entries in your hosts file with a single click on the context menu attached to the icon in the system tray. I discussed the LINQ debugging aspects of this code in the same article I mentioned previously, LINQ Secrets Revealed: Chaining and Debugging, but if you want a full understanding of the entire HostSwitcher application, see my other article that discusses it at length, Creating Tray Applications in .NET: A Practical Guide.

This is quite interesting.  My big problem with LINQ in the past was that Visual Studio’s debugger treated a LINQ statement as a black box, so if you got anything wrong inside a long chain of commands, good luck figuring it out.  This lowers that barrier a bit, and once you get really comfortable with LINQ, it’s time to give F# a try.

Comments closed

The Central Limit Theorem

Published 2016-12-07 by Kevin Feasel

Vincent Granville explains the central limit theorem:

The theorem discussed here is the central limit theorem. It states that if you average a large number of well behaved observations or errors, eventually, once normalized appropriately, it has a standard normal distribution. Despite the fact that we are dealing here with a more advanced and exciting version of this theorem (discussing the Liapounov condition), this article is very applied, and can be understood by high school students.

In short, we are dealing here with a not-so-well-behaved framework, and we show that even in that case, the limiting distribution of the “average” can be normal (Gaussian.). More precisely, we show when it is and when it is not normal, based on simulations and non-standard (but easy to understand) statistical tests.

Read on for more details.

Comments closed

Checking On Stats

Published 2016-12-07 by Kevin Feasel

Kendra Little has a script to get vital information regarding a table’s statistics:

For most modern versions of SQL Server, I like to join to sys.dm_db_stats_properties() — you can get a LOT of detail in a single query! (This works with SQL Server 2008 R2 SP2+ / SQL Server 2012 SP1+ / All higher versions)

Here’s the query, looking at a sample table in the WideWorldImporters database:

Click through for the script, as well as a version which works on 2005 and 2008.

Comments closed

Broken References In SSISDB

Published 2016-12-07 by Kevin Feasel

Andy Leonard explains how broken environment references can come into being within the SSIS Catalog:

If the reference was broken after the SSIS package execution was scheduled, we may see an error similar to that shown below in the SQL Agent log for the job step that attempted to execute the SSIS package:

Failed to execute IS server package because of error 0x80131904. Server: vmSql16\Test, Package path: \SSISDB\Test\ParametersTest\SensitiveTest.dtsx, Environment reference Id: 35.  Description: The environment ‘env2’ does not exist or you have not been granted the appropriate permissions to access it.

Andy has an explanation of what these are, how you might find them, and how to fix them.

Comments closed

Sankey Custom Visual

Published 2016-12-07 by Kevin Feasel

Devin Knight looks at the Sankey visual in Power BI:

In this module you will learn how to use the Sankey Power BI Custom Visual.  The Sankey is a type of diagram that visualizes the flow of data between a source and destination.

Sankey diagrams are among the most information-dense diagrams out there.  They aren’t general-purpose diagrams, but for someone willing to take the time to unpack them, they can be quite informative.

Comments closed

R + Power Query

Published 2016-12-07 by Kevin Feasel

Ryan Wade makes his argument that R can be more powerful than M inside Power Query:

I want to leave you with two more things. If you look at the trade balance data set you will notice that it is not in a good format for data analysis. Here is a link to the file if you want to take a closer look. When you are doing data analysis you want your data to be in a “tidy” format. A “tidy” format means that each column represents a variable and each row represents an observation. To make this data set “tidy” you need to reformat the data into the following format: Country, Year, Trade Balance, Exports, and Imports.

This was an interesting example.

Comments closed

Build A Python App Which Connects To SQL Server

Published 2016-12-07 by Kevin Feasel

Steve Jones walks through a Python tutorial:

However, there were other errors, which I suspect are related to Python 2.7 v Pyhton 3.5. Rather than solve those, I went on to the columnstore demo. In this, you create a table with 5mm rows and then run a query against it from Python. I did that, then created the columnstore index, then ran it again. The results are below.

And within an hour or so of starting, Steve has hit the 2.x vs 3.x mess in Python.

Comments closed