OS : Server Clocks out of sync issue with NTP

Server Clocks out of sync issue with NTP

These type of issues triggers if any host's clock goes out of sync with the time given by NTP.

In resolution, can perform following steps as troubleshooting:

1. Open Command prompt as Administrator
2. Check the current source Server to time sync on host:
     w32tm /query /source

3. Command to check Host Server date and time:

     echo %date%:%time%

4. Command to check DC’s current time settings:
     w32tm /stripchart /computer:<DC_Name> /dataonly

Steps to reconfigure Win time services:
Net stop w32time
W32tm /unregister
W32tm /register
Net start w32time

W32tm /config /syncfromflag:DOMHIER /update
W32tm /resync /nowait
Net stop w32time
Net start w32time

If doesn’t works then try with rediscover:
W32tm /resync /nowait /rediscover
W32tm /query /source
W32tm /monitor

Sync time with specific DC :

Net time \\<DC_NAME> /set /y

Command to check DC’s current time settings:

W32tm /stripchart /computer:<DC_Name> /dataonly

To Sync with DC current system time with an external time server:

W32tm /resync /computer:<DC_Name> /nowait

SQL Server : How to configure Transparent Data Encryption (TDE)?

How to configure Transparent Data Encryption (TDE)?

USE master;
GO
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password@123'; -- Create master Key

/*command to view Keys*/
SELECT name KeyName,
  symmetric_key_id KeyID,
  key_length KeyLength,
  algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;



CREATE CERTIFICATE TDECerti WITH SUBJECT = 'TDE certificate'; --Create Certificate

/*command to view certificate */
SELECT name CertName,
  certificate_id CertID,
  pvt_key_encryption_type_desc EncryptType,
  issuer_name Issuer
FROM sys.certificates
WHERE issuer_name = 'TDE certificate';


/* create DEK */

USE SQLDummyDB;
GO
 
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE TDECerti;


/*Command to view all DEK */
SELECT DB_NAME(database_id) DbName,
  encryption_state EncryptState,
  key_algorithm KeyAlgorithm,
  key_length KeyLength,
  encryptor_type EncryptType
FROM sys.dm_database_encryption_keys;



ALTER DATABASE SQLDummyDB SET ENCRYPTION ON; --Enable TDE

Backup the certificate and keys

Use master;
GO
 
BACKUP SERVICE MASTER KEY 
TO FILE = 'E:\MSSQLServer\Backup\SvcMasterKey.key'
ENCRYPTION BY PASSWORD = 'Password@123';


BACKUP MASTER KEY 
TO FILE = 'E:\MSSQLServer\Backup\DbMasterKey.key'
ENCRYPTION BY PASSWORD = 'Password@123'


BACKUP CERTIFICATE TDECerti 
TO FILE = 'E:\MSSQLServer\Backup\TdeCerti.cer'
WITH PRIVATE KEY(
  FILE = 'E:\MSSQLServer\Backup\TdeCerti.pvk',
  ENCRYPTION BY PASSWORD = 'Password@123');
GO

/* Restore Certificate Key */

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='DPassword@123';
GO


USE MASTER
GO
Create CERTIFICATE TDECerti
FROM FILE = 'D:\Backups\TdeCerti.cer'
WITH PRIVATE KEY (FILE = 'D:\Backups\TDECerti.pvk',
DECRYPTION BY PASSWORD = 'Password@123' );
GO

Advantages
1. The performance impact of TDE is minor.
2. Performs real-time I/O encryption and decryption of the data and log files.
3. No application code changes are required.
4. No architectural changes required.
5. Encrypts the Entire data in Database.

Disadvantages
1. Not granular – Cannot just encrypt specific tables/columns
2. Not good for high CPU bottleneck servers
3. No protection for data in memory
4. Not protected through communication/networks

MSSQL : Recommedation for maximum degree of parallelism ( MAXDOP )

What is MAXDOP ?

Maximum degree of parallelism (MAXDOP), that helps to controls the number of processors used to run a single statement that has a parallel plan for running. The default value is set to 0, which allows you to use the maximum available processors on the machine.

Please use the following recommendations when you configure the maximum degree of parallelism server value:

    - Single NUMA node: < = 8 logical processors, keep MAXDOP <= actual number of cores
    - Single NUMA node: > 8 logical processors, keep MAXDOP = 8
    - Multiple NUMA nodes: < =16 logical processors, keep MAXDOP <= actual number of cores
    - Multiple NUMA nodes: > 16 logical processors, keep MAXDOP = 16 (SQL Server 2016 and above), keep MAXDOP = 8 (prior to SQL Server 2016)

SQL Server estimates the cost of the query at the time of execution, If this cost exceeds the cost threshold of parallelism, SQL Server considers parallel plan for this query. The number of processors it can use is defined by the instance-level maximum degree of parallelism, which is superseded by the database-level maximum degree of parallelism, which in turn is superseded by the query hint for maximum degree of parallelism at the query level.

To gather the current NUMA configuration for SQL Server 2016 and higher, run the following query:

        SELECT
         @@SERVERNAME,
         SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
        cpu_count,  /*the number of logical CPUs on the system*/
        hyperthread_ratio, /*the ratio of the number of logical or physical cores that are exposed by one physical processor package*/
        softnuma_configuration, /* 0 = OFF indicates hardware default, 1 = Automated soft-NUMA, 2 = Manual soft-NUMA via registry*/
        softnuma_configuration_desc, /*OFF = Soft-NUMA feature is OFF, ON = SQL Server automatically determines the NUMA node sizes for Soft-NUMA, MANUAL = Manually configured soft-NUMA */
        socket_count, /*number of processor sockets available on the system*/
        numa_node_count /*the number of numa nodes available on the system. This column includes physical numa nodes as well as soft numa nodes*/
        from sys.dm_os_sys_info

You can check the current value using the following query:
       
        Exec sp_configure 'max_degree_of_parallelism'

Working of MAXDOP at Different level in SQL Server: 

I would like to put shed some light about working of MAXDOP at different level:
As you know the Maximum Degree of Parallelism (MAXDOP) can be defined in one of three ways:

    1. Instance Scoped via sp_configure
    2. Database Scoped via database properties
    3. Query Scoped via query hint option

So now which of these will trump other as follows:

   - A query hint always overrides the database and instance configuration.
   - The database scoped configuration will override the instance configuration only
when it is not the default value ( 0 ).
   - The instance scoped configuration limits MAXDOP across all databases and queries
when the a query option and database scope have not been defined.

If the databases are configured with MAXDOP=0 as the Database Scope Configuration,
then the instance level MAXDOP setting is used.

To Verify current setting or to Change at database level MAXDOP:
	1. Open the database properties dialog in the UI from Object Explorer
	2. Navigate to the Options tab >> Advanced
	3. Under Parallelism tab >> Max degree of parallelism (the default value = 0 )

	Or you can use query:

	SELECT * FROM sys.database_scoped_configurations