What are the differences between Database Migration Assistant (DMA) and Database Experimentation Assistant (DEA)?

S. No	Data Migration Assistant	Database Experimentation Assistant
1	Data Migration Assistants assists you to migrate the database from lower version to higher version	Database Experimentation Assistant is new experimentation solution for SQL server upgrades.
2	It is used for performing the assessment and migration of SQL server databases.	It captures the profiler trace on the source database (Prod) and replays it on the destination servers (NonProd).
3	Two project types are supported: -          Assessment (Reports on compatibility issues and feature recommendations) -          Migration (It provides the option to migrate the database with logins to target server)	Three options are currently supported: -          Capture a workload on the source server -          Replay the captured workload on target1 and target 2 -          Analyze replayed traces collected from target 1 and target 2
4	Only option is to store the results as (.json or .csv) format	The results can be stored in the database.

Drop Orphan Users On All User databases

Below query will get the information from all the user databases with no mapped logins.

       DECLARE @sql varchar(2000),
               @dbname varchar(64)

       --IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
   CREATE TABLE tempdb.dbo.#temp(
   [Database Name] VARCHAR(30),
   Name VARCHAR(20),
   [Type] VARCHAR(1),
   [Type Description] VARCHAR(20),
   [SID] VARBINARY(85),
   [SID Length] INT
   )
   
       DECLARE orphaned_users CURSOR FOR 
   SELECT d.name
   FROM  sys.databases d
   WHERE d.database_id >4 

       OPEN orphaned_users
       FETCH NEXT FROM orphaned_users INTO @dbname 
       WHILE @@FETCH_STATUS <> -1
       BEGIN

        SELECT @sql ='USE [@dbname]
INSERT INTO tempdb.dbo.#temp  ([Database Name],Name,[Type],[Type Description],[SID],[SID Length])
        SELECT  
DB_NAME() As ''Database Name'',
dp.name AS ''Name'', 
        dp.[type] AS ''Type'',
CASE WHEN dp.[type]=''U'' THEN ''WINDOWS USER''
     WHEN dp.[type]=''S'' THEN ''SQL USER''
WHEN dp.[type]=''C'' THEN ''USER MAPPED TO A CERTIFICATE''
WHEN dp.[type]=''K'' THEN ''USER MAPPED TO AN ASSYMETRIC KEY''
    END AS ''Type Description'',
dp.[sid] AS ''SID'',    
LEN(dp.[sid]) AS [SID Length] 
        FROM sys.database_principals dp
        LEFT JOIN sys.server_principals sp
        ON dp.[sid] = sp.[sid]
        LEFT JOIN sys.certificates c
        ON dp.[sid] = c.[sid]
        LEFT JOIN sys.asymmetric_keys a
        ON dp.[sid] = a.[sid]
        WHERE sp.[sid] is null and c.[sid] is null and a.[sid] is null
        AND dp.[type] in (''U'', ''S'', ''C'', ''K'') 
        AND dp.principal_id > 4 -- 0..4 are system users which will be ignored
        AND NOT (dp.[type] = ''S'' and LEN(dp.[sid]) = 28)'

SELECT @sql = REPLACE(@sql,'@dbname',@dbname)

--PRINT (@sql)
        EXEC  (@sql)

        FETCH NEXT FROM orphaned_users into @dbname
        END
        CLOSE orphaned_users
        DEALLOCATE orphaned_users

    SELECT 'USE '+[Database Name]+CHAR(13)+'DROP USER '+QUOTENAME(Name)+' ;' AS 'Drop                 Orphan Users' FROM #temp

DROP TABLE #temp 

How to find the Principal Server from Mirror Server in Database Mirroring?

There are instances where you may need to find the principal server from the mirror server.

You can use the below query:

SELECT DISTINCT(mirroring_partner_instance)
FROM sys.database_mirroring
WHERE mirroring_partner_instance IS NOT NULL

Add Transparent Database Encryption (TDE) - All User Databases

Applying Transparent database encryption is one of the important tasks which we need to do across the enterprise level databases.

Below script will apply the encryption on all user databases in a particular instance. The certificate need to be created on all secondary databases in an Always-On environment.

USE [master]

--Create Master Key

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Azdb_pRd@2017';

--Create Certificate  
 
CREATE CERTIFICATE AZ_TDE_PRD
WITH SUBJECT='AZ_TDE_ON_PRD';
GO

--Create Database Encryption Key on each user databases

DECLARE @sql varchar(2000),
        @dbname varchar(64)
  
DECLARE apply_tde CURSOR FOR
   SELECT d.name
   FROM  sys.databases d
   WHERE d.database_id >4

   OPEN apply_tde
       FETCH NEXT FROM apply_tde INTO @dbname
       WHILE @@FETCH_STATUS <> -1
       BEGIN

            SELECT @sql ='USE [@dbname]
            CREATE DATABASE ENCRYPTION KEY
            WITH ALGORITHM = AES_128
            ENCRYPTION BY SERVER CERTIFICATE AZ_TDE_PRD'

            SELECT @sql = REPLACE(@sql,'@dbname',@dbname)
            PRINT  'Encryption Started --'+@sql
            EXEC  (@sql)
        
            SELECT @sql = 'USE [master] ALTER DATABASE [@dbname] SET ENCRYPTION ON'
            SELECT @sql = REPLACE(@sql,'@dbname',@dbname)
            PRINT  'Encryption Finished --'+@sql
            EXEC  (@sql)

        FETCH NEXT FROM apply_tde into @dbname
        END
   CLOSE apply_tde
   DEALLOCATE apply_tde


BACKUP CERTIFICATE AZ_TDE_PRD
TO FILE = 'C:\temp\AZ_TDE_PRD'
WITH PRIVATE KEY (file='C:\temp\AZ_TDE_PRD_PVT',
ENCRYPTION BY PASSWORD='Cer#Azdb_pRd@2017')

GO

SELECT
     DB_NAME(database_id) AS 'Database Name'
    ,create_date AS 'Create Date'
    ,set_date AS 'Set Date'
    ,(CASE
        WHEN Encryption_State='0' THEN 'No Database Encryption'
        WHEN Encryption_State='1' THEN 'Unencrypted'
        WHEN Encryption_State='2' THEN 'Encryption In Progress'
        WHEN Encryption_State='3' THEN 'Encrypted'
        WHEN Encryption_State='4' THEN 'Key Change In Progress'
        WHEN Encryption_State='5' THEN 'Decryption In Progress'
        WHEN Encryption_State='6' THEN 'Protection Change In Progress'
      END) AS 'Encryption State'
    ,key_algorithm AS 'Algorithm Used'
    ,key_length AS 'Key Length'
    ,encryptor_thumbprint AS 'Encryptor Thumbprint'
    ,percent_complete AS 'Percent Complete'
    ,encryptor_type AS 'Encryptor Type'
FROM sys.dm_database_encryption_keys
GO

After executing the above script, you need to copy the certificate to the secondary server in an always-on environment and create the certificate in a secondary server.  

This is also applicable if you are implementing the transparent database encryption in production and restoring it in test/development environment. 

CREATE CERTIFICATE AZ_TDE_PRD
FROM FILE='C:\temp\AZ_TDE_PRD'
WITH PRIVATE KEY (
FILE = 'C:\temp\AZ_TDE_PRD_PVT',
DECRYPTION BY PASSWORD='Cer#Azdb_pRd@2017')


References:

sys-dm-database-encryption-keys
transparent-data-encryption