September 30, 2003 at 6:01 am
Ok...I've tried and failed.
Situation is the following....
Source DB - SQL2000, destination is heterogenous (SQLBase via ODBC)
For each article, I want only the Primary Key and Index to go across, and no other indexes - I can't switch off nonclustered indexes in Article properties though, unless I 'include declared RI' I have a reason for this as well.. I'm replicating to SQLBase and it doesn't handle the RI statements too well.
Since SQL2000 creates the tables schema and creates the index BEFORE data copy, this obviously slows the copy down. I'd rather copy the data first, then create the indexes. Is it possible? I can't use DTS either due to faults in the SQLBase ODBC drivers.
Any pointers? Many thx.
Paul
September 30, 2003 at 2:16 pm
Run a presnapshot script that would drop any indexes, make other changes you want before the data gets loaded maybe.
Andy
September 30, 2003 at 3:43 pm
After the data is copied you can create the index with a post snapshot sctipt.
Be sure to check the scripts right, before executing them...
October 1, 2003 at 6:38 am
Interesting suggestion , and one that I did not consider as a possibility. That is an exceptional suggestion!
As I'm replicating to non-SQLServer destinations, I'll have to manually insert steps into the Snapshot agent job to do this.
October 1, 2003 at 6:48 am
Not necesarely, you can specify the full path of a script in the pre and post_snapshot_script parameters of the sp_addpublication sp for transactional replication, and the same parameter for merge replication in sp_addmergepublication sp.
October 3, 2003 at 5:13 am
Hmmm...I have to admit that I rarely use the SPs directly. However, if setting replication up through EM, then you cannot actually set pre/post scripts unless going to SQLServer only. The post script (to create indexes) needs to run against the non-SQL destination.
Original setup is...
Database A (MSSQL) trans-replicates a subset nightly to Database B (internal reporting use only). Database B is then snapshotted through to ODBC (SQLBase) - for customers (minus some details). Cascading replication - PITA for schema changes in A. We need the reporting DB to be stable during the day without changes going through. Having given this some thought, and given other considerations, I've implemented the following.
Database A --> B as before.
B is DTS-d to C (with no indexes), performing some transformations.
C snapshotted as appropriate.
This actually gives me tons more flexibility, and has enabled me to optimise elsewhere.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply