The Complete Weekly Roundup of SQL Server News

In this issue:

Vendors/3rd Party Products

Tech News : Security

SQL Server News

Product Reviews

Microsoft News : General Interest

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Computing in the Cloud

Blogs : Data Mining

Blogs : Database Design, Theory and Development

Blogs : Performance and Tuning

Blogs : Professional Development

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : SQL Server 2014

Blogs : T-SQL

Database Weekly - www.databaseweekly.com

The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server News for 2014-07-14

SQL Server replication eBook Get to grips with SQL Server replication
In this FREE eBook, Sebastian Meine guides you through the fundamentals of SQL Server Replication, to the point where you should feel comfortable using it in production. Download the free eBook.
SQL Backup Pro SQL Backup Pro “It just works”
The SQL Backup step-by-step wizards and a centralized GUI take the legwork out of creating, restoring, and verifying your backups. Check it out for yourself with a free trial.
SQL Doc Hate explaining your database in meetings?
SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.
Editorial - Back in the USSRS

Several years ago, more than I want to count, the "Big New Thing" in SQL Server and a life changer, for me at least, was SQL Server Reporting Services. I recognized it immediately as a tool that filled many gaps between the data entry and data delivery components of SQL Server. Plus, it was free (disclaimers apply), so I knew straight away that SSRS and I were going to get to know each other well, and over the ensuing years we did. Perhaps a little too well.

My company adopted it entirely for all reporting requirements, and I became engrossed, developing more reports than I care to count, and writing several books on the topic. In the beginning it was a lot of fun. Sure, there were many time I'd find myself shouting at the screen in disbelief, "Huh!? What happened there?" but it was exciting to hack out solutions, at least in the early days, and rewarding when I finally got the report working and looking exactly as I wanted.

Over time, however, there seemed to be less singing, more shouting. It felt like most times I developed a new report I was having to find some new hack to get it to work just how I wanted. Sure, I always managed to get there in the end, but it was generally painful.

And then, suddenly, I changed roles and we parted ways, SSRS and I. Sure, we still conversed, occasionally, when someone else had an issue with a report they were creating, or I needed to bash out a quick chart, but that was rare, and before I met my new friend, PowerView. I had not developed a full blown SSRS report in over 2 years.

Then one day, very recently in fact, I took a new position. Faced with an important documentation project, I felt mixed emotions as it occurred to me that SSRS might be the tool for the job. So I jumped back in, back in the USSRS.

Boy this product sure had come a long way…so many new features and stellar new charts and maps. I'd been away so long I hardly knew the place! Twenty minutes into developing my new report, I was smiling broadly. I had all my data into a lovely-looking line chart and just needed to add the required filters. Add the first filter value, test it out, all good. Add the second filter value and…NO DATA AVAILABLE…

"Huh?! What happened there?"

I removed the second filter value and we were back working again. I tried loading the two filter values in one element using IN. No go. I tried a SPLIT function and, while that may or may not have "worked", I stopped the report after it ran for 5 minutes without returning any results. I rolled up my sleeves and, a short period of hacking later, got it to work by using BETWEEN, but that was really only because luck was on my side; my filter values started with a "U" and a "T" and there were no other values between them.

I sat back and smiled ruefully. Much had changed, but much had stayed the same. I am going to keep at it with SSRS, because it definitely still serves a purpose for me, but this time, rather than just hacking and pressing on, I'm going to report these issues to Microsoft and hope they continue supporting and improving the tool. Heck, one day they might even incorporate Word, to provide baked-in support for rich text formatting. Now that really would make my life back in the USSRS much easier. Ring out you balalaikas!

Rodney Landrum (Guest Editor)

» Join the debate, and respond to today's editorial on the forums


The Weekly News

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Vendors/3rd Party Products

Setting up continuous integration for your database with Red Gate and AppVeyor - Jonathan Hickford walks through setting up a hosted continuous integration (CI) system for your SQL Server database....(more)

SQL Monitor Custom Metric: Memory used by ad hoc queries running once - This metric measures the amount of memory used by the total number of ad hoc queries in the plan cache that have only run one time. This value is only accurate for the instant the query is run, and the value can change radically from one capture time to the next....(more)

Tech News : Security

Hacker's App Automates Over-The-Shoulder iPad Spying - Simply pretend to be fiddling with your phone or tablet a few meters away from an iPad user while he or she enters their PIN or password, and shoulderPad can uses your device’s camera to read and interpret the target’s keystrokes....(more)

SQL Server News

End of Mainstream support for SQL Server 2008 and SQL Server 2008 R2 - We would like to remind all customers that Mainstream Support for SQL Server 2008 and SQL Server 2008 R2 has ended on July 8, 2014. For both SQL Server 2008 and SQL Server 2008 R2, Microsoft will continue to provide technical support which also includes security updates during the duration of extended support. ...(more)

Product Reviews

Professional Microsoft SQL Server 2014 Integration Services Book Review - This is a revised version of a popular SSIS book, updated for SQL Server 2014. So how does it fare?...(more)

Microsoft News : General Interest

Nadella on Microsoft's culture change: 'Nothing is off the table' - Microsoft staffers worldwide should buckle their seat belts because a big culture shakeup is in the works at the company. In a long letter sent to employees on Thursday, CEO Satya Nadella said that he and his lieutenants are taking "important steps to visibly change our culture."...(more)

Microsoft to roll out new Azure StorSimple cloud storage arrays - StoreSimple is a cloud-integrated storage (CiS) product that allows users to store heavily used data on-premises/locally and put older and lesser-used data in the cloud....(more)

Blogs : Administration

How to change TempDB system Database files location? - A quick stepwise guide....(more)

Performance and Stability Related Fixes in Post-SQL Server 2012 SP1 Builds - If you are running SQL Server 2012, I really think you should be running the latest SQL Server 2012 Service Pack and Cumulative Update. Right now, that means Service Pack 1, CU10 (Build 11.0.3431). I am advising people to hold off on SQL Server 2012 Service Pack 2 until SQL Server 2012 SP2 CU1 is released....(more)

Adding Another Transaction Log File To Gain Performance - Thomas Kejser explains a very rare case where adding a second transaction log to the same database makes sense from a performance perspective. ...(more)

Worst Day of a DBAs Life - Grant Fritchey has yet to put a company out of business or kill anyone with any errors he's made, although has worked at places where either was possible......(more)

Oracle Terminology for the SQL Server DBA - At some point you’re going to be confronted with an Oracle installation or even just an Oracle DBA. Communicating with a DBA who works on a different product can be difficult, it’s like speaking US English and having a conversation with a native English speaker from Scotland. The words are the same, but they have different meanings....(more)

Blogs : Analysis Services / BI

Optimising MDX Calculations With The Unorder() Function - The Unorder() function is probably one of the least used functions in the whole of MDX...but Chris Webb discovers that it does have its uses…...(more)

Blogs : Backup and Recovery

Solving Errors with Existing BLOB Leases When Using BACKUP TO URL - Greg Low on BACKUP TO URL and the dreaded "nonrecoverable I/O error"....(more)

Blogs : Computing in the Cloud

Using the Microsoft Azure IaaS Cost Estimator Tool - Have a look at the Microsoft Azure pricing calculator and you’ll end up needing a bandage to cover the damage you’ve done to your head from scratching. To help simplify things, Microsoft has released a new tool called the Microsoft Azure (IaaS) Cost Estimator Tool. This should be a great tool. I’ll walk you through using this tool in this article and then have a look at a problem I noticed....(more)

Microsoft Machine Learning – Data Driven Intelligence - Microsoft will be releasing for Preview a Machine Learning tool that will be built on Microsoft Azure, providing organizations the ability to build data models, analyze data, and share information across a broad range of users within an enterprise....(more)

Azure Update: SQL Database: Easy upgrade to new service tiers, performance improvements, PITR for Basic, and Automated Export for all service tiers - Tobias Ternstrom provides an update on the new SQL Database Basic and Standard and Premium service tiers, including the ability to export a logical backup of a database periodically on a schedule....(more)

Blogs : Data Mining

A tiny research team at Tableau is building tomorrow’s UX for data - Tableau Software is many things: a fast-growing thorn in the side of legacy analytics vendors, stock-market gold and the poster child for the next generation of user-friendly data analysis, among them....(more)

Blogs : Database Design, Theory and Development

My Relationships Have All Dried Up - DRY amounts to saying, "for each of the entity types, code and data, within each type build orthogonally". Yet, by building DRI into a SQL database (as close to the RM as we can currently get), one gets as much DRY as it is possible. Eliminates a tonne of code, too....(more)

How In-Memory Database Objects Affect Database Design: Uniqueness - Louis Davidson on why the lack of constraints is one of his least favorite parts of the new in-memory structures....(more)

Blogs : Performance and Tuning

TPC-H: Data And Query Generation - In an attempt at making TPC-H more accessible, I have created a new GIT repo on Bitbucket that should make it a easier to build TPC-H. It is work in progress, but the build scripts now work well with Postgres on OSX (which means it is likely to work on all FreeBSD and Linux variants)....(more)

Comparing Performance Counters - A common TSG step is to look at some perf counter, wait n minutes, then look at it again. When we’re talking about pools, it gets a little more involved. PerfMon.exe isn’t the most easy to automate (you can save the counterset to an .msc, then copy that file across machines, but you still have to log into the machine to get the local data.)...(more)

Examining OLEDB Waits - Generally we will see OLEDB waits when we make a call to an OLE DB provider and we are waiting on our data. OLEDB waits happen a lot in environments that use linked servers....(more)

Shift Left Performance Testing - a Different Approach - A different approach to traditional Multi User Performance testing; using the same tools but combine them with modern data visualization techniques to gain early insight into location specific performance and application areas that may have "sleeping" performance issues....(more)

Join Row Estimation in Query Optimization - The new cardinality estimator in SQL Server 2014 is helpful in resolving known issues, but has little to improve row estimates after the initial access at the data source beyond fixed rules which may be more generally true than the rule used before....(more)

Finding All the Red M&Ms: A Story of Indexes and Full-Table Scans - Chris Saxon explains a very important topic using an analogy with chocolates: When does a database use an index and when is it better not using it. ...(more)

Stabilizing Execution Plans: Plan Guides and NORECOMPUTE - Sometimes you end up in a good plan / bad plan situation: an important query runs just fine most of the time. The query is parameterized, a good execution plan gets re-used, everything is cool. But sometimes, a “bad plan” gets compiled and starts to be reused. This is “bad” parameter sniffing. ...(more)

Blogs : Professional Development

Largest collection of FREE Microsoft eBooks ever... - Including: Windows 8.1, Windows 8, Windows 7, Office 2013, Office 365, Office 2010, SharePoint 2013, Dynamics CRM, PowerShell, Exchange Server, Lync 2013, System Center, Azure, Cloud, SQL Server, and much more. Longest URL for an article ever too?...(more)

Blogs : Reporting Services

Creating a Matrix Infographic in SSRS - Taking inspiration from the sort of “Matrix Infographic” used to display forecasts of World cup matches, Koen Verbeeck attempts something similar using SSRS....(more)

Power View Can't Load Power Pivot Models - You have created an SSRS application in SharePoint and specified that the application pool would use a specific domain service account that is designated to SSRS. When you attempt to create a Power View report from a deployed Power Pivot model, you get an error......(more)

Blogs : Security and Auditing

db_ddladmin and the SSMS table designer - If you want to grant a user the ability to create/alter/delete any table, SP, function etc in a database...adding the user to the db_ddladmin role is the way to go. But there is a bug in the SSMS table designer in SSMS 2008R2 that will not allow a user that is not at least a member of the db_owner role to modify tables....(more)

Crypto primer - In cryptography discussions, 4 characters are commonly used: Alice, Bob, Eve, and Mallory. Alice and Bob want to send messages to each other. However, Alice and Bob are not alone in the world....(more)

Blogs : SQL Server 2014

Columnstore Table Analyzer - Partick Keisler provides a script that will output an organized list of tables, along with the column name and data type, that cannot be used within a Columnstore index....(more)

Blogs : T-SQL

Calculating and Verifying Check Digits in T-SQL - A lot of numbers that we use everyday such as Bank Card numbers, Identification numbers, and ISBN codes, have check digits. As part of the routine data cleansing of such codes we must check that the code is valid- but do we? Dwain Camps shows how it can be done in SQL in such a way that it could even be used in a constraint, and keep bad data out of the database....(more)

Another Way to View Automatic Updates to Statistics - Behind the visuals that make it easier to dissect plans, Plan Explorer also offers the ability to execute a query and view the actual plan (rather than running it in Management Studio and saving it off). And on top of that, when you run the plan from PE, there is additional information captured that can be useful....(more)

T-SQL Window Functions – Part 3: Aggregate Functions - Part 3 of Steve Hughes' series on SQL window functions explores using aggregation functions with T-SQL windows....(more)

Step one in producing Shared Access Signatures for SQL Server Data Files in Azure - Generating a SHA2 256 HMAC Hash using Native T-SQL - Greg Low reports on his efforts to to create a shared access signature using native T-SQL. Shared access signatures are needed when working with SQL Server data files in Azure. Mostly, DBAs would use a programmatic utility such as Azure Storage Explorer to generate these signatures....(more)

Slow query using non-deterministic user defined function - Jack Li reports on his work with a customer who reported a query that used to run a few seconds in SQL Server 2000 but it never finishes in SQL Server 2008 R2 following upgrade....(more)

SSIS Lookup transformation in T-SQL - There is no equivalent to the SSIS Lookup transformation in T-SQL – but there is a workaround if you’re careful. Rob Farley explains....(more)

ASCII Value Character Counts Procedure - Faced with an issue where NUL characters (ASCII value 0) were stored and were producing unexpected results, sql solace wrote a stored procedure to determine the ASCII values being stored inside the data. ...(more)

T-SQL Tuesday - Assumptions, and Wisdom - This month's T-SQL Tuesday topic: assumptions, things that are accepted as true, without proof....(more)


Administrative