February 10, 2010 at 9:47 am
Good afternoon,
Over the years we've found ourselves in "server sprawl" and often times adding databases to a particular server out of convenience rather than any sort of grand strategy. We have a couple dozen servers, couple hundred databases, spanning all sorts of business units.
I am now looking at organizing, consolidating, etc. and trying to determine a strategy for deciding what databases belong on what servers.
My thoughts range from:
1. Functional / Performance – keep similar business units together to limit cross server queries since they are less efficient.
2. Security - some databases require much more heightened security than others with regards to firewall, logins, etc.
3. Developer abilities - the reality of our situation is we have very new developers as well as very seasoned developers. Occasionally the new developers do something to take down the server – I would like to limit the impact they have.
4. HA / DR requirements – group mission critical databases together since those servers will be clustered and the databases log shipped.
I'm curious as to what high level strategy / thoughts you employ when laying out your environment?
Thanks!
February 11, 2010 at 5:07 am
I don't think there's ever an answer that fits all. I'd say that keeping developers away from any other server is good. I'd put all the devs on the same server - the new devs will learn quicker if they annoy the old devs - or hopefully the old devs will nurture the new devs!
Quite often applications make demands which mean they have to be isolated - the usual demand is that you won't get support if you share the environment.
Instances is a good way to consolidate to a physical box, virtualisation only cuts down physical boxes it doesn't reduce servers to manage at the sql/os level.
Consolidation is also tricky with resource. I have previously identified what I call small not used by many people databases and put them on one server. You get problems when the logins are mapped as sa or sysadmin.
You really have to identify usage and consolidate that way, and as you mentioned HR , often these need to be isolated for surity reasons.
Probably this doesn't help too much.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 11, 2010 at 5:38 am
Thanks for the response Colin.
Recognizing there is no correct answer, all thoughts and ideas are helpful!
February 11, 2010 at 11:15 am
Have you ever used the SQL Server SQLdiag Utility?
This is a very usefull utility used in analyzing in the databases consolidation process:
http://msdn.microsoft.com/en-us/library/ms162833.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 11, 2010 at 11:55 am
Welsh, thanks for the response.
My question was really looking for general theory on why you would choose to group databases together. That said, I do a fair amount of troubleshooting and was not aware of this tool. I will certainly be taking a look at it.
Thanks again.
February 11, 2010 at 12:04 pm
In regards to developers we don't let ours touch production and we don't mix PROD and TEST/DEV databases on the same SQL Server. We tend to look more at load than function when determining hosting decisions on our 60+ servers and 2000+ databases. If we determine there is not a significant difference in impact between server X and server Y in regards to CPU, memory, or IOPS then we look at other factors such as similar customer base, SLA requirements, etc.
Consolidation is an art as much as it is a science.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 11, 2010 at 1:05 pm
Thanks Tim.
We don't have quite the distinction between Dev and Prod unfortunately.
Along your thoughts of optimizing for load, I'm leaning toward limiting cross server queries. We do a lot of that and that just adds a layer of complexity and inefficiency I'd rather do without.
February 11, 2010 at 1:05 pm
SQL Server Consolidation Professional Consultants as well as Microsoft SQL Server Server Support Staff use the SQL Diag Utility to assess the feasibility of consolidation of candidate databases as well as a number of other uses. The key factor is knowing how to interpret the results.
There are many other tools and factors to consider but this utility is frequently used as an assessment tool. You must of course need to know how to evaluate the results...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 11, 2010 at 1:19 pm
Perhaps a distinquest professional that is a member of this forum could add or dispute my suggestion with respect the use of the SQL DIag Utility or any other diagnostic tool that may be helpful in resolving the forum members question.
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 11, 2010 at 1:22 pm
I'll give this paper as a reference: http://msdn.microsoft.com/en-us/library/ee692366.aspx
Like everything else, this has some level of tradeoff on how you do it. I like VMs since they're easier to move to new hardware than instances, but as Colin mentioned, they don't reduce servers.
I would not mix Prod/Dev. If you have them mixed, you're asking for trouble in terms of interference with each other and balancing of resources. The first think I would do is separate them out. I can't imagine why you would need these together as a policy. What's done is done, but I'd separate them.
Two reasons: security and load. Security should be obvious, but for load: developers "test" things, and they'll cause wildly unpredictable loads for that reason. Until you really know how much of a load you need, you can't plan production consolidation.
We have consolidated in the past, pre-VM craze, but examing those single-database servers. Compare hardware and load, see which make good candidates. We had a spare server for new apps, and would move something there in production to establish a load (since any guesses are just that, guesses). Once we knew the load, we could size a particular box or migrate it to another SQL Server as a new database. There were cases were security dictated separating some things out from others.
February 11, 2010 at 1:25 pm
I would suggest that you not group by department or work type. But by size and the work load on the database. You do not want to over whelm one server while others are sitting there doing nothing.
Spread the workload over a few servers. And then, create each environment the exact same way. Do not use one server for everything.
I have had only two very busy databases on one server, while the data was on a SAN, the workload was on that one box. But the processors never went above 20 percent. While I had another server with over 50 databases. But that box was not being worked as much, each database was always active. Some of the smaller databases were for things like the badge readers, conf room scheduling, etc....
Keep all environments the exact same, so when you migrate, you can just about use any SSIS pacakge from any server to migrate the databases around. Or when you need to move a database from one environment to another, your queries are all the same. The drive letters are the same, the database names are the same, etc..... You do not have to worry about the SQL jobs, they are all looking at the same database name.
That is another huge topic, naming a database. Select a good naming convention. DO NOT use Version names in a database name. That name will never change. For example, very poor name Memberships_v1_1_2 Very poor name. There will never be a Memberships_v1_1_3. Also make the name descriptive. I once has a database name of X. A single letter X, how stupid is that? All that I could do was take the database offline and wait until someone screams.
Andrew SQLDBA
February 11, 2010 at 1:36 pm
Thank you Steve for articulating this challenge for it was very well stated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 11, 2010 at 1:43 pm
An additional thing I always look at is maintenance windows.
If you need down time to install OS patches, run diagnostics, etc., you don't want a mix of applcations on a database server that leaves you with no common maintenance window.
February 11, 2010 at 1:46 pm
You are welcome, but Michael brings up something I'd forgotten to mention. Maintenance is an issue. If you have an app (db) that is being patched/changed more often than others, you might not be able to co-locate it with databases that cannot have the downtime.
February 11, 2010 at 2:23 pm
Well thank you all for a very positive and constructive dialogue. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply