Choice of a Database System

[Adigga1]

Hello everyone,

I am in the process of designing a business system (Database System) for a relatively large organization.
This system must be able to enter data, apply conditions and formulas, perform datamining and data warehousing, to name a few of its functions;
It has to be in a location or structure whereby 300+ users can access and operate in its environ. seamlessly without much bandwith or slow down issues.
Can someone suggest the best platform to handle this type of workload? Is it an access db or SQL server? What OS and hardware architecture would be recommended?

I hope this was the correct forum to pose this question.

Thank you all in advance
A...

 

[Atomic Shrimp]

If you're talking about 300 users concurrently connected to it, Access almost certainly isn't going to perform well - in fact, if it's 300 users, but only an assorted 25 of them at any one time, it's still going to be stretching the limit for Access.

It sounds like you need a proper database server solution at the back end (you could still use Access for the user interface, or not)

Are the users all in one building with an established LAN? If not, is there already an established corporate Wide Area Network connecting them all?
What server products are already in place for this organisation?

 

[Adigga1]

If you're talking about 300 users concurrently connected to it, Access almost certainly isn't going to perform well - in fact, if it's 300 users, but only an assorted 25 of them at any one time, it's still going to be stretching the limit for Access.

It sounds like you need a proper database server solution at the back end (you could still use Access for the user interface, or not)

Are the users all in one building with an established LAN? If not, is there already an established corporate Wide Area Network connecting them all?
What server products are already in place for this organisation?

Thanks Shrimp for the response, some users may be accessing the database from remote or satellite locations. As far as erver products; we have not decided on one as of yet, but we are lookin for efficiency; bot in cost, performance and reliability..

Any recommendations? Thanks

A....

 

[Atomic Shrimp]

Sorry - it's more questions...

How do you intend to implement this? Are you planning to choose a solution on the basis of recommendations, then get a technical contractor or support company to implement it?

Or are you going to employ someone to do it? Or set it up yourself?

Who do you envisage as being responsible for the ongoing support of the whole setup?

 

[Adigga1]

Sorry - it's more questions...

How do you intend to implement this? Are you planning to choose a solution on the basis of recommendations, then get a technical contractor or support company to implement it?

Or are you going to employ someone to do it? Or set it up yourself?

Who do you envisage as being responsible for the ongoing support of the whole setup?

"How do you intend to implement this? Are you planning to choose a solution on the basis of recommendations, then get a technical contractor or support company to implement it?"

I'm just the low-ly IT guy making an informed recommendation to my superiors for their budget decisions. They plan to open another branch and they need some of these pieces in order to construct a feasible budget to fund it. I believe they will hire a contractor to implement, then we will support it. Hope this helps?

Thanks

 

[Atomic Shrimp]

This is potentially quite a big and complex undertaking let's try to break it down...

Database hosting:
The database has to live somewhere - this could be on your own hardware, or you could buy hosting from a third party.

If you host the database yourself, you have lots of control, but you face:

• cost of hardware
• cost of server software licence(s)
• cost and complexity of setting up connectivity across multiple office locations (you need to set up a reliable and secure Wide Area Network, of some description, or at least make the server available externally from wherever it lives)
• Burden of ongoing support and maintenance

If you buy hosting, you benefit from letting someone else do much of the admin, support, maintenance and configuration/setup work - and configuring connection to the database then becomes identical for all clients, but you face:

• Possibility that hosting costs will grow as your data expands to fill more space and as your usage of the system increases
• Risk of delegating the maintenance/backup and some of the disaster recovery etc to somewhere beyond your immediate control.

 

[dportas]

Adigga1,

Your requirements sound pretty standard for a small / medium enterprise (300 users). You should be considering a client-server DBMS such as Oracle / SQL Server but I suggest you take advice from whoever is responsible for delivering the solution about the actual technology choices. Unless you are very sure of your ground it's probably too early to estimate hardware costs right now. The most important and frequently the biggest part of the budget is likely to be for the human resources for development and support. Those will also determine the time constraints. I would work on estimating those first.

 

[Banana]

AS has given you some good starting points, so I'll focus on the terms being used here.

As Atomic Shrimp indicated, this is a big undertaking and I think it's very essential that you and whoever is in charge clamp down and understand precisely what you want. You threw out 'data mining' and 'data warehousing' in the OP, and I have to ask are you/your company sure you understand what it means and entails and whether you actually want that?

The reason why I ask that is because without a clear understanding of what your company actually need, it's possible to end up with wrong product or wrong approach or worse, both. Take data warehousing for example. It usually means you're storing terabytes of data in a simple, somehow de-normalized schema such as star or snowflake schema that makes ad hoc querying/reporting upon the schema more straightforward for several users. In this case, all users are just read-only, and data warehousing merely receive updates/inserts from a designated source in bulks.

This is in contrast to say, Decision Support System (DSS) where many users may input new data, modify data, then report upon most current snapshot of the data. Obviously, DSS has much more writes than reads, requires a robust OLTP system in contrast to data warehousing (which could be more generally classified as OLAP), where performance consideration may necessitate a need for non-transactional system simply because users aren't going to be writing to the source.

Here, I've given you some more terms, but I think what should matter more is that instead of picking a term that sounds good to you, write out the specs first and see which term fits your specs the best and look into this.

While on the subject, it also serve to help clearly distinguish what a product is. Access for example is actually just a rich client that's capable of using many data sources to do data processing. It happens to be conveniently packaged with a local database engine (Jet/ACE) which you can either not use or use in conjunction with the chosen backend, be it Oracle, SQL Server, MySQL, whatever. You can equally choose to use web clients instead of Access for the same backend or do it from scratch in C/C++, Java, whatever floats your boat. What matters the most is that it also meets your requirement the best.

For example, if you want a flexible front-end that you can update and change rapidly and readily, Access is your prime candidate. But if you must have online access independent of VPN or secure tunneling, you may have to look into ASP/ASP.NET/PHP/Perl/whatever. Furthermore, if you have resources and money and your requirement are such that it won't change for 10 years (e.g. data warehousing), it may make sense to use C/C++, Java to create your custom front-end client.

I'll say it again, get the specs down and crystal clear. Spell not just 'whats' but also 'hows'. You said 300 users. That's a 'what' but you need to identify the 'how' such as connecting to the source via a web client, a thin client, a rich client, or carrier pigeons.

You may also want to take a look at the guide for more high-level discussions on using ODBC source which may indirectly address the questions you have and help you refine the questions/specs.

I hope this help.

 

[Adigga1]

Thank you guys so much for your input and suggestions with this undertaking; I have been losing some sleep since they dropped this project on my lap...I understand the scope now and where my starting points should be; I will be drafting out a plan / proposal and update the group on our progress in this..

Again, thank you all for the contributions

Regards,
A...

 

[The_Doc_Man]

Another factor having to do with this kind of thing is the estimate initial size, growth, and data retirement issues - often generically referred to as a data life cycle.

If you start with an average of 200 bytes per record for 50,000 records of various types, you are looking at 10 Mb of data. If that grows at the rate of 5,000 records per year, it is only about 1 Mb per year. With a single 200 Gb disk, you can host that on a smaller system for a long time. You can run backups by copying the database to a CD-ROM (Write-Once Read Many, or WORM) drive, which can trivially handle 600 Mb for less than 10 cents per platter. You could make a nightly full copy for $40/year.

If you are looking at the same 200 bytes per average record for 5 MILLION records of varioius types, you start life with a Gigabyte. You need DVD for backups up to about 4.5 Gb, after which you need something else that is far more expensive.

Operationally, you also need to consider that if you do your own hosting, you need operators for every shift for which this DB will be online. You will need to make a schedule that allows you to do regular maintenance. You will need to make a schedule for upgrades, backups, reloads, reboots, etc. You need to consider the operational infrastructure in detail.

Next question, will you require a disaster recovery component in your solution? This usually involves off-site backup, possible storage area network elements, and even a stand-by site to take over ops if you have a site problem. I work in New Orleans. We were devastated by Hurricane Katrina, but we were out of service only about four hours while we copied everything via storage area network from the home office to the stand-by office, switched the network and name tables, and otherwise prepared the system for the action at the remote site. Not many companies were able to do that - but we did it.

You need to determine the scope of what you are doing. If you have the intention of getting this done by consultants, one technique is to put out a request for proposal in which you outline some of the things we have discussed and ask the consultants to offer a proposal on construction and maintenance of this thing you want to build.

 

[gemma-the-husky]

even though access may not be the right beast for the job

as a rapid application tool (and cheap) you can prototype applications easily.

this is not going to be cheap, i would imagine. if you are not buying a packaged solution you need a lot of time to plan your requirements - before you start doing anything.

i would seriously estimate this could easily take 2-3 months to specify. You then need to source a designer, and build it - i would plan on a 2011 implementation - if it comes in earlier, great - but its better to get it right first time.

 

[dportas]

i would seriously estimate this could easily take 2-3 months to specify. You then need to source a designer, and build it - i would plan on a 2011 implementation - if it comes in earlier, great - but its better to get it right first time.

You could do that but you probably shouldn't. Consider Agile methods instead of estimating and fixing scope. Three months is too long to specify one system and a year is too long to deliver.

Prototyping also tends to be frowned upon these days. In my view it can make sense for certain systems but the original question was actually about chosing a database system and I don't think there's much to be gained from prototyping a database in anything other than the target DBMS platform.

 

[The_Doc_Man]

dportas, the details certainly do not need to be determined intially but the scope does because you buy hardware based on scope. You buy software packages based on scope. Jet won't handle more than 5-8 users online at once - but Access as a front-end and something more robust would be a good choice when you get into the hundreds of users and millions of records. I don't care HOW agile you think your development tools are, they aren't any good if they don't support a database of the size that you need. I've seen this "wheel" turn three or four times. It comes in cycles. Someone comes up with a "new" way to look at things, only to find that the traditional ideas of scoping a project ahead of time still are the most reliable ways to do it.

Yes, I agree you don't need to know every field on every form. Yes, you don't need to know how many queries you will eventually have to build. But if you don't know how much data and how many users, you have already lost the battle. At least to orders of magnitude, you need to know where you are aiming. So there is no substitute for scoping a job. At least, not where I work.