design a strategy for implementing various applications in one database

20 pts.
Tags:
Database
Database administration
Database archiving
Database Storage
how can i design a strategy to run multiple applications on one database to ensure a conflict-free database storage?

Answer Wiki

Thanks. We'll let you know when a new response is added.

There are many ways to make sure your database is (almost) conflict-free. It doesn’t matter how many applications use the database, it’s more how many transactions are running against it and what are these transactions doing. So to reduce contention in the database you should follow few rules:

* Transactions as short as possible
* Use lower transaction isolation level when possible
* Partition tables (Enterprise Edition)
* Tune you T-SQL to use indexes properly and lock as less resources as possible
* Spread tables/indexes across physical disks to avoid IO contention
* Monitor memory usage and increase memory if necessary.

But why would you like to have all applications in the same database? Do they all use same tables/objects?
Note that even if objects are in a different database, you can still use them.

There are many advantages in separating applications to different databases, because each database will be smaller and therefore:

* Less contention on Transaction Log
* Less blocks/locks on system tables
* Faster backups
* Less management – you don’t have to spread objects on separate file groups to avoid disk contention (or you do it less than in a larger database)
* Tables are smaller (depends on the design) and therefore IO operations are faster

Michelle.
www.pythian.com.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    When you say "conflict-free", what specifically are you asking? If you mean "deadlock-free", then the best way to ensure that is to make sure that all transactions using multiple tables use the tables in a strict deterministic order - usually alphabetically by table name. As long as all transactions lock the tables in the same order, you will not have deadlocks. So - general rules for a "conflict-free" system: 1. Keep all transactions as short as possible. 2. Use "with (nolock)" for queries that will not be used for updates (i.e., report queries). 3. When accessing multiple tables for updates, "lock" the tables in alphabetic table name order to avoid deadlocks. Note - this may require explicitly locking the tables with a series of "select .. for update" queries before a multiple-table update statement. 4. Ensure that your update transactions use a table's primary key to select the record(s) for update (or at least use a unique index on the table, if the primary key isn't appropriate).
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following