Capacity planning
-
Good day all,
Not so long ago, I was asked to spec a new box for our LIVE SQL Server environment, plus OS and SQL versions.
I asked for expected growth figures and could not get anything of real interest. The best I have is database growths. Which is big, in my mind, for growth levels. Went from a 2GB db to just under 5GB in 6 months.
I capture the virtualstats info everynight.
Basically my boss has come out of a meeting where they discussed growth, and unfortunately is not able to tell us, confidentiality and all. He has however said, that whatever I want I can get and that the growth that we have seen, is nothing compared to what to expect in the next 6 months to 3 years. They expect an additonal 75% on the staffing side, and an additional 4 products, which is double what we currently have. As well as increased use of the current products.
My company works on a costing base of 3 years for hardware.
What we have is:
SQL Server 2000 SP4 standard edition
HP Proliant ML530 Generation 3 boxes (discontinued now)
3 GBS memory
Dual processors
os = raid 1
logs = raid 1
data = raid 5 (50% read and 50% write cache)
temp = seperate drive
My boss has also told me that he expects to see SQL Server 2005 Enterprise Edition 64-bit, in my planning to upgrade document.
Basically I have a couple of varied questions.
1. What sort of SPEC machine should I be looking at? minimum quad processors, 8GBs mem, RAID10, drive write speeds, etc. Intel or AMD?
2. What would the general upgrade path be?
3. Has anyone out there done this sort of thing?
4. How do I find out what the actual limit is to my current box? (IE. max transactions per second, number of writes, etc)
5. Is it worthwhile looking at 64-bit?
All help is much appreciated!
Thanks in advance,
Graham
-
This was removed by the editor as SPAM
-
I'm not sure what your relationship with your boss is like, but if it won't risk you being sacked my first reaction would be to ask him how on earth you're supposed to spec anything when he won't give you the most critical piece of information! "How much is this going to grow by?"
If he won't tell you that, then what are you supposed to do? All you know is that a growth of 3Gb over 6 months is "nothing compared to what you're going to see". I'm sorry, that's just a stupid attitude for him to take, especially when he actually has the information!!
Anyway, you can only work with what you've got, so all you can do is make some (hopefully) sensible guesses. Having made them, make sure you document them, explain how you arrived at them, and INCLUDE A DISCLAIMER THAT STATES THEY MAY BE INACCURATE DUE TO LACK OF AVAILABLE INFOMATION! That way you're covered when everything breaks in 6 months time.
1) Current db size.
Db space is a funny thing. "Big" is so subjective - you think that 3Gb in 6 months is a lot. I support databases that grow by >1Gb per day, so you see the issue ...
Let's assume roughly exponential space usage (doubling every 6 months), so by the end of the year you'll be at nearly 10Gb, the following year 40Gb, and the year after that 160 Gb. (And that's without taking the 4 new products into account).
Based on this, and the anecdotal hint that growth would be "like nothing you've seen yet", let's settle on 500 Gb for data over the next 3 years.
Of course, you will have some sort of archiving strategy in place, won't you
, so 500 Gb could be overkill. But, we're assuming the worst case here, so let's stick with it.2) Backups
All this data needs to be backed up, and this will usually require AT LEAST an equivalent amount of space (since you'll presumably want at least 2 online copies of each database backup, plus transaction log backups). If you use a 3rd-party product like SQLLitespeed (from Quest) then you should be able to compress all this down into 500 Gb.
3) Memory
With all this data, memory is going to be a major issue. Get as much as you can. Get 64 Gb - why not? If that's considered excessive, get a MINIMUM of 8Gb, and as much in excess of that as you can. To use it properly, invest in 64-bit, since this means you won't have to mess around with /3Gb switches and /AWE , all of which are compromise solutions to large memory addressability. With SQL2005, you don't need to buy Enterprise edition either to be able to use large memory (unlike SQL2000). However, Enterprise SQL2005 does have certain facilities (partitioned tables, database snapshots, full reporting services options etc.) that aren't available in standard edition, so you'll need to choose carefully.
4) O.S. and SQLServer
As discussed above, I'd recommend SQLServer 2005 Enterprise 64-bit. For large memory, you'll need to get Windows 2003 Enterprise Edition (used to be Advanced Server in Windows 2000)
5) CPU - difficult to say. Unless you're applications are going to support hundreds of users all running hugely-number-crunching queries, then I'd get four of the fastest dual-core processors you can. (Are quads available yet?)
6) Disk
You're going to need a lot of disk, by the sound of it. The RAID setup depends on the type of database access that predominates. If it's mainly READ access (i.e. it's principally a data retrieval system with low volume updates, typical of a call-centre, for example), then RAID5 will probably be adequate. However, it's poor for database updates and insertions, so if your data changes frequently go for RAID10.
7) Upgrading SQL
This is nice and easy - all you need to do is install SQL20005 on your new hardware, detach the database data files from your old server, copy them and re-attach them on the new server. This automatically does the necessary to upgrade the databases to SQL2005. You'll need to recreate the logins manually and then map them correctly in the new databases.
DTS packages - these are a bit more of a problem. As long as they're just native DTS, there is a migration tool which will allow existing packages to run as normal under SQL2005 using SSIS, although you won't be able to modify them. If they're a bit more complex, though, (e.g. use VBscript transformations) then you may well encounter problems and find that they won't work.
I hope all this is some help, but you'd be so much better off if you could explain to your boss just WHY you need to know. Maybe the total cost of all the above will finally convince him to tell you so that you can make a more accurate (and possibly cheaper!) recommendation!
-
Thanks for the response...
Strange request I know, and I have already told my boss that he is out of his mind, if he expects me to come back with anything realistic on the information that I have been given.
And thankfully I am still around!
He is pretty easy going... Jokes aside...
Sadly that is what I needed to see, things that I need to think about, that are outside of where I am currently.
Thank you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply