Tony Davis

To Not CI to Eye

2012-02-03T11:21:57Z

Many developers, including Troy Hunt, here on Simple-Talk, have argued persuasively that each database developer in a team needs to work as sole user of a dedicated database-development environment whilst creating or updating databases. Troy makes a good case, listing several shortcomings of the shared development model: developers are no longer free to experiment and pursue evolutionary design of the database layer, in the same way as any other component of the application, without unduly interfering with the other developers; without the required discipline of checking in changes for continuous integration, source control for the database is hard to enforce; it's very hard to run reliable tests with data dependencies when everyone is free to change the data structures and data.

And yet our surveys suggest that around 80% of database development still takes place in a shared environment, and for valid practical reasons. It isn't just about the data, though for enterprise databases, holding perhaps terabytes of data, and with that test data generated by complex ETL processes, it is no trivial task to replicate this environment in each developer's sandbox database.

Troy's model seemed to assume that developers within the shared model couldn't also access their own sandbox servers, and also that they had no source control. Source Control certainly can be done in the shared model, but just requires more supervision when changes appear that weren't in source control. Many of the consequences he described were due more to this lack of source control, rather than working together on a development server.

Perhaps the strongest argument for the dedicated model, however, is the need to maintain a constant, stable base against which to develop, in which the DDL code in Source Control is used for Continuous Integration. However, does this CI argument really apply equally to the database as to the application? For any enterprise database of reasonable complexity, the up-front design effort should mitigate the need for continuous evolution of the database design, during development. Also, with many database changes being immediately validated, the value to be gained from dedicated development plus CI, to find out if you broke anything, is surely lessened?

Could it be that many of the difficulties of integration and deployment that have been so thoroughly documented are more due to poorly thought-out application-interfaces within the database? Integration problems that are due to version differences are far more likely where this has been omitted from the design, and applications have unrestricted access through the database. Are we in danger of trying to change database development practices to try to cure a problem that is far easier to solve by proper system design?

I'm sure many will disagree; as always I'd love to hear about it.

Cheers,
Tony.

SQL Server's Big Red Buttons

2012-01-06T12:24:00Z

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.

Of course, you have to wonder why the technologists who built the lair put that big red button on the wall. It seems to be an irresistible urge, and one to which the creators of SQL Server are not immune, as @SQLPoolBoy noted last week in one of his tweets...

"Looking at a database that has 99% fragmentation across the board. The cause, AutoShrink"

...a database brought to its knees by some poor soul who had accidentally hit one of SQL Server's big red buttons.

You have to feel sorry for anyone who accidentally accepts the default database sizing and auto-growth settings, or turns on AutoShrink, or accidentally creates a collation conflict, or falls foul of any other of a host of 'Red Button' actions that can eventually lead to metaphorical pyrotechnics and DBAs being tossed in the air like dolls.

SQL Server makes it very easy to tweak its various database- and server-level settings and so it's easy for inconsistency to creep in between database and servers, and it's easy for someone to unwittingly hit one of the red buttons. However, for the DBA managing tens of servers, it's not necessarily easy to find out which buttons have been pressed where, or to find best practice advice on how some of these settings really should be configured for each environment.

However, help in various forms is slowly emerging. Brent Ozar has made publicly available his SQL Server Blitz script, which helps you verify some of the absolute fundamentals (Are backups being taken? Are DBCC checks being run?), and then seeks out a few of the more common red buttons, which may need deactivating.

Then there is also SQLCop, a free community tool for "detecting common problems with database configurations and TSQL code". Named after a similar .NET tool (FxCop), it's broader in scope than the Blitz script, checking everything from configuration settings, to fragmented indexes, to missing Foreign Keys, to "code smells" in stored procedures. Red Gate has done some work with the makers of this tool, in incorporating some of their tests into the SQLTest tool, the idea being that the final step to resolving such problems may be automated testing.

However, there is still much work to be done. What are your favorite "Red Button" actions in SQL Server? What is the best way to find and deactivate them before they cause havoc in your Server and databases?

Cheers,

Tony.

A suitable present, whatever one's past

2011-12-15T17:32:47Z

Even DBAs have devoted aunts. They are probably also oblivious to the mental anguish they cause to their relatives in the run-up to Christmas. What would be a suitable gift for someone so deeply in the grip of technophilia that they can tell you the difference between ten apparently identical brands of Smartphone, and have a couple of them stuffed in their pockets? Who then use them to discuss with colleagues the finer points of query-plan caching or buffer-chaining?

I've got it you cry.some amusing, techno-gadgets! Something they'd want to keep with them, at their desk at work, and maybe be reminded of the source of the gift. We've all seen them: the USB lava lamps, a Voltron USB drive, a USB-heated blanket, and toast hand warmers. No; this stuff might have some value to the type of geek that holes up in a drafty basement, secretly inventing new programming languages, or trying to break the latest record for overclocking a CPU. Many DBAs, however, would open the parcel with a frozen rictus of a smile, a gurgle of feigned delight.

A bit more imagination is required. So, here is an idea: a USB Home Brew kit. Plug it into your server or laptop and, with a bit of control software, you can keep the fermentation temperature at a steady 68F, and watch proudly as the brew progresses. Plus, the gentle bubbles of fermentation coupled with the faint odour of malt will be more satisfying, and relaxing, than any lava lamp.

Could I be wrong? I encourage you to submit your ideas for the most delightful, but yet-to-be-manufactured, Christmas gift for IT professionals. The winner will receive one each of every gift linked in this blog (or equivalent value voucher). Three runners up will receive a gift each.

Since this is my final Simple-Talk editorial of 2011, I would like to thank everyone for their support over the year, and to wish you all a happy festive season and 2012. I will probably be back in the New Year, depending on how well my new USB Home Brew business takes off.

Cheers,

Tony.

Fair Comments

2011-11-10T16:23:43Z

To what extent is good code self-documenting? In one of the most entertaining sessions I saw at the recent PASS summit, Jeremiah Peschka (blog | twitter) got a laugh out of a sleepy post-lunch audience with the following remark:

"Some developers say good code is self-documenting; I say, get off my team"

I silently applauded the sentiment. It's not that all comments are useful, but that I mistrust the basic premise that "my code is so clearly written, it doesn't need any comments". I've read many pieces describing the road to self-documenting code, and my problem with most of them is that they feed the myth that comments in code are a sign of weakness. They aren't; in fact, used correctly I'd say they are essential.

Regardless of how far intelligent naming can get you in describing what the code does, or how well any accompanying unit tests can explain to your fellow developers why it works that way, it's no excuse not to document fully the public interfaces to your code. Maybe I just mixed with the wrong crowd while learning my favorite language, but when I open a stored procedure I lose the will even to read it unless I see a big Phil Factor- or Jeff Moden-style header summarizing in plain English what the code does, how it fits in to the broader application, and a usage example. This public interface describes the high-level process and should explain the role of the code, clearly, for fellow developers, language non-experts, and even any non-technical stake holders in the project.

When you step into the body of the code, the low-level details, then I agree that the rules are somewhat different; especially when code is subject to frequent refactoring that can quickly render comments redundant or misleading. At their worst, here, inline comments are sticking plaster to cover up the scars caused by poor naming conventions, failure in clarity when mapping a complex domain into code, or just by not entirely understanding the problem (/ this is the clever part).

If you design and refactor your code carefully so that it is as simple as possible, your functions do one thing only, you avoid having two completely different algorithms in the same piece of code, and your functions, classes and variables are intelligently named, then, yes, the need for inline comments should be minimal. And yet, even given this, I'd still argue that many languages (T-SQL certainly being one) just don't lend themselves to readability when performing even moderately-complex tasks. If the algorithm is complex, I still like to see the occasional helpful comment.

Please, therefore, be as liberal as you see fit in the detail of the comments you apply to this editorial, for like code it is bound to increase its' clarity and usefulness.

Cheers,

Tony.

A temporary disagreement

2011-10-28T09:54:34Z

Last month, Phil Factor caused a furore amongst some MVPs with an article that attempted to offer simple advice to developers regarding the use of table variables, versus local and global temporary tables, in their code. Phil makes clear that the table variables do come with some fairly major limitations.no distribution statistics, no parallel query plans for queries that modify table variables.but goes on to suggest that for reasonably small-scale strategic uses, and with a bit of due care and testing, table variables are a "good thing". Not everyone shares his opinion; in fact, I imagine he was rather aghast to learn that there were those felt his article was akin to pulling the pin out of a grenade and tossing it into the database; table variables should be avoided in almost all cases, according to their advice, in favour of temp tables. In other words, a fairly major feature of SQL Server should be more-or-less 'off limits' to developers.

The problem with temp tables is that, because they are scoped either in the procedure or the connection, it is easy to allow them to hang around for too long, eating up precious memory and bulking up the shared tempdb database. Unless they are explicitly dropped, global temporary tables, and local temporary tables created within a connection rather than within a stored procedure, will persist until the connection is closed or, with connection pooling, until the connection is reused. It's also quite common with ASP.NET applications to have connection leaks, as Bill Vaughn explains in his chapter in the "SQL Server Deep Dives" book, meaning that the web page exits without closing the connection object, maybe due to an error condition. This will then hang around in the heap for what might be hours before picked up by the garbage collector.

Table variables are much safer in this regard, since they are batch-scoped and so are cleaned up automatically once the batch is complete, which also means that they are intuitive to use for the developer because they conform to scoping rules that are closer to those in procedural code. On the surface then, an ideal way to deal with issues related to tempdb memory hogging.

So why did Phil qualify his recommendation to use Table Variables? This is another of those cases where, like scalar UDFs and table-valued multi-statement UDFs, developers can sometimes get into trouble with a relatively benign-looking feature, due to way it's been implemented in SQL Server. Once again the biggest problem is how they are handled internally, by the SQL Server query optimizer, which can make very poor choices for JOIN orders and so on, in the absence of statistics, especially when joining to tables with highly-skewed data. The resulting execution plans can be horrible, as will be the resulting performance. If the JOIN is to a large table, that will hurt.

Ideally, Microsoft would simply fix this issue so that developers can't get burned in this way; they've been around since SQL Server 2000, so Microsoft has had a bit of time to get it right. As I commented in regard to UDFs, when developers discover issues like with such standard features, the database becomes an alien planet to them, where death lurks around each corner, and they continue to avoid these "killer" features years after the problems have been eventually resolved.

In the meantime, what is the right approach? Is it to say "hammers can kill, don't ever use hammers", or is it to try to explain, as Phil's article and follow-up blog post have tried to do, what the feature was intended for, why care must be applied in its use, and so enable developers to make properly-informed decisions, without requiring them to delve deep into the inner workings of SQL Server?

Cheers,
Tony.

Time for a rethink on SQL CLR?

2011-09-29T11:10:16Z

It is almost seven years since Microsoft announced the sensational news that, with their new SQL Server 2005, you could do data manipulation in Visual Basic or C# as well as SQL. The marketing people got overexcited, stabbing out clichés from their keyboards such as "new era", and "revolutionary". However, they had run off barking in a different direction to the technologists, missing the whole value of SQLCLR for providing specialized extensions to SQL Server

Unsurprisingly, the technology was misunderstood and many DBAs have never recovered from their initial feelings of dread, still shuddering at the merest mention of the term "CLR function". In the opening passage of his recent review of SQL#, a suite of very useful SQL CLR functions for string manipulation, regular expressions, and more, Grant uses terms such as "throwing your arms up" "screaming", "crying out loud", "nuts" and "stupid", which seems a pretty fair assessment of the attitude of many DBAs toward CLR functions in SQL Server.

Several factors have added to its bad reputation. Some of this is misuse, using SQL CLR for functionality that belongs in the application tier, rather than the database. Security issues have compounded the anathema toward CLRs. Much harm was done by poor implementation, mainly on the part of the coder, but also on the part of Microsoft and SQL Server.

The worst problem is the complexity of writing effective CLR routines for high performance and scalability. The key to success is the constant streaming of data, using IEnumerable methods and the SQLDataReader, so that SQL Server never has to process too much data at once. The use, instead, of the ADO.NET dataset, and various custom user collections, can make place huge memory demands on SQL Server, involving heavy multi-page memory allocations in the non-buffer pool region. This issue, especially in 32-bit versions of SQL Server, with access to limited amounts of Virtual Address Space, was the cause of paging problems, and specific errors such as "App Domain marked for unload due to memory pressure", or "701" errors, where SQL Server fails to allocate a contiguous region of VAS for an operation.

Unfortunately, SQL Server does not necessarily make it easy for the SQL CLR programmer. SQL CLR functions cannot use a SQLDataReader over the context connection, so the efficient IEnumerable-based streaming simply isn't available and the programmer is forced to use less efficient techniques, or to find ways around the problem, which ultimately involves classifying your assembly as EXTERNAL ACCESS, or possibly even UNSAFE.

If you're someone like Adam Machanic, you persevere. His blog, and various presentations, detail his journey through writing custom IEnumerable classes to enable streaming, creating multiple threads in the SQL CLR to help deal with issues such as "rude aborts" and evolving all this into a powerful "query parallelizer" component.

If all of this is enough to discourage the rest of us from writing our own SQL CLR components, it still doesn't mean we should deny ourselves access to the useful functionality and performance that they can provide. Professionally-written and properly-tested, SQLCLR libraries, such as SQL#, may cause us to refine or qualify the general anti-CLR position. Thorough testing by the likes of the author of SQL#, Solomon Rutzky, has shown that for functions that require heavy computational power, SQL CLR routines have obvious performance advantages over T-SQL. The SQL CLR gives us a "turbo-charged cursor". Any type of calculation that lends itself naturally to cursor-based logic, such as the running total calculation, will run faster as a SQL CLR.

For accessing important CLR libraries such as the Regex functions SQL CLR assemblies are essential. There are a host of specialized requirements where CLR can replace the clunky and antiquated OLE Automation techniques, and there is the added value of allowing new complex data types such as vectors and coordinates. Yes, the technology has its quirks, but CLR libraries are worth having. In fact, unless you have kicked out the Hierarchy functions and geospatial functions, you already probably have CLR libraries installed!

I'd be interested to hear if, and how, you're currently using SQL CLR, or at least if your attitude towards it is softening.

Cheers,

Tony.

The ALMs Race

2011-09-16T10:43:00Z

How did we get to the point where Visual Studio, and Team Foundation Server, so strongly influences our way of developing applications?

Visual Studio's history is convoluted, and it is still expanding its remit. Microsoft originally bought in a prototype visual form generator. It was combined with the dialect of Basic originally developed for the Omega project (later Access) to create Visual Basic. Although the Xerox Star workstations were the pioneers of this sort of visual interface, Windows users had never seen an IDE like this; it was popular, and duly proliferated, with Microsoft releasing Visual C++, Visual Interdev, Visual Foxpro and so on. Eventually, Microsoft undertook Project Boston, which combined many of these tools into one product, and the result was Visual Studio 97.

For many developers Visual Studio is a still just a convenient, visual tool with which to build and compile their application code. However, the first hesitant steps in expanding this role came with the acquisition of Visual SourceSafe, to provide a rudimentary source control system. Since then Microsoft has been working hard to reposition VS as an Application Lifecycle Management (ALM) tool; i.e. one that not only helps you create code, but also provides a means to support all the operational processes that are necessary to guide an application from initial development (agile, of course) through testing, to release and maintenance.

Visual Studio 2005 Team System (rebranded in VS 2010 as Visual Studio ALM) and Team Foundation Server provides a central, source-controlled data store accessed by the client (VS) via a set of web services, and providing features to support project tracking, build management, deployment, reporting and so on.

It's a strong industry trend amongst the larger players. IBM offers its "Rational" set of integrated ALM tools and Hewlett-Packard has its own ALM offering. The advantage is an integrated, consistent approach to all aspects of DevOps, allowing the team to focus on business requirements, writing good code, and delivering, regular, incremental improvements to the users. The downside is, of course, vendor lock-in. A disadvantage of being cocooned with within a single-vendor ALM solution is that you could end up having to use products like Visual SourceSafe.

By adopting single-vendor ALM, you embrace the specific development model of your ALM platform and adapt established processes accordingly. Remote team-working, for example, isn't easy within VS without Hosted TFS, so you tend not to do it. There is no notion of off-line working; if the network goes down, you stop working, which can be a considerable restraint.

You come to rely on that platform's particular tools, and styles of working, even if it doesn't really match your particular requirements. You may, for example, need a distributed rather than centralized source control system that allow for off-line working. A significant weakness in any one of the tools (bug tracking within TFS is not universally admired, for example) can frustrate the team and slow them down considerably. ALM components tend to be incompatible with any other but their own.

ALMs have the allure of any shrink-wrapped solution, but even if they offer all the tools you need to in order to support development processes, they should, surely, have open APIs and more pluggable architecture so as to allow development teams to swap specific tools in and out in order to fit ALMs to their own requirements, rather than the reverse? This is the area into which the likes of Collabnet and Atlassian seem to be moving.

I'd be interested to hear your thoughts.

Cheers,
Tony.

SQL Code Reuse: teaching a dog new tricks

2011-09-01T16:42:46Z

Developers, by every natural instinct and training, strive to make their code reusable and generic. Dissuading them from doing so, in certain circumstances, is akin to trying to persuade a dog not to fetch a stick. However, when Gail Shaw commented on Twitter last week that "For the record, code reuse in SQL.is not always a good thing", it was more than a causal remark; it was borne out of bitter experience.

The problem is that, in the absence of their usual armory O-O techniques such as encapsulation and inheritance, the price of making database code easier to maintain, by such obvious methods, can be high. The "generic" views, stored procedures and functions that result, may seem elegant and reusable, but can destroy performance, because it is tough for the query optimizer to produce an efficient execution plan. It hurts to make SQL code generic.

At some point, nearly every SQL Programmer gets infected with the feverish idea of passing table names to stored procedures. "Hey, why write scores of procedures to do this process on each table when I can write a generic, reusable procedure that does it on any table!" Bad idea; behind every stored procedure is an execution plan and a stored procedure designed to work with "any table" will result in a generic execution plan that will perform very poorly for a majority of tables. It is far better if they are tailored for specific tables and specific needs.

Another typical example is where the logic for some seemingly-complex calculation has been "abstracted" into a monstrous, but reusable, view, which performs tortuous aggregations and multiple joins, executes appalling slowly, acquires numerous long-held locks and causes severe blocking in the database. Often, such twisted logic can be replaced by simple, easily optimized SQL statements. Granted, it isn't "reusable" and flaunts the 'DRY' (Don't repeat yourself) principle, but it is relatively easy to write and will often perform orders of magnitude faster.

User-defined Functions (UDFs) are another favorite mechanism for promoting code reuse, and are often even more problematic. In-line logic is always much faster, even if to the sensitive developer it has the look of hippos doing line-dancing. Memories of the overuse of UDFs can make any seasoned DBA flinch. If you ever bump into Grant Fritchey at a community event, buy him a beer and ask him about the case of the application with multi-statement UDFs that called other multi-statement UDFs in an attempt at enforcing inheritance in a database. Also ask him how well it scaled beyond a single-user and a single row.

Should SQL Server simply get better at adopting and supporting such basic and universally-accepted programming practices as putting logic in a function? Probably, yes, but in the meantime, we must measure any code reuse in the database against the likely performance penalty.

Perhaps the most effective form of code reuse is via constraints, though it requires lateral thinking to extend this beyond simple data rules. Functions can be used, but extra care and effort is required to write them as inline functions; in-line code or calculated columns will always outperform UDFs. Stored procedure use is to be actively encourage; just don't try to make them generic.

On Simple-Talk we've published a lot about execution plans, query optimization and performance. We believe that, once a developer is aware of the process, they are better able to judge that fine balancing point in the compromise between performance and maintainability. Even better, we hope we've also given a glimpse of an alternative path to those goals, by means of intelligent database design. A neat trick, if you can do it.

Cheers,

Tony.

A DBA's best friend is his tempdb

2011-08-18T16:22:32Z

There is a saying amongst welfare agencies that one can tell how well a family is functioning by looking at their dog. If the dog is neurotic, neglected or maltreated, one fears for the welfare of the children. Likewise, you can tell a lot about the skills of a team of DBAs and developers by looking at the tempdbs on their servers.

The tempdb database is available to all users of a SQL Server instance to house temporary objects such as cursors and tables, and it is where SQL Server creates various internal objects for sorting and spooling operations, as well as index modifications. It can get really busy in there, especially if there are unruly processes. The wise DBA will look after tempdb, giving it plenty of space, making sure it is never mistreated. In short, a happy, moist-nosed tempdb is the mark of a nurturing DBA.

By default, tempdb will be installed, with the other system databases, on the c:\ drive of the SQL Server machine. This is far from ideal in almost all cases. Tempdb requires a lot of space, pre-allocated so files aren't constantly growing. You need more than one tempdb data file; one data file per CPU core is a common recommendation. These files need to be located on drives with the highest write performance possible; a RAID 10 array is a good choice. Also, tempdb storage is one area where Solid State Disks are becoming a popular storage choice in preference to conventional magnetic drives, again due to their vastly higher write performance.

Of course, not all DBAs can afford the luxury of RAID 10 arrays and expensive SSDs. In such cases, special training and vigilance is required. The developers, who exercise the dog, must be encouraged away from complex, unwieldy routines. Structured activity is best, breaking down routines into a series of well-defined steps, and storing intermediate results in a set of explicit temporary tables. In this way, tempdb usage patterns become much easier to predict. Also these tables will hopefully be cached, and the bigger ones can be indexed, both of which will help reduce contention. The DBA must diligently monitor the health of tempdb, detecting the SPIDs of wild processes, investigating and killing them if necessary (the SPIDs, not the developers).

Even given all this, one can't help feel that Microsoft could do more to prevent a lot of tempdb agony. After all, it feels like an area that's going to get worse rather than better, especially as use of Snapshot isolation becomes more prevalent. Aaron Bertrand has filed several tempdb-related Connect items urging Microsoft to offer better advice during the installation process, but to little avail. Judging by the number of forum questions relating to the often-painful process of moving tempdb and reallocating disk space, more action is needed.

Some early adopters have their eyes on the new Contained Databases feature in Denali, hopeful that the concept of user databases independent of "plumbing features" like logins and roles may be a move toward selective workspaces for user processes.

In the meantime, how healthy is the dog in your server? If you've got tales of exemplary nurturing or scandalous abuse, we'd love to hear them. The best story will, as always, win a prize.

Cheers,

Tony.

Bug Me Not

2011-07-08T09:10:13Z

Bug metrics are a notoriously erratic way to judge the performance of a development team and project, but despite this almost all software projects use them. There is a lot of data you can get from an electronic bug-tracking system, from bugs per lines of code, bugs per component, to defect trend graphs and bug fix rates. It is tempting to try to find meaning in the data, but how useful is this data, ultimately, in driving up software quality, in the long term?

If you judge software testers on the number of bugs that they find, then more bugs will be found. If you judge developers on the number of bugs for which their code is responsible, then you'll get much less buggy code, but you'll probably struggle to ship a product on any reasonable timescale. Over the course of a project, it's easy for the team and even individual developers to feel oppressed by the bugs, and under intense pressure to produce 'better' code. Bugs continue to be logged and reported assiduously, but many of them simply disappear into the backlog, to be fixed "at some later time". As the pressure of the ship date mounts, developers are simply forced to cut corners, to change their perception of what "done" really means, in order to increase their velocity, and meet the deadline. Software quality and team morale suffers as result, and despite being rigorously tracked and reported, bugs fester from release to release, since there is never time to fix them. Before long the team finds itself mired in the oubliette.

So how can we use bug metrics to drive up software quality software, over the long term, while enabling the team to ship on a predictable and reasonable timescale? In all likelihood, the surprising answer is "we can't". In fact, the ultimate goal of an agile development team might be to dispense with the use of an electronic bug tracking system altogether!

Certainly at Red Gate, some teams are using JIRA for incoming customer defects, but also a more holistic "technical debt wall", consisting of post-it notes describing the most important issues causing "drag" on a team. They then collectively seek to resolve these issues, whilst striving to remain close to zero new internal defects.

The team works to cultivate an atmosphere of zero tolerance to bugs. If you cause a bug, fix it immediately; if you find a bug in the area in which you're working, tidy it up, or find someone on the team who can. If you can't fix a bug as part of the current sprint, decide, very quickly, how and even if it will be fixed. This is not easy to achieve; it requires, among other things, an environment where it is "safe" for the team to stop and fix bugs, where developers and testers work very closely together, and both are strongly aligned with the customer, so they understand what they need from the software and which bugs are significant and which not.

However, when you get there, what becomes important is not the number of bugs, and how long they stay open in your bug-tracking system, but a deeper understanding of the types of bugs that are causing the most pain, and their root cause. The team are then judged on criteria such as how quickly they learn from their mistakes, by for example, tightening up automated test suites so that the same type of bug doesn't crop up time and again, or by improving the acceptance criteria associated with user stories, so that the team can focus on fixing what's important, as soon as the problem arises.

These are criteria that really will drive up software quality over the long term, and allow teams to produce software on predictable timescales, and with the freedom to feel they can "do the right thing".

What do you think? Is this a truly achievable goal for most teams, or just pie-in-the-sky thinking?

Cheers,
Tony.

Cloud Backup: Getting the Users' Backs Up

2011-06-24T13:40:25Z

On Wednesday last week, Microsoft announced that as of July 1, all data transfers into its Microsoft Azure cloud will be free (though you have to pay for transferring data out). On Thursday last week, SQL Azure in Western Europe went down. It was a relatively short outage, but since SQL Azure currently provides no easy way to take a standard backup of a database and store it locally, many people had no recourse but to wait patiently for their cloud-based app to resume. It seems that Microsoft are very keen encourage developers to move their data onto their cloud, but are developers ready to do it, given that such basic backup capabilities are lacking?

Recently on Simple-Talk, Mike Mooney described a perfect use case for the Microsoft Cloud. They had a simple web-based application with a SQL Server backend; they could move the application to Windows Azure, and the data into SQL Azure and in the process free themselves from much of the hassle surrounding management and scaling of the hardware, network and so on. It was a great fit and yet it nearly didn't happen; lack of support for the BACKUP command almost proved a show-stopper. Of course, backups of Azure databases are always and have always been taken automatically, for disaster recovery purposes, but these are strictly on-cloud copies and as of now it is not possible to use them to them to restore a database to a particular point in time.

It seems that none of those clever Microsoft people managed to predict the need to perform basic backups of Azure databases so that copies could be stored locally, outside the Azure universe. At the very least, as Mike points out, performing a local backup before a new deployment is more or less mandatory.

Microsoft did at least note the sound of gnashing teeth and, as a stop-gap measure, offered SQL Azure Database Copy which basically allows you to create an online clone of your database, but this doesn't allow for storing local archives of the data. To that end MS has provided SQL Azure Import/Export, to package up and export a database and its data, using BACPACs. These BACPACs do not guarantee transactional consistency; for example, if a child table is modified after the parent is copied, then the copied database will be in inconsistent state (meaning, to add to the fun, BACPACs need to be created from a database copy). In any event, widespread problems with BACPAC's evil cousin, the DACPAC have been well-documented, and it seems likely that many will also give BACPAC the bum's rush.

Finally, in a TechEd 2011 presentation tagged "SQL Azure Advanced Administration", it was announced that "backup and restore" were coming in the next SQL Azure CTP. And yet this still doesn't mean that we'll get simple backups as DBAs know and love them. What it does mean, at least, is the ability to restore any given database to a point in time within a 2-week window.

For the time being, if you want a local copy of your data and don't want to brave the BACPAC, one is left with SSIS or BCP, creative use of schema and data comparison tools, or use of SQL Azure Backup (currently in beta) in order to perform this simple but vital task.

Cheers,

Tony.

An Agile House of Straw

2011-03-31T14:39:56Z

The ideal Agile application developer welcomes changing requirements, even late in development. The DBA or Database Developer doesn't. Why is that?

You can't create complex databases in the Agile way, by breaking tasks into small increments, with minimal planning. Building a database that will perform quickly, reliably and securely over time, as it grows, is more like building a house: it involves architecture and has to be planned in detail beforehand. The tables, their data types, their key structure, constraints, and the relationships needed between the various tables, need to be thought out before you start the first cut.

The database structure then needs to be loaded with realistic transactional data and rigorously tested to make sure that the expected results are always returned in the various reports. It needs to be stress tested under load to ensure adequate performance, smooth scalability, and that there are no data integrity issues caused by predicted levels of concurrent access/modification of the data. (If you think SQL Server automatically protects you from such eventualities, it doesn't, as Alex Kuznetsov proves in his Defensive Database Programming book).

When a database architect finally gets all this right, it's only with the greatest reluctance that any changes to the database structure are allowed. Sure, you can make changes, but it is far trickier to do. Every change necessitates another round of regression, stress testing and so on.

This need to test data structures plus data is the critical difference between database and application testing. Data retrieval logic is not enshrined in the SQL code; the database engine decides how to do it based on a range of factors including table/index structure, the data that is stored, its volume, distribution, and so on. This means that, often, each round of testing must use not just "realistic" data but also consistent data, in terms of the data types, ranges of values, and distribution of data within those ranges. The test data must stay the same between builds so that you can compare aggregations to ensure that nothing is broken, and get relative performance figures.

All of this makes Agile database development hard. This becomes a severe problem when the database development becomes a full participant in the Agile process. There is no easy way to accommodate the evolutionary changes that some developers would like to freely make part of an Agile sprint, without destroying the resilience, maintainability and performance of the database. After all, you wouldn't build a house by an evolutionary process, would you?

Cheers,

Tony.

New Wine in New Bottles

2011-03-17T17:15:39Z

How many people, when their car shows signs of wear and tear, would consider upgrading the engine and keeping the shell? Even if you're cash-strapped, you'll soon work out the subtlety of the economics, the cost of sudden breakdowns, the precious time lost coping with the hassle, and the low 'book value'. You'll generally buy a new car.

The same philosophy should apply to database systems. Mainstream support for SQL Server 2005 ends on April 12; many DBAS, if they haven't done so already, will be considering the migration to SQL Server 2008 R2. Hopefully, that upgrade plan will include a fresh install of the operating system on brand new hardware. SQL Server 2008 R2 and Windows Server 2008 R2 are designed to work together. The improved architecture, processing power, and hyper-threading capabilities of modern processors will dramatically improve the performance of many SQL Server workloads, and allow consolidation opportunities.

Of course, there will be many DBAs smiling ruefully at the suggestion of such indulgence. This is nothing like the real world, this halcyon place where hardware and software budgets are limitless, development and testing resources are plentiful, and third party vendors immediately certify their applications for the latest-and-greatest platform!

As with cars, or any other technology, the justification for a complete upgrade is complex. With Servers, the extra cost at time of upgrade will generally pay you back in terms of the increased performance of your business applications, reduced maintenance costs, training costs and downtime. Also, if you plan and design carefully, it's possible to offset hardware costs with reduced SQL Server licence costs. In his forthcoming SQL Server Hardware book, Glenn Berry describes a recent case where he was able to replace 4 single-socket database servers with one two-socket server, saving about $90K in hardware costs and $350K in SQL Server license costs.

Of course, there are exceptions. If you do have a stable, reliable, secure SQL Server 6.5 system that still admirably meets the needs of a specific business requirement, and has no security vulnerabilities, then by all means leave it alone. Why upgrade just for the sake of it? However, as soon as a system shows sign of being unfit for purpose, or is moving out of mainstream support, the ruthless DBA will make the strongest possible case for a belts-and-braces upgrade.

We'd love to hear what you think. What does your typical upgrade path look like? What are the major obstacles?

Cheers,

Tony.

Going by the eBook

2011-03-03T17:28:08Z

The book and magazine publishing world is rapidly going digital, and the industry is faced with making drastic changes to their ways of doing business. The sudden take-up of digital readers by the book-buying public has surprised even the most technological-savvy of the industry. Printed books just aren't selling like they did. In contrast, eBooks are doing well.

The ePub file format is the standard around which all publishers are converging. ePub is a standard for formatting book content, so that it can be reflowed for various devices, with their widely differing screen-sizes, and can be read offline. If you unzip an ePub file, you'll find familiar formats such as XML, XHTML and CSS. This is both a blessing and a curse. Whilst it is good to be able to use familiar technologies that have been developed to a level of considerable sophistication, it doesn't get us all the way to producing a viable publication. XHTML is a page-description language, not a book-description language, as we soon found out during our initial experiments, when trying to specify headers, footers, indexes and chaptering. As a result, it is difficult to predict how any particular eBook application will decide to render a book. There isn't even a consensus as to how the cover image is specified.

All of this is awkward for the publisher. Each book must be created and revised in a form from which can be generated a whole range of 'printed media', from print books, to Mobi for kindles, ePub for most Tablets and SmartPhones, HTML for excerpted chapters on websites, and a plethora of other formats for other eBook readers, each with its own idiosyncrasies.

In theory, if we can get our content into a clean, semantic XML form, such as DOCBOOKS, we can, from there, after every revision, perform a series of relatively simple XSLT transformations to output anything from a HTML article, to an ePub file for reading on an iPad, to an ICML file (an XML-based file format supported by the InDesign tool), ready for print publication. As always, however, the task looks bigger the closer you get to the detail.

On the way to the utopian world of an XML-based book format that encompasses all the diverse requirements of the different publication media, ePub looks like a reasonable format to adopt. Its forthcoming support for HTML 5 and CSS 3, with ePub 3.0, means that features, such as widow-and-orphan controls, multi-column flow and multi-media graphics can be incorporated into eBooks. This starts to make it possible to build an "app-like" experience into the eBook and to free publishers to think of putting context before container; to think of what content is required, be it graphical, textual or audio, from the point of view of the user, rather than what's possible in a given, traditional book "Container".

In the meantime, there is a gap between what publishers require and what current technology can provide and, of course building this app-like experience is far from plain sailing. Real portability between devices is still a big challenge, and achieving the sort of wizardry seen in the likes of Theodore Grey's "Elements" eBook will require some serious device-specific programming skills.

Cheers,

Tony.

Hype and LINQ

2011-02-02T12:18:00Z

"Tired of querying in antiquated SQL?"

I blinked in astonishment when I saw this headline on the LinqPad site. Warming to its theme, the site suggests that what we need is to "kiss goodbye to SSMS", and instead use LINQ, a modern query language! Elsewhere, there is an article entitled "Why LINQ beats SQL".

The designers of LINQ, along with many DBAs, would, I'm sure, cringe with embarrassment at the suggestion that LINQ and SQL are, in any sense, competitive ways of doing the same thing. In fact what LINQ really is, at last, is an efficient, declarative language for C# and VB programmers to access or manipulate data in objects, local data stores, ORMs, web services, data repositories, and, yes, even relational databases.

The fact is that LINQ is essentially declarative programming in a .NET language, and so in many ways encourages developers into a "SQL-like" mindset, even though they are not directly writing SQL. In place of imperative logic and loops, it uses various expressions, operators and declarative logic to build up an "expression tree" describing only what data is required, not the operations to be performed to get it. This expression tree is then parsed by the language compiler, and the result, when used against a relational database, is a SQL string that, while perhaps not always perfect, is often correctly parameterized and certainly no less "optimal" than what is achieved when a developer applies blunt, imperative logic to the SQL language.

From a developer standpoint, it is a mistake to consider LINQ simply as a substitute means of querying SQL Server. The strength of LINQ is that that can be used to access any data source, for which a LINQ provider exists. Microsoft supplies built-in providers to access not just SQL Server, but also XML documents, .NET objects, ADO.NET datasets, and Entity Framework elements. LINQ-to-Objects is particularly interesting in that it allows a declarative means to access and manipulate arrays, collections and so on. Furthermore, as Michael Sorens points out in his excellent article on LINQ, there a whole host of third-party LINQ providers, that offers a simple way to get at data in Excel, Google, Flickr and much more, without having to learn a new interface or language.

Of course, the need to be generic enough to deal with a range of data sources, from something as mundane as a text file to as esoteric as a relational database, means that LINQ is a compromise and so has inherent limitations. However, it is a powerful and beautifully compact language and one that, at least in its "query syntax" guise, is accessible to developers and DBAs alike. Perhaps there is still hope that LINQ can fulfill Phil Factor's lobster-induced fantasy of a language that will allow us to "treat all data objects, whether Word files, Excel files, XML, relational databases, text files, HTML files, registry files, LDAPs, Outlook and so on, in the same logical way, as linked databases, and extract the metadata, create the entities and relationships in the same way, and use the same SQL syntax to interrogate, create, read, write and update them."

Cheers,

Tony.