Creating DataBase
I am re-writing a single-user, local application that supports MS-Access to a multiple concurrent user application that will support MS-Access, SQL Server, Oracle, DB2, and XML.
In the current application, a Microsoft Access Database (*.mdb) file (with structure but no data) is distributed with the application. When the user wants a new database, the distributed file is copied.
With a move to SQL Server, etc. most users will likely not have the ability to create databases on the fly (I assume most companies keep that in the hands of IT). What is the best or at least accepted method of distributing the database structure with an application?
[667 byte] By [
MEW99] at [2007-12-25]
If the application will only run on the users local machine, then you could use MSDE (Microsoft SQL Desktop Engine). This is a single user stand-alone version of sql server that is free. It doesn't come with enterprise manager or anything like that (does come with a command line tool, osql.exe), but it is the accepted way to have a local sql server for the user.
Is this what you were looking for? If not, please give some more details to better answer your question.
Hope that helps!
Sean Chambers
MS SQL Express is a better option.
Actually, the biggest problem with this application is that is single user standalone. We are positioning the new product in the corporate world with multiple concurrent users. For stand-alone use, we will continue to support MS-Access and will begin to support SQL Express (MSDE will not be supported under Vista). Our large corporate users will not purchase a standalone solution. Most are running either SQL Server or Oracle.
The application is being modified to handle multiple concurrent users. My question is during deployment, what is the accepted method of creating the central database structure (again, I assume that this ability is held by the IT groups). Is this a script that the IT department can modify and execute or some other method.
If you have decided to use SQL Server Express, you can attach the database file and make it a part of the deployment (.MDF)... just like any other file.
Optionally you can create the database and the entire schema from your application code itself. The downside of that is it is time consuming for creating the database schema for the first time and if you want to make changes to the schema at any time you have to change the code.
 MEW99 wrote: |
| The application is being modified to handle multiple concurrent users. My question is during deployment, what is the accepted method of creating the central database structure (again, I assume that this ability is held by the IT groups). Is this a script that the IT department can modify and execute or some other method. |
|
The question is how wide-spread your application would be (at the departmental level or enterprise wide) and how customizable it is
Also you need to think about upgrades (for future versions)
If the application is smaller you probably want an unattended installation (script of code) and if it ihas a wider scope then it would probably be better to have it as a script and you (i.e. somebody from your company / implementor) would (most likely) work with the client's IT group to install and configure it
Arnon
SQL Express is the solution MSDE will not be supported under Vista
Why not create / update the database using Transact SQL scripts that you send to the database from code, during a custom setup procedure?
If you're worried about someone altering the script, you can always encrypt it.
This depends on the users having permissions on the DB to create databases which they may and in many case won't have.
Let me get this straight. You're going to build an application that will support multi-user operation on a DB. Your DB should be in some kind of central place and will only be "created" once, during installation. You are actually confortable letting regular users set this up? There should at least be some coordination to make shure there is one and only one DB. Changes are a regular user won't understand.
I'm sorry if I didn't get this right. Then the above may not apply.
You could also ship the .mdf and .ldf files for the database and attach them as part of the installation process. This has the advantage of not relying on a significant amount of knowledge on the part of the the person running the setup. The installer would need db create privileges but that's probably not unreasonable for someone installing software.
If this is going to be a packaged application that is distributed to customers than I recommend that you have an administration utility that can set up the database. Whenever the product is to be deployed in environment, the admin would launch this tool, select the kind of database server they will be working with, and then the application would create it for them.
Option 1: Best for the long run, but harder to implement.
The admin tool would be driven by metadata through a configuration file (say xml), that contains all the metadata for your database in a non-database specific format. Then your deployment tool would read in this metadata and then create the appropriate structure in the destination datasource. Your utility uses providers to handle the specific datasources. This way you would have an interface defined called say IDataSource that would have methods like CreateSchema, CreateTable, CreateField, CreateIndex, AddDataRow. You would then have OracleDataSource,AccessDataSource, SqlDataSource,DB2Datasource implementations. Your app would read the metadata and then it would call methods on the provider to do the actual creation / population of data.
Advantages of this approach
- Easy to use for the admin
- Schema information is stored in one place and easily maintained.
- Additional datasources can easily be added such as say MySQLDataSource without having to change the core code.
Disadvantages
- More work up front to implement
Option 2: Easier to implement, more maintenance
The admin tool would have an IDataSource interface. This time however IDataSource simply has one method called CreateDB that accepts a bunch of parameters such as the connection string and the database name. Then each IDataSource implementation will contain all the code it needs to create the database.
Advantages
- Easy to use for the admin
- Supports multiple databases
Disadvantages
- Requires one version of the code for each different datasource. Changes to the core data structure need to be propagated across all different versions.
Option 3: Easiest to implement, more headache.
No admin tool. For each database you will have a set of scripts / database backups. The DB Admin in the environment wil get shipped the appropriate versions for the database platform they will use. The admin will then have to install / restore the database.
Advantages
Disadvantages
- Requires maintaining several different versions of databases / scripts and keeping them in synch.
- Requires more work / knowledge on the side of the admin
e
