Click here to monitor SSC
Simple-Talk Editorial Team

Database Delivery Patterns and Practices

Continuous database delivery is an automated process for building, deploying and testing databases to reduce risk and make rapid releases possible. It's enabled by a pipeline that starts when database changes are checked in, and ends when they're deployed to production. The articles collected here will help you understand the theories and methodologies behind every stage of the database delivery pipeline. Read more...

Phil Factor

Continuous Delivery and the Database

Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of software delivery and deployment by making build, integration and delivery into a routine. The way that databases fit into the Continuous Delivery story has been less-well defined. Phil Factor explains why he's an enthusiast for databases being full participants, and suggests practical ways of doing so. Read more...

Grant Fritchey

The DBA Detective: Disturbing Developments

Originally one of the articles in the first DBA Team series, Grant wonders what Raymond Chandler or Dashiell Hammett would have done if asked to write technical articles for Simple-Talk. He came up with the DBA detective, hard-boiled Joe Dee Beay Read more...

Warwick Rudd

AlwaysOn Availability Groups - What Not to do when Adding Databases

SQL Server's AlwaysOn Availability Groups provide a very resilient way of providing High-availability for SQL Server databases, but there are inevitable limits to their capacity. How many databases can you reasonably add? It depends on the resources available and the workload, but you can come up with a reasonable estimate as Warwick Rudd explains Read more...

Laerte Junior

The PoSh DBA – Specifying and Gathering Performance Counters

If you are needing to keep tabs on a number of servers and applications in a Windows domain then performance counters provide the bedrock of information. It is important to identify the counters you need and gather baseline data to allow you to create alerts when abnormal conditions occur. When it comes to monitoring SQL Server, don't guess, collect. Read more...

Robert Sheldon

Seven SQL Server Under-Used Utilities

There are more than ten useful command-line applications that are either associated with, or are distributed with, SQL Server. Some, like BCP are used often, whereas others like LogDumper, almost never. However, they all have their uses and several become important as part of script-based automation of tasks. It is definitely worth knowing what is lurking in your tools\binn directory. Read more...

David Tate

Guerrilla Project Management for DBAs

All DBAs need to engage in a little project management to help make sure everything runs smoothly. In this extract from the book Tribal SQL, David Tate explains his system for managing workload, coleagues, and projects, and how not to be just "the guy who says no". Read more...

Nirmal Sharma

Hosting SQL Server in Hyper-V Replica Environment

A Hyper-V replica will provide a rapid disaster-recovery by simply replicating to a standby site a VM running at the primary site. Is it, therefore, ideal for running SQL Server in high-availability? Well, it depends on the type of HA you require, and whether you need the features that aren't supported. Nirmal explains the details and shows how to set it up. Read more...

Feodor Georgiev

Collect Your SQL Server Auditing and Troubleshooting Information Automatically

If you have a number of SQL Server instances with versions ranging from 2005 upwards, with a whole host of databases, and you want to be alerted about a number of diverse events that are useful for first-line problem-diagnosis and auditing, then Feodor's homebrew solution, using SSIS and Robocopy is likely to be what you're looking for. Read more...

Robert Sheldon

The SQL Server Sqlio Utility

If, before deployment, you need to push the limits of your disk subsystem in order to determine whether the hardware’s I/O capacity meets the needs of a database application, if you need performance baselines, or if you want to identify any performance-related issues, then why not use the sqlio utility? Read more...

Laerte Junior

The PoSh DBA - Reading and Filtering Errors

DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of the host servers as well. When server numbers get large, the traditional Windows GUI approach breaks down, and the PoSH DBA reaches for a PowerShell script to do the leg-work. Read more...

Robert Sheldon

The SQL Server Sqliosim Utility

The sqliosim utility is provided with SQL Server to test the I/O stability and 'correctness' of a server. It doesn't measure performance but simulates the read, write, checkpoint, backup, sort, and read-ahead activities of a typical SQL Server instance under load. It is generally used before installing SQL Server in order to ensure that new hardware can handle your expected loads. Bob Sheldon explains. Read more...

Laerte Junior

The PoSH DBA - SQLPSX: SQL Server PowerShell Extensions

Every DBA needs to know about SQLPSX, the PowerShell module library that is built by DBAs for DBAs, and designed to provide intuitive functions around the SMO objects. It makes the automation of database administration easier across all versions of SQL Server since SQL Server 2000. Laerte Junior, who is one of the developers on the open-source project, describes how to use it. Read more...

Laerte Junior

The PoSh DBA – Getting Airborne with PowerShell

Maybe the best way of helping the busy database professional to get started with practical PowerShell-based administration is to pull together all the essential community cmdlets into a toolkit for the POSH DBA, and explain how and why you'd use them. Read more...

Mark S Rasmussen

SQL Server Storage Internals 101

This article is an extract from the book Tribal SQL. In this article, Mark S. Rasmussen offers a concise introduction to the physical storage internals behind SQL Server databases. He doesn't dive into every detail, but provides a simple, clear picture of how SQL Server stores data. Read more...

Robert Sheldon

DAX Statistical Functions

Following on from his first four articles on using Data Analysis Expressions (DAX) with tabular databases, Robert Sheldon dives into some of the DAX statistical functions available, demonstrating which are the most useful and examples of how they work. Read more...

Richard Morris

Developing for Delivery, a Practical Example

Richard Morris interviewed Michael Stoop, a database developer at Calvi, Europe's leading provider of Telecom Invoice Management software. The discussion focused on how Calvi transformed their database delivery process to accommodate massive database growth, statutory regulations, and developments in their application. Here's their story. Read more...

Laerte Junior

The PoSh DBA – Getting to know PowerShell

Although it was primarily designed for System Administrators, PowerShell is now extending its use to Database, SharePoint, Exchange and all Microsoft products. In this article Laerte Junior offers an introduction to PowerShell and describes how DBAs can use PowerShell to automate repetitive tasks. He also explains when to use PowerShell instead of, or in addition to, T-SQL and SSIS. Read more...

Dwain Camps

Condensing a Delimited List of Integers in SQL Server

In real-world applications, it often makes sense to show denormalized data such as delimited lists within the application's user interface. Dwain Camps shows why, and how, the distribution business stores information about 'islands' in sequences in order to track the status of the shipping of a consignment. It makes a great SQL puzzle Read more...

Robert Sheldon

Using DAX to create SSRS reports: The Basics

You can use DAX to create reports from a tabular database in SQL Server Reporting Services. To do so requires a few workarounds. Rob Sheldon describes a poorly-documented but important technique for Business Intelligence. Read more...

Allen White

Test Your SQL Server Backups with PowerShell

The best way of checking SQL Server backups is to restore them and run DBCC CHECKDB on the restored database. To do this regularly means that you need to automate the task. Allen White shows how, with PowerShell. Read more...

Phil Factor

Getting Data between Excel and SQL Server using ODBC

With ODBC, you can summarise, and select just the data you need, in an Excel workbook before importing it into SQL Server. You can join data from different areas or worksheets. You can even get data from the result of a SQL Server SELECT statement into an Excel spreadsheet. Phil Factor shows how, and warns of some of the pitfalls. Read more...

Tony Davis

Eight Steps to Effective SQL Server Monitoring

If you need to start monitoring your SQL Server instances and database, and there are many good reasons to do so, Tony Davis gives you the eight essential steps to diagnosing problems quickly. Read more...

Grant Fritchey

Database Administration as a Service

A DBA should provide two things, a service and leadership. For Grant Fritchey, it was whilst serving a role in the Scouts of America that he had his epiphany. Creative chaos and energy, if tactfully harnessed and directed, led to effective ways to perform team-based tasks. Then he wondered why these skills couldn't be applied to the workplace. Are we DBAs doing it wrong in the way we interact with our co-workers? Read more...

Robert Sheldon

Columnstore Indexes in SQL Server 2012

The columnstore index in SQL Server 2012 stores columns instead of rows, and is designed to speed up analytical processing and data-warehouse queries. Whilst columnstore indexes certainly do that effectively, they are not a universal panacea since there are a number of limitations on them. When used appropriately, they can reduce disk I/O and use memory more efficiently. Read more...

Louis Davidson

Baselining with SQL Server Dynamic Management Views

When you're monitoring SQL Server, it is better to capture a baseline for those aspects that you're checking, such as workload, Physical I/O or performance. Once you know what is normal, then performance tuning and resource provisioning can be done in a timely manner before any problem becomes apparent. We can prevent problems by being able to predict them. Louis shows how to get started. Read more...

Feodor Georgiev

Collecting the Information in the Default Trace

The default trace is still the best way of getting important information to provide a security audit of SQL Server, since it records such information as logins, changes to users and roles, changes in object permissions, error events and changes to both database settings and schemas. The only trouble is that the information is volatile. Feodor shows how to squirrel the information away to provide reports, check for unauthorised changes and provide forensic evidence. Read more...

Rob Garrison

Exploring In-memory OLTP Engine (Hekaton) in SQL Server 2014 CTP1

The continuing drop in the price of memory has made fast in-memory OLTP increasingly viable. SQL Server 2014 allows you to migrate the most-used tables in an existing database to memory-optimised 'Hekaton' technology, but how you balance between disk tables and in-memory tables for optimum performance requires judgement and experiment. What is this technology, and how can you exploit it? Rob Garrison explains. Read more...

Jonathan Allen

Preparing to Upgrade your SQL Server

It isn't a problem to use deprecated TSQL features until it comes to the time to move the database to a server with a newer version of SQL Server, because The Upgrade Adviser tool will tell you what needs to be changed. An alternative is to flush out the use of archaic features via scripting during the development process so there are no surprises later. Jonathan Allen shows how Read more...

Alexander Karmanov

Automating SQL Server Database Deployments: Scripting Details

To wrap up the series on Database Deployment Challenges, Alexander takes a dive into the details of how he scripted a solution and comes up with several practical tips for getting the most out of any automated database deployment framework. Read more...

Grant Fritchey

The DBA Detective: The Case of the Missing Index

When problems arise in SQL Server, we're faced with a server full of suspects, including disk I/O, memory, CPU, incorrect or missing indexes, badly written T-SQL code, out of date statistics, and full disk drives. All of these have motive and opportunity to murder the performance of our databases, and it's the DBA's job to collar the culprit, quickly without relying on luck or heroics. Read more...

Francis Hanlon

Monitoring Transactional Replication in SQL Server

If you are using replication in SQL Server, you can monitor it in SSMS, but it makes sense to monitor distribution jobs automatically, especially if you can set up alerts or even set up first-line remedial action when a problem is detected. Francis shows how to do it in TSQL as an agent job. Read more...

Adam Machanic

The Ten Commandments of SQL Server Monitoring

It is easy to get database monitoring wrong. There are several common-sense rules that can make all the difference between a monitoring system that works for you and helps to avoid database problems, and one that just creates a distraction. Adam Machanic spells out the rules, based on his considerable experience with database monitoring. Read more...

Kat Hicks

Diagnosing Common Database Ails

When a database starts showing signs of an illness, it's up to the DBA to get to the root of the problem, fast. Kat Hicks takes a look at the most common causes of database troubles, free tools that can help, and the misconceptions that get in the way. Read more...

Tony Davis and Shawn McGehee

Managing the SQL Server Transaction Log: Dealing with Explosive Log Growth

You've just become responsible for a database, only to find that the log file is growing out of control. Why is it happening and what do you do to correct it? Read more...

Seth Delconte

Ad-Hoc XML File Querying

When you need to shred just part of the data within a large XML file into a SQL Server table, the most efficient way is to just select what you need via XQuery or by using XPath, before shredding it into a table. But precisely how would you do that? Read more...

Alexander Karmanov

Automating SQL Server Database Deployments: A Worked Example

Alex talks through a simple practical example of a database deployment, First creating a empty database and then upgrading it through three steps by writing T-SQL scripts, adjusting configuration files and the change log, before generating a full build script containing all schema objects. Read more...

Feodor Georgiev

Full Text Searches on Documents in FileTables

SQL Server's FileTable technology is an intriguing way of accomodating file-based text data in a database, and allowing for complex searches. As with most technologies, the best way of learning them is to try it out and experiment. Feodor shows how to set it up, add some sample data and set up full-text search. Read more...

Alexander Karmanov

An Incremental Database Development and Deployment Framework

Often, an existing database application must evolve quickly by incremental steps. Alex describes a tried and tested system to provide an automated approach to deploying both new and existing database systems, whilst dealing with common security and configuration issues. Read more...

Phil Factor

Database Deployment: The Bits - Versioning

Although databases have no inherent way of recording their version numbers, SQL Server provides the means of doing so, and much more besides. This is a great advantage to anyone faced with the task of deploying databases without errors. Read more...

Robert Sheldon

Getting Started with Extended Events in SQL Server 2012

Extended Events provide a way of unintrusively monitoring what's going on in a SQL Server instance. Unlike SQL Server Profiler and SQL Trace, it has little performance impact. Now, in SQL Server 2012 SSMS, it is relatively easy to use, as Robert Sheldon shows. Read more...

Alexander Karmanov

Database Deployment Challenges

Traditionally, Database Deployment is a process that isn't associated with smiles, bonhomie and tranquility. There are a number of challenges that make the task more difficult. Alex reviews the common techniques for deploying new databases and upgrading existing ones, and their flaws, and argues the advantages of an automated, incremental, script-based approach to deployments Read more...

Fabiano Amorim

Using Optimizer_WhatIF and StatsStream to Simulate a Production Environment

SQL Server's Query optimiser judges the best query plan from the data in the relevant tables and the server's hardware. How, then, can you investigate the query plans being generated for slow-running queries on a customer's production server when you can neither access the server, nor recreate the database from a backup? Read more...

Warwick Rudd

Expanding AlwaysOn Availability Groups with Replication Publishers

SQL Server 2012 AlwaysOn Availability Groups provide a high-availability and disaster-recovery solution for you SQL Server 2012 environments. Replication has been around in SQL Server for quite some time and allows you to scale out your environment. Warwick Rudd explains how to join these technologies together Read more...

Feodor Georgiev

HDDs, SSDs and Database Considerations

In this article Feodor clears up a few myths about storage, explains the difference in how HDDs and SSDs work and looks into the considerations every DBA should have in mind when choosing / working with SSDs. Read more...

Robert Sheldon

Handling Errors in SQL Server 2012

The error handling of SQL Server has always been somewhat mysterious. Now at last, the THROW statement has been included in SQL Server 2012 that, combined with the TRY ... CATCH block, makes error handling far easier. Robert Sheldon explains all. Read more...

Fabiano Amorim

Hypothetical Indexes on SQL Server

Sometimes, you have to test out alternative indexing strategies, but the task of creating the candidate indexes would just take too long. Is there another way? Well, yes, why not use the same method the DTA (Database Tuning Advisor) uses, and take the tedium out of the job. Read more...

Grant Fritchey

SQL Server Backup Questions We Were Too Shy to Ask

During presentations about doing database backups and restores, there seem to be two types of questions that are commonly asked - those that come from the floor during the presentation, and those that are asked in private afterwards. These are sometimes more interesting, and challenging to answer well. Read more...

William Brewer

Disaster Recovery Planning for Data: The Cribsheet

Planning for disaster recovery and business continuity aren't amongst the most exciting IT activities. They are, however, essential and relevant to any Database Administrator who is responsible for the safety and integrity of the companies' data, since data is a key part of business continuity. Read more...

Glenn Berry

Making the Case for a SQL Server Platform Refresh

With the release of Windows Server 2012, SQL Server 2012, and the new generation of Sandy Bridge Xeon processors, your organization is likely to get many tangible benefits from upgrading your current database infrastructure with a complete platform refresh. Read more...

Buck Woody

Setting up a Data Science Laboratory

There is no better way of understanding new data processing, retrieval, analysis or visualising techniques than actually trying things out. In order to do this, it is best to use a server that acts as data science lab, with all the basic tools and sample data in place. Buck Woody discusses his system, and the configuration he chose. Read more...

Phil Factor

Database Deployment: The Bits - Database Version Drift

When you are about to deploy a new version of a database by updating the current version, one of the essential pre-deployment checks is to make sure that the version in production is exactly what it should be. If changes have somehow slipped in, you'll need to understand them and deal with them before you can deploy. Read more...

Feodor Georgiev

How come the Hourglass? Why database applications slow down.

It is frustrating when you hit 'submit' and you get the hourglass big-time. Is it the database to blame? It could be, but there are other suspects that should be considered. Feodor describes where, in the long route, to and fro', that a data request makes before returning, that things can get snarled up. Read more...

Feodor Georgiev

Handling Backups for Rapid Resilience

The backup and restore system in SQL Server hasn't changed a great deal over the years despite a huge growth in the typical size of databases. When disaster strikes, and an important service is taken offline while a restore is performed, there is often time to reflect on whether it might be possible to design databases for a more rapid recovery of the most critical parts of a database application. Read more...

Feodor Georgiev

How come the Hourglass? Why database applications slow down

It is frustrating when you hit 'submit' and you get the hourglass big-time. Is it the database to blame? It could be, but there are other suspects that should be considered. Feodor describes where, in the long route, to and fro', that a data request makes before returning, that things can get snarled up. Read more...

Joe Sack

Fixing Gatekeeper Row Cardinality Estimate Issues

The Query Optimiser needs a good estimate of the number of rows likely to be returned by each physical operator in order to select the best query plan from the most likely alternatives. Sometimes these estimates can go so wildly wrong as to result in a very slow query. Joe Sack shows how it can happen with SQL Queries on a data warehouse with a star schema. Read more...

William Brewer

Database Deployment Cribsheet

As part of our long-running Cribsheet series, we asked William to write a guide to deployment that described in general terms what is involved in the deployment of a database application, and the sort of issues that one is likely to come up against. Read more...

Seth Delconte

Manipulating XML Data in SQL Server

When the average database developer is obliged to manipulate XML, either shredding it into relational format, or creating it from SQL, it is often done 'at arms length'. A shame, since effective use of techniques that go beyond the basics can save much code, and are likely to perform better. Read more...

Dmitri Korotkevitch

Partitioned Tables, Indexes and Execution Plans: a Cautionary Tale

Table partitioning is a blessing in that it makes large tables that have varying access patterns more scalable and manageable, but it is a mixed blessing. It is important to understand the down-side before using table partitioning. Read more...

Feodor Georgiev

Designing Databases for Rapid Resilience

As the volume of data increases, DBAs need to plan more actively for rapid restores in the event of failure. For this, the intelligent use of filegroups is important, particularly when the Enterprise Edition of SQL Server offers the hope of online restores. How, though, should you arrange your data on the different filegroups? What happenens if the primary filegroup gets corrupted? Why backup and restore indexes? Read more...

Joe Sack

Exploring Semantic Search Key Term Relevance

SQL Server's 'Semantic Search' feature seemed an exciting feature when first shown. Was it really true that Microsoft had come up with a system to rival the industry-leaders, one that could extract the contextual meaning of terms in text, or automatically categorise the subject matter of text? On first inspection, it seems unlikely. Read more...

Seth Delconte

Getting Started With XML Indexes

XML Indexes make a huge difference to the speed of XML queries, as Seth Delconte explains; and demonstrates by running queries against half a million XML employee records. The execution time of a query is reduced from two seconds to being too quick to measure, purely by creating the right type of secondary index for the query. Read more...

Glenn Berry

Provisioning a New SQL Server Instance – Part Three

Once you've installed and configured SQL Server 2012, there are some tasks that should be done to ensure that maintenance, monitoring and alerting systems are in place to keep the instance running smoothly. Glenn Berry explains how. Read more...

Laerte Junior

The PoSh DBA: Solutions using PowerShell and SQL Server

PowerShell is worth using when it is the quickest way to providing a solution. For the DBA, it is much more than getting information from SQL Server instances via PowerShell; it can also be run from SQL Server as part of a system that helps with administrative and monitoring tasks. Laerte explains how. Read more...

Robert Sheldon

Report Builder 3.0: Formatting the Elements in your Report

There is a lot that can be done to make basic tabular reports more readable, using Microsoft's free Report Builder. Rob Sheldon continues his exploration of the power of this tool by showing how to format various elements within reports. Read more...

Grant Fritchey

Why is that SQL Server Instance under stress?

There are several reliable indications, using SQL Queries, of the what is causing SQL Server performance problems. Some of these are fairly obvious, but others aren't. Grant shows how you can get clues from any SQL Server as to the cause of stress. Read more...

Michael Sorens

Practical PowerShell for SQL Server Developers and DBAs – Part 2

Having shown just how useful PowerShell can be for DBAs in executing queries, Michael Sorens now takes us through navigating SQL Server space and finding meta-information - valuable information for anyone looking to be more productive in SQL Server. Read more...

Gail Shaw

Gail Shaw's SQL Server Howlers

For the latest in our series of SQL Server Howlers, we asked Gail Shaw which common SQL Server mistakes and misunderstandings lead to tearful DBAs and plaintive cries for help on the forums. Read more...

Glenn Berry

Provisioning a New SQL Server Instance – Part Two

So how should you install and configure SQL Server 2012 properly? Glenn Berry completes his two-part series by explaining the steps needed to complete the preparation and do the actual installation. Read more...

Dave Ballantyne

Cleaning Up SQL Server Deployment Scripts

Although, generally speaking, source control is the truth, a database doesn't quite conform to the ideal because the target schema can, for valid reasons, contain other conflicting truths that can't easily be captured in source control. Dave Ballantyne explains the problems and suggests a solution. Read more...

Michael Sorens

Practical PowerShell for SQL Server Developers and DBAs – Part 1

There is a lot of confusion amongst DBAs about using PowerShell due to existence the deprecated SQLPS mini-shell of SSMS and the newer SQLPS module. In a two-part article and wallchart, Michael explains how to install it, what it is, and some of the excellent things it has to offer. Read more...

Phil Factor

PowerShell SMO: Just Writing Things Once

Sometimes, you can tire of writing the same PowerShell code once again. After this happened to Phil whilst keying in an SMO pipeline to access databases, it occurred to him that he should have only one pipeline to access databases in PowerShell, one reusable pipeline Read more...

Jonathan Kehayias

Optimizing tempdb configuration with SQL Server 2012 Extended Events

One of the most obvious bottlenecks in the performance of tempdb is caused by PAGELATCH, in-memory latch contention on the allocation bitmap of each data file used. We can use one of the rules-of-thumb to choose what should be roughly the best number of files, but how then do you check to see whether you've got it right for your data and workload? Read more...

Warwick Rudd

Encrypting Your SQL Server 2012 AlwaysOn Availability Databases

It is likely that you'll want to add a database with TDS Encryption to your AlwaysOn Availability Group. If you do so you'll find that you can't use the SSMS wizard to do so. So, how do you achieve it? Read on... Read more...

Glenn Berry

Provisioning a New SQL Server Instance – Part One

Before you even install SQL Server, there a number of preparatory steps you need to take in order to get a new machine with a fresh copy of the operating system completely ready to install SQL Server properly. This is to maximize performance, reliability, and security. Read more...

Laerte Junior

The PoSh DBA - The Attributes of Advanced Functions

Once you pass that point of just hurriedly writing PowerShell scripts for immediate use and start to write PowerShell functions for reuse, then you'll want a robust set of parameters that allow functions to work just like cmdlets. Read more...

Alex Kuznetsov

Developing Low-Maintenance Databases

Alex's team of developers are geared to doing rapid development of database applications in a busy corporate setting, yet take considerable time over meticulous database design, extensive constraints, automated tests, error logs, and defensive coding. Why? Because it cuts down on the subsequent need for maintenance Read more...

Peter Larsson

The Road to Professional Database Development: Database Normalization

Not only is the process of normalisation valuable for increasing data quality and simplifying the process of modifying data, but it actually makes the database perform much faster. To prove the point, Peter takes a large unnormalised database and subjects it to successive stages of normalisation. Read more...

Joe Sack

A first look at SQL Server 2012 Availability Group Wait Statistics

If you are trouble-shooting an AlwaysOn Availability Group topology, a study of the wait statistics will give a pointer to many of the causes of problems. Although several wait types are documented, there is nothing like practical experiment to familiarize yourself with new wait stats, and Joe Sack demonstrates a way of testing the sort of waits generated by an availability group under various circumstances. Read more...

Warwick Rudd

SQL Server 2012 AlwaysOn

SQL Server AlwaysOn provides a high-availability and Disaster-recovery solution for SQL Server 2012. It makes use of existing SQL Server features, particularly Failover Clustering, and provides new capabilities such as availability groups. Warwick Rudd explains the basics and shows you how to implement it. Read more...

Jonathan Kehayias

Handling Deadlocks in SQL Server

In this excerpt from his book Troubleshooting SQL Server: A Guide for the Accidental DBA, Jonathan Kehayias provides a guide to identifying the causes, reacting to, and ultimately preventing the dreaded deadlock. Read more...

Greg Larsen

SQL Server Transaction Log Fragmentation: a Primer

Generally, you will have no need to worry about the number of virtual log files in your transaction log. However, if you use the default settings for 'auto-grow', you can end up with such 'fragmentation' in your transaction log as to affect performance noticably. How can this be avoided? How can you tell it's a problem? What do you do about it? Greg explains. Read more...

Buck Woody

Big Data is Just a Fad

The Term 'Big Data' is nothing more than a fad, and we'll soon be cringing with embarrassment at the thought that we ever used it. However, the data, and the challenges to processing it that it presents, will stay with us. If jargon like 'Big Data' helps us focus on the problems, then let’s use it: temporarily, perhaps. Read more...

Phil Factor

Generating Data for Database Tests

It is more and more essential for developers to work on development databases that have realistic data in both type and quantity, but without using real data. It isn't exactly easy, even with third-party tools to hand. Phil Factor shows how it can be done, taking the classic PUBS database and giving it a more realistic set of data. Read more...

Luciano Moreira

No Significant Fragmentation? Look Closer…

If you are relying on using 'best-practice' percentage-based thresholds when you are creating an index maintenance plan for a SQL Server that checks the fragmentation in your pages, you may miss occasional 'edge' conditions on larger tables that will cause severe degradation in performance. It is worth being aware of patterns of data access in particular tables when judging the best threshold figure to use. Read more...

Peter Larsson

The Road to Professional Database Development: Set-Based Thinking

Under the pseudonym of 'SwePeso', Peter Larsson is famous on SQL forums for the amazing performance he can get from SQL. How does he do it? In the first of a series of articles, Peter explains his secrets. Read more...

Phil Factor

Automated Script-generation with Powershell and SMO

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. Read more...

Robert Sheldon

Converting String Data to XML and XML to String Data

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. Read more...

Rob Garrison

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

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. Read more...

Phil Factor

Confessions of a DBA: My worst mistake

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. Read more...

Jonathan Kehayias

Great SQL Server Debates: Buffer Cache Hit Ratio

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. Read more...

Robert Young

Relational Databases and Solid State Memory: An Opportunity Squandered?

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

Buck Woody

Buck Woody's Cloud Howlers

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. Read more...

Alex Kuznetsov

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

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. Read more...

Sam Bendayan

Constraints and the Test-Driven Database

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. Read more...

Feodor Georgiev

Administrating SQL Server Integration Services - Planning, Documenting and Troubleshooting

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. Read more...

Jonathan Kehayias

Great SQL Server Debates: Lock Pages in Memory

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. Read more...

Grant Fritchey

Grant Fritchey's SQL Server Howlers

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. Read more...

Greg Larsen

SQL Server Database Growth and Autogrowth Settings

It's easy to create a database nowadays with point-'n-click, but if you've left your database's autogrowth settings at their default, you may hit problems in the future. Why? What do I do about it? Read on! Read more...

Grant Fritchey

7 Preventable Backup Errors

The loss of a company's data is often enough to put the company out of business; and yet backup errors are generally avoidable with the application of common sense rather than deep technical knowledge. Grant digs into memories of his long experience of giving forum advice, to come up with the most easily preventable backup errors. Read more...

Laerte Junior

Create a Monitoring Server for SQL Server with PowerShell

At some point, you are going to need a notification system for a range of events that occur in your servers, even if it is only a warning of low disk space. Laerte shows how you can even set up temporary or permanent alerts for any WMI events to give you a system that fits your server environment perfectly. Read more...

Grant Fritchey

Auditing DDL Changes in SQL Server databases

Even where Source Control isn't being used by developers, it is still possible to automate the process of tracking the changes being made to a database and put those into Source Control, in order to track what changed and when. You can even get an email alert when it happens. With suitable scripting, you can even do it if you don't have direct access to the live database. Grant shows how easy this is with SQL Compare. Read more...

Brad McGehee

Database Properties Health Check

Within an instance of SQL Server, the database settings can have a direct effect on the database’s behavior, performance and availability. Sometimes, it is difficult to tie a symptom to a cause, so it is wise to routinely check and record these settings. Before you change the current setting, it pays to understand exactly what it means and the implications of any change. Read more...

Fabiano Amorim

Statistics on Ascending Columns

It comes as rather a shock to find out that one of the commonest circumstances in an OLTP database, an ascending primary key with most querying on the latest records, can throw the judgement of the Query Optimiser to the extent that perfomance nose-dives. Fabiano once again puts on snorkel and goggles to explore the murky depths of execution plans to find out why. Read more...

Grant Fritchey

Preventing Problems in SQL Server

It is never a good idea to let your users be the ones to tell you of database server outages. It is far better to be able to spot potential problems by being alerted for the most relevant conditions on your servers at the best threshold. This will take time and patience, but the reward will be an alerting system which allows you to deal more effectively with issues before they involve system down-time Read more...

Laerte Junior

Using PowerShell and WMI Events Queries for Powerful Notifications

With PowerShell in one hand, and WMI in the other, DBAs can do almost anything in their Window's environments, and Laerte is using his powers for good. He built a highly precise, highly configurable alerting system for his servers, and now shows us exactly how he did it. Read more...

Glenn Berry

Configuring the Storage Subsystem

The storage subsystem for a SQL Server can prove to be a bottleneck if the best choices of hardware aren't made, but there are ways to relieve the I/O bottlenecks if the causes are well understood. This requires benchmarking. Glenn Berry gives expert advice on getting to grips with the disk subsystem. Read more...

Joe Celko

Arrays in SQL that Avoid Repeated Groups

It is certainly possible to fake an Array in SQL, but there are only a few occasions when it would be the best design. Most often, the wish for an array in SQL is a sign of a forlorn struggle against poorly-normalised data. One of the worst sins against Codd is the repeating group, as Joe explains. Read more...

Grant Fritchey

SQL Backup Pro for the Accidental DBA

If you've suddenly found yourself responsible for maintaining and backing up your company's servers, you're an 'accidental DBA'. If you've been dropped in the deep end, let Grant Fritchey show you through the backup and restore functionality of Red Gate's SQL Backup Pro, showing you how to schedule regular backups, compress, restore and document your backups with ease. Read more...

Grant Fritchey

SQL Server Backup and Restore for the Accidental DBA

Not everyone who is tasked with the job of ensuring that databases are backed up, and easily restorable, consider themselves to be database administrators. If you are one of these 'Accidental DBAs' then Grant Fritchey has some good straightforward advice for you to ensure that things go well when a database has to be restored from backups Read more...

Grant Fritchey

It's 3AM and I'm on call

If you are part of a team that is required to ensure that an application stays running at all hours, then you're likely to experience that 3AM-callout feeling. Grant knows all too well what is required, and gives hard-won advice on the best way of keeping on top of the task of keeping the IT services running, no matter what time of day the problems occur. Read more...

Grant Fritchey

Performing DBCC Checks Using SQL Virtual Restore

Ever restored a corrupted database from the backup, only to find that the backups are corrupted too - All the backups? Sure it can happen and it isn't nice when it does. To check that a database backup is internally consistent you have to use DBCC CheckDB. On a huge highly-loaded live system? Grant Fritchey has, as usual, a practical solution. Read more...

Feodor Georgiev

Database Documentation - Lands of Trolls: Why and How?

When database documentation is mentioned in an IT Department, everybody nods wisely, yet everyone does their best to avoid doing it. Attention to the database documentation can be the best invertment in time a development group can make. It is essential, and no system can be properly maintained without it. Feodor gives a sensible explanation and guideline for the unloved task of creating database documentation. Read more...

Mike Mooney

Simple Database Backups With SQL Azure

SQL Azure can take away a great deal of the maintenance work from a hosted database-driven website. It isn't perfect, however, in that it doesn't support the archiving of data by the users. Mike Mooney explains how he customised the solution with SQL Compare and SQL Data Compare to solve the problem of getting local backup copies of database schema and data. Read more...

Grant Fritchey

SQL Strategies for 'Versioned' Data

If you keep your data according to its version number, but need to work only with a particular version, what is the best SQL for the job? Which one works best? Which one do you use and when? Read more...

Hugo Shebbeare

The Polyglot of Databases: How Knowledge of MySQL and Oracle Can Give SQL Server DBAs an Advantage

Although switching between different RDBMSs can be the cause of some culture shock for the Database Administrator,it can have its advantages. In fact, it can help you to broaden your perspective of relational databases, refine your problem-solving skills and give you a better appreciation of the relative strengths of different relational databases Read more...

Brad McGehee

Correlating SQL Server Profiler with Performance Monitor

Both Performance Monitor and SQL Server Profiler provide valuable information about your server. However, if you combine the data from the two sources, then this correlated information can help find some of the subtler bugs. Brad explains how. Read more...

Timothy Ford

Investigating Transactions Using Dynamic Management Objects

There can be a great difference in the performance of a particular routine in a test database, and in a fully loaded production system. When you hit performance problems in a database under load, and there is excessive locking and blocking, how can you determine exactly where the problems lie, in order to fix them? Read on... Read more...

Rod Colledge

Policy-based Management and Central Management Servers

Whilst it may be more exciting as a DBA to rush around fixing broken databases, it is far better to forestall problems by making sure that your servers conform with best-practices. It is even better if you can also manage your servers centrally, and monitor that they are all adhering to company policies. Read more...

Brad McGehee

Rebuilding Indexes using the SSMS Database Maintenance Wizard

Index fragmentation can cause problems with query performance. Indexes therefore need to be occasionally rebuilt. the Rebuild Index task of the SSMS Database Maintenance Wizard drops and rebuilds every index in a database. It is effective but an off-line activity that is resource-intensive, so it not always the best way of avoiding index fragmentation in a production database. Brad explains... Read more...

Greg Larsen

Creative Solutions by Using a Number Table

One irritating thing for the occasional SQL Server Developer is that the experts assume that you are familiar with the use of number tables when they write about techniques. We therefore asked Greg Larsen to spell it out in a way that would help the beginner and intrigue those already familar with the technique. Read more...

Brad McGehee

Automate and Improve Your Database Maintenance Using Ola Hallengren's Free Script

If you ever feel uneasy when you set about handcrafting database maintenance jobs for SQL Server, it may be the voice of your conscience saying 'Ola, Ola!'. Follow your conscience. Ola Hallengren has already crafted a superb set of routines to do this for you. Why reinvent something that does it all, safely and well. Brad McGehee explains.... Read more...

Laerte Junior

Gathering Perfmon Data with Powershell

When you have to routinely collect data from Performance Monitor Counters, it soon becomes easier and more convenient to use PowerShell. SQL Server MVP Laerte Junior was inspired to create a script, and guides us through its useful functions. Read more...

Laerte Junior

I'm a SQL Server DBA, and I'm in Love with PowerShell

To learn PowerShell, Laerte suggests that you just start using it. To encourage you to start, he provides a series of tips on using PowerShell with SQL Server to solve various everyday problems. With a little patience, a good IDE, and a bit of help and advice, "the lion is dead". Read more...

Buck Woody

Mission Critical: SQL Server General Configuration

You know how to set up a SQL Server instance, but it is great to have a list of all the necessary checks and procedures: even better is to have the link to in-depth explanations for every stage by the renowned SQL Server Expert Buck Woody. Read more...

Thomas LaRock

Statistical Sampling for Verifying Database Backups

A DBA's huge workload can start to threaten best practices for data backup and recovery, but ingenuity, and an eye for a good tactic, can usually find a way. For Tom, the revelation about a solution came from eating crabs. Statistical sampling can be brought to bear to minimize the risk of failure of an emergency database restore. Read more...

Buck Woody

Mission Critical: SQL Server Upgrade

You're faced with the task of doing a SQL Server Upgrade? Do you know all the steps, and the right order to do them? You do? Even with interruptions and distractions? Maybe, but it is wise to be able to refer to the Mission-Critical Task checklist. Read more...

Buck Woody

Mission Critical: Engine Pre-Installation

Even with a task you're entirely familiar with, it is always great to have a checklist to make sure it is all done. No? Have you never forgotten your car-keys? Read more...

Buck Woody

Mission Critical: Database Design

There is nothing like a checklist to make sure you've completed all the tasks in designing a database, and there is absolutely nothing like Buck Woody's military-style Critical Task List Read more...

John Magnabosco

Transparent Data Encryption

Transparent Data Encryption is designed to protect data by encrypting the physical files of the database, rather than the data itself. Its main purpose is to prevent unauthorized access to the data by restoring the files to another server. With Transparent Data Encryption in place, this requires the original encryption certificate and master key. It was introduced in the Enterprise edition of SQL Server 2008. John Magnabosco explains fully, and guides you through the process of setting it up. Read more...

Rod Colledge

Planning for Disaster

There is a certain paradox in being advised to expect the unexpected, but the DBA must plan and prepare in advance to protect their organisation's data assets in the event of an unexpected crisis, and return them to normal operating conditions. To minimise downtime in such circumstances should be the aim of every effective DBA. To plan for recovery, It pays to have the mindset of a pessimist. Read more...

Buck Woody

Mission Critical: SQL Server 2008 Engine Post-Installation

Even an experienced DBA finds it safer to double-check that all the tasks have been done, and in the right order. Buck Woody continues his series with another handy checklist, army-style. Read more...

Buck Woody

Mission Critical: SQL Server 2008 General Maintenance

There is nothing that beats a simple checklist for ensuring that things don't get forgotten in the Database Maintenance process. Once again, Buck Woody imagines how the US military would have tackled DBA checklists for 'mission-critical' databases. Read more...

Laerte Junior

Exceptional PowerShell DBA Pt 3 - Collation and Fragmentation

In this final look into his everyday essentials, Laerte Junior provides some useful scripts for the DBA that use an alternative way of error-logging. He shows how to use a PowerShell script to check and, if necessary, to defragment your indexes, write data to a SQL Server table, and change the collation for a table. Being an exceptional DBA just got a little easier. Read more...

Buck Woody

Mission Critical: SQL Server 2008 Performance Tuning Task List

In which Buck Woody imagines how the US military would have tackled DBA checklists for 'mission-critical' databases. And having imagined it, lo, it came to be. The CTL for the DBA with short-back-and-sides. Read more...

Laerte Junior

Exceptional PowerShell DBA Pt 2 - The Morning Checklist

Laerte Junior takes us further into the world of an Exceptional PowerShell DBA, showing us how he uses PowerShell 2.0 to take all the headaches out of even more of his daily checklist. What could be better than having your morning checklist run itself? Read more...

Rodney Landrum

Managing Data Growth in SQL Server

'Help, my database ate my disk drives!'. Many DBAs spend most of their time dealing with variations of the problem of database processes consuming too much disk space. This happens because of errors such as incorrect configurations for recovery models, data growth for large objects and queries that overtax TempDB resources. Rodney describes, with some feeling, the errors that can lead to this sort of crisis for the working DBA, and their solution. Read more...

Brad McGehee

Brad's Sure DBA Checklist

Sometimes, all a DBA needs, to help with day-to-day work, is a checklist of best-practices and dos and don’ts. It provides a handy reminder. Brad has come up with a new update to his famous checklist Read more...

Brad McGehee

Database Maintenance Plans in SSMS: An Overview

Maintenance plans are essential. Microsoft provide two tools, the Maintenance Plan Wizard and the Maintenance Plan designer, to assist the busy DBA to get started with one, but they don't include all the likely tasks, so Custom-created T-SQL or PowerShell scripts are usually required sooner or later to provide a more comprehensive plan Read more...

Thomas LaRock

Monitoring SQL Server Virtual Log File Fragmentation

One of the delights of PASS is to be able to pick up ideas from some of the presentations and recombine them in new and interesting ways. Tom recounts how he used two different insights to solve a problem of monitoring a large number of servers for signs of Virtual Log-file Fragmentation. Read more...

Robert Sheldon

Working with the bcp Command-line Utility

Even though there are many other ways to get data into a database, nothing works quite as fast as BCP, once it is set up with the right parameters and format file. Despite its usefulness, the art of using the command-line utility has always seemed more magic than method; but now along comes Robert Sheldon to shed light on the murky details. Read more...

Laerte Junior

Exceptional PowerShell DBA Pt1 - Orphaned Users

Inspired by Brad McGehee's 'How to Become an Exceptional DBA', Laerte decided it was high time he took a proactive approach to his work. Using PowerShell, he came up with a solution that makes finding and fixing orphaned users as easy as cake, and now he's sharing it with us. Read more...

Rodney Landrum

Eating SQL Server Installations for Breakfast

Here you will find wholesome SQL Server installations on the menu, complete with Express, Continental and Deluxe breakfast choices, depending on your application’s appetite. This is the article where your new SQL Server installation is completely yours, having not as yet been turned over to the general populace of developers or users. Enjoy it while you can: From the SQL Server Tacklebox Read more...

Bob Cramblitt

Exceptional DBA Josef Richberg: fighting cancer, but still sharing

Josef Richberg is an exceptional person: The Judges of the Exceptional DBA Award were quickly proved right in their decision to choose him, when he subsequently showed extraordinary resilience and determination in the face of an unexpected diagnosis of cancer. His determination to deal positively with a difficult life-event is surely a source of inspiration to all of us. Read more...

John Magnabosco

Obfuscating your SQL Server Data

If you are required to use real production data to test applications, any sensitive data should be "disguised" before loading it into the development environment. Although there are tools to generate convincing test data, it occasionally happens that the variances and frequencies within data cannot easily be simulated. In such cases, the DBA should apply one or more of the obfuscation techniques described in this article, extracted from John Magnabosco's excellent new book, Protecting SQL Server Data. Read more...

Anith Sen

Five Simple Database Design Errors You Should Avoid

Anith follows up his highly successful article Facts and Fallacies about First Normal Form with a fascinating discussion of five common database design errors which persist in spite of the fact that the unfortunate consequences of their use is so widely known. It is a needy reminder to anyone who has to design databases. Read more...

Rodney Landrum

Finding Data Corruption

In this article, taken from Chapter 8 of his new book, SQL Server Tacklebox, Rodney describes how a working DBA goes about troubleshooting data corruption. He demonstrates the tools and scripts required to seek out and fix data corruption in a timely manner, and so prevent it propagating into your backups. Read more...

John Magnabosco

Honeycombing a Database

In this article, taken from Chapter 9 of his new book, Protecting SQL Server Data, John describes how to set a "honey trap" for would-be data thieves, allowing the DBA to identify the precursors of an attack and respond quickly and also to better understand the techniques being used to breach existing security measures. Read more...

Thomas LaRock

SQL Server Consolidation

In the crusade to cut expenses, Data Centres (and IT Infrastructure) are now coming under scrutiny as a possible source of savings. Half-informed executives now rally behind the cry of "Virtualization!", but virtualization is only part of the story - The tip of the consolidation iceberg - and it might not actually save you as much as you thin. Thomas takes us deeper. Read more...

Brad McGehee

A Code of Conduct for DBAs

Despite the fact DBAs are protectors of an organization’s knowledge, and privy to much confidential information, there is no clearly defined set of rules and standards to help govern and guide their ethical conduct. Brad McGehee, in an article based on a chapter from the second edition of his book 'How to Become an Exceptional DBA', discusses a Code of Conduct for DBAs. He advises on why such a code is required, the sort of topics it should contain, and how it might be enforced. Much of this article has relevance for any IT professional. Read more...

Timothy Wiseman

Python for the SQL Server DBA

Python is increasingly used by DBAs as a general-purpose scripting language, despite the pressure to adopt Microsoft's PowerShell. They find it easy to learn, powerful, and reasonably intuitive. Here Timothy Wiseman, a working DBA, explains the attraction of Python and gives a general introduction to the language, suitable for anyone who has been wondering whether to give it a try. Read more...

Rodney Landrum

SQL Server Tacklebox Free eBook

As a DBA, how well-prepared are you to tackle issues such as backup failure due to lack of disk space, or locking and blocking that is preventing critical business processes from running, or data corruption due to a power failure in the disk subsystem? If you have any hesitation in your answers to these questions, then Rodney Landrum's SQL Server Tacklebox is a must-read. Read more...

Robert Sheldon

Defragmenting Indexes in SQL Server 2005 and 2008

Rob Sheldon tackles the subject of Index Defragmentation in SQL Server 2005 and 2008, using the sys.dm_db_index_physical_stats system function. He shows how to analyze indexes and, if necessary, how to go about  reorganizing or rebuilding indexes. He makes the point that, by analyzing indexes effectively, you can save a lot of unnecessary  rebuilding of indexes. Read more...

Rodney Landrum

The DBA as Detective: Troubleshooting Locking and Blocking

In this article, taken from Chapter 5 of his great new book, SQL Server Tacklebox, Rodney describes in his own unique style how he, as a working DBA, goes about troubleshooting problem queries, and investigating various types of locking and blocking problems. In the process, he  passes on valuable tips learned from practical, and sometimes stressful, experience. Read more...

Brad McGehee

Do Not Forget to Maintain Your Indexes

Index Maintenance Plans are essential for the well-being of a database. However, there is more to the process than just the use of the wizard. Brad McGehee takes this one stage further in suggesting that, instead, you should create your own index maintenance tasks that you can schedule to run using the SQL Server Agent. Read more...

Brad McGehee

Brad's Sure Guide to Indexes

In order to best appreciate the reasons for, or importance of, the various administrative tasks that are needed for indexes, it is a good idea to take a look at how the various types of indexes work. Who better to help with a clear expanation for this than Brad McGehee? Read more...

Thomas LaRock

SQL Server Audit: Magic without a Wizard

In SQL Server 2008, Microsoft introduced SQL Server Audit. This is much better than anything we had before, and is likely to meet the needs of all but the largest, or the most highly-regulated of industries. SQL Server 2008 Enterprise Edition includes all of the features whereas SQL Server 2008 Standard Edition only provides only a subset. What is most attractive about it is that it is easy to administer, as Thomas LaRock explains. Read more...

Allen White

Let PowerShell do an Inventory of your Servers

If you run a regular and comprehensive inventory of all the servers you manage, you can solve problems more quickly and answer most questions from management. If you then repeat these reports over a time period, it helps to to track trends such as disk space usage so you can spot trouble before it becomes a problem. Allen describes a PowerShell script to do the inventory. Read more...

Alex Kozak

Estimating Disk Space Requirements for Databases

In the first of a series of Database Maintenance Tips and Tricks, Alex Kozak discusses some general issues surrounding disk space requirements for SQL Server databases and shows how to estimate Disk space usage and requirements. He gives tips on using DBCC CHECKDB consistency check and for estimating the space required for replication. Read more...

Thomas LaRock

Monitor your Database Backups Using Operations Manager

Thomas LaRock shows just how easy it is for a DBAs to monitor any aspect of the databases in their charge by using Operations Manager, just as long as they are prepared to spit on their hands and do some coding. As he says, 'The possibilities are endless'. Read more...

Rodney Landrum

The DBA Script Thumb

Like many DBAs, Rodney has squirrelled away a large number of routines that he uses almost daily to check on his servers and databases. Of this large collection he chooses five that he wouldn't want to be without. and there is something for everyone in this DBA's Script collection which goes with him on his 'Script thumb' Read more...

Francis Hanlon

On the Trail of the Expanding Databases

It is sometimes difficult for other IT people to understand the constraints that DBAs have to work under. So often the ideal or obvious solution is frustratingly out of reach because of 'political' reasons that seem baffling to the spectator. Francis Hanlon describes a typical incident: It is essential reading for anyone wishing an insight of the problems that DBAs face Read more...

Ron Dameron

Why This SQL Server DBA is Learning Powershell

Ron describes how he decided to study Powershell as a single scripting system to automate all the common repetitive server tasks. He concludes that time spent learning PowerShell is time well spent, and that it can help a great deal in understanding the .NET Framework Read more...

Brad McGehee

Policy-Based Management

Every DBA knows the frustration of trying to manage tens of servers, each of which has a subtly different configuration. Policy-based management, now introduced in SQL Server 2008, could ease a lot of this pain. Brad McGehee explains.... Read more...

Rodney Landrum

SQL Server eBook Download - SQL Server Tacklebox

The SQL Server Tacklebox contains a collection of practical tools and techniques to automate and standardize SQL Server installation, document and report on servers, migrate data and manage data growth, troubleshoot performance issues, receive notifications of impending issues, secure access servers and fight off the data corruption. Read more...

Randy Volters

DML Trigger Status Alerts

When databases suddenly stop working, it can be for a number of different reasons. Human error plays a large part, of course, and the DBA needs to know what these various humans are up to. DDL triggers can help alert the DBA to unauthorized tampering with a production system, of course, but DDL triggers can't tell you everything. At some point, you will need to implement your own checks. Read more...

Alexander Karmanov

Deploying Database Developments

When a team is developing a database application, it is a mistake to believe that deployment is a simple task. It isn’t.  It has to be planned, and scripted. Alexander Karmanov describes many of the problems you’re likely to meet, and provides an example solution that aims to save the DBA from the nightmare complexity of an unplanned deployment. Read more...

Brad McGehee

Professional Certification for DBAs

In this article, republished from Brad McGehee's book 'How to Become an Exceptional DBA', Brad explains why there are several advantages for DBAs in continuing to take exams throughout their careers. Read more...

Shawn McGehee

SQL Server Tracing: An Automated and Centralized Solution

When you are trying to pin down the cause of a problem with a SQL Server, there is probably going to come a time when you need to get 'trace' information. If you've ever done that, you'll know how easy it is to get overwhelmed by the detail. Here, Shawn McGehee shows how to get round the problem by capturing trace information on a schedule, filtering the captured information, and monitoring it from a central location. Read more...

Robyn Page and Phil Factor

SQL Server Alerts: Soup to Nuts

In which Robyn Page and Phil Factor try to get to grips with the difficult subject of SQL Server Alerting, and give you enough detail to put effective alerting systems into your database. Read more...

Rodney Landrum

Doing Eighty Things at Once

Rodney Landrum is a SQL Server DBA for a large company, with at least eighty SQL Servers to look after. He is now a great fan of SQL Multiscript and explains why ... Read more...

William Brewer

The Science of Compliance

'William Brewer takes a look at what is involved with IT 'Compliance', and discusses what SOX compliance means for the DBA and IT Application developer.' Read more...

Timothy Ford

Greetings from the Alpaca Mill

Timothy Ford fails to make the leap from his cubicle to the Alpaca farm, due to the help, sympathy and advice from Simple-Talk's readers Read more...

Rodney Landrum

Changing Service Credentials

One day, you may need to change those service credentials under which your SQL Server services normally run. If you have a number of servers, then you'll really want to read about Rodney's solution. Read more...

Simon Sabin

What Specification Server Should I Buy?

Simon Sabin explains why he is always cagey about giving advice on the sort of hardware to run SQL Server on, but admits to some general rules. Read more...

Timothy Ford

Do Alpacas Dream of farming DBAs?

When IT starts to stand for 'Implement This', even the keenest DBA begins to dream of farming Alpacas as a career-change. ...and we ask our readers how they would have solved the DBA's dilemma. Read more...

Ron Dameron

Comparing Python and PowerShell DBA Scripting

Just when it seemed that there were were no adequate scripting systems for DBAs that could access .NET, along came two. Powershell and Python. Read more...

William Brewer

SQL Server Endpoints: Soup to Nuts

SQL Server Endpoints are database objects that define the ways and means that SQL Server 2005 communicates on the network. Any DBA working with SQL Server 2005 will soon need to become familiar with them, particularly if using SOAP, Service Broker or Database Mirroring. Read more...

András Belokosztolszki

Foreign Keys and their States

András Belokosztolszki, Red Gate developer, shows how foreign key constraints can be disabled and re-enabled in order to simplify operations such as the bulk loading of data into related tables Read more...

William Brewer

Database High-Availability: Soup to Nuts

William Brewer argues that, although there are technologies around that will minimise downtime in most circumstances, they are only part of the solution. At the heart of every robust system, there is planning, documentation, scripting, testing and drill. Read more...

András Belokosztolszki

Source Control and Databases

András's article on Source Control shows a lot of the thinking amongst the developers at Red Gate at that time that eventually crystallized into SQL Source Control. It identified several problems that had to be overcome before the task could be done properly. Andras's suggestions for the use of SQL Compare are now built-in to SQL Source Control, but it remains a fascinating explanation of the complications along the way. Read more...

Robyn Page and Phil Factor

SQL Server Replication Crib Sheet

Robyn Page and Phil Factor delve into all the things you need to know, rather than want to know, about SQL Server replication. Read more...

Nigel Rivett

Partitioned Tables in SQL Server 2005

Nigel Rivett provides an in-depth, practical examination of how to create and manipulate partitioned tables and indexes in SQL 2005. Read more...

Doug Burns

What use is a Development DBA?

"I can't help thinking that unless you have a good DBA on a development team and use him or her as a consultant on all database matters, we're all losing out. I end up having work thrown at me that I could teach a trainee to do, which is a waste of my talents, and the development team's database skills might be 'good enough' but could be so much better." Doug Burns assesses the role of the Development DBA and its increasing importance to the success of software projects. Read more...

Dan Sullivan

PowerSMO at Work Part 2

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

Louis Davidson

Ten Common Database Design Mistakes

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

Arthur Fuller

Database Design: A Point in Time Architecture

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

Robyn Page

SQL Server Security Cribsheet

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

PowerSMO At Work Part I: DBA Scripts and Functions

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

Pop Rivett

Pop Rivett and the Uncontrolled Release

Pop Rivett offers a hard lesson in the dangers of an uncontrolled software release. Read more...

Dan Sullivan

Managing SQL Server using PowerSMO

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

Phil Factor

The Data Dialog

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

Scott W. Ambler and Pramod J. Sadalage

Refactoring Databases: The Process

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

Phil Factor

Automating Common SQL Server Tasks using DMO

Phil Factor's DMO automation procedures provide a valuable additon to any DBAs toolkit. Read more...

Nigel Rivett

Creating CSV Files Using BCP and Stored Procedures

Nigel Rivett demonstrates some core techniques for extracting SQL Server data into CSV files, focussing on a stored procedure approach that gives complete control of the format of the extracted data. Read more...

Pop Rivett

Pop Rivett's SQL Server FAQ No.5: Pop on the Audit Trail

Pop provides a cunning, trigger-based technique for auditing the activity on SQL Server tables Read more...

Sanchan Sahai Saxena

Fine Tuning your Database Design in SQL 2005

Sanchan Saxena gets to grips with the new index-tuning tools and features in SQL 2005. Read more...

Phil Factor

More Database Administration and Development Automation using DMO

Phil Factor provides a handy DMO automation toolkit to take care of some core SQL Server administrative tasks. Read more...

Andrew Calvett

SQL Server 2005 Snapshots

Find out about Database Snapshots, new to SQL 2005 Enterprise Edition, which provide a read-only, "virtual" copy of a database, at a given point in time. Read more...

Hilary Cotter

The identity crisis in replication

This article discusses three common problems DBAs are likely to encounter when columns have an identity property that will auto-increment its value when data is inserted. These problems are humorously referred to as the identity crisis. Read more...

Brian Moran

Tracking tempdb growth

Runaway tempdb growth can be a problem, so how do you track its growth and correlate the growth to specific commands? Read more...

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.