SQL Server 2005 mirror database still in (restoring...) state

Question

I just successfully mirrored the same database on two of our servers. The principal database state in Management Studio is: (databasename, Principal, Syncronized). The mirror database state is (databasename, Mirror / Restoring...). What is our next step?

Answer 1

Nothing. This is the normal state of the mirror. As long as the principal says "Synchronized", everything's in sync between the servers and the mirror is working correctly. See this Technet article for more details: http://technet.microsoft.com/en-us/library/cc917681.aspx#ELAA.

The only thing I would suggest is to test the failover, but only if the primary database isn't a live production database.

Answer 2

That is what should happen. The mirror database will always be in a restoring state, as it is always taking in new transactions. When you fail over the mirror the database which is currently active will show as restoring and the current mirror will become active.

Answer 3

I would suggest you test a fail over now. You need to ensure that the rest of the ecosystem around the database is correctly configured for fail over. Here are the most common things to checks:

• the dbo SID maps to a valid login
• users are not orphaned after fail over
• database master key can be opened after fail over (both instances have added their service master key encryption to the database master key)
• necessary agent jobs are configured on the new principal host and they can log in properly
• all applications use connection strings that include the mirroring partner and they connect to the new principal and resume
• if the database is a replication publisher, the replication agents are configured to follow the failover to its new principal
• there are no cross database queries that rely on the availability of another database

Check out the relevant topics in Managing Metadata When Making a Database Available on Another Server Instance

Answer 4

Yup, just test failover and failback, and make sure you're in the mirroring operating mode you want. There can be a big trade off between fault tolerance and performance with transactional safety full versus transactional safety off. You also might want to look into updating your apps' connection strings to be mirroring-aware if you want them to reconnect automatically after a failover.