Having an updated base every day

Hello,
I have two symmetrical databases on two SQL servers - one remote, the
other - local. The data in the remote server changes every minute. The data
in local SQL server is used for creating some reports and general testing.
Sometimes data from the local base can be irreversibly deleted.
I want to have some sort of procedure, that would do one-direction
synchronisation of the two databases. The data in the remote database should
overwrite the data in the local database, so that after the update the local
database is exact copy of the remote database. Of course, this kind of
result could be achieved by detaching the remote base and bringing over the
database files, or doing full DTS. However, detaching and transfering is not
a viable solution since the remote base is actively used and the size of
data files are considerable; And, doing full DTS would take years. There is
some need to do some comparison of record hashes etc., so that the size of
the data that have to be transfered is reduced to minimum.
Thanks,
Pavils
The first thing that comes to mind is BACKUP and RESTORE. After you restore
a database backup you will have an exact copy of the original (as of the
date and time the backup completed).
You mention that the size of the data files are "considerable." I am not
sure what that means, or if that is a limitation for you in your
environment.
You might be able to look into replication.
Another option: if you have datetime columns (such as DateUpdated or
DateInserted) stored on your tables you could create your own shipping
routines...simply find the "recent" data (whatever that means to you) and
insert it.
You could also use linked servers and INSERT INTO...SELECT...WHERE NOT
EXISTS... you will have to fill in the blanks, but they should be fairly
easy.
Keith
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:OsrHETCkEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have two symmetrical databases on two SQL servers - one remote, the
> other - local. The data in the remote server changes every minute. The
data
> in local SQL server is used for creating some reports and general testing.
> Sometimes data from the local base can be irreversibly deleted.
> I want to have some sort of procedure, that would do one-direction
> synchronisation of the two databases. The data in the remote database
should
> overwrite the data in the local database, so that after the update the
local
> database is exact copy of the remote database. Of course, this kind of
> result could be achieved by detaching the remote base and bringing over
the
> database files, or doing full DTS. However, detaching and transfering is
not
> a viable solution since the remote base is actively used and the size of
> data files are considerable; And, doing full DTS would take years. There
is
> some need to do some comparison of record hashes etc., so that the size of
> the data that have to be transfered is reduced to minimum.
> Thanks,
> Pavils
>
|||Hello Keith,

> You mention that the size of the data files are "considerable." I am not
> sure what that means, or if that is a limitation for you in your
> environment.
The idea is that I don't want to transfer every single bit of the whole
database.

> You might be able to look into replication.
Would replication allow to create a fully matching copy wit using as little
data xfer as possible?

> Another option: if you have datetime columns (such as DateUpdated or
> DateInserted) stored on your tables you could create your own shipping
> routines...simply find the "recent" data (whatever that means to you) and
> insert it.
That could be a good solution, but it requires reworking much of the current
application.

> You could also use linked servers and INSERT INTO...SELECT...WHERE NOT
> EXISTS... you will have to fill in the blanks, but they should be fairly
> easy.
Surely, I can do that, however I was hoping for some more automatic (or
wizard-style) solution.
-- Pavils.
|||"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:uUQ1L7CkEHA.3968@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hello Keith,
not
> The idea is that I don't want to transfer every single bit of the whole
> database.
understood

>
> Would replication allow to create a fully matching copy wit using as
little
> data xfer as possible?
That is the point of replication
[vbcol=seagreen]
and
> That could be a good solution, but it requires reworking much of the
current
> application.
Probably so (if you don't have a way to identify "new" rows.
[vbcol=seagreen]
fairly
> Surely, I can do that, however I was hoping for some more automatic (or
> wizard-style) solution.
This is not much different than my previous idea...it simply requires you to
type up some Transact-SQL. Hey, no one said being a dba/developer/IT
Professional was easy.

> -- Pavils.
>
Keith