|
|
Simple-Talk Editor
News, views and good brews
-
Posted Tuesday, April 29, 2008 7:08 PM |
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.
|
-
Posted Wednesday, April 16, 2008 1:55 PM |
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.
|
-
Posted Tuesday, April 01, 2008 9:21 AM |
"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.
|
-
Posted Monday, March 17, 2008 11:43 AM |
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.
|
-
Posted Wednesday, February 27, 2008 11:02 AM |
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.
|
-
Posted Tuesday, February 12, 2008 11:38 AM |
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.
|
-
Posted Tuesday, January 29, 2008 2:11 PM |
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.
|
-
Posted Tuesday, January 22, 2008 6:37 PM |
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.
|
-
Posted Tuesday, January 08, 2008 2:11 PM |
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.
|
-
Posted Tuesday, December 18, 2007 11:33 AM |
Visual Basic v9 appeared on November 19. In the past, the new release of Microsoft’s longest-running language might have caused a stir, but it was part of Microsoft .NET Framework 3.5 and there were plenty of other distractions. The changes to VB itself were pretty minor: The 'Inline IF' was finally retired in favour of a true ternary IF. We got support for LINQ, Lambda expressions like those of Python, support for XML Literals, and Type Inference. Hopefully, the real changes to the language will come with Visual Basic v10, which will use the Dynamic Language Runtime, and benefit from experience gained in the development of IronPython. It is set to be released with Silverlight 2 as 'Dynamic Visual Basic'. In the meantime C# continues to increase its dominance in the .NET world. Poor JScript.NET seems to be in terminal decline despite its high quality, though it is, like VB, promised a DLR makeover for SilverLight, and is likely to be renamed 'Managed JScript'.
For .NET scripting, things already look a lot livelier, thanks in part to the Dynamic Language Runtime (DLR). IronPython and PowerShell have, in the past year, found good solid niches, thanks to their effortless access to the CLR and, in the case of IronPython, excellent tutorials and good compatibility with existing Python code. We all hoped for more with IronRuby, which now seems to be stuck in a pre-alpha limbo due more to legal than technical problems. This is disappointing for those of us who liked some of the ideas in Ruby on Rails. While we wait for Ruby, there is Boo and Nemerle to play with.
The scripting language PHP has been a problem for Microsoft since the introduction of .NET. There are millions of websites that use PHP, yet PHP in Windows is hamstrung by having to use the COM interface. Typically, 80 per cent of PHP developers use Windows to develop and build applications and then deploy them to Linux in order to get a better performance. However, this may change. PHP scripts now will run on .NET due to Phalanger, a project started at Charles University and supported by Microsoft. Phalanger v1 compiles PHP language into .NET assemblies and can run most PHP code unmodified. Version 2 is in its third Beta and already is capable of running MediaWiki in .NET!
The .NET platform becomes more attractive the more variety it can support. Let's hope, therefore that Managed JScript gets the attention it deserves, and VB v10 breathes new life into the venerable language. It is sensible that Microsoft should also encourage the third-party development of more languages that are full participants in .NET with access to the CLR. (There is even a Cobol.net and a Fortran.net with Visual Studio integration.) It must continue to back Open Source projects and industry-wide scripting languages that, unlike C#, can provide cross-platform applications. Open Source languages have a wide support amongst developers, and so it seems a mature and sensible approach for Microsoft to ensure that its .NET-based server and PC platforms support them wholeheartedly. It will be refreshing to see a rich variety of languages that are able to participate fully in .NET and make use of the CLR. Next year should be exciting.
As always, we'd love to hear your thoughts on Managed JScript, the Dynamic Language Runtimes and VB, or any of the other issues raised in this editorial. Just add your comments to this blog (you'll need to be signed in), and all entries will go into a special Christmas prize draw to win one of three excellent prizes: a pair of Sennheiser Noise Cancelling Headphones, a license for SQL Backup Pro, and a deluxe Simple-Talk gift bag!
|
-
Posted Thursday, November 15, 2007 11:13 AM |
The history of IT is littered with the corpses of software companies that assumed that a solution that worked well in particular circumstances would necessarily become a generic panacea. Who, for example, now remembers the past giants of the Workflow industry?
The Orcas chrysalis is now hatching into the Visual Studio 2008 butterfly. Well, most of it. Visual Studio 2008 and .NET FX 3.5 will ship before the end of November. The Entity Framework, vNext, isn't ready yet. There is a lot in Visual Studio 2008, and it will take time to assimilate. Language Integrated Query (LINQ) has had a lot of attention. It is a neat solution, but not to every problem. The concept is difficult to argue with: we have just one querying language for every source of data. Data sources can export their metadata to the application; we then create logical, object-oriented, data sources by mapping entity-relational models to the object model (which is where vNext comes in). The problems of having to learn a different query language for every data repository, XML, LDAP, array, enumerable class, dataset, in-memory object, relational database, document, ISAM (…and so on) suddenly vanishes.
However, what works well with an XML file, a dataset, or a low-volume data-store could, in fact, be a disaster for a commercial, high-volume online transactional processing system. The problem is that the cost of a harmonised and logical application interface is likely to be a cacophony of 'dynamic SQL' at the database.
It is true that LINQ and vNext Entity Framework can work with stored procedures, but recent Microsoft presentations have made little of this. In fact, the difference in tone at PASS and TechEd was striking, with the emphasis to the developers being much more based on making direct calls into the database at table level. If this comes about, then there will be interesting times ahead for the DBAs. The problems of tracking and correcting locking/blocking problems, rogue queries, performance problems due to poorly constructed queries or the flushing of the procedure cache, not to mention security issues, will keep them busy; so busy, they won't have the time to worry about the fact that they cannot change the entity/relationship model without breaking LINQ code.
vNext is probably going to be the vital technology for the data architect because it provides the means to present one rational Entity Data Model for the application, rather than maintaining separate EDMs for persistence, reporting (e.g. SMDL), analysis (e.g. UDM), workflow, or data management (e.g. SMO).
It seems likely (though at this stage, not certain) that one will be able to provide a logical interface between the EDM and SQL Server that is based on stored procedures, views and functions. The application developers will be able to LINQ into the data model, thereby avoiding what they refer to as 'impedence mismatch'. This looks a lot more like a solution that will please everybody.
So what are your thoughts on this contentious LINQ issue? Just add them as a comment below (you'll have to be singed in). Again, all contributions will go into the draw for an iPod Shuffle!
Cheers,
Tony.
|
-
Posted Wednesday, October 31, 2007 12:12 PM |
We all have our favourite websites. Not the ones we read as part of our job, but the ones we scan whilst munching sandwiches at the desk or, surreptitiously, during an application build process.
I'm hooked on giveawayoftheday.com at the moment. It is a site that gives away licensed software. It features a different product every day, and you can only download a given product on the day it's featured. There is a great variety of products, from screensavers to file archiving utilities. They vary from the gorblimey to those that work sublimely. The addictive part is not so much the free software, but the voting and the comments. Each application is voted on and reviewed by hundreds of users, and each review is then voted on. The general consensus is never far off the mark: In this case, at least, almost every opinion is interesting.
Some classics have been on the site, such as Aston Shell, Concept Draw and TNT Screen Capture. There have also been some truly original pieces of software on there, such as the one that takes the sound of you humming a tune, and turns it into Midi, but these are actually in a tiny minority. It is the run-of-the-mill ones that are more fascinating. How and why do they get written? What is the urge that impels a group of people to risk time and money writing a text editor, a calculator, or a DVD copier? It is strange that the vital creative spark seems to be missing in much of what is written. It is the software equivalent of massed ranks of musicians playing 'Stairway to Heaven'.
If giveawayoftheday.com provides a representative glimpse of the output of the software industry, then surely it is time for a re-think. There must be room for the truly iconoclastic applications that suddenly change the rules, just as Visicalc, Quark Express, Visio, Delphi, Threadz Organizer or Ventura once did.
It got us wondering: what, today, are the most innovative, radical, .NET tools? Not so much the IDEs, but those applications that make life easier in the development process. We'd love to hear from you! Post your suggestion as a comment to this blog, and you'll go into a draw to win an iPod Shuffle!
Cheers!
Tony.
|
-
Posted Tuesday, September 18, 2007 7:38 PM |
MONDAY, 17 September 2007
On a slightly overcast Denver Monday morning, I strolled into the Colorado Convention Centre, intent on registering for the PASS conference. I stared in wonderment at the distinctive blue bear, entered the building, and followed the sign for "PASS registration". I duly approached the registration desk.
"Hi, I'm here to register"
"Great, what Name?"
I allowed a slightly smug note to enter my voice.
"Davis; it's a press registration".
"Oh, OK, can I see a business card, please?"
With an impressive flourish, I pulled a card out of my back pocket.
"Thanks, I'll just pull up the details and print your badge"
At this point another member of the registration staff joined in, muttering that she needed to "check this out". She stared at my business card with extreme scepticism.
"Simple Talk…never heard of it"
"Oh. I'm sorry. But I am registered for a press pass"
"I don't think you are." She gave me the Cagney and Lacey look
I briefly imagined a childhood spent watching crime serials on daytime TV, acting out the fantasy of pursuing dangerous Columbian drug barons. Her ambitions had been thwarted but she was living the dream.
"Yes, I am. I have a full conference pass, which Anna Vue upgraded to a press pass"
"Anna who?"
"Vue, v-u-e."
"Never heard of her"
Her tight smile indicated that she knew my type, and had exposed my ruse for the shameful deception that it was.
"Well, she is the one who registered me for the press pass"
"Anna Vue does not exist."
You could almost hear the incidental music. I was out of my depth here. Playing a game for which I didn't know the rules. I stared at her incredulously.
"Anna Vue does exist"
Fortunately, the original chap intervened at this point and put us out of our misery.
"What conference are you registering for?"
Of course, I was at the wrong registration desk. I turned away feeling slightly depressed and wearied by the confrontation and stumbled on to the real PASS desk. I checked my directions with a security lady, in a once-bitten-twice-shy sort of way.
"Down this way for PASS registration?"
"No straight on and right for registration; down there for lunch…make sure you hurry and get registered then get yourself down for some food, because we stop serving at 1" smiled the security lady, with reassuring kindness.
The real PASS registration went seamlessly and I trotted back round the corner with lifted spirits, and down the stairs for a spot of lunch. The PASS attendant glanced at my newly acquired press badge and directed me to a serving table, laden with silver tureens. Ah, the conference tureen, a sight to strike fear into the strongest heart. I approached them with a due sense of trepidation, but was distracted by the sight of a familiar face. Standing by the table was Rick Heiges. My spirits rose still further. Rick wasn't exactly a close friend, but I'd met him on several previous occasions and he was a nice guy. I had tried on a couple of occasions to get him to write for Simple-Talk.
"Hi Rick" I beamed", extending a hand, "Great to meet you again".
Rick looked distracted. He clearly had other things on his mind.
"Erm, yes, yes. OK. Erm, can you excuse me for a second?"
"No problem"
I returned my attention to the tureens. I peered dubiously into the first one. Was something moving in there? I threw caution to the wind and began ladening my plate with food. I was on to tureen number four when I was distracted again, by a tap on my shoulder.
"Erm, sorry" a second PASS lady said apologetically "Can I just check your badge again…oh, yes, err, you see you're registered for the Tuesday pre-conference session only, so you get lunch with that…but not today. Sorry if that wasn't pointed out to you"
It was a firm but tactful reproach. I hadn't even realised it was a pre-conference session lunch, and the first PASS attendant hadn't said anything. We were in an awkward spot here. I stood by the gleaming tureens, holding my plate of food uncertainly. She was apologetic, but clearly unsure whether to allow the interloper to go ahead and eat the food he'd already purloined, or to take his plate, send him packing, and scrape the food ceremoniously into the bin, possibly to the sound of faint cheering in the background.
"It's fine. Go ahead" she said after a few seconds that seemed like longer.
I sat and ate, unsure whether this was victory or defeat. A few moments later the PASS lady returned. She apologised for the awkwardness of what had happened and wondered why "that man had felt the need to intervene".
It turns out that the sleuth-like Rick had spotted the "T" on my badge (for "Tuesday") as I approached to shake his hand, and had excused himself in order to alert the relevant authorities.
Later, back in my hotel room, I mused on why Rick had acted in the way he did. We'd met before and he could easily have alerted me to my mistake with a wry smile, and avoided all the fuss and embarrassment.
Viewed dispaasionately, however, one has to acknowledge that Rick had succeeded in unmasking fraudulent use of PASS dining facilities, where others before him had failed. And you've got to admire his attention to detail and keen sense of rectitude – qualities that will serve PASS well.
Cagney and Lacey would have been proud.
|
-
Posted Wednesday, July 11, 2007 3:49 PM |
Whilst trying out Robyn and Phil's excellent TSQL Newsfeed, some of the first articles that I saw was Linchi Shea's fascinating Blog entries about the performance impact of not using owner-qualified stored procedure calls. I had no knowledge of this particular performance hit, which seemed to be coming out at around 15% and 25%. Prefixing with the owner will stop SQL Server from placing a COMPILE lock on the procedure, while it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.
More recently, I read Tony Rogerson's explanation of the beneficial effect on performance of using parameterisation and stored procedures. It inspired me to do a brief survey of programming practices that could be slowing down code, and of ways of fixing that, or avoiding the problems altogether.
With a bit of exploration I came across a surprisingly good MSDN article on patterns and practices for Improving SQL Server performance. Often, when Books on line tackle issues like this, they seem to descend into fragmented paragraphs and strangulated prose, but this piece was excellent.
A short but very useful summary of performance tips, largely gleamed from sqlserverperformance.com, is provided by Simon Harriyott. On sql-server-perfomance itself, the article on Temporary Tables vs. Table Variables and their effect on SQL Server Performance caught my eye. Here, Dmitry Tsuranoff ran the tests between the two types of table on both SQL Server 2000 and 2005, and found, to his surprise, how poor SQL Server 2005 was with some operations involving large temporary tables.
Dennis Forbes, in his epic series of articles on SQL Server performance, has an interesting section on using Indexed Computed Columns. It is one of those techniques that is easily forgotten in the heat of "getting the job done", but it is easy to see where it could be very handy.
Blocking is, of course, a common reason for poor performance. Our own Pop Rivett and the Case of the Rogue SPIDs provides an ingenious means of seeking out those occasional blocks. For some reason, it's taken me a long time to discover the wonderful stored procedure that is used for monitoring blocking in the article, How to monitor blocking in SQL Server 2005 and in SQL Server 2000. The dbo.sp_blocker_pss80 stored procedure looks like an occasional 'lifesaver' that should be part of every DBA's script library.
As far as books on SQL Server performance go, my favourite has always been Ken England's recently-updated, Microsoft SQL Server Performance Optimization and Tuning Handbook. Of course, there is also Microsoft Press' own SQL Server 2000 Performance Tuning Technical Reference by Steve Adrien DeLuca.
I suspect that one could easily be overwhelmed by the complexities of performance optimisation. However, I suspect that Phil Factor is right when he says that developers should always work with the slowest possible development server that one can find, so as to force developers to be conscious of slow code. It can be a novel experience for a developer to have to completely rewrite code several times until it runs efficiently, but it is the way of the world for a seasoned database developer.
Cheers,
Tony.
|
-
Posted Wednesday, June 27, 2007 4:24 PM |
The whole area of "SQL Server myths" is an interesting one. One of the fascinations of working with SQL Server is that, the more you learn, the more you realize you don't know. To the beginner SQL Server is presented as a series of black-and-white facts and rules designed to make learning easier. It is a necessary dogma. However, the more one discovers, the more one is obliged to admit that there are shades of grey. There are very few technical answers to which you couldn't add the qualifying clause 'But, on the other hand...'.
There have been some very interesting blog entries that set out to be Myth Busters, but are really pointing out that it isn't wise to cling to hard-and-fast rules any longer than you have to. A recent one caught our eye, taking a new look at the adage that the use of foreign key constraints slowed performance. I suppose that the answer is 'Yeeesss, but, on the other hand...' (Selects can be faster sometimes, evidently).
Denis Gobo's blog entry, SQL Myth: Truncate Cannot Be Rolled Back Because It Is Not Logged was a useful reminder of the need to test anything you're told about SQL Server. Also, it reminded me of the debate about using table variables, which was triggered off by a tip last year on SQLServerCentral. The tip contained this advice:
Table variables are created and manipulated in memory instead of the tempdb database'
This ended in a fascinating post by Darren Gosbell, with a comment by Greg Linwood.
Greg Linwood's series on clustered indexes has been fascinating reading, and the dogma about every table requiring a clustered index received a mauling in his blog entry Debunking myths about clustered indexes - part 1 (CIXs worsen bookmark lookups)
On a broader subject, Euan Garden's blog is always interesting and his posting on SQL MythBusters – SQL Server is really a Sybase product not a Microsoft one was refreshing and timely. More of these legends are mentioned in the amusing articles The myths and legends of the holy land of the database, by Mark Whitehorn, which contain the excruciating 'So, happily, Larry Ellison doesn't have to say to his marketing people, "That's another fine myth you've gotten me into".'
One of the most irritating myths for the relational database developer is the myth of the superiority of the object-oriented approach and OODBMSs. Here is a typical contribution to the debate from Dare Obasanjo, Why Aren't You Using An OODMS?
It is therefore refreshing to read Object Oriented Programming Oversold!, by Mr. B. Jacobs
These just happen to have been the contributions that have stimulated us to argue and try things out, but we'd love to hear from anyone else who has seen some iconoclastic work on SQL Server. Simple-Talk gift bags to the three best contributions.
Cheers,
Tony.
|
|
|