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.

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>

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