Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
velnias2010
Posting Yak Master
125 Posts |
 Posted - 2010-09-29 : 09:09:19
|
| 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 databaseOr 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 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-29 : 09:28:46
|
| Look at transactional replication for this.http://msdn.microsoft.com/en-us/library/ms151198.aspx |
 |
|
|
velnias2010
Posting Yak Master
125 Posts |
Posted - 2010-09-29 : 10:28:50
|
| Hey had a look at that, looks really good.One question though lets say publisher database had 100 tables and I use Transactional Replication to move the data from those 100 tables to Subscriber Database that would be fine.But lets say I dont want the 100 tables but i want to create 3-4 Views which contain the key information I want from those 100 tables. How Would I achieve this.1) Fistly I guess the views need to be created on the publisher database2) Secondly Do i need to create then 3/4 Tables in the Subscriber database which have the same columns as the view from publisher database.3) What sort of replication or maybe even SSIS or something to move the data from the publisher view to subscriber database |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-29 : 11:31:43
|
| I replicate the tables referenced by the views.If you run through the publication wizard, you'll find that replication is really easy to set up.Best practice dictates that subscribers should be treated as read only. You can't actually make 'em read only, else replication would fail. I mention this because inserts/deletes against subscribers can cause issues -- especially when identity columns are involved.The decision on how to move/synch the data depends on the latency you can tolerate. In your initial post, you said "immediately." That makes transactional replication your best option. If the need is "nightly" then you'll want to look at either snapshot replication or SSIS.If write operations must occur at the subscriber, then you need to decide how to reconcile them against the publisher. This may mean Peer to Peer replication, or maybe SSIS. |
|
|
|
|
|
|
Replicating Data from One DB to another