SQL Database Backend for Accounting Software

Rick Sharp asks: "At my firm we use a pretty good mix of Linux, BSD and Novell solutions for our networking needs. We don't have any NT servers, and don't intend to start either. However, I was told today by the vendor of our accounting software package (Harper and Schuman) that our program would require MS-SQL 7.0 after the next upgrade cycle, which is only a few months off. Does anyone know if it is possible to use this package with a Linux SQL database. We currently are using PostgreSQL on Linux for some other projects, and I would not like to get sucked into an MS-SQL only solution."

Comments

Lemme Guess...

[Anonymous Coward]

Lemme guess, Solomon?

sql

[Anonymous Coward]

Has microsoft used it's "embrace and extend" policy on SQL now?

Re:sql

[Anonymous Coward]

Actually MS-SQL is more ANSI SQL Compliant than Oracle's PL-SQL... yes there are extensions, but not nearly as many as Oracle...

Re:sql

[IntlHarvester]

Yes, but MS's strategy is to encourage whenever possible to avoid writing raw SQL. They have embraced ANSI SQL, but the "extend" part is OLEDB, ADO, DTS, MS OLAP, and the numerous other services that ride on top of MS-SQL.
--

Re: What about a simple version

[Anonymous Coward]

There isn't even a Quickbooks version available. I hope we see something soon. Mark

Re:ODBC Socket Server

[Anonymous Coward]

Thats not the point! He wants to use a non-NT sql server.

The Deadly Embrace and why it's (now) hurting

[Anonymous Coward]

You let your Application Vendor specify the underlying RDBMS before and all they are doing are changing their mind as to what that should now be. IMOpinion the damage has already been done. Don't even think about changing your vendor until you are stone cold certain that you can still export all of your data in usable form (i.e. without missing but crucial tables / columns etc). Another thing you have to guess is ?Are there any stored procedures running on the server? If there are, well you are totally sunk because they are an integral part of the Application. All in all, sorry to bring bad news but bad news is all there is. Make better choices for RDBMS and Applications next time (mandating ODBC compliancy for your Application to perhaps Level 2 would be a good start). Eager to give advice to a fellow RDBMS user - High.Flight@btinternet.com

Re:Oops!

[Steve Philp]

Just wanted to reply to this (even though it's wildly off-topic).

I've been working with ODBC Socket Server for about a month now, and I'm incredibly happy with it.

We're tied into a Centura SQLBase backend for our ERP package but wanted to publish some of that data to our webserver. Centura doesn't make enough information available to create a Linux-side client, leaving only JDBC as an option. I'm not familiar with Java and don't want to be.

ODBC Socket Server works great for us and has allowed us to stay with the web server-side languages that we're familiar with (Perl and PHP3).

PDC on the SQL server? Ooohh bad MCSE, bad MCSE.

[Colin Smith]

Sorry, I've been through the pain. PDC, BDC and SQL server as separate boxes, preferably have the PDC at a separate site.

Also, with accounting systems, you don't

"periodically come back or dial in to verify everything is working OK"

, you

"have a competent systems or application administrator onsite to fix problems before they happen"

.

Adding another platform is expensive.

[Colin Smith]

Ooh I don't think I like the sound of this. Nest they'll be telling you that you must upgrade twice per year and give them kickbacks (on top of the licenses) to be allowed to run their software.

It'd be worth mentioning to them that in order to run NT/SQL server 7, you would have to employ an NT sysadmin, a SQL server admin, buy hardware for PDC, BDC and SQL server system, upgrade the network to cope with the additional load, consultancy on the topology, training, PC upgrades to run the new clients etc etc.

Sounds like it might be cheaper for you to go with someone else's "more expensive?" accounting software...

Re:Adding another platform is expensive.

[KyleCordes]

It's not that bad.

I'm an MCSE (and heavy user of Linux), and will speak against the "party line" by pointing out that you can easily run the PDC and SQL on the same (decent) server, your network will very likely be just fine since it is supporting the accounting software now, and you don't need a full-timer on staff to keep it running.

Pay a competent NT/SQL admin get it up and running, then have them periodically come back or dial in to verify everything is working OK, etc.

If they are also moving all the client to Windows, Colin is right, you need to include the cost of a bunch of new PCs etc.

NT has more than it's share of Issues, but it works fine with good people at the helm. Can you really say that Linux or Unix tolerate incompetent sysadmins any better than NT? I can't.

Re:Adding another platform is expensive.

[IntlHarvester]

MSSQL doesn't require NT authentication. That means there is absolutely no need to have PDC/BDC boxes.

An NT domain is really only required to support a bunch of Microsoft infrastructure. One SQL server doesn't really justify an entire domain.
--

Re:Adding another platform is expensive.

[lazlo]

I'm also an MCSE and Linux advocate. I'd have to say that you don't have many choices, and my first reaction is that if you don't like something about a package at first blush, chances are you'll find other things you hate about it deeper in. If you have problems like this, conversion to some other package might be your best option.

the next question is, do you have to upgrade? The version you have works, on the system you have. If when your vendor calls you and tells you that they've got their new version out and you should upgrade, just say "Sorry, but your upgrade won't run on our servers. As soon as it does, we'll look at it." Who knows, they may even pay attention to you. (but probably not. The company I work for is one of two dozen customers using the accounting package we have. Even though we represent about %5 of their market, they don't listen to us.)

But, in the end, I will have to say that adding an NT/MSSQL server shouldn't be too big of a problem. The biggest problem with most MS systems is admins and users mucking around with things they don't understand. If you just install NT, and install MSSQL, and pretty much leave the server alone after that, chances are it'll do OK. Microsoft makes the occasional great product, a few "fair to middlin'" ones, and many that really suck. NT Server and MSSQL seem to me to fall into that middle category. If you have reasonably competent people who know enough to run some other SQL server and are generally fairly careful, chances are you won't have any problems. The biggest headache I suspect you'll run into is figuring out how you want to do user authentication. Most accounting packages deal with this poorly. Very poorly. But now and then there's one that does, and if you want to be really slick you could set things up such that your users are logging into sql via integrated security (i.e. it authenticates against the NT OS password), in which case you may want to put your PDC on one of your linux boxen.

But that's just a suggestion. Personally I've always had a lot of fun with integrating Linux and NT networks. But then again, I'm kind of a sick and twisted person that way. :)

Finally, none of your choices is all that bad. If it were me, I'd ask the vendor to make changes, and if they ignore you, ask your bosses to try and find a better vendor, and if that doesn't work, oh well, there probably won't be too many problems (and you'll get something new to put on your resume)

Good luck!

Re:Adding another platform is expensive.

[Malcontent]

I think the point is that in a shop full of unix sysadmins getting an NT sysadmin is going to cost big extra bucks (even if it's a one time charge). It may be cheaper to switch the accounting system.

Sybase?

[RedDirt]

Why not use Sybase on Linux? It's protocol is wire-compatable with MS-SQL and should do what you want to do.

Re:Sybase?

[IntlHarvester]

MS SQL 7 was a huge rewrite, and any application targetted to version 7 is no longer Sybase-compatible.
--

not so humble opinion.

[BiLlCaT]

any accounting package worth it's nutsack that is sql compliant shouldn't limit itself to M$. i work for a firm that is in the accounting software biz. i asked one of our programmers what it would take me make our software connect to other SQL engines... his response : change an ini file. SQL is sweet as long as you don't fall into the traps of writing exclusively for any one package AND STICK TO THE STANDARD... it is, after all, a standard. Any package that doesn't should be ashamed... imho.
------------------------------------------
the amazing bc
latin/funk flugelhorn & trumpet
webnaut, music junkie, sysadmin from hell

Re:Options

[KyleCordes]

I third this. Go with what they spec (SQL7 on NT) or get a new package.

You don't stand a ice cube's chance in hell of seamlessly moving someone else's front end over to talk to a different DB they don't support without having the source code and programmers who can adjust for differences in SQL dialects.

SQL7 doesn't seem bad at all for what we run it for (to back up SalesLogix). It has been reliable (no crashes) and fast.

Re:PDC on the SQL server? Ooohh bad MCSE, bad MCSE

[KyleCordes]

The key idea is that someone checks things out, fixes problems before they become big problems, etc. I don't think it is absolutley necessary that they be onsite to do this. NT is not as easy as Unix/etc. to admin offsite, but it can be done.

Certainly they should not need a person there working in it all the time.

Re:Options

[IntlHarvester]

What the question doesn't tell us is what the current backend is ... My guess is Paradox, FoxPro or some other XBase system.

So while it's true you can point fingers at MSSQL and say that it isn't as good as Oracle or DB2, in this case it's a probably huge improvement over the existing system.

MSSQL 7 handles small datastores without much trouble, and with virtually no DB administration required. It's often used in 'turnkey' packages like accounting software for this reason.

It sucks that you have to support a new platform (NT) just for one application, but as others have said, the only real alternative is to switch vendors.
--

Re:Options

[Bryan Andersen]

There is a third option. Tell the vender to support Oracle or Sybase as backends on non NT environments or lose your busisness. Put the upgrade on hold, and start looking for replacements. When you send the letter make sure it is signed by your cheif technical officer and CEO. Also send it to their CEO/president as well as your salesman. Give it some teeth.

Re:Oops!

[gmhowell]

No apologies necessary. I've been looking for something like this for a little while. I think:)

I'm going to check the page now.

The road ahead

[jocknerd]

First you get a Win2K Server to run SQL Server 7. Next, you'll find that your accounting package will require Exchange. So you'll need another server with Exchange on it. Your accounting package will probably require you to have Office 2000 to run reports. So another Win2K server running only Office 2000. And your accounting package will become Web-enabled but only using IIS. So another Win2K server is needed as a web server.

So you've just bought 4 Win2K servers because your accounting package will use only SQL Server 7. What's that $25,000 minimum. I'm not even including licensing.

By the way, the city I work for is getting ready to start looking into enterprise financial systems to replace our legacy accounting system. Why do I have this fear that they may try a system that runs on Win2K? Why not. We spent $500,000 last year upgrading to Office 97.

You may be looking for a new vendor

[New Breeze]

We do development with several SQL backends, and my experience says that they likely won't be able to easily switch backends, and you'll never get them to support it.

And I've done a lot of looking, a good business accounting package for Linux doesn't seem to exist yet. I'm not talking quickbooks, but something you could actually use for a medium to large business.

Re:Oops!

[Malcontent]

Hey neat idea!. I'm going to play with it for a while and it's just exactly what I've been looking for. Do you have any Java examples? I think it would be awsome to get to it from an applet.

ODBC Socket Server

[TeamFXML]

ODBC Socket Server does exactly what you want. ODBC Socket Server is an open source database access toolkit that exposes Windows NT ODBC data sources with an XML-based TCP/IP interface.

It includes clients for Perl, PHP, C++. The server is GPL, clients public domain.

I am the author, e-mail me with any questions at fxml@excite.com. Check us out at: http://odbc.linuxbox.com.

Oops!

[TeamFXML]

My apologies, I just re-read the post now and realize that this question is specific to your accounting package... humblest apologies. ODBC Socket Server will let you access MS SQL from Linux, which you don't need. Sorry about getting your hopes up.

Re:Options

[phutureboy]

I second this. Either get an NT server or a new accounting package.

There are way too many little differences between SQL databases to transparently move from one to the other, 'specially without the blessing or support of the application's vendor.

IMHO, MS SQL Server 7 is one of the better MS products. It seems to work well as a backend for small-to-mid size web sites. Unfortunately it runs on NT :P

--

Could work with Sybase

[Matts]

I'm the developer of O'Reilly WebBoard's NNTP server. I do all the development at home on Linux and Sybase. The product ships on MS SQL Server 7 (or 6.5). It does take a little bit of work to make the two compatible, but not much. However I suspect it's an awful lot easier going from Sybase to MS SQL than the other way around, YMMV. However ultimately I doubt very much the licence for the package will allow you to change db like that - you'll lose all support.

Options

[PhiRatE]

To be honest and up front, I don't see that you have more than two options. Get NT or drop your accounting software vendor. MS-SQL 7 is, from experience, a particularly nasty piece of work.

Although a vast improvement over 6.5, and over the old DBs of the past, this is by no means saying anything worth being said, and both the Open Source, Oracle and IBM solutions are far more effective.

My recommendation would be to inform your vendor that you have no intention of getting MS-SQL 7, and if they want you to upgrade they had better find another solution. Back-ending into an SQL database in these cases is trivial, if your vendor has any sense they will have been using ODBC for some time, or at the very least the standard TDS 4.2 protocol that both Sybase and MS-SQL can talk.

If this is the case, you can at the very least back-end into Sybase, or more hopefully, anything with the relevant ODBC drivers.

If the vendor won't budge on this issue, my personal recommendation would be to drop them and find someone with sense. I wouldn't want people so obviously narrow minded writing my software. But I'm kinda hard-line on that sort of thing, you may well end up having to install NT/SQL-7.

In this case, I can offer one ray of gloomy light. NT is stable so long as its only doing one thing. From experience, if you install SQL-7 and other required software, and nothing else, and do nothing else with the box, and don't try anything tricky, it will remain relatively stable and perform reasonably.