| Author |
Topic |
|
delta
Starting Member
4 Posts |
 Posted - 2008-03-15 : 11:08:57
|
| I have a replication question. I have a rather large transaction database, that until now received only transactions from a local application. At the moment we're deploying the same application at pretty much the other side of the world.There is a VPN connection between both locations, but the application will run locally and use the local database for latency reasons. Now I want to maintain a central database (for reporting purposes). What is supposed to happen is the following. Both databases will receive new transactions and updates to existing ones, each by its own local application, but I want the transactions of the remote database (and updates to existing transactions) to be merged into the main database (but not other way around!). There will be no conflicts between these transactions, since each transaction ID will be prefixed with a location ID.Initially I thought I could use SQL Server's merge replication, but this requires both databases to contain the same contents. This is not what I want, since the main database is far bigger than the remote database is supposed to become. Also I do not need the transactions from the main database to be in the remote database.As an alternative I was thinking along the following line: replicate (using transaction replication) the remote database to the central database server in a separate database and create a view that UNIONs both databases. Then insert and update in the physical databases, but use the view for reporting purposes. I'm a bit afraid such a view will be much slower. Perhaps this is an irrational fear since the underlying databases will be correctly indexed? Also this would mean some changes in the application.Anyway, I was hoping for some feedback on this. Perhaps there are other ways to make the above happening? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-15 : 12:27:34
|
| I really didn't get clear picture of what you want. You don't need Merge replication for your scenerio. I would only replicate the main tables that are used for reporting services instead of migrating whole database. |
 |
|
|
delta
Starting Member
4 Posts |
Posted - 2008-03-15 : 12:49:34
|
| I'm sorry if I wasn't entirely clear. The database is mainly one table containing the transactions. There used to be just one database. The reason a second one exists at a remote location is for latency reasons only. I would rather have that application use the central/first database. However, the 300/400ms latency would make its performance unacceptable.This is why the second/remote database exists (with an identical schema). My idea was to stream all inserts and updates from the second database into the central database (which of course receives its own inserts/updates as well). This is basically what I want. I do not need the transactions from the central database (>270,000,000) on the remote location, but I want the transactions of the remote location in my central database. How do I do this without having a snapshot applied to either of the databases (which is what all SQL Server's replication methods seem to require)? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-15 : 13:15:05
|
| How big is the remote database as of right now? |
|
|
|
delta
Starting Member
4 Posts |
Posted - 2008-03-15 : 13:22:47
|
| oh, the remote database is right now very small. Just a few test transactions. It won't be used until I've resolved this issue. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-15 : 13:56:55
|
| The best solution will be :1) create new table in Central database( with different name but same schema)2) Start transactional Replication:while you are doing Publication - click article highlighted properties: choose your destination table name(make sure put different table name)3)This will start tran replication from remote to central without downtime. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-15 : 15:56:34
|
| You don't need replication if don't need real time reporting. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-15 : 20:55:00
|
| Agreed. you need Primary key for Tran rep? |
|
|
|
delta
Starting Member
4 Posts |
Posted - 2008-03-16 : 02:49:06
|
| Thanks for the feedback. I do need the replication into my central database though. This is because the application that runs centrally will show the latest transactions that it reads from the central relational database to users.The transactions from the remote database need to be there as well, and real time at that (well a few seconds delay is acceptable).Apart from that there's also reporting using Analysis Services going on, though that could conceivably be solved by using the remote OLTP database as a source. But the first requirement cannot.Nonetheless I found the answer in the mean time. Apparently when you use transactional replication, select 'Allow initialize from backup' in the publication on the remote database, and make the subscription on the central database WITHOUT initializing using the wizard in Management Studio, it does exactly what I want: all new transactions are replicated to the central database (in the same table as the transactions from the local application).Of course that does not cover any existing transactions in the remote database from before the replication, but that I can manage. The first time is no problem (since there are no previous transactions). If I need to restart replication later on I can always run a compare manually to update any discrepancies. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-16 : 09:54:24
|
| you need to run full backup and tran backup if you want to 'initialize with backup option'. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-16 : 17:15:35
|
| Not sure you want to do this if use single db on central server. |
|
|
|
|
one way merge replication