December 5, 2008 at 1:40 am
I have a situation where I have 2 servers A and B.I want to continuously update B with A's data but at the same time any change done to B should not affect A and also it should stay in B.
B is a report server so it needs to be available to the application.
Can database mirroring work in this case?I know mirror server is always in restoring mode and also that mirror is a copy of the principal.
Please advise.
December 5, 2008 at 1:53 am
Consider transactional replication rather.
Mirroring is a high-availability solution, not for reporting.
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
December 5, 2008 at 3:08 am
GilaMonster (12/5/2008)
Consider transactional replication rather.Mirroring is a high-availability solution, not for reporting.
If you think you will be updating B regularly or making changes to the databases, this could get in the way of replication. Assuming that it is not necassery to have upto-the-minute accuracy in B, I would set the replication to Snapshot. This way any changes you may apply to B (which you may not want to keep) will be overwritten with each snapshot. Snapshots can be scheduled to run as frequently or in-frequently as you like.
If you just want to report on the data, you may wish to not replicate triggers.
To allow replication you db must be in Full or Bulk recovery model.
HTH. 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 5, 2008 at 3:37 am
ghoshmoumita (12/5/2008)
{...}B is a report server so it needs to be available to the application.
Can database mirroring work in this case?{...}
No. With database mirroring, the mirrored database (on server B) cannot be accessed even as read only.
December 5, 2008 at 4:41 am
Schadenfreude-Mei (12/5/2008)
To allow replication you db must be in Full or Bulk recovery model.
Replication does not require any particular recovery model. All forms of replication (snapshot, transactional, merge) work fine under any of the recovery models.
Database mirroring requires Full recovery
Log Shipping requires full or (I think) bulk logged
Transactional Replication (which is the one that uses the transaction log) has its own set of markers in the log to ensure that a log record can't be discarded (by a checkpoint in simple or a log backup in full/bulk-logged) until that records has been picked up by the log reader.
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
December 5, 2008 at 5:50 am
GilaMonster (12/5/2008)
Schadenfreude-Mei (12/5/2008)
To allow replication you db must be in Full or Bulk recovery model.Replication does not require any particular recovery model. All forms of replication (snapshot, transactional, merge) work fine under any of the recovery models.
Database mirroring requires Full recovery
Log Shipping requires full or (I think) bulk logged
Transactional Replication (which is the one that uses the transaction log) has its own set of markers in the log to ensure that a log record can't be discarded (by a checkpoint in simple or a log backup in full/bulk-logged) until that records has been picked up by the log reader.
So its only Transaction Replication that needs Full/Bulk? I didnt know that i thought it was Replication in general.
Thanks Gail 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 5, 2008 at 6:02 am
Log shipping works with either full or bulk recovery, but this is still not a candidate for reporting, access to the database will either be prevented while logs are being restored, or will kick you out and preventing you from accessing the database while the log restore happens.
Mirroring is for redundancy only, however you could create snapshots against the data in the mirror and report against those, however you are limited to the point in time of which the snapshot was taken (although you could create multiple snapshots).
Replication is by far and away the best method. Snapshots of the data would work to create point in time sets of data, however would destroy the tables on the subscriber end, and would be blocked by or block users attempting to run reports. Transactional replication is the way to go. Is is set up, after the initial snapshot, to move transactions when applied on the publisher and will give you a latency, depending upon the size of the row and the number of rows affected in the transaction, measured in seconds. Additionally replication scales really well and you can publish out to multiple servers and provide a distributed reporting environment (in this scenario it would be better to have a separate distribution box).
December 5, 2008 at 6:20 am
Schadenfreude-Mei (12/5/2008)
So its only Transaction Replication that needs Full/Bulk? I didnt know that i thought it was Replication in general.
No. As I said above
Replication does not require any particular recovery model. All forms of replication (snapshot, transactional, merge) work fine under any of the recovery models.
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
December 5, 2008 at 6:24 am
Nicholas Cain (12/5/2008)
Transactional replication is the way to go.
Agreed. This is almost a textbook case for transactional replication
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
December 5, 2008 at 8:02 am
GilaMonster (12/5/2008)
Schadenfreude-Mei (12/5/2008)
So its only Transaction Replication that needs Full/Bulk? I didnt know that i thought it was Replication in general.No. As I said above
Replication does not require any particular recovery model. All forms of replication (snapshot, transactional, merge) work fine under any of the recovery models.
Ok, could you please then explain this.
I have recently setup BES replication, to do this the instructions prompted me to change recovery model to FULL. Replication went fine, but I periodically get messages pertaining to autogrow of the main database.
Ideally i want to set recovery model back to SIMPLE but in tests the result of this is that changes do not get replicated.
Please explain my misunderstanding or where i am going wrong. The db server is 2k5sp2
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 5, 2008 at 9:15 am
By BES replication do you mean the replication within Blackberry Enterprise Server?
December 5, 2008 at 9:26 am
Schadenfreude-Mei (12/5/2008)
I have recently setup BES replication, to do this the instructions prompted me to change recovery model to FULL.
What's BES Replication?
I've got, on my desktop, a replication setup between SQL 2005 and 2008. Both the publisher and the subscriber are simple recovery. I can take and post screen shots if you want.
SQL's native replication does not require any particular recovery model.
Replication went fine, but I periodically get messages pertaining to autogrow of the main database.
Reduce the autogrow amount, monitor the database's free size and grow it manually in a quiet time before it becomes necessary for autogrow to kick in. Autogrow should only be there so that the DB doesn't fall over if there's an unexpected, sudden increase in data.
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
December 5, 2008 at 9:39 am
BES has it's own replication topology that has nothing to do with SQL Server. SQL is just the database that happens to sit behind the BES application. There are other databases that can be used other than SQL I believe.
BES replication is only for disaster recovery purposes also I believe (more like a log shipping scenario).
December 5, 2008 at 10:18 am
Gail: BES is Blackberry Enterprise Server
According to the instructions from the BlackBerry web site, for DR purposes you have to setup Transactional Replciation (there is a script to run which identifies one from the other from BES). But they are quitw clear to set to FULL recovery model.
Off the BES subject for a sec, I have always been told that I cant have Simple for replication, if this is not so I would love to know how. I did a quick test on my desktop b4 my last post and could not see table additions on my subscriber db after and update to the publisher. Then I switched to FULL and reinitialized and it worked.
So what am i doing wrong?
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 5, 2008 at 10:19 am
Nicholas Cain (12/5/2008)
By BES replication do you mean the replication within Blackberry Enterprise Server?
Sorry no, this is database replication for dr purposes.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply