SQLSaturday 26 Sessions: 10/3 in Redmond, WA

Thanks to everyone that attended my sessions at SQLSaturday 26 in Redmond, WA on 10/3!!

This was my first SQLSaturday event. I was granted the opportunity to be a last minute replacement speaker and gave two presentations. This was my first time speaking in front of a large audience at an event. It was a thoroughly enjoyable experience, and I hope to speak at future events as well.

As promised in my presentations, I have uploaded my materials and am sharing them here through my blog.

Session 1:

Using SQL Trace/Profiler Effectively
This session covered how to use SQL Trace/SQL Profiler effectively. Topics included converting trace templates into T-SQL scripts, running server side traces (and why you should do so), reading trace files via T-SQL.

Demos included creating a trace template in SQL Profiler, converting it into a SQL script, and running a server-side trace via the SQL script. We then stop the trace and read the contents using the trace stored procedures and functions.

Also covered was how to set up a SQL job that detects high CPU utilization and starts and stops a custom SQL trace when CPU utilization crosses a predefined threshold.

The files from this sessions can be downloaded here.

Session 2:

Database Mirroring: Maximizing Availability Through Automation
Database Mirroring is often thought of as a “set it and forget it” technology. But what happens if there are problems and the one person on your team that knows mirroring inside and out is not there? You can have a prolonged outage or you can be prepared ahead of time and set up automation procedures for all of your database mirroring needs.

This session covered setting up automation scripts for managing database mirroring failovers in a controlled fashion. A demo was performed comparing mirroring failovers performed via the GUI (graphical user interface) vs. ad hoc T-SQL statements vs. a well thought out, planned, and praticed automation script.

Also discussed was an automation script to automatically fail a mirroring session back to the original principal as soon as it was back online and the databases are synchronized in order to protect your ability to cover both servers with a single SQL Server license.

The files from this sessions can be downloaded here.

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.

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

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.

Question: Why does Database Mirroring Monitor report that my mirror is disconnected?

This question was sent to a discussion list via email. My reply follows.

Sometimes, I have seen a behavior where the Principal was unable to connect to the Mirror. What is the reason for this behavior? Could it be network? I could not find any information that explain this behavior.

Any help here would be appreciated.

My answer:

This does not mean that the principal is disconnected from the mirror.

This simply means that the Database Mirroring Monitor has not connected to the mirror yet. This is not an unusual behavior to see when first looking at a mirrored database in the monitor. The monitor connects to the principal and then to the mirror. Usually, this will clear up within a few seconds on its own as it eventually makes the connection.

If it does not make the connection on its own within a short time, then the problem exists between the monitor and the mirror.

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

This question was sent to me via email. My reply follows.

Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future. I have not tried this, need mirroring setup and was hoping to get a jump on the future upgrade.

My answer:

Yes, a SQL 2008 machine can act as witness for 2 SQL 2005 machines. When upgrading the systems from SQL Server 2005 to SQL Server 2008, the witness can be upgraded at any time (first, last, or in the middle).

 How many mirrrored databases can I have on a server?