John Liu Blog

Every drop counts

SQL 2025 Gotcha

SQL 2025 is GA and SQL 2016 is on it’s end of life on Jul 14, 2026. Some changes in SQL 2025 might break things working before. SQL 2025 uses MSOLEDBSQL 19, and its defaults are different from what we have been using, like with old SQL Native Client (SQLNCLI). In SQL 2025, encrypt is on by default, trust server certification is off by default, and it enforces strick certificate chain validation.

Posted by John Liu Monday, February 9, 2026

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 Server Replication

There a 4 type of replications: Snapshot replication Transactional replication Peer-to-Peer replication Merge replication Peer-to-Peer Replication Column/FK/Constraint changes are replicated but trigger/index changes are not replicated. Changes are replicated almost instantly. Table with timestamp column is not supported, no work around. Identity column needs to be managed carefully. When setup replication, identity column will automatically have the NOT FOR REPLICATION option set to YES. Don’t set the option to NO (even though you can via script after replication setup).

Posted by John Liu Tuesday, September 6, 2022

Remove SQL Server Replication

Following script helps to cleanly remove all replication objects configured on the publisher server. ONLY run this script if you want to completely remove entire replication set. DECLARE @distributionDB SYSNAME = N'distribution' -- CHANGE THIS TO THE DISTRIBUTION DATABASE NAME. default is distribution ,@publisher SYSNAME = N'' -- CHANGE THIS TO THE PUBLISHER NAME. ,@publicationDB SYSNAME = N'' -- CHANGE THIS TO THE PUBLICATION DATABASE NAME. ,@subscriber SYSNAME = N''; -- CHANGE THIS TO THE SUBSCRIBER NAME, comma separated if more than one subscriber.

Posted by John Liu Monday, September 5, 2022