| Author |
Topic |
|
morphviper
Yak Posting Veteran
60 Posts |
 Posted - 2008-03-19 : 11:20:48
|
| I have a DBA who wants to put multiple applications on one SQL Server 2005 and seperate the data by using schemas.I know that 2005 was designed to accomodate this and sounds good.My question is this... If a deadlock happens on one of the application schemas will all schemas be affected by the deadlock. of if one schema is using a tremendous amount of resources, will the other schemas be affected.right now in 2000 if an application deadlocks or runs a massive query the other databases are uneffected and life is normal for them.I am concerned that moving this all to one db under schema's might actually bring issues to our systems. and I know the question will be, why are we getting deadlocks. well the 3rd party software that does this is a terrible software, but beyond my control to change it. (CEO bought it and now we live with it!)Thank you for any advice or suggestions anyone can give me! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-21 : 19:38:43
|
| Deadlock happens on tables not on schema, and you can create db for each app. To handle all kind of queries, you need to spec server hardware properly. |
 |
|
|
morphviper
Yak Posting Veteran
60 Posts |
Posted - 2008-03-22 : 16:05:34
|
| I know that deadlocks only happen at the table level, but I have seen performance issues when dead locks happen. Our dba doesn't want to use seperate db's but one db with many schema's. I just want to go into it completely informed so that I don't make mistakes down the road.Thank you for your response. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-22 : 16:20:10
|
| Sql will break deadlock, not sure what kind performance issue you are taking about. But I'll never let multiple apps share a single db. Because if that db goes wrong, it'll affect all apps. |
|
|
|
morphviper
Yak Posting Veteran
60 Posts |
Posted - 2008-03-22 : 16:28:45
|
| That was my argument, but I am getting push back that won't happen in 2005. but he want's to prevent linked servers. right now we have all kinds of linked server which cause performance issues. So it sounds like it might be a catch 22. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-22 : 17:50:48
|
| What won't happen in sql 2005? Why need linked server in first place if all dbs are on single server? |
|
|
|
morphviper
Yak Posting Veteran
60 Posts |
Posted - 2008-03-24 : 08:54:05
|
| Well right now we have everything on 2000 sql db's and we have performance issues when there are deadlocks, even though we are not touching the locked table, but the system slows down. I guess we need linked server because of permissions, and right now the db's are on seperate servers. Our DBA wants them on 1 server and 1 db. No linked servers, and run everything on schemas. I am a db developer, so I usually just write sql code all day. I never do admin stuff, so I want to make sure that this design is standard or best practice and will not cause issues.I know from a developement point of view it will be very nice, but from an application usage and db point, I don't know the best practice. We do get deadlocks and some apps run some really lengthy queries that bog down the system. I do not have the permissions to touch those queries to enhance. So I want to make sure that we have a smooth transition to 2005 and not make our upper management second guess our future choices. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-03-24 : 12:50:44
|
I would say it is *not* a best practice to have a single db for multiple unrelated apps. it's a single point of failure, as rmaio says.
elsasoft.org |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-24 : 23:02:43
|
| You need linked server if you have multiple sql servers and want to query data crossing them. You may have blocking issue, that will affect performance, instead of deadlocking. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-03-25 : 07:54:06
|
| Your DBA knows that you can have multiple databases on a single instance right?The senario you have described, and as I understand it, is not an appropriate use of schemas. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-03-25 : 08:30:44
|
| I would agree with everyone else on this one it's not a good idea at all to have a single db as the backend for loads of different apps that is just asking for trouble, if there is a problem with that particular db then the whole system will die instead in having either multiple db's or instances this could never happen and would just be limited to that one application.Its up to your company how they want to go about this problem but I am surprised a DBA has even suggested that solution |
|
|
|
|
Schema Questions