Run CMD Shell with TSQL
Note: By default XP_CMDSHELL is turned off, to enable XP_CMDSHELL run the following on the database server…
Execute Dynamic SQL with sp_executesql
Sources: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15
SQL Server Login Error: The certificate chain was issued by an authority that is not trusted
Are you trying to connect to a SQL Server instance and ending up with the error: The certificate chain was issued by an authority that is not trusted You aren’t alone. SQL Server 2005 introduced authentication encryption (by default) in the SQL Native Access Client (SNAC). SQL Server will self-generate a certificate that’s then used... » read more
AlwaysOn and Transaction Log
Availability Groups must retain all transaction log records until they have been distributed to all secondary replicas. Slow synchronization to even a single replica will prevent log truncation. If the log records cannot be truncated your log will likely begin to grow. This becomes a maintenance concern because you either need to continue to expand... » read more
AlwaysOn Synchronous vs Asynchronous
Availability modes There are two availability modes, synchronous commit and asynchronous commit. Selecting a mode is equivalent to selecting whether you want to favor data protection or transaction performance. Both availability modes follow the same work flow, with one small yet critical difference. With synchronous commit mode, the application does not receive confirmation that the... » read more
Database Partition and Performance
A partitioned table is one where the data is separated into smaller physical structures based on the value for a specific column (called the partitioning column, which is defined in the partition function). If you want to separate data by year, you might use a column called DateSold as the partitioning column, and all data... » read more
Restore Database From 2008 Backup to 2019
Restore a SQL Server 2008 R2 SP3 (10.50.6560) database to SQL Server 2019 (15.0.2000.5). Note: After restore, the database files are same size as original files.
Azure SQL: Recover Deleted Records
Via Azure Portal, restore a copy of the database from a backup at specific point in time. Via SSMS, copy over deleted records from recovered database back to the original database. Note: In Azure Portal, go to the original database and select “Retore”. Select a point in time before the accidental delete occurred. Time is... » read more
Always On Automatic Failover Jobs
In addition to the automatic failover that occurs for Always On setup, these jobs also needs to be created on both the Primary and the Secondary server to help with the automatic failover. In the event of a failover, all user jobs should be disabled on the Secondary server and enabled on the Primary server... » read more