SQL Server Performance Forum – Threads Archive
Consolidating servers
Hello, I would appreciate, if somebody please take a moment to answer this: I was wondering, how could i condolidate SQL Servers? Here are the details: Current scenario: Would like to condolidate our SQL Server environment. Why? It’s getting very tidious to manage multiple SQL Server 2005 running and importantly avoid paying license fees to Microsoft. Action item: Here is the strategy i have been thinking. I don#%92t know, if this is the correct way to approach: But if it isn#%92t, then please advice: Since we have Server A and Server B both running with couple of databases. I would like to capture performance data on Server A and Server B. Compare these two collected data and see, if server B databases can fit on Server A. Now the question is, what data should I need to collect and how to correctly interperet and take a right approach? In addition, how could I determine what#%92s the correct server#%92s load in percentage or resouces at any given time? Any way to gather this performance data and put it in some sort or report? Has anybody done this before? If yes, could you please share it? Many thanks,Cali
It is not easy or clear task…It depends on the CPU, memory and Disk configuration. If you collect the info on one server you can#%92t correlate the same with other server because your server specs may not the same as the source server. You can try the following to collect the stats… Collect the transactions/sec – Perfmon counter
Disk Avg. Reads – Perfmon counter
Disk Avg. Writes. – Perfmon counter
CPU utilization. – Perfmon counter
Number of users – Perfmon counter
File stats. Using the following articles…http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15786&DisplayTab=Article
http://www.sqldev.net/misc/fn_filestats.htm http://www.novicksoftware.com/Articles/sql-server-io-statistics.htm MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
sql server 2005 file stats script… select DB_NAME(database_id), [file_id],io_stall_read_ms,num_of_reads,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as ‘avg_read_stall_ms’,io_stall_write_ms,num_of_writes,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as ‘avg_write_stall_ms’,io_stall_read_ms + io_stall_write_ms as io_stalls,num_of_reads + num_of_writes as total_io,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as ‘avg_io_stall_ms’from sys.dm_io_virtual_file_stats(null,null)order by avg_io_stall_ms desc MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
As Mohammed said it is not an easy task to consolidate, you have to identify each and every information on the servers for software, application, services, SLAs and users connectivity etc. before even planning such actions.
Review thishttp://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1180355,00.html (ebook) which will bring you the actions and reactions involved in such exercises, I have referred and planned our tasks successfully. http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1154953,00.html one too on pros & cons. If you are running various applications that are needed a consolidation task then where many services are running under distinct user accounts, be aware that this may exhaust the desktop heap resources and you may need to do some tuning in order to ensure that services are able to start successfully. Each Windows station can contain zero or more desktops and each desktop object has a desktop heap associated with it. A finite amount of desktop heap is available to Windows, and when the available desktop heap has been exhausted, failures may occur when starting the services. The performance of SQL Server in consolidated environments depends greatly on the hardware you are using and on the characteristics of the SQL Server workloads that are involved. Also refer tohttp://www.microsoft.com/technet/prodtechnol/sql/2000/plan/SQL2KCon.mspx technet paper on the consolidation. Further the server & data migrations are time-consuming and without a planning it is very hard to implement, or even test them before. So it is best to test a server consolidations and virtualization using a test platform with a small application, the access of application should be based on logical view and not on a physical location. All the changes must be made without impacting the live systems. There are many third party tools out-there provides such flexibility of virtualization such as Polyserve and with minimum capital expenditure and they must be tested and refer to the reviews on web before even going towards the evaluation edition.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Some more to get some idea… http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx
http://h71028.www7.hp.com/ERC/downloads/4AA0-0191ENW.pdf
http://www.sqlservercentral.com/columnists/sjones/serverconsolidation.asp
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Thank you MohammedU and Satya for the info. I will glance through the provided links and see, how it goes. Thanks again. you guy’s are the best.
Cali
]]>