John Martin shows that part of securing your environment includes encrypting SQL Server connections:
In order to demonstrate just how easy it can be to get hold of the information inside the TDS packets I will be using Network Monitor from Microsoft, this will capture the network packets sent and allow me to see the details of what is being sent. Other tools such as Wireshark will also provide a level of insight into what is being sent between the application and SQL Server. I have configured three Windows Server 2012 R2 systems, one with the client (SQLCMD), one with SQL Server, and finally one which will act as a router between the two subnets that each server is on. This configuration can be seen below;
Wireshark is a good friend of mine. It should be a good friend of yours, too, but only if your environment allows you to have a packet capture tool installed.
Melissa Yu gives us an introduction to Tableau:
Do you know the difference between a blue pill and a green pill? What happens when you add a green pill to the color shelf? Pills, cards, and shelves are the foundation of Tableau visualizations. If you are new to Tableau or have been playing around with it but not quite sure how to get the view to look the way you want, we have a video for you! In this video, I’ll help you understand pills, cards, and shelves so instead of wondering “Why did Tableau do THAT?” you’ll be in better control of what you want Tableau to do.
Power BI and Reporting Services 2016 are exciting technologies, but there’s a wide world outside of Microsoft, and Tableau is a major player in the world of visualization.
Jason Brimhall digs into an error where page-level locking is disabled:
You receive the error message similar to the following:
Msg 2552, Level 16, State 1, Line 1 The index “blah” (partition 1) on table “blah_blah_blah” cannot be reorganized because page level locking is disabled
Immediately, you start double-checking yourself and verifying that it worked the previous night. You even go so far as to confirm that the same index was previously reorganized. How is it possible that it is failing now on this index. What has changed? Has something changed?
There’s an interesting troubleshooting story, but the important message is about setting up a good set of Extended Events so that you can troubleshoot these types of problems.
Melissa Coates digs into DirectQuery mode on Tabular models:
When developing an SSAS Tabular model, you can choose one of two options for handling the underlying data:
In-Memory Mode (aka Imported Mode). Stores the data in the in-memory model, so all queries are satisfied by the data imported into the Tabular model’s storage engine. This requires the model to be processed for updated data to become available for reporting. This mode is conceptually analogous to MOLAP in SSAS multidimensional models (though the SSAS architecture differs significantly).
DirectQuery Mode. Leaves the data in the source and sends the queries to the underlying database. In this case there’s no processing and SSAS serves as a semantic model to improve the user experience. This mode is conceptually analogous to ROLAP in SSAS multidimensional models (though there are architectural / implementation differences between DirectQuery and ROLAP).
It looks like DirectQuery mode doesn’t fit all circumstances, but there are a few cases in which it makes a lot of sense.
Microsoft quietly announced SQL Server 2016 RC1 is available:
In SQL Server 2016 RC 1, we made enhancements to SQL Server Reporting Services, including:
Updated preview of the new web portal: The new web portal by default, and the classic Report Manager now removed. Additionally, open the Mobile Report Publisher and Report Builder from the new web portal using any modern browser.
Custom branding: Customize the web portal with your organization’s logo and colors.
KPIs and mobile reports: Click a KPI and see a view with more details, and connect KPIs and mobile reports to parameterized datasets.
Modern paginated reports: Design beautifully modern paginated reports with new, modern styles for charts, gauges, maps and other data visualizations.
It looks like Reporting Services is getting to release shape.
Aaron Bertrand looks into how STRING_SPLIT performs compared to other string splitting methods:
So with those limitations out in the open, we can move on to some performance testing. Given Microsoft’s track record with built-in functions that leverage CLR under the covers (cough
FORMAT()cough), I was skeptical about whether this new function could come close to the fastest methods I’d tested to date.Let’s use string splitters to separate comma-separated strings of numbers, this way our new friend JSON can come along and play too. And we’ll say that no list can exceed 8,000 characters, so no
MAXtypes are required, and since they’re numbers, we don’t have to deal with anything exotic like Unicode.
The results are surprising. I expected it to be somewhere around CLR-level, but not way better.
Kendra Little compares and contrasts Live Query Statistics against actual execution plans:
Getting plan details isn’t free. The amount of impact depends on what the query is doing, but there’s a stiff overhead to collecting actual execution plans and to watching live query statistics.
These tools are great for reproing problems and testing outside of production, but don’t time query performance while you’re using them– you’ll get too much skew.
Live Query Statistics is one additional tool, but won’t replace actual execution plans. At its best, it will make you think more about what’s going on with the system, whether row counts are what you’re expecting, and take account of which operators stream data through without blocking (such as nested loop joins) versus those which require all the data before continuing (sorts).
Simon Sabin uses Powershell to install Power BI:
Having recently been having rebuilding my machine I finally decided to automate the process of installing the software I need.
This was a life saver as I was reinstalling a few times to try and figure out why I wasn’t getting sound on my external monitor. So I was gradually uninstalling everything until I found out that it was Hyper-v that was causing the problem.
The outcome meant I was installing PowerBI lots and had to automate it.
This looks like the first step toward a Chocolatey script.
Erik Darling has opened a can of worms here:
WITH ALL THE TROUBLE NULLS CAUSE…
You’d think people would be more inclined to avoid them. Slap a NOT NULL constraint and a default value on your column and call it a day. I’m perfectly fine with bizarro world canary values. If it’s an integer column, some really high (low?) negative number. If it’s date-based, why not have it be the lowest value your choice accomodates?
Check out the comments, definitely. I don’t think it’s as clear-cut as Erik argues; the idea of NULL has been and will remain controversial because it’s a useful concept but one which requires explicit consideration.
Chrissy LeMaire has an update on SQLPS and SQL Server Linux:
Microsoft is investigating options for a cross-platform lightweight SQL Management Studio GUI tool for Linux.
Microsoft is investigating open sourcing the SQL Server PowerShell provider and cmdlets, and that it “makes a lot of sense” and “aligns with what Microsoft has already done with our Azure PowerShell cmdlets on github.” This is being tracked by connect item 2442788.
Microsoft doesn’t have dates or more details to share for any of these items at this time and will keep the community updated on their progress as they continue to evaluate our plans based on customer feedback
I’m most interested in the first of these points, but this is all interesting news. Also check out her guest appearance on the PowerScripting Podcast.
Kevin Feasel
2016-03-21
Security