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.

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
}

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.

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

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 Do I Configure SSIS to Work With a Named Instance? 

By default, SSIS (SQL Server Integration Services) uses the msdb database of the default instance on the server for storing packages. SSIS can still manage packages stored as files in the file system without any changes to the current setup. To be able to use the msdb database of a named instance, you have to manually edit the SSIS configuration file. Here are the steps for that:

1. Shut down the IS service
2. Find the configuration file: <install location>\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml
3. Open the configuration file in a text editor such as Notepad
4. To configure a named instance instead of the default instance, change the existing ServerName entry to ServerName\InstanceName under the Folder node of type SQLServerFolder
5. To configure a named instance in addition to the default instance, add a new Folder node of type SQLServerFolder under the TopLevelFolders node
6. Save and close the configuration file
7. (Re)start the IS service

Before change:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
 <TopLevelFolders>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>.</ServerName>
  </Folder>
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\Packages</StorePath>
  </Folder>
 </TopLevelFolders> 
</DtsServiceConfiguration>

After change to a named instance:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
 <TopLevelFolders>
  <Folder xsi:type="SqlServerFolder">
   <Name>MSDB</Name>
   <ServerName>MyServer\Myinstance01</ServerName>
  </Folder>
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\Packages</StorePath>
  </Folder>
 </TopLevelFolders> 
</DtsServiceConfiguration>

 How many mirrrored databases can I have on a server?

The following was sent to me by my friend and colleague Dave Miller:

Dave's Email:

Wanted to pass along something I hadn't used before and found useful to easily get rid of duplicates in a set of data. The functionality has existed in the SQL language and was supported in SQL Server 2005. This uses Common Table Expressions (CTE) and the ROW_NUMBER() function.

The PARTITION BY portion of the statment specifies when to reset the row number, in my example I had: