Troubleshooting SQL Server 2005 Database Mirroring

We have had very good luck with SQL Server 2005 Database Mirroring over the last 18 months. It is much easier to configure and maintain than database clustering (which we also use), and it provides some key advantages (such as extremely fast fail-over) that are extremely useful for our environment. Having said that, here are a few observations and troubleshooting tips that I have learned over that time.

First, never rely on the database status indicator in SQL Server Management Studio (SSMS). When you have database mirroring enabled, you will normally see (Principal, Synchronized) as a status next to each mirrored database in SSMS. Unfortunately, if there is a problem with database mirroring (which is pretty rare), this status is not automatically refreshed to show the changed status. Instead, I recommend running this query periodically (wrapped in a stored procedure, perhaps called by a web page or desktop client application). It will alert you to problems with database mirroring, replication, etc.

-- Find the problem with the transaction log
    SELECT name, database_id, log_reuse_wait_desc 
    FROM sys.databases

Second, if you do detect a problem, the first place to look is the SQL Error Log and the Windows Application Event Log. After looking there, these queries can help troubleshoot connectivity problems:

-- Check the endpoints
SELECT name, type_desc, port 
FROM sys.tcp_endpoints;

-- Check the role and whether the endpoint is started
SELECT name, role, role_desc, state_desc 
FROM sys.database_mirroring_endpoints;

-- Check permissions for login
   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) 
   FROM sys.server_permissions SP , sys.endpoints EP
   WHERE SP.major_id = EP.endpoint_id
   ORDER BY Permission,grantor, grantee; 

It is also a good idea to confirm that you can ping and TELNET from the mirror to the principal and vice/versa.

Third, you need to be more aware of when you do common database maintenance tasks (like index rebuilds, index reorganizations, etc.)  Remember, with database mirroring, every action that goes into the transaction log on the Principal has to be sent over the network to the Mirror side, and then replayed there. This means that you may overload your network or the I/O subsystem on the Mirror during maintenance, so be careful. The mirror database does not get the benefit of the SQL Server Lazy Writer, so you will see more I/O on the mirror than you expect. SQL Server 2008 will compress the transaction log data (log stream compression) that is sent over the network (at the cost of some CPU), which will help with the network portion of this issue.

This portion of Books Online has more troubleshooting tips.


Technorati Tags:

This entry was posted in SQL Server 2005. Bookmark the permalink.

35 Responses to Troubleshooting SQL Server 2005 Database Mirroring

  1. Unknown says: hp nc6120 battery dell inspiron 1100 series battery dell inspiron 1150 battery dell inspiron 5150 battery dell inspiron 5160 battery dell inspiron 5100 battery dell latitude 131l battery dell vostro 1000 battery dell gd761 battery dell nt379 battery dell latitude d531 battery dell precision m65 battery dell vostro 1700 battery gateway 8msb battery hp pb992a battery hp dv2100 battery hp dv2200 battery hp hstnn-c29c battery hp nc8000 battery dell inspiron b120 battery dell inspiron b130 battery gateway 12msbg battery hp 7400 battery dell inspiron b120 battery toshiba pa3356u battery hp 2510p battery hp f3172a battery dell inspiron 2200 battery gateway squ-414 battery hp hstnn-lb33 battery mitac bp-8089 battery toshiba satellite m55 battery toshiba satellite m115 battery toshiba qosmio f25 battery toshiba satellite m60 battery toshiba satellite p200 battery toshiba pa3536u-1brs battery hp hstnn-db17 battery

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s