Back End Database

Posted on by admin

In the early years of VNCScan, all of the data was stored in a Microsoft Access database and the settings were stored in INI files.  This worked well but required that I distributed a lot of additional runtime files to access the database(pardon the pun).

In late 2003, I decided to move away from Access in favor of flat XML.  To this day, I still debate with myself which was better because each have their issues.  The XML file format for data made it possible to ship a smaller product with less dependencies but it severely limited the multi-user capabilities for sharing data.

The XML format is just plain text files. When one console reads the data and then writes a change, there’s a chance that another console that is also reading from that same textual XML file can write back what it thinks is accurate data and overwrite the first consoles changes.  It can get messy real fast.

At least with the Access database, you could have a few users adding and reading data at the same time without overwriting the whole file just for one change to a single field. 

I realize that Microsoft is steering its developers to their mini desktop SQL server instead of Access but it just seems like a lot of overhead for the type of data being stored. 

Today, all of the routines needed to read and write to an Access database is built into the existing DotNet Runtime.  What drove me to this XML format is no longer a problem. 

So, what do you think?  Should we make the move back to the Access database to facilitate a better multi-user experience or stick with the XML?

  • Name

    stick with the xml

  • Eric

    You have several options here, and where you want to go with this is ultimately up to you. If you feel you can put more features etc into one than the other, then that's the way I would go.

    Side note however, if you decide to stick with the xml, why don't you create an xml file for locks? Have each client put their version of time, so it would log the offset of each client, and then whenever they want to write to a file, they put the filename in that lock.xml with the filename they are writing to and the time. Give it a timeout before the file lock is removed by another client. This way if for some reason the other client lost connectivity or went offline during the write attempt, it won't keep the lock on the file in quesiton.

  • Vladimir Brlas

    In the case where you program is used solely by one person, there is no need for any additional database SW. But I can agree that in the case of multi-administrator scenario, it seems that you cannot avoid it.
    I belong to the first group, so I vote for non-SQL.
    Is it possible to be on the user's behalf to choose?

    Zagreb, Croatia

  • MDarlison

    I guess it depends on the context you use it – we dont use access at all at work, everything is done is sql and for me it would be a definate boon to have the data being stored there. Reporting options etc are not that dissimiliar regardless of what you choose, both access and sql can return similiar information. SQL data store would enable me to link the system into other things (simple example being I could link the history notes from a given user into their service desk report). SQL Express is of course free so there is no real cost implication although there can be a resource overhead. Perhaps offering all three options would be a better initial move, and then seeing how people progress with the different data types?

  • bbradley

    SQL or Access, definitely. I'd think SQL for the future. Why build something you'll have to change later? Seriously, SQL. This would make an enormous impact on our use of the prog and time spent updating groups / individual PCs.

  • long_time_happy-user

    xml is so wide spread that i m sure the multi-users problem will be solved in time.
    Keep away from MS-SQL PLEEEEAAASE !

  • crewsen

    anything to help multi user support. I bought the enterprise version for 7 of us to use and share. We finally dropped the sharing part and we all have local installs. We were restoring the groups.xml file weekly to fix lost groups.

  • joelsplace

    I'm a single user that uses VENM on several boxes in various locations. It would be great to be able to sync it through something like Live Mesh. This may already work? I vote for whatever makes it sync easier.

  • dstrawn

    SQL with option for XML

  • neilcamp

    how about letting me put it on my sql server for easy access across my network

  • seanohlrich

    MySQL/PHP – Low-end web server hosting the shared data.

  • Andrew

    XML would still be the most simple option for single user installs. Anything past that though, SQL is the way to go.

    I agree adding the option to choose which format during installation would be the best way to keep both groups happy.

  • mckinnonk

    I think you need a database. Either Access or SQL.

  • seansco

    I have to local the folder so that only one person can change the xml files. However you are comfortable fixing the issue is fine with me. It does 'feel' like you are going backwards using access but I am sure it is just my bias. Does the new Access still use page locking or does it have record locking?

  • bcartwright

    I would suggest that it depends on your user experience. If you have received a large number of compaints/comments about lost or corrupt data due to overwrites, then it would suggest a migration. If not then it comes down to weighing between a problem of unknown magnitude against the known cost and effort of migrating everyone's data to access.

    For the record, I like the sound of Eric's proposal of writing your own psuedo record locking algorithm.

    Good luck,
    Brian.

  • AlanK

    From time to time I've had problems with the XML files getting corrupted or disappearing. Just today I had to replace settings.xml and one of my managed group XML files from a backup because they had gotten crunched. I don't know why this happens, but it happens.

    If SQL was more bullet-proof then I'd want it.

  • Name

    I'm a single admin, so the lightweight XML option is best for me.

  • We have been using VNCscan since at least 2003. The XML database solution in its current state is not a workable solution for a multi-user environment. I agree with crewson. We are licensed for the Enterprise version and rely on VNCscan for a multi-user environment. We regularly have four or more staff in the application sharing the same xml files. It is unusable. We have to restore the xml files on almost a daily basis.

    Whatever choice you do make, please ensure that you build it for a multi-user environment.

    In addition, we would be happy to trial beta versions for you and to provide specific feedback if necessary.

    We look forward to the continued improvements.