Category Archives: Database options

The full text error and why I was happy to see it.

The Situation: I was recently involved in the migration of a group of core databases from three SQL Server 2005 instances to three SQL Server 2012 instances. There were quite a few moving parts that needed to be considered as part of … Continue reading

Posted in Database options, Disaster Recovery, SQL Server | Tagged , , , | 2 Comments

Quick Tip: Migrating a SQL Server Mirroring Witness server.

If you’re like me, you might be thinking “I want some more things to think about when I migrate between SQL Servers.” … No? Okay, fair enough. There’s a whole list of things to think about when migrating from one SQL … Continue reading

Posted in Database options, Disaster Recovery, SQL Server, T-SQL | Tagged , , | 3 Comments

In Memory OLTP with SQL Server 2014

SQL Server 2014 CTP is available for download and evaluation and it contains a couple of exciting performance enhancements. One of these is OLTP databases optimised to be memory resident. So I wanted to have a play – and I … Continue reading

Posted in Database options, Database Theory, SQL Server, T-SQL | Tagged , | 1 Comment

SQL Server 2008 Statistics: What does a DBA need to know?

I’ve been looking into the behaviour of statistics in SQL Server 2008 – so I’d like to present an overview from a DBA perspective. What are statistics? Statistics are a database scoped object that are used by the query optimiser … Continue reading

Posted in Database options, SQL Server, Uncategorized | Tagged , | Leave a comment

Restore database permissions

Introduction: I came across a couple of subtle gotchas around the permissions required to restore a database in SQL Server 2008+ recently. I’m interested in situations where a DBA needs to allow certain users the ability to restore a given … Continue reading

Posted in Database options, Disaster Recovery, Security, SQL Server, T-SQL | Tagged , , | 13 Comments

Migrating SQL Server Databases that use Database Master Keys

There’s a lot of things to consider when migrating databases between SQL Server instances. I want to provide some tips for dealing with Database Master Keys, and in particular the case when those keys are encrypted by the Service Master … Continue reading

Posted in Database options, Disaster Recovery, Security, SQL Server, T-SQL | Tagged , , | 27 Comments

Quick tip: Count table rows in a database, and locate those rows in the data files.

Here are a couple of quick database and table level queries that I have found useful lately. How many rows in each table of a database? The first temptation with this one was to use something like sp_MSforeachtable with a … Continue reading

Posted in Database options, Database Theory, SQL Server, T-SQL | Leave a comment

Querying database usage

A question that a DBA will often be asked is what databases are actively being used on my SQL Server? We get asked it all the time, usually as part of a consolidation or rationalisation project. My solution to this … Continue reading

Posted in Database options, SQL Server, T-SQL | Leave a comment

Quick tip: Querying database metadata with DBCC DBINFO

Page 9 in file 1 (of the primary filegroup) in a SQL Server database is the header page and contains a wealth of metadata about the database. We can have a look at this with DBCC PAGE or DBCC DBINFO. … Continue reading

Posted in Database options, SQL Server, T-SQL | Tagged , | 1 Comment

Service Broker: What does a DBA need to know?

Service Broker is SQL Server’s internal messaging system. It has been designed as an asynchronous, reliable and transactional messaging system for inter or intra database messaging, or even inter SQL instance messaging. It is a powerful tool for decoupling extended … Continue reading

Posted in Database options, Service Broker, SQL Server, T-SQL | 7 Comments