General discussion

  • Creator
  • #2310826

    Database Recommendations


    by jim cueno ·

    We are looking into consolidating the thirty or so MS Access databases scattered across at least three departments our municipal government (about 33,000 population) and the County goverment. We know we will need to move up to a bigger, enterprise-grade solution before that will happen. We will probably be adding in data from other sources (namely spatial data from the Geographic Information System) as well – which will restrict our list of possible databases.

    We’ve got a bad habit of “building instead of buying” so we anticipate doing the work ourselves; we’ve got experience developing MS Access applications, VB and ASP applications and we have support from all levels of management so we don’t expect to be floundering hopelessly.

    Now the meat of my question / request is this: What has been your experience with some of the larger, proprietary databases, particularly DB2, Informix, Oracle and SQL Server?

    An open-source solution will not be adequate, we need to interact with too many applications with too many specific needs / limitations / requirements, so I would appreciate enterprise-wide opinions of those four databases.

    Thank you.

All Comments

  • Author
    • #3478262

      What GIS Software?

      by bunce ·

      In reply to Database Recommendations


      We’re using SQL to host our enterprise data and also a couple of GIS systems, without any problems to date.

      What GIS software are you using?


      • #3464280

        Re: What GIS Software?

        by jim cueno ·

        In reply to What GIS Software?

        We’re an ESRI shop.

        • #3464712

          Same here..

          by bunce ·

          In reply to Re: What GIS Software?

          Same with us -> ArcGIS, ArcIMS, ArcView etc, on SQL Server 2000 for quite a busy intranet mapping system. Few problems to date and integrates quite well into other apps.

          I’m not the actual GIS guy but can ask some questions of him if you like…

        • #3464584

          What GIS?

          by smchris ·

          In reply to Same here..

          Well, as yet another person who doesn’t know what GIS software is, let me make a couple blind comments anyway:

          1. Have you actually done some research on postgresql? Try this link, particularly the BASF case study:

          Can’t your Windows apps handle ODBC? Postgresql does. It’s really pretty impressive. Maybe like an Oracle 7.0 clone at a price a public organization would like. And Access imports aren’t a problem.

          2. Among the commercialproducts, it seems to me you have to weigh needs and costs. Oracle and DB2 are the big league. But they cost a lot too. Frankly, with your user base, you don’t seem like the big league. It isn’t like you are Google getting hit with a million users an hour (or whatever), right?

          So maybe SQL Server would be the cheapest commercial product in your case. However, I saw a chart a few months ago that _might_ be relevant. It was comparing db2, Oracle, SQL Server and MySQL as a web server back end. Oracle, db2 and MySQL kept chugging with a straight-line performance degradation as they threw more load on them but SQL Server performance started to choke into a power curve at, if I remember correctly, a rather surprisingly low 200 users.That’s active simultaneous users, of course, so you would be the one to know the potential bottlenecks of your organization. But if that performance curve holds for SQL Server with your apps as well and you have more than 200 simultaneous users, you might have to weight some factors: maybe more than one SQL Server, beefier hardware, whatever. Might still be cheaper than Oracle or DB2.

          But seriously, check out PostgreSQL again. Might be an opportunity to be a public sector tax saving hero.

        • #3479578

          What GIS?

          by jim cueno ·

          In reply to What GIS?

          I suppose that if all you’re interested in doing is maintaining data with a spatial component (anything as simple as a customer table with ZIP codes, maybe something a little more complicated like trying to map customers by address, or something really involved like maintaining a sign inventory), it could be maintained in postgresql (or any database for that matter).

          However, I’m more interested in the true spatial data – the base map itself for the uninitiated. I’d like to store the ZIP boundaries, the street centerlines and the survey information in the database – as well as raster imagery, annotation and cadastral information in the form of polylines and polygons. The actual behind-the-scenes meat of GIS – for any “person who doesn’t know what GIS software is”.

          Therefore, I should have prefaced my initial request with a little more information or clarification.

          The particular restrictive application is ESRI’s ArcSDE, which will allow us to do all of things I mentioned above, but it restricts us to DB2, Informix, Oracle or SQL. Sure, we will also be doing regular day-to-day database stuff, but I’d like to “to be a public sector tax saving hero” and only implement one database and have it pull double duty. By storing the spatial data in a database, managed by ArcSDE I would be able to deploy a much more secure, robust and flexible system than by maintaining attributes in the database and geography in separate files.

          The other helpful replies have suggested that we do in fact have skills in our “bush-league” organization to implement a system using one of those four systems. We do not however, have a base of skills with open-source databases and I fear we would be wasting too many resources to get up tospeed to implement them, regardless of their initial appearance as being “low-cost”.

          Thank you

    • #3464472

      Legacy systems into SQL Server

      by just ·

      In reply to Database Recommendations

      The bane of my existence is legacy Access databases that have been created by untrained amateurs. They become a single point of failure in the operating divisions, but then they break or the account clerk who created them loses interest, or (more likely) gets in over her head, and they become the squalling baby on MIS’ doorstep. We have established a policy of if it’s either used by multiple people or mission critical (a week of downtime is a bad thing), then we will “assist” by converting it to a SQL Server backend with a browser based front end. This is costly, however, since it is time consuming, and we generally have to start from scratch because there is no written documentation on the existing system. Most of our apps are < 20 users,so we don't have performance problems, and we have dedicated web and database servers. We use SQL Server because it is reliable, we are a MSFT shop, our current staff is experienced with it, and if we have to hire contractors to help, we can easily get experienced ASP/VB/SQL Server folks locally.

    • #3479670

      VB/SQLServer = best solution

      by sbonham1 ·

      In reply to Database Recommendations

      I just built a hugely successful solution for three departments that had dispersed Access DB’s. I did this by leveraging the in-house skillsets in VB, Access and SQLServer. If costs are an issue, I recommend using Access front-end (VBA) with ADO middle tier and with an MSDE pilot DB. This development environment will cost you about $800 bucks (if you are on Win2000, you can by Developer packages from on-line brokers since MS has discontinued). This environment is extremely supportive of data migration from old Access DB’s. Also, with ADO, performance problems become trivial (if the right amount of time is spent designing and reading up on ADO). If a good middle tier is designed using ADO, then you allow yourself a cleaner upgrade to Web/ActiveX support in the future. Later, porting the MSDE DB to SQLServer is a no-brainer. This last step is where the real costs come in to play – bigger servers and SQLServer licenses. Our system supports 40 GUI windows, 80 tables, 90 stored procedures, and 10 Class objects (ADO middle tier). It is used by 40 concurrent users both dialed in through PCAnywhere and onsite. Finally, if cost is not a huge issue, then bypass the Access front end and purchase a complete VB development environment (don’t forget to use VSS).

      Good Luck,

      Steve Bonham
      Bonham Associates, LLC

    • #3479469

      Big League Databases

      by rickman ·

      In reply to Database Recommendations

      I am sure that ArcGIS’s compatibility with Access was one of the reasons that you chose ESRI in the first place. I recently worked on a project that integrated ArcGIS with Oracle 9i. Every went smoothly. Integration with both Oracle and MS SQL Server and ArcGIS is well supported. Your choice of backend should be based on access to experience or training with the Databases as well as the current structure of your current Access DBs.

      Access upsizing is a multistep undertaking. First getting the users onto the new DB platform using the oldapplications. Database consolidation and normalization, and finally client redesign.

      The choice of Oracle or SQL Server should be based upon either comfort level with the server or the structure of your Access apps. I would not look to the Access Upsizing wizard with certain versions of Access.

      The first step is to move all your tables to the backend database and modify the code to connect to the backend. If using split apps or ADO then either SQL Server or Oracle will do. If using linked tables or DAO then SQL Server will be the easiest transition. With ADO modifying the provider and the SQL will be the primary modifications. With linked tables, use linked SQL Server tablesto run normally. This first step must be short term. There must be a concurrent team performing database consolidation and normilization of a new DB design.

      If you are going to just migrated the tables and run the current apps as is, the project will fail, as this is a waste of the backend server and will be a nightmare for maintenance and new projects. The Access front ends should be replaced by VB front ends that more meet the business and domain needs of the clients of the cosolidatedDBMS. Access VBA is slow, and the MDB files are subject to corruption, where an Exe or DLL file is not.

      Good Luck

      DanZAK Development Systems, Inc.

    • #3462332


      by technontology ·

      In reply to Database Recommendations

      If at all possible have an Information Architect look over the structures of the existing databases. You may find huge potential for collapsing data into common types, sizes, organizational hierarchies, etc which facilitate systems that may be integrated in the future. And eventually users expect this to happen, whether they know it yet or not. If you need to store spatial data, the current favorites should rank Oracle and MS SQL Server high as they both have SDE(Spatial Data Engine) architectural components designed specifically to handle spatial data. They both play well with ESRI ARC products. Regarding Total Cost of Ownership, Oracle will be probably more expensive. But research will tell.

Viewing 4 reply threads