July 30, 2008 at 12:42 pm
Need expert thoughts on Data migration.
We do data extraction every day from flat files and stores on our database. There is third party tool which extract data from flat files through BCP command and stores in the tables which is much faster.
We do the same thing to our clients also but the difference is data filter based on the client. for example if the client A need data we then send specific data to that client but the process is same.
I recomended my boss that we should not do this because we already stored data one time on our database and the client should come get the data based on the filtering condion by creating Views.
To do this we will not do the duplicate work by loading the data to the client, Client will get uptodate data all the time. There will be no VB code involved.
My only concern is the connectivity and speed. Iam also thinking creating a new server and do restore database every night and create views on that server so the client will connect to new server and get the data. In this case the main server will not have too much load.
Let me know if I am doing the right thing.
July 30, 2008 at 12:54 pm
It depends on the amount of data and the connection speed, really.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2008 at 12:58 pm
I'm not completely clear what you mean here or need to accomplish?
Are you and the client both loading files separately? Do you need to create a file for them? Or are you considering importing the data multiple times and storing in different tables/databases?
July 30, 2008 at 1:17 pm
Thanks for your reply.
Right now , both we and our client extract the data separately from flat files with a same process. We get all the data including client data but clients gets only his part of data. so we have clients data already stored in our database and I want client should connect to our database through view and get the data.
I don't know if I use views then how much it will affect the performance query and also the conectivity.
We have 2 tera bype of database.
July 30, 2008 at 3:27 pm
How are the clients going to get their data out of your database, assuming you go ahead with that?
Will they have some local application at their site, and connect to the database at your location, through the Internet? Will there be significant latency or bandwidth issues if they do that?
Will they connect directly to your server and write queries there?
Will there be a web page hosted at your location that they will use?
In any of these scenarios, will they be querying/updating many rows at a time, or just one at a time?
All of those options make differences in what answer is best for you.
Also, is the data load daily, monthly, quarterly, annual, something else? How big is it? A few dozen rows, a few Gig, something in between?
Differences in those also affect how the data is best handled.
For example, if it's a daily load of a Gig of data, and they are using a local (their location) application to run large reports and drill-downs on thousands/millions of rows of data at a time, and they are on a different continent than you, the best solution is definitely for them to have a local copy of the database with their data in it.
On the other hand, if the load is quarterly, consists of a couple of thousand rows of data, and they need to query four or five rows at a time, from across town, then the data in your server is going to be more efficient and less error prone.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2008 at 4:15 pm
Good questions:
This is what is happening right now.
1. There is a Data Extractor tool written by third party which Extract data from flat files and stored into sql tables. this process is happenning at our end too, the only difference is client gets the filetered data based on client's specific requirment and we get all the data. The data is same.
2. They don't connect to our server. once they get the data they do the query at their machine.
3. There is no web page hosted at our location.
4. They don't update rows in our database.
5. The data loaded weekly at client side from the third party tool called extractor
6. There are couple of million rows of data for the clients to work on.
7. Finally they do validation between our database and their database to check if all the counts are matching.
Here is my suggestion
1. I want Extrator tool works at our end only not the client's end because we already stored the clients data at our end and I want clients should connect to our server and get the data by creating filtered views.
this way clients will get uptodate data . there will be no extration process which takes long time and there will be no validation routine.
August 4, 2008 at 7:49 am
It sounds like it might work to centralize the database, like you're talking about. How they query the data and how much data will be transmitted at a time will matter.
You could set up a test environment, with a separate copy of the database, and have someone do some performance testing with your proposed new solution. That would be best.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2008 at 9:16 am
Thanks a lot.
How about if I get one more server and install all the views which is pointing to our main database and the client will access those views on new server.
Will that help in load balancing. ?
Is it a good thing for the performance purposes.?
August 4, 2008 at 11:48 am
No. That won't accomplish anything useful. Views don't work that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply