SQL Server Performance Forum – Threads Archive
apps & db
is there a general rule for limiting the number of differenet applications per same database? if there are 5 different small applications for ex, that can access the same data set(same db), should a DBA limit this here?each app has its own db?
depends on performance?
5 app per same database here? ???
It depends on the applications. If they cause no performance issues or locking you can have 100s of them. But with some apps not coded correctly when it comes to transactions and other things it’s enough with 2 to cause issues. There is no general rule saying that X number of apps is a good number.
but is it general practice to have say 3 or 4 applications hitting the same database? that seems like it could be a pain to manage. so many places could change the data.
It is the same thing as one application changing the data from many different places though. If the application rely on completely different data, it makes no sense to have them in the same database. On the other hand, if several application rely on the same core data, and have some data specific to only them, you will end up with a star scema on your database. The core will be accessed by everyone. Then each database will have another subject are attached to the core. The big thing is to ensure that the core has integrity across the applications. Derrick Leggett
yes. core data… I have 5 small applications that need the same core data. Then each of the 5 have their own tables, sp, functions, etc. Why not point all 5 small applications to the same database? performance will not be a problem for me here.
that’s the argument i am getting from our Dev Team. must be a hard rule on this from a DB standpoint.
I think that it makes sense to put them all in the same database. They are only small apps, performance is not an issue as you say, and disaster recovery is much simpler with only a single database (unless the core data is all static) Also FK relationships can easily be defined within a single database Cheers
Twan
Say for instance if those applications are limited to a 5 user base then no issues on pointing all the ones to a single database which is easy to administer as suggested. Ensure the h/w is marked to takeup this load without any issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
if i put say 4 apps in the same database, should i put the App names in the db objects? (stored procedure specifically)
I prefer to have the database divided by subject area. Have contact information in a contact area, loan information in a loan area, marketing information in a marketing area. You can then have join tables and application tables that drive business and application flow attached to these main subject areas. This allows you to develop in a modular way, yet maintain normalization of your core data. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
do u put application reference in you stored procedure names?
(with 1 app. per db or 2 or more app.s per db)
I prefer to follow the same rules when I can. Have subject-area specific procedures that perform things relevant to that subject area and are reusable. This would include stored procedures that insert a new contact, or initiate a loan, etc. Each application can then have stored procedures that drive application specific items. If I have two loan origination systems for different types of companies though, I still want them using a common interface that’s unique to the subject area. Sometimes you do need to break the functionality down and give specific functionality by app though, so you always need to provide for that. Anytime a stored procedure is application or subject are specific, I like to have the abbreviation for that as the beginning of the proc, so you can easily see what applies to what. That’s just me though. Just be sure the abbreviations themselves are standardized. If you’re going to do that, come up with abbreviations for non-standard procs also: GEN – crossing application and subject areas and just a general procedure.
UTIL – perform system-wide maintenance. etc.etc. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>