June 13, 2012 at 6:12 pm
There is a huge db being transactional replicated from Server1 (Publisher, OLTP) to Server2 (Subscriber, Reporting), both sql 2005.
One of the main transactional tables TimeSheet has 40+ million rows.
This table has a few indexes on Server1, but none on Server2 (other than PK).
Server2 is pretty slow (high CPU) due to lack of indexes. So we need to add them during a Maintenance window.
Since adding indexes online is pretty slow on huge table, should Replication be STOPPED, for offline indexing?
If so, this table is one of many tables replicated, so can we Pause replication for just one table? i.e.
let transactions collect & grow on Server1 (which has Distributor), and apply them after indexes are added.
If not possible to pause just one table, is there some job(s) we can disable to Pause replication as a whole?
And Resume afterwards without any sync-error?
June 14, 2012 at 5:37 am
Script out the indexes from publication side and apply on subscriber.but do these pushes in business off hours with some settings like "sot in tempdb = on", ONLINE = on and MAXDOP.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply