John Liu Blog

Every drop counts

SQL Server Firewall

In Windows, you can use following Powershell script to explicitly enable remote access to your SQL Server instance (substitute the DisplayName and LocalPort accordingly). New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow You can find more information about ports used by various SQL Server services in this articals Configure the Windows Firewall to allow SQL Server access.

Posted by John Liu Wednesday, August 14, 2024

SQL Server on Azure VM

Some considerations when configure SQL Server on Azure VM. This also applicable to AWS VM For storage: - Separate volume for data and log file - Enable read caching on data file volume - Disable any caching on log file volume - Use D: drive (local SSD that will be cleared each time with server reboot) for TempDB - Enable instant file initialization - Move trace file and error log directories to data volume To evaluate disk performance when migrate from on-premises to cloud, capture following counters in Performance Monitor:

Posted by John Liu Wednesday, November 17, 2021

SQL Server on Azure config best practice

Each Azure VM has a OS disk (C drive) and a temporary disk (D drive). Only store on the temporary disk temp data that doesn’t need to be retained as all data on this disk will be lost after server reboot. The best practice for SQL Server on Azure VM is to use Premium Disks pooled for increased IOPs and storage capacity. Data file should be on its own pool with read-caching on the Azure disk.

Posted by John Liu Sunday, November 7, 2021