John Liu Blog

Every drop counts

SQL Server Replication Setup

To setup SQL Server transactional replication, the publisher needs to be Standard or Enterprise edition. Subscriber can be any edition (except SQL Server Compact), but will require standard or Enterprise edition as well if configure merge (bi-directional) replication. We will need to create some local Windows accounts to run replication agents: Agent Location Account name Snapshot Agent Publisher <server name>\repl_snapshot Log Reader Agnet Publisher <server name>\repl_logreader Distribution Agnet Publisher/subscriber <server name>\repl_distribution Merge Agnet Publisher/subscriber <server name>\repl_merge On Publisher machine, create a file share for the snapshot folder.

Posted by John Liu Thursday, July 25, 2024

SQL Playground Setup

This post descript the steps to setup a SQL Server failover cluster playground environment. Create a base server template VM of Windows Server 2022, with features .Net Framwork 3.5.1, Failover Clustering, and Multipath I/O installed via the Server Manager. Assign 4 Network Adapters, with the first adapter using NAT. Once the template server setup is done, run sysprep from cmd. The options for sysprep should be set to “Enter System Out-of-Box Experience (OOBE)” and the checkbox for Generalize should be checked, and set Shutdown option as Shutdown.

Posted by John Liu Saturday, October 14, 2023

PowerShell export data to file

This example PowerShell script exports data from SQL table into files and then zip them together. Clear-Host function ExportDataToFile { param( [string]$instancename, [string]$dbname, [string]$flowname, [string]$clientname, [string]$filepath ) $guid = @() $guid_replace = @() if ([string]::IsNullOrEmpty($filepath)) { throw "filepath is rquired but not specified!" } if ($filepath -match "\\$") { $filepath = $filepath.Substring(0,$filepath.Length-1) } #connection to SQL $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=" + $instancename + ";Database=" + $dbname + ";Integrated Security=True" $SqlCmd = New-Object System.

Posted by John Liu Saturday, July 1, 2023

SQL Server Relocate Tempdb file location

There might be time you would like to relocate tempdb file to a new location. Make sure the SQL Server service account has required permission to the destination folder. Run the following command to move each database file to the new location: ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'E:\Temp\tempdb.mdf') Restart SQL Server service and tempdb should be relocated to the new location. If you encounter any issue that caused the SQL Server service fail to start, you can try to start the service in emergency mode and then connect to it make any required adjustment.

Posted by John Liu Sunday, June 18, 2023

SQL Server with minimal configuration

There are time that the SQL Server instance might not be able to start up due to tempdb files are not accessable. To change the tempdb file location, you can start up the instance with minimal configuration, using parameter -f net start mssql$NamedInstance -f You can also use trace flag 3608 that tells SQL Server not to start up/recover any database but master. Once the instance is start up, you can then connect using either SQLCMD for SSMS to change the tempdb file location.

Posted by John Liu Sunday, June 11, 2023