I went through this website because we have migrations coming up pretty soon for Landesk Management Suite.
We are planning to move to SQL 2014 but the site’s contents are mostly relevant to SQL 2005.
Are there any other good websites or documentation from microsoft which I can go through to estimate I/O, disk and CPU planning before the Migration?
The url which I went through was this
Editing. I am trying this tsql code to get min,max and avg size of databases since 2015. Would this help?
SET NOCOUNT ON DECLARE @endDate datetime, @months smallint; SET @endDate = GetDate(); -- Data atual SET @months = 12; -- Nr. de meses a analisar ;WITH HIST AS (SELECT BS.database_name AS DatabaseName ,YEAR(BS.backup_start_date) * 100 + MONTH(BS.backup_start_date) AS YearMonth ,CONVERT(numeric(10, 1), MIN(BS.backup_size / 1048576.0)) AS MinSizeMB ,CONVERT(numeric(10, 1), MAX(BS.backup_size / 1048576.0)) AS MaxSizeMB ,CONVERT(numeric(10, 1), AVG(BS.backup_size / 1048576.0)) AS AvgSizeMB FROM msdb.dbo.backupset as BS WHERE NOT BS.database_name IN ('master', 'msdb', 'model', 'tempdb') AND BS.type = 'D' AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate GROUP BY BS.database_name ,YEAR(BS.backup_start_date) ,MONTH(BS.backup_start_date)) SELECT @@SERVERNAME ,MAIN.DatabaseName ,MAIN.YearMonth ,MAIN.MinSizeMB ,MAIN.MaxSizeMB ,MAIN.AvgSizeMB ,MAIN.AvgSizeMB - (SELECT TOP 1 SUB.AvgSizeMB FROM HIST AS SUB WHERE SUB.DatabaseName = MAIN.DatabaseName AND SUB.YearMonth < MAIN.YearMonth ORDER BY SUB.YearMonth DESC) AS GrowthMB FROM HIST AS MAIN ORDER BY MAIN.DatabaseName ,MAIN.YearMonth
What I would think to do is see how your current server is performing by benchmarking the older hardware. This will allow you to get a good baseline so you do not go backwards from the migration.
To really hammer the disks have a few workstations use CrystalDiskMark and all point to the server, this will allow you to get concurrency testing also to simulate load.
As far as CPU, I default to Perfmon to see how the CPUs are holding up on the older hardware. If they are getting hammered then you either need to get more CPU or/and start tuning.
For the new server, Brent Ozar Unlimited has a first responder kit that has a good setup guide for SQL Server. However, he has a section in the setup guide that is a bit outdated, “Format the drives with 64K allocation blocks”. Honestly, I don’t believe this applies anymore 🙂
You can get this guide and a bunch of other goodies at First aid.
When you have the new server online, I would then start to have your QA team run through testing to make sure that everything works and that you are also getting the same or better performance from the server. Remember in 2014 we have a new cardinality estimator along with other features.