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:

Error when importing RegServer list from SQL 2005 to SQL 2008

I discovered the already known bug in SQL 2008 that you can't import a SQL 2005 server list (exported as a regsrvr file). For someone like me who deals with a large numger of SQL Servers, this is a major issue. Fortunately, Buck Woody has provided a simple solution on his Blog: Importing SQL Server 2005 Registered Servers to SQL Server 2008. This saved me a great deal of time and, more importantly, a great deal of annoyance!!

Thanks Buck!!

My Bug Report

I reloaded my laptop and installed a new installation of SQL 2008 RTM (Dev. Ed.). Previous setup was SQL 2005 SP2. Prior to wiping hard drive, I exported the registered servers to regsrvr files (1 for database engine, 1 for AS, and 1 for IS). I can’t import SQL 2005 regsrvr files into SQL 2008. For me, this is a big hindrance!! Is there a workaround?

When I try to import these files into the SQL 2008 SSMS, I get the following error for all 3 regsrvr files:

Cannot import from C:\Users\v-rodav\Desktop\SQLIS.regsrvr.
The operation 'Import' failed. (Microsoft.SqlServer.Management.RegisteredServers)
Serialization output is invalid. (Microsoft.SqlServer.Management.Sdk.Sfc)
'None' is an invalid XmlNodeType. Line 17, position 10. (System.Xml)

SQL 2008 SSMS Version info:

Microsoft SQL Server Management Studio      10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Analysis Services Client Tools      2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC)      6.0.6001.18000 (longhorn_rtm.080118-1840)
Microsoft MSXML      3.0 4.0 5.0 6.0
Microsoft Internet Explorer      7.0.6001.18000
Microsoft .NET Framework      2.0.50727.3053
Operating System      6.0.6001

Automatic Failover - an incomplete solution

Automatic failover with Database Mirroring is great, but what about all of the other things that you need to happen when the database fails over? There is no built-in support in Database Mirroring for anything external to the database. This is where automatic activation in the Service Broker comes to the rescue.

Service Broker and Automatic Activation

The Service Broker can be used to automatically activate a stored procedure when certain events occur. The stored procedure would need to process the messages in the Service Broker's queue and respond accordingly. Setting this up requires the following database objects stored externally to the mirrored database:

1. Stored procedure to process the messages in the queue
2. Service Broker queue
3. Service Broker service
4. A route for the Service Broker service
5. An Event Notification for the DATABASE_MIRRORING_STATE_CHANGE event

Automatic activation stored procedure

Your stored procedure will need to read from the Service Broker queue and process the messages. The event will be sent as an XML message and will need to be parsed to determine how the database mirroring state has changed. For the following example, the Service Broker queue that I am going to create will be named DBMirrorQueue. This sample code reads the first message in the queue and parses the message. Based ont he parsed message, you will need to decide whether you should take any action.

Declare @Message XML,
    @DBName sysname,
    @MirrorStateChange int,
    @ServerName sysname,
    @PostTime datetime,
    @SPID int,
    @TextData nvarchar(500),
    @DatabaseID int,
    @TransactionsID int,
    @StartTime datetime

/* Receive first unread message in service broker queue */
Receive Top (1) @Message = Cast(message_body as XML)
From DBMirrorQueue;

/* Parse type of state change and database affected */
Set @MirrorStateChange = @Message.value('(/EVENT_INSTANCE/State)[1]', 'int'); -- 7 or 8 = database failed over, 11 = synchronizing, 1 or 2 = synchronized
Set @DBName = @Message.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');
Set @ServerName = @Message.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname');
Set @PostTime = @Message.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');
Set @SPID = @Message.value('(/EVENT_INSTANCE/SPID)[1]', 'int');
Set @TextData = @Message.value('(/EVENT_INSTANCE/TextData)[1]', 'nvarchar(500)');
Set @DatabaseID = @Message.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'int');
Set @TransactionsID = @Message.value('(/EVENT_INSTANCE/TransactionsID)[1]', 'int');
Set @StartTime = @Message.value('(/EVENT_INSTANCE/StartTime)[1]', 'datetime');

Service Broker Queue

Creating a Service Broker queue named DBMirrorQueue:

/* Create Queue if not exists */
If Not Exists (Select 1 From sys.service_queues Where name = 'DBMirrorQueue')
  Begin
    Create Queue DBMirrorQueue
        With Status = On,
        Retention = Off,
        Activation (Procedure_Name = dbo.DBA_MirroringStateChanged,
            Max_Queue_Readers = 1,
            Execute As Self);
  End

Service Broker Service

Creating a Service Broker Service named DBMirrorService on the BMMirrorQueue using the built-in PostEventNotification service type:

/* Create Service if not exists */
If Not Exists (Select 1 From sys.services Where name = 'DBMirrorService')
  Begin
    Create Service DBMirrorService
        On Queue DBMirrorQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
  End

Service Broker Route

Creating a Service Broker route named DBMirrorRoute for the DBMirrorService service:

/* Create Route if not exists */
If Not Exists (Select 1 From sys.routes Where name = 'DBMirrorRoute')
  Begin
    Create Route DBMirrorRoute
        With Service_Name = 'DBMirrorService',
        Address = 'Local';
  End

Event Notification

DATABASE_MIRRORING_STATE_CHANGE is not one of the events that can be automatically trapped. The event is available as a manually created Event Notification.

Creating an event notification named DBMirrorStateChange to raise a notification for the DATABASE_MIRRORING_STATE_CHANGE event:

/* Create Event Notification if not exists */
If Not Exists (Select 1 From sys.server_event_notifications Where name = 'DBMirrorStateChange')
  Begin
    Create Event Notification DBMirrorStateChange
        On Server
        For DATABASE_MIRRORING_STATE_CHANGE
        To Service 'DBMirrorService', 'current database';
  End

1. Component Services -> Computers -> My Computer -> DCOM Config -> MSDTSServer
2. Right click on MSDTSServer
3. Click properties
4. Click on the Security tab
5. Select Customize and add the users/groups to the Launch/Activation and Access tabs accordingly
6. Add the user to the local Distributed COM Users group
7. Restart the SSIS service

Error Logs Part II -- Enumerating the error logs

In my first post on SQL Server Error Logs, I briefly mentioned using xp_enumerrorlogs to list the archived error logs. Here I want to demonstrate how to use the procedure to find and output all error logs since a specific date.

xp_enumerrorlogs

This procedure returns 3 columns: Archive #, Date, and Log File Size (Byte). Archive numbering is 0 based with 0 being the currently active log file. As a log file is archived, the number increases; 0 becomes 1, 1 becomes 2, and so on. The output is ordered alphabetically by the Archive #.

 Finding which archive to start with is easy. You just find the newest file that is older than the date for which you are searching. The one catch here, is that if your date is beyond the range of the archives, your query will not find an archive. As a sanity check, if no archive is found beyond the range of my search date, I simply choose the oldest file available.

From there, it's a simple looping process to read in each file starting with the highest archive number and continuing down to 0, the currently active number.

The Script: ErrorLogsSinceDate

Declare @OldestLog datetime,
    @FirstLog int
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 @OldestLog = '12/31/2007'

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 *
From @ErrorLog
Order By LogID Desc

"Select *" is bad. Everyone knows it, but everyone still uses it. I use it. Most of the time it is fairly innocuous. No harm, no foul, right?

Updating your procedures to not use Select * can be a daunting task. Here's a tip to help you work smarter, not harder. Do less work that is more meaningful.

Shhhh!! Be vewy, vewy quiet. I'm hunting Select *!!

Some of you younger DBA’s out there may not know who Neil Sedaka is or remember his poignant words "Breaking up is hard to do" in the song by the same title. I'm sure we've all gone through a break up at some point. But breaking up isn't necessarily a bad thing, especially when it comes to large or complex queries.

Breaking up can be a good thing. Or so that is the point I try to make in my article Breaking Up is Easy to Do

No, I'm not going to keep making corny references to war and being a soldier. But in many ways, we dba's are soldiers in a war against poor performance. Often it can be a struggle to get the information and resources you need, but that doesn't lessen the expectation that we can deliver speedy results.

I hope this first article will help a few people resolve issues a little bit quicker by helping them search their error logs.

I intend to follow up this article with a more advaned look at the error logs and how to use it to search for hidden problems before they become public.