Post reply

Schema is a better approach or Separate database

  • December 29, 2010 at 6:30 am

    #232813

    Hi

    In my company we had 3 separate database all are connected to the same application

    1.DB for static data(contain millions of static data)

    2.DB for application settings (contain only intial settings for appliation)

    3.DB for Inventory (Contai transaction,booking etc.)

    I just want to know whether it is a better aproach to Use one DB with schema.

    which one will be better option in terms of management, scalability,reusable

    Thank you

    With regards

    Dilip D

  • December 29, 2010 at 6:46 am

    #1267129

    I would use separate databases that would give you more flexibility in terms of optimizing and scalability.

    I'd consider keeping the static data and application settings in the same database though.

    The probability of survival is inversely proportional to the angle of arrival.

  • December 29, 2010 at 7:14 am

    #1267148

    I believe using different schema in same db will give you benefit.

    since, you can easily fetch and join tables and using schema you alredy put different data in different box.

    you may have some advantages with separate databases with same application but i think single would be easier to manage...

  • December 29, 2010 at 7:44 am

    #1267172

    If it's all for a single application, I'd go with schema's to seperate out storage, access, security. This way you can have referential integrity across the schema's as needed, something you absolutely cannot do with seperate databases. In general, I try to stick to one database per application unless there are some extreme extenuating circumstances to suggest otherwise.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 30, 2010 at 12:19 am

    #1267546

    Thanks for the reply, i think i should go with seperate DB. I have one static DB which contain millions of data which don't changes fequently. In terms of DB backup there is no need to take the backup of static data..so disk space will be free.

    So i think it is better to go with seperate DB.

  • December 31, 2010 at 1:49 am

    #1268040

    @dilip : if you are going for separate db just only to avoid lengty backup time.

    so, you may try FileGroups, you can keep different data in different filegroups.

    so, you can backup desired filegroup and leave fixed Bulk data portion in other filegroup...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply