July 23, 2008 at 10:00 am
I have a production database, and alot of users are connecting to it simaltaneously accessing alot of data. So i am planning to implement Replication for Load Sharing & High Availability. Can any what tell me what will be the impact of replicatoin on performance of the publishing database which is a production database?
Thanks,
Usman
July 24, 2008 at 1:47 pm
depends on what replication Model you use, there are different setups, you can have 1, 2 or 3 servers handling the process.
an ideal setup to avoid possible bottleneck on your OLTP is to have a remote distributor setup on a secondary or subscriber server that will handle all th processing of tracking and moving data.
July 30, 2008 at 5:21 am
make sure you apply the latest Service pack and cumulative update of sql server (if we talk about 2005 curently its the 8th CU for the 2nd SP), because they do resolve a lot of problems with replication, specially merge.
July 31, 2008 at 7:20 am
We use transactional replication with 3 servers: Publisher - Distributor - Subscriber. We off-load all reporting and job processing from the Publisher to the Subscriber. We only allow OLTP on the Publisher. It sounds like this is similiar to what you want to do. The impact of replication running with this configuration is minimal on the Publisher.
Good luck!
August 4, 2008 at 8:23 am
RML51 (7/31/2008)
The impact of replication running with this configuration is minimal on the Publisher.
What's the size of the database? How many users connect to it during peak hours? And do you publish the entire database?
Sorry for all the questions, but we're looking implementing (transactional)replication soon.
August 11, 2008 at 7:09 am
Sorry I didn't respond sooner, but I was on vacation last week. 🙂
We publish 5 dbs totaling about 500GB - all from our Prod server to our Reporting server. The largest of the 5 dbs is about 390GB. We publish all tables in all 5 dbs. At peak times, we have approximately 1,000 user connections.
August 11, 2008 at 7:22 am
Thanks for the response. (Holiday??)
Do you have any tips (apart from the documented procedures/articles available) in working with replication? I.e. something you wish you knew before you started working with it?
August 11, 2008 at 8:16 am
Our dbs and the associated application is supplied by a vendor. We had them create replication setup scripts for us, although we found we had to do some modification to those scripts.
SQL 2005 replication is much more stable than SQL 2000. We've had replication in place for about 5 years, so we used to have it setup with SQL 2000.
Here are a few things to keep in mind...
Assuming both your Pub and Sub are SQL - Use native SQL Server for the snapshot format (this is the default)
Make sure your snapshot folder has a lot of disk space available. I think you need something like 1.4 times your largest db for the snapshot folder. i.e. to publish a 100GB db you need your snapshot folder to have at least 140GB available.
If you're publishing text columns you may need to adjust you max text repl size. We had this problem, so we made this config change on all 3 instances: Pub, Dist, and Sub.
After replication is established, watch your table modifications. You may have to drop a table from being published - make the modification(s) - and then republish/resnapshot the table.
August 12, 2008 at 2:17 am
Thanks for the info.
I'm sure it will come in handy very soon!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply