What is query optimizer fix setting in database scoped configuration.
This was introduced in SQL 2016 version , this actually will take care of the query optimizer improvements while the hotfix or CU is updated when the setting is ON depending on the db compatability option , when the db compatability is 130 (sql 2016) then it doesn't matter if the setting is ON or OFF. The trace flag 4199 needs to enabled to get the advantage of this configuration after hotfix is updated.
WHAT IS OPTIMIZE FOR ADHOC WORKLOAD
When this setting is set to 1 the plan cache stores the stub plan for the query in the buffer cache instead the entire plan , when the same query is executed it reuses the stub plan .
hashtagTransparent Data Encryption (TDE):
Encrypts the entire database at rest.
Data is automatically encrypted and decrypted as it is read from and written to disk.
Provides encryption at the file level, securing backups and data files.
Key management is simplified, as TDE handles key encryption with a database encryption key (DEK) and a certificate stored in the master database.
Column-level Encryption:
Allows encryption of specific columns within a table, providing granular control over data security.
Different columns within the same table can have different encryption keys.
Offers protection for sensitive data while allowing non-sensitive data to remain unencrypted for easier querying and processing.
Requires explicit encryption and decryption functions to access encrypted data.
Always Encrypted:
Enables client-side encryption of sensitive data before it is sent to the database.
Data remains encrypted during transmission and while stored in the database, ensuring end-to-end encryption.
Encryption keys are managed outside of SQL Server, typically by client applications, enhancing security.
Supports deterministic and randomized encryption schemes, allowing for different levels of security and search capabilities.
Backup Encryption:
Provides the ability to encrypt database backups to protect sensitive data during storage and transmission.
Backup encryption can be applied at the time of backup creation using a certificate or asymmetric key.
Protects backups stored locally or in the cloud, preventing unauthorized access to data in case of backup theft or interception.
Decryption is required to restore encrypted backups, ensuring data integrity and security.
Transport Layer Security (TLS):
Encrypts data transmitted between SQL Server and client applications.
Provides secure communication over the network, preventing eavesdropping and data interception.
Supports various TLS protocols and cipher suites for encryption and authentication.
Configuration options in SQL Server allow for enabling and enforcing TLS encryption to protect data in transit.
COLUMN LEVEL ENCRYPTION
Lets understand with and example for column-level encryption:
-- Creating a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123';
-- Creating a certificate
CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate';
-- Creating a symmetric key
CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCertificate;
-- Encrypting data in a table
OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE MyCertificate;
UPDATE dbo.YourTable
SET EncryptedColumn = EncryptByKey(Key_GUID('MySymmetricKey'), 'SensitiveData');
-- Decrypting data
SELECT CONVERT(varchar, DecryptByKey(EncryptedColumn)) AS DecryptedData
FROM dbo.YourTable;
CLOSE SYMMETRIC KEY MySymmetricKey;
Connect SQL SERVER instance when there is no logins working.
Sometimes we may miss password or logins not working to connect sql server in this case follow the below steps to connect the instance.
1. Stop the instance.
2. Open CMD in Adminstator mode- Root to SQL Server bin folder with -m which will start the sql server in single user mode.
3. Open another CMD in adminstrator mode- Run the script to set the new login or create login .
4. Exit both CMD and start the instance.
Why the query is running slow in application and fast in SSMS.
This can be due to below factors.
1. The parameters or variables are not same between Application and SSMS.
2. Check execution time on server.
execution time on server can be checked by
SET STATISTICS TIME ON
<YourQuery>
SET STATISTICS TIME OFF
The time may vary due to network speed between the servers or hardware resource different.
3. SET options
Some options like artithabort ,nulls,force_plan settings may different in server and application.
For eg. by default arithabort is ON in ssms and OFF in application, this may affect the performance.
How to migrate SSRS from one server to another.
https://datasteve.com/2024/04/03/migrating-ssrs-from-one-server-to-another.
Difference between Full and tail log backup ?
Is it possible to take backup when db is corrupted ?
what parameter we need to use whenever we are taking the backup and db is corrupted ?
Scenario: You have an on-premises Microsoft SQL Server (SQL1) hosting five databases. The goal is to migrate these databases to an Azure SQL Managed Instance with minimal downtime and no data loss. What tool should you use?
Options:
A) Always On Availability Groups
B) Backup and Restore
C) Log Shipping
D) Database Migration Assistant
Answer: B) Backup and Restore
Explanation:
While the Database Migration Assistant (DMA) is valuable for assessment and conversion tasks, it doesn’t directly support migrations to Azure SQL Managed Instance. The recommended approach for database migration is the Azure SQL Migration extension for Azure Data Studio, which supports both online and offline migrations. However, this option isn’t listed here. Log shipping isn’t viable for Azure SQL Managed Instance as it only supports full backups for restoration.
