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
SELECT EP.name, SP.STATE, 
   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) 
      AS GRANTOR, SP.TYPE AS PERMISSION,
   CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id)) 
      AS GRANTEE 
   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:

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s