John Liu Blog

Every drop counts

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

Linked Server DTC

When run query involve transactions using Linked Server, you will need to enable Network DTC Access for MS DTC transaction on both servers. If not configured, you may receive following error message: “The partner transaction manager has disabled its support for remote/network transactions” (if not enabled on remote server) “The transaction manager has disabled its support for remote/network transactions” (if not enabled on the local server) “The operation could not be performed because OLE DB provider “MSOLEDBSQL” for linked server “LinkedServer” was unable to begin a distributed transaction”

Posted by John Liu Friday, July 22, 2022

Upgrade SQL SSISDB

When upgrade SSISDB, a few steps need to be considered when moving the SSISDB to a higher version on a new SQL instance. 1; Backup currently database master key BACKUP MASTER KEY TO FILE = 'file location/name' ENCRYPTION BY PASSWORD = 'your password for the backup key file' If you forgot the current password for the master key, you can reset the password via backup the master key (if not already have one) and then restore it with a new password

Posted by John Liu Tuesday, July 19, 2022

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