A process in a complex database occasionally, and apparently randomly, manages to put table locks on vital tables. Several applications are brought to a complete halt. Armed with a T-SQL stored procedure, a violin and a keen investigative spirit, Pop Rivett tracks down the rogue SPIDs that are causing all the problems...… Read more
Measuring what is actually happening is always the best course of action when investigating performance issues on databases, rather than relying on cache hit ratios, or best practices, or worst of all, guesswork. This article introduces some techniques that will allow you to pinpoint exactly where the performance issues are in your system, so you'll know exactly where to spend your time (and money) in solving them.
… Read more
Cursors and iterations are both renowned for slowing down Transact SQL code, but sometimes seem unavoidable. In this workbench, Robyn Page and Phil Factor demonstrate some set-based techniques for string manipulation and time interval-based reporting, which use helper tables rather than the dreaded cursor.… Read more
In part 3 of Dan Sullivan's in-depth exploration of PowerSMO, the versatile command line utility for managing SQL Server databases, he describes how to control the identity that SMO uses to login to SQL Server, how to set up Server activity monitoring, and how to create standalone PowerSMO scripts, suitable for use in a production environment.… Read more
Never a man to walk away from a challenge, Phil Factor set himself the task of automating the production of Word reports from SQL Server, armed only with OLE automation and a couple of stored procedures.… Read more
Robyn Page and Phil Factor present practical T-SQL techniques for controlling access to sensitive information within the database, and preventing malicious SQL injection attacks.… Read more
If database design is done right, then the development, deployment and subsequent performance in production will give little trouble. A well-designed database 'just works'. There are a small number of mistakes in database design that causes subsequent misery to developers, managewrs, and DBAs alike. Here are the ten worst mistakes
… Read more
In most relational database implementations. Update and Delete commands destroy the data that was there prior to their issue. However, some systems require that no information is ever physically deleted from or updated in the database. In this article, Arthur Fuller presents a solution to this requirement in the form of a Point-in-Time architecture: a database design which allows a user to recreate an image of the database as it existed at any previous point in time, without destroying the current image.… Read more
Grant Fritchey steps into the workbench arena, with an example-fuelled examination of catching and gracefully handling errors in SQL 2000 and 2005, including worked examples of the new TRY..CATCH capabilities.… Read more
If you've ever had brain meltdown trying to understand SQL Server users, roles, permissions, logins etc. then Robyn Page's security cribsheet might be just be your Panacea.… Read more
Dan Sullivan delves deeper into PowerSMO, the versatile command line utility for managing SQL Server databases. Using a certificate strategy, he provides a step-by-step guide to creating and deploying secure, signed DBA scripts. He then describes how to use PowerSMO functions to manage the extended properties of SQL Server objects.… Read more
The long-awaited final instalment of Steve Joubert's popular 4-part "from the ground up" guide to SQL Server 2005 Reporting Services. Here, he dissects the Report Definition Language and provides a practical demonstration on the use of Report Builder, an ad-hoc reporting tool that allows end users to build and customize their own reports.… Read more
The need to produce Excel reports from SQL Server is very common. Here, Robyn Page and Phil Factor present practical techniques for creating and manipulating Excel spreadsheets from SQL Server, using linked servers and T-SQL. The pièce de résistance is a stored procedure that uses OLE Automation to allow you full control over the formatting of your Excel report, and the ability to include sums, ranges, pivot tables and so on.… Read more
Most DBAs don't have time to perform routine processes manually. Everything has to be scripted, automated and scheduled. Here, Dan Sullivan describes how a combination of PowerShell and SMO ("PowerSMO") can provide a familiar but powerful command line tool for managing common SQL Server tasks.… Read more
Most companies have long since jettisoned the role of the grizzled old data architect, whose job it was to ensure a common understanding of the meaning of all data entities in the enterprise, and the actions carried out on it. Many are just now coming to realize what a costly mistake that might have been...… Read more
The topic of cursors is the ultimate "hot potato" in the world of SQL Server. Everyone has a view on when they should and mainly should not be used. By example and testing Robyn Page proves that, when handled with care, cursors are not necessarily a "bad thing". This article coined a phrase, 'Quirky Update', that has since established itself as the industry-term. We now feature a new revised version of the old classic, with help from Phil Factor.… Read more
Nigel Rivett provides a technique for moving files from one FTP directory to another, and for performing this processing from SQL Server Integration Services.… Read more
Is your IT project in trouble? Do you need to create a smokescreen so that no-one can pin the blame on you? No problem! Marvel at Phil Factor's versatile use of T-SQL functions in creating the "ultimate excuse database" and then start generating your excuses immediately!
… Read more
Database refactoring can greatly improve the efficiency of your database code. However, even a seemingly simple database refactoring such as "Move Column" can be tricky to implement correctly in a production environment. In this article, Scott Ambler and Pramod Sadalage describe a rigorous process for the correct implementation of an appropriate refactoring. … Read more
