I am a newbie dba and relatively new to replication area.
I am in a process of setting up a transactional replication on sql 2000
for a reporting purpose to offload this activity on the production
server.
My basic goal is to enhance the performance on the production server. I
thought replicating to a reporting server would be an ideal choice
using transactional relication scheduled on an hourly basis.
following is the setup i am trying to achieve...
I am using Production server which is more like an OLTP server as a
publisher, Distributor is going to be on the same server as of
subscriber because of less resources. Moerever I was suggested that
running the Distributor & Pulling the subscription would result in
better performance for both the servers.
Now my questions are...
1. Am i going in a write direction to achieve my task?
2. what are the precautions one should take before making the
"production server" as publisher is transactional relication?
3. Do i need to increase the size of db's and logs on production
server?
4. How should we know that replication is causing stress on production
server?
5. what is the ideal size of database and the transaction log that is
going to hold the replicated data?
6. How to remove replcation from a database completely in case if it
fails and causing issues on productions?
Thanks very much all. I will really appretiate your tips and
suggestions.
AK
Firstly, the method you are following is fairly commonly used. The size of
the logs and databases depends entirely on your initial data size and
transaction volume, so as long as you have the logs set to a large size or
autogrow, you'll just have to monitor them for your circumstances. The
impact of replication on your system can be monitored using the normal
counters in windows system monitor (processor usage, ram usage and disk
usage - if you are not familiar with these I can dig out some links). To
prevent the use of the replication overhead in the event of a slowdown, you
could initially stop the log-reader agent then remove the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the reply paul,
I have left the database/log size to autogrow and will keep an eye on
their size.
also, you haven't answered clearly whether am i doing a right thing by
keeping the distributor on a subscriber server instead of keeping it
with publisher in production server?
Could you also please indicate what sort of implication the production
server will have with this kind of setup? I have to make it clear to my
manager before i setup this thing on live environment.
And the basic question, I would think the initial snapshot should be
done out of business hours? If yes, how often i need to take the
snapshot or just reading the transaction logs would be enough as long
as there are no schema changes on the published tables?
Will the snapshot agent gets the entire shcema and data from the
published tables every time it runs or only at once in the begining in
transactional replication?
and the last thing, If you please don't mind can you show me some links
on how to read and understand processor and memory usage things.
Thanks very much
AK.
Paul Ibison wrote:
> Firstly, the method you are following is fairly commonly used. The size of
> the logs and databases depends entirely on your initial data size and
> transaction volume, so as long as you have the logs set to a large size or
> autogrow, you'll just have to monitor them for your circumstances. The
> impact of replication on your system can be monitored using the normal
> counters in windows system monitor (processor usage, ram usage and disk
> usage - if you are not familiar with these I can dig out some links). To
> prevent the use of the replication overhead in the event of a slowdown, you
> could initially stop the log-reader agent then remove the publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||For a 1:1 system, putting the distributor on the subscriber box is a valid
choice. I prefer to have the distributor on the production box so i have
control over the backups centrally, and will therefore take the extra hit
there. Pull distribution agents or push will run on the subscriber in your
case. You could have the distributor on the production box and use a pull
subscriber to have the agent still run on the subscriber's box. However
these are not equivalent, and there will be a lot of disk read/write access
to the distribution database which generally contends with the production
database as it resides on the same disk array - hence this is why your
choice makes sense.
For the implications of the log-reader reading the transaction log and
writing the transactions to the distribution database - only you can
determine this for your setup. Best practices are to set up this in a lab
environment mimicing the production site.
Only run the snapshot agent ONCE and then disable it. You'll need to run it
manually for adding new articles and in the case of reinitialization
(hopefully never
).For links, I'll dig some out for you...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
Thanks very much for your valuable tips. I really appreciate your time
on this.
Now one last thing i want to ask, once i have disabled the snapshot
agents, then how the log reader agent will read whenever there are any
transactions happend on publisher?
I want toschedule the job to read the transactions every hour. Shall i
schedule the log reader agent or the distribution agent to achieve this
task?
Many Thanks.
AK
Paul Ibison wrote:
> For a 1:1 system, putting the distributor on the subscriber box is a valid
> choice. I prefer to have the distributor on the production box so i have
> control over the backups centrally, and will therefore take the extra hit
> there. Pull distribution agents or push will run on the subscriber in your
> case. You could have the distributor on the production box and use a pull
> subscriber to have the agent still run on the subscriber's box. However
> these are not equivalent, and there will be a lot of disk read/write access
> to the distribution database which generally contends with the production
> database as it resides on the same disk array - hence this is why your
> choice makes sense.
> For the implications of the log-reader reading the transaction log and
> writing the transactions to the distribution database - only you can
> determine this for your setup. Best practices are to set up this in a lab
> environment mimicing the production site.
> Only run the snapshot agent ONCE and then disable it. You'll need to run it
> manually for adding new articles and in the case of reinitialization
> (hopefully never
).> For links, I'll dig some out for you...
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log reader should run continuously and the distribution agent can be set
to run hourly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Paul,
Thanks very much for your help. I have successfully tested this setup
in test environment.It was spot on. I am hoping to implement this on
live very soon.
Thanks,
AK
Paul Ibison wrote:
> The log reader should run continuously and the distribution agent can be set
> to run hourly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
