i recently set up sql transnational replication and i have some performance issue when i'm initializing/re-initializing it from snapshot. The biggest table have is about 8gb as snapshot file network connection is fine - when im copying (just to check network speed is not the case) this snapshot file into desktop of this subscriber machine then it is around 200 MBps.
When this source machine witch is distributor also is bulk inserting this table into subscription database then CPU on this subscriber machine almosts sleeps , batch requests is between 0 and 1 and DATABASE IO is 4 MBps max...
Why it is going so slow ? is there any way to speed this up ? 8gb table will be +- 30min what if i will have 50gb table some day?
Can it be the case that the publisher machine is distributor also ? and because of that there are some network bulk insert commands limitations ? It is how i think that in that case that the distributor is actually opening this snapshot that he generates before and doing this bulk insert like thru linked server in that case?
On my destination recovery models is simple so it is not this also.
thanks and regards!