Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Having issues with sf_replicate and pkchunk option

Looking to trim down time for sf_replicate on larger objects. Using an object with about 200K rows as a test case, and trying to get pkchunk options working per the documentation:

1. Tried with default syntax:
exec sf_replicate @table_server = @server, @table_name = @objectname, @options='pkchunk'

I get no errors, but also no data after a few minutes, so I assume it is timing out.

12:59:59: DBAmp is using the SQL Native Client.
12:59:59: Opening SQL Server rowset
13:00:01: Using the bulkapi with polling every 60 seconds.
13:00:02: Job still running.
13:00:17: Job Complete.

Again per the documentation, I lowered the batchsize to 25000:

2. exec sf_replicate @table_server = @server, @table_name = @objectname, @options='pkchunk,batchsize(25000)'

This gives me an immediate error on the batch size?

13:11:07: Error: Invalid batchsize. Must be between 1 and 2000

So I drop the batch size to 2000...

3. exec sf_replicate @table_server = @server, @table_name = @objectname, @options='pkchunk,batchsize(2000)'

This seems to work part way (batch size reset), but then hurls immediately after...

13:12:58: Batch size reset to 2000 rows per batch.
13:12:58: Error: Invalid options. Valid options are batchsize(x) , partitions(x) jobid(x) and bulkapi .

As a final test, I tried the sf_replicateLarge routine:

4. exec SF_ReplicateLarge @table_server = @server, @table_name = @objectname, @batchsize = 50000

This actually completed, but only shaves about a minute off the standard sf_replicate functionality. So I'd really like to see if I can get the pkchunk option working.

Any suggestions?
1 person has
this problem
+1
Reply
  • 1. What version of DBAmp are you running ?

    2. Can you post the complete message output when using pkchunk ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned

  • Hi Bill

    According to DBAmp output, version is 3.1.3. Account object contains ~170000 rows. Any suggestions appreciated.

    1. Output from sf_replicate with 'pkchunk' only. No errors, but no data moved.

    --- Starting SF_Replicate for Account
    08:01:38: Drop Account_Previous if it exists.
    08:01:38: Create Account_Previous with new structure.
    08:01:38: Run the DBAmp.exe program.
    08:01:38: DBAmp Bulk Operations. V3.1.3 (c) Copyright 2006-2015 forceAmp.com LLC
    08:01:38: Populating local table Account_Previous , VNDVWSQL05D\DEV5A / TEST_SALESFORCE .
    08:01:39: DBAmp is using the SQL Native Client.
    08:01:39: Opening SQL Server rowset
    08:01:40: Using the bulkapi with polling every 60 seconds.
    08:01:41: Job still running.
    08:01:57: Job Complete.
    08:01:57: Drop Account if it exists.
    08:01:57: Rename previous table from Account_Previous to Account
    Caution: Changing any part of an object name could break scripts and stored procedures.
    08:01:57: Create primary key on Account
    --- Ending SF_Replicate. Operation successful.

    2. Output from sf_replicate with 'pkchunk,batchsize(25000)'

    --- Starting SF_Replicate for Account
    08:09:06: Drop Account_Previous if it exists.
    08:09:06: Create Account_Previous with new structure.
    08:09:06: Run the DBAmp.exe program.
    08:09:06: DBAmp Bulk Operations. V3.1.3 (c) Copyright 2006-2015 forceAmp.com LLC
    08:09:06: Populating local table Account_Previous , VNDVWSQL05D\DEV5A / TEST_SALESFORCE .
    08:09:06: Error: Invalid batchsize. Must be between 1 and 2000
    08:09:06: Error: DBAmp.exe was unsuccessful.
    08:09:06: Error: Command string is C:\"Program Files"\DBAmp\DBAmp.exe Export:pkchunk,batchsize(25000) "Account_Previous" "VNDVWSQL05D\DEV5A" "TEST_SALESFORCE" "SANDBOX_SERVER"
    --- Ending SF_Replicate. Operation FAILED.
    Msg 50000, Level 16, State 1, Procedure SF_Replicate, Line 244 [Batch Start Line 0]
    --- Ending SF_Replicate. Operation FAILED.

    3. Output from sf_replicate with 'pkchunk,batchsize(2000)'

    --- Starting SF_Replicate for Account
    08:11:21: Drop Account_Previous if it exists.
    08:11:21: Create Account_Previous with new structure.
    08:11:22: Run the DBAmp.exe program.
    08:11:22: DBAmp Bulk Operations. V3.1.3 (c) Copyright 2006-2015 forceAmp.com LLC
    08:11:22: Populating local table Account_Previous , VNDVWSQL05D\DEV5A / TEST_SALESFORCE .
    08:11:22: Batch size reset to 2000 rows per batch.
    08:11:22: Error: Invalid options. Valid options are batchsize(x) , partitions(x) jobid(x) and bulkapi .
    08:11:22: Error: DBAmp.exe was unsuccessful.
    08:11:22: Error: Command string is C:\"Program Files"\DBAmp\DBAmp.exe Export:pkchunk,batchsize(2000) "Account_Previous" "VNDVWSQL05D\DEV5A" "TEST_SALESFORCE" "SANDBOX_SERVER"
    --- Ending SF_Replicate. Operation FAILED.
    Msg 50000, Level 16, State 1, Procedure SF_Replicate, Line 244 [Batch Start Line 0]
    --- Ending SF_Replicate. Operation FAILED.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned

  • Can you upgrade to the latest version of DBAmp? The latest version has functionality that should speed up the Replicates considerably. To upgrade to the latest version of DBAmp follow the directions at the following link: http://forceamp.com/upgrade.htm

    Note: take a look at the Replicating Large Tables section in Chapter 3 and the DBamp Work Directory section in Chapter 10 of the DBAmp doc here: http://forceamp.com/hats/DBAmpDoc.pdf

    Let us know if you have questions.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned

  • It appears my DBAmp version and the documentation I am using are out of synch. Most of the features I attempted to speed up replication were introduced in later releases. Doh.

    I will check to see if a DBAmp upgrade can be added to the roadmap.

    Thanks for the feedback
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned