Best practices to create databases for different applications

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Best practices to create databases for...

11 posts, Page 1 of 2

1 2 »»

Best practices to create databases for different applications

Rate Topic

Display Mode

Topic Options

Author

Message

Mani-584606

Posted Wednesday, October 31, 2012 7:52 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:28 PM
Points: 535, Visits: 1,797

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

Post #1379583

T_Dot_Geek

Posted Wednesday, October 31, 2012 10:32 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, November 08, 2012 9:15 AM
Points: 54, Visits: 82

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

Post #1379612

jerry-621596

Posted Thursday, November 01, 2012 2:52 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 363, Visits: 578

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.

Post #1380041

Mani-584606

Posted Monday, November 05, 2012 8:19 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:28 PM
Points: 535, Visits: 1,797

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.

Post #1381348

Michael Valentine Jones

Posted Monday, November 05, 2012 10:54 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 2,944, Visits: 10,507

Mani-584606 (11/5/2012)

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.

Post #1381380

bleroy

Posted Tuesday, November 06, 2012 2:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:59 PM
Points: 137, Visits: 546

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

Post #1381463

jerry-621596

Posted Tuesday, November 06, 2012 1:37 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 363, Visits: 578

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.

Post #1381702

schleep

Posted Wednesday, November 07, 2012 6:18 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:38 AM
Points: 344, Visits: 601

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.)

Post #1381942

scogeb

Posted Wednesday, November 07, 2012 8:06 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:08 AM
Points: 64, Visits: 315

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.

Post #1382004

arnipetursson

Posted Wednesday, November 07, 2012 9:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 135, Visits: 414

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.

Post #1382064

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions