The Complete Weekly Roundup of SQL Server News

In this issue:

Tech News : Security

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Computing in the Cloud

Blogs : DMO/SMO/Powershell

Blogs : Performance and Tuning

Blogs : Professional Development

Blogs : Reporting Services

Blogs : Security and Auditing

Blogs : T-SQL

Articles : Administration

Articles : Development

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 2013-12-02

SQL Surce Control Get your SQL Server database under version control now!
Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…
SQL Backup Pro Logo "A real time saver" Andy Doyle, Head of IT Services
Andy and his team saved time by automating backup and restores with SQL Backup Pro. Find out how much time you could save. Download a free trial now.
SQL Prompt FREE eBook – "45 Database Performance Tips for Developers"
Improve your database performance with 45 tips from SQL Server MVPs and industry experts. Get the eBook here.
Editorial - Going over their heads: IT Decisions

Marketing people find it hard to puzzle out the strange osmotic processes that determine IT decision-making in large companies. There are, of course, companies that have no decision-making process beyond calling the fire-brigade when smoke fills the room. The decision-process is so obscure that, In IT marketing generally, there is a persistent fantasy that one can make a huge sale by a direct appeal to the top, rather like a fairy-tale where one appeals directly to the King.

Serf: Sire! (Kneels) “With this new cross-bow I have invented, the forthcoming battle will end in a glorious victory”

…Fantasy…

King: “You speak the truth. (Aside, to the Lord Chancellor) Dress this man in finest raiment, press gold sovereigns into his hand, and make sure we have these excellent crossbows.”

…Reality…

King: (paraphrased) “Whatever. Where’s lunch?” (Sound of Serf being dragged away)

Likewise, the idea that a CIO should show leadership in the technical direction of the company is ridiculous. The modern fairy-tale involves the CIO being awash with data. I quote:

“The data deluge is here, and CIOs must invest in the applications and systems that enable them to turn increasing amounts of data into actionable intelligence and strategic insight—or run the risk of drowning in the data flood.”

And:

“The current flood of data is at biblical proportions”

This marketing message, whilst pretending to inform, instead attempts to convince the CIOs that their infrastructure is ill-equipped to deal with the data from social media, test equipment, websites, and so on and we should instead open the wallet for the new range of kit: This will make the CIO, in political-speak, the 'flag-carrier'.

CIO: (looking around the table at the management meeting) "Look, guys, we're awash with data and (glances at notes) need to invest heavily in serious big data infrastructure…"

…Fantasy…

All: Yes, how else do we turn all this valuable data into the actionable intelligence and strategic insights that will drive us forward?"

…Reality…

CTO: "The Data people say that it isn’t data, it is noise. It is as likely to hold the key to a better corporate strategy as the mould on that unwashed coffee-cup holding the successor to penicillin".

Ops Manager: “And we wouldn’t want to support a solution before we’d had a full evaluation of the cost of maintaining it. Anyway, we are fully occupied with ISO27001 compliance for the next six months.”

HR: “The training cost for all those self-service BI tools and fancy visualisations is going to make the eyes water. Finance is going to hate this”.

And so on, getting gradually more depressing.

So what makes the reality so different? Unless a technology serves to answer a pressing need, such as R, SSDs, JSON, REST or MVC, it will always be a struggle to convince the market that they can’t be without it. You might convince the odd CIO in the same way that a poacher occasionally hits a bird with a blunderbuss: but it is sheer luck: There is little he can do anyway. The CIO's task is to gather experts into one’s team and get them to cooperate to determine a technical direction, even if, at times, it seems silly, conservative, or fanboi. The CIO in a large company can do little to affect the technical direction beyond getting it reviewed by the rest of the team.

Technology doesn’t sell itself. There has to be a good consensus that it is worthwhile, and it is ironic that the culture of IT is so habitually conservative. It can be convinced, but it isn't easy.

» 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.

Tech News : Security

European Parliament's network hacked; public Wi-Fi shutdown - The European Parliament has shut down its public Wi-Fi network in Strasbourg after a hacker was found to have "captured the communication" between smartphones and tablets. A man-in-the-middle attack was discovered that led to some staff mailboxes being compromised. EU IT staff will begin installing certificates on all devices that staff use to access internal IT systems, including email. ...(more)

Blogs : Administration

Lines In The Sand: Discovering HammerDB Benchmarking - Want to measure Database storage performance? Take a look at SQLIO or IOmeter. Need a full end testing suite? Paid tools like LoadRunner and Benchmark Factory can help you. HammerDB is a handy tool that allows you to run a TPC-C benchmark against your instances(and not just SQL Server!). I have become a big fan of HammerDB. ...(more)

Wasted Space Per Database For Fill Factor: Quick Tip - I keep finding interesting Metrics to look into. Looking my own stuff, I've found 300GB of space. That could be huge. Just simple things to think about. My previous post explains some things I've learned about Fill Factors, pros vs cons. If you have time, what's your highest number? What's your lost space as a whole?...(more)

Updating statistics for Memory-Optimized tables in SQL Server - Due to the fact that for Memory-Optimized tables statistics are not automatically updated, we must manually update statistics in order to take advantage of new statistics for In-Memory OLTP. But this is not so simple and as you may know, even when statistics are manually updated Natively Compiled Stored Procedures seem to ignore them...(more)

Dealing with Negative Session ID Denials in SQL Server - When we look at sessions being blocked in SQL Server, the session most often causing the blocking is a Session ID (SPID). However, there are some occasions where the SPID doesn't make any sense. In these cases, you'll see a negative number as the blocking session. ...(more)

Storage Myths: Dedupe for Databases - While dedupe is great in use cases like VDI, it offers very limited benefit in database environments while potentially making performance worse. That in itself is worrying, but what I really see as a problem is the way that certain storage vendors appear to be selling their capacity based on assumed levels of dedupe....(more)

A Rickety Stairway to SQL Server Data Mining, Part 14.6: Custom Mining Functions - Although we can control the output of the standard prediction functions of SSDM to return any values we want, we can create a custom mining function to provide a more flexible output format. We might also want to perform custom calculations that wouldn’t normally be implemented in standard prediction functions like PredictStDev or PredictSupport....(more)

Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews - With skews introduced in the new Cardinality Estimator – similar to previous versions and depending on the query and schema, multi-column stats and calculated columns can still be helpful in reducing the Cardinality Estimator skew. ...(more)

Windows Server 2012 and SQL Server 2012: Better Together - Some of the ways in which Windows Server 2012 works better with SQL Server 2012. Higher Capacity SQL Server Virtual Machines, Increased SQL Server VM Density, Improved Availability, Improved Storage Utilization and New Disaster Recovery Option ...(more)

What To Do If Your Database Catches Fire - If you are having an issue with a database server should you just start rebooting it in the hopes that it clears itself up? After your initial assessment is done you should have a handful of viable options to explore at that point. You need to know the pros and cons for each of these options. That’s where the initial planning comes handy, too....(more)

Blogs : Analysis Services / BI

Querying SSIS package pipeline output from T-SQL - The Data Streaming Destination comes with the Microsoft SQL Server 2012 Integration Services Data Feed Publishing Components. To demonstrate the functionality, I created a simple SSIS package that reads data from the DimCustomer table of the AdventureWorks database. The output is then routed to the Data Streaming Destination....(more)

Getting Started with SQL Server Sample Databases - The Contoso Retail DW data set is several times the size of AdventureWorks and comes as a pre-built star schema. While it’s not the biggest database (my copy is around 1.6GB), ContosoRetailDW provides a large enough data set where aspiring database professionals can really start to push the limits of a local SQL Server....(more)

Logical OLAP to Physical SQL Model Mapping - One of the most important parts of proper OLAP on top of the relational database is the mapping of logical attributes to their physical counterparts. In SQL database the physical attribute is stored in a column, which belongs to a table, which might be part of a database schema. For data browsing, the Cubes framework has to know where those logical (reported) attributes are physically stored. It needs to know which tables are related to the cube and how they are joined together so we get whole view of a fact....(more)

Blogs : Computing in the Cloud

Boosting Transaction Performance in Windows Azure Virtual Machines with In-Memory OLTP - With the release of SQL Server 2014 CTP2, you can now significantly boost the performance of your OLTP workloads in Windows Azure Virtual Machines. By creating a new VM with our preloaded image of SQL Server 2014 CTP2 on Windows Server 2012 R2, or installing SQL Server 2014 CTP2 on your VM, In-Memory OLTP functionalities are immediately available to you. ...(more)

Windows Azure Storage Release - Introducing CORS, JSON, Minute Metrics, and More - Windows Azure Storage release 2013-08-15 provides various new functionalities across Windows Azure Blobs, Tables and Queues. This includes CORS (Cross Origin Resource Sharing):, JSON (JavaScript Object Notation) and Minute Metrics in Windows Azure Storage Analytics...(more)

Blogs : DMO/SMO/Powershell

Deploying PowerShell 4 with Group Policy - There are several ways to deploy PowerShell 4. You could build it into your image, use SCCM, or Group Policy scripts. Because Group Policy scripts are universally accessible and free, we will use them to deploy PowerShell 4....(more)

Blogs : Performance and Tuning

SQL Server Troubleshooting Useful Links - I’ve started compiling a list of resources that I feel help to show how troubleshooting doesn’t have to be a hard exercise. Here is my list...(more)

Blogs : Professional Development

Two SQL Server Resources That Improved When You Weren’t Looking - SSC’s Stairways series covers topics start to finish with 5-15 tutorials from one or two authors. I love the consistency on these – you can settle in with one author and really dig into a topic with a logical flow. Think of it as an interactive book chapter, often with lots of demos you can run to illustrate concepts. They’ve added stairways for T-SQL, indexes, transaction logs, PowerShell, replication, SSRS, and other good foundational topics. The existing stairways keep getting better as the authors add more posts....(more)

Blogs : Reporting Services

SSRS Table Cells with Mixed Formatting - n this quick post I’ll show you how to create and format a report in Reporting Services so that you can configure individual table cells to display their contents with a mixture of text formatting. ...(more)

Blogs : Security and Auditing

Cloud insecurity - Is a concern about cloud security an irrational fear? In short, we have no way of knowing. In the case of almost all the data breaches of the past year, the custodians of the data weren’t aware of the extent of their security weaknesses until their vulnerabilities were exposed by hackers. ...(more)

Fraud Detection with the SQL Server Suite Part 3 - data overview activities interleave with the data preparation. In order to find outliers, we must get the idea of the distribution of a variable. We can use Microsoft Office Excel Pivot Tables and Pivot Graphs for this task. However, many times it is faster to use statistical computations and interpret the results. With Transact-SQL queries, we can calculate a lot of useful statistical information....(more)

Blogs : T-SQL

Decrypting Stored Procedures Quick Tips - You see a job calling a Stored Procedure that don't know what it does. You attempt to script it out and view it. Encrypted? This script by Jon Gurgul can be insanely useful if you're taking over an organization that everything was encrypted and you can't get into it, until now....(more)

Prevent overlapping of time events with an indexed view - This article does not pretend to present this solution as superior to others, it's intent is to show you how to solve the overlapping problem with an indexed view. ...(more)

Ansychronous triggers and cursors in SQL Server - In Relational Database Management Systems (RDBMS) all triggers are synchronous in the sense that the next statement after the triggering statement is only executed after the execution of the trigger has completed. There are however situations where an asynchronous execution can be a valid option. This article shows how cursors can effectively be used in implementing asynchronous triggers....(more)

Articles : Administration

The ‘A-ha’ About Active and Inactive Data - There are only two kinds of files: active files and inactive files.Users create a file. As long as the file is being worked with, it's an active file. Users can share, collaborate, and edit the file to their heart's content, but eventually the file is no longer current, and it becomes inactive. But in this litigious and increasingly regulated world, there is no such thing as deleting a file. You're stuck with that inactive file, for years, if not forever, like your 30-year-old college dropout kid who still lives in the basement....(more)

Articles : Development

SQL Prompt - An Add-In To Aid Productivity - SQL Prompt is aimed at professional SQL developers, providing them with a range of features to make coding easier and more interactive. SQL Prompt costs from $369 but you can download a 14-day free trial. As it's a useful tool that it is easy to become reliant on it Redgate presumably hopes that after using it for 14 days for free you won't want to give it up....(more)

Introducing the Red Gate SSMS Ecosystem - Red Gate has lots of experience developing tools that integrate with SSMS. Over time, we’ve built a collection of common libraries and APIs that make developing SSMS add-ins much simpler. We’ve made these available free, so developers can build their own SSMS add-ins and integrate them with other tools....(more)

Single-Page Applications: Build Modern, Responsive Web Apps with ASP.NET - Single-Page Applications (SPAs) are Web apps that load a single HTML page and dynamically update that page as the user interacts with the app. In this article, I’ll walk through creating a simple SPA app. Along the way, I’ll introduce some fundamental concepts for building SPAs, including the Model-View-Controller (MVC) and Model-View-ViewModel (MVVM) patterns, data binding and routing....(more)

Translating reliably between XML and JSON (xml2json) - MongoDB stores data as JSON. But it turns out we often have customers - especially in the important financial services market segment - where data is in XML. (Yes, SOAP still exists too!) To store that data into MongoDB, we need to transform it into JSON....(more)


Administrative