database mirroring/replication, SQL Server 2005

Question

Hi there,

I have two database servers running SQL Server 2005 Enterprise that i want to make one of them as mirror database server.

What i need is, create exact copy a database from primary server on mirror server, so when the primary server was down, we could switch database IP on application to use mirror server.

I have examined "mirror" feature on SQL Server 2005, and based on this article:
http://aspalliance.com/1388_Database_Mirroring_in_Microsoft_SQL_Server_2005.all

The mirror database cannot be accessed directly; however snapshots of the mirror database can be taken for read only purposes. (Prerequisites no. 4)

So how it can be useful when i can't access it when primary server was down?

I've been thinking about creating a regular backup on primary server and restore it on mirror server on hourly basis, but that's quite inefficient (slow) especially if i want exact copy (since hundreds data's are added once in minute).

Any other suggestion?

EDIT:
Maybe what i mean was a replication things... not a mirror (thanks JP for commenting)

Answer 1

If I understand your question correctly, you shouldn't have to do that. There are several role switching forms you can use to have your mirror take over as primary. You don't change the IP address at the application level, the cluster itself has a virtual IP address that allows access to the data at any given time (given a reasonable amount of time for the switch over to the mirror from a primary failure). The mirror stays in synch by itself. :) There are good articles here and here on clustering.

Edit: Okay, based on the comments, check out the various options for replication.

Answer 2

They are referring to the fact that you can't perform queries on the mirrored copy, but you can get around that limitation by creating a snapshot of the mirrored database. This is often done to create a read-only database copy for reporting uses. You would have full access of the mirror if the primary were to fail, but it will not failover automatically.

Log shipping is another option, which allows you to query (read-only) the standby database without having to create a snapshot.

Answer 3

Your confusion is common - there's a lot of ways to do disaster recovery planning with SQL Server. I've recorded a 10-minute video tutorial of SQL Server disaster recovery options including log shipping, mirroring, replication and more. If you like that one, we've got a longer one at Quest called Disaster Recovery Techniques but that one requires registration.

Instead of investigating a specific technology here, what you might want to do is tell us what your needs are, and then we can help you find out what option is right for you. The videos will give you an idea of what kinds of information you need to know before selecting a particular solution.

Answer 4

When using only two SQL Servers, you need to do the fail-over manually. The 'backup' database will be usable after you do two things;

1. Disable mirroring on it
2. Restore the database with RECOVERY (but without a backup file, this will make the database usable).

Therefore mirroring in this manner does make scense, however it is hard to maintain;

Moving back from the backup database to the primary is a 'pain' as you have to set-up the complete mirroring again using a backup of the redundant server. This is needed to get the primary back up to speed.

My recommendation would be to get a thrid SQL Server into the picture that can act as a witness. The witness will monitor the status of the mirroring databases. Your bonus; you will get automatic failover, and will not have the fail-over (and after fail-over) issues.

If I remeber correct, the witness server can be running SQL Express so no need for the Enterprise version on all three - just the two where the actual mirroring will take place.

Let me know if you need Transact SQL for the commands to fail-over and 'anti-fail-over' in a two server scenario, and I can dig them up.