A certificate used in TDE will continue to work even after its expiration date. This is because the Database Encryption Key (DEK) in the user database is the key that encrypts the data at rest. DEK is the symmetric key stored in the user database boot record. The certificate which is stored in the master... » read more
Issue: Databases keep being in a status “synchronizing”. Question mark in the icon for primary server. Answers: “Synchronizing” state is the normal, healthy state for an Asynchronous-commit replica. Asynchronous-commit replicas support only the manual failover mode. Moreover, because they are never synchronized, they support only forced failover. Asynchronous-commit replicas are never synchronized
Also configured in the Linked Server Properties “Server Options” section called “Enable Promotion of Distributed Transactions for RPC”. ‘remote proc transaction promotion‘ is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off (FALSE),... » read more
Make sure the “Distributed Transaction Coordinator” service is running in the Window Services. Make sure the Security settings is configured correctly in Windows Component Services. Make sure firewall is not being blocked in the “Allow apps to communicate through Windows Firewall” section of Windows Firewall Control Panel section. If SQL Server Linked Server is setup,... » read more
Specifies the start of a Transact-SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC). Unlike a transaction on a local database, a distributed transaction involves altering data on multiple databases. Consequently, distributed transaction processing is more complicated, because the database must coordinate the committing or rolling back of the changes in a transaction as a self-contained unit. Sources: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-distributed-transaction-transact-sql?view=sql-server-ver15
Error: Fix: Make sure the “Enable Promotion of Distributed Transaction” is set to “False” on the linked server property. ‘remote proc transaction promotion‘ is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off... » read more
Error: OLE DB provider “SQLNCLI11” for linked server “xxxxxx” returned message “The partner transaction manager has disabled its support for remote/network transactions.”. Msg 7391, Level 16, State 2, Procedure dbo.xxxxxx, Line 1 [Batch Start Line 14] The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “xxxxxx” was unable to begin... » read more
Database backup will not happen on the primary server unless you specific in the “Backup Preferences” of Availability Group Properties. Note: Default is set to “Prefer Secondary”. When setting up database backup via maintenance plan, the job will still run and report success, however the backup files will not be created because the fn_hadr_backup_is_preferred_replica(‘MyDB’) flag... » read more
SSMS -> Management -> Maintenance Plans Maintenance Plan Tasks Back Up Database Check Database Integrity Execute SQL Server Agent Job Execute T-SQL Statement History Cleanup Maintenance Cleanup Notify Operator Rebuild Index Reorganize Index Shrink Database Update Statistics Sources: https://docs.mendix.com/developerportal/deploy/mendix-sql-maintenance-plans