August 18, 2005 at 11:54 pm
Hi Everyone,
From last 3 months We where facing a problem the best way for data transfer.
I have a OLAP Server same as One of my production server .The Production resides 200 km away from OLAP server we connect by lease line.
As our production server is in 24*7 mode . We have 200+users connected each time .We get 1 hour time for dts during the shift change.
In my olap server i used 24 tables each having more then 50,000,00+rows and i have to transfer the data everynight.These 24 tables gets updated daily as these are the main transaction tables in our ERP setup.
So firstly we created linked queries
Ex: Delete from databasename.username.tablename
insert into databasename.username.tablename from [linkedservername].databasename.username.tablename
(it took 13+ hours to transfer the data from production server to olap server)(Tried on holidays)
Then we tried Creating BCP Files and transfering then on network.
Ex: exec master..xp_cmdshell 'bcp epinav.dbo.salesline out c:\BCPFILES\salesline.bcp -n -"networkname" -U -P'
This took 7 hours to transfer and we faced problem in loading data
string truncation errors so we have to script the production tables and execute them on my olap server and reprocess loading BCP files.
in all this process took 9 hours to complete.
This also doesnt work in our case.
Then we tried a process which need too much of maintenance.
The steps are
(1) Created BCP files (takes total time 15 mins)
(2) scheduled automatic delete and create files. ( 3mins)
(3)Created batch files to zip the bcpfiles (zipping of 24 bcp files takes approx 5 mins)(used winzip)
(4) scheduled the batch files under windows schedular
(5) transfer zip files to olap server takes 5 hours to transfer.
(6) unzip it in a location
(7) LOad data from BCP files.
In this 7 steps it takes Apoprox 6 hours.
We tied this step also
We created a small database(DTS) of 24 tables used in olaps and created a DTS package in which we used execute sql task and datadriven queries to transfer data from production server to my DTS server and takes backup of the DTS database ZIP it and tranfer it on the network the datbase size is 6 GB and takes longtime to transfer(time not calculated).
After transfer completes we restore the database and transfer data from dts database to olap database.
Total time(+6 hours).
At last stay bare handed no result for our hardwork....
Can u all pls suggest that what is the best and fast methord to transfer data.
Thanx for ur help and Time
We use Microsoft Navision Axapta the ERP Programme for user interface.
We have SQL server 2000 SP4 on both the machine.
Production server has 3 CPU with 6 GB RAM.(RAID 5)
From
Killer
August 22, 2005 at 8:00 am
This was removed by the editor as SPAM
August 22, 2005 at 8:30 am
Raj,
I had a similar situation a few years back and our solution was to set up SQL snapshot replication between the production and "reporting" servers. This allowed us to do the huge, ugly management reports in the wee hours of the morning figuring the load was less on production at that time and the reports would be ready by 8 AM. Not sure if this is something you can implement but it is an alternative to trying to DTS data.
Hope this helps,
Darrell
August 24, 2005 at 9:08 am
Hi,
As i described in my question the replication takes too much of process so i do try it.
But yes our problem sloved.
The process we applied need too much of maintinance
(1) we use to transfer the data from production server by drop and create methord that is 50% faster then inserting the records.(1 hours 20 mins)
(2) For few tables with less columns we transfer the data in test files and zip then using winzip.
for few tables we used access .
(3) transfer the records from the transfer server to access data base.
zip the access database.
now we transfer the files using packetier.
the whole process takes 3 hours andit complete till 3 am.
thanx for ur suggestion.
from
killer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply