October 28, 2009 at 6:06 am
We have a client that currently has a large busy database that they back up using database mirroring. As part of a reporting requirement we need to use some of the data stored in the database to run reports against but we cannot access the data directly. The client also want their data to be as up to date as possible.
Is it possible for us to run transaction replication on that server to get our data while it is running database mirroring at the same time? We were planning to use a SQL 2008 database to store this data on. If this is possible would there be any issue with the SQL 2008 subscriber taking data off a SQL 2005 publisher?
October 28, 2009 at 7:32 am
Which one is "that" server?
You can run replication on the primary server of the mirror. If it's really busy I might put the distributor on another server.
you cannot run replication on the mirror database. It is in restoring mode constantly, so you can't access it. If you have enterprise edition, you could create snapshots, but they will not be up to date.
October 28, 2009 at 7:46 am
Opps.. sorry.. should have been clearer. By "that" I mean the principal/primary server.
My worry is that having 2 different types of replicated processes (mirroring and transaction replication) both running on the server, transferring the data to 2 different servers, will cause some kind of conflict.
Also, I assume there will be no issue with a SQL 2005 publisher replicating to a SQL 2008 subscriber?
October 28, 2009 at 7:55 am
My worry is that having 2 different types of replicated processes (mirroring and transaction replication) both running on the server, transferring the data to 2 different servers, will cause some kind of conflict.
Also, I assume there will be no issue with a SQL 2005 publisher replicating to a SQL 2008 subscriber?
There should not be any conflict arising when you have Database Mirroring and Replication setup on the same Database. ( by the way you are not implementing Transactional replication with updatable subscriptions or Merge Replication where conflicts may arise)
I am not sure 100% about 2005 publisher replicating to 2008 subscriber (I think it works) , but would be able to test and update here by evening.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 28, 2009 at 7:56 am
see
http://msdn.microsoft.com/en-us/library/ms151799.aspx
would not surprise me if replication latency is higher on a mirrored database.
---------------------------------------------------------------------
October 28, 2009 at 8:14 am
There's additional load, and you might have slightly higher latency. You also might need more log space. If there is a hiccup somewhere, the log won't clear until the subscription commits.
Also, what happens if you failover? Replication is then gone. Make sure you account for that.
October 28, 2009 at 10:03 am
Thanks for all the help and links guys. Much appreciated! Now for the fun of setting the whole thing up!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply