July 21, 2020 at 1:23 pm
Hi,
I'm looking for a recommended Hardware Spec to run SQL Server 2019 Enterprise on.
Our software vendor has suggested the below, but was looking for other people's thoughts. Our Database is currently sitting on a SQL2000 database - it's around 300GB in size and is accessed by around 90 people at a time. The current server is very out of date and freezes up regularly with the current activity levels.
Recommended by our software supplier:
Minimum 128Gb O/S Drive (Raid1)
128Gb Software Drive (Raid1)
4TB Data Drive Size (Raid5)
64Gb Memory
16 Core Processor
Any thoughts or advice would be appreciated.
Thanks
July 21, 2020 at 4:11 pm
You are running EE, so no memory limits. 64GB might be small these days. I think I'd go to 128GB at a minimum or 256 if I think this workload will grow.
What's the current SQL 2000 Core count?
July 21, 2020 at 4:12 pm
Thanks. The SQL2000 is running on 4 cores at the moment.
July 21, 2020 at 4:27 pm
Maybe I'm misunderstanding them, but these specifications sound a bit old fashioned to me. Does your organization have a SAN? If so that would be the best place to put your data for best flexibility and growth options. Something to consider is where are you going to put tempdb, which is a heavily used resource. Also, how much of your storage will be SSD vs traditional magnetic drives?
You mention you are using Enterprise Edition, but you are only going to give the server 64 GB of RAM. Are you using features only available in Enterprise? If not, you may be better off just with Standard Edition, which starting with 2016 has many of the features that used to be Enterprise only. You could use that money to give yourself 128 GM of RAM and improve some of your other hardware specs:
It's difficult to suggest processor without knowing your usage types and knowing if the data processing is CPU bound or not. In general, I'd follow advice by Glenn Berry who typically states to look at the per core performance as opposed to how many cores the CPU has. The more cores you have, the more you are paying for SQL Server.
If you have more details on the type of workload on this database, OLTP vs OLAP, Intensive CPU computations or mostly grab and push data around to other servers, large batches seldom through the day or thousands of smaller transactions, etc. then I'm sure people could help you better.
July 21, 2020 at 8:32 pm
Just putting my 2 cents in here but putting 300 GB of data on a 4 TB disk sounds like overkill. If possible, I'd put it on a SAN and have room to grow. If SAN is not possible, then I'd probably cut back on the disk and put that money towards memory.
But that recommendation comes with an assumption - my assumption is your data has a slow grow rate from the days of SQL 2000 to today (assuming it was installed back in 2000) being only 300 GB, that is 300 GB every 20 years OR 15 GB per year (roughly). It will take you roughly 253 years (rounded down) to fill the disk at your rate of growth. That is presuming my assumptions are accurate or at least close.
And, if I am adding things up right, with all of that RAID-ing going on, you are looking at 7 disks minimum - 3 for RAID5 and 2 for each of the RAID1's. Do you NEED your application software to be separate from the OS? If not, that will save you some money as you can cut out 2 disks. Performance hit as your disk I/O is now shared between the OS and your applications, but in general this performance hit should not be that bad.
Those are my thoughts on it (that were not covered in the above posts).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 27, 2020 at 12:51 pm
Your server spec looks big for your data volumes.
You will be moving to latest hardware (or thereabouts) so for 300GB data 4 cores with 64gb memory should be ample. However, if you have other databases then a more powerful server may be useful so you can consolidate all your DBs on to it.
Regarding disks, if you have a SAN then use that. If not then 128GB system drive, 1TB data, 200GB log, 200GB SSD for tempdb. Suggested RAID levels seem ok. No need for a dedicated software drive. With current costs you could consider SSD for all your drives.
Have you validated you need Enterprise Edition, my first choice would be Standard for this size system, unless you have a dependency on EE or are consolidating other DBs.
What is your BCO plan. One advantage of EE with Service Assurance is you can have a second server in a second location for no extra license if you do not run any workload on it. If this looks interesting for you, you can use a Distributed Availability Group to replicate all your data to the second server. You cannot use a dAG with Standard Edition.
Another choice if you have just a single database is Azure SQL DB. Again a 4 core instance would be ample, but you should look at all the other implications of moving to Azure before going for this.
Before you migrate, I recommend you set up DNS Aliases for your applications that point to your old SQL box. This means when you migrate you just have to change where the alias is pointing, and your application connections strings need no further change. Also before you migrate you should validate you are not using any features that do not work in SQL2019. The main type is '>=' syntax for left outer join. There are many other things deprecated but most of them still work.
After you migrate, change your DB compatibility level to 15 and set recovery options. There is a lot on the web about best options for SQL2019 DBs. Also if you have EE then look at compressing tables with over 5000 rows using page-level compression, this can help reduce IO.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 27, 2020 at 1:25 pm
With the trainwreck of junk that can't be deleted that Windows Updates leaves behind, I'd allocate 300GB for the OS drive and make bloody sure that SQL Server was installed on a different drive.
Don't forget to allocate a separate drive for the swap file and make sure it's big enough and then allocate it all to the swap file.
Make sure you DON'T put TempDB on a Raid 5. Not sure I'd use Raid 5 on the data drives. Maybe Raid 10?
You also need a backup and restore plan. Where are the backups going to live (MUST NOT be on the same hardware).
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2020 at 9:04 pm
Thank you all for the feedback - we'll definitely increase the Memory spec. The backups are dealt with on a separate server.
We are likely to be adding two other databases to this server in the future, which is one of the reasons for the increased spec.
On the SQL Server software - does anyone have any recommendations on where to buy Enterprise Edition from? Our usual supplier provided a quote and when we accepted they came back to advise they couldn't source it at that price any more and wanted to increase the price by over £50k.
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply