Professional Web Applications Themes

Full-Text Change Tracking and Updates via BCP. - Microsoft SQL / MS SQL Server

OK - this is a bit of a cliff-hanger as we're just 2 weeks away from launch. Here's the scenario: 1) I have a production database in Bangkok which contains our product catalog. Updates are being performed regularly throughout the day. (SQLServer 2000 sp3). The database is currently about 100Mb in size and growing. 2) I have an online Web database in Singapore in our datacenter which contains an exact copy of the production database (also SQLServer 2000 sp3). 3) I only have a low-bandwidth link to the online database in Singapore from Bangkok (128k if I'm lucky - maybe ...

  1. #1

    Default Full-Text Change Tracking and Updates via BCP.

    OK - this is a bit of a cliff-hanger as we're just 2 weeks away from launch.

    Here's the scenario:

    1) I have a production database in Bangkok which contains our product
    catalog. Updates are being performed regularly throughout the day.
    (SQLServer 2000 sp3). The database is currently about 100Mb in size and
    growing.

    2) I have an online Web database in Singapore in our datacenter which
    contains an exact copy of the production database (also SQLServer 2000 sp3).

    3) I only have a low-bandwidth link to the online database in Singapore from
    Bangkok (128k if I'm lucky - maybe via VPN - FTP is also available)

    4) One of our product tables has a full-text index on it with
    change_tracking and background_updateindex enabled.

    5) All of the important tables in our production database - including the
    table with the Full_Text index, have an update trigger that updates a Column
    called UpdatedDate with the DateTime that the row was either added or
    updated (there's also a CreatedDate column). Deletions do not matter at the
    moment.

    6) I need to send updates from Bangkok - to Singapore, once a day - or maybe
    only once every couple of days (high autonomy and high latency). The online
    DB in Singapore is effectively a read-only DB so the updates are one-way.

    7) I've looked at the various types of tion and I think we are going
    to write our own simple process for updating the on-line database using BCP.
    The process will take all the changes (UpdatedDate) since the process was
    last run and export them in native format. We'll FTP the file to Singapore
    and import it into the online database.

    Here's the 64,000 dollar question. Will new and updated rows inserted into
    the online DB in Singapore via BCP also cause the change_tracking and
    background_update index to fire? Or will they be ignored? (even if we were
    using Snapshot tion - BCP is doing the job in the background I think
    so this question would still apply).

    This is a fairly critical point for us so any advice or technical
    information would be greatly appreciated.

    Cheers,

    Tony





    Anthony Bouch Guest

  2. #2

    Default Re: Full-Text Change Tracking and Updates via BCP.

    Actually we're not worried about triggers on the on-line database.

    What we need to know is if the Full_Text catalog will update when change
    tracking is enabled and data is imported into the table via BCP.

    Perhaps a better question would be - how does full text services monitor the
    table?

    "Wayne Snyder" <wsnydercomputeredservices.com> wrote in message
    news:#cb19XvPDHA.1556TK2MSFTNGP10.phx.gbl...
    > By default triggers to not fire, but there is an option FIRE_TRIGGERS
    which
    > causes the triggers to fire... This is doc'd in books on line search for
    > "bcp utility, accessing and changing data"
    > "Anthony Bouch" <tonynospamforever.com> wrote in message
    > news:uUEjbPuPDHA.1552TK2MSFTNGP10.phx.gbl...
    > > OK - this is a bit of a cliff-hanger as we're just 2 weeks away from
    > launch.
    > >
    > > Here's the scenario:
    > >
    > > 1) I have a production database in Bangkok which contains our product
    > > catalog. Updates are being performed regularly throughout the day.
    > > (SQLServer 2000 sp3). The database is currently about 100Mb in size and
    > > growing.
    > >
    > > 2) I have an online Web database in Singapore in our datacenter which
    > > contains an exact copy of the production database (also SQLServer 2000
    > sp3).
    > >
    > > 3) I only have a low-bandwidth link to the online database in Singapore
    > from
    > > Bangkok (128k if I'm lucky - maybe via VPN - FTP is also available)
    > >
    > > 4) One of our product tables has a full-text index on it with
    > > change_tracking and background_updateindex enabled.
    > >
    > > 5) All of the important tables in our production database - including
    the
    > > table with the Full_Text index, have an update trigger that updates a
    > Column
    > > called UpdatedDate with the DateTime that the row was either added or
    > > updated (there's also a CreatedDate column). Deletions do not matter at
    > the
    > > moment.
    > >
    > > 6) I need to send updates from Bangkok - to Singapore, once a day - or
    > maybe
    > > only once every couple of days (high autonomy and high latency). The
    > online
    > > DB in Singapore is effectively a read-only DB so the updates are
    one-way.
    > >
    > > 7) I've looked at the various types of tion and I think we are
    > going
    > > to write our own simple process for updating the on-line database using
    > BCP.
    > > The process will take all the changes (UpdatedDate) since the process
    was
    > > last run and export them in native format. We'll FTP the file to
    Singapore
    > > and import it into the online database.
    > >
    > > Here's the 64,000 dollar question. Will new and updated rows inserted
    into
    > > the online DB in Singapore via BCP also cause the change_tracking and
    > > background_update index to fire? Or will they be ignored? (even if we
    were
    > > using Snapshot tion - BCP is doing the job in the background I
    > think
    > > so this question would still apply).
    > >
    > > This is a fairly critical point for us so any advice or technical
    > > information would be greatly appreciated.
    > >
    > > Cheers,
    > >
    > > Tony
    > >
    > >
    > >
    > >
    > >
    >
    >

    Anthony Bouch Guest

Similar Threads

  1. Tracking value change in Label control
    By aSuitableBoy in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: May 9th, 03:02 PM
  2. tracking text
    By Jim_Slim@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 4
    Last Post: September 18th, 03:17 PM
  3. Tracking create and change time
    By André Hänsel in forum MySQL
    Replies: 0
    Last Post: March 12th, 10:14 PM
  4. tracking on dynamic text?
    By Brian in forum Macromedia Flash Ad Development
    Replies: 0
    Last Post: March 22nd, 01:19 AM
  5. Send text to a PHP script which updates a .txt file??
    By Nicolaj Tidemand in forum Macromedia Flash Sitedesign
    Replies: 2
    Last Post: October 15th, 01:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139