Question: What mechanism allows SQL Server 2008 to commit transactions on the mirror faster than SQL Server 2005?

This question was asked on a technical discussion group. My reply follows.

Hello. In SQL 2008 we have touted the “fast commit” that enables the mirror to more rapidly provide acknowledgement to the principal that it has received the transaction. Is this feature implemented through caching mechanisms that are persisted to disk? Or does the mirror allocate RAM for this and then provide the acknowledgement? The former is what the customer would like to hear for fear of data loss.

My answer:

I haven’t heard the term “fast commit” used before, but I can make an educated guess at what you are asking. There are a few different things going on that allows the transactions to be committed on the mirror faster in SQL 2008.

1. Log stream compression: The stream of tran log records are compressed before being sent to the mirror. This results in faster transfer of transactions.
2. Write-ahead of the log stream: As transactions are streamed in, the transactions are written to disk without processing them while at the same time transactions written to disk are being processed by the mirror.
3. Better use of the log send buffers: rather than create a new log send buffer in the log cache every time a log flush occurs, SQL reuses existing log send buffers if there is enough free space to hold the log records. The log cache is a section of memory reserved for storing data that is going to written to the log.

Suggested Reading: Microsoft SQL Server 2008 Administration with Windows PowerShell

My earliest forays into writing powershell for SQL Server found me scrambling for help. I found several resources online that covered some specific topics, but I still had trouble finding what I needed.

Ultimately, I found a lot of great articles on one of my old haunts Database Journal. I used to be regular on Database Journal. I posted some of my earliest articles there and am still a moderator of some of the forums there. I got a lot of help from some articles written by a fellow dba and Database Journal regular Muthusamy Anantha Kumar aka "The MAK". I got some additional help from articles written by another Database Journal regular that I didn't know as well, Yan Pan.

The book: Microsoft SQL Server 2008 Administration with Windows PowerShell

I decided to check out Amazon.com to see if they had any books on powershell for dba's and learned that MAK and Yan Pan have collaborated on a book on this subject. Based on the articles I have already read by MAK and Yan Pan on Database Journal and from already knowing the depth of experience and knowledge that MAK brings to the table, I pre-ordered a copy for myself. I think it will become an essential book for anyone that uses powershell for database administration.

Best of luck MAK and Yan!!

Behind Every Great Restore is a Great Backup

I showed you how to do a database backup with powershell a few days ago. Today I'll show you how to do the restore. Restores can sometimes be quite a bit more complicated than a backup. You have to account for the possibility that the database file names may already be in use by another database or the database may already exist. If the database does already exist, the restore process requires exclusive access to the database.

A Powershell Restore Script

This script accepts 3 parameters for the server name, the database name, and the path and name of the backup. The previous backup script returns the path and name of the backup upon successful completion. If you are backing up the database in order to restore it someplace else, you can use this return value as a parameter for the restore script. I show an example of this further down.

Parameters:

1. $Server: [String], Name of server, Required.
2. $Database: [String], Name of database, Required.
3. $Backup: [String], Backup path and name, Required.

Example Usage:

.\RestoreDB.ps1 MyServer MyDatabase "c:\mssql\BAK\MyDatabase.bak"

Using the backup and restore scripts together:

$BAK = .\BackupDB.ps1 MyServer MyDatabase "c:\mssql\BAK"

.\RestoreDB.ps1 MyServer MyDatabase $BAK

A real world demo:

PS C:\Windows\System32\WindowsPowerShell\v1.0> pushd C:\Users\v-rodav\Documents\Powershell
PS C:\Users\v-rodav\Documents\Powershell> $BAK = .\BackupDB.ps1 "v-rodav4" PSP "C:\bak"
        Database PSP backed up to C:\bak\PSP\PSP_backup_20090422210339.bak
PS C:\Users\v-rodav\Documents\Powershell> .\RestoreDB.ps1 "v-rodav4" PSP $BAK
        Database PSP restored from C:\bak\PSP\PSP_backup_20090422210339.bak

The Script

param (
        [string] $Server,
        [string] $Database,
        [string] $Backup
)

## Path and name used to invoke script
$CUR_SCRIPT = $myinvocation.InvocationName

## Load SMO assemblies
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")|out-null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")|out-null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
$SMO = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
## Parse out the internal version number
$SMOVer = $SMO.FullName.Split(",")[1].Split("=")[1].Split(".")[0]
## Load SMOExtended if not SQL Server 2005 (9)
if ($SMOVer -ne 9) {
        [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")|out-null
}

## Check user input, prompt for each value not provided as parameters
if(!$Server) { $Server = read-host "Enter Server Name" }
if(!$Database) { $Database = read-host "Enter Database Name" }
if(!$Backup) { $Backup = read-host "Enter Backup Path\Name" }

## Return Help and exit if any required input is missing
if(!$Server -or !$Database -or !$Backup) {
        write-host "Usage: $CUR_SCRIPT options:
        string SQL Server Instance
        string Database Name
        string Backup Path and Name" -f red
        exit
}

## Function to raise error
Function RaisError ([string]$ErrMsg){
        write-host $ErrMsg -f red
        $error.clear()
}

## Create server object
$Srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Server

## Make sure backup file exists
$BackupExists = Test-Path $Backup
if (!$BackupExists) {
        RaisError "`tBackup file does not exist or is not accessible."
        Exit
}

## If database exists, delete it
$DBExists = $Srv.Databases[$Database]
if ($DBExists) {
        if ($DBExists.status -eq "online") {
                $Srv.KillDatabase($Database)
        } else {
                $DBExists.drop()
        }
        if ($error){
                RaisError "`tDrop of existing database returned an error."
                Exit
        }
}

## Restore the database
$Restore = new-object "Microsoft.SqlServer.Management.Smo.Restore"
$Restore.Database = $Database
$Restore.Action = 'Database'
$BkFile = new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$BkFile.DeviceType = 'File'
$BkFile.Name = $Backup
$Restore.Devices.Add($BkFile)
$Restore.ReplaceDatabase = $false

## Check file list and generate new file names if files already exists
$DateSerial = Get-Date -Format yyyyMMddHHmmss
$DataFiles = $Restore.ReadFileList($Server)
ForEach ($DataRow in $DataFiles) {
        $LogicalName = $DataRow.LogicalName
        $PhysicalName = $DataRow.PhysicalName
        $FileExists = Test-Path $PhysicalName
        if ($FileExists) {
                $PhysicalName = $PhysicalName -replace(".mdf", "_$DateSerial.mdf")
                $PhysicalName = $PhysicalName -replace(".ldf", "_$DateSerial.ldf")
                $PhysicalName = $PhysicalName -replace(".ndf", "_$DateSerial.ndf")
                $Restore.RelocateFiles.Add((new-object microsoft.sqlserver.management.smo.relocatefile -ArgumentList $LogicalName, $PhysicalName)) | out-null;
        }
}
$Restore.NoRecovery = $false
$Restore.PercentCompleteNotification = 5
$Restore.SqlRestore($Server)
if (!$error){
        write-host "`tDatabase $Database restored from $Backup" -f green
} else {
        RaisError "`tRestore of database $Database returned an error."
        Exit
}

I Need a Backup ... And I Need It Now

Have you ever wished that creating a backup on the fly was easier and quicker? Whether you do it with the GUI or with T-SQL, you still have to open up Management Studio, connect to the database, and go through the steps. You have to figure out where to put it, give it a name, and so on. An easier way is to have a script ready that you can simply pass a couple of parameters and let it do all of the work for you. You can do this easily enough with a Windows script (what we old-timers like to call DOS when we're feeling nostalgic) to call SQLCmd. You can also do it with Powershell. I'll show you how.

A Powershell Backup Script

This script accepts 3 parameters for the server name, the database name, and the backup path. The backup path parameter is optional. If not provided, the script will use the default backup directory configured for the server. If not provided and the default backup directory returns null, an error will be raised. The backup directory should be set automatically at installation, so this shouldn't happen unless you purposefully delete the configuration.

Parameters:

1. $Server: [String], Name of server, Required.
2. $Database: [String], Name of database, Required.
3. $BackupPath: [String], Backup directory, Optional, Defaults to configured server defualt

A subdirectory with the name of the database will be created for the backup. For example, if you pass in MyDatabase for the database and c:\mssql\bak as the backup path, the backup will be created in c:\mssql\bak\MyDatabase\.

Wow, you posted about something unrelated to database mirroring?

Well, not exactly. This script is really just a small part of my database mirroring automation script. As I'm sure you know, you must create a full backup as one of the steps for setting up database mirroring. So, yes, there is a tie in.

What About Restoring the Backup

Yes, I already have that script written too. I will post it soon. Probably tomorrow or Monday. Come back soon.

Finally, the Script

param (
        [string] $Server,
        [string] $Database,
        [string] $BackupPath
)

## Path and name used to invoke script
$CUR_SCRIPT = $myinvocation.InvocationName

## Load SMO assemblies
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")|out-null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")|out-null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
$SMO = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
## Parse out the internal version number
$SMOVer = $SMO.FullName.Split(",")[1].Split("=")[1].Split(".")[0]
## Load SMOExtended if not SQL Server 2005 (9)
if ($SMOVer -ne 9) {
        [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")|out-null
}

## Check user input, prompt for each value not provided as parameters
if(!$Server) { $Server = read-host "Enter Server Name" }
if(!$Database) { $Database = read-host "Enter Database Name" }
if(!$BackupPath) { $BackupPath = read-host "Enter Backup Path (optional)" }

## Return Help and exit if any required input is missing
if(!$Server -or !$Database) {
        write-host "Usage: $CUR_SCRIPT options:
        string SQL Server Instance
        string Database Name
        string Backup Path (optional)" -f red
        exit
}

## Function to raise error
Function RaisError ([string]$ErrMsg){
        write-host $ErrMsg -f red
        $error.clear()
}

## Create server object
$Srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Server

## Get default backup path if not provided
if (!$BackupPath) {
        $BackupPath = $Srv.BackupDirectory
}

## Make sure backup path exists
if ($BackupPath) {
        $BackupPath = [System.IO.Path]::Combine($BackupPath, $Database)
        [System.IO.Directory]::CreateDirectory($BackupPath) | out-null
} else {
        RaisError "`tUnable to find a backup path"
}

## Connect to database
$DBase = $Srv.Databases[$Database]

## Create backup name
$BkDate = Get-Date -Format yyyyMMddHHmmss
$BkName = $Database + "_backup_$BkDate.bak"

## Backup the Principal database
$Backup = new-object "Microsoft.SqlServer.Management.Smo.Backup"
$BkFile = new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$BkFile.DeviceType = 'File'
$BkFile.Name = [System.IO.Path]::Combine($BackupPath, $BkName)
$BKFileName = $BkFile.Name
$Backup.Devices.Add($BkFile)
$Backup.Database = $Database
$Backup.Action = 'Database'
$Backup.Initialize = 1
$Backup.BackupSetDescription = "Backup of database $Database"
$Backup.BackupSetName = "$Database Backup"
$Backup.PercentCompleteNotification = 5
$Backup.SqlBackup($Srv)
if (!$error){
        write-host "`tDatabase $Database backed up to $BkFileName" -f green
        return $BkFileName
} else {
        RaisError "`tDatabase $Database backup returned an error."
}

What's my name?

Fully qualified domain name that is. One of the little gotchas I encountered when rewriting my database mirroring automation scripts in powershell was that there is no simple way to get the SQL Server's fully qualified domain name. I didn't want to resort to opening a connection to the SQL Server to run a T-SQL query. There had to be a better way through powershell.

I started looking at ways to ping or query DNS via powershell. I went down the ping highway first and wasn't convinced that it was the right way to go. I changed my thinking to querying DNS and was surprised at just how simple querying DNS is with powershell.

system.net.dns

With a single call to the GetHostEntry function of system.net.dns, I was able to get the fully qualified domain. The syntax is as follows:

          [system.net.dns]::GetHostEntry("Machine Name").HostName

For the database mirroring script, I need to call the function 2 or 3 times depending on if I am configuring a witness. I assign a variable to the function for each server passing in the appropriate server's name. I then use the variables for creating the TCP connection strings for assigning the mirroring partners and witness.

Can it stand alone?

I wrote a little scriptlet that can be reused to make the process easy. The script accepts a machine name as a parameter, but if you don't give it a machine name, it will just use the name of the local machine.

          Param ($Machine)

          if (!$Machine) {$Machine = read-host "Enter machine name"}

          if (!$Machine) {$Machine = get-content env:Computername}

          $FQDN = [system.net.dns]::GetHostEntry($Machine).HostName

          return $FQDN

A Port, a Port! My Kingdom for a Port!!

One of the steps in setting up database mirroring is to assign a port to the endpoint. It's common practice to simply give it port 5022, but what if that port is already being used? The endpoint creation doesn't verify the availability of a port and will be created successfully. You won't know that the port is already being used until you get a communication error when trying to start the mirroring session. Then you have to go through troubleshooting steps to determine if it is the port, and if so, which port?

There's an easier way

Rather than waiting for mirroring to fail with a communication failure, why not simply verify the availability of a port before assigning it to the endpoint? Well this too kind of sounds like a lot of trouble to go through to prevent a failure that only occurs a small percentage of time.

I figured that there had to be an easy to do it. I was in the process of writing an powershell script for automating database mirroring setup when I decided to look for a solution to this problem. This issue was especially pressing for me because I was testing the script using instances on the same server. I did not want to rely on hard coded port numbers, and I wanted my solution to be part of my powershell script. So I found a powershell solution.

The Script

In addition to including the script in my database mirroring setup script, I created a stand-alone powershell script for this function alone.

Execution Syntax:

     .\FindAFreePort.ps1 "RemoteServer"
     .\FindAFreePort.ps1 "RemoteServer" "5022,5023"

Parameters:

     $ServerToCheck - [string] Name of server to check.
     $PortsToExclude - [string] Comma delimited list of ports to exclude.

FindAFreePort.ps1:

## Find an unused port
Param (
     [string] $ServerToCheck,
     [string] $PortsToExclude
)

if (!$ServerToCheck) {
     $ServerToCheck = read-host "Enter server to check:"
}

if (!$PortsToExclude) {
     $PortsToExclude = read-host "Enter ports to exclude (optional):"
}

if (!$ServerToCheck) {
     write-host "     Server to check is a required parameter. Execution halted." -f red
     Exit
}

$PortsUsed = $PortsToExclude.Split(",")
$PortArray = ((5022..5025), (7022..7025), (5026..6000), (7026..8000))
$socket = new-object System.Net.Sockets.TcpClient
$PortAvailable = 0
foreach ($Ports in $PortArray) {
     foreach ($Port in $Ports) {
          if ($PortsUsed -notcontains $Port) {
               $erroractionpreference = "SilentlyContinue"
               $socket.Connect($ServerToCheck, $Port)
               if (!$socket.Connected) {
                    $PortAvailable = $Port
                    $erroractionpreference = "Continue"
                    $error.clear()
                    $socket.Close()
                    break
               } else {
                    $socket.Disconnect()
               }
          }
     }
     if ($PortAvailable -ne 0) { break }
}
write-host "     Port $PortAvailable appears to be available" -f green
return $PortAvailable

Why exclude ports?

Ah, you noticed that I included an optional parameter to include a delimited list of ports to exclude. One issue I discovered along the way is that whn you create an endpoint, it doesn't actually start using the port until the mirroring session has been started. So if my principal and mirror are on SQL instances on the same machine, I will get the same port back both times since the port still is not in use yet.

The mirroring setup script does this behind the scenes, but for the purpose of this script, if I want to ensure I get a different port each time, I remind it what ports it has already given me. For example, if it returns port 5022 the first time, and I use it to create the endpoint on the principal, I now want a different port so I can create an endpoint on the mirror. So I pass "5022" as the ports to exclude, and it does not even test this port for availability.

What's with the weird array at the beginning?

Since I wrote this script specifically for use with database mirroring, I wanted the function to search for preferred port numbers first before just doing a straight port scan. This ensures that the common ports for mirroring will be checked first before the uncommon ports. So basically, it is an array of arrays. I loop through the array to get the arrays inside the arrays and then loop through these arrays one at time. And to make things more confusing, I used the ".." range indicator rather than listing the individual range members in the classic comma delimited style.

Coming Soon: Pro SQL Server 2008 Mirroring

Pro SQL Server 2008 Mirroring will be the first book dedicated to the subject of database mirroring. Most books only gloss over the topic of database mirroring. Even books that claim to be dedicated to failover clustering and database mirroring only have a few chapters on mirroring. My book will be more comprehensive and delve deeper than any other discussion on the topic so far.

Pro SQL Server 2008 Mirroring has just recently been added to Amazon.com's bookshelves. You should start seeing it for presale on other internet booksellers in the near future and should hit the stores in September.

Since this is my first published book, it's been really interesting for me to see it start showing up on internet booksellers in other countries. So far, Sweden leads the pack with three different internet booksellers showing my book. I even learned something new by searching for my book. I learned about the island country of Niue, a self-governing annex of New Zealand. I've wanted to visit New Zealand for a long time, and if I ever make it, I definitely want to include Niue on the itinerary.

For more information, please see the publisher's website or Amazon.com. If you see any other booksellers selling my book, please drop me a message or post it on here!!

Details, details, details

Title: Pro SQL Server 2008 Mirroring
Author: Robert L Davis
ISBN10: 1-4302-2423-1
ISBN13: 978-1-4302-2423-5
Estimated page count: 400 pages
Publish Date: September 1, 2009
Publisher: Apress Inc.

Tentative Chapter List:
1. High Availability Options
2. Planning and Best Practices
3. Setting it Up: Step-by-step
4. Automating Setup and Processes
5. Maintenance and Troubleshooting: Simplifying and Automating
6. Monitoring and Alerting
7. Building the Missing Components
8. Upgrading Mirrored Databases to SQL Server 2008
9. New Features in SQL Server 2008

Do You Support the Ancient Ones?

Are you still supporting SQL Server 2000? How about 7.0 or 6.5?

Officially, Microsoft support has ended for these products. I know plenty of companies that are still using SQL Server 2000. Even within Microsoft SQL 2000 is still in use. Mostly it's old internal applications that no longer have development budgets and have entered the "sustained engineering" phase. In other words, no one is willing to pay for the labor to upgrade it, and no one is willing to pull the plug. Or maybe there's just one person still using it, but that one person has enough stroke to keep the application on life support.

I read a good article called Ordering Tables To Preserve Referential Integrity posted on SQL Server Central recently that included a script for calculating a logical order for tables based on defined referential keys. Someone requested a version of the script that would work on SQL Server 2000, and the author, R Glen Cooper, gladly posted an update for SQL 2000.

That got me thinking that I think it is time to stop officially supporting SQL Server 2000. I have frequently in the past posted versions of scripts that are compliant with SQL Server 2000 T-SQL. More recently, most of my writings have been on features that do not exist in SQL Server 2000 (such as database mirroring), and thusly SQL 2000 never entered the picture. Going forward, I will no longer be posting scripts specifically written to work in SQL Server 2000. I will gladly help out if someone needs a script for SQL 2000, but it will be by request only.

Chime In

I'd love to hear if other people are still supporting SQL Server 2000 and to what degree. Please post any feelings you may have on the subject.

Database Snapshots Can Take a Long Time to Create

Believe it, it's true!! One of the big selling point for database snapshots is that creating them is almost instantaneous. Since a database snapshot is created as an empty shell, it should be and almost always is a very quick process. What they don't tell you is long running active transactions can extensively delay the creation of the snapshot file.

What's causing my snapshot creation to freeze for long periods of time?

When you create a database snapshot, the snapshot contains all open transactions at the time. As part of the initialization process, it calls the recovery process on the snapshot. Part of the recovery process is to roll back all active transactions in the snapshot (they do not roll back in the actual database). If there are long running transactions -- such as a large index rebuild -- running at the time of the snapshot creation, the rollback can take a really long time.

If it appears that your snapshot creation is frozen, you can check sys.dm_exec_requests, and you should see rollbacks in progress in the database snapshot.

According to Books Online:

Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected).

Question: Why is my mirror database in a restoring state?

This question was asked on a technical discussion group. This is a follow up to an earlier discussion regarding troubleshooting database mirroring setup. He was able to get database mirroring set up with the help of one of my colleagues, Balmukund Lakhani. My reply follows.

Hi,

I am still working on DB mirroring with principal and mirror. There will be no Witness. This is what I did and my current issue:

1. I have two SQL servers with a database called SCMAX on them. This is as simple as File ⇨ New database. There is no application involved here.
2. I did set up mirroring with Balmukund’s help. He was great. There is no witness. It is asynchronous mode.

My answer:

The mirroring database will always be in a restoring state until it is brought online. That is the natural state of it. To see what is in it, you can create a database snapshot of the mirror database if you are using Enterprise Edition or Developer Edition (if not in production). Remember that a snapshot is a point in time, so you will only see things that already exist. If you add new things, you’ll need to create a new database snapshot.

To bring the mirror online manually after dropping mirroring, issue the following command (you can’t do it while it is still mirrored):

Restore Database <Database Name> With Recovery;

Question: How do we handle transaction log maintenance for a mirrored database?

This question was asked on a technical discussion group. My reply follows.

Hi,

My customer would like to know how to handle the txn log maintenance with DB Mirroring? They normally truncate the txn log after a full backup without DB Mirroring. But with DB mirroring, can they truncate the txn log? What happen at the mirror DB if the txn log is truncated? Do they need a different maintenance plan at the mirror DB?

My answer:

They shouldn’t be routinely truncating the transaction log whether they are using database mirroring or not. They need to perform regular log backups and that should be it. The more frequent, the better. At least every half hour. If the system is very active, then every 15 minutes or every 5 minutes.

Don’t use the database maintenance plan for the log backups because it will try to backup up the database if it is the mirror and will fail because the database is not online. You want to create a job that uses T-SQL to query for databases in the online state and only backup those databases that are online.

Reply back:

Hi Robert,

My customer is performing a daily fully backup and log backup every 2 hours, after the nightly fully backup, they will truncate the log to maintain the size of the log file. If they don’t clean up the log, the log file will growth extremely fast and eat up the disk space.

What is the recommendation to maintain the size of the log file if the suggestion is not to routinely truncate the log? How about the size of the log file growth at the Mirror DB? Will that be the same size as of the Primary DB?

My answer:

Truncating the log file repeatedly is bad. It degrades performance of the log file over time as it causes SQL to create lots of virtual log files as the size grows back up. They should have never started this practice. I’m not saying that there is never a legitimate reason to truncate the log file, but using it instead of log backups is a very bad practice.

They key to maintaining the size of the log file is frequent log backups. Every 30 minutes would be the lowest frequency I would use. If their system is very active, then they should run log backups every 15 minutes or every 5 minutes.

Reply back:

Hi Robert,

When a log backup is applied to the production site, what would be applied to the txn log file the Mirroring DB?

My answer:

When you backup the log on the principal, the virtual log files (individual units within the log file) are marked as re-writable. The same VLF’s are marked as re-writable in the mirror log file as well. The VLF status is mirrored on the database.

Question: What would be the best practice to configure Failover Clustering and Database Mirroring?

This question is frequently asked in one of the technical discussion groups I frequent. My reply follows.

I have a customer that wants to combine Failover Clustering with Database Mirroring in which the last one will be for disaster recovery. In order that the mirroring will act as primary only if the cluster fails, does the mirroring should be configured as manual? How do we configure mirroring in automatic when both nodes in the cluster fail?

My answer:

If you want automatic failover in case all clustering nodes fail, then you should definitely not set it to manual failover only. The best practice is to set the partner timeout period for mirroring to a number large enough to not be triggered by a cluster failover. The default timeout is 10 seconds.

The following command would set it to a timeout of 30 seconds:

Alter Database <Database Name> Set Partner Timeout 30;

Note: this can be set on the principal server only.

Question: Will the Mirror automatically fail back to the original Principal when it comes back online?

This question doesn't usually come up until someone has experienced an unplanned failover and something that they forgot about has been failing as a result. Hopefully this question will start coming up while planning for database mirroring.

My answer:

Automatic failover only occurs as a result of the current Principal being offline. Database Mirroring does not have any mechanisms built in to set one server as the default active server. When the Mirror partner takes over as the active Principal partner, it will continue to serve as Principal until a failure triggers another automatic failover or a manual failover is performed.

You can set up this functionality on your own, however. It's a simple process. Create a procedure to check the state and role of your mirror partners and manually fail them back to the original Principal if it is connected and in a synchronized state. Then set up a job to run this procedure every minute. Once the original Principal is back online and fully synchronized, the job will fail the server back within one minute.

The procedure:

Create Procedure dbo.dbm_FailoverMirrorToOriginalPrincipal
     @DBName sysname
As
Declare @SQL nvarchar(200)

Set NoCount On;

/*
     If database is in the principal role and is in a synchronized state
     then fail database back to original principal
*/
If Exists (Select 1 From sys.database_mirroring
          Where database_id = db_id(@DBName)
          And mirroring_role = 1 -- Principal partner
          And mirroring_state = 4) -- Synchronized
  Begin
     Set @SQL = 'Alter Database ' + quotename(@DBName) + ' Set Partner Failover;'

     Exec sp_executesql @SQL;
  End

Set NoCount Off;

Please Note:

You may wonder why I use dynamic SQL for this procedure rather than just hard coding the database name. If you hard code the database name and try to create the procedure while the database is in the mirror role, it will fail because it will be referencing a database that is offline.

How do I ... Determine Database Growth If I Am Not Tracking It?

If your database has grown considerably and you're not sure when or why it happened, you'll be hard pressed to figure out the answer unless you're tracking changes in database size. Luckily, if you are performing regular full backups, you can get this information from the SQL logs.

When you create a full backup, SQL creates an entry in the SQL Log that looks like the following:

Database backed up. Database: PSP, creation date(time): 2008/12/22(18:25:10), pages dumped: 171, first LSN: 39:41:37, last LSN: 39:59:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'c:\Bak\PSP.bak'}). This is an informational message only. No user action is required.

So we can look for these statements in the SQL logs, parse out the pages dumped, and calculate the size of the used portion of the database.

The Script:

Declare @OldestLog datetime,
     @FirstLog int,
     @SearchText nvarchar(50),
     @DBName sysname
Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key,
                              LogDate datetime null,
                              ProcessInfo nvarchar(100) null,
                              LogText nvarchar(max) null)
Declare @EnumLogs Table (ArchiveNum int not null primary key,
                              ArcDate Datetime not null,
                              LogFileSize bigint not null)

Set nocount On
Set @OldestLog = '2/1/2009'
Set @SearchText = N'pages dumped: '
Set @DBName = '<database name>'

Insert Into @EnumLogs
Exec master..xp_enumerrorlogs

Select Top 1 @FirstLog = ArchiveNum
From @EnumLogs
Where ArcDate < @OldestLog
Order By ArcDate DESC

If @FirstLog Is Null
  Begin
     Select Top 1 @FirstLog = ArchiveNum
     From @EnumLogs
     Order By ArchiveNum DESC
  End

While @FirstLog >= 0
  Begin
     Insert Into @ErrorLog (LogDate, ProcessInfo, LogText)
     Exec master..xp_readerrorlog @FirstLog
     Set @FirstLog = @FirstLog - 1
  End

Select Convert(varchar, LogDate, 101) As BUPDate,
Cast(Cast((Cast(RTrim(LTrim(SubString(LogText, CharIndex(@SearchText, LogText) + Len(@SearchText), CharIndex(',', LogText, CharIndex(@SearchText, LogText)) - CharIndex(@SearchText, LogText) - Len(@SearchText)))) as BigInt) * 8.0)/1024 As Decimal(9, 2)) As varchar) + ' MB' As BUPSize
From @ErrorLog
Where CharIndex('Backup', ProcessInfo) > 0
And CharIndex('Database backed up. Database: ' + @DBName, LogText) > 0
Order By LogDate Asc

Set nocount Off