My company has a small SQL environment for LANDesk and they want to migrate all the servers to a single SQL cluster per region. What I want to know is, how do I determine the capacity for such a scenario? I have obtained the database growth per individual server (All Landesk) and can I take this data as a rough estimate for the consolidation in to the single SQL cluster? The Windows team tells me that the data from these servers is kinda wonky and might not give an accurate result what they want.
The new cluster in question already has a couple of databases but it is not at it's fullest capacity yet. Once they start adding the remaining data from the servers, it should reach it's full capacity. I have been running Perfmon on this for a while and for checking database growth I used the below TSQL code from sqlskills.com
SELECT [database_name] AS "Database", DATEPART(month,[backup_start_date]) AS "Month", AVG([backup_size]/1024/1024) AS "Backup Size MB", AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB", AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio" FROM msdb.dbo.backupset WHERE [database_name] = N'AdventureWorks' AND [type] = 'D' GROUP BY [database_name],DATEPART(mm,[backup_start_date]);
Is there anything I can do to get an accurate result? The LANDesk team doesn't want me to use the older servers and the only option I have is to use the new cluster where they plan to migrate all the data and I only have 6 months worth of data on this. Should I ask them questions like number of years this new cluster be used? etc...
Thank you all for your answers. Here is what I have from the PAL tool
I used the PAL tool to determine perfmon data and these are some of the warnings it gave
1)Response time greater than 25ms 2) Less than 10% Idle Time - the disk queue has at least 1 outstanding I/O 90% of the time 3)Greater than or equal to 64 KB IO sizes. Generally, the larger the IO size, the more data can be transferred per second, but the response times are longer.
4)Physical disk read latency - greater than 25ms 5)Process % Privileged Time - SQL server using more than 30% of privilged mode CPU usage 6)SQL Server Access methods FreeSpace Scans/sec - A ratio of more than 1 freespace scan for every 10 batch requests - confirmed with High paglatches, async i/o completion and low page life expectancy 7)SQLServer:Access Methods Workfiles Created/sec - A ratio of more than 1 workfile created for every 20 batch requests 8)SQL Buffer cache hit ratio : Less than 97 percent buffer cache hit ratio 9)Red: Greater than 20 Lazy Writes per second 10)Red: Page life expectancy is less than 5 minutes (300 seconds) 11)Yellow : A ratio of more than 1 SQL Re-Compilation for every 10 SQL Compilations
Regarding I/O we are moving to SQL 2012 cluster so I am not sure which is another possible way to measure IOPS. Would you suggest SQLsim or IOmeter etc? Thanks again!