A developer suggested to me recently that the life of the DBA was, surely, a dull one. My first reaction was indignation, but quickly followed by the thought that for many people excitement isn't necessarily the most desirable aspect of their job.

It's true that some aspects of the DBA role seem guaranteed to quieten the pulse; in the days of tape backups, time must have slowed to eternity for the person whose job it was to oversee this process, placing tapes into secure containers, ensuring correct labeling, and.sorry, I drifted off there for a second. On the other hand, if you follow the adventures of the likes of Brent Ozar or Tom LaRock, you'd be forgiven for thinking that much of a database guy's time is spent, metaphorically, diving through plate glass windows in tight fitting underwear in order to extract grateful occupants from burning database applications.

Alas it isn't true of the majority, but it isn't as dull as some people imagine, and is a helter-skelter ride compared with some other IT roles. Every IT department has people who toil away in shadowy corners doing quiet but mysterious tasks. When you ask them to explain what they do, you almost immediately want them to stop, but you hear enough to appreciate that these tasks are often absolutely vital to the smooth functioning of an IT organization. Compared with them, the DBAs are prima donnas.

Here are a few nominations:

• Installation engineer - install all of the company's laptops and workstations, and software, deal with licensing, shipping and data entry.many organizations, especially those subject to tight regulation, would simply grind to a halt without their efforts.
• Localization engineer - Not quite software engineering, not quite translation, the job is to rebuild a product in a different language and make sure everything still works.
• QA Tester - firstly, I should say that the testers at Red Gate seem to me some of the most-fulfilled in the company. I refer here to the QA Tester whose job is more-or-less entirely to read a script, click some buttons and make sure the actual and expected values match.
• Configuration manager - for example, someone whose main job is to configure build environments so that devs can access their source code; assuredly necessary for the smooth functioning and productivity of the team, and hopefully well-paid.

So what other sort of job in IT should one choose if the work of a DBA proves to be too exciting? Or are these roles secretly more exciting than many imagine? I invite you all to put forward your own suggestions.

Cheers,
Tony.

I once wrote an editorial, multi-core mania, on the conundrum of ever-increasing numbers of processor cores, but without the concurrent programming techniques to get anywhere near exploiting their performance potential. I came to the.controversial.conclusion that, while the problem loomed for all procedural languages, it was not a big issue for the vast majority of programmers. Two years later, I still think most programmers don't concern themselves overly with this issue, but I do think that's a bigger problem than I originally implied.

Firstly, is the performance boost from writing code that can fully exploit all available cores worth the cost of the additional programming complexity? Right now, with quad-core processors that, at best, can make our programs four times faster, the answer is still no for many applications. But what happens in a few years, as the number of cores grows to 100 or even 1000? At this point, it becomes very hard to ignore the potential gains from exploiting concurrency. Possibly, I was optimistic to assume that, by the time we have 100-core processors, and most applications really needed to exploit them, some technology would be around to allow us to do so with relative ease.

The ideal solution would be one that allows programmers to forget about the problem, in much the same way that garbage collection removed the need to worry too much about memory allocation. From all I can find on the topic, though, there is only a remote likelihood that we'll ever have a compiler that takes a program written in a single-threaded style and "auto-magically" converts it into an efficient, correct, multi-threaded program.

At the same time, it seems clear that what is currently the most common solution, multi-threaded programming with shared memory, is unsustainable. As soon as a piece of state can be changed by a different thread of execution, the potential number of execution paths through your program grows exponentially with the number of threads. If you have two threads, each executing n instructions, then there are 2^n possible "interleavings" of those instructions. Of course, many of those interleavings will have identical behavior, but several won't. Not only does this make understanding how a program works an order of magnitude harder, but it will also result in irreproducible, non-deterministic, bugs. And of course, the problem will be many times worse when you have a hundred or a thousand threads.

So what is the answer? All of the possible alternatives require a change in the way we write programs and, currently, seem to be plagued by performance issues. Software transactional memory (STM) applies the ideas of database transactions, and optimistic concurrency control, to memory. However, working out how to break down your program into sufficiently small transactions, so as to avoid contention issues, isn't easy. Another approach is concurrency with actors, where instead of having threads share memory, each thread runs in complete isolation, and communicates with others by passing messages. It simplifies concurrent programs but still has performance issues, if the threads need to operate on the same large piece of data.

There are doubtless other possible solutions that I haven't mentioned, and I would love to know to what extent you, as a developer, are considering the problem of multi-core concurrency, what solution you currently favor, and why.

Cheers,

Tony.

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.

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.

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.

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.

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.

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.