December 18, 2010 at 10:56 pm
Hello,
I have MS CRM database with a big size.
I have to replicate this DB one another server for reporting services.
For replicating the DB, the recovery model must be FULL, and the log file will grow to be very huge.
How to replicate the DB without to have issues with huge size of log file.
Regards,
December 19, 2010 at 1:34 am
http://sqlinthewild.co.za/index.php/2008/12/05/a-new-sql-myth/
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 19, 2010 at 3:16 am
As Gail's blog post article states, Transactional Replication does not require the Publication database to be using the FULL Recovery model.
Interestingly, I wonder if perhaps this confusion has come about with the introduction of the Database Mirroring technology. Mirroring of course does require the Principal Database (which can also be a Replication Publication) to be using the FULL recover model. In this scenario, two of the Database Mirroring Operating Modes(see reference below) require that Transactions must be hardened to the log of the Mirror Database before they can be picked up by the Log Reader Agent and replicated. If this Replication behaviour is undesirable it can be overridden through the use of a Trace Flag and is discussed in the White Paper SQL Server Replication: Providing High Availability using Database Mirroring, written by Paul Randal.
See Replication and Database Mirroring for more information.
December 20, 2010 at 8:27 am
If the database is simple/or bulk logged database, will the minimally logged activities such as rebuild indexes be replicated to subscribers?
December 20, 2010 at 9:31 am
Pei Zhu-415513 (12/20/2010)
If the database is simple/or bulk logged database, will the minimally logged activities such as rebuild indexes be replicated to subscribers?
Those activities are not replicated. It would be good to review what is replicated though as DDL activity can be replicated based on your settings.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 20, 2010 at 1:35 pm
Pei Zhu-415513 (12/20/2010)
If the database is simple/or bulk logged database, will the minimally logged activities such as rebuild indexes be replicated to subscribers?
Those aren't replicated regardless of the recovery model.
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 20, 2010 at 1:44 pm
Gail:
Are you sure? rebuild index(such as alter index .. rebuild) is fully logged if db is full recovery db. As a result, log reader should scan log and replicated to subscriber. Do you have Microsoft KB refering the behavior you mentioned?
Thanks,
Pei
December 20, 2010 at 1:56 pm
Pei Zhu-415513 (12/20/2010)
Gail:Are you sure? rebuild index(such as alter index .. rebuild) is fully logged if db is full recovery db. As a result, log reader should scan log and replicated to subscriber. Do you have Microsoft KB refering the behavior you mentioned?
If I may, I am 100% certain that an Index Rebuild operation is NOT picked up by the Log Reader Agent and Replicated to Subscribers, in a Transactional Replication topology.
Take a look at the reference: How Transactional Replication Works
The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database
The key phrase here being "marked for replication" i.e. not all transactions get marked.
December 20, 2010 at 1:57 pm
As I stated those are NOT replicated commands. Here is an article[/url] that walks you through testing this.
Enjoy.
Edited: Previous wording sounded bad. 🙁
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 20, 2010 at 2:04 pm
Pei Zhu-415513 (12/20/2010)
Gail:Are you sure? rebuild index(such as alter index .. rebuild) is fully logged if db is full recovery db. As a result, log reader should scan log and replicated to subscriber. Do you have Microsoft KB refering the behavior you mentioned?
Thanks,
Pei
Replication != log shipping or mirroring. Not everything that goes into the log is replicated. DDL statements (with conditions), insert, update, delete. That's it.
The fact that the index rebuilds are logged doesn't mean they're replicated. Truncate table is logged, but can't be done on replicated tables. File changes and recovery model changes are logged, they don't get applied at subscriber.
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 20, 2010 at 2:06 pm
Thanks alot.
Interesting and hard to believe. I used to experience the out of sync alot after building indexes for a large table. TLog bloated due to the pending replication.
December 20, 2010 at 2:11 pm
Have you read this? Sounds like skipping commands during index operations might be an issue. I stumbled across this when looking for (vainly I might add) a document that shows all the commands that log reader sees possible to "mark for replication".
http://support.microsoft.com/kb/959001
Interested to know if this applies to your version of SQL Server.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 20, 2010 at 2:12 pm
Pei Zhu-415513 (12/20/2010)
Interesting and hard to believe.
So you're right and 4 of us (and the SQL documentation) are wrong?
TLog bloated due to the pending replication.
Yes. To be expected. There are a lot of log records that the log reader has to read through and see if it wants. The fact that it doesn't want those log records doesn't change the fact that it has to read through them to check. Especially if there are replicated commands scattered between the rebuild log records.
The log reader has to read every log entry to see if it's marked for replication. Large numbers of log records (like for an index rebuild) means it has a lot of records to read through.
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 20, 2010 at 2:15 pm
Gail, I understand that. These logged activities you mentioned (of course ) will not replicated to subscriber since SIMPLY they are not publication. And they have nothing to do with the articles/objects defined in the publication.
I believe you are right that rebuild index would not be reflected in the subscriber db. I wonder how to explain the outofsync and bloated tlog in publication db due to the pending replication.
December 20, 2010 at 2:18 pm
Gail, thanks. Your explanation why tlog bloated makes sense.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply