January 6, 2009 at 12:39 am
Hi all
Another interesting one!
I would like to now if it is possible to replicate from a mirror database. Consider the scenario:
The primary database is mission-critical. It is morrored to a standby database (the mirror database). However, I would like to offload reporting as well. Mirrored databases run in a mode that prevents (even read) access. Therefore, I would like to know if it is possible to then replicate the mirrored database to a third database (the reporting database). I know you can replicate from the primary database, but I don't want to do this since it is mission critical and will therefore suffer the performance hit of both replication and mirroring. I would prefer any performance hits to be taken by the mirrored database.
Any ideas?
Regards,
Tony
January 6, 2009 at 2:54 am
Database mirroring can be used in conjunction with replication to provide availability for the publication database.
http://msdn.microsoft.com/en-us/library/ms151799.aspx
James Howard
January 6, 2009 at 3:41 am
Hi James
Thanks for the prompt response.
Yes, I had a look at that article before in my investigation, but it is not quite what I need. The context I am looking at is not when failover occurs. The context is this:
A normal (principle) database can be replicated to a secondary (replicated) database - no problem.
But what if the normal database is actually a mirrored database itself? Can this mirrored database then be the source for replication to another database?
It's best to consider the above scenario in the context of excluding anything to do with high availability or failover.
Regards,
Tony.
January 6, 2009 at 5:49 am
You can't use a mirror as a publisher, because the mirror database will be in a recovering state and hence unreadable. You can use the principal database as a publisher without problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2009 at 6:15 am
Hi Gail
Thanks for the reply - it answers the question perfectly!
My other post regards managing Views from a Database Snapshot of a mirror. Since Mirror databases cannot replicate (since it's by definition in the recovering state) and offload to a reporting database that way, we are thinking of using a Database Snapshot for reporting. However, each time we take a snaphot we will have to connect to the new database. How do we make Views persistent across Database Snapshots elegantly? I presume the easy thing to do would be to just create the views each time from a script, but is there a more elegant solution - i.e. having the Database Snapshot delegate to the mirror database for Views, Procedures and Functions?
Regards,
Tony.
January 6, 2009 at 6:50 am
tony.harris (1/6/2009)
- i.e. having the Database Snapshot delegate to the mirror database for Views, Procedures and Functions?
That's the only way you can do it. Database snapshots are read-only and hence cannot be modified in any way, including adding views.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply