Best way to push this data? (LONG)

There's a lot of explanation involved in this, so bear with the long
post:
Our company has offices in the US and China, and an AS400 (where the
data originates) in the US, and 3 SQL boxes (which mirror the AS400
data, with the odd extra field thrown in) - 2 in the US, and one in
China.
We're changing our item number scheme from a numeric field to a 25-
character char field. We're doing this over Memorial Day weekend...
we're dropping replication and everything on Friday, and then the AS400
starts its conversion process, and sometime late Saturday or early
Sunday the conversion process will be done on the AS400, at which point
we start copying the data over to our local SQL box, and then over to
China.
China starts its day Sunday night at 7pm. For that day, they will be in
read-only mode, so that they won't be writing changes to their old,
outdated tables. The downside to being read-only is we also cannot push
data to them (since they're using the data) until their end-of-day at
approximately 4am Monday. This gives us a very small window to push the
data over, re-establish replication, make sure everything works fine,
etc. By the way, I'm talking about several gig worth of data, at least
12gb and as much as 20gb.
One of the suggestions was to create a scratch-db containing all the
data, and disconnecting that db and transferring the mdf & ldf files
over the pond during the middle of our night, so at 5am they would be
sitting there ready for us. We could then detach our existing db, and
attach the new one in its place. The problem with that suggestion is
that once we re-establish replication, how would it know which data
already exists in China? Our fear is that replication will drop and re-
create the tables and start pushing from scratch, which defeats the
purpose of pushing the mdf/ldf files.
Another suggestion was to set up another box over there with SQL, and
putting the tables there and establishing replication to that machine...
and when China stopped for the day, changing the DNS mappings to point
to the new SQL box instead of the old box. However that suggestion
makes too much sense, and therefore will probably be shot down by
management.
I guess the nutshell version of my question is this: What's the fastest
way to get that large an amount of data over to China, and ready to
replicate without error?
Regards,
ScottI don't envy your job, but here goes a couple of
sugestions.
1) Don't send over your files. Back up the entire
database up, zip up the file then send that file over to
China. In China unzip it then use the restore from file
option.
Advantage - One file, reasonable safe transfer
Disadvantage - Restores are not the most stable of things.
Large file size being transfered. You will lose the extra
fields.
2) Change your replication to Transactional on the server
which is going to perform the upgrade. Ensure that the
location where the transactional files is accessable to
all servers.
Advantage - This will only log the changes to the data so
file size is not too big, it sould be faster to transfer.
Disadvantage - Its replication, and is sometimes not very
easy to do.
3) You can just send over the datafile, rather than both.
There is a command something on the lines of
db_attach_single_file (check it up on bol), again zip it
up and send it.
As you can see each has its advantages and disadvatages.
Personally I would go for 3 if there is time after the
update to send it to china before china comes on read-
write, otherwise 2, replication.
Anyway keep me posted on what happens. My email is
little_flowery_me@.hotmail.com.
J
>--Original Message--
>There's a lot of explanation involved in this, so bear
with the long
>post:
>Our company has offices in the US and China, and an
AS400 (where the
>data originates) in the US, and 3 SQL boxes (which
mirror the AS400
>data, with the odd extra field thrown in) - 2 in the US,
and one in
>China.
>We're changing our item number scheme from a numeric
field to a 25-
>character char field. We're doing this over Memorial
Day weekend...
>we're dropping replication and everything on Friday, and
then the AS400
>starts its conversion process, and sometime late
Saturday or early
>Sunday the conversion process will be done on the AS400,
at which point
>we start copying the data over to our local SQL box, and
then over to
>China.
>China starts its day Sunday night at 7pm. For that day,
they will be in
>read-only mode, so that they won't be writing changes to
their old,
>outdated tables. The downside to being read-only is we
also cannot push
>data to them (since they're using the data) until their
end-of-day at
>approximately 4am Monday. This gives us a very small
window to push the
>data over, re-establish replication, make sure
everything works fine,
>etc. By the way, I'm talking about several gig worth of
data, at least
>12gb and as much as 20gb.
>One of the suggestions was to create a scratch-db
containing all the
>data, and disconnecting that db and transferring the mdf
& ldf files
>over the pond during the middle of our night, so at 5am
they would be
>sitting there ready for us. We could then detach our
existing db, and
>attach the new one in its place. The problem with that
suggestion is
>that once we re-establish replication, how would it know
which data
>already exists in China? Our fear is that replication
will drop and re-
>create the tables and start pushing from scratch, which
defeats the
>purpose of pushing the mdf/ldf files.
>Another suggestion was to set up another box over there
with SQL, and
>putting the tables there and establishing replication to
that machine...
>and when China stopped for the day, changing the DNS
mappings to point
>to the new SQL box instead of the old box. However that
suggestion
>makes too much sense, and therefore will probably be
shot down by
>management.
>I guess the nutshell version of my question is this:
What's the fastest
>way to get that large an amount of data over to China,
and ready to
>replicate without error?
>Regards,
>Scott
>.
>