Geek Answers Handbook

Database Mirroring / Replication, SQL Server 2005

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

I need; to create an accurate database of copies from the primary server to the mirror server, so when the main server was turned off, we could switch the database IP address to the application to use the mirror server.

I reviewed the mirror feature on SQL Server 2005 and based on this article:
http://aspalliance.com/1388_Database_Mirroring_in_Microsoft_SQL_Server_2005.all

It is not possible to access the mirror database directly; however, snapshots of the mirrored database can be read-only. (Background No. 4)

So, how can this be useful when I cannot access it when the main server was unavailable?

I thought about creating a regular backup on the primary server and restoring it on the mirror server hourly, but this is pretty inefficient (slow), especially if I want an exact copy (as hundreds of data are added once a minute).

Any other suggestion?

EDIT:
Maybe I mean this is replication, not a mirror (thanks JP for the comment)

+4
source share
4 answers

They refer to the fact that you cannot execute queries on a mirrored copy, but you can get around this limitation by creating a snapshot of the mirrored database. This is often done to create a read-only copy of the database for use in reports. You would have full access to the mirror if the main one had a failure, but it will not automatically switch.

Sending a log is another option that allows you to query (read-only) the backup database without the need for a snapshot.

+2
source

Your confusion is common - there are many ways to plan for disaster recovery with SQL Server. I recorded a 10-minute video tutorial on SQL Server disaster recovery options , including log shipping, mirroring, replication, and more. If you like this one, we have one more in Quest called Disaster Recovery Methods , but registration is required for this.

Instead of researching a specific technology here, what you might want to do is tell us what your needs are, and then we can help you figure out which option is right for you. The video will give you an idea of ​​what information you need to know before choosing a specific solution.

+1
source

If you are using only two SQL servers, you need to fail manually. The backup database will be used after two actions:

  • Disable mirroring on it
  • Restore the database using RECOVERY (but without a backup file this will make the database usable).

Therefore, mirroring in this way makes scense , however, it is difficult to maintain;

Returning from the backup database to the main one is a pain, because you need to configure full mirroring again with the backup of the redundant server. This is necessary to get the basic backup up to speed.

My recommendation would be to get a brief SQL Server in the picture, which can act as a witness. The witness will monitor the status of the mirroring databases. Your bonus; You will receive an automatic transfer to another resource and will not have a problem with the failure (and after the failure).

If I remember correctly, the witness server can run SQL Express, so there is no need for an Enterprise version for all three — only those where the actual mirroring will take place.

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

+1
source

More articles:


All Articles