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

Setup R and Python on SQL Server 2022

When setup R or Python on SQL Server 2022, especially for named instance, some special notes required, apart from the offical documentation. You need to make sure when doing install, both Database Engine Services and Maching Learning Services and Language are installed. Unlike with earlier version of SQL, R and Python libraries are not installed/configured automatically in SQL 2022. You need to download and install latest R4.2. Then need to install some standard R dependencies.

Posted by John Liu Tuesday, March 5, 2024

SQL Server File Size

There are serveral ways to find out database size. Apart from using the SSMS GUI, you can using following scripts options. --this return size info for all databases --the size is in kilobytes exec sp_databases --this return size info for all databases SELECT DBName = DB_NAME(database_id) ,FileName = name ,physical_name ,type_desc ,Allocated_Space_GB = CONVERT(DECIMAL(18,2),size * 8.0/1024/1024) --,max_size --,growth --,is_percent_growth FROM sys.master_files WHERE database_id > 6 AND type IN (0,1) --this return size info for current database with free space info SELECT DBName = DB_NAME() ,FileName = name ,physical_name ,type_desc ,Allocated_Space_GB = CONVERT(DECIMAL(18,2),size * 8.

Posted by John Liu Sunday, March 19, 2023