October 10, 2018 at 9:49 am
I have a database that's running smoothly in a "local" deployment (SQL on the same server as my application). Upcoming policy changes where I work require that DB be moved to a SQL cluster and the DBA of that system is asking me to provide her with my requirements. Would anyone be willing/able to provide me with a list of the metrics I need to gather in order to ensure the cluster is resourced appropriately? I could currently give her the typical CPU and RAM utilization on my server as well at total DB size but I suspect that's not really what she's after. Transactions/sec? Number of connections? Obvs. not a DBA here and could really use some advice in order to be a " good citizen".
TIA!
October 10, 2018 at 12:19 pm
I would start with the size of the database, and the expected growth rate (size now, size in one year, or monthly growth)
How critical the application is may also be a factor. What sort of uptime are the users expecting? Is it only in use during the daytime? Is there a downtime window agreed? Also, how do the users access it? Is there a front end, or do the users run ad hoc queries in SSMS at random intervals? This may affect how your DBA places the database on the cluster, if there are several instances as options.
CPU utilization would be a nice thing to give, but exact numbers will be difficult (especially if the current server has more than one user database on it). If you can rate it on some scale (heavy/medium/light CPU usage), that may be enough. You may be asked to back up some of this information with Perfmon stats. Collect Process:SQL Server %Processor Time, and Process: IO Data Operations/Sec. I expect this will not be necessary, but you should be ready to collect some stats specified by your DBA.
Mostly, the DBA is looking for any warning signs as to how likely this database/application is to overwhelm the CPU, Memory, or disk of the cluster. Very likely there are plenty of other databases on the cluster, so her job is to make sure they all "play nice" together. It may help if you thought about what sort of information you would want about a database that someone else would want to plant on your server, but it sounds like you already have given it some thoughts.
October 10, 2018 at 12:24 pm
Thanks very much for the thoughts Crow, they're appreciated and I'll start there.
October 10, 2018 at 12:34 pm
My take on this if I was the DBA.
From your side I would only be interested in knowing the following
--Expected growth of the databases
--Batch job activity (if not kicked off from SQL Server Agent)
For everything else I (as a DBA) would require a lot more info that can be taken from the Instance - but this would be executed/setup by the DBA
-- Logins
-- SQL jobs
-- Databases (and all their configurations/sizes)
And for the performance side of it
Metrics collected through a period of 1-2 weeks through a monitor tool (SQL Monitor for example) or through perfmom and extended traces.
-This would give a good idea of the level of usage of the SQL Instance, Performance Metrics (CPU/Cache and so on)
October 10, 2018 at 12:50 pm
Awesome Frederico, thanks so much! Really appreciate the input.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply