May 6, 2002 at 4:23 pm
First off, I am not a developer, IT Professional, or Database guru at all. I am over my head which is why I need all of your expert advice.
I work for a small healthcare consulting company (<10 employees) that works closely with hospitals and other medical facilities. We receive data dumps from their system in usually a .udd file format. We have been using Access as both our backend and our front end. We have developed complex forms and reports in Access as a expedient way of sifting through the records. Recently, we have expanded our company to include other healthcare issues. This expansion also means that we are going to be receiving MUCH more data. In the last data set that we received, one portion of data has roughly 25,000,000 records. Obviously, Access is not designed for this much data, and here in lies our first issue. How difficult is it to split the backend into SQL, and still be able to use our forms/reports in Access?
One of our future plans is to have a web app written to tie into our SQL database, so the healthcare facilities can have up to the minute reports on the progress we are having with their accounts. Do both SQL 2000 Standard and SQL Enterprise offer this ability? I printed off the feature comparison matrix from the MS website, and there are many things that Enterprise offers that Standard does not. I can't imagine us having more than 15-25 concurrent users, so what are the advantages of Entereprise. Are there any factors we should take into consideration?
Thanks for your help
May 6, 2002 at 6:59 pm
One thing you'll want to look at carefully is licensing. If its just for internal use you can buy client access licenses (CAL's), but if you're going to have internet users you'll need to buy per CPU licenses. Figure out your requirements then shop carefully - there are still some deals to be found on per cpu purchases. Regardless of version, 10-15 concurrent users is usually no problem. Needs to run on a server buy itself and I recommend that you start with 2g of RAM and buy it so that you have room to upgrade to 4g.
That said, it's relatively easy to just move the data into SQL, then replace your table names with linked tables to SQL. By default when you link a SQL table in access it will be prefixed with the owner, so that if you have a table called patients in Access that you move to SQL, when you link it back it will be called dbo_patients. Just remove everything up through the underscore and Access will never know the difference. Once you have that running and start to get comfortable with SQL then you can start to leverage the more advanced features..if you need to!
Andy
May 6, 2002 at 7:36 pm
25million is not a lot of records. Usually standard is enough unless you will need specific things only Enterprise does which in you case may be using 3GB of ram or more. Other than that I suggest look at the long term requirments of the database and consider if you will be implementing things only Enterprise allows (cluster being the big one after memory). As for the up to the minute reports then yes especially if you are only using on server. However if you replicate out to other servers it make take a little planning to get pretty close to real time as with any DB server.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 6, 2002 at 9:59 pm
Enterprise Edition has some performance enhancements for read-aheads and queries doing table scans on the same table concurrently, but with the load you've stated I don't know if it'll do you much good. If you have a requirement for a high-availability clustered solution, of course standard is out the window. Generally, though, unless you have to have the features of Enterprise Edition, the pricing is prohibitive.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
May 7, 2002 at 9:23 am
Thanks all for your posts. They have been very helpful. Could you give me an example of when we would need a "high-availability clustered solution"?
May 7, 2002 at 9:55 am
I understand clustering to be basically redundancy on multiple servers. Is this correct? Also, how valuable is the Analysis Services Feature? I see that Enterprise is much more robust in this area. On Ebay, it is not uncommon to see SQL 2000 Enterprise for about $2200 for the software and 25 CALs. That slims the barrier down quite a bit. Standard from Dell with 10 licences is $2k, or on Ebay around $1600. Seems like a no-brainer that we should go with Enterprise for that little of a difference, assuming that we are getting a new unreged copy. Could you all point me to a place that has deals on per CPU licences so I know ballpark dollars?
Also, We are building a server to run SQL on, and have been looking at the Dell machines. Would a dual 1.26 PIII with 3x73g SCSI drives in a RAID config be a place to start? I see you recommend having 2g of RAM, so we will have to increase that in our quote. Also, do any of you have experience with Veritas Exec or other backup software util? Can it backup to another harddrive instead of Tape or other removable Media? The reason I ask is because we have removable 160g IDE drives on one of our networked machines in the office that we currently are backing up to. When the backup is done we just pull out the drive and store it in the safe - it's quick, cheap, and they hold more than most tape drives.
Thaks again for your posts -
Dave Oakley
May 7, 2002 at 10:29 am
Problem with EBay is a lot of time it is OEM software which unless you bought the hardware you will get hit for license violation. Also a lot of the time it is copies or MSDN outdates or some other item that there is no license for. Be real carefull who you deal with.
Most all the utilities are able to do this but I have seen compalints about restores.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply