building an sql server and nas combination

[Burningchair]

Hi,

first and formost sry for any bad english not my mother tongue.

I am tring to configure an server for NAV2017 (uses an sql database) and some Network Storage.

Current idea

epic 7282 

2x 64gb eec ddr4

a good rais caard ( but no idea whats a good one) 

and some 2tb ssd's like wb red NAS sata. or are there any not hugly more expensive sas ssd avible?

i think that raid 5 woud be a great option 

 

the main gole is to get nav2017 fast. currently its a bit laggy when opening big data bases. 

 

its supposed to replace a server with folling config:

 

ex. Pro. Virtual Power Dual-CPU SAS:
1. 19" Rackmount, Geh Server Supermicro 2HE SC825TQ-R740LPB REV. MA

    Höhe 89mm x Breite 437mm x Tiefe 648mm; Netzteil:720W, redundant, 80+ Gold zertifiziert
2. 8x 3.5" SAS/SAS HDDs hot swap

3. Board SVR Supermicro X9DR3-LN4F+ LGA2011, iC606, E-EXT-ATX, 2x GBit LAN, ...
4. 2x Intel Xeon Prozessor E5-2620v2 2,10 GHz 15M, LGA2011 7.2 QPI/1600 MHz 6Core/12Threads 22nm 80W
5. 8x 8GB DDR3 DIMM 1600MHz (64GB)/ECC Registered (max. 768GB)
6. Controller RAID PCIe 3.0 x8 SAS 6Gb/s LSI MegaRAID 9271-8i SGL 8-K. (2x x4 intern) 1024MB
7. 4x HDD Seagate 600GB SAS (6GBit, 16MB,15000U)
8. IPMI mit KVM-over-IP onboard (sep. LAN Port)

 

 

 

 

[Master Disaster]

Before you look at replacing the server its a good idea to check you don't have a bottleneck elsewhere.

 

I see the server has dual GBit LAN, is the rest of the network also GBit (routers, switches, all client NICs and CAT 5e/6 cabling between them all)? Check the server and the client both have GBit connections to the router. If something is running at 100BASE it would cause this behaviour.

[WereCatf]

2 minutes ago, Master Disaster said:

Before you look at replacing the server its a good idea to check you don't have a bottleneck elsewhere.

 

I see the server has dual GBit LAN, is the rest of the network also GBit (routers, switches, all client NICs and CAT 5e/6 cabling between them all)? Check the server and the client both have GBit connections to the router. If something is running at 100BASE it would cause this behaviour.

Not only that, but...I'd just check if the gigabit Ethernet itself is the bottleneck. The OP says that it's "laggy" when opening big databases, which sounds like it could just be the gigabit Ethernet being the problem and switching to 10 gig would be the far simpler and cheaper fix than replacing everything.

[Burningchair]

the network shoud not be the bottelnack i change the main connections to 10gbs. it helped a little bit but not enough. 

 

edit:

 

to be mor cler i drwa a beautfufll pic of the network and al the swiches i hope that helps a bit all red conenctions are 10gbs

[Burningchair]

@WereCatf you meen adding a 10gbs network card in the server? it coud help but i am not so sure wether the old hdd might not be the problem  also its running server 2018 an sever 2008 so an uprade to an new server woud be good for securety reasons. i know you can uprad server2012 ro server 2019 but i was tld that tas not anoption becase we need a seamles transession. if there is s sollutuion for that to i woud be happy to hear it. 

[leadeater]

@Burningchair Edit: lol never mind blind and didn't look at bottom of post

[leadeater]

1 hour ago, Burningchair said:

2x Intel Xeon Prozessor E5-2620v2 2,10 GHz 15M, LGA2011 7.2 QPI/1600 MHz 6Core/12Threads 22nm 80W

You'll want something with much better frequency than this, in the same generation what would be E4-2643v2/E5-2667v2/E5-2687Wv2. These are the frequency optimized models which is what best suits MSSQL as the higher clocks give significantly better query latency, can be around half of similar Xeons. Also Microsoft currently still recommends Intel Xeon over EPYC (not sure about EPYC2) as their database engine is optimized very well for Intel architecture.

 

Just note that while the total transaction per second for basically all the Xeons is the same the per transaction latency is much lower on the higher frequency ones.

 

Current generation equivalents would be 6226R and 6246R, there are more but these are the better value ones. If you want to go with AMD, which I think would be fine, then 7F32 and 7F52 would be the better ones to look at.

 

1 hour ago, Burningchair said:

2x 64gb eec ddr4

Definitely don't go with just 2 ram modules, you'll want to fill each ram channel otherwise performance will be lost. 8x 8GB or 8x 16GB would be the most optimal starting point.

 

1 hour ago, Burningchair said:

i think that raid 5 woud be a great option 

Don't use RAID 5 with SSDs and database servers, you'll lose a very large amount of write performance with that. Either go with a 2 SDD RAID 1 mirror or 4/6/8 SSD RAID 10.

 

Samsung PM883 or SM883 would be good choice for reasonable cost.

 

Basically any LSI/LSI based RAID card will do fine doing RAID 1 or RAID 10.

 

As for why the performance is slow that is most likely storage performance combined with missing Indexes for the queries being run. You could probably gain a decent performance increase just through some database optimization.

[WereCatf]

51 minutes ago, Burningchair said:

you meen adding a 10gbs network card in the server?

No, I specifically told you to check whether the network is the bottleneck or not.

[leadeater]

8 minutes ago, WereCatf said:

No, I specifically told you to check whether the network is the bottleneck or not.

Rather unlikely. Network traffic on MSSQL is usually very low unless running a full backup.

 

Configuring Data Collection and analyzing query performance will give a good indication where the most execution time is being spent.

https://www.mssqltips.com/sqlservertip/1746/performance-data-collection-and-warehouse-feature-of-sql-server-2008-part-1/

https://www.mssqltips.com/sqlservertip/1756/performance-data-collection-and-warehouse-feature-of-sql-server-2008-part-2/

https://docs.microsoft.com/en-us/sql/relational-databases/data-collection/system-data-collection-set-reports?view=sql-server-2017#Server

[WereCatf]

Just now, leadeater said:

Rather unlikely.

I know, but I figure that since the OP is planning upgrades, it'd be a good idea to check anyways and also be aware of where the bandwidth-consumption is at the moment and how much room there is, should the demand increase in the future.

[Burningchair]

@leadeater i know thats the old server  i am plning on an epic 7282 but i am open for change. we are currently not sure if 128gb ram is enouth  woud 4x32gb be fine so we coud atleast upgrade to 256gb later? also about the sql database optimation. as far as i know that datatbase ist crated bei microsoft nav2017 it self so i can't optimise anything in that regard. 

 

@WereCatf thats why i dray the paint pcture. i tried that one the new server is installed with 10gbs ever pc has a 1gbs conection to it i have Buffalo Business Switch BS-MP2008 L2 8-Port 10gbs swich and conechtet to this are 3 Netgear GS110MX 10-Port Gigabit/10G Ethernet LAN Switch from wich each ps geht a 1gbs conections. the buffalo swich is pluged directly in to the  gateway and the server. so i cant see any Bottleneck 

[Electronics Wizardy]

54 minutes ago, Burningchair said:

i know thats the old server  i am plning on an epic 7282 but i am open for change. we are currently not sure if 128gb ram is enouth  woud 4x32gb be fine so we coud atleast upgrade to 256gb later? also about the sql database optimation. as far as i know that datatbase ist crated bei microsoft nav2017 it self so i can't optimise anything in that regard.

optimal number of ram sticks depends on the epyc sku, butis normally 8, with 4 being optimal for some low end skus. Id probalby go 8 dimms for the best performance.

 

What does current utlization on the server look like?

 

Do you have the current server specs? 

 

[leadeater]

1 hour ago, Burningchair said:

i know thats the old server

I realize that but I was more pointing at the CPU not being one that is optimal for MSSQL, while I don't think you are CPU limited even with what you have now it's still rather possible so I'd still suggest if you are going to be buying a new server you go with something like I mentioned. Until you collect some performance data and query plans you're not going to know what is the performance limiter for you currently but I'd suspect it's disk performance.

 

1 hour ago, Burningchair said:

also about the sql database optimation. as far as i know that datatbase ist crated bei microsoft nav2017 it self so i can't optimise anything in that regard

Once the database has been created you can connect to the SQL instance and make changes like adding Indexes, this won't alter the database structure or the data itself. What adding Indexes does is create a copy of the indexed data in an optimal layout on disk so it can be queried much more quickly at a lower disk I/O load.

 

If you have a table with 100 columns and a million rows but your most common query is to select data filtering by lastname, firstname then you'll get a massive performance increase by creating a Index on that table for those two columns. Without good indexes it's like trying to find a persons phone number in a phone book but instead of a logical order it's just adding new records consecutively, no turning to the section starting with G so now you have to scan the entire book i.e SLOW. 

 

It is very common for products like Nav2017 to not create table indexes for you or ones best suited for you.

 

Also I don't know what SQL licenses you have but if you are using Per Core license model then you really do want to go with a lower core count much higher frequency CPU, these cost more than regular CPU models but less than SQL licenses when you start going higher in core counts.

 

1 hour ago, Burningchair said:

we are currently not sure if 128gb ram is enouth  woud 4x32gb be fine so we coud atleast upgrade to 256gb later

Probably more than you need. Not sure how many users you have, concurrent sessions, database size etc but our SQL clusters have 256GB/384GB ram with 2x 6254 CPUs hosting few hundred odd databases around the 20TB mark total. Throwing hardware resources at a performance problem can often not have the results you are looking for, I would say in your case however since the current server is kind of on the lower end and still using SAS HDDs you will get a significant improvement.

[Burningchair]

@Electronics Wizardy the current spec is in the first post

 

  @leadeater currently the cpu's arent hugly ulelised so there is no cpu bottelneck. i also run NAV directly on the server to check if the networ is the problem. and there wasn't a big improfment. i chosse the 7282 for the hig singelcoreboost and and good pice at my delaer. 85% of the ram is urrently used so i thoiught dubbeling it woud be a good Idea how bad woud it be to habe 4x32gb instad of 8x16gb? i quitelike the idea of haveing the option to upgrad to 256gb by just adding a few more stick down the line. 

i also deffenetly agrre that the hdds are the bottelneck ther for the ssd whats the bennefitof the samungs ssd compared to the wd red nas. the red nas are raded for 24/7 use and are betenn 50-100 € cheper compared to the samungs. and whats the disatvantage from stepping down vom sas to sata?

[leadeater]

20 minutes ago, Burningchair said:

85% of the ram is urrently used so i thoiught dubbeling it woud be a good Idea how bad woud it be to habe 4x32gb instad of 8x16gb? i quitelike the idea of haveing the option to upgrad to 256gb by just adding a few more stick down the line. 

MSSQL will by default just reserve as much ram as it can, so even if you put 1TB of ram in the server at some point it'll reserve nearly all of it and you'll see in task manager all the ram as allocated and used. Problem is that doesn't actually mean it needs it or how much of that ram is active, that's where SQL performance monitoring tools come in.

 

Generally it's considered good practice to not allow MSSQL to use maximum ram possible and set a limit, then monitor performance and increase if you see SQL performance counters indicate you actually need more.

 

4x32GB should be fine though, that's a good starting point and Quad Channel memory bandwidth is likely more than you need but I'd still not go less than 4 ram modules.

 

One thing you could do is purchase the SSDs now and put them in the current server and migrate the database files over to the SSDs and see how the performance increases, downside is it'll most likely give a huge performance increase and then the desire/need to spend money on a new server goes away and I definitely prefer to keep servers under warranty.

[Electronics Wizardy]

8 hours ago, Burningchair said:

@Electronics Wizardy the current spec is in the first post

 

  @leadeater currently the cpu's arent hugly ulelised so there is no cpu bottelneck. i also run NAV directly on the server to check if the networ is the problem. and there wasn't a big improfment. i chosse the 7282 for the hig singelcoreboost and and good pice at my delaer. 85% of the ram is urrently used so i thoiught dubbeling it woud be a good Idea how bad woud it be to habe 4x32gb instad of 8x16gb? i quitelike the idea of haveing the option to upgrad to 256gb by just adding a few more stick down the line. 

i also deffenetly agrre that the hdds are the bottelneck ther for the ssd whats the bennefitof the samungs ssd compared to the wd red nas. the red nas are raded for 24/7 use and are betenn 50-100 € cheper compared to the samungs. and whats the disatvantage from stepping down vom sas to sata?

WHat is disk usage like? What is the averge queue  depth?

 

 

Id probably just try. ssd swp, modern ssds re much faster than those hds you have now.

 

what model of server do you plan on getting? Does it support nvme in the front bays? How many tb do you need?   

[Burningchair]

@Electronics Wizardy sry for the late reply. somhow i dont get the diks usage in taskmanger. don't know why. but i am quite sure that the hdds are the problem. i coud try to use an sd to chash them but i am not sure if any wore in that server wood be an good idea. 

 

Currently the supermicro 13ac2-605wb is plannd but i am open for change 

 

are ther any NVME SSD that are not 2x the prece of an sata server ssd?

[Electronics Wizardy]

9 minutes ago, Burningchair said:

@Electronics Wizardy sry for the late reply. somhow i dont get the diks usage in taskmanger. don't know why. but i am quite sure that the hdds are the problem. i coud try to use an sd to chash them but i am not sure if any wore in that server wood be an good idea. 

 

Currently the supermicro 13ac2-605wb is plannd but i am open for change 

windows server doesn't show disk usage in task manage by default, use resource monitor to look at disk usage

 

can you put a link? That model number doesn't bring up anything on google.

 

Look at the dell r7525 and r6525 aswell

[Burningchair]

sure but i cant find i in englisch

https://server-konfigurieren.de/product/single-amd-epyc-server/1HE-Supermicro-113AC2-605WB-AMD-EPYC-CPU-Server

the mainbord is the  H12SSW-NT i only need 4 bays for 4x2tb ssd raid 10 

i attaced a pic oth the resource monitor  but i have to say the server is cuurently more or les idel with only 2 users later the day it will be use by around 20 

 

[Electronics Wizardy]

9 hours ago, Burningchair said:

sure but i cant find i in englisch

https://server-konfigurieren.de/product/single-amd-epyc-server/1HE-Supermicro-113AC2-605WB-AMD-EPYC-CPU-Server

the mainbord is the  H12SSW-NT i only need 4 bays for 4x2tb ssd raid 10 

i attaced a pic oth the resource monitor  but i have to say the server is cuurently more or les idel with only 2 users later the day it will be use by around 20 

 

DOes your server have multiple disks, I only see the C drive now.

 

Can you show it under load when you want it to be faster, idle usage doesn't really matter.

 

Im guessing that supermicro server is pretty cheap, right. Those dells cost more, but you get more features. For this use the cheaper one should be fine.

[Sprawlie]

Another question: How confident are you in how well the Database is designed and how efficiently it has been setup?

 

you can throw all the hardware in the world at a database of any size, but if it's poorly optimized. has indexes that don't match the common queries, or numerous other inefficiency in the DB

 

As your databases get larger, these lack of efficiencies become extremely noticeable.

 

Also, how much RAM your server will need will vary based on what load and types of transactions it's handling. but the good answer is. the more RAM you can get the better. if you can fit your whole DB in RAM, even better

 

don't know if anyone else has mentioned either. DON'T RAID 5 your DB partitions. horrendous write performance. 

 

if possible: Dedicate drives to your DBs. and keep them separate. if you're looking for mission critical DB performance than don't share their disks with other applications or even each other. Give them the exclusive read/write to their own volume

 

 

 

[Burningchair]

i am quite confident that the database is just fine. NAV ist only allowed to be installed and set up by microsoft quallified vendors therfor it shoud be an at least ok optinmised Database. 

[leadeater]

1 hour ago, Burningchair said:

i am quite confident that the database is just fine. NAV ist only allowed to be installed and set up by microsoft quallified vendors therfor it shoud be an at least ok optinmised Database. 

Like I mentioned that is rarely the case, there will be nothing wrong with it but there will be optimizations that you can do to increase performance.There are some really good SQL performance monitoring tools that will give you a really good insight in to how your database server is performing and what is being run on it.

https://www.red-gate.com/products/dba/sql-monitor/

https://www.red-gate.com/hub/product-learning/sql-monitor/monitoring-sql-server-performance-whats-required

https://www.datadoghq.com/dg/monitor/ts/sql-benefits-ts/

 

More basic optimizations could be adding more tempdb files, the default used to only install a single tempdb file and was an after install take. More tempdb files means more I/O throughput/performance and more queries that need to use tempdb can run, tempdb can become a bottleneck.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15

https://www.sqlshack.com/overview-of-the-shrink-tempdb-database-in-sql-server/

 

Other optimizations are environment specific to you and you need to use SQL performance statistics or a monitoring tool to find the best optimizations for you. These are all the things you can't know ahead of time and is why Microsoft or any other vendor can't proactively do them beyond the common basics. This is the case for Microsoft System Center products which have always required after the facts changes and ongoing maintenance, it's also the case for vendor software that is not from Microsoft that use and install MSSQL as part of the install.

 

Just moving over to SSDs will get you a huge performance increase so it's quite likely you won't need to do anything more than check the number of tempdb files but it's not a good idea to assume that a Microsoft product or vendor has best optimized your database, there is a difference between installing as per best practice and actually optimizing a database for the workload or customer.