In this issue:

Tech News : General Interest

Tech News : Security

Tech News : The Lighter Side

Microsoft News

Blogs : Administration

Blogs : Analysis Services / BI

Blogs : Backup and Recovery

Blogs : Computing in the Cloud

Blogs : Database Design, Theory and Development

Blogs : DMO/SMO/Powershell

Blogs : High Availability/Disaster Recovery

Blogs : Integration Services/ETL

Blogs : Performance and Tuning

Blogs : Professional Development

Blogs : Security and Auditing

Blogs : Software Development

Blogs : SQL Server 2012

Blogs : T-SQL

Blogs : Virtualization

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-01-21

Take our database development survey $100 Amazon Vouchers Up For Grabs!
Take our short survey about database development and be entered into the prize draw! Begin database development survey.
SQL DBA Bundle ‘Beating Backup Corruption’
The DBA Team are back, revealing more of their ‘Top 5 Hard-earned Lessons’. Lesson two is out now! Learn to beat backup corruption.
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
Editorial - The Data Scientist

There are many who tend to pour scorn on the sudden promotion of the role of 'Data Scientist'. At the very least, they view it just one more bandwagon; suddenly anyone who's added a smattering of PowerPivot wizardry to their reports, or has a little experience with Hadoop, is a data scientist. Yet, it's a real pity because the skill of the true data scientist is increasingly valuable.

The role of Data Scientist involves a lot more than fancy 'visualizations', and has nothing to do with the froth of Big Data or NoSQL. Data Scientists should have dual qualifications; yes, they need to be able to manipulate and report on data, but at the same time to be able to draw reliable conclusions. They must extrapolate from data, understand and be able to test for data quality, understand probability, cause-and-effect, variance, and advanced parametric statistics. Part DBA, part BI analyst, part statistician.

Business Intelligence must be more science than soothsaying. After many spectacular failures of database reporting, over the years, to be able to predict trends and provide what the marketing men term 'actionable insights', more companies are realizing that business data is stochastic, like the social science data for which parametric statistics were first developed. Measuring business indicators isn't like using a ruler. You need to be sure that the results are 'statistically' significant, and that you're not betting the farm on a mere quirk in the data.

I'm not sure that we need a separate profession of Data Science, beyond a narrow specialism. I think that anyone doing serious business reporting and data analysis should ensure that their skills are broad enough to take in the task of ensuring that the quality of data is sufficient, and that it is scientifically-justifiable to draw the conclusions you are providing to the business. That means being literate in parametric statistics.

A Stairway to 'R', anyone?

Phil Factor.

» 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 : General Interest

Firefox OS: The Future of Microsoft's Mobile Ambition? - Apple's stock is taking a beating as analysts and investors are waking up to the very real likelihood that market share matters, and Google's Android is dominating mobile market share with its one-two punch of zero license fees and open source flexibility. Meanwhile, Microsoft has been attempting to insert itself into the mobile conversation with a slick, Apple-esque Windows Phone 8 strategy, which seems unlikely to work, given that Apple has already claimed the high end and Google has taken the mass-market, commodity middle ground. ...(more)

Security audit finds dev OUTSOURCED his JOB to China to goof off at work - A security audit of a US critical infrastructure company last year revealed that its star developer had outsourced his own job to a Chinese subcontractor and was spending all his work time playing around on the internet....(more)

Tech News : Security

Why Red October malware is the Swiss Army knife of espionage - The Red October malware that infected hundreds of computer networks in diplomatic, governmental, and scientific research organizations around the world was one of the most advanced espionage platforms ever discovered, researchers with antivirus provider Kaspersky Lab have concluded....(more)

Microsoft Security Essentials Fails Certification Again - At the end of November, German anti-virus lab AV-TEST published the results of tests conducted with twenty-four of the latest anti-virus programs for home users. Twenty-three products received AV-TEST certification when used with Windows 7, but Microsoft's own Security Essentials suite failed. Now two months later, the suite has failed certification again....(more)

Malware Discovered at Two U.S. Power Plants - Initial reports from ICS-CERT on the infections at two U.S. power plants indicate they were likely to be widely available pieces of malware not specifically designed to target supervisory control and data acquisition (SCADA) environments, said security researcher Dave Pack. That said, he added, the malware was still disruptive....(more)

Tech News : The Lighter Side

Petition to Build Death Star Prompts Hilarious Response from White House - Why would we spend countless taxpayer dollars on a Death Star with a fundamental flaw that can be exploited by a one-man starship?...(more)

Microsoft News

Microsoft to offer its 'Drawbridge' virtualization technology on top of its Windows Azure cloud - Microsoft is planning to make its 'Drawbridge' virtualization/hosting technology available on its Windows Azure cloud....(more)

Blogs : Administration

Last Time CheckDB was Run - I run this script on every server I touch to get a report for the last known good checkdb for every single database....(more)

Documentation: It Doesn’t Suck! - Documentation is a task many people roll their eyes at, procrastinate about starting, have a hard time keeping up-to-date, and in general avoid. Stop avoiding it, and embrace the benefits!...(more)

The SQL Server Instance That Will not Start - Everyone's SQL Server nightmare: The instance will not start. If such a problem strikes you, keep calm, follow Gail's advice, and you'll soon be back up and running. In the meantime, practice these steps so as to be ready! ...(more)

SQL Q&A: In search of performance - Mirroring problems, database shrinking and more in a Paul Randal Q&A...(more)

Setting up SQL Server Database Mail with gmail - If you want to mess around with Database Mail from your laptop and you don't have a mail server, you can configure gmail for that purpose. SQL Denis shows how....(more)

Top 10 Free SQL Server Tools - What better way to kick off a new year than with a look back at some of the top free SQL Server tools of 2012. I know you all love talking about your favorite tools and maintenance scripts. Whether your pleasure is problem diagnosis, query tuning, index analysis or performance monitoring. These tools are all available absolutely free and with the common goal of making your life easier....(more)

Regression Testing before CUs and Service Packs - You’d be surprised how many apps I’ve worked on, whether third party or developed internally, where we didn’t have a set of comprehensive regression tests. If I was lucky, we had a good set of tests for each release, but more often than not I’ve found developers and testers focusing on specific features and ignoring the overall application....(more)

More SSDT naming confusion - Jamie Thomson on he naming confusion that Microsoft have created by their inconsistent use of the term “SQL Server Data Tools” (SSDT) in SQL Server 2012. Is it a Visual Studio shell incorporating project templates for building databases, SSIS packages, SSAS cubes & SSRS reports? Or is it simply the project template for databases?...(more)

Reindexing? Check your DOP - Merrill Aldrich reflects on a long night watching Perfmon counters while coaxing data warehouse fact data into new files. He learned something important: don’t assume that your re-indexing work, even the biggest flavor, is automatically I/O limited....(more)

Blogs : Analysis Services / BI

A Different Approach To Last-Ever Non-Empty in DAX - At the core of this approach is the idea that when you’re searching for the last non empty date on which a sale was made......(more)

SSAS: One giant cube or many small ones? - When using SQL Server Analysis Services (SSAS) to build a cube, you may start out with one fact table, but then decide to add another fact table. You are then faced with a decision: Do you add that fact table to your existing cube and have multiple measure groups, or do you create another cube, each with a single measure group?...(more)

Blogs : Backup and Recovery

Getting backs up about backups - I’ve been leafing with interest through the book, Pro Data Backup and Recovery, by Steve Nelson. For anyone predisposed to consider backup strategy largely from the perspective of a SQL Server database administrator, there are some revelatory passages, and a few that may cause you to splutter coffee over your keyboard....(more)

SQL Server – Enabling backup CHECKSUM with Trace Flag 3023 - If you do not have control over underlying T-SQL for backup then you can enable backup checksums using Trace Flag 3023....(more)

Centralizing and Analyzing SQL Backup Pro Backup and Restore Data - The more servers that a DBA is responsible for, the more important it is to have an automated way of documenting and reporting the admin events and activity in these servers. It is particularly useful to know about the outcomes of backups and restores....(more)

Blogs : Computing in the Cloud

Windows Azure SQL Database and SQL Server -- Performance and Scalability Compared and Contrasted - While SQL Server and Windows Azure SQL Databases (SQL Database for short, formerly SQL Azure) have large and important similarities, they are not identical, and while the differences are relatively small, they affect the way that applications perform on SQL Database compared to SQL Server. As a result, the application architecture and performance evaluation techniques for each platform also differ....(more)

Execution Plans on Azure SQL Database Portal - If you’ve been working with the Azure SQL Database portal, you know that you have a query window. Within the query window you can run queries against your server. You’ll get back results, timing, everything you need to observe behavior within the system. What’s that? You never heard of this? ...(more)

Blogs : Database Design, Theory and Development

Down to the Basics of Business Modeling Concepts - Representing a segment of reality in a computerized database requires a model of that reality mappable to a data structure that provides intelligent information retrieval and facilitates data integrity enforcement....(more)

Designing a Database: 7 Things You Don’t Want To Do - When I come across a design that is sub-optimal it makes me ask myself “what did this data do to deserve to be treated so poorly?” Data lasts longer than code and it should be treated accordingly....(more)

Blogs : DMO/SMO/Powershell

PowerShell Workflows: Job Engine - Richard Siddaway continues his series, this time looking at running workflow jobs....(more)

Updated Warehouse Re-Index Script - Having worked recently on a re-indexing project that took the partitioned fact rows from our warehouse and relocated them into new files, Merrill Aldrich shares his PowerShell “helper” script to generate the actual T-SQL that moves the data. The idea is to find all the indexes that use a particular partition scheme in the database, and make the CREATE INDEX statements that would recreate them on the new partition scheme. ...(more)

Blogs : High Availability/Disaster Recovery

Database mirroring: avoiding ‘cannot obtain a LOCK resource’ problems - Paul Randal explains why you may see 1204 and 1454 errors on the mirror server with no other databases or activity on the mirror server apart from database mirroring....(more)

Expanding AlwaysOn Availability Groups with Replication Publishers - SQL Server 2012 AlwaysOn Availability Groups provide a high-availability and disaster-recovery solution for you SQL Server 2012 environments. Replication has been around in SQL Server for quite some time and allows you to scale out your environment. Warwick Rudd explains how to join these technologies together...(more)

Blogs : Integration Services/ETL

How to get started with performance tuning of SSIS packages - Well if you are a DBA and one fine day you get notified that SSIS packages have started performing slowly. Here is how to get started with finding the bottleneck and pointing the possible cause of sudden performance problem....(more)

Data tapping during SSIS package execution in SQL Server 2012 - During SSIS package development we can add a data viewer to the data path of the data flow task to analyze the data passing through the data path or the pipeline, but once the package is deployed (or when not using Business Intelligence Development Studio) there was no built-in support for data tapping to analyze data for troubleshooting. I have heard we can do this now with SQL Server 2012, can you explain how it works? ...(more)

How clean is your data? - “There’s no need to clean the data – just extract it from the source systems and load it into the warehouse. Our data is already clean.” I wonder how many times some poor extract, transform, load (ETL) consultant has heard those words, smiled diplomatically and then scanned the room for a sturdy wall to bang their head against repeatedly....(more)

Ragged Flat File Processing in SSIS - Tim Mitchell on how to properly use SQL Server Integration Services to handle ragged flat files, if you haven't yet the luxury of using SSIS 2012....(more)

Blogs : Performance and Tuning

My Favorite Query For Investigating SQL Server Performance - I work in a lab environment, often examining running SQL workloads for performance analysis. This is a query I've used many times to see what's running _right now_ on a server and what kind of resources it's using. It dumps the running queries and query plans and takes a snapshot of the system wait stats. One cool thing is that it adds CPU_TIME as a "wait stat"....(more)

STATS_DATE() Doesn’t Indicate The Validity of Statistics - here is a myth out there that when statistics are old, they are automatically out of date. I’ve seen scripts that treat old statistics as out-of-date, indexing presentations that mention the same, and even health check tools that do this as well. In many cases, the person bringing it up touts it as a trick they learned that will fix the problem....(more)

Blogs : Professional Development

You Can Say “No” - “We want you to sign this non-compete agreement that says you’ll never be a DBA for any other company after leaving ours.” Ha! No....(more)

New Year Ambitions – Brent Ozar – Consulting - Richard Douglas talks to Brent Ozar about social media, running a happy business, database horror stories and more. ...(more)

Blogs : Security and Auditing

The problem with website security is us! - I write a lot about website security. Sometimes I’ll publicly point out flaws in software but there are many, many other times where it remains a private conversation for various reasons. The one common thread across most of these incidents is that as developers, we often make bad security design decisions. It’s us – the organic matter in the software development process – that despite the best of intentions make bad choices that introduce serious risks....(more)

Blogs : Software Development

Mooney’s Law Of Guaranteed Failure - f i had a nickel for every time our deployment strategy for a new or different environment was to edit a few config files and then run some batch files and then edit some more config files, and then it goes down in a steaming pile of failure, I would buy a LOT of Sriracha....(more)

Blogs : SQL Server 2012

Top 20 exciting features of SQL Server 2012 – Part 1 - Dividing the product features into “revolution” and “evolution”, Shivprasad koirala reviews his favorites....(more)

Blogs : T-SQL

Generate a set or sequence without loops – part 1 - There are many use cases for generating a sequence of values in SQL Server. I’m not talking about a persisted IDENTITY column (or the new SEQUENCE in SQL Server 2012), but rather a transient set to be used only for the lifetime of a query. Or even the simplest cases – such as just appending a row number to each row in a resultset – which might involve adding a ROW_NUMBER() function to the query (or, better yet, in the presentation tier, which has to loop through the results row-by-row anyway)....(more)

Finding equivalent elements in SQL (regrouping) - Regrouping is something that I've had to do many times during my career in data analytics, unfortunately a lot of relationships aren't defined through a single unique identifier, at least not at first. This code snippet is designed to flush out the transitive relationships defined between pairs of elements and assign all elements in the graph a single unique identifier. Get ready for some SQL looping action......(more)

Blogs : Virtualization

Big Changes for Big Virtual Machines in VMware vSphere 5 - Virtualization just presents a lump of CPUs and memory to our guest. Our virtual machine has no idea what the underlying NUMA configuration is – and it can’t, because it could change at any time when we’re moved from one host to another. This isn’t a performance problem for most apps because they don’t need to know anything about NUMA. They just want a lump of CPUs and memory. Unfortunately, this is a performance problem for SQL Server......(more)

Microsoft launches open source VM image 'Depot' for Windows Azure - Microsoft Open Technologies, a subsidiary of Microsoft, has launched a public preview of its app store for Windows Azure called VM Depot, which it describes as "a community-driven catalogue of open source virtual machine images"....(more)


Administrative