1. Choose a database engine.  Often departments start with Microsoft Access, and if the size of the database or the number of hits warrants it, they move their database to Microsoft SQL Server.  Large databases, like UCD's administrative databases, employ the more powerful Oracle.  UCD IS supports:

   • Simple comma- or tab-delimited text file "databases"
   • Microsoft Access via our Microsoft web server (IIS), www2.uchsc.edu
   • Oracle access of administrative data via a special web server.

   Currently IS does not have a community SQL Server server (read on).
    

2. Make the database accessible to the web server.  Databases are not stored on the web server.  They are stored on an IS community file server (Mustang) or, less often, on a department file server.  We do this partly for security (not one of IIS's strengths) and partly to avoid database updates via database GUIs running on the web server.  
   
   A share is created for the database, and a special Stargate account, named something like Pulm_CCCC_IIS (department-project-IIS), has the sole access to the database (at the cost of $11.66/month).  A group named something like Pulm_CCCC_DB is also created, with the account a member of it, because IS's Servers group likes to manage by groups.
   
   In some cases a department is asked to provide their own hardware - for instance, if it wants to use software IS doesn't support (ex: Cold Fusion or even Microsoft SQL Server), if it expects a lot of traffic that might swamp a general purpose web server, or if it requires any special version or configuration of system software (ex: Microsoft MDAC 2.6 instead of MDAC 2.7).  If both the database service and the web service have peculiar needs, it may mean the department should provide 2 servers (because databases are not stored on a web server).
   
   Department-owned web and database servers can be located in IS's server room; contact Sherry Fischer for details.
    
3. Create the code that opens the database.  Good design dictates that access passwords are not stored in the web page or transmitted between servers in the clear.  We require webs that access databases be on the secure (SSL) virtual web server on www2, which means your URLs will start with "https://", not "http://".  We hide the "2" and "s" in https://www2.uchsc.edu/your_web behind a forward from http://www.uchsc.edu/your_web, which is the URL you should advertise.  There is a choice of DSN or DSN-less connections, but so far most are DSN-less.  IS can help create a "database connection string" that defines where the database is.
   
   
4. Design the user interface.  This means creating the GUI users and administrators use to query or modify the database.  A scripting language is used to create dynamic pages to display whatever information comes back from the database.  On a Windows platform Active Server Pages (ASP) written in Visual Basic Script (VB Script) or JavaScript are common, and on Unix written in Perl, JavaScript, PHP, or Python.  Additional tools that can help with this include  Dreamweaver MX, Borland Delphi, Cold Fusion, and FrontPage.  Sometimes projects write a web-based GUI and never create a traditional database-based GUI. 
   
   If your department doesn't have the necessary skills, there are local contractors.  The majority of this work is handled by Amy Beck, an independent with ~25 departments as customers, and Joe Huggins @ AHEC, who implements using Dreamweaver MX.
   
   
5. Check security.  Besides the security mentioned above, a common request is that access to the database be restricted to certain people.  If all the users are employees or students, Stargate authentication can restrict access of the web that accesses the database to a certain Stargate group.  If select non-employees need access, a personal database of logins and passwords will need to be created and maintained (Amy is good at this).
   
   One issue to consider is "How much new data can I afford to lose (because of a server catastrophe)?".  Microsoft Access may lose a whole day's worth, while SQL Server (and Oracle) can be configured so that no more than a half hour's (say) data is ever lost.  Also, SQL Server and Oracle support access logs or audit trails, so one can tell who changed what when and perhaps why.
   
   If your database contains identifiable patient data, IS needs to know this and you need to take special precautions, per HIPAA confidentiality requirements.

IS servers currently host a few dozen Microsoft Access databases on IIS, several administrative databases via Oracle, and we are testing some of the other solutions mentioned above.  For large or popular databases SQL Server offers distinct structural and performance advantages over Access.

If you wish to proceed, please start by mailing the webmaster.

Back

Last updated 03-30-2005