Data handling in a production environment

Hi,
Please bear with me, this is a fairly long and general post. Please answer
any of these questions or point me to appropriate references...thanx...
My company manufactures some product and currently have some EOL testers
which write test results for serialised parts into text files using a mixture
of VB and standard programs like testpoint and labview. These files may be
accessed or written to by several testers in parallel or at different points
in the production process. This occurs in three seperate manufacturing
locations that are networked on a WAN.
We are considering updating some of these EOL test programs to write the
test results into a database. The benefits that we forsee in doing this are
that we could improve the speed of read / write transactions (would this
improve write speed?), improve ease of reporting / data gathering, and could
backup the data more easily.
We have SQL server 2000 standard(2 processor) on one site which would be the
final repository for all the data, and each site would generate a maximum of
100,000 records / day. Bearing this in mind I am looking at the
network/database design and have questions regarding:-
1. Database to use on each site and where to locate it?
Should each EOL tester have it's own database or should there be one
dedicated database server on each site?
MSDE2000 or MySQL, as far as features, ease of use, and most of all
manageability, which is better, and what sort of maintenance tasks would we
need to perform on each?Could these tasks be easily automated?
2. Stored Procedures? I have been looking at stored in SQL server. Is
this the best was of managing the inserting of EOL test data into the
database? Could this confirm that the insert worked? could it also allocate
a new serial number? What other ways are there to do this? Are there any
examples you can point me to?
3. Additional metrics to gather:- are you aware of any standard prouction
reporting/analysis tools I could look at? We would make reports available
through an intranet.
Thank you in advance for your help
PhilInline responses...
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:95EA188E-7C59-495C-BA75-E971E2BA3FA6@.microsoft.com...
> Hi,
> We are considering updating some of these EOL test programs to write the
> test results into a database. The benefits that we forsee in doing this
are
> that we could improve the speed of read / write transactions (would this
> improve write speed?), improve ease of reporting / data gathering, and
could
> backup the data more easily.
Yes these are all huge benefits. Writing transactions may not improve the
speed, but would greatly improve the reliability. Transactions follow the
ACID format and are better.
> 1. Database to use on each site and where to locate it?
> Should each EOL tester have it's own database or should there be one
> dedicated database server on each site?
> MSDE2000 or MySQL, as far as features, ease of use, and most of all
> manageability, which is better, and what sort of maintenance tasks would
we
> need to perform on each?Could these tasks be easily automated?
One dedicated database server on each site, unless you have the bandwidth
(and network uptime) to use the single SQL Server on the dual processor
machine. It should easily handle 300k transactions in a day. (You can
optimize this system with multiple NICs as well.).
If you do a dedicated at each site, MSDE is great for small databases. If
you are going to upload all of the data each day and then clear the
database, this should not pose any problems and is an inexpensive way of
doing things.
> 2. Stored Procedures? I have been looking at stored in SQL server. Is
> this the best was of managing the inserting of EOL test data into the
> database? Could this confirm that the insert worked? could it also
allocate
> a new serial number? What other ways are there to do this? Are there any
> examples you can point me to?
>
Stored procs are definitely the way to go here. They help to reduce network
traffic and can take multiple inputs and generate multiple outputs. They
can allocate new serial numbers and a variety of other things. You could
use a trigger to generate a new serial number, but the stored procedure
would be the recommended method. You could skip the stored procedures and
use a front-end program to generate the INSERT statements complete with the
necessary data and serial numbers etc.
I would still recommend the stored procedures.
> 3. Additional metrics to gather:- are you aware of any standard
prouction
> reporting/analysis tools I could look at? We would make reports available
> through an intranet.
SQL Reporting Services is free with SQL Server. You could start there.
The other big dog in the Microsoft world is Crystal Reports which are
capable of generating a report in a variety of formats including HTML, XML
and PDF. You could always write an ASP or ASP.NET (or J2EE) application
that can do the number crunching and generate the ASP(x) pages.
> Thank you in advance for your help
> Phil
No problem, I'll send you my bill later. <wink>
Rick Sawtell
MCT, MCSD, MCDBA
>