The 2 technologies are used for different purposes really. Log Shipping
copies and applies the transaction log to another source, by doing this
the data and the schema are the same with some latency (depending on how
often you backup, copy and apply the transaction log) and this is
generally used for a disaster recovery solution. Transactional
replication is a data only and is usually for off loading data for other
purposes like reporting. Choose the technology that suits your needs
for the target server.
As far as the overload, the first thing I would look into is making sure
that the Distributor is not the Publisher. Make the Distributor its own
box or on the Subscriber. I recommend putting the distributor on its
own box.
Rich Baumet
SQL DBA
-----Original Message-----
From: Mike Sofen
[mailto:mssqldba-ezmlmshield-x50386420.[Email address protected]
Sent: Friday, March 31, 2006 7:37 AM
To: LazyDBA Discussion
Subject: Log Shipping vs transactional replication
Ok Super DBA's, I need your help. I'm not a dba, I'm a db architect and
do not know replication intricacies and need your help discerning the
difference between these two replication models and why you'd pick one
over the other.
The situation: a production db server (sql 2005 Enterprise x64) with
one active db on it, 6 active tables getting hit hard - our stress
testing went up to 6000 write operations/sec, hit maybe 30% cpu. On
startup, SQL Server took 8gb of the 16gb of ram, never needed more. It
is connected to a SAN via dual 2gb HBA cards. There were no page faults
or disk queues.
Now, after our self-taught dba applied transactional replication, with
only 60 operations/second, we're running 80-95% cpu, and most bizarre,
only 1 gb of ram is in play. There are still no page faults or disk
queues. Since this is a phased rollout, we haven't yet turned on the
big guns that will pound this server. Using the default memory
settings for sql server (no limits). Just changed them to 8gb minimum
and 14.5 gb max, no change.
So I'm wondering - would log shipping be a better route? We don't need
real time replication, just near real time - every 5 minutes would be
fine. Any other ideas about what to look for?
Mike Sofen
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
The information contained in this electronic message is for the exclusive and confidential use of the addressee. Any other distribution, use, reproduction or alteration of the information contained in this electronic message, by the addressee or by any other recipient, without the prior written consent of the sender is strictly prohibited. If you have received this electronic message in error, please notify the sender.
MS Sql Server LazyDBA home page