How to use Drupal for managing a public website together with an intranet and an MSSQL database?

Question

I'm new to Drupal and I've been digging through the documentation to find out most of the features and possibilities of this software.

I'm asking you an advice in evaluating if Drupal could be the case for a CMS for a client of ours.

The client is an Italian public administration; right now they've got two sites: internet and intranet. The two sites feature different layouts and different content (some content is mirrored in both sites, some is only on internet and some is only on intranet). Right now everything is managed manually with classic xHTML and some programming.

In an effort to switch to a CMS (don't consider content migration right now) we have to deal with different infrastructure problems, such as:

• the intranet site is behind a private LAN not accessible from outside
• the internet site is on a DMZ with no link with the private LAN (and they want to stay it that way)
• the client database option is SQL server (they could provide access on a shared database from both sites, this is ok, but it is the only link they could give)

So I have some trouble to find a good software that could solve most of their problems. How to:

1. Let them have only one administration panel (content is managed only once)
2. Let them choose if the content added has to appear on one of the two or on both sites
3. How to sync the files uploaded on both sites (if files are stored in the db it shouldn't be a problem)
4. Is there some module that can let editors to insert images directly in articles (let's suppose they have wysisyg editors for content) and save those attachment in the db (same problem as n. 3)
5. Manage different layouts on sites and also different content organization (menù, views, blocks etc.)

What do you think about it?

Answer 1

Drupal can most of this very well.

Someone with more experience can address how it can be set up for Intranet and Internet. It can handle multiple sites linked with data from same database (Not sure if this is what you want).

User permissions can be defined effectively down to the smallest content level so restricting access based upon any user categories you choose is not a problem.

Drupal works from a database (MySQL), apache, and php. Transferring data will not be a problem for an experienced DB manager.

Images are stored as separate files on the site with references in the DB. Images can be handled in a variety of ways on any page display including direct insertion.

Page layouts can be handled either through layout themes that handle HTML and CSS management and are fully customizable.

Drupal is exceptionally flexible and can do just about anything you can imagine. There are hundreds of modules to choose from (all free and open source).

Having said all that, doing sophisticated site features is not for beginners and there is a steep learning curve for new site developers. Getting a sophisticated site up and running requires an experienced Drupal developer. Some of them are on this site and you can find others at Drupal.org.

Answer 2

Maybe using a multi site setup could already get you fairly far? Such setup also has some pros and cons that would have to be investigated first of course.

Consider using Module Forena to address part of the infrastructure problem related to MS SQL. It can be used to query (not update) the data in MS SQL databases.

For more details about Forena, 2 types of documentation are available:

• Community documentation.

• Documentation that comes with Forena, which you can access right after install and enable of the module. Checkout the demo site for an online example of the current:

  • Forena documentation - use the link 'Reporting documentation' or visit relative link /reports/help.
  • Forena samples - use the link 'Reporting samples' or visit relative link /reports/samples (these samples are fully functional, so make sure to experiment a bit with it, such as the drill downs available on the SVG Graph sample).

The newest 7.x-4.x version also includes an amazing (I think) UI for either creating your reports (the WYSIWYG report editor) and/or for creating your SQL queries (the Query Builder).

Here are some variations of the MS SQL database part in the questions:

1. Using a SQLite instead of MS SQL database: the Forena samples actually get shipped including a (Tiny) SQLite database. Go check it out in the demo site: the data shown there are data contained in the sampledb, which is in SQLite format.
2. Forena comes with a full suite of Supported database connections ... such as MS SQL, Oracle, Postgress or any PDO compliant variation.

Enough reasons for considering giving Forena a try? While doing so, use it's issue queue for any type of support/docu requests you may have.

Be aware: I'm a co-maintainer of Forena.