Simple Talk RSS Feed

Automated Script-generation with Powershell and SMO

Phil Factor — Fri, 03 Feb 2012 00:00:00 GMT

In the first of a series of articles on automating the process of building, modifying and copying SQL Server databases, Phil Factor demonstrates how one can generate TSQL scripts for databases, selected database objects, or table contents from PowerShell and SMO.

Using SQL Test Database Unit Testing with TeamCity Continuous Integration

Dave Green — Thu, 02 Feb 2012 00:00:00 GMT

With database applications, the process of test and integration can be frustratingly slow because so much of it is based on manual processes. Everyone seems to agree that automation of the process provides the answer to accomodating shorter development cycles, but how, exactly? Dave Green describes a successful process that integrates third-party tools.

Converting String Data to XML and XML to String Data

Robert Sheldon — Wed, 01 Feb 2012 00:00:00 GMT

We all appreciate that, in general, XML documents or fragments are held in strings as text markup. In SQL Server, XML variables and columns are instead tokenised to allow rapid access to the data within. This is fine, but can cause some odd problems, auch as 'entitization'. What, also, do you do if you need to preserve the formatting? As usual Rob Sheldon comes to our aid.

How to Document and Configure SQL Server Instance Settings

Brad McGehee — Mon, 30 Jan 2012 00:00:00 GMT

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.

What's the Point of Using VARCHAR(n) Anymore?

Rob Garrison — Mon, 30 Jan 2012 00:00:00 GMT

The arrival of the (MAX) data types in SQL Server 2005 were one of the most popular feature for the database developer. At the time, there was a lot of discussion as to whether this freedom from having to specify string length came at a cost. Rob attempts to give a final answer as to any down-side.

Confessions of a DBA: My worst mistake

Phil Factor — Mon, 23 Jan 2012 00:00:00 GMT

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.

SQL Cop Review

Grant Fritchey — Thu, 19 Jan 2012 00:00:00 GMT

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.

Great SQL Server Debates: Buffer Cache Hit Ratio

Jonathan Kehayias — Wed, 18 Jan 2012 00:00:00 GMT

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.

Buck Woody's Cloud Howlers

Buck Woody — Tue, 17 Jan 2012 00:00:00 GMT

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.

Relational Databases and Solid State Memory: An Opportunity Squandered?

Robert Young — Tue, 17 Jan 2012 00:00:00 GMT

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'.

Close Those Loopholes: Lessons learned from Unit Testing T-SQL

Alex Kuznetsov — Mon, 09 Jan 2012 00:00:00 GMT

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.

Tom LaRock's SQL Server Howlers

Thomas LaRock — Thu, 05 Jan 2012 00:00:00 GMT

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.

Incorporating XML into your Database Objects

Robert Sheldon — Wed, 04 Jan 2012 00:00:00 GMT

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.

Window Functions in SQL Server: Part 3: Questions of Performance

Fabiano Amorim — Wed, 04 Jan 2012 00:00:00 GMT

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.

Constraints and the Test-Driven Database

Sam Bendayan — Wed, 14 Dec 2011 00:00:00 GMT

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.

Centralized management: Tips for Managing Backups on Multiple Servers

Grant Fritchey — Tue, 13 Dec 2011 00:00:00 GMT

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.

SQL Test: Seeing Red Change to Green

Rob Sullivan — Tue, 13 Dec 2011 00:00:00 GMT

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.

Administrating SQL Server Integration Services - Planning, Documenting and Troubleshooting

Feodor Georgiev — Mon, 12 Dec 2011 00:00:00 GMT

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.

Great SQL Server Debates: Lock Pages in Memory

Jonathan Kehayias — Mon, 12 Dec 2011 00:00:00 GMT

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 Methods in SQL Server

Robert Sheldon — Thu, 08 Dec 2011 00:00:00 GMT

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.