High-transaction replication

We are trying to set up replication of a large (over 200GB) database,
attempting to break the transactional processing and reporting apart,
obviously replicating from the processing database. (This is a temporary fix
until we can implement our data warehouse.)
There are approximately 70 tables that will need to be replicated for this
to work for us, and I have broken these tables into logical grouping by
publication. We ran into some errors initially ("The process could not
execute 'sp_replcmds'..." errors), and found some references to assist with
this.
The next issue we have run into and are struggling with now are errors in
the log reader stating "The process could not execute
'sp_MSadd_repl_commands27hp'..." The descriptions reference that there was
deadlocking and the logreader was killed. The Distribution cleanup agent
appears to be the other process that was locking the table(s), with the job
taking up to 58 seconds to complete (48k transactions and 60k statements
deleted). I took and modified the schedule of the cleanup job to run every
minute between 6 and 6, and every 5 minutes during the night - thinking there
would be fewer transactions to delete, and allowing the table to be locked
for a shorter period of time. My thinking was correct, but the deadlocking
still happens, but recovers quicker than previously.
The worry we have is that we have not created the publications for all the
tables, and are already sending over 500k transactions/hour through. Once we
get all the tables back up and going we are projecting about another 500k
transactions, and are worried about the deadlocking. Are there any
ideas/settings we should be looking at to enable SQL to handle transactional
replication of 1 million+ transactions an hour, or are we really looking for
SQL to do what it cannot handle?
Thanks in advance for any ideas!
You should migrate to a remote distributor. You should also look at
replicating the execution of stored procedures. This will improve the
performance of your solution dramatically.
I would also look at decreasing your transaction retention period to perhaps
a day.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:1A4C62BB-A2E9-445D-8FE7-A9339E2EC209@.microsoft.com...
> We are trying to set up replication of a large (over 200GB) database,
> attempting to break the transactional processing and reporting apart,
> obviously replicating from the processing database. (This is a temporary
fix
> until we can implement our data warehouse.)
> There are approximately 70 tables that will need to be replicated for this
> to work for us, and I have broken these tables into logical grouping by
> publication. We ran into some errors initially ("The process could not
> execute 'sp_replcmds'..." errors), and found some references to assist
with
> this.
> The next issue we have run into and are struggling with now are errors in
> the log reader stating "The process could not execute
> 'sp_MSadd_repl_commands27hp'..." The descriptions reference that there
was
> deadlocking and the logreader was killed. The Distribution cleanup agent
> appears to be the other process that was locking the table(s), with the
job
> taking up to 58 seconds to complete (48k transactions and 60k statements
> deleted). I took and modified the schedule of the cleanup job to run
every
> minute between 6 and 6, and every 5 minutes during the night - thinking
there
> would be fewer transactions to delete, and allowing the table to be locked
> for a shorter period of time. My thinking was correct, but the
deadlocking
> still happens, but recovers quicker than previously.
> The worry we have is that we have not created the publications for all the
> tables, and are already sending over 500k transactions/hour through. Once
we
> get all the tables back up and going we are projecting about another 500k
> transactions, and are worried about the deadlocking. Are there any
> ideas/settings we should be looking at to enable SQL to handle
transactional
> replication of 1 million+ transactions an hour, or are we really looking
for
> SQL to do what it cannot handle?
> Thanks in advance for any ideas!
|||You can also take a look the Microsoft White Paper Replication Performance
Tuning. I have found some very interesting results with much larger databases
than that with implementing some of the modifications in there particulairly
the (-maxcmdsintran ) switch in the log reader agent and creating a
performance profile for the distribution agent. I can give you examples or
better documentation if you require. Please email - Richard.Hale@.MM-Games.com
with any questions
"Rob C" wrote:

> We are trying to set up replication of a large (over 200GB) database,
> attempting to break the transactional processing and reporting apart,
> obviously replicating from the processing database. (This is a temporary fix
> until we can implement our data warehouse.)
> There are approximately 70 tables that will need to be replicated for this
> to work for us, and I have broken these tables into logical grouping by
> publication. We ran into some errors initially ("The process could not
> execute 'sp_replcmds'..." errors), and found some references to assist with
> this.
> The next issue we have run into and are struggling with now are errors in
> the log reader stating "The process could not execute
> 'sp_MSadd_repl_commands27hp'..." The descriptions reference that there was
> deadlocking and the logreader was killed. The Distribution cleanup agent
> appears to be the other process that was locking the table(s), with the job
> taking up to 58 seconds to complete (48k transactions and 60k statements
> deleted). I took and modified the schedule of the cleanup job to run every
> minute between 6 and 6, and every 5 minutes during the night - thinking there
> would be fewer transactions to delete, and allowing the table to be locked
> for a shorter period of time. My thinking was correct, but the deadlocking
> still happens, but recovers quicker than previously.
> The worry we have is that we have not created the publications for all the
> tables, and are already sending over 500k transactions/hour through. Once we
> get all the tables back up and going we are projecting about another 500k
> transactions, and are worried about the deadlocking. Are there any
> ideas/settings we should be looking at to enable SQL to handle transactional
> replication of 1 million+ transactions an hour, or are we really looking for
> SQL to do what it cannot handle?
> Thanks in advance for any ideas!

Hiding a table in a report

Hello:
I have a report with several tables, each of them "Inserts a page break
before" (so it renders on an individual page)
The visibility of one of these tables depends on the data and some problems
are derived from this situation:
1 -The table with the Hidden expression is not inserting the page break no
matter if it is visible or not.
2 -The space occupied by the definition of the table is always there, no
matter if the table is hidden.
I try putting all my tables in a main table and hide the row containing the
conditional table. This solves the 2nd problem, but when you do this the
"Insert a page break before this table" property of the contained tables are
ignored and everything renders on the same page.
What I would like is that when the table is visible, it "inserts a page
break before" and renders in another page, and when it is hidden, the
current page doesn't have an extra white space between its end and the
footer, and the next table renders on the next page.
Is there any way to do this?
Thanks.Try putting each table into a rectangle.. Then show/hide the rectangle... I
think that might get you where you wish to be...
Have fun!
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Daniel Bello" wrote:
> Hello:
> I have a report with several tables, each of them "Inserts a page break
> before" (so it renders on an individual page)
> The visibility of one of these tables depends on the data and some problems
> are derived from this situation:
> 1 -The table with the Hidden expression is not inserting the page break no
> matter if it is visible or not.
> 2 -The space occupied by the definition of the table is always there, no
> matter if the table is hidden.
> I try putting all my tables in a main table and hide the row containing the
> conditional table. This solves the 2nd problem, but when you do this the
> "Insert a page break before this table" property of the contained tables are
> ignored and everything renders on the same page.
> What I would like is that when the table is visible, it "inserts a page
> break before" and renders in another page, and when it is hidden, the
> current page doesn't have an extra white space between its end and the
> footer, and the next table renders on the next page.
> Is there any way to do this?
> Thanks.
>
>|||Hello Wayne:
Having the tables inside rectangles works the same for me, both ignore the
"Insert a page break before this element" clause once you add an expression
to change the visibility.
Any ideas?
Thanks,
Daniel Bello.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:5862533C-8506-454B-B78D-2BBD871F190B@.microsoft.com...
> Try putting each table into a rectangle.. Then show/hide the rectangle...
> I
> think that might get you where you wish to be...
> Have fun!
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Daniel Bello" wrote:
>> Hello:
>> I have a report with several tables, each of them "Inserts a page break
>> before" (so it renders on an individual page)
>> The visibility of one of these tables depends on the data and some
>> problems
>> are derived from this situation:
>> 1 -The table with the Hidden expression is not inserting the page break
>> no
>> matter if it is visible or not.
>> 2 -The space occupied by the definition of the table is always there, no
>> matter if the table is hidden.
>> I try putting all my tables in a main table and hide the row containing
>> the
>> conditional table. This solves the 2nd problem, but when you do this the
>> "Insert a page break before this table" property of the contained tables
>> are
>> ignored and everything renders on the same page.
>> What I would like is that when the table is visible, it "inserts a page
>> break before" and renders in another page, and when it is hidden, the
>> current page doesn't have an extra white space between its end and the
>> footer, and the next table renders on the next page.
>> Is there any way to do this?
>> Thanks.
>>