I am working on an article for submission to explain the steps we use to update a Transactional Replicated table to a Data Warehouse where the database is also mirrored to an off-site Disaster recovery site.
The mirrored site connection and transfer rate ranges from 500k/sec to 2000k/sec and the added columns to a large (20+million rows) produce alot of TLog to transfer to mirrored site. The replicated transaction is delayed by the mirror.
In general, here are the steps:
1. Bring OLTP and OLAP systems down. This is already done because of release.
2. Make sure all transactions have been committed to subscribers. Use Replication Monitor.
3. Generate scripts for DROP and CREATE Publication and Subscription(s)
4. DROP Publication and subscription(s)
5. ALTER TABLE or any other scriptions on all databases, publication and subscribers
6. Create Publication without running any add subscriptions parts of the scripts
7. Use system SP - sp_scriptpublicationcustomprocs to generate replication stored procedures.
8. Execute SPs on Subscriber(s)
9. Run Create subscription script with @sync_type='none' not 'automatic'
10. Use Replication Validation from Replication Monitor to see if everything is OK
The only issue has been if the mirror still has not finished the transaction. Sometimes we have to wait 1-2 hours for this to finish.
There is a new trace flag -T1448, that is suppose to help with this, but the test we have tried have not shown this to be so.
God Bless,
Thomas
No comments:
Post a Comment