September 17, 2009 at 11:09 am
I need some help concerning replication. I'm new to SQL Server replication never used it before.
I've setup replication between 2 server with pull subscriber. I'm replicating 12 tables and 2 of them are pretty big (200g each) because they hold varbinary data. These are documents stored within the database. Some documents are of decent size, but most are smaller then 20mg and replicate in less then 30 seconds.
I'm having an issue with files that are larger, a 55mg file takes 6 minutes to replicate, 100m 18 minutes, 250 71 minutes. this is unacceptable for 2 reason, 1 the user wants to see the data within 5 minutes and 2 I'm only testing one doc at a time, may be the users are loading 100 documents at once.
The reason we are replicating this data to another machine is for read purposes. We have fulltext indexes on these tables, but we can only schedule 1 population a night and the users want the indexes populated immediatly. So the goal is to replicate and have auto-change tracking turned on in the replicated DB.
Any idea's on what could be causing the performance issues? By the way the server is 8processor 8g RAM. I created an named instance to replicate to so it's not going over the wire. Same server, different instance.
September 18, 2009 at 7:46 am
Same server different instance means you are sharing the resources. Publisher, distributor and the subscriber.
1. CPU
2. RAM
3. IO
This will cause pretty bad performance. If your Disks are not configured properly, you will get the same issue.
-Roy
September 18, 2009 at 7:51 am
After I posted this, I was thinking the same. I'm going to setup a test to a separate server and see what kind of improvment I get. I'll post the results
September 18, 2009 at 10:10 am
Just for interest, why do the customers not just access the files straight from the source instead of pumping out to a named instance?
September 21, 2009 at 7:45 am
Well I changed the location of the subscrber from a named instance on the same server as the publisher to a seperate server altogether.
Document replication went from 5 minutes for a 55meg file to 24 minutes. Which is really disappointing.
September 21, 2009 at 7:55 am
Thats network traffic for you. I think you need to check out your disk setup and run it from the same server. Again though I wonder why you don't allow the customer to view it via the original db.
September 21, 2009 at 8:00 am
Trying to remove these tables from the orginal DB, because they are FTI and to auto update these fields is a resource issue on the server.
I'm on the test machine now, that is way I had it going to a named instance.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply