Hi,

We are creating sequential Dump from the live server. It tooks around 5/6 hrs. For that we have scheduled the job which runs at night. The sequential dump files size is around 18 GB.
After creating the sequential dump we are creating table from sequential dump on a backup server. This we do once in a week as the creation of table from sequnetial dump tooks 40 hrs.

We want to optimize this restoration time.
Pls help how and what steps or configuration needs to be set on the backup server.
Or is there any alternet method.

Thanks.

It would help if you give the hardware specs of both these servers..

sk

Configuration :
The Backup server is
IBM xSeries@235 XEON 2.4 Ghz x 2, 2.5 GB RAM, 320 GB HDD

The live server is
IBM xSeries@346 XEON 3.6 Ghz x2, 4 GB 4 x 146 GB + 2 x 73 GB HDD

Both Servers configuration are good enough to process the 18 GB data.

Nilesh

Hi there,

are you running one job to create the seq. dump?
You will gain speed by splitting the dump task by running two or more jobs, each working on a different set of tables to be exported.
Same applies to importing, although 'create tables from seq. dump' always takes much longer than 'create seq. dump of table'.
Have you ever tried to sync the two systems via audit? I never tried it, but it should work.
Another solution would be to create the seq. dumps, compare the files with the last set and only import the records which are new/have changed - although deleted records would have to be processed separately with a session that you have to create for this purpose. Should be possible, but never tried this too.

hth

Norbert

From your profile you are using informix The fastest method would be to use the native database tools what about using the unload and load option in informix

Hi Nilesh,

18 GB data and 5/6 hours is too much. I think, tuning application/DB is required. We backing-up 18 GB data in only 30 min. approx. and hardware is too similar to yours i.e. HP ML570.

Okay...do you know MAX_ARRAY_INSERT/FETCH? Search here...dont know it works for informix or not.

regards,

-Suhas

Woa!!!
Now thats what i think should be a trend setter...Care to provide exact details on your Hardware and what sort of backup tool you use and whether you use Raid?

sk

I am talking about seq. backup using ARRAY INSERT/FETCH.

We use RAID 10.

regards,

-Suhas

Hi Suhas,

We are following the below steps for the restoration process

1) Schedule job @ night on live server to create the sequential Dump file from table.
2) Total size of these files is 18 GB.
3) Copy the files to Backup server.
4) Split the sequential files into 5 folders
5) Delete the tables on backup server
6) log out and relogin
7) run the create tables from sequential dump session for these 5 folders - Seperate login per folder.
8) Parameters are set as follows
a) Append if table exists
b) Disable the Domain Constraint
c) Ignore referential Integrity Constraint
d) field seperator "|"

9) To finish the activity of creation of tables from sequential dump tooks 40 hrs.

We are not doing anything on OS / Database level .
How can we optimize the time through BaaN interface.

thanks
Nilesh

Hi Suhas,
I hope you are talking about the baan sequential dump as the search for the keyword "ARRAY INSERT/FETCH" in baanboard gave me no results. Is this a setup done at the database level? It would be great if you could share how you proceed with this.. Last but not least, i take it that this is available on a SQL Server environment and not just on Oracle.


sk

P.S: When you say Raid 10 do you mean Raid 1 and 0?

Hi Nilesh,

Mentioned steps are ok but can be reduced.

40 Hours is a huge time, I advice you to look into fine-and-tune your App/DB as well as hardware/network (If three-tier).

We are not doing anything on OS / Database level .

What's the reason not doing on OS/DB level. You should compare in terms of feasibility/time involved/accuracy/efforts/resources.

Anyway, if you choose BaaN's way, you can optimise/automate it by using batch scripting and scheduler.

1.Create a list of BaaN tables company wise and spool it in 000.txt text file.
(You can more optimise it for not taking zero size tables and taking sharing table at only once, I dont know about SQL and Informix, but I can do for Oracle.)

2.Create a batch file for setting environment variable as user / and or ARRAY FETCH / INSERT.

User bdbpre - I bkp\000.txt -o bkp\dump.000 -q bkp\0.log -E bkp\0.err -C 000

This will create company wise backup so you need not split later.

3. Start xcopy/d/q/h/r/o/y/k/e source_data mapped_drive
Mapped drive is test server, where you would like to post it.
4. Create a schedule and run this script at night.

5. Similar create a batch script using bdbpost and refint (reorganise) on test server and scedule it after copying.

I hope this helps.

You can refer to know more - http://www.baanboard.com/baanboard/showthread.php?t=25713&highlight=reorganise

Hi Sukesh,

Dont know, what you searched.

http://www.baanboard.com/baanboard/showthread.php?t=1818&highlight=MAX_ARRAY_INSERT

http://www.baanboard.com/baanboard/showthread.php?t=7885&highlight=MAX_ARRAY_FETCH


Is this a setup done at the database level?

No..BaaN App level into db_resource.

When you say Raid 10 do you mean Raid 1 and 0?

Yes...It's 1 (Mirroring) and 0 (Stripping).

regards,

-Suhas

Hi,

To get optimum performance for DB backup, one should use DB utilities. You can use OnBar/OnTape utilities of informix to backup 18 GB data. It will hardly take 20 minutes.
To restore the backup, it will take around 50 minutes.

Regards,
Kaustubh

Thank you Suhas, I did go through all those threads and the information you gave. Unfortunately, i couldnt find one that is related to SQL Server as the back end. Its not a secret that Oracle Database does provide faster backups and so any application sitting on it should show similar performance.(E.g: Baan on Oracle).
As of now, a Backup of 15GB takes somewhere close to 5 hours under normal circumstances. Hardware wise, we have Intel Xeon 3.06Ghz x 2(Dual processor), 3GB Ram and 3 x 146GB Storage in Raid 5, all of which are housed in a HP ML 370 case..
Software wise: O.S: Win 2k3, DB : SQL 2k, Baan 4c4 SP20

The backup file or the sequential dump of the company is written on the hard-disk which is then backed up on a tape later on...I am not too conversant with the Baan commands like bdbpre, bdbpost etc and so considering all these factors in mind can anyone suggest what setting should i tweak in order to get mind blowing backup and restoration speeds as mentioned in these threads...

sk