In this article, I will talk about how SQL Server writes data to storage and reads data from storage. I will also explain how to create the necessary infrastructure for faster reading and writing of data.
Before you read this article, I suggest you read the article “Network Configuration of SQL Server Servers and Always ON Availability Group Synchronization from a Different Ethernet Card“.
After learning how the network infrastructure works, let’s talk about the story of a query:
The application makes a request to go to the database with a query through the Client. This request goes from the application server’s ethernet card to the organization’s main network switch.
If the application and the database server are not in the same vlan(for the security of the database, the application must be in a different vlan), it is controlled by firewal whether the requesting server is authorized to access the database server through the specified port.
Of course, many companies have some security devices that check whether the incoming query is attacking. Like IPS.
Query go to the database server if it passes authorization check in Firewall.
If the query is insert, data must be written to the disk. If the disks used by the SQL Server are on a storage, the records in the query go to the SAN Switch using the FC ports on the SQL Server Server. These records then go to the relevant storage via SAN Switch and write the data.
If query is select, sql server first looks for records in memory. If the data available in memory, sql server retrieves the data from memory and sends it back to the application in the same way.
Otherwise, it will have to go to disk and bring it from disk. Usually there is a SAN Switch between the SQL Server server and the storage.
After receiving the data through storage, sql server transfers the data to the memory on the SQL Server Server. It then sends the data via the network switch back to the application.
How To Configure the SAN Infrastructure for faster performance:
If you have more than one storage and more than one SQL Server server, you should use a SAN Switch in your organization. And SAN Switch should not be managed by inexperienced people. A small error can cause you to lose disks.
Let’s explain the sample SAN infrastructure.
There must be at least 4 FC ports on the server. If the data traffic is too high, these ports must be at least 8gbps. Of course, evolving technology and increasing data are constantly changing this value. Also for a simple application, 1gbps ports may also be sufficient.
There must be at least 2 SAN switches and these SAN switches must be configured as backups of each other.
In consolidated systems, 2x8gbps FC port per server is sufficient in today’s conditions. But by calculating the total IOPS and throughput of your applications, you can use 4x8gbps or 2x16gbps.
To calculate the total IOPS and the throughput value of your server, you can read my article “How To Calculate IOPS and Throughput of Your Database Server Using Data Collector“.
