April 29, 2013 at 11:22 am
So I've been researching this, but can't get a clear how to on this. I just joined a new job and one of my first projects is to migrate a 2005 sql server to 2008. I need to give specs to my infrastructure guy for new environment.
We have an application running on Windows 2003 Box (4 processors, 4GB of ram) with SQL 2005 SP4. We are running on VM Ware with Net App. We need to get to Windows 2008 with SQL Server 2008 R2.
How do I determine Specs for the New Box. For example, how many CPU (and their speed), how much RAM, etc.
Is there a tutorial you guys can point me to?
------------
🙂
April 29, 2013 at 2:45 pm
There is no tutorial for that, because each application is different. And it is very hard to give a "cook recipe" or one quick answer.
You need to get a baseline of your existing environment. You can use perfmon or DMVs for that. Run it for a few days or a week, so you can get a better understanding of what resources your app uses most , etc.
A few important are:
-CPU usage
-RAM usage
-Disk latency (read and writes)
-Network utilization
-Page life expectancy (SQL instance level)
Just to mention a few.
So if you see that your server barely uses CPU over a one week period or more, you know that buying a powerful CPU won't be the best thing. On the other hand, if you see that your apps use a lot of RAM, you know that putting enough RAM would be critical.
Also, please keep in mind that SQL running on VMware is different than running on a regular an physical server, as VMware manages CPU and RAM for the actual guest. As a matter of fact, getting perfmon from a VMware machine can be misleading, as CPU and RAM are just an abstraction of the real stuff. If that's the case, ask your VMware guy for the virtual machine metrics, like CPU, RAM, disk latency, etc, so you can get an idea of how much workload you have right now on that virtual machine.
April 30, 2013 at 3:32 am
It is very easy to get caught up in trying to come up with a perfect spec, but it is rarely worth the time since you are running a virtual machine where
resources can be added fairly easy. I would not spend weeks of looking at a billion metrics because it is a very complex job to convert all that into a spec
that will basically say 2-4 cores with x amount of RAM.
If I understood you correctly your SQL 2005 is virtual today, just get a couple of reports from your vmware admin where you can see IO/Memory/CPU usage
for that VM. Have a look at past week, past month and so on.
Run the 2005 Performance Dashboard report and have a look at the IO, it will display which databases use the most IO.
The databases that show up in this report with a high % are likely those you have be a little cautious with.
Very very often the performance of the server is used by databases where bad indexing causes high IO % figures, not solved by adding hardware.
Do the same for expensive queries regarding cpu.
(Remember that info above is only since last restart)
Have a look which databases use the most of your buffer mem, you can schedule below code during the day just
and it will tell you which one of your db's are "agressive" on the mem.
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;
But basically I would spec out a machine that is better than you have today (not more cores, four are "probably" enough, a lot of cores in a vm can be a pain).
Add as much memory you can get a hold of.
Start migrating your databases one by one and monitor your new server. And if your spec doesn't hold upp, adjust it accordingly as you go.
That will probably be easiest way to get started.
April 30, 2013 at 7:03 am
Thank you both of you. It's really good information.
I have been reading about running Perfmon data, but then yesterday I figured out that it doesn't really apply here because of VM environment.
I'll post here my process after I'm done with specs. 🙂
------------
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply