I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size. There are some quite large tables in the DB with more about 1010 .. 11 records. Every table has typical primary key index. Also most of the tables have composite non-clustered indexes too (B-tree). In a few cases we have defined columnstore indexes. Which of the below physical designs of the server will result in better performance1 and db management2 overtime? **Scenario 1.** create one (or more) filegroup per physical drive (we only have SSD NVMe drives), and spread the heavily used tables and indexes across different drives. **Scenario 2.** Bundle all drives using RAID 10 and create a single large volume. Data will be striped on all disks by the OS. In this scenario, data file (.mdf), log file (.ldf) and tempdb are all stored on a large RAID 10 volume. **Scenario 3.** Partition tables/indexes and store each partition on different physical drive. I should mention that it wold be close to impossible to distribute data uniformly across all partitions because of the type of data we are dealing with. If you think none of the above are ideal solutions; I'd love to know what would you recommend. Many Thanks. by performance I mean lower retrieval time and higher IOPs, for a given indexing architecture by db management I mean add/remove/update data, keeping the performance, redundancy, availability ..

I installed MySQL db for storing my simulation data in phd. First I used a pc with i7-8700 cpu, 24 gb ram and 1 TB nvme disk. Then I transferred data to a newer pc with i7-12700 cpu, 48 GB ram and 2x1 nvme 1 TB disk in soft raid0 configuration. Since then my query performance become very slow. It was 10-15 minutes in old pc to 2.5 hours on the newer hardware. I configured the conf file according to new hardware but nothing changed. Any ideas please. I am desperate.

I am setting up new server and am a little confused in my research of HD configuration. My main question is : 1. Is it recommended to have the Trans Logs and DB logs (ldf) on separate spindles or can they be on the same? 2. Do Trans Logs and ldf logs write concurrently or one after the other? 3. My plan is to configure it as follows: >C:\ Raid 1, >Data (Raid 10), >Logs (Trans & .ldf) Raid 10, >TempDB (Raid 1), >Local Backups (Raid 10). Does that make sense or should I separate the Trans & .ldf files onto separate raid configs ?

Apologies if I am posting this to the wrong place. We are unfortunately having to handle our own server administration in-house and no one here is an expert at servers in any way! (we are web app developers and .Net/SQL/javascript programmers) We have a Dell server with a RAID 1 setup on the C: drive (for the Windows Server system) and D: drives (for our SQL databases), which has worked great in the past when 1 of the 2 drives has an issue.. We recently had the C drive fail completely, (BAD_SYSTEM_CONFIG_INFO) and unfortunately the RAID 1 did not help in this case as the system on both active drives were completely unusable (at least to our knowledge). We are back up and running now but it is very time consuming to have to re-install Windows Server, SQL Server and all the updates. My question : Is there an alternative way we could be setting up or backing up the C: drive to avoid having to reinstall everything from scratch, should we have another such failure?

I have a SQL Server database running on a virtualized Windows Server 2012 R2 (VMware ESXi). I have a task from Accountancy to boost the performance of the database. The database has about 70GB and the RAM allocated is about 90GB. The virtual machine is spining on SAS Disks 10k - RAID10 at the moment. I want to make an upgrade from spinners to SSD but I would like to undertand if this will give a big difference in performance as the uprgade is somewhat expensive as I'm talking about Enterprise SSD. Down bellow I've written what spinners I use at he the moment and the SSD that will be bought eventualy, the SSD's will be used in RAID10. I would like to ask you to help me understand if the upgrade will give me the needed boost in performance? Thank you. Hardware- Server: Lenovo x3650 M5 CPU: 2x - Intel Xeon E5-2630 v4 10Cores 2.2 GHz 25MB RAM: 128 GB Storage: Lenovo Storwize V3700 V2 (SAS Disks 10k - RAID10) **What I Have:** IBM (Seagate ST900MM0168 900GB 10K RPM 12Gbps 2.5" SAS Hard Drive) Brand: IBM Model: 01EJ586 Capacity: 900Gb Interface: SAS 2.5 inch Data Transfer Rate: 12Gb/s Rotational Speed: 10,000rpm External Transfer Rate: 1200 MB/s Sustained Transfer Rate (Outer to Inner Diameter): 215 to 108 MB/s Average Latency: 2.9ms Average Seek Time: 4.6ms Internal Cache: 128Mb Hot-swappable: Yes Caddie: Yes **The Upgrade:** IBM AS7J 1.6TB 12G SAS 2.5" MLC G3HS Enterprise SSD Based on proven HGST Ultrastar SSD1600MM drive technology Uses 20 nm Multi-Level Cell (MLC) NAND flash memory Part number - 2.5" G3HS: 00FN409 Interface: 12 Gbps SAS Capacity: 1.6 TB Endurance (drivewrites per day over 5 years): 10 DWPD Endurance (total bytes written): 29.2 PB Data reliability: 1 in 10(17) bits read MTBF: 2,500,000 hours (0.35% AFR) IOPS reads (4 KB blocks): 130,000 IOPS writes (4 KB blocks): 100,000 Sequential read rate (64 KB blocks): 1100 MBps Sequential write rate (64 KB blocks): 765 MBps Read latency (seq): 100 µs Write latency (seq): 45 µs

Link: https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15#Recommendations > To maximize performance, create files or filegroups on different > available disks as possible. Put objects that compete heavily for > space in different filegroups. > Use filegroups to enable placement of objects on specific physical > disks. > Put different tables used in the same join queries in different > filegroups. This step will improve performance, because of parallel > disk I/O searching for joined data. > Put heavily accessed tables and the nonclustered indexes that belong > to those tables on different filegroups. Using different filegroups > will improve performance, because of parallel I/O if the files are > located on different physical disks. > Don't put the transaction log file(s) on the same physical disk that > has the other files and filegroups. In a SAN network, is there any benefit to creating files or filegroups on different disks? or any of the above quotes? I am asking because the SAN network has multiple disks and a single file is scattered amongst multiple disks.

I read a lot about performance improvement if instead of a single large .mdf file you partition it by multiple (.ndf) files. But I also found from documentation that the same improvement can be accomplished if a file created on RAID. So would that make sense for me to partition .mdf file into multiple files or should I just leave it as is, since it's on RAID?

What I learn is: PG block size >= ni * VM block size >= nj * host block size But what happens when the DB in a VM is on an SSD RAID ARRAY? The ARRAY strip size changes the game and puzzle me. What should be the strip size for an ARRAY of SSDs for an OLTP DB? Is there a relation like : Array strip size >= n * host block size ? Let start from the base by adding one layer at a time: i) The recommendation of a PostgreSQL DB running on a physical server is strait. PG block size (default 8K) should be a multiple of the OS block size : PG block size >= n times OS block size. ii) When PG runs inside hyper-V (host and VM are Win2019) I would say that the host and VM block sizes (cluster sizes) should be equal. Can anyone confirm? iii) RAID strip size The array is 10 x 8TB SSD in RAID 6 (would prefer RAID10, but RAID 6 chosen for capacity) https://learn.microsoft.com/en-us/answers/questions/564372/raid-setup-and-drive-formatting-for-hyper-v-vhdx-1.html says to use 64KB stripe size, and 64KB cluster size on file systems of the host and VM, but I am not sure it is right for a PostgreSQL DB (OLTP type)? Our system is currently configured as follow: PG 8KB, VM 4KB, host 64KB, ARRAY 64KB 1. My first guess would be to change all block and strip sizes to 8KB. Does it make sense? PG 8KB, VM 8KB, host 8KB, ARRAY 8KB 2. But, 8KB strip size may be too small, should I leave it to 64K and set all block sizes to 8K? PG 8KB, VM 8KB, host 8KB, ARRAY 64KB 3. Another possibility would be to compile PG with a 16KB block size and set the other block sizes accordingly. PG 16KB, VM 16KB, host 16KB, ARRAY 16KB or 64KB BTW we do not have a pure OLTP DB because our large tables are written in large chunks and a significant portion of selects read entire chunks (blocks read should be collocated). Am I losing my time and should leave the current settings as is? According to https://backupchain.com/i/hyper-v-block-size-for-ntfs-whats-recommended I am.

An Ubuntu 18.04 server dedicated to running a PostgreSQL 11.2 server (with TimescaleDB extension) will be running out of disk space soon so new SSD disks will need to be added to the machine to support the growing database size. The data is expected to continue increasing at the same/higher rate, so the storage hardware will need to be increased continuously until the machine runs out of 2.5" drive bays. Only then will distributing the database over multiple machines be considered, due to the increased complexity involved. **Thoughts** 1. Union file systems like mergerfs can pool the drives together, solving the storage expansion problem easily. But this will increase the latency of database operations and are thus not recommended . Redundancy can be added by having an underlying RAID-1/5/6/10 or using SnapRAID. 2. RAID-0 and RAID-10 allows expansion of the RAID array into the newly added drive, with the bonus of increased performance from striping. However every drive being added is an additional point of failure. Furthermore, numerous people claims that mirroring SSDs is of limited use as both SSDs in RAID-0 will likely fail at the same time . So maybe this means that RAID-10 is no better than RAID-0. Furthermore, failure rates increase linearly with each additional SSD added. 3. RAID-5/6 reduce performance due to parity calculations and having write to 2 drives, reducing the effective IOPS by 75% . Seems to be a poor choice for databases. 4. PostgreSQL TABLESPACES can be used to assign every table to a specific drive. However, using tablespaces will make recovery very complicated . Furthermore, is it possible to create new tablespaces on the new drives and let Postgres automatically decide where to write new record to? 5. ZFS, BTRFS? Not familiar with them, willing to explore if they are suitable. **Question:** What is the year-2020 recommended method of expanding the storage of a PostgreSQL machine, if expansion has to be done frequently (1-2X a year), performance should not be affected much, and recovery should not be so complicated that it may lead to data loss? RAID-10 seems like a good idea to me, except that RAID-1 appears to have limited usage while causing the 'loss' of half the disk space, made worse by the increasing points of failure with increasing number of drives. Due to budget constraints, we cannot fill up all 16 drive bays in a 2U chassis with SSDs at once, so it has to be done incrementally. Any advice is greatly appreciated! **EDIT:** After looking into ZFS, it seems like this might be one of the solutions for my case. - A ZFS pool with only mirrored ZFS vdevs (2 drives per vdev) will allow storage pool expansion by adding 2 drives at a time - Keeping 1-2 hot spares in the ZFS pool can allow automatic failover by ZFS when 1 of the drives fail - With mirrored vdevs, the rebuild time after a drive has failed will be way faster than with RAIDZ vdevs. This also reduces the chance that the surviving drive used for the rebuild can fail during the process. The degraded mirrored vdev will have much greater performance than a degraded RAIDZ vdev during rebuild - ZFS supports inline data compression which helps significantly (4X compression) with reducing the storage requirements of TimescaleDB data without having to use native TimescaleDB compression that prevents compressed data from being updated unless it is uncompressed. This is pretty important too as TimescaleDB is known to have poorer data compression compared to other databases like InfluexDB - Monitor and replace the SSDs when they are about to fail, as suggested by *jjanes* If I understand this correctly, using ZFS mirrored vdevs will check all my boxes: Continuously add drives, allow drive pooling which provides a single mountpoint, data redundancy and a bonus ~4X data compression.

Why is that when a single disk fails from a RAID 6 setup MS SQL 2014 database corrupts with the following error: >DESCRIPTION: SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0xad191b0d). It occurred during a read of page (1:144366313) in database ID 19 at offset 0x0001135b5d2000 in file 'D:\MSSQL12.LIST133\MSSQL\DATA\test.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. The RAID 6 status is healthy and hasn't change even with one disk failed (the issue should appear when 3 DISKs fail). How does the SQL knows what is going on with the DISK since it is not a software RAID 6? There is a seperate controller and a storage. Also what is the point of having spare disks in the RAID if the SQL is going to corrupt anyway? How can I avoid this?

I hope I am asking in the right community, If not, any suggestion will be appreciated. I am doing a survey paper where I am doing a comparison between erasure coding and replication techniques. At this stage, I am comparing them regarding specific parameters as below. The table that I am trying to construct is dealing with parameters that differentiate which technique is better in: storage efficiency, availability, durability, encoding time, latency of failure and cost of reconstruction. - Since replication is faster in terms of read performance when failure occurs, is it correct when I say that replication technique has **Higher latency on failure**? and the same for encoding time, is it correct to say replication has a **high encoding time** since it has better performance time in writing? - Does the reconstruction cost of failure in erasure coded system is higher than replication? does it involves more disk I/O ? will it be different if the failure is transient or permanent? - Will it be more informative if I compared all the above parameters according to transient and permanent failures? ---------- is it correct if I compare them as below? *Erasure code:* **Higher** ( Durability, Storage Efficiency, Availability ) and **Lower** (Encoding time, latency on failure, cost to reconstruct) Replication : * **Higher** ( Encoding time, latency on failure, cost to reconstruct) and **Lower** ( Durability, Storage Efficiency, Availability) ----------

According to best practices, it is recommended to move all tempdb (not just tempdb) files into different physical disk. I have a virtual server which had originally 4 LUNs from the same RAID 10 pool. By the help of volume manager, I converted those 4 LUNs into 4 different volumes. Now the question is, will it make any difference by moving tempdb into separate volume, or it is just fine to keep them with other SQL Server files in terms of performance?

I'm working on web hosting project. The planned system structure is going to be like this: - one main hard-drive will contain the binary of mysql. - A second hard-drive will contain the datadir - the third hard-drive should contain a backup of this mentioned datadir. I can only think of two options here either replication using 2 instances of MySQL servers or just using RAID 1 mirroring only the datadir. I would go with mirroring but I'm kind of suspicious of using this method and my main frightening thought is what if I lost the main hard-drive that contain the binary of mysql does setting new version will cause my a problem .. any ideas suggestions will be great full thanks I should mention that using INNODB is a constraint

I attached 4 10GB disks to MySQL Server and I used RAID 0: but still MySQL performance is the same. Do I have to change configuration in my.cnf to let MySQL know about RAID 0? This are my mount results: This is not development box, it is my production server. I used to have MySQL server on AWS but now I am using Azure, and I/O really hurts MySQL. I am not a server admin expert, but I am trying to solve here my problem, a consultant recommended to me to do RAID 0. I am using newrelic to test my performance. It shows how bad is MySQL, my server is 8 cores and 14 GB Azure server.

I installed Windows Server 2012 on my server using RAID 5. But a problem occurred on my server. I extracted the data from 10 hard disks with data recovery. When I attached the extracted data files in SQL Server 2012 the file is corrupted. How can I fix it?

While reading SQL Server Query Performance Tuning written by Grant Fritchey, I found it difficult to understand the following part: ***avoid RAID 5 for t-logs because, for every write request, RAID 5 disk arrays incur twice the number of disk I/Os compared to RAID 1 or RAID 10.*** I know that RAID 5 differentiate from other RAIDs with its parity feature. It means that if some of the drives fails, then it is possible to recover lost data from the other drives. I want to understand why it is not recommended to use RAID 5 for a transaction log file. Explanation in the book was not enough for me to get it. Maybe someone could explain it to me or provide a good article.

I'm considering using a RAID0 setup for one of our SQL Server clusters. I'll outline the situation and am looking for why this may be a bad idea. Also if someone you have use cases, white papers or other documentation you can point me to on this topic, that would be great. We have 3 servers in 2 datacenters that are part of an SQL cluster. They are all running SQL Server in an Availability Group. The primary has a replica sitting right next to it and another in the other datacenter. They are running synchronous replication with automatic failover. All drives are enterprise class SSDs. They will be running SQL Server 2017 or 2019. I'm thinking that there would be multiple benefits to running them on RAID0 arrays over other methods with few, if any, real drawbacks. The only negative I'm currently seeing is lack of redundancy on the primary server, so it failing increases. As pros: 1. If a drive fails, rather than run in a slowed, degraded state until someone receives a notice an manually acts on it, the server will immediately fail to a secondary maintaining full operational capability. This will have an added benefit of notifying us of a failover, so we can investigate the cause sooner. 2. It reduces the chance of failure overall per TB capacity. Since we don't need parity or mirror drives, we reduce the number of drives per array. With fewer drives there is less total chance of a drive failure. 3. It is cheaper. Needing fewer drives for our required capacity obviously costs less. I know this isn't the conventional business thinking, but is there something I'm not considering? I'd love any input either pro or con. I'm not trying to do this for query performance gains, though if there are meaningful ones feel free to point them out. My primary concern is failing to consider or address a reliability or redundancy issue that I haven't thought of. The OS is on a separate mirrored drive, so the server itself should stay up. One of those drives can be replaced and again mirrored. It is small and there aren't any database files other than system DB's on it. I can't imagine it taking more than minutes. If one of the data arrays fails, we replace the drive, rebuild the array, restore and resync with the AG. In my personal experience, restoring has been MUCH faster than a RAID5 drive rebuild. I haven't ever had a RAID1 failure, so I don't know whether that rebuild would be faster or not. The restores would be coming from a backup and rolled forward to match the primary, so the load increase on the primary server should be very minimal only syncing the last few minutes of logs with the recovered replica.

I did raid reconfiguration of SQL Server 2016 box. All my systems databases files (mdf and ldf) are in D drive and SQL Server installation also is in D drive and i have them intact. I have lost user databases and i have backup of all those. But after raid reconfiguration,i tried to start the SQL Server(MSSQLSERVER),SQL Server Lauchpad and SQL Server Agent. Only Agent starts and SQL Server(MSSQLSERVER) starts are then stops. SQL Server Launchpad gives an error when i start with error message "The request failed or the service did not respond in a timely fashion.Consult the event log or other applicable error log for details". I dont need the lauchpad,but i need SQL Server service to start. Any idea why the service is not starting.? I am using NetworkService and i chnaged it to LocalSystem and tried ,but i get error.So i have it back to NetworkSerive again.

We are looking at new servers. Today our SQL Servers are built as follows: - C/D on RAID 1 , - E : Tempdb on RAID 1, - F:log files on RAID 1 , - G : Catalog data on RAID 1 , - H: model data on RAID 1 , - I : symbols on RAID 1, - J: DB maintenance on RAID 1 , So we have a lot of SSD’s, all on RAID 1 with no sharing. We are considering changing to a large RAID 1+0. However the concern is if we will end up with performance impacts since there are random reads that happen throughout the day but not as much as writes. My question is - whether a large 1+0 RAID is suggested / recommended, which would reduce the quantity of SSD’s that we use and allow us to expand a single container easier or stick with single RAID 1’s?

Wanted some opinions, on a predicament I’m currently facing. I just got thrown with this mess.. SQL Server database files saving to Raid volume, long story short raid card died so volume is not accessible. SQL service is still running and applications accessing the databases are still responding. The backups aren’t upto date of course. I have tried to export the databases out but error occurs because the files do not exist as the volume isn’t present. However the ram is currently at 47GB. My question is what does SQL Server store in memory, as my understanding is it stores everything in ram and then dumps to file? Is the data recoverable by specialist if I do a raw ram dump? Could I get backup of the database and remount another volume with the same drive letter and file structure and try exporting the database?