No more hiding in the server room
Dealing with database sprawl
This is my first blog here … I hope it’s not too long winded.
I have too many databases servers running. The problem is basically that many apps want a database and we just end up installing another one for every app we deploy. Installed RT so we added another copy of PostgreSQL, setup MediaWiki so we also installed another copy of MySQL, added another line of business app and we ended up installing another MSSQL instance, etc … and it just keeps adding up. Off the top of my head I can think of 3 copies of PostgreSQL, 4 copies of MSSQL, 2 copies of MySQL and a copy of Oracle running. That’s just what I can think of right now; I know there’s a lot more out there. All of these support only one app each and run only one database each. Moreover during this year we’re going to bring up another three apps; two of these require Oracle and the other wants DB2 or Oracle.
Some of my staff have said: “Who cares? Just install them and move on. What’s the problem?”
I feel that the problem is that all these databases need to be maintained and they all consume resources. The most important issue is that these all need to be maintained. They all need patches (nobody wants to have an exploit on that copy of MSSQL express that came with BackupExec for instance) and they all need to be configured, backed up, and monitored.
Another important consideration is that we’re trying to move to a more controlled environment based around scripting and automation. The more databases we have out there then the more complicated that becomes; it’s not enough just to install and configure the app now we have to deal with the backend DB as well. For resources it’s true that that copy of MySQL backing a wiki doesn’t consume much but when you have 10 copies of MySQL sitting around it does add up.
So what we’re looking at is consolidating it all down to two DB platforms; Oracle and MSSQL. Then we’ll further consolidate that down to a cluster for each. MySQL and PostgreSQL have their good points and being open source is not the least of these but not all of our apps support them. Between Oracle and MSSQL we can support all of our apps and both have mature robust clustering support. This won’t be completely set in stone and some apps may justify a dedicated database for various reasons but the majority of our apps will be backed by one of these two clusters.
Aside from killing off all these single purpose instances we’ll also be able to justify clustering and DR replication. Using RT as an example few people have a clustered RT backend, it might be nice but hard to justify. On the other hand I can justify deploying a cluster if it’s going to support a whole pile of apps across the organization. It’s like VMware HA; it brings higher availability to apps that otherwise would never have justified it.
Some readers may ask: Are you insane! What about performance and licensing costs?
First I need to say that this applies to my scope which is a SMB size non-profit. So what that means is that many of these DB instances are lightly loaded and further more we get highly discounted licenses. So while this won’t be cheap it won’t be hundreds of thousands of dollars either. From a performance standpoint even a modest two socket quad core box with say 32GB of RAM should be able to handle every DB we have with ease. We will have more then one box but it will be for HA not performance. So while this would not be feasible for every organization it is for us.
So that’s what I’m planning for dealing with DB sprawl. Consolidate it all down to a handful of high quality boxes and keep a close eye on those boxes. It will make our applications servers easier to deploy and maintain and it will allow us to offer more sophisticated database functionality for all the applications not just a favored critical few.
Anyone else dealing with DB sprawl? What, if anything, are you doing about it? Got any feedback for me? Think this awesome, think this is the road to hell? This is still in early planning so I’m more then happy to hear anything you’ve got.
I have too many databases servers running. The problem is basically that many apps want a database and we just end up installing another one for every app we deploy. Installed RT so we added another copy of PostgreSQL, setup MediaWiki so we also installed another copy of MySQL, added another line of business app and we ended up installing another MSSQL instance, etc … and it just keeps adding up. Off the top of my head I can think of 3 copies of PostgreSQL, 4 copies of MSSQL, 2 copies of MySQL and a copy of Oracle running. That’s just what I can think of right now; I know there’s a lot more out there. All of these support only one app each and run only one database each. Moreover during this year we’re going to bring up another three apps; two of these require Oracle and the other wants DB2 or Oracle.
Some of my staff have said: “Who cares? Just install them and move on. What’s the problem?”
I feel that the problem is that all these databases need to be maintained and they all consume resources. The most important issue is that these all need to be maintained. They all need patches (nobody wants to have an exploit on that copy of MSSQL express that came with BackupExec for instance) and they all need to be configured, backed up, and monitored.
Another important consideration is that we’re trying to move to a more controlled environment based around scripting and automation. The more databases we have out there then the more complicated that becomes; it’s not enough just to install and configure the app now we have to deal with the backend DB as well. For resources it’s true that that copy of MySQL backing a wiki doesn’t consume much but when you have 10 copies of MySQL sitting around it does add up.
So what we’re looking at is consolidating it all down to two DB platforms; Oracle and MSSQL. Then we’ll further consolidate that down to a cluster for each. MySQL and PostgreSQL have their good points and being open source is not the least of these but not all of our apps support them. Between Oracle and MSSQL we can support all of our apps and both have mature robust clustering support. This won’t be completely set in stone and some apps may justify a dedicated database for various reasons but the majority of our apps will be backed by one of these two clusters.
Aside from killing off all these single purpose instances we’ll also be able to justify clustering and DR replication. Using RT as an example few people have a clustered RT backend, it might be nice but hard to justify. On the other hand I can justify deploying a cluster if it’s going to support a whole pile of apps across the organization. It’s like VMware HA; it brings higher availability to apps that otherwise would never have justified it.
Some readers may ask: Are you insane! What about performance and licensing costs?
First I need to say that this applies to my scope which is a SMB size non-profit. So what that means is that many of these DB instances are lightly loaded and further more we get highly discounted licenses. So while this won’t be cheap it won’t be hundreds of thousands of dollars either. From a performance standpoint even a modest two socket quad core box with say 32GB of RAM should be able to handle every DB we have with ease. We will have more then one box but it will be for HA not performance. So while this would not be feasible for every organization it is for us.
So that’s what I’m planning for dealing with DB sprawl. Consolidate it all down to a handful of high quality boxes and keep a close eye on those boxes. It will make our applications servers easier to deploy and maintain and it will allow us to offer more sophisticated database functionality for all the applications not just a favored critical few.
Anyone else dealing with DB sprawl? What, if anything, are you doing about it? Got any feedback for me? Think this awesome, think this is the road to hell? This is still in early planning so I’m more then happy to hear anything you’ve got.
Views: 43
-
Comment by Jeff Hengesbach on July 23, 2009 at 2:28pm -
You are definitely not alone and I'd guess it doesn't matter the size of the organization. The thing about sprawl of really anything is a result of not looking at the long term big picture. In big companies you have isolated groups doing their own things all over the place, and in small organizations you may have lack of experience or a high level of informality (just do it) - they all end up in the same situation at different scales.
Your approach appears sound and the great thing about database based application architectures is you can split out the loads, or consolidate, generally with minor pains. Consolidating will make for fewer direct activities maintaining the databases, but you'll have to be more careful those few database updates play well across all applications that rely on the one database server. Centralization can be a good thing but it really raises the bar for change control and security.
-
Comment by Isaac on July 23, 2009 at 8:44pm -
I agree with you about lack of coordination and strategic thinking leading to sprawl. You’re also right about this being seen in organizations of all sizes. I'm glad you think my DB consolidation concept seems good.
I thought about your point that consolidation requires a higher standard from IT today . A lot of the infrastructure changes we’ve made over the past couple of years have inherently set a higher bar for IT and I really think the dept is better off for it.
As an example a few years ago we started moving the bulk of our users to terminal services. In the past, when everyone had desktops, it was no big deal to just drop in any old app and sometimes those apps wouldn’t work well with standard user permissions. We all know giving local admin rights is the root of all evil but it was easy and often done as one offs to handle edge cases. Except eventually there where a lot of edge cases and a lot of people with local admin and of course we had all kinds of malware issues as a result.
After we moved to TS we absolutely could not allow local admin for the users. We had to be significantly more carful because a mistake wouldn’t affect just some staff it would affect all of them. So we started to have to take the time to properly deploy and test the apps. As a result of needing to be more careful our desktop stability improved dramatically. It wasn’t because of terminal services per se but because IT is being held to higher standard instead of just doing whatever when it came to desktops.
-
Comment by Tsahy Shapsa on July 24, 2009 at 7:58pm -
Is there no product out there that will:
1. Discover all your DB instances? providing an inventory
2. Give you patch alerts for each one
3. provide centralized configuration point for all? (do you need that, now that you know where your DBs are?)
Also, shouldn't backup be done in the context of the application? I mean, some applications will require to sync the DB backup with the application activity to achieve consistency, no?
-
Comment by Isaac on July 24, 2009 at 8:39pm
-
I’m not aware of a product that will do all of that. I’m saying it doesn’t exist, just I’m not aware of it. An app like that would achieve some of our goals but not all of them. If we had a product that does what you describe then the day to day operations would be simplified.
But that still would not address our goals of being able to hands off automated installations and rebuilds of our app servers. If we separate the DBs from the apps then the app boxes should be able to be easily reloaded without concern for the backend. Kind of like how if I have a file server with the data on a SAN lun the when the box is rebuilt it simply needs to be reattached to the lun; you don’t have to deal with setting up raid groups and file systems as it has already been handled.
As a clarification I’m not interested in automation because we have so many servers (because we don’t have many servers) it’s about being able to do things in a repeatable fashion that minimizes the chance of human error.
The other thing is that I’d like to be able to cluster these DBs to improve availability. HA DB clusters are not cheap and if you’re going to spend the money it makes sense to have it benefit as many apps as possible.
Your point about backups needing to be done in sync with the frontend app is valid. Some of our apps simply direct you to use the databases native backup tools but others have their own tools that are needed. Like I said we are still looking into the feasibility of this idea and it very well may end up not being feasible.
Comment
© 2012 Created by Elizabeth Ayer and Michael Francis.
Powered by
You need to be a member of The SysAdmin Network to add comments!
Join The SysAdmin Network