A Fine Slice Of SQL Server
Kennie Nybo Pontoppidan has a roundup for this week’s T-SQL Tuesday:
Last duty for me as a T-SQL Tuesday host is to write up a summary of all the posts. Soooo, here we go…
Kennie goes the extra mile and summarizes each story with a moral.
Comments closedArun Sirpal shows that Transparent Data Encryption will also lead to encrypted backups:
Ok great let’s check this backup file using a cool tool (XVI32). I really want the contact number of a guy called SQLDOUBLEG because I need his help tuning my SQL Servers so I go looking for a text string, hopefully the phone number will be close by.
As Arun points out, you don’t need TDE to get encrypted backups, but it does the job.
Comments closedSander Stad shows how to test database restorations en masse using dbatools:
Testing your backups is a tedious job and it takes a lot of time which I as a DBA don’t have. I don’t have the time to restore a database, run a DBCC command for every database that’s backed up.
There is a solution and it’s called “Test-DbaLastBackup” which is part of the dbatools module.
Rob Sewell also has a recent post on the topic.
Comments closedRob Sewell shows how to restore a slew of databases using one dbatools command:
In my lab I had installed SQL 2016 on a server running Server 2016 TP5 which expired so I needed to re-install Windows and therefore needed to restore all of my user databases again. This was so easy using the dbatools module that I thought it was worth sharing to show how easy your disaster recovery process could be.
Having re-installed Windows and SQL and copied the backup files back to the server (although I could have used a network location), I then had to restore all of the user databases.
This is how I restored all of my user databases using the dbatools module command Restore-SQLBackupFromDirectory
Read on for the answer, as well as a warning that the upcoming dbatools 1.0 release may change some things.
Comments closedMatt Allington reports that Power BI Desktop supports a new type of matrix (in preview):
Expand, Collapse, Drill and Filter
Expand and collapse behaves just like a pivot table however with a slightly different UI. The new matrix experience is however entirely consistent with the chart drill experience so it is very intuitive.
The new cross filter behaviour is of course not possible in a regular pivot table in Excel (without VBA). You can select any column, row or cell in the matrix and it will cross drill the other visuals on the canvas as can be seen above.
This looks like an interesting change, and Matt shows how to enable the preview.
Comments closedWe setup our nice new SQL 2016 SSRS servers to authenticate using the new SQL 2016 GUI which just supports it now. One problem with the GUI is that it requires that you use encryption. No big deal, SendGrid supports encrypted SMTP.
The problem with SendGrids encrypted SMTP is that it has a funky certificate chain which isn’t trusted by default. And it’s a convoluted process to get the correct certificate chain installed on a few servers (we have an SSRS farm as there’s thousands of reports being delivered every morning).
Click through for the solution.
Comments closedSometimes we get lucky comparing a literal value to a column of a different type.
But this is very complicated, and joining on two columns of different types in the same family without explicitly converting the type of one of the columns resulted in worse performance in Paul White’s tests, when the columns allowed NULLs! (Note: I haven’t rerun those tests on 2016, but I think the general advice below still applies.)
General advice: don’t rely on being lucky. Pay attention to your data types, and compare values of the same data type wherever possible.
That’s great advice.
Comments closedDave Turpin diagnoses an issue where scheduled Policy-Based Management policy checks were failing:
While it is easy to build and test policies by executing them on demand (especially powerful when run through Central Management Server) I had some issues getting my policies to run in “on schedule” mode.
To be more specific, my policies that use the ExecuteSQL function have been an issue. What I was finding was:
- The policy would run fine “on demand” but…
- When I run the policy through the PBM scheduler, the policy would fail.
Dealing with false positives is not a good start for any monitoring service, so getting to the root of the issue was critical.
Read on for the solution.
Comments closedJames Serra talks about Polybase use cases:
For federated queries: “N” requires all data from the source to be copied into SQL Server 2016 and then filtered. For “Y”, the query is pushed down into the data source and only the results are returned back, which can be much faster for large amounts of data.
I mention “Maybe” for age out data in SQL DW as you can use PolyBase to access the aged-out data in blob or Azure Data Lake Storage (ADLS), but it will have to import all the data so may have slower performance (which is usually ok for accessing data that is aged-out). For SQL Server 2016, it will have to import the data unless you use HDP/Cloudera, in which case the creation of the MapReduce job will add overhead.
The thing that I like about this chart is that the new Polybase sources (SQL Server, Oracle, Teradata, Mongo, and generic ODBC) do support predicate pushdown. For large data sets, that’s huge: it lets the database engine on the opposite end do as much filtering as possible before sending results back to your SQL Server head node.
Comments closedTomaz Kastrun uses the igraph package to graph package dependencies in R:
With importing package tools, we get many useful functions to find additional information on packages.
Function package.dependencies() parses and check dependencies of a package in current environment. Function package_dependencies() (with underscore and not dot) will find all dependent and reverse dependent packages.
This probably tilts more toward “fun” than “practical,” but this will let you see the full set of dependencies for a package if, for example, you need to grab all of these packages for upgrading an offline instance.
Comments closed