December 8, 2008 at 4:15 am
Having worked with Oracle databases as a developer and a dba for over 14 years, a new client project requires SQL Server 2005.
Our development process on Oracle has to date given each developer one or more users (a combination of Login, User & Schema in SQL Server 2005) in a single Oracle development instance. Developers share nothing except the database instance; the build scripts pick up each developer's DB login credentials from a myconfig.properties file to create the tables and test data necessary for regression testing in each Oracle user.
My basic question is this: Should we use multiple databases or multiple schemas?
Related to this are questions of ease of management and scalebility: Which option is easier to manage? Which option will scale better? Transaction performance is an issue as each regression test drops the tables, recreates them, inserts test data and runs the unit and intergration tests invoking further CRUD. Data issues - e.g. backups or size of datafiles - are not really a concern as everything is scripted and volumes are small.
Thanks in advance for your help.
Jean-Marc Reynaud
Oracle DBA
RDF Group, 2 Bartholomews, Brighton, BN1 1HG
December 8, 2008 at 6:21 am
Well, the issue is mostly one of maintenance and how much work you're planning to put into it. The two most important questions I see are:
Do you really want your scripts searching through multiple dbs just to pull the scripts from various developers?
Do you actually have the hardware resources to efficiently serve multiple connections in the same database?
If yes to question 2, I'd advise going with multiple schemas instead of multiple databases. The maintenance on one DB is easier than on multiple DBs, IMHO. I'm talking backups, security, optimizations, etc. The fewer instances & databases you can get away with from an administration stand-point (without sacrificing security or consistency) is best.
December 8, 2008 at 7:47 am
jean-marc.reynaud (12/8/2008)
...My basic question is this: Should we use multiple databases or multiple schemas?Related to this are questions of ease of management and scalebility: Which option is easier to manage? Which option will scale better? Transaction performance is an issue as each regression test drops the tables, recreates them, inserts test data and runs the unit and intergration tests invoking further CRUD. Data issues - e.g. backups or size of datafiles - are not really a concern as everything is scripted and volumes are small.
Thanks in advance for your help.
Having worked with both Oracle and SQL Server for several years, I'd say the main thing is if the developers are working on the same program/system or not. In an Oracle environment, it's common for different programs to be in separate schemas (and tablespaces), and there are easy ways to work in a schema and not affect the rest of the database such as being able to use the Export and Import utillities on a specific schema. In SQL Server, there really aren't any tools that work at the schema level, so in your environment it sounds like it will likely be easier to manage having different databases for different programs.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply