Replicating Data from One DB to another

Question

Hey Guys,

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

I would create views, with the neccessary permissions on the master database for the key informastion.

On top of that you could consider setting up a nightly batch job to migrate data into your database. It means your database is alaways a day behind, but with the key views in place i mentioned before, it may be a useful solution.

As you indicating there are around 100 tables, copying data from those on the fly over when updates occur will occur an overhead, so you might have to consider alternatives.

EDIT

Furthur to my example you could create the required Views in the live/master database. Set up a linked server on your side, which points to the live/master database... then refer to the view in there by using server.database.dbo.view

Make sure the account you use to link across only has access to what you need.

The key here is setting up a Linked Server to access the required content.

Answer 2

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.

Answer 3

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

Answer 4

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.