SQL Server Performance Forum – Threads Archive
Replication vs log shipping
I am trying to kill 3 birds with one stone: 1. Implement a redundant production copy of the database at the data center with high availability2. Separate transactional application from reporting (OLTP from OLAP) at the data center, with latency of replication 1-2 minutes being our prefered option in order to achieve high availability
3. Create a replica of the database at the remote location, connected via T-1 link, latency of replication can be 24 hrs We are using SQL Server Enterprise 2000. Replicas will use transactional tables as read only. One of the potential implementations is to replicate transactional database to the reporting server one-to-one, in order to be able to use the reporting server as a failover system, then denormalize tables as needed by using triggers in the reporting database. OLTP is mostly inserts, with very rare updates (~ 1% of data gets updated). Expected peak load for the next 6 months is 20 business transactions per second (each consists of about 3-4 lookups and inserts in 4-5 tables), with increase to 40 business TPS within a year. Size of the database is 5 Gb, but it doubles every 3-5 months and growth will accelerate next year. Expected size of the database at the end of 2005 is > 30Gb.
I am considering two options: log shipping and replication, with anticipated clustering for OLTP database within 6-8 months. My questions are: How much of performance degradation (percentage ?) would transactional replication cause on a relatively busy OLTP server, if interval is set to 1 minute? What is a reasonable time interval for log shipping? Is it possible to set log shipping with 1 minute interval? What would be impact of clustering on the either model(replication or log shipping)? Would a mixed model work, with replication set for the reporting database only and log shipping for the remote one? Is it reasonable to make a subscriber (reporting server) also a distributor? Thank you in advance, THG
How much of performance degradation (percentage ?) would transactional replication cause on a relatively busy OLTP server, if interval is set to 1 minute?
You can enhance the performance of transactional replication in your application and on your network by:
– Running agents continuously instead of on frequent schedules.
– Reducing the distribution frequency when replicating to numerous Subscribers.
– Configuring the Distributor on a dedicated server.
– Increasing memory on the Distributor.
– Minimizing the retention period for transactions and history.
– Increasing the read batch size for the Log Reader Agent.
– Using custom stored procedures for inserts, updates, and deletes at Subscribers. What is a reasonable time interval for log shipping? Is it possible to set log shipping with 1 minute interval?
The minimum interval time was set at my end is 5 minutes and it is purely depends on the database size and its activity, also based on the hardware of the primary and secondary servers with assurance of no glitches on network/hardware. What would be impact of clustering on the either model(replication or log shipping)?
I have no experience in setting Replication or Log shipping on Cluster, and I beleive if one can affod Cluster solution then there is no requirement of sync. or warm standby interim solutions between the Live and DR servers. Would a mixed model work, with replication set for the reporting database only and log shipping for the remote one?
What do you mean by Mixed model, please explain. Is it reasonable to make a subscriber (reporting server) also a distributor?
Never tried so.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Satya, Thank you for responding. Do you imply that transactional replication, if designed right, would not have visible impact on the publisher? Would glitches on the network impact replication as much as log shipping? Would clustering protect database in case of data corruption better than log shipping or replication? How would you suggest to replicate data to the remote server that is not going to be clustered and needs a separate copy of the database? Under mixed model I meant setting once a night log shipping to the remote server and frequent transational replication to the local reporting server. THG
True, once the transactional replication is set in order it will not have any impact, refer to the replication tips in this website for further performance information. Network glitch will affect any part of SQL Server, it may not be necessarily Log shipping or replication processes. SQL failover clustering provides high availability support and during an operating system failure or a planned upgrade, you can configure one failover cluster to fail over to any other node in the failover cluster configuration. In this way, you minimize system downtime, thus providing high server availability. Whereas Log shipping provides warm standby which can perform role changes in case of disaster and least data loss would be the interval of log shipping restore job. Cluster is an expensive solution with high availability and Replication & Log shipping solutions are less compared when it comes to the availibility. As I said I don’t have experience in setting replication for a log shipping pair. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks a lot!
Personally I would like to know which option you’ve chosen after the comments & investigation. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
>>What is a reasonable time interval for log shipping? Is it possible to set log shipping with 1 minute interval? I use to run log shipping with 1 minute interval without any issues on a medium to large size databases (250-300 Gb each) until we created clusters. Questions I usually ask myself when faced with log shipping vs replication question
1) Do I need all of the data in the "standby" or only a subset of it? (if only a subset is needed, log shipping is out)
2) How much is the maximum delay I am willing to tolerate? (For some systems shipping logs every 1 min is way too long )
3) Do I need write access to "copied" database? (If yes, log shipping is out) If you are building a standby and can tolerate 1+ minute belay – consider log shipping. If you want a reporting server and/or need near instant updates – properly designed replication is your friend. simas
IN the terms of cost both the process are available using Enterprise Edition, but when it comes to managability I prefer Log shipping is better than Replication. There is a hassle in replication and is not fully suitable for all types of design. As explained Log shipping may not give you full recoverability but keeping the resources available you can avoid any data loss. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>