September 12, 2008 at 8:26 am
Hi, i have a situation in my work and i would like to know if we are doing the right things.
Here every table we create for every application are stored in the same database, obviously this database will grow exponentially. Can somebody tell me if it's ok? or if we have to create a database for each application we make?.
I think so the 2nd, but i'd like to have some argument for discuss that.
Thanks in advance.
September 12, 2008 at 8:32 am
It can depend upon the situation. If you have two or more applications using substantially the same dataset - I'd consider that fair enough. If you mean one database containing the data for a whole mess of unrelated applications than I'd regard that as a bad idea
September 12, 2008 at 8:43 am
Keeping all data for disparate applications would be a hassle if you needed to restore only one app's data from a backup. Reminds me of my Oracle 7 days when data was in a single instance and a backup and restore affected everyone.
Greg
September 12, 2008 at 9:15 am
If the applications are not sharing substantial portions of data, different databases. Further, when you put them on different databases, don't allow cross-database queries. This is PAINFUL experience speaking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 12, 2008 at 10:10 am
Grant,
Out of curiosity, what problems have you had with cross-database ownership chaining?
Thanks
Greg
September 12, 2008 at 11:06 am
As mentioned, it depends on what data you're keeping and why.
For example, if all of your applications have to access the same list of customers, then only keep one list of customers, in one database, and have all the applications access that. Seems pretty obvious, but I've seen similar things get all messy. Lists of US states, lists of orders, lists of marketing events, lists of Zip codes, even Numbers tables, in dozens of different databases, with all kinds of weird hacks to try to keep the data "sort of mostly synchronized".
On the other hand, application isolation can be nice if, for example, you need to move one application and its data to another server, for performance or security reasons. Hard to do if its tables and procs are mixed in with a dozen other apps, easy to do if it's a separate database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2008 at 11:09 am
Another limitation of having multiple databases is not being able to create an indexed view across databases. Big, ugly hindrance but due to schemabinding I can understand why. Don't like it but I understand it.
So, make sure that it makes BUSINESS sense to seperate it, i.e. they are truly distinct sets of data and rarely / never the two will meet.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 12, 2008 at 11:12 am
Greg Charles (9/12/2008)
Grant,Out of curiosity, what problems have you had with cross-database ownership chaining?
Thanks
As long as those two databases live forever side-by-side... None.
As soon as you put them on different servers and you have to start working with linked servers... security, performance, network... issues start to arise.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 12, 2008 at 11:31 am
Understood, thanks a lot to all.
September 12, 2008 at 12:25 pm
However even if the applications share a SQL 2005 database you can still obtain some separation by placing the tables that are not common in different schemas.
September 12, 2008 at 12:37 pm
dmoldovan (9/12/2008)
However even if the applications share a SQL 2005 database you can still obtain some separation by placing the tables that are not common in different schemas.
True.
The problems we've run into with multiple applications sharing a database are not from performance or configuration though. As a matter of fact, the shere number of connections and apps running all different kinds of queries that we've pushed through shows the strength & versatility of SQL Server. No, our issues came because of development and deployment. Application A wants to change a table that Application B references, but Application B is in the middle of new development and doesn't have time to go back and fix the problems. Application C is ready to deploy to production, but it needs Application D to make some changes to one of it's stored procedures, but it just so happens that Application D has already been working on that proc and they've already changed it in a different way that Application C wasn't ready for...
And then it goes down hill...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 12, 2008 at 12:56 pm
Grant, that's true, things can easily go "crazy" in this situation...SQL 2005 is great, however this does not extempt anybody from carefully planning ahead...
September 12, 2008 at 1:14 pm
Well, I was concerned about the performance, by the fact that with the passage of time a large number of applications would be running instructions against the same database. Although several applications consult several different tables, if these tables are in the same database, they are running instructions on the same file.
September 15, 2008 at 6:22 am
Performance, assuming a proper data design, good indexes, well written queries, etc., is not an issue. Supporting hundreds of users running queries is supporting hundreds of users running queries. It doesn't matter if they app is named "A" or "B."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 15, 2008 at 7:06 am
Performance would be the least of the reasons to separate databases by application. There's no difference between a server accessing one database for a thousand users, or accessing ten databases for a thousand users. It's the same CPU, RAM, drives, etc., regardless of the number of databases it's split into.
The only time that matters is if you want to move one or more applications to a separate server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply