October 31, 2012 at 7:52 pm
Hi,
We are in a process of developing 4 different applications and they are all not related.
All 4 application developers created their own tables for each application and requested to create 4 different databases and create their tables
Question:
What is the best praice for this scenario?
1. Create 4 databases for 4 different application and create tables?
2. Create ONLY one databases and create 4 different schemas for 4 different applictions and create tables in those schemas?
Please advise
October 31, 2012 at 10:32 pm
I am not a DBA. Here are my two cents as per developer's point of view.
1. Create 4 databases for 4 different application and create tables?
Pros - it is easy to identify and navigate through tables, jobs, DTS, views, sp's, Fx and other components
Pros - Database security would not be cumbersome and can easily be customized for each seperate database
Pros - If one database turns offline then other databases won't be affected
Pros - High Scalability and database distribution
(e.g. if there is unexpcted growth of the database threats exceeding server hdd size then it is easily migrated to another server)
Cons - Seperate maintainence plans needs to be written for each single database
2. Create ONLY one databases and create 4 different schemas for 4 different applictions and create tables in those schemas?
Scenario One's Pros are Cons and Cons are Pros in Scenario Two
November 1, 2012 at 2:52 pm
Unless the applications need to share a large chunk of common data, which it does not sound like it is the case here, I would always create separate databases for applications.
Segmentation will work in your favor throughout the application life cycle. This includes the almost inevitable request to move one application into it's own instance if it gets to a point that it is consuming too many resources for a shared environment.
November 5, 2012 at 8:19 pm
Thanks Jerry,
We have only one production ORACLE database with 6 schemas in it and having this makes database maintenance is very easy as application works great.
For example set up data gaurd for one database, set up backup for one database.
Why the same is problem in SQL Server? why we need separate databases?
Is SQL Server schema exactly same as Oracle or not? What is lacking in SQL Server schema?
If you have multiple databases in SQL, will need set up database mirroring/Log Shipping for all databases. Maintenance overhead right?
Please advise.
November 5, 2012 at 10:54 pm
Mani-584606 (11/5/2012)
Thanks Jerry,We have only one production ORACLE database with 6 schemas in it and having this makes database maintenance is very easy as application works great.
For example set up data gaurd for one database, set up backup for one database.
Why the same is problem in SQL Server? why we need separate databases?
Is SQL Server schema exactly same as Oracle or not? What is lacking in SQL Server schema?
If you have multiple databases in SQL, will need set up database mirroring/Log Shipping for all databases. Maintenance overhead right?
Please advise.
The real difference is that in ORACLE you almost have to do it that way, since you can really only have one database per instance.
In SQL Server, you can have as many databases as you want on an instance. You can implement different schemas in the same database, but there is really very little advantage, unless they are closely related in the same application.
November 6, 2012 at 2:57 am
Hi,
I remember viewing this video on Channel 9 a little while ago and found it very interesting and it helped me lots to understand the differences between Oracle and SQL Server, especially the bits about Server/Instance/Database.
SQLShorts: SQL Server for the Oracle DBA (K Kohrs/M Wilmot):
http://channel9.msdn.com/Blogs/mwilmot/SQL-Server-for-the-Oracle-DBA-K-KohrsM-Wilmot
B
November 6, 2012 at 1:37 pm
It can be done either way. If it is easier to maintain all your applications' data in a single database then that is your business case.
However, setting up separate maintenance plans, backups, replication, etc. is pretty much a single activity unless something changes.
Providing single database for single application enables much more flexibility for moving applications to different environments altogether if the need arises. It also ensures that a corrupted database will not bring the entire organization to its knees.
In the end, it all comes down to an organization's individual needs being weighed against the risks of either configuration. If a good business case can be provided, that is fine. But a business case doesn't always take infrastructure and other organization needs into account unless it is very carefully crafted. Business needs or assumptions can be very counter intuitive to what is the best for IT and business continuity at times.
November 7, 2012 at 6:18 am
If the apps really are not related, I can't see any reason not to have separate databases.
Setting up maintenance plans / log-shipping / etc... are one-time costs.
What happens if you need to do a restore? Restoring a single schema is not supported, I don't think.
(Obviously it can be done, but probably not in a timely fashion.)
November 7, 2012 at 8:06 am
I would do 4 seperate dbs for sure. Restores would be easier and the segregation would allow for easier managment. The initial setups of jobs is a one time deal.
November 7, 2012 at 9:56 am
Think of a database as a unit of recoverability within an instance.
Think of a schema as a security container within a database.
Often you want to have multiple databases for a given application, particularly when you want to separate out the transaction logging of different application activities.
November 7, 2012 at 10:51 am
Separate databases can have different database options set. Recoverability is a BIG advantage partularly in development. If one developer hoses what he's doing and wants to revert to the backup 2 days earlier, that's very easy to do provided you have the backup. As was pointed out, restoring schema objects or data from a database backup restored elsewhere can be done, but it would require a lot more time & careful planning if you don't want to throw away work within other schemas.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply