October 1, 2014 at 12:19 pm
I am looking for suggestions on how to better our current setup. Currently we are having memory, CPU, and load issues in various servers.
The current setup is:
We have 8 individual servers on a colocation.
DB1
DB2
DB3
DB4
DB5
DB6
DB7
SSRS
My vision is to have 1 SQL server to host all the databases from all 8 individual server but the design, approach, and requirements are a little fuzzy.
Please advise.
Steve
October 2, 2014 at 6:24 am
This is too big a topic to fully answer in a forum post (IMO), but what you are talking about is consolidation and, since you've posted in the High Availability forum, presumably you are looking for HA options too.
For starters, you need to ascertain that all your databases can be hosted on a single SQL Server instance from a legal/regulatory/vendor support standpoint.
Assuming that they can then you need to spec. the server(s) to have enough CPU, Memory and Disk Storage to run all your databases and meet expected performance targets, SLAs, OLAs etc.
For HA you are looking at Failover Clustered Instances and/or Database Mirroring (since you are running SQL Server 2008 (R2?)).
You'll need to document the approach you are going to take and, preferably, go through at least one dry run to discover and iron out any issues before doing this in Production. In order to produce the documentation you'll need to make the requirements a lot less fuzzy, e.g. speak to your Business Users to find out things such as backup requirements, Recovery Time Objectives (RTOs) and Recovery Point Objectives (RPOs).
Have a look at this link for much more detailed information.
Oh and welcome to the forums! 🙂
Regards
Lempster
October 2, 2014 at 6:41 am
If you're already having capacity problems, then consolidation is probably not a good idea. Consolidation is great for when you have multiple databases on different servers and those servers are not being used to capacity.
If you're maxed out on load now, any server you get to host all 8 of those must have hardware that exceeds the sum of what is in those individual servers. That's memory, CPU and IO throughput
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2014 at 5:49 pm
So what is the approach? Keep working with the 8 servers and just add more memory (RAM and HDD) when needed?
We have over 100 databases across these servers. I am looking for best architectural setup for this kind of scenario.
October 9, 2014 at 2:37 am
No idea.
This is not the kind of problem that can be solved with a couple of forum posts. I suggest you consider getting a good SQL consultant in (I can give you names if you like) to evaluate your environment, requirements, growth expectations, security considerations, etc, etc and recommend you a road map targeted for your organisation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2014 at 5:06 pm
scantoria (10/8/2014)
So what is the approach? Keep working with the 8 servers and just add more memory (RAM and HDD) when needed?We have over 100 databases across these servers. I am looking for best architectural setup for this kind of scenario.
You may not be able to put everything on ONE server, but you shold be able to reduce the number of server you manage.
You can identify the databases that do not generate a heavy load and do not depend on other databases (e.g stored procs referencing tables in other databases) and consolidate those on a single server.
Then come up with a strategy for the heavy hitters. It will be easier to analyze once the noise is reduced.
Do you have applications that have peak loads at different times of day? Then those could be combined on a server...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply