The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

There is a reason why we like to make a good attempt at designing a database up-front, rather than evolving it from a vague, woolly idea of what it maybe ought to look like: it’s the data and its domains. For an empty database and a single developer, it all seems simple, as we can just delete and rebuild, especially if we can cut the prototype database directly from the Entity-Relationship diagram. We can be as free as an application programmer to evolve ideas and try things out.

Then we build the team. We can get around many of the problems of team-working by using source control. However, we have to work from an existing consensus of what the data model should be. Any changes to the model take both tact and discussion because of the wide ramifications of a design decision.

The bigger problems associated with radical change come when one has to preserve the data. This may initially just be for your test data, but ultimately it may end up as a data-migration process in a deployment. Any serious redesign in a database schema means that automatic data synchronisation isn’t entirely possible: it has to be supplemented with a hand-cut 'migration script' that is capable of handling such things as table-splits or even a rearrangement, or renaming, of columns. If you are deploying changes to an existing database without taking it offline, then you’d have to provide ways of rolling out, or rolling back a change whilst preserving any concurrent changes in the data. All this is quite separate to the problem of maintaining a compatible interface with one or more applications that are using the system.

One could go on and on about the practical repercussions of refactoring existing databases. It therefore always surprises me when Application programmers tell me that all 'Agile' techniques are directly applicable to database development. Ideally, yes; but reality gets in the way. It would certainly be great to experience this sort of freedom, but we need to be realistic about what is possible, without the appropriate tools and techniques.

Once you've experienced working on a large corporate database that is critical to the wellbeing of the company, it is hard not to be awed by the number of tasks involved in even the smallest change to a production system. Although it has become easier in the past decade with the advances in tools, it still isn’t a simple, automated process. It will be interesting to see what develops in the next couple of years to allow database development work the same freedoms enjoyed by Agile.

Phil Factor

Phil Factor from SQLServerCentral.com

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

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

SQLCop integration with Red Gate's SQL Test - Approximately a year and a half ago, friends of mine and I created SQLCop. Our motivation was to provide a tool that users can download and run against their database. This tool is very effective at detecting common problems with database configurations and TSQL code. Not every issue highlighted by SQLCop requires a fix, but you are very likely to discover potential problems that you didn't even know you had....(more)

Tech News : Data Mining/Warehousing

To Know, but Not Understand: David Weinberger on Science and Big Data - In an edited excerpt from his new book, Too Big to Know, David Weinberger explains how the massive amounts of data necessary to deal with complex phenomena exceed any single brain's ability to grasp, yet networked science rolls on....(more)

Tech News : General Interest

CloudOn Brings Free Cloud-Hosted Microsoft Office Functionality to iPad - Silicon Valley startup CloudOn today released a free iPad app [App Store] that aims to bring the functionality of Microsoft Office to the iPad. The iPad app actually serves as an interface for the full cloud-based app on CloudOn's servers, and thus requires an Internet connection to function, but offers what seems to be a remarkably functional implementation of Microsoft Word, Excel, and PowerPoint, all integrated with file storage and syncing through Dropbox....(more)

Tech News : Security

More Turn To Do-Not-Track Software To Maintain Privacy - As more social networks and web app developers are getting into the online tracking game, exploring novel ways to derive fresh revenue from tracking data, more and more average web users are discovering and using available anti-tracking technologies, saying they want to be in control of who collects data about what they do online....(more)

Latest SQL Injection Campaign Infects 1 Million Web Pages - SANS warns of uptick in 'Lilupophilupop' attack, but Cisco says total number of infected URLs might be 'inflated'...(more)

AntiSec Hacks Signal Same Old, Same Old In Database Insecurity - Hacktivist group takes down two law enforcement associations with ease...(more)

Care2 Discloses Breach; Company Has Nearly 18 Million Members - Care2.com servers were attacked, resulting in a security breach. The hackers were able to access login information for Care2 member accounts. Time for a rethink on password-based security?...(more)

Microsoft News : General Interest

Microsoft will add Linux virtual machines to Windows Azure - Microsoft is preparing an expansion of the Windows Azure virtual machine hosting technology that will let customers run either Windows or Linux virtual machines, as well as applications like SQL Server and SharePoint, according to Mary-Jo Foley at ZDNet. ...(more)

Windows 8 Storage Spaces detailed: pooling redundant disk space for all - When Microsoft killed Windows Home Server's "Drive Extender" technology, we mourned its loss but held up hope that the company would persevere with the concept. The company has done just that with a new Windows 8 feature called Storage Spaces...(more)

Microsoft News : Security

The Case of My Mom’s Broken Microsoft Security Essentials Installation - As a reader of this blog I suspect that you, like me, are the IT support staff for your family and friends. And I bet many of you performed system maintenance duties when you visited your family and friends during the recent holidays...(more)

Blogs : Administration

SQL Server Error Logs - I read a blog post this morning on SQLServerCentral by Tim Radney about SQL Server Error Logs. Tim talks about increasing the number of error logs in SQL Server. One thing that came to mind to take this one step further and is just as simple to do is to ‘Cycle’ the error log. ...(more)

SQL Server – Identifying default data directory for multiple instances through registry - Earlier I posted about using xp_instance_regread to get default data location for current instance. It translates a given registry path to instance specific registry path. This can return the value for a single instance only. However, if you need to locate default data directories for all available instances on a machine, you need to use xp_regread, which reads from an absolute registry path....(more)

SQL Server's Big Red Buttons - One of the most reassuring aspects of watching a vintage James Bond film is the comfort of knowing that, just when there seems no further hope that the villain's plans for world domination will be thwarted, Bond will glance up at the wall and notice a big red button. Instantly, he knows that all he has to do is press it and the villain's lair will self-destruct messily, with plenty of pyrotechnics, and armed men being tossed into the air like rag dolls....(more)

What is an e-commerce database? - There are things you do differently depending on whether a database you support is OLTP or DSS/DW or somewhere on the spectrum between the two. There are also things you do differently for vended databases vs. custom databases....(more)

What 2012 Work Scares You? - Right now, this very moment, there’s an ugly piece of work tapping on your shoulder – and I don’t mean your boss. You agreed to do it months ago during the 2012 planning sessions, but you never really expected January 2012 to arrive. Now that it’s here, you’re starting to get nervous because you’ve never done it before and you’re not quite sure where to begin....(more)

January 2012 – Monthly SQL Server Checklist - Start the year off right with Jason Strate's January SQL Server checklist, covering backup and restore validation, SQL Server Updates and more....(more)

Blogs : Analysis Services / BI

Use parameters in your #DAX queries - Using parameters in your DAX queries isn't as simple as it ought to be......(more)

Blogs : Computing in the Cloud

Conor vs. SQL Azure/SQL Server 2012 - A little something to help people evaluate databases to see if they are able to be moved to SQL Azure – it’s free and you don’t need an Azure account....(more)

Blogs : Database Design, Theory and Development

Business Logic in the Database - Chris Travers recently responded to Tony Marston’s critique of an earlier post where Chris advocated “intelligent databases”1. Chris’ response is well reasoned, particularly his point that once a database is accessed by more than a single application or via third-party tools, it’s almost a given that one should attempt to push “intelligence” and business logic into the database if possible....(more)

Refactoring large live OLTP tables without downtime - Refactoring tables does not have to be such a big deal. We do not have to shut modifications down, migrate all data into new structure, deploy modified modules, and do it all at once, while the system is down....(more)

Be Kind to Idiots - Many idiots are so because they've chosen the path of ignorance, devolving further into idiocy. We see this in politics and IT, specifically the nexus with RDBMS. ...(more)

Should we use Singular or Plural Database Table Names - Well, I have been in a dilemma about this for a while. After reading several posts on Singular vs Plural Database table names, I finally concluded that both have its advantages and disadvantages, and that everyone has their own opinion on this, and that the most important thing is that your naming conventions are consistent across the database......(more)

Do You Make These 5 Database Design Mistakes? - Any one of the following five mistakes listed below will add additional costs to your company. It’s guaranteed. The costs could be hardware related (extra disk space, network bandwidth), which tend to add up quickly. The costs are also support related (bad performance, database re-design, report creation, etc.), and they add up very quickly....(more)

Blogs : DMO/SMO/Powershell

Grabbing Table Row Counts Into SQL Using PowerShell - This script could easily be setup to execute daily to load the data into SQL so that you can perform quick and easy trends on table growth....(more)

Blogs : FILESTREAM Data

SQL Server 2012 FileTable – Part 1 - In a series of blog posts we will have a look at the new SQL Server 2012 table type called FileTable. This first blog post will be a simple getting started – how to create a FileTable and how to dump files into the folder and do some simple file manipulation....(more)

Blogs : Integration Services/ETL

31 Days of SSIS in a PDF - At some point last year after finishing the 31 Days of SSIS, I decided to bundle up all of the posts into a single document. I never got around to distributing it. No real good reasons. So, if you want all of it in one place. Here it is…...(more)

The rise of Hadoop: Is ETL dead? - “Is ETL dead?” This was the question posed to me recently by an anxious funds manager whose investments included a significant position with an IT vendor that offers, among other things, data integration software, including ETL....(more)

Blogs : Performance and Tuning

Workaround for Bug in Activity Tracking Event Session Template in 2012 RC0 - In SQL Server 2012 RC0 there are a number of event session templates provided that make creating a commonly used session easier using the Event Session Wizard in SQL Server Management Studio. One of these has a bug in it’s definition XML file....(more)

Performance impact: hyperthreading for OLTP queries - The OLTP queries used to check out the hyperthreading impact are the two TPC-C read-only transactions (Order Status and Stock Level), slightly modified to work properly in the test environment. ...(more)

Performance impact: hyperthreading for reporting queries - There are a lot of questions on hyperthreading, but not a lot of answers. There is no shortage of opinions, but very few are based on significant first hand experience or solid test data. Linchi Shea provides some hard data....(more)

Running OLTPish system without deadlocks, and loving it - Our OLTPish mixed load system has not had a single deadlock since last April, and we just love it. Alex Kuznetsov explains how theyy achieved it....(more)

Notes on Scalability - We all hope that we’re going to succeed beyond our wildest expectations. Startups long for multi-billion dollar IPOs or scaling to hundreds, or even thousands, of servers. Every hosting provider is touting how their new cloud offering will help us scale up to unheard of heights. I’ve built things up and torn them down a few times over my career...(more)

Evaluating server hardware: a sign of the times - Linchi Shea descibes a specifically modified version of the TPC-C benchmark that he's used successfully for 10 years, in evaluating server hardware running SQL Server, and how he's now found this benchmark failing to fully utilize all the processor power on machines with a large number of processors, most notably those with 40 or more processors....(more)

Survey results: How cluster key size can lead to GBs of wasted space - Paul Randal reports the results of a survey spanning more than 500 systems across the world and showing the amount of space that is wasted, and the performance problems that can result, from not choosing a narrow clustered index key....(more)

Blogs : Professional Development

Your Blog is The Engine of Community - In a time where this is much gnashing of teeth around the meaning of community, what being on the "inside" vs. the "outside" means, I want to take a moment to remind my fellow blog writers, blog readers, blog commenters what makes it all work. You....(more)

Recognition Compensation - I think the MVP program means well. It’s not trying to be a conspiracy or filch you of your just desserts. But if you think about the MVP program as a compensation system, it becomes very clear why people feel disillisioned....(more)

Valentine’s Day and Your Career - Perhaps you’re the kind of person who doesn’t buy Valentine’s day cards, or take your significant other out for dinner on Valentine’s day. After all, it’s a manufactured, made up holiday from the greeting-card companies, right? Somebody just decided to come up with a day to make you do something you don’t normally do. Here’s a tip: do it anyway....(more)

Blogs : Reporting Services

SSRS - Embedded Custom Code - When developing reports in Reporting Services you will often use the built-in expression language to make report data and formatting dynamic. The expression language can do a lot to within Reporting Services but any gaps in functionality that it has can be filled with custom code. There are two ways to implement custom code in Reporting Services. The first way, which I will show in the post, is with embedded code....(more)

Blogs : Security and Auditing

Extracting object ownership information from Active Directory into SQL - There could be many reasons why tracking Active Directory objects ownership might be important: audit requirements, identity management processes enforcement, just to name a few. This blog will outline an approach of using SQL Server Integration Services (SSIS) for exporting ownership information from AD into a relational format (SQL table), for the purposes of subsequent report generation. This solution relies on SSIS Active Directory Source Component....(more)

SQL Server Error 18452, "Login failed. The login is from an untrusted domain" Windows 7 - I have just got this error message again on a Windows 7 Workstation, that I am using SQL Server Management Studio on. It reminded me that I wanted to blog about the resolution to this and also add how to solve this for Named Instances as well....(more)

Ten Things To Do to Secure an Important Person's Computer (or even Ashton's or a Kardashian's) - Make it so you can find your stuff, but others can't find you. Use pins or passwords on your devices when you can. Encrypt sensitive data in TrueCrypt files or Bitlockered drives. Use "two factor" authorization for crucial services like email. ...(more)

Server level permissions for developers - A development team wants to have right to kill user sessions on development servers, but to be able to kill a session, you have to be a sysadmin, processadmin, or have ALTER ANY CONNECTION right granted to you at server level. Obviously, in certain environments these permissions are not the ones DBAs want to give......(more)

Blogs : Software Development

How are Scripting Languages different from general Programming Languages? - I find myself in Germany, at a Dagstuhl conference on Foundations for Scripting Languages and with a with a bit time to ponder things. For example, why do we label some languages as Scripting Languages and some not? The first part of the answer is easy enough: a language is a scripting language if we use it write scripts. But just what makes a script different from a program?...(more)

Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experiences - Over the past eighteen months I have worked on four separate projects for customers that wanted to make use of Visual Studio 2010 Database projects to manage their database schema.All the while I have been trying to take lots of notes in order that I can write the blog post that you are reading right now – a compendium of experiences and tips from those eighteen months....(more)

Application review: Northwind Starter Kit - Ayede Rahien raises an eyebrow at the complexity of the Northwind Starter Kit project....(more)

Structuring your Unit Tests, why? - Ayende Rahien questions the value of the unit test structuring scheme championed by Phil Haack....(more)

Blogs : SQL Server 2008 (Katmai)

Book Review: Microsoft SQL Server 2008 Internals - My Brain Hasn't Exploded, But It Was Close A Couple Times. This book was really in-depth and really technical. I feel much smarter after reading it. I have a feeling that in three months or a year, I'll have a problem, and I'll think, "Oh, I read about that somewhere." I'll be able to pull this out and reference it. So, I may not understand all of it right now, but in the long run, it was a great decision to stick with it and read it. ...(more)

Blogs : T-SQL

The Art of the Execution Plan - There’s beauty everywhere – even in SQL Server. Brent Ozar is struck by the beauty of a complex execution plan....(more)

Using XML to pass lists as parameters in SQL Server - XML seems, on the surface, to provide a built-in way of handling lists as parameters. No need for all those ancillary functions for splitting lists into tables, one might think. I’m not so sure that one can, in all conscience, give advice to use XML without qualification. It is fine for short lists, and I've never used the technique for anything else......(more)

Stored Procedure Contracts and Temp Tables - Temp tables are visible within the scope where they are declared but also in sub-scopes. This means that you can declare a temp table in one stored procedure but access it in another stored procedure that is executed from within the first stored procedure....(more)

Grant Truncate Table Permissions in SQL Server without ALTER Table - How do you grant a SQL Server developer truncate table permission without giving them the ability to alter the table? Check out the examples and explanation in this tip....(more)

Blogs : Virtualization

Renaming SQL Server Instances - Working with virtual machines frequently now I often duplicate entire systems for testing purposes and rename the newly created machine. When SQL Server is included in these images, the new copy is left not knowing what it's own name is....(more)

SQL Server 2012 Licensing VMs without SA - A subtle change, one that caught my eye. In SQL Server 2012, if you don’t have Software Assurance, you have this limitation for Enterprise Edition: “SQL Without Active Software Assurance (SA) VM density is limited to one VM per physical Core. VM Density can never rise above the hardware footprint. “...(more)