SQLWebpedia

Occasionally, when you install identical databases on two different SQL Server instances, they will behave in surprisingly different ways. Why? Most likely, it is down to different configuration settings. There are around seventy of these settings and the DBA needs to be aware of the effect that many of them have. Brad McGehee explains them all in enough detail to help with most common configuration problems, and suggests some best practices.

Over the next few months, we'll be asking various well-known DBAs to describe their worst disaster caused by a mistake they made. To kick off the series, we asked Phil Factor to confess. He came up with a classic: The mistaken belief that a backup WITH CHECKSUM guaranteed a good backup that could be restored, and the ensuing disaster.

Static code analysis is used a lot by application programmers, but there have been surprisingly few tools for SQL development that perform a function analogous to Resharper, dotTest, or CodeRush. Wouldn't it be great to have something that can indicate where there are code-smells, lapses from best practice and so on, in your Database code? Now there is.

One of the more popular counters used by DBAs to monitor the performance, the Buffer Cache Hit Ratio, is useless as a predictor of imminent performance problems. Worse, it can be misleading. Jonathan Kehayias demonstrates this convincingly with some simple tests.

We asked Buck Woody to come up with his favourite 'Cloud' Howlers. After 'Howler' monkeys, we are faced with Howler letters. Buck dreams of sending Howler letters to the folks who dreamed up the marketing hype around 'cloud' services, who misunderstand services, who don't prepares applications for distributed environments and so on.

The relational model was devised long before computer hardware was able to deliver an RDBMS that could deliver a fully normalized database with no performance deficit. Now, with reliable SSDs falling in price, we can reap the benefits, instead of getting distracted by NOSQL with its doubtful compromise of 'eventual consistency'.

Alex has done some pioneering work on the testing of stored procedures over four years, and has learned a great deal in the process. In this article, he spells out the lessons learned from a wealth of experience in Unit Testing T-SQL.

In this next article in our series where well-known SQL Server people write about their favorite SQL Server Howlers, we asked Tom Larock for his top five common misunderstandings about how SQL Server works that end in tears, and plaintive forum questions.

XML data can become a full participant in a SQL Server Database, and can be used in views, functions, check constraints, computed columns and defaults. Views and table-valued functions can be used to provide a tabular view of XML data that can be used in SQL Expressions. Robert Sheldon explains how.

A SQL expression may look elegant on the page but it is only valuable if its performance is good. Do window functions run quicker than their conventional equivalent code? Fabiano gives a definitive answer.

Bad data always seems to appear when, and where, one least expects it. Sam explains the great value of a defensive approach based on constraints to any team that is developing an application in which the data has to be exactly right, and where bad data could cause consequential severe financial damage. It is perhaps better seen as creating a test-driven database.

Managing backups on a handful of servers should be a relatively straightforward process, but what about DBAs that need to automate to stay on top of large numbers of servers? What options are available, and how do they compare? Grant Fritchey looks at what's out there.

Now that we have some very good test frameworks for SQL Server, particularly tSQLt, all that was missing was a way of running your unit tests from within SQL Server Management Studio, and 'seeing red change to green'. Now the wait is over, with the arrival of SQL Test in preview. Rob Sullivan is one of those users who are helping shape the way that SQL Test develops, and explains what is going on, and why.

SQL Server Integration Services is an essential component of SQL Server, and designed to perform a variety of routine and occasional tasks. It majors on ETL tasks and for administrative jobs across servers. The DBA needs also to be aware of their role in optimising SSIS by planning, trouble-shooting, optimising performance, and in documenting installations.

There has been much debate over the need for the Lock Pages in Memory privilege, on 64-bit versions of SQL Server. Jonathan Kehayias presents a "warts and all" account of its history, the confusions surrounding its use, and why he believes it's still a good default configuration for 64-bit SQL Server instances, even when running Windows Server 2008 and Windows Server 2008R2.

The XML Data type has definite uses, but the way of interrogating, retrieving, and manipulating the values of properties and attributes within XML have been so foreign to the SQL language as to be somewhat of a barrier to their use. Fortunately, Robert Sheldon has once more managed to make the subject accessible to those of us who just need to get the job done.

A Backup system is merely part of a recovery system. If your backups can't be used to recover the database, then they're useless. Do you regularly make sure that you can restore a database from your backups?

Backups are an everyday part of DBA life, whereas restores tend to happen on call at 3 a.m. In this article, Grant Fritchey looks at what you should be doing to make your restores as quick and seamless as possible.

We decided to ask various well-known SQL Server people to write about their favourite SQL Server Howlers. These are the common misunderstandings about how SQL Server works that end in tears, and plaintive forum questions. Grant Fritchey opens the series with some of his favourite howlers.

Fabiano continues his introduction to SQL Server's implementation of the window functions by giving a history of what got implemented and when, and explaining the concept of the 'Frame' in a window function, with plenty of examples.