Can I solve this using replication? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Can I solve this using replication?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-14 : 08:29:27

Geoff writes "Hi,

I have the following problem:

I have a production (Goldmine) SQL database here in the USA.
I would like to have a copy of the database available at an
office in China. The copy in China can be out of date by up
to a week. There is no special connection between the two
offices. Any traffic must pass across the public internet.
I need hardly point out that because of the distance this is
VERY SLOW.

What is the simplest and most reliable way to achieve this?

Additional information:
The production database server is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: )

The target server in China will be the same.

The database is a Goldmine Sales and Marketing 5.7 database.
Currently it is 4.425 GB in size containing ~240,000 records.
Note: the tables in this database have *no primary keys*.

Thanks for any suggestions.
Cheers,
Geoff"

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-14 : 10:00:12

Based on the your comments above I'm assuming that the China copy can be read only... #2 is probably the simplest.

1. A customized version of log shipping. You can have a read only copy (able to restore additional t logs) in China. Of course this assumes you're in full recovery mode. You'll have to ensure that you're backing up the transaction log, send that backup to your China office, and restore. You should have a maintenance plan or job that backs up your transaction logs. The transaction logs should (but may not be) smaller than a full backup so emailing of FTPing should be quicker, you could always zip the backup(s). You can write a job to apply the transaction log and keep the DB in Read only and able to restore additional transaction logs.
2. Full back up, copy, and restores on a xxx basis, probably weekly. Zip the files to help with transmission times.

Of course there are other questions:
1. What's the purpose of the China copy? Do you need the complete DB or only certain tables? More work, but if you only need a handful of tables, you could bcp out, copy file, trunc china tables, bcp in.
2. Are you 24 x 7? Do you have regular maintenance windows?

glt101
Starting Member

28 Posts

Posted - 2006-02-21 : 15:56:53

Hi Joe,

Thanks very much for the reply. Sorry I didn't see it before...
-no e-mail notification for some reason. I guess its because
SQL Team posted for me.

Some answers:
* Yes, the database in China could be read only.
* The purpose is so that the China office can do data mining
over-night. They don't need to write to the DB.
* I think we need the whole DB.
* We are 24/6, mostly, but that window does close on some
occasions.

My Questions:
#2 is the way I would like to go, because it is the simplest
-the China office has minimal SQL expertise. But, even the
compressed Backup file is 1.2GB. Never even bothered to try to
ftp that over there. Assuming that we can get the file there,
I am not sure about the automation of this. Could we just use
command line ISQL at the China end to do a restore? Or were you
assuming some kind of DTS connection? We will try a test ftp to
see how long it takes.

#1 If we do the Transaction log approach, does the China DB need
to receive regular full backups too? Or can we just keep adding
the transaction logs forever?

Thanks again,
Geoff

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-22 : 10:21:57

1. You just keep applying transaction logs. Your users will be disconnected while the log is restoring (but you run into the same issue with #2). I don't think I even asked what recovery model your DB is in, but it would have to be full. Depending on your activity transaction logs may or may not be smaller.

2. This method is a simpler. There's a number of options for automating a restore, you could use isql, a DTS, a job, a script... Not sure how you want to handle this. I'd probably try a scheduled batch file on the China server.

Sounds like the biggest issue is transmitting the data.

Good luck.

Doug_Castell
Starting Member

3 Posts

Posted - 2006-06-23 : 13:40:26

Sorry for the OLD thread response, but there is a different path that could be better than replication for this instance..

GoldMine has it's own synchronization fuction built-in that could keep that remote site up-to-date with incremental syncs of just the changed data on a nightly (hourly?) basis and would actually allow them to update the data in a two-way manner... As for intially getting the data there, GoldMine can create an installation file, complete with the data to be copied to a portable hard drive or whatnot and shipped out there.

Given remote access to a server connected to a portable hard drive with such data on it, a decent GoldMine consultant ought to be able to get the remote system in China set up to stay updated.

--
Doug Castell
GoldMine Guru
www.castellcomputers.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources