Normally, whenever I visit my clients, I always notice that they deploy SQL SERVER in its default paths and values. It seems very easy to press the NEXT button on every screen on the Installation wizard and complete the installation. But as a result, it affects production performance and company business.
So today, I am going to explain the Installation of SQL SERVER best practices. For this purpose, I used SQL SERVER 2022 Developer Edition. The size of the software is 1.08 GB, and it can easily be downloaded from the Microsoft site. SQL Server 2022 on-premises Download link https://www.microsoft.com/en-us/evalcenter/download-sql-server-2022
Step 1
Step 2
Step 3
The below File is downloaded in a designated location, double click on the file to download the full installer.
Step 4
Step 4
Step 5
Step 6
Below SQL Server ISO file downloaded with the size of 1.08 GB.
Hardware and Software Requirements
Disk Space Requirment
Windows installer require 6 GB minimum disk space during SQL Server installation process, it uses this space for temporary files on the system drive.
Microsoft published below feature wise disk space requirment, so the user can install and choose feature as per business needs.
Storage (Disk): –
Let us discuess Storage. As it is the main valuable area for SQL SERVER components. Storage should have proper redundancy and fault tolerance according to meet all business needs. Capacity planning is most important part. Before proceeding installation, the designated person must review number of required disks as per business and budget, Plan and follow proper disk naming conventions and format all disk and follow below points.
Separate Disk for each component
All DB components should be place in separate Disk to ensure best performance.
Main components include Windows, DATADB, MASTERDB, LOGDB and TEMPDB
Allocation Size
Each disk should have 64k allocation unit size. Because SQL SERVER extent size 64k which contain 8 pages and each page size 8k.
GPT format
If you need more storage and capacity is concerned, then GPT is a good option because it supports 128 primary partition and the GPT maximum size is 9.4 ZB (zettabytes) as compared to MBR which support 2 TB size with 4 partitions only.
Note: – One Zettabyte = almost one thousand Exabytes = almost one billion Terabytes = almost one Trillion Gigabytes
Disk Performance Test
Once all disks properly configured then Disks performance test need to be performed through SQLIO tool to check disks response time, it is important to act pro-actively.
Monitor
Atleast Super-VGA (800×600) or higher resolution requires SQL Server
Memory
Express Edition require Minimum 512 MB while recomendation is 1 GB
All other Editions Require Minimum 1GB while recomendation is 4 GB.
Note:- Memory should be inscreases as Database size increase to achieve optimal performance.
Processor Speed
Minimum processor x64 , 1.4Ghz While Microsoft Recommendation is 2.0 Ghz or faster.
Instant File Initialization
Enable Instant File Initialization, this option improves file auto growth performance in SQL SERVER. Restart SQL SERVER instance to effect changes.
Software Requirments:-
Below requirments apply to all SQL Server installations
Operating system
Atleast Windows 10 or Windows Server 2016 require
.NET Framework
Minimum .NET framework version 4.7 or higher requires with Minimum OS (Win 10 or Win 2016 Server)
Install SQL Server 2022
Select SQL server Edition. In our case it is Evaluation edition
Accept the License Terms
Microsoft Update
TEMPERORY DATABASE
TempDB should be configured in multiple data files to gain the maximum I/O throughput. All datafiles should be the same size as SQL SERVER use them in ROUND ROBIN fashion. SQL SERVER recommend 1:1 ratio between TempDB data files and logical processors up to 8.
As we configure tempdb to its own separate disk, so we make it bigger as per our load, but all files size should be same. In my case I make it 1024 MB initial and auto growth size.
MAXDOP
The default value of MAXDOP is Zero. Zero means MAXDOP use all processors if query runs in parallel.
You can set your MAXDOP value based on your load. We can also limit the number of processors for parallel processing. When you change the value, there is no need to restart the SQL SERVER. It effects immediately.