asked
next one year plan of server
hi experts, i am new to Server depth, Our management gave task such as we are using sql server 2005 and windows server 2003, we have 700 GB of disk space and 200 GB free space. they thought server is too old, so time for replace it. They ask the database size need of next one year? whether replace of sql version 2005 to 2008 or 2012? what is current server bottleneck? is it cpu or memory defiency or I/0? So how can i get the report of next one year of Server disk space? how can i suggest for sql version upgrade? if i upgraded sql version what is the pros? how can i identify bottleneck in current server? plz....sql...experts........any one help me........ how can i estimate of Disk space future one year?
So in one hour you expect people who do this for free to answer all those questions, without giving more information than SQL version and diskspace? **Your questions and some answers:** **whether replace of sql version 2005 to 2008 or 2012?** Ask the vendors of your 3rd party databases/systems and the developers of your in house built systems. Create a list for each database and you will see which version you are able to move to. Don't forget that you can install instances with different versions on the same server. **what is current server bottleneck? is it cpu or memory defiency or I/0?** Well, you need to test your server to know. Start with disks for example and run SQLIO, you can determine your maximum throughput/IOPs. But you are instructed to move to a new server, so maybe its overkill to find out where todays bottleneck is? Especially since you do not have the knowledge today. **So how can i get the report of next one year of Server disk space?** Answered by eghetto and Thomas **how can i suggest for sql version upgrade?** Basically same answer as above. **if i upgraded sql version what is the pros?** Depends on the version you do choose, check
Microsoft.com/SQL. If you choose 2008 R2 and up you get backup compression for free if you are on standard. **how can i identify bottleneck in current server?** Basically same answer as above and hire a consultant to help you. **how can i estimate of Disk space future one year?** Answered by eghetto and Thomas. My best tips is to hire a consultant to help you get started.
I would start with looking at Glen Berry's diagnostic information queries:
http://dl.dropbox.com/u/13748067/SQL%20Server%202008%20Diagnostic%20Information%20Queries%20(August%202012).sql I am sure that you will find some useful info there.
HI rade, Can you suggest me any DMV Query to find BOTTLE NECK IN CURRENT SERVER? ex: SELECT * FROM sys.dm_os_performance_counters WHERE object_name='SQLServer:Buffer Manager' This query we can get page reads per sec and writes per sec. but how can i summarize to pros or cons? is there any limit in page reads/writes per sec doesn't cross to that limit?
Disk space requirements for database files: -- Have you figures from the data sizes over the last year from which you can extrapolate the information? Have you figures from the business indicating what their estimated growth rates are for the data in those databases? If not, then you could extrapolate it from database backup sizes - assuming that you are backing up data and not trashing the backup history... See my blog post here on the subject:
http://thelonedba.wordpress.com/2011/03/08/t-sql-tuesday-16-aggregates-and-average-weekly-backup-sizes/ - you can extract the historical information from the query at the end of the post: ;WITH WeeklyBackupTotals AS (SELECT DISTINCT Datepart(week, backup_start_date) AS WeekNumber, database_name, Sum(backup_size) OVER (PARTITION BY Datepart(week, backup_start_date), database_name) AS TotalBackupSize FROM backupset ) SELECT DISTINCT database_name, CONVERT(BIGINT, Avg(TotalBackupSize) OVER (Partition BY database_name)/(1024*1024)) AS AvgBackupSizeMB FROM WeeklyBackupTotals ORDER BY Database_name
The backup size gives an indication of the database size, particularly if the backups are uncompressed which, if you're using SQL Server 2005 with no native tools, they are.
hi thomas rushton, thaking you for your answer. But i want details information to do that. Have you figures from the data sizes over the last year from which you can extrapolate the information? Ans: Last 4 years Have you figures from the business indicating what their estimated growth rates are for the data in those databases? Ans: i dont know. they are not revealed. kindly how can i get database growth rates?
As far as I know there is NO built-in information (views) about database sizes over the last year. We wrote a dedicated job to monitor database size and collect these specific information, which you can use to extrapolate for the future.
11 People are following this question.
