January 9, 2013 at 9:45 am
I have a developer that wants to create 4 different databases on a specific server that what technically seems for 1 app.
ITApplicationSecurity
ITApplicationState
ITApplicationService
ITApplicationLogging
The same application will write to each database based on what function is performing. I told him it would be a better idea to create one database and schema out each function that the application will perform.
Developer came back saying that this design is to support multiple applications and separating the databases out will help with System performance.
Just another fact, the DB server they want it on already has 60+ databases. I just think four databases for 1 app is over kill.
Your thoughts?
January 9, 2013 at 10:11 am
dbdmora (1/9/2013)
I have a developer that wants to create 4 different databases on a specific server that what technically seems for 1 app.ITApplicationSecurity
ITApplicationState
ITApplicationService
ITApplicationLogging
The same application will write to each database based on what function is performing. I told him it would be a better idea to create one database and schema out each function that the application will perform.
Developer came back saying that this design is to support multiple applications and separating the databases out will help with System performance.
Just another fact, the DB server they want it on already has 60+ databases. I just think four databases for 1 app is over kill.
Your thoughts?
Without more details it is hard to have a strong feeling one way or the other. However just given the names of the databases they all sound like a single table db???
Maybe a database named ITApplication with 4 tables?
Security
State
Service
Logging
Of course I would not allow table names that are that vague but that is sort of how I am reading it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 9, 2013 at 12:45 pm
i would go with one app=one database.
it sounds like they have four suites of tables related to a given functionality.
with the current planned design, at some point, you can be sure that some report or functionality will need data combined into a single report.
That is much more easily fixed with your plan for a single unified database.
Otherwise, pulling report data into datatables, merging the data with Linq or something, or else you run into cross database issues, especially with potentially different users for each database.
Based on the name, for the "Logging" database, i bet it's a common log used for all three modules, anyway.
i'd make them use a single database, and just add a naming convention to group suites of tables together
itAppSec_Invoices
itAppSec_Status
itAppState_Stuff
itAppService_Stuff
itAppLogging_Stuff
Lowell
January 9, 2013 at 12:53 pm
If you'd like an easy discussion with your developers, here's my usual approach. Databases are really just backup containers. If the information in these four databases needs to be kept in sync on data restores, then they're one app to the database layer. If not, let 'em loose.
It's hard from a schema/structural standpoint to decide one way or the other as to how this should really be organized, we just don't know enough.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 9, 2013 at 12:57 pm
You might also ask him exactly what performance gains he expects to see with multiple databases.
January 9, 2013 at 1:26 pm
Thanks for the responses all.
The argument came down to, when should a DBA (operations) need to know about the four databases. Development teams say it should be at the QA\staging phase once they are done developing. I feel it should be before development to make sure we know where the database will go once it reaches production. Developers want to create as many DBS as they want in Dev and once it goes to qa/staging then we can tell them where it goes. Only issue I have with this is that we have dedicated DEV\QA servers that mimic production. Why develop on the wrong server if eventually we might move it. Then cause more work for DBAs and devs to move the databases.
We are meeting next week to go over the details on why they need 5 now seperate databases. It will be interesting meeting.
P.S just an fyi, my department does not have strong SQL developers mostly Operations DBAs. This is one of the setbacks i have currently.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply