MS SQL Server - SQL Server Replication Issue

Author

SQL Server Replication Issue

maxvalery@gmail.com

2006-12-12, 7:12 pm

Hi,

I have a SQL Server 2000 server in Location A. I have another SQL
Server 2000 in Location B about twenty miles away. I need to create an
exact copy of a database from Location A on my Location B server EVERY
NIGHT.

Right now, Location A runs a DTS job to export ALL Location A tables as
text files and posts them on an FTP server. Then the Location B server
downloads all files and runs a DTS package to import files into the
Location B tables.

Everything was fine and dandy until daily data grew to 7Gb - now it's
simply unmanageable. Text file imports take too long, FTP downloads
take even longer, and we need a dedicated person just to manage
synchronization between Location A and Location B database servers.

Is there a way to replicate Location A database on the Location B
server without exporting/uploading/downloading/importing?

David Browne

2006-12-12, 7:12 pm

<maxvalery@gmail.com> wrote in message
news:1165935070.110058.287400@j44g2000cwa.googlegroups.com...
> Hi,
>
> I have a SQL Server 2000 server in Location A. I have another SQL
> Server 2000 in Location B about twenty miles away. I need to create an
> exact copy of a database from Location A on my Location B server EVERY
> NIGHT.
>
> Right now, Location A runs a DTS job to export ALL Location A tables as
> text files and posts them on an FTP server. Then the Location B server
> downloads all files and runs a DTS package to import files into the
> Location B tables.
>
> Everything was fine and dandy until daily data grew to 7Gb - now it's
> simply unmanageable. Text file imports take too long, FTP downloads
> take even longer, and we need a dedicated person just to manage
> synchronization between Location A and Location B database servers.
>
> Is there a way to replicate Location A database on the Location B
> server without exporting/uploading/downloading/importing?

Sure, first off you can just use a backup instead of text files. Take a
full backup of the database at location A, copy that and restore it at
Location B.

You can do even better, though. Once you have the backup restored at
Location B, you can take a Transaction Log backup from Location A, copy that
and apply that at location B. This is called "Log Shipping", and it's a
supported feature in SQL 2005 and SQL 2000 Enterprise, although you can
acomplish the same thing with just backup/restore and file copies.

David

maxvalery@gmail.com

2006-12-12, 7:12 pm

Thanks for the speedy reply. A few questions remain:

1. What would 'log shipping' accomplish?
2. Can I write a backup procedure programmatically? DTS package?
Needs to be seamless.
3. If the resulting export/import table files are ~7Gb, would backup be
at least that?
4. What's the operational difference between uploading one gigantic
backup file and uploading a bunch of smaller text files?

I really appreciate your help.

David Browne

2006-12-12, 7:12 pm

<maxvalery@gmail.com> wrote in message
news:1165952438.643858.249450@73g2000cwn.googlegroups.com...
> Thanks for the speedy reply. A few questions remain:
>
> 1. What would 'log shipping' accomplish?

You would only be moving changes, not a new copy every day.

> 2. Can I write a backup procedure programmatically? DTS package?
> Needs to be seamless.

Yes. Using DTS, and or batch files, OSQL, SqlCommand, SSIS you can automate
the whole process.

> 3. If the resulting export/import table files are ~7Gb, would backup be
> at least that?

You already have backups of the database, right? Just look at them.

> 4. What's the operational difference between uploading one gigantic
> backup file and uploading a bunch of smaller text files?
>

Not much, I think.

David

Tracy McKibben

2006-12-13, 7:12 pm

maxvalery@gmail.com wrote:
> Thanks for the speedy reply. A few questions remain:
>
> 1. What would 'log shipping' accomplish?

Log shipping uses backups of your transaction log to "replay"
transactions that have occurred. So, you'll take a backup of the
transaction log from Database A, restore it to Database B, and any
transactions that were contained within that backup will be reproduced
in Database B.

> 2. Can I write a backup procedure programmatically? DTS package?
> Needs to be seamless.

Yes. Read about BACKUP DATABASE, BACKUP LOG, RESTORE DATABASE, and
RESTORE LOG in Books Online.

> 3. If the resulting export/import table files are ~7Gb, would backup be
> at least that?

Possibly, but not necessarily. A database backup is a copy of the
database pages that contain data. Using a backup gives you the option
of compressing it (SQL backups zip up really well) before sending it via
FTP.

> 4. What's the operational difference between uploading one gigantic
> backup file and uploading a bunch of smaller text files?

It's not so much the number of files, it's the process of importing
those text files that is slowing you down. Restoring a backup will be
MUCH faster than importing rows of data.

>
> I really appreciate your help.
>

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Sponsored Links