Help replicating to central site
-
At my company we are trying to replicate the ERP's Database from different locations (USA, Mexico, Belgium, Spain, etc...) to a central location in Chile. The databases in the central location are used for read-only purposes (querying and building a data warehouse).
It seems none of the SQL Server's replication methods work for my purposes since:
1) The snapshot creates a lock in the source tables (while taking the snapshot) and later it locks the destination (while writing the snapshot). I can't have the databases offline, especially the original ERP databases.
2) The transactional replication requires that all tables have primary keys (which is not the case and I can't change the tables).
3) The merge is like #2
Am I right in my conclusions? Are there any other third-party tools that might work? Will a file replication software like RepliStore work for me?
If anybody has any experience doing anything like this please let me know. I'll appreciate it very much.
Thanks.
-
How "up-to-date" does the central database need to be? If 'next day' is OK, you could do something like log shipping every night to update your central repository. Since things are happening in off hours you should need to worry a lot less about locking issues.
Steve G.
-
The problem is that not all of our locations are on the same time zone, so let's say in Belgium 11 PM (good time to start replicating) in Chile it will be 4 PM and they will be using the central (target) database.
Does logshipping lock the source or the target, or both?
The local ERP database can be locked during local off hours, but the central database should never be locked since the whole world will use it.
The central database doesn't HAVE to be "up to date" in real time, but it is better to be as close as possible.
-
Use transactional replication with the concurrent snapshot option. This will minimize the locks held on your erp database so that the users can continue to work on it.
You need PK's with transactional replication - it is highly likely you can extend this tables to add primary keys. However this probably is not an option for you.
Merge adds a rowguid column to the tables it is replicating but it doesn't require pk's.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
-
I wish I wouldn't have to alter my ERP's database tables.
Has anybody used third pary tools like RepliStore, Metilinx Replicator or Availl Database Backup?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply