Arun Sirpal gets started on T-SQL Tuesday a little early:
Cannot find server certificate with thumbprint ‘0xC0367AC8E9AE54538C17ACB0F63070D6FF21316A’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.The dreaded cannot find server certificate with thumbprint message. Why is this happening?
Read on for the solution.
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.
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.
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.
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.
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.
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.
James Serra links to the Azure Total Cost of Ownership calculator:
For a long time clients would ask me how to determine the cost savings by migrating their applications and databases to Azure. I never had a good answer until now: The Total Cost of Ownership (TCO) Calculator. Now in preview, just provide a brief description of your on-premises environment to get an instant estimate of the cost savings you can realize by migrating your application workloads to Microsoft Azure
I think this is a useful start, though a big part of the value I see in Azure is moving certain things from IaaS to PaaS, like getting rid of the web servers and going to Azure Websites.
Regis Baccaro shows how to allow non-domain admins to configure Kerberos Constrained Delegation:
Now I need to add some special permissions to computer objects, so I click Add again. Once again, I’ll select the DBA group, then I need to switch to Descendant Computer objects. I click Write and then scroll down until I see Validated write to service principal name. I’ll click the box to enable it, and then OK, OK, and OK.
The end result looks like below :
2 permissions for DBA group,
All descendants objects : Write all properties
Descendant computer objects : Validate write to Service Principal Name
Regis has the whole process documented well, so check it out.
Brent Ozar has a short test on effective RPO and RTO:
You can’t repair the corruption (it’s a clustered index, and there aren’t enough nonclustered indexes to cover all the columns), and the business needs that data back. You’re on: answer these questions:
Click through for the questions. They’re definitely worth thinking about.
Kevin Feasel
2016-12-07
Backups, Security, T-SQL Tuesday