Replicating Data from One DB to another

Question

Im wondering if you could point me to a few possibilities in the best way to move/replicate data from one database on a different server to my database.

Ideally any update on the other database would be immediately pushed to mine.

Another thing i was thinking about ok lets say the master database has 100 tables I could build a process to move the data from the 100 tables to mine i.e have the same 100 tables in my database

Or I could buy some views on the master database which would only carry the essential data i need, and then look at the best ways to move the data from these views to my database

Any help would be greatly appreciated

EDIT : Using SQL Server 2008

Answer 1

An alternative could be using triggers. It's a bit dependant on how much data you're dealing with, but, once you'd snapshotted the database and copied it, each update could be captured in the trigger and each update the remote server (using Linked Servers) or (probably better) write it to a holding table, which you could move in one go at night, then have another process on the receiving server to update the tables.

Updating linked servers via triggers is, in theory, a dangerous no-no. It's kinda dependant on the amount of data you have, how important to you the process is, and how quickly you need a solution.

Answer 2

Which edition of 2008? If you have the core editions, you can use replications. The other versions require you to be a "subscriber".

Answer 3

You've looked at SQL Server Replication Services right? ;)

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.