replication of certain tables

[Paulvtis]

is it possible to replicate, using 'replication' function, only certain tables every night?

but then, only the changes within the day will be replicated not all contents of the table.

i know there is such thing if we will replicate the whole DB, but what if i will just replicate let us say 100 tables within the said DB is this possible?

tnx

 

[nigelrivett]

You can create a publication that only includes certain tables - you can also create a filter so that not all data in those tables is replicated.

There is a big administration overhead with replication though and I prefer to code it myself.

You can make a single server publisher, subscriber and distribtion to test.
See bol there is lots of info there.

You sound a bit like you want to take a snapshot every night then transactions during the day.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Paulvtis]

1.) can i filter only to what has been modified? meaning those are the only ones that will be replicated.

2.) can you pls elaborate what you mean by the administration overhead? can you specify samples?

3.) This is because we will have a 'transaction server' for daily entry of trx then another 'analysis server' for reporting. Therefore we need to replicate what has been done to the 'trnasaction server' to the 'analysis server' to update the reports and therefore will only have 1 day delay of information.

any suggestion given what we want?

tnx

 

[nigelrivett]

How big is the database?
Everything would have to start with a restore of a backup

Simplest would be backup the database - copy then restore every night.

start with a restore then take differential backups and restore those.

Start with a restore and restore log file backups taken periodically (log shipping) = also take differentials as a safeguard. A log shipping solution is supplied but it's so simple to code that I would advise implementing it yourself.

Start with a restore and set up transactional replication - this means that all changes made to the source database are copied to the distributor then applied to the destination.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.