Tony Davis

The myth of over-normalization

2008-07-21T15:36:00Z

I’ve always been suspicious of denormalizing an OLTP database. Denormalisation is a strange activity that is supposed to take place after a database has been normalized, and is assumed to be necessary in order to reduce the number of joins in queries to a tolerable level. C.J. Date is quite clear on this; well, he is slightly less opaque than usual: any denormalization to a level below 5NF is a ‘bad thing’ (he says ‘contraindicated’).

In practice, normalization to fifth Normal Form is unusual. Normally, the Database designer reaches for his hat and coat after reaching Boyce/Codd Normal Form (BCNF), which is 4NF, and few databases I’ve ever seen are even reliably BCNF.

Why does one ever normalize a database? It is often said that it is to avoid logical inconsistencies, and to avoid insertion, delete and update anomalies. Also, to avoid redundancy, or duplication, of data. I think there is more to it than that. It also ensures that your data model makes logical sense. If, at the end of the normalization process, you arrive at a set of tables that correspond to simple, easily understood entities, then the chances are that you have got a database model that will sail through the inevitable changes in scope, changes in the application, extensions and so on. If you don’t, then it is time to tear up your database design and start again. Normalization isn’t like sprinkling on fairy-dust; it is a way of testing and ‘proving’ your design.

Too often, denormalization is suggested as the first thing to consider when tackling query performance problems. It is said to be a necessary compromise to be made when a rigorous logical design hits an inadequate database system. As the saying goes, “Normalize ‘til it hurts, then denormalize ‘til it works”. In fact, Denormalization always leads eventually to tears. It complicates updates, deletes and inserts; it renders your database difficult to modify. Maybe once there was an excuse for a spot of denormalization, but on a recent version of SQL Server or Oracle, with indexed or materialized views, and covering indexes, the performance hit from multiple joins in a query is negligible. If your database is slow, it isn’t because it is ‘over-normalized’!

As always, we'd like to hear what you think. The best comment, according to our distinguished panel of judges, will receive a $50 Amazon voucher, and three runners-up will get a Simple-Talk gift pack.

Cheers,

Tony.

VB.NET: The Ugly Duckling

2008-07-08T10:35:00Z

One of the greatest pleasures of programming in C# is that wonderful, giddy, feeling of superiority one has over VB.NET programmers. In C#, one has a vague awareness that what one is doing has elegance and style. It must be the effect of the curly braces. By contrast, VB.NET steadfastly maintains its status as the 'ugly duckling'.

Is this really justified? After all, a routine that is programmed in any of the.NET Framework languages will be compiled to almost the same IL opcode sequence and the JIT-compiler will produce very similar CPU instructions: Therefore, VB.NET is likely to be as fast as C# or any other .NET Framework language. It is also just as versatile: after all, Microsoft used it to write the Silverlight JavaScript compiler, and the current VBx (VB 10) compiler was written in VB.NET.

The negative perception is due, in part, to the fact that the first 'DotNet' version, Visual Basic.NET 7, gave every appearance of having been slapped together in a big hurry. It had fewer features than VB 6, and abandoned VB's greatest asset: that of being a dynamic language for rapid application development. Despite this, Visual Basic became the most popular .NET language (Heffner 2005, Forrester Research) with over twice as many users as C#. Visual Basic Express Edition is by far the most popular download and all indications from postings in blogs and forums is that Visual Basic is the most popular of the DotNet languages. Of course, there was already a large reservoir of skilled VB programmers in the industry, but given that by 2005 most VB programmers had long ago added C# to their skill-set, its continued popularity indicates that VB has an appeal all of its own.

VB 8 quietly and steadily closed the gap on C# and VB 9, the current version, introduced all sorts of useful features such as XML literals, XML axis properties, and Smart Query expressions. The forthcoming version, Visual Basic .NET 10.0 (VBx), could be the release that finally cures its inferiority complex once and for all. It uses the Dynamic Language Runtime (DLR) so can be used for application scripting, just like VBScript and VBA. It also allows dynamic methods and type generation. Use of the DLR makes late' or 'dynamic' binding almost as fast as static binding, and fully returns VB to the immediacy that VB 6 had as a quick way to get scripting done.

We all love Java and its cousin C#, of course, but once the Dynamic Language Runtime, and the corresponding Da Vinci Machine, are perfected, I suspect the language debate will change. Both the Java Virtual Machine and .NET have, so far, constricted the effective choice of development languages because they didn't support dynamically-typed languages such as Python and Ruby. Once this is fixed, it will result in greater choice and allow the programmer to choose the language that best fits the task in hand, rather than the one best honed to the inherent limitations of the framework.

Cheers,

Tony.

SQL Server Tumbleweed Awards

2008-06-24T13:35:00Z

Several parts of SQL Server look as though they were started and then suddenly abandoned. The classic example is the TEXT datatype. Phil Factor has a theory on most things, and in this case it's that the programmer responsible for implementing the TEXT datatype at Microsoft succumbed to the unbearable pressure of the job, one day, leaping up in his cubicle, throwing off all his clothes, and flinging himself through the window, shouting 'I must join the penguins'. As a memorial to their departed colleague, his sorrowful team left the code in SQL Server at exactly the point it had been abandoned.

Anyone who has tried to use the TEXT (or IMAGE) datatype will immediately sympathize with Phil's theory. It has so many restrictions placed on it, and so many quirks, that using it is like training a mule to jump through hoops.

Two other examples spring to mind, of features that seem to have been abandoned in mid-development. The first is the SSMS Templates. The macro processor that allows actual values to be substituted for placeholders is valuable, but curiously primitive in its implementation; more like a 'proof of concept' exercise. The datatype field doesn't seem to be used at all, leaving just a crude system of string substitution with no attempt at validation. It is odd that such a useful system has never been developed further.

The second is SQLCMD. Anyone who bases a robust scripting system on SQLCMD soon becomes frustrated by its limitations. Why are there so many differences between the commands in the command-line version of the tool and the SSMS 'SQLCMD mode'? Why can't variables be used in expressions? Even the simplest conditional statement is impossible. One cannot perform any operations such as concatenation or simple maths on values within variables. This is no harder than a second-year undergraduate project.

I wonder if the SQL Server product is now so colossal that not even Microsoft's highly-advanced project management techniques can prevent the odd component from falling through the cracks. The TEXT data type has, to general relief, been replaced by VARCHAR(MAX), but the issues with SSMS Templates and SQLCMD remain.

Templates provide arguably the first, and best, productivity aid that a database developer should use and yet, under-developed and under-publicized by Microsoft, they remain a mystery to all but a few. SQLCMD is extraordinarily powerful, and many DBAs reach for it rather than SSMS, only to become frustrated by the inconsistencies and weaknesses of its scripting variables.

It reminds me of those, somehow elegiac, "work in progress" signs that one sometimes encounters on a remote back road, where no-one seems sure what work was planned or whether it will ever be completed. We encourage your own nominations for Simple-Talk's 'SQL Server TumbleWeed' award. The winning nomination, added as a comment to the editorial blog, will receive a $50 Amazon voucher, and three runners-up will get a Simple-Talk gift pack.

Cheers,

Tony.

CLR, beer and turkey

2008-06-06T09:07:00Z

It is strange that two important programming techniques in a SQL Server Database can't be done in TSQL. The first is, of course, being able to read multiple results from a stored procedure. You can do it in ODBC/ADO and so on, but not in TSQL. The other obvious failing is that one cannot write user aggregate functions (which perform a calculation on a set of values and returns a single value) in the same way as scalar or table functions, except by using the CLR integration. TSQL can't help you to add your own functions to SUM(), COUNT(), MAX() MIN() etc.

CLR integration was, along with Notification Services, the surprising turkey of the SQL Server 2005 launch. In reality, it was often doubling the development time over T-SQL procedures/functions. Whereas the task of building a function in T-SQL is trivial (load up management studio, edit the template, save and test), the construction of a CLR function was only made easy in the expensive VS Pro. Even so, there were two extra steps and a lot more for us who had only VS Standard. Microsoft had failed to build the CLR development process into Management Studio. CLR integration was over-sold as an alternative to TSQL rather than a replacement for Extended Stored procedures.

Where you have to construct a custom aggregate function, then CLR is the only way there is, so should that be enough to send us scurrying to our cheque-books to buy VS Pro? We had an editorial discussion about doing a workbench on CLR Aggregate functions. Phil loved the idea, but insisted on a really useful example, that readers would actually want. Even a couple of Adnams Broadsides failed to bring from any of us an example besides concatenation, and that is already in Books-on-Line, and can be done without CLR. The idea is still there on the back-burner.

So, what are you using CLR for? Are you writing user aggregation functions, or using it to access powerful CLR libraries such as Regex? Even if you've looked at it and abandoned the idea of using it, we'd be interested to hear why. Usual rules apply – a prize to the best contribution.

Cheers,

Tony.

The Object-oriented Orthodoxy and the DBA

2008-05-27T16:11:00Z

Server Management Objects (SMO) is a very impressive product. SMO (and its previous incarnation, SQL-DMO) is essentially an object-oriented interface into the management of SQL Server installations and databases. It provides an intuitive way for the VB or C# programmer to automate any operation that can be performed via SSMS.

We've run a few SMO articles in the past and to our surprise they have been met with little more than polite indifference. So, what is the problem? Well, firstly, DMO-based scripts worked via OLE automation. They could be run from any scripting language, even TSQL. Many DBAs spent many months DMO-automating their administration tasks only to find that they couldn't be used in SQL Server 2005. To use SMO means Visual Studio and NET Framework only. Microsoft eventually did a U-turn and released DMO for 2005 (although without support for features specific to SQL 2005), but it still created a lot of ill feeling among DBAs.

A more fundamental problem is that the Object Oriented model, while crystal clear to the geeks at Microsoft, is alien to many of the DBAs who were the product's intended users. There is a vast cultural gap between SQL and C#.

In SQL Server 2008, with the introduction of PowerShell and "PowerSMO", I wonder if Microsoft is about to make the same mistake again. PowerShell may be crystal clear to the dome-heads of Redmond, but is as intuitive as runic scripts to many of the DBAs at the coal-face.

Personally, I think that Microsoft would stand a much better chance of having the DBA community embrace the technology with open arms, if they had instead provided a refined relational model for the management of SQL Server (think DMVs, but more versatile, with CRUD operations).

What do you think? Is it high time that Microsoft acknowledged that there is room for a richer diversity of IT culture, and that it is futile to try to impose the C# object-oriented orthodoxy on everyone? Or do DBAs need to "adapt or die"? Add your comments to this blog and the best entry will receive a prize.

Cheers,

Tony.

The DBA Daily Checklist

2008-05-13T14:56:00Z

The average DBA has to perform many routine checks on his or her servers. There will be daily checks, weekly checks and, probably, monthly checks. A short while ago, we were having quite a debate about what items would be on the DBA Daily checklist. Because we all like lists with ten items in them, we compiled a list that goes something like this:

Connectivity: Make sure each database is available and accessible both by logging in via user applications, as well as running test scripts.
Backups: Check database and log backups, archiving and offsite storage.
Events: check all database logs, application logs and system logs, Agent history, device logs, NIC logs etc. Investigate any job failures
Processes: Check that all required processes on the server are running, including Replication
Integrity: Perform all database and server integrity checks; look for objects that break rules
Indexes: Check on indexes to see if they are being used, need re-creating, or if any are missing
Volumetrics: Check resources on the server such as files sizes and disk space, and monitor growth
Performance: Check application performance, and performance statistics, using the Perfmon tool; research and resolve any issues.
Procedures: Check all Disaster Recovery Plans
Security: Look for security policy violations

We thought we had a fairly exhaustive list, but then a Simple-Talk forum entry made us think again. Randyvol had posted in response to our DDL Trigger workbench:

"…last week some idiot turned a host of triggers off in our ERP system, causing a cascade of posting problems on dozens of orders before we caught the root cause…"

He wanted to know if there was some way of checking whether Triggers have been disabled. It may be unusual and obscure, but it makes perfect sense that on some production servers you will need to check to see if triggers have somehow been disabled. The consequences could be dire.

We added this check to our list but then began thinking: what else are we missing? There are so many different ways that SQL Server is used that there must be a host of other checks that could make all the difference.

As always, we'd like to hear what you think. Post your suggestions as a comment to this blog, and you'll go into the draw to receive one of five Simple-Talk gift bags!

Cheers,

Tony.

Bad Database Security

2008-04-29T18:08:00Z

The Daily WTF recently reported that the Sexual and Violent Offender Registry of Oklahoma had to shut down its website for 'routine maintenance'. It turns out that this routine maintenance was necessary because 10,597 social security numbers from sex offenders had been downloaded, by SQL injection.

Sadly, this is not an isolated case. There has been a recent spate of SQL injection attacks, most of which have been blamed on IIS. However, closer inspection, in this case, reveals that the JavaScript file responsible was merely testing for all the well-known security loopholes, many of which are open due to poor database security.

SQL injection attacks rely on several fundamental, but frustratingly common mistakes:

Allow the end-user or the logins assigned to the website direct access to the base tables.
Perform inadequate checking of input (inadequate type handling, incorrectly filtered escape characters)
Perform no parameterisation of input in the calls to the database.

Without wishing to re-open an old debate, Simple-Talk has long advised that the interface between the application and the SQL Server database should always use stored procedures. However, many are still resistant to the idea.

The attacker who is out to get data also needs to have it delivered on-screen, as was conveniently provided in the Oklahoma incident. Database errors should never be displayed on-screen in a production system. On the contrary, all errors, even 404 errors, should be logged and emailed to production staff as part of the general alerting system for any corporate application.

No longer can developers sit back in their chairs once an application is shown to perform to specification. They must prove that their application can counter all current security threats and be able to report when an attack happens. When implemented, you may be shocked by the vast number of attempted intrusions to which the typical application is subjected.

As always, your views are encouraged. Add your comments to this blog and the three best entries will win a Simple-Talk gift pack, complete with polo shirt and much-coveted USB dongle.

Cheers,

Tony.

The best and worst ways to optimize your code

2008-04-16T12:55:00Z

I was chatting to Phil Factor the other day about the slow start-up of some CLR applications. He started telling me, with apparent irrelevance, how he once, a long time ago, developed a database system in Z80 assembler code and a large eight-inch floppy disk. The point he was actually making was this: the more you know about what an application is doing, the more likely you are to be able to fix slow-running code. Through watching the bare disk drive on his desk as it whirred and clicked, he could actually watch the reads and writes, the head position, and the tracking across the drive. It gave him a lesson for life in the importance of performance metrics.

Much has changed, but the need to monitor the performance of an application hasn't. With the right information, you will see at a glance any unnecessary initialization, premature loading of data or blocking process. You still need a tool that will allow you to monitor down to the details of thread creation, thread switching and context switching,

Performance Counters are the first resort when investigating or understanding a performance problem. There are performance counters for almost every aspect of the CLR and .NET Framework. They are always available and do not change the performance characteristics of your application. Once you have the big picture, then a profiler will soon tell you where the causes of performance problems lie.

Armed with this information, it is a matter of great job satisfaction to be able to make a big gain through a small adjustment to the code. It is possible to tweak any code to make it run faster, usually by changing the algorithm, but it will only have a perceptible effect if it is done in the right place. The art of improving performance is in knowing where to make those alterations.

There are some dreadful ways of making code trying to make code run fast. The worst mistake is to write your code in a machine-Friendly-human –unfriendly way, an art beloved of a few Perl programmers. Another bad idea is to compromise a good Object-oriented model in pursuit of small savings in Garbage-collection, a process equivalent to the sin of de-normalisation. It is programming without all your faculties, blind to what is going on and, even worse, without the faculty of thought.

We encourage your nominations for the least (and most!) effective ways to optimize your code. The best contribution, added as a comment to this blog, will win a $50 Amazon voucher. The winner last time, for their contribution to the "That's not a database, it's a spreadsheet" editorial, is digory!

Cheers,

Tony.

That ain't a database, it's a spreadsheet!

2008-04-01T08:21:00Z

"That ain't a database, it's a spreadsheet!"

From the Sayings of Phil Factor

There is a world of difference between an enterprise-level relational database and a 'repository of persistent data'. Until you've had the experience of dealing with a high-volume, high-transaction database with large amounts of data, the truth of this doesn't really hit home.

The constant friction between the relational and the object-oriented model is due to a misunderstanding. Something that works well in the small scale doesn't necessarily cut it at the enterprise level. Programmers tend not to appreciate the importance of indexes, stored procedures, referential integrity, constraints, or even normalisation, until they have experienced an enterprise-scale database. DBAs, in turn, find it hard to repeatedly explain the reasons. The result is often a guerrilla war between the database developers and application developers.

By the same token, where a database is unlikely to become large, or have challenging performance requirements, there is a lot to be said for techniques such as Object-Relational Mapping using Entity Framework or Hibernate, and the use of XML. Anything that can reduce the labour of application development should be considered with an open mind.

But it has to be tested.

From the very first stages of designing a development project, you must test your proposed data-handling architecture against the worst buffeting that a production system can experience, with the data volumes and characteristics you can expect. The interface between the data and application layer has to withstand the stress of real data volumes. It has to withstand attempts at intrusion or malicious damage. There is no substitute for this process, in terms of uncovering any issues with the design of your application.

This week, Red Gate launches SQL Data Generator, which we hope will help a lot with this sort of work. The tool can fill a database with enough data to expose any weaknesses in the design of a database, and the application that uses it. The team have worked hard to make sure the data generated is as close to reality as possible, in order to avoid those bugs that come from developers making assumptions.

So, if you're working with an enterprise scale database, and are tempted to introduce one of the "latest and greatest" enterprise technologies to your application – step forward Object-Relational Mapping, XML Columns, and Entity-Attribute-Value modelling, to name but three –then we strongly suggest you test your solution thoroughly, against millions of database rows and high transaction volumes. You may find it a sobering experience.

Perhaps the great divide between the object and relational cultures is there for a reason. What do you think? Add a comment to my blog, and the best contribution will receive a $50 Amazon voucher.

The winner of the voucher for their contribution to my previous "Not the right place" editorial is TadRichard.

Cheers,

Tony.

Not the right place

2008-03-17T10:43:00Z

Many orthodoxies, or 'truisms', exist in IT; rules of programming that emerge in the light of shared experience and are then passed on from programmer to programmer and instilled as "the right way" to tackle a certain problem. The difficulty is that many of these truisms persist long after advances in technology have rendered them obsolete.

There is one persistent truism that always provokes vigorous head nodding amongst developers, and that is the assertion that the database is "not the right place" for any data presentation logic. According to this rule, SQL Server has no place in the presentation or rendering of data; its role is only to provide the raw datasets that can then be used in procedural code to represent the data on the screen.

However, with the introduction of the XML data type, as well as non-size-restricted types such as varchar(max), has SQL Server 2005 tipped the balance of the argument? Perhaps, now, there are circumstances where TSQL can make a strong contribution to the presentation of data

A while back, Simple-Talk published the Cross-Tab Pivot-table workbench, which showed one of several ways of providing cross-tab reports using TSQL. In our example, a stored procedure, spDynamicHTMLCrossTab, produced the HTML code which could be rendered directly on the browser. One of our readers responded by showing how it could be done using XML.

While well-received, there was the usual nervousness expressed by some readers, about TSQL code producing HTML. Nobody nowadays would bat an eyelid in surprise if you used SQL code to produce XML, which would subsequently be translated into HTML via XSLT. Why then strain at a gnat after swallowing a camel like this?

Sometimes, there will be cases where the insertion of extra logic in a .NET layer doesn’t seem to add much: If, for example, you wish to represent a hierarchical directory on a browser, such as an organisational list, is there anything intrinsically wrong, or morally suspect, in generating an XHTML fragment directly from a stored procedure, which could then be rendered on a browser? After all, the database knows all the factors which determine how a hierarchy should be represented.

Of course, there still remains a line which the database programmer should not cross. The database must never impose any restrictions in the way that data is rendered. The mark-up should be used to delimit the parts of the data in order to show the nature of the data, and its logical structure, without constraining the application programmer in any way.

An interesting comment was made at the Denver Code Camp last week that the interface between the database and the application developer should be a contract. This should be drawn up as part of the planning of a project in the light of the special demands and requirements of each project and team, rather than relying solely on existing orthodoxies.

We support this proposal, becasue we think it would force developers to constantly challenge these orthodoxies to make sure they are still true.What do you think? We'd love to hear from you and the best entry, made as a comment to this blog (you'll need to be signed in), will receive a $50 Amazon gift voucher.

Thanks to everyone for the fantastic response to the previous "How to layout SQL Code" editorial. The winner of the Amazon voucher is Tore. However, I give special mention to Alex Kuznetsov, louisducnguyen and billweh, who made it a tough choice.

Cheers,

Tony.

How should you layout code?

2008-02-27T11:02:00Z

It is strange to see the heat generated over arguments about how code should be formatted. With Visual Studio, of course, it isn't much of an issue, as it is done for you, but the closer you get to the 'live free and die' communities of the LAMP platforms, the more contentious it gets. Likewise, SQL Server's TSQL inhabits a strange land where there seems little consensus over the correct way to lay it out.

There are some great tools for doing automatic layout of code. Visual Studio sets a high standard for this, but other IDEs have caught up. Visual Slickedit is hard to beat as a general-purpose programmers' editor, with facilities to tidy up almost any language. Nevertheless, for the most part, each language has its favourite tool. A few examples that spring to mind:

A perennial favourite for .NET code is Jetbrains ReSharper, thought by many to offer better formatting capabilities than native Visual studio.
For PHP, I've always liked Waterproof's PHPedit, and their command-line phpCodeBeautifier works well and is free.
There is nothing to beat HTMLTidy for tackling HTML and XML layout
Topstyle is the only tool I know of that sorts out CSS layout properly
Aptana studio does a great job in laying out JavaScript.
SQL Refactor is an essential tool for untidy SQL Server 2005, programmers. I've found very little else to touch it as a SQL code beautifier.

I was recently re-reading Joe Celco's excellent book 'SQL Programming Style' and it struck me that there really wasn't much in what he says about the layout of code that one could argue with. However, the feedback that Red Gate gets over the SQL Refactor product is that providing 40 different options for laying out SQL code just isn't enough.

After years of 'anything goes' SQL coding, it is hard to get any consensus on the right way to lay it out. Every large IT department seems to have its own standard, ranging from incredibly strict, to "everything in lower case". I have a sneaking tolerance towards the latter, liberal approach but, as the editor of a website that has a lot of SQL on it, I have my own reasons to hope for some sort of consensus.

It is worse still for our sister website, SQLServerCentral.com. In this case, people describe their coding problems in forums, and sometimes paste in code that makes one involuntarily flinch. I can see how years of suffering in this way occasionally turns Joe Celco from his usual mild Dr Jeckell persona into a Forum Mr Hyde. Jeff Moden is now famous for his attempts to try to persuade forum users to adopt a Forum etiquette that includes writing SQL that can easily be understood by others.

In short, there would seem to be an obvious need to tighten up standards somewhat. So, how's this for a suggestion:

Identifiers:

No Reverse Hungarian notation
Use ISO-11179 where appropriate
Up to 30 characters.
Avoid abbreviations.
Avoid quoted (delimited) identifiers,
Use standard postfixes
Use only letters, digits and underscores for names
Scalars should be in lower case
Schema object names should be capitalised
Reserved words should be in upper case.

Layout:

Use a comma, followed by one space (or newline), as a delimiter in a list.
Use one space only between language tokens
Use a tab-space of three ems (spaces)
Put each clause on a new line, indenting if it is a subordinate clause or subquery.

Disagree? We'd love to hear from you - jsut add your suggestions as a comment to this blog (you'll need to be signed in). The best contribution will receive a $50 Amazon voucher!

Cheers,

Tony.

SQL Server Diagnostics

2008-02-12T11:38:00Z

SQL Server diagnostics are a mess. To monitor the well-being of a server, you need to be able to inspect all logged errors, have a whole raft of performance information, WMI data, and be able to study the history of backups and scheduled jobs. You have to monitor server downtime, low disk space, low physical memory, job failures, jobs failing to start, jobs taking too long to finish, blocks, locks and long running queries. There will be DBCC checks too. Because the beast we think of as SQL Server is actually built as a collection of services, you need to look for information in several places, including the host operating system. As well as that, you need to check periodically on the top cached query plans just to ensure that there is nothing awry with indexes or application design. You need also to check for long-running SPIDs, intrusion attempts, and patterns of usage that indicate fraud.

The DBA with around a hundred production servers may rightfully start to believe that her/his workload is excessive, especially as there are a whole raft of other Database administrative functions to perform as well. Around two years ago, the last time we got reliable information, Google admitted to half a million PCs serving information. It must be a lot more now, and it would be interesting to know how they are administered.

The past twenty years have seen an explosion of techniques for distributing the holding, analysis and processing of data. The monolithic systems of a previous generation have, in some cases, been replaced by container-loads of commodity PCs. Relational databases haven’t always kept pace with the change.

A group of SQL Servers requires an approach that is more organic. One has to think of syndromes, where one deduces a cause from a variety of symptoms or indications. It is one thing to collect all the indications of trouble, but quite another to work out the underlying cause. What then are the key symptoms; The twinges of pain the spots, the rashes, the depressions? What, even, is the catalogue of illnesses?

There have been many fascinating approaches to this problem, and a number of software tools that seek to provide the panacea. SQL Response Beta really is a Beta, in that it provides a framework, in order to find out from real users what the key requirements are. It acknowledges that the DBA really knows best. Even this early Beta is the result of much reflection on the predicament of DBAs in administering large numbers of Servers. It doesn’t pretend to provide all the answers. Dan and the other developers really want to know whether they are on the right track; what changes or improvements are necessary to make it more useful; and anything else that would be important to professional DBAs with heavy workloads.

One speaks of DBAs as though they were a uniform breed with similar requirements. The reverse is truer, and it would be fascinating to learn of some of the extremes of the tasks facing working DBAs, and whether a software tool could really help the workload significantly. Do you have a vast number of servers, a huge jelly-mass of inscrutable scripts? Post your headaches as a comment to my blog. All entries will go into a draw for a $50 Amazon voucher!

Cheers,

Tony.

Katmai: Ship date or ship-shape?

2008-01-29T14:11:00Z

By now, most of you will have read the news of the delay in releasing SQL Server 2008. It all seems fairly typical Microsoft, though I wouldn't want to be overly critical, even if the bizarre blog announcement, written in strangled Dalek-speak, was almost beyond parody (although Phil Factor had a good attempt).

In reality, it isn't a huge delay, and the sense that the product is slipping has probably been exaggerated by the fatuously-entitled 'Heroes Happen Here' launch wave, which attempted to roll three "launches" into one. This meant that the VS2008 launch was too late, the Windows Server 2008 launch was about right, and the SQL Server 2008 launch was much too early.

Compared with the two-year delay to SQL Server 2005, mostly caused by security issues, the current progress seems acceptable. Nobody wants a product with bugs in it and a subsequent trail of fat service packs. The only time to ship a product is when everyone agrees it is ready. Within reason, the date of release isn't the big issue; it is whether the product will be sufficiently improved to make upgrading an obvious business decision for the companies that use SQL Server.

Nobody who lives by producing commercial software can really condemn product slippages. Certainly, Red Gate has slipped a product or two in its time (SQL Prompt 3 being one), but the overall goal was to ensure that the tool, when it finally arrived, was good and fit for purpose.

OK, a 2-year delay on SQL Server 2005 was excessive, but when it finally arrived, it was pretty good. Or was it? We all have our bugbears, the parts of SQL Server we like to criticize, such as SSMS. However, it would be fascinating to hear your nominations for the one aspect of SQL Server 2005 that could have done with another year's development before it saw the light of day, and why.

Post your nominations as a comment to my blog. All entries will go into a draw for a $50 Amazon voucher!

Cheers,

Tony.

The Decline of IE and the rise of browser-based desktop applications

2008-01-22T18:37:00Z

Until recently, the Internet Explorer V8 group had been a pretty taciturn bunch. However, a few weeks ago, Dean Hachamovitch suddenly popped up with a blog entry announcing that the new IE8 browser had passed the Web Standards Project 'Acid2' test. The blog was full of aspirations about Microsoft's commitment to standards, and their Acid2 test was held up as a sign of this.

There was a time that this sort of news would have been significant. No longer. Safari passed the Acid2 test over two years ago, with Opera managing it soon afterwards. Besides Internet Explorer, only the Gecko-based browsers such as Firefox are yet to pass, but Gecko 1.9 beta already passes.

Microsoft's decline in their share of the Browser market has been slow but remorseless. The other day, I suddenly noticed that I was no longer using Internet Explorer, other than for testing Web sites against IE 6 and 7.

The breaking point for me was the advent of the Firebug debugger. Debugging JavaScript reliably in IE is ridiculously difficult. By contrast, with Firebug, along with IDEs such as that provided by Aptana, the process is as easy as pummelling out VB.

It's not just me. On Simple-Talk.com, the usage of Internet Explorer has dropped slightly from 75% to 70%, whereas Firefox has increased its share from 20% to 26%. Extraordinarily few of our readers have bothered to 'upgrade' from IE6 to IE7 in the past year. Just under half of our Internet Explorer users are sticking to IE6. In the past year, the proportion of stalwarts for IE6 has dropped only from 65% to 45%.

Ten years ago, the Gecko project seemed doomed. Now, under the wing of the Mozilla Foundation, and their Firefox product, it is all coming right for Gecko.

It is not just the ghost of Netscape that has come to haunt Microsoft. Safari too has made inroads, due to its resilience, and clever marketing by Apple. Opera too is looking like a viable alternative.

The improvements in browsers in the past two years have spawned a number of sophisticated JavaScript frameworks that start to make browser-based desktop applications look viable to mainstream business. E-bay Desktop http://desktop.ebay.com/ and AOL's Top 100 videos http://music.aol.com/help/syndication/desktop-widgets are already out there and there are plenty more in development. It seems to me to be a sign of health in the IT industry that there are now so many alternative ways to create applications.

What do you think? Once again, just add your comments to my blog, and all entries will go into a prize draw to win a $50 Amazon gift voucher!

Cheers,

Tony.

TSQL and the Tower of Babel

2008-01-08T14:11:00Z

It is always a bit of a strain to program in several languages at once. A simple task like writing a database-driven website will involve you in at least three, maybe more. The experience of diving into JavaScript, VB.NET, and TSQL, all at once, is one that most human brains aren't, in evolutionary terms, designed for.

Every year or so, someone gets the brainwave of developing a universal language which gets around the awfulness of having to learn, and use, so many. Either these people do not know the history of IT (PL/1 is a good starting point), or they think that 'this time it will be different'. It never is.

There are alternative approaches to this. By far the worst is to make languages 'converge' so that, for example, a comment block, Switch statement, or 'for loop' is the same in all languages. As languages get more similar, the brain gets more confused. All those little clues that remind the dozing consciousness what language he's in are taken away, and one starts making mistakes. VB and C# are converging noticeably. There is even a joke going around VB programmers that the way to convert code from C# is to comment out the semicolons. It is getting to be almost true.

An alternative strategy is to make languages diverge strongly enough that it is obvious, and that one can mentally switch from one to another without conscious effort. I never had a moments problem, in times past, in switching from C to assembler, to SQL. I'm happy with switching between JavaScript and TSQL. On the other hand, switching between JScript, JavaScript and Java can make the head spin.

SQL Server is now its own 'Tower of Babel'. It is noticeable that TSQL has been added to by Microsoft programmers with a variety of sympathies. The original Sybase variety had a reasonable consistency, but this has been compromised by interference from the procedural and object-oriented cultures that followed. Additions to SQL Server all seem to reflect the prevailing technological culture of the time. Now, when we are faced with XPath and SQL in the same environment, it is all starting to look a bit messy.

There probably is a process, or decision process, within Microsoft for ensuring the consistency of the syntax of TSQL, as it develops in SQL Server, but wouldn't it be a wonderful thing to make it into an open standard so we can all use a restraining hand when the Microsoft Hotshots start dreaming up more extensions to the language in a variety of syntax conventions?

What are your views on how TSQL (and SQL Server in general) has evolved over recent years? Has it become bloated and confusing, or simply more powerful (or both)? And what's the solution? Just add your comments to my blog, and all entries will go into a prize draw to win a Simple-Talk gift bag!

Cheers,

Tony.