October 1, 2010 at 2:30 am
My distribution log is growing huge, so can i set distribution database to simple recovery.
What are the pos & cons that impact my replication while changing from full to simple recovery for my distribution datatabse. Distribution is the on the same server as the publisher.
October 5, 2010 at 11:50 am
The distributor can be set up in Simple mode. I do not see why you will want to keep it in Full mode.
-Roy
October 5, 2010 at 12:05 pm
Actually IIRC, all of the databases involved in replication can be set to simple.
I've never done this on the publisher/subscriber since any database important enough to be replicated is typically important enough to need point in time recovery..but the distributor can and should be set to simple.
October 5, 2010 at 1:10 pm
Derrick Smith (10/5/2010)
Actually IIRC, all of the databases involved in replication can be set to simple.
Yup. Replication does not depend on a specific 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
October 6, 2010 at 1:43 am
yup the Replication doesn't depend on the Recovery model,but
my query is that
1) how much perfomance gain i can get using simple recovery for distributor database.
2)how the DR stratagy should be for the Distibution database.( like if my distribution server is down should i need to do the point in time recovery to minimize the downtime)
October 6, 2010 at 1:45 am
yup the Replication doesn't depend on the Recovery model,but
my query is that
1) how much perfomance gain i can get using simple recovery for distributor database.
2)how the DR stratagy should be for the Distibution database.( like if my distribution server is down should i need to do the point in time recovery to minimize the downtime)
October 6, 2010 at 2:11 am
Gowtham Chowdary (10/6/2010)
1) how much perfomance gain i can get using simple recovery for distributor database.
Probably none. Recovery model does not have a big (if any) impact on performance.
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
October 6, 2010 at 4:54 am
Reg. performance as per my knowledge simple recovery will be better than full in case like BCP. and in my environment the disrtibution log file is growing huge auto growing the file regularly, where i hope simple recovery will do better in reducing the log file size and increasing the performance.
Please suggest me how to proceed. and correct me if i am wrong.
October 6, 2010 at 5:06 am
Gowtham Chowdary (10/6/2010)
Reg. performance as per my knowledge simple recovery will be better than full in case like BCP.
Maybe, but is there a lot of BCP on the distribution database?
and in my environment the disrtibution log file is growing huge auto growing the file regularly, where i hope simple recovery will do better in reducing the log file size and increasing the performance.
Reduce log growth, maybe, depends on how often you were doing log backup. Improve performance, well if autogrow was causing performance problems, something around the log file was poorly configured independant of recovery model.
The choice between full and simple recovery is not a performance related one. It's related to database recoverability and the need for point in time recovery. If you don't need point in time recovery, put the DB into simple. If you do, leave it in full.
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
October 6, 2010 at 5:43 am
thanks for your suggestion, and clarifying me things around performance and recovey model.
the thing is that the distribution log is growing and i am taking Tx-log backups(in case for point in time recovery).
Do i need to do point in time restoration for distribution database to resume replication as quick as possible or there is any other method to resume the replication with minimal downtime.
if you say that point in time is not nessasary to resume the replication. then i will change my DR stratagy and switch the Distribution database to simple Recovery so that my log file won't grow further and no need to take the log backups also.
October 6, 2010 at 6:50 am
Gowtham Chowdary (10/6/2010)
if you say that point in time is not nessasary to resume the replication. then i will change my DR stratagy and switch the Distribution database to simple Recovery so that my log file won't grow further and no need to take the log backups also.
What is your current DR strategy?
As Gail already mentioned - the decision on full v/s simple is not related to performance. Check http://msdn.microsoft.com/en-us/library/ms152560(v=SQL.90).aspx for details on backing up and restoring replication DBs - the "Databases At the Distributor" section has information regarding recovering the distribution DB.
The "Synch With Backup" option on the distributor DB and the publisher DB is important for how you want to be able to recover the publisher DB and distributor DB - and setting this option comes with some add-on costs.
If set on the distribution DB then transactions in the publisher log will not be truncated till the distribution DB is backed up (i.e. till those transactions are backed up in the distribution DB), if set on the publisher DB then transactions are not sent over to the distributor until they are backed up on the publisher.
Depending on your DR strategy and replication volume of data - a full recovery for distributor and publisher with the "Synch with Backup" set on both these DBs and regular log backups (how regular would depend on the impact of the "Synch with Backup" setting based on replication volume) would be one way to approach things.
October 6, 2010 at 7:33 am
thanks for edifying me and guiding me on how to approach.....
October 6, 2010 at 8:13 am
There is a small catch in the "The "Synch With Backup" option on the distributor DB and the publisher DB is important for how you want to be able to recover the publisher DB and distributor DB - and setting this option comes with some add-on costs. "
The catch is that if the PublisherDB is set with the option "Synch with back Up" the replications commands will not propagate to the distribution DB till the log back up is done in Publisher. This means that there will be a high latency.
-Roy
October 8, 2010 at 4:05 am
winash (10/6/2010)
What is your current DR strategy?
As Gail already mentioned - the decision on full v/s simple is not related to performance. Check http://msdn.microsoft.com/en-us/library/ms152560(v=SQL.90).aspx for details on backing up and restoring replication DBs - the "Databases At the Distributor" section has information regarding recovering the distribution DB.
I have gone thru the link you provide and come with an opinion to set my Distribution database property "issyncwithbackup" to TRUE.
now My Publisher and Distribution database are on the same server, where publisher is replicated to 29 other servers over WAN. "With row level filtering"
1)What is the best DR Stratagy for my scenario to get point in time recovery
2) What are the best practices shud i follow to get good performance
Thanks
Gowtham M
October 9, 2010 at 10:26 am
I have gone thru the link you provide and come with an opinion to set my Distribution database property "issyncwithbackup" to TRUE.
Remember that with this option set to true on the Distributor you need to factor in the following:
Setting this option on the distribution database has no effect on replication latency. However, it will delay the truncation of the log on the publication database until the corresponding transactions in the distribution database have been backed up (which can result in a larger transaction log in the publication database).
now My Publisher and Distribution database are on the same server, where publisher is replicated to 29 other servers over WAN. "With row level filtering"
1)What is the best DR Stratagy for my scenario to get point in time recovery
2) What are the best practices shud i follow to get good performance
The general recommendation is to put your distributor on a separate machine to avoid the extra load of running the log reader and distribution agents on the publisher. Since you have it on the same server you'll need to factor this into performance.
Some of the best practices for replication performance are documented here - some of the options documented depend on your environment. For e.g. log reader agent switches like -MaxCmdsInTrans are very dependent on whether you are okay with having the subscribers view transactionally incosistent data, distributor agent switches like -SubscriptionStreams, -CommitBatchSize, -CommitBatchThreshold need to be tested out to find the optimal values in your environment, for large volumes of data if you can publish stored procedure execution instead of the data then that will reduce the amount of commands that need to be replicated etc
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply