A couple of Databases Versus Many Databases
-
Does anyone know if there is an article out there about performance issues of having a couple of databases or even one database in your sql server versus having many databases. I currently have 69 databases to handle every application separately. I want to go to 1 main database and then name each object with applicationName_tableName (tables) or ApplicationName_Select_UserInfo (SPs).
Currently it takes about a minute to open SQL server for the first time, I understand it's because it has to index it the first time and then subsequent collapses and expansions open the enterprise manager quickly. Same things happens when I try to open Query Analyzer the first time.
I believe if I go to the one database method it would eliminate this issue but what other issues could it create?
Again if anyone knows of an article out there that talks about this issue, I'd appreciate if you could send me a link. Also if you are using this method, please send me a message. Which way is industry standard?
Edward M. Sokolove -
Well aside from recoding 69 apps (client and server side) to change the db connections and all the tablenames/view names and everything related too that. Not much... maybe just a few 10 000s hours of work.
-
I am not going to try to consolidate existing databases but I don't want to end with with 200 databases in a year or two.
Edward M. Sokolove -
I still don't think it's a good idea. If an application needs a db, I don't see why it shouldn't have one of it's own. I know I wouldn't like to be scrolling through 5000 tables just to get to the good application's section.
Also have you considered how long it would take to open that db in EM. Assuming you have 1000 objects in each db. You'd have 200 000 objects to open up when you open the said database. I can tell you it's not gonna take less than 1 minute.
-
Edward,
The number of objects you create is going to be the same, no matter how many databases you create. Remi is right on the mark with his comments.
EM will not be faster if you combine everything together. Plus, your administration nightmares will become very real if you do combine everything together.
Best thing to do is do a good implementation of file groups for your databases and keep them separate where they need to be.
-
I hear what you are saying about the objects, but for every database created there are 52 system objects added does this not create more overhead?
Edward M. Sokolove -
Not as much as you are thinking. Forcing everything into a single DB would actually, IMHO, create more of a problem.
-
Well, for what it's worth, we run several hundred databases in our environment. It creates some adminstrative headaches and some things (like starting and stopping the service) are very slow, but in general, the application performance has not been to much of an issue.
Bob
SuccessWare Software -
And further more ...
Suppose you consolidate the DBs and as a part of weekly maintenance you run a dbcc checkdb repair_fast. This requires single user mode. So it hits a table that it can't process, the job blows up and now the database is stuck in single users mode. Now you are left with a hundred applications that can't run, instead of just the one that should be affected.
This scenario might be a bit contrived, but it gives you something to think about.
Bob
SuccessWare Software -
I decide on what constitutes a database based upon data integrity and disaster recovery. Generally a database should contain those elements that need to be managed together to maintain data integrity. If you need to recovery a database you would want to restore all the objects that have data integrity relationship to the same point in time. Putting data that has little or no data integrity relationship in the same database would mean restoring data back to a point in time for some of the data which you would not wish to back out to. For instance, if I need to restore my customer order database to some point in time in the past (using database backup) I would not wish to restore my accounts payable data to that same point in time so I would manage the customer order data in a separate database from my accounts payable data. However, I very rarely have to restore databases from backup so that may not be much of an issue. Designing a database of related data and containing and maintaining data integrity within the bounds of that database just makes sense and simplifies design, maintenance, and understanding of the database.
-
We have 83 databases running on one installation of SQL Server 2000, and performance is better than when I had 3 on a less powerful dedicated server running SQL Server 7, so I think there are other factors that may swamp the performance issues of having to do cross-database joins. I'm personally the dbo for 11 of these databases, and pull information regularly from 3 others, and I can't say I've noticed a significant hit when I join across databases. But then again, I'm a statistical analyst, so I'm usually building reports rather than running transactions, so maybe what is insignificant to me makes more of a difference if 10,000 users are trying to place orders on your website simultaneously. In other words, I think as with most things there isn't a universal correct answer to this question.
One other issue that I haven't seen discussed in managing security. Part of the reason we separate databases is that we have some data which is highly confidential, and we have choosen to store that on a database to which only the appropriate people have access. Yes, we could manage this via table permissions, but this way we don't have to remember to set permissions on tables when we add them to this database. Even if we took a noticable speed hit, in our case we would still follow this practice.
-
Edward:
Have you checked if you have 'Auto Close' turned on in all your databases? If you open up 'Databases' in SQL Enterprise Manager it will take alot of time to start up all databases if they are closed. In production it is not recomended to have Auto Close on. (I do not know if this might be the problem that makes it take one whole minute to open up Enterprise Manager but I just thought to comment on it...)
Happy hunting, Hanslindgren!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply