January 23, 2012 at 7:16 am
So just ran into a situation where I was asked to see if it would be a good idea or not to take our two production databases and merge them into one, or have one database as the code/engine and the other for the data.
So my question: Why would you have one database for code/engine and a second for just the data? I have never heard of this setup so am curious.
Stephen
Jr. SQL Dev
January 23, 2012 at 7:46 am
I've seen this in cases where you have multiple clients and want to separate their data, but the code is all the same.
However when I've done this, I usually use something like replication to move the stored procs/code to the various client databases and only deploy to the central code one.
How are you calling the code, and from what context?
January 23, 2012 at 7:55 am
Well we have 2 different .net applications due to the difference between users and business requirements. However, the data is the same for both.
My immediate thoughts on this would be that there would be some sort of performance gain, or optimization purpose, but I don't see how having one database for code/engine and the other for just data really helps.
I still don't know why we couldn't (and wouldn't) just leave it in one database (data/code/engine)..
There must be something I am not understanding yet :unsure:
Stephen
January 23, 2012 at 8:00 am
If that's the reason, two apps, use schemas or something else. Having it in two databases isn't providing performance gains, and potentially this is a greater administrative headache.
January 23, 2012 at 8:05 am
When you say ‘the data is the same for both’, do you really mean data or data structure (DDLs)?
I am also finding difficulty in understanding the application architecture. Do you have 2 applications that run on same data (& data structure)? Or you have 2 customers with different business requirements thus you have 2 databases?
It might be very obvious but would you please explain it in detail.
January 23, 2012 at 8:18 am
how you need to report or aggregate the information may play a factor; also whether they ever reference each others data.
the data is really independent of each other, right? there's never a time, other than maybe reporting, where the data needs to reference across to the other database?
do you ever need to join the two data together for reporting? do the tables in the two different database ever need to be placed in the same report , so you'd have to UNION ALL from each database's table sources?
If that was true, I'd think that a single database might be beneficial.
if's the data is really separate animals, then separate databases would be fine, right?
Lowell
January 23, 2012 at 8:20 am
1 app is for administrative (reporting, oversight, management), while the other is for end users (data entry, update, etc).
For the purpose of the question though, lets just say we have 1 database and 2 applications ( as just stated). Would their be any benefit to seperating this 1 database into 2: 1 for just the data and the other for code/engine?
January 23, 2012 at 8:32 am
I think what's confusing is you say "separate 1 for the data, and 1 for the code/engine", but you say 2 applications. So are you proposing, or dealing with:
db1
- data
db2
- code for app1
- code for app 2
When you say code, I assume you mean just stored procedures and UDFs.
If that's the case, there's no reason to have two databases.
January 23, 2012 at 8:33 am
It makes sense if you have multiple users & have multiple databases for users. You can take one database offline and only one user will be impacted. The rest can continue their routine works as usual.
On performance ground, it might help as well. Because multiple tables for users will minimise locking events.
January 23, 2012 at 8:43 am
Thanks Steve. Would you happen to have any links (you suggest) on managing/developing a DB that has calls from 2 or more applications?
January 23, 2012 at 8:48 am
I'm not quite clear what you mean?
The two applications don't matter if they're sharing the same data. You just have code and tables, and you call what's appropriate from each application. If you have applications that need to rev/update a particular table or piece of code, you have to coordinate among the applications to make the change at the same time.
Otherwise, there isn't any need or reason to separate things.
If I have a web application that calls spMyCustomers to get a list of customers, and I have a fat client administrative app that needs a specific list of customers, and calls spGetUSCustomers, those two procedures can live in the database and there's no issue.
If you are trying to prevent one set of developers from seeing the code from the other set, then you might separate the two sets of code into schemas.
I'm not completely clear on what you are trying to do in terms of separation of the applications. Perhaps you could explain it more?
January 23, 2012 at 9:10 am
No, Steve you hit the nail on the head. I left out any details only to get a overall perspective from you guys on the topic.
I am Jr SQL Dev, so I may have worded the question wrong or did not incorporate the proper information. Never really had a need for schemas before, but I see what you are getting at, and now I do!
I also like to ask questions this way, so that it forces me to do research. Typically the research allows me to really fully understand the situation and get down to the nitty griddy (and even learn things I did not know before).
Thanks again for the info!
Stephen
Jr. SQL Dev.
January 23, 2012 at 9:51 am
You are welcome, good luck, and if you have more questions, post away.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply