Press "Enter" to skip to content

Day: August 29, 2016

Registering U-SQL Assemblies

Published 2016-08-29 by Kevin Feasel

Michael Rys shows how to register an assembly in the U-SQL catalog:

While the ADL Tools in VisualStudio make it easy to register an assembly, you can also do it with a script (in the same way that the tools do it for you) if you are for example developing on a different platform, have already compiled assemblies that you just want to upload and register. You basically follow the following steps:

You upload your assembly dll and all additionally required non-system dlls and resource files into a location of your choosing in your Azure Data Lake Storage account or even a Windows Azure Blob Store account that is linked to your Azure Data Lake account. You can use any of the many upload tools available to you (e.g., Powershell commands, VisualStudio’s ADL Tool Data Lake Explorer upload, your favorite SDK’s upload command or through the Azure Portal).

 

  1. Once you have uploaded the dlls, you use the CREATE ASSEMBLY statements to register them.

We will use this approach in the spatial example below.

There’s quite a bit going on in this post, making it an interesting read.

Comments closed

Power BI Line Charts

Published 2016-08-29 by Kevin Feasel

Reza Rad digs into line charts:

Value Line

Line chart can show one or more measures as measures, such as Sales Amount, Total Costs, Quantity of the goods sold and etc.

Trend Line

Depends on the variety of values across a time period, Line chart can illustrate a straight line as a trend. This trend is good to understand how in overall products are selling, is revenue going up or down for example.

Reference Line

You might want to define minimum, maximum, average, or median values for your line chart as separate lines, and compare values lines with these lines. These are reference lines which can be dynamically created based on value of measures in the chart.

I think this was a rather helpful post in figuring out what Power BI is capable of doing, although if you build a lot of charts that look like Reza’s last example, you probably want to scale that back a bit.

Comments closed

Ambari Metrics Collector Error

Published 2016-08-29 by Kevin Feasel

Jon Morisi had to troubleshoot an issue with the Ambari metrics collector not starting:

Last week I had a bit of a trial by fire:
“Here’s a 7 node, Hortonworks Hadoop cluster, metrics is broken, fix it, go!”

The initial indication that metrics was broken was apparent in the Services tab for Ambari Metrics.  Here it showed that there was an error and that Metrics Collector was Stopped.  The error however wasn’t very informative:

Connection failed: [Errno 111] Connection refused…

That didn’t tell me much at all, and neither did googling.
(I hope the title of this blog helps someone else find this solution quicker.)

Jon includes the answer and some additional helpful details.  Check it out.

Comments closed

Live Query Stats Progress Changes

Published 2016-08-29 by Kevin Feasel

Pedro Lopes mentions that the latest version of Live Query Stats in SSMS 2016 has some improvements:

Below the finished query. Again, overall query execution cannot go over 100%, but at the operator level, percentages are shown as the real ratio between actual and estimated rows, with no caps.

So you can see how the actual rows from the clustered index scan on PhoneNumberType table was 14500% above estimations, and how a series of severe misestimations are coming from the bottom right area of the plan (where actual is not even 1% of estimated rows), worsening as it goes up in the nodes.

I think this makes Live Query Stats a better tool for query analysis.  I haven’t used it much in production, but this makes me want to give it another try.

Comments closed

Running CheckDB On Log Shipping Subscribers

Published 2016-08-29 by Kevin Feasel

Brent Ozar shows how to support running DBCC CHECKDB on a log shipping subscriber:

 

At first glance, if you set up log shipping the way most folks do, it looks like you can’t run CHECKDB on a subscriber:

On SQL Server Enterprise Edition, you can try working around it by creating a database snapshot – but that won’t work either:

Here’s the trick: your database needs to be in standby mode instead of norecovery. When you run restores with the standby option, you’re able to query the database in between restores.

This doesn’t obviate the need for running CHECKDB on a primary, but it can offload some of that work some of the time.

Comments closed

CISL 1.3.1

Published 2016-08-29 by Kevin Feasel

Niko Neugebauer has released the newest version of his columnstore index library:

Here is the small description of what is new in this release:

  • The database snapshots (.dacpac) for all platforms are now included in the Releases\DacPacs.

  • Includes new Powershell functions for installing and removing CISL from the Instances:

    • Install-CISL.ps1 will allow you to install the CISL at multiple databases of a SQ Server Instance (or Azure SQLDB).
    • Remove-CISL.ps1 will allow you to remove the CISL from the multiple databases of a SQL Server Instance (or Azure SQLDB).

  • Support for the different collation is included.

  • Includes information on all recent SQL Server updates.

  • Included support of the new Columnstore Indexes Trace Flags in SQL Server 2016.

  • Basic Unit Tests (based on t-sqlt) are included for SQL Server 2012 & SQL Server 2014, guaranteeing the quality of the released code.

  • A good number of bug fixes.

  • Further parameter enhancements for the existing functions.

Sounds like there’s a lot packed into this release.

Comments closed

Deploying SSIS Packages In VS 2015

Published 2016-08-29 by Kevin Feasel

Neil Gelder notes that you can deploy different versions of SSIS packages using Visual Studio 2015:

For years I’ve dream’t of having one set of tools for developing SSIS packages! not a lot to ask really and  great step towards this from Microsoft was decoupling the development IDE from the main SQL Server install to produce the standalone SSDT (SQL Server data tools)

But like most people I work in an environment which has legacy versions for SQL Server in production, but equally like most tech folk (giddy kids wanting new toys) I always try and use the most current and exciting  version of VS.  This however proves a problem when developing for SSIS, for example if you developed a SSIS package in VS 2013 you’d not be able to deploy this correctly to a SQL Server 2012 version of Integration services catalog.  In the past this resulted in having two IDE’s installed, SSDT 2012 (VS shell) for any 2012 catalog development and VS 2013 installed for other work.

I had one person mention during a talk I gave that this isn’t foolproof, but my experience (limited to SQL Server 2012 and 2014) was that deployment worked fine.  As always, test before making changes.

Comments closed

Troubleshooting Data Factory Errors

Published 2016-08-29 by Kevin Feasel

Ginger Grant discusses Azure Data Factory errors:

Unfortunately, while developing Data Factory I became very familiar with errors. All of the errors show up at the end and provide very little insight as to what in the process failed. Here’s an example.

Database operation failed on server ‘Sink:DBName01.database.windows.net’ with SQL Error Number ‘40197’. Error message from database execution : The service has encountered an error processing your request. Please try again. Error code 4815. A severe error occurred on the current command. The results, if any, should be discarded.

This sounds like classic Microsoft error messages:  “An error occurred.  Here is a code you can put into Google and hope desperately that someone has already figured out the answer.  Good luck!”

Comments closed

Thoughts On Linked Servers

Published 2016-08-29 by Kevin Feasel

Dave Mason defends the honor of linked servers:

I seem to be in the minority when it comes to SQL Server linked servers. When it’s another SQL Server instance on the other end, I quite like them for administrative purposes. But other SQL pros have some reservations and gripes. I’ve even seen the word “hate” thrown around freely. Most of the complaints seem to fall into one of these categories: poor performance, insufficient permissions, poorly configured security, and challenges related to remote execution of queries.

I think Dave’s reasoning makes a lot of sense.  Linked servers are not themselves evil.  I think it’s likely a mistake to incorporate them into your mainline application (a mistake I’ve made in the past), but for the kinds of administrative tasks Dave mentions, it’s certainly not a bad idea.

Comments closed

Thoughts On Dynamic Data Masking

Published 2016-08-29 by Kevin Feasel

John Martin on Dynamic Data Masking:

For the rest of this blog post, I will be working with the following scenario:

  • I have an SSRS Server, hosting a number of reports that display information about my SQL Server estate. From performance metrics through to details of failed jobs and poorly performing queries. I want to add an additional layer of security, restricting who can see the names of servers, databases, and other internal infrastructure information. Permission to view these reports will be granted to both support teams and business users, with the business users not being permitted to see the sensitive data.

John is much more optimistic about this feature than I am.

Comments closed