Baanboard.com

Baanboard.com (http://www.baanboard.com/baanboard/index.php)
-   Operating Systems & Databases (http://www.baanboard.com/baanboard/forumdisplay.php?f=4)
-   -   Sync tables between 2 databases (http://www.baanboard.com/baanboard/showthread.php?t=70228)

Ivanus 19th July 2017 14:39

Sync tables between 2 databases
 
Hi, we are having a production system and on a separate Server our Testsystem. Both are having the same OS and the same Version of SQL Database.
To refresh our Testsystem from time to time I was thinking about to synchronize some tables we need from the production system to the Testsystem.
I don't want to copy the whole database because then I have to make a lot of adjustments on the Testsystem again.
Any idea how this could work?
Thanks

mark_h 19th July 2017 21:54

I would not do this for test - but I am not a DBA, but stuff gets out of sync and it makes it hard to test. But it should work assuming you get all the necessary tables and the referenced tables - I am thinking from a 4c4 perspective. What we do is we actually have another copy where we do not load certain tables (like tfgld tables) but we know for a fact those tables are not needed in the simulation company. All this really does is make the cloning faster for that company. Just my opinion.

Ivanus 20th July 2017 11:06

Hi, we also need the finance tables in our Testsystem (it's also our Development System).
I also tried it with the common way to dump in BaaN the whole company out but this takes 15 hours and than I have to import it in the Testsystem and this will also take more than 15 hours.
Any other suggestion?
Thanks

jeffersyuan 21st July 2017 13:23

Hi,

To myself, I would like you to write a small program to do the sync job.

Indeed, I just finish one small tool which support copy company data between two different database, for example, from SQL to Oracle, from sql to sql, from oracle to sql, from oracle to oracle etc.

Jeffers

Quote:

Originally Posted by Ivanus (Post 203903)
Hi, we also need the finance tables in our Testsystem (it's also our Development System).
I also tried it with the common way to dump in BaaN the whole company out but this takes 15 hours and than I have to import it in the Testsystem and this will also take more than 15 hours.
Any other suggestion?
Thanks


toolswizard 9th August 2017 15:24

We have a 2tb database on Oracle. Originally we did the dump/load from oracle and it would take 5 to 7 days.

I broke the seq dump down into segments, the largest files in their own job, the smaller ones in groups. I got the job down to 30 hours. We have faster processors now on the production system and run 10 jobs at a time, getting the dump down to 2 1/2 hours from 20. Total time now is about 18 hours total. Loading is slower as the processors and memory are less, and we only run 6 to 8 jobs at a time.

If you did the same, breaking down the total tables into several jobs, you should be able to get it down in a few hours.

GaryEd 14th August 2017 19:35

This is how we do it

Notes:
Be very careful doing this as it is very easy with one simple typo to wipe out a whole lot of data.
Before beginning make sure table sharing between test companies matches production companies.
Running the exports (step 5) will lock the table during the dump so it must be run when no users are on the system (including jobs).
1. Run session ttaad4222m000 (count number of records by table) for each production company. Output to a text file.
2. Run baan sql query “logical”. Output to a text file. (this query is specific to us but basically gets a list of logical tables to be able to filter them out of the lists of tables to dump)
3. Using MS Access (or some other method) create a text file for each company with a list of tables that are not a logical table and that contain data. Save the file as XXXseqdump.txt in the E:\baan\bin folder (where XXX = company number). The idea is to only dump tables that contain data and to not dump data that is physically in another company so that the process runs as quickly as possible.
4. From a command prompt on the server change to the E:\baan\bin folder and run the following to override the normal default settings. This will help the process run quicker (per baan support).
fillenv
Set USER=baan
set RDS_FULL=100
set SSTS_SET_ROWS=1000
set MSQL_ARRAY_FETCH=1
set MSQL_ARRAY_INSERT=1
set MSQL_MAX_ARRSZ=100
5. Run the following command for each company in turn (where XXX = company number).
bdbpre -t"|" -IXXXseqdump.txt -oE:\seq_dumps\XXX –CXXX
6. Run baan session tccom0214m000 (from file/run) in each source company. The directory must match the folder where you dumped the data (see step 5). You must specify all prod company to test company mappings. The field is not big enough to do it all at once so you need to split it in two and run the session twice. The format is prod1>test1;prod2>test2 (separator is semi-colon).



7. Run session ttaad4231m000 to delete all tables in each test company. You will get a report stating that logical tables could not be deleted, this is ok. This makes sure that there is no orphan data in tables that are not populated in the production company.
8. From command prompt run following for each company (where XXX = source/prod company and YYY = target/test company).
bdbpost -t"|" -kmnf -DE:\seq_dumps\XXX -cYYY
9. Run session ttaad4230m000 to create remaining empty tables in each test company.
10. Log in to test company and make sure all is ok.


All times are GMT +2. The time now is 05:07.


vB.Sponsors
©2001-2017 - Baanboard.com - Baanforums.com