Fog Creek Software
Discussion Board




Shrinkwrap db app deployment

I have an idea for a shrink-wrap enterprise database application which I plan to develop (and hopefully sell). The application would be a web based client running on an intranet. One requirement is that the customer would need an existing MS SQL Server on which the database could be set up and operate.

My problem is that I've never seen a packaging and deployment tool for installing database files. When I've seen enterprise database applications like these in the past, a field engineer from the company would install the database. But I've never seen it come on a disk so that the customer's own system administrators can install it.

How is this normally done?  Are there package and deployment wizards for this?  Or, do software vendors simply provide all the DDL, DCL, and DML files for the system admin to run themselves?

Nick
Monday, September 30, 2002

My feeling is that if a customer has a DBA then the DBA will want to see the scripts and execute them.
It is possible to execute SQL using a COM component (I forget what, the name SQLDMO springs vaguely to mind) but I am not convinced you will be thanked for this.

If the user does not have a DBA then you are probably in trouble, SQL server is probably too complex to say 'buy it, install it, get on with it', I think you are too likely to be blamed when they screw up / find out they haven't got any backups.

Then again, you can ship MSDE (under certain conditions that  have been covered here recently IIRC). Obviously you then have to provide your own UI to simple tasks e.g. database creation  / deletion, user addition / deletion, backup, restore.  This is what we did, I rather wish we hadn't since
a) the vast majority of users use Access/Jet databases
b) the few that use MSDE find it complex and awkward to use.


For our software, for SQL Server we provide database creation scripts (which create the tables in a database the user has created manually in the SQL server admin), and scripts to grant access to all our tables to a particular role or user.

Harvey Pengwyn
Monday, September 30, 2002

We wrote a specialist installer app - the user/admin has to provide server and log in details - the installer then reads version information from the database and then fires the relevant scripts at the database in the correct order. 

Therefore a relative novice can install or upgrade the database (the system is plastered with warnings about backing up, which of course users then ignore), or the admin can look in the correct folder and run all the scripts manually.

For actually running the scripts we set up an ADO Connection to the database, parse each script file looking for the GO statements and execute each chunk in turn, while updating a progress bar.  It's not perfect but it does the job. 

Rahoul Baruah
Monday, September 30, 2002

The whole MSDE conversation didn't make sense to me. Microsoft plainly states that "MSDE 2000 is a royalty-free, redistributable database engine".

http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp

And the EULA grants very acceptable redistribution rights:

http://www.microsoft.com/sql/howtobuy/deveula.asp

pb
Monday, September 30, 2002

I don't think anyone (me or otherwise) was suggesting that the distribution rights were particularly restrictive or onerous just that they were not 'do what the hell you like with it regardless of whether you own any Microsoft tools or not'.

Harvey Pengwyn
Monday, September 30, 2002

If they have SQL Server already installed, you should be able to create an installer that will execute the scripts to create the database for you.

I have recently downloaded some asp.net sample applications that will do this for me, here is an example - http://asp.net/Forums/Download/

Ben
Monday, September 30, 2002

Thanks for the feedback. I guess if I ever sold a copy, I would just provide the database scripts on a disk.  If I sold a lot of copies then I would consider writing an installer using SQL-DMO, but it wouldn't be worth it at inception.

In just thought there might be a packaging tool available for this purpose.  I envisioned something like InstallShield, where the user would simply connect it to the server, select the database, and walk through selecting the elements to create.

Nick
Tuesday, October 01, 2002

Back several years (and companies...) ago, I helped to install new help desk software at my company. The default installation used an Access database (i.e. .mdb), but they recommended using a 'real' database if there were more then 5 users.

The installation for a new database was really easy. We had to create a new ODBC datasource and then tell the software which datasource to migrate to - it created the new database via standard SQL statements and then copied the information from the old database. Quick, easy, and very painless.

jeff
Tuesday, October 01, 2002

Every time I see one of these discussions about problems deploying database applications, my mind wanders back to the good old days when I experienced the same trouble.

Everyone needing simple to medium complex database support on Windows, should take a look at DBISAM. It puts all database functionality right into your exe file. For a 400KB larger exe file your database application will work anywhere, anytime. No more ODBC, RDO, DAO, ADO, OLEDB, ADO.NET, MSDE, YouNameIt.dll's, system/middleware deployment/configuration or weird conflicts ever. All you have to do is copy your application.exe to your customer's computer and it will work.

The disadvantages are:
- A 400KB larger exe size.
- The database is a directory with 2/3 files per table and not a single file, like in Access.
- It is not fit for high end.
- Some might find the fact that you need Borland Delphi or C++Builder a disadvantage.

And oh it comes with a royalty free server (if you need it), full source code, in-memory mode and dozens of other exciting features.

This might sound like an advertisement, but these guys really deserve it, because they make database support a no brainer:

www.elevatesoft.com

Jan Derk
Tuesday, October 01, 2002

If you don't want to be forced into the Borland camp, there's a nice package at http://www.sqlite.org which is well worth looking at. A SQL database, embeddable into your app. Pretty nice.

Chris Tavares
Tuesday, October 01, 2002

Does anyone know any super-lite-weight, microDB written in PERL like dbunder http://sullof.com/dbunder/ but even more lite-weight?

pb
Tuesday, October 01, 2002

You can write a VB/VBScript app to run sql scripts via SQL-DMO, it is what I did in a previous project.  I'm surprised InstallShield doesn't come with a SQL script to do these types of things...

Tim
Wednesday, October 02, 2002

Nick,

Back to your original question, I was able to locate the following (something I've been interested in for a while myself).

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_embeddingmsde.asp

Jeff MacDonald
Friday, October 04, 2002

Jeff,

Thanks for the link, but MSDE would be too limited in scale for my application.  It would have to be a database that would scale to possibly 100's of users.  The application would be targeted toward small to mid-sized companies, and MS has that market with SQL Server 7 & 2000.

Nick
Saturday, October 05, 2002

*  Recent Topics

*  Fog Creek Home