Transparent Data Encryption

PRIMARY REPLICA

STEP 1

USE MASTER 
BACKUP DATABASE DEMO TO DISK = 'E:\BACKUP\DEMO.Bak' 
GO 
BACKUP LOG DEMO TO DISK = 'E:\BACKUP\DEMO.trn' 

STEP 2

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TESTDBPASSWORD' 

--SELECT name KeyName,  symmetric_key_id KeyID,  key_length KeyLength, 

--algorithm_desc KeyAlgorithm,create_date FROM sys.symmetric_keys;

STEP 3

CREATE CERTIFICATE TDECert WITH SUBJECT = 'My TDE Certificate for all user database'

--SELECT name CertName,   certificate_id CertID,   pvt_key_encryption_type_desc EncryptType, 

--issuer_name Issuer,start_date,expiry_date FROM sys.certificates 

STEP 4

USE [DEMO]

GO  

CREATE DATABASE ENCRYPTION KEY  

WITH ALGORITHM = AES_128  

ENCRYPTION BY SERVER CERTIFICATE TDECert 

GO 

SELECT DB_NAME(database_id) DbName,   /*CASE CONVERT(int,[encryption_state]) 

    WHEN 0 THEN 'no encryption'     WHEN 1 THEN 'Unencrypted' 

WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' 

WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' 

WHEN 6 THEN 'Protection change in progress'     ELSE encryption_state 

END as [EncryptState]*/ encryption_state EncryptState,   key_algorithm KeyAlgorithm,   key_length KeyLength, 

encryptor_type EncryptType,percent_complete FROM sys.dm_database_encryption_keys; 

STEP 5

   USE DEMO

   ALTER DATABASE [DEMO] SET ENCRYPTION ON

STEP 6

USE MASTER  

GO  

BACKUP CERTIFICATE TDECert   

TO FILE = 'E:\BACKUP\TDECert_File.cer'  

WITH PRIVATE KEY (FILE = 'E:\BACKUP\TDECert_Key.pvk' ,  

ENCRYPTION BY PASSWORD = 'TESTDBPASSWORD' )  

GO

BACKUP DATABASE DEMO TO DISK = 'E:\BACKUP\DEMOTDE.Bak' 

GO 

BACKUP LOG DEMO TO DISK = 'E:\BACKUP\DEMOTDE.trn' 

GO 

==============Secondary replica,

USE MASTER  

GO  

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TESTDBPASSWORD' 

GO  

CREATE CERTIFICATE TDECert  

FROM FILE = 'E:\BACKUP\TDECert_File.cer'   

WITH PRIVATE KEY (FILE = 'E:\BACKUP\TDECert_Key.pvk',   

DECRYPTION BY PASSWORD =  'TESTDBPASSWORD'

GO

ALTER DATABASE [DEMO] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [DEMO] FROM  DISK = N'E:\BACKUP\DEMOTDE.Bak' 

WITH  FILE = 1,  MOVE N'DEMO' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SECOND\MSSQL\DATA\DEMO.mdf', 

MOVE N'DEMO_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SECOND\MSSQL\DATA\DEMO_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5

ALTER DATABASE [DEMO] SET MULTI_USER

GO

=============ROLLBACK

USE DEMO

ALTER DATABASE [DEMO] SET ENCRYPTION ON

DROP DATABASE ENCRYPTION KEY

USE MASTER

DROP CERTIFICATE TDECertificate

DROP MASTER KEY

sqlauthority

https://ittutorial.org/sql-server-dba-scripts-all-in-one-useful-database-administration-scripts/

Stored Procedure – Clean Cache and Clean Buffer

DBCC FREEPROCCACHE (ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE)

DBCC DROPCLEANBUFFERS

sys.dm_os_buffer_descriptors returns cached pages for all user and system databases

DBCC Commands can be ran to free memory:

• DBCC FREESYSTEMCACHE
• DBCC FREESESSIONCACHE
• DBCC FREEPROCCACHE

DBCC FREESYSTEMCACHE:

Releases all unused cache entries from all caches.

DBCC FREESESSIONCACHE
Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

USE database_name

CHECKPOINT;
DBCC shrinkfile (logfilename, 1)

USE AdventureWorks
GO
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = AdventureWorks_Log,SIZE = 1GB)
GO
DBCC LOGINFO
GO

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.

DBCC CHECKIDENT (yourtable, reseed, 34)

Call your Stored Procedure prefixed with dbo.SPName – fully qualified name.
When SP are called prefixed with dbo. or database.dbo. it will prevent SQL Server from placing a COMPILE lock on the procedure. While SP executes it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.

PATINDEX is CHARINDEX + WildCard Search. Use either of them depending your need.\\\

·         Set a reasonable size of your database and transaction log (25% of database size).

·         Leave the Autogrow feature ON for the data files and for the log files with reasonable size of autogrow increment.

·         Don’t set the autoshrink feature run the task during off-peak hours.

·         Place the log files on other physical disk arrays than those with the data files to improve I/O Performance.

ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK AFTER 30 SECONDS
OR
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE

RAID (redundant array of independent disks) levels 0, 1, and 5 are typically implemented with SQL Server.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190764(v=sql.105)

• Use SORT_IN_TEMPDB option when the table is created if tempdb is on different disks. This will increase the performance to create the Index.
• Rebuild Index frequently using ALTER INDEX and De-fragment Index to keep performance optimal for Indexes.

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE AdventureWorks
FROM DISK = 'C\:BackupAdventureworks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Data\datafile.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\logfile.ldf',
REPLACE

USE pubs
GO
SELECT a.au_id
FROM authors a
JOIN titleauthor b ON a.au_id = b.au_id
OPTION (MERGE JOIN)
GO

https://blog.sqlauthority.com/2007/04/28/sql-server-good-better-and-best-programming-techniques/

DBAs are creating all the tables with no Clustered Index.

USE AdventureWorks ----Replace AdventureWorks with your DBName
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE] GO

sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

sp_configure 'fill factor', 90

GO

RECONFIGURE

GO

EXEC sys.sp_configure 'show advanced options', '1'

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure 'fill factor (%)', '90'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure 'show advanced options', '0'

RECONFIGURE WITH OVERRIDE

GO

Disable Nesting/Recursing Triggers
Following script will stop executing all the nested triggers.
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

There is also alternate way to stop Trigger Recursion

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Restrict Trigger Nesting to certain level
Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.
IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

USE AdventureWorks
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO
----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO

SQL SERVER 2005 Syntax
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
Example:
The following example sets the deadlock priority to NORMAL.
SET DEADLOCK_PRIORITY NORMAL;
GO

https://blog.sqlauthority.com/2007/07/02/sql-server-2005-comparison-sp_executesql-vs-executeexec/

Best Practices and Recommendations for TempDB in SQL Server.
Analyze the existing workload and adjust the space for projected concurrent activities in SQL TempDB.
SQL Server does not cache the temp table if it is created as part of dynamic SQL.
Perform index maintenance and update statistics on TempDB as well, even thought objects in TempDB are not permanent.
Set Auto Grow to ON for TempDB.
Instant file initialization improves the performance of auto grow operations in TempDB.
Create TempDB database on other disk drive than other database. This will improve performance for database as different disk controller will improve the performance for disk input/output.

SQL SERVER 2000:
DBCC SHOWCONTIG was used to find index fragmentation. In SQL SERVER 2005 it is deprecated and replaced by query to sys.DM_DB_INDEX_PHYSICAL_STATS.

SQL SERVER 2005:
SELECT query to sys.DM_DB_INDEX_PHYSICAL_STATS displays all the Index Fragmentation related information.

To return the Index Information for only Sales.SalesOrderDetail Table:
USE AdventureWorks;
SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID(N'Sales.SalesOrderDetail'), NULL, NULL, 'DETAILED')
To return all the information for all the Indexes in Database:
SELECT *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

SQL SERVER 2000:
The DBCC INDEXDEFRAG and DBCC DBREINDEX statements are used to reduce table fragmentation.

To Rebuild only one Index:
USE AdventureWorks
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID
ON Production.ProductPhoto
REORGANIZE
GO

TO Rebuild all the Indexes on Table with Specifying options:
USE AdventureWorks
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO

T-SQL to rebuild all the indexes on table.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

https://blog.sqlauthority.com/2007/11/04/sqlauthority-news-best-articles-on-sqlauthoritycom/

-- Change database Parameterization to Forced
ALTER DATABASE AdventureWorks
SET PARAMETERIZATION FORCED
GO
-- Change database Parameterization to Simple
ALTER DATABASE AdventureWorks
SET PARAMETERIZATION Simple
GO

https://blog.sqlauthority.com/2008/02/13/sql-server-introduction-to-three-important-performance-counters/

Find Unused Indexes of Current Database

USE AdventureWorks
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO

Display Fragmentation Information of Data and Indexes of Database Table

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20

LOG SHIPPING vs MIRRORING vs REPLICATION

Log Shipping::

It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled.

Mirroring::

Database mirroring is a primarily software solution for increasing database availability.
It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.

Replication::

It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Components

Log Shipping::Primary server, secondary server and monitor server (Optional).
Mirroring::Principal server, mirror server, and witness server (Optional).
Replication::Publisher, Subscribers, Distributor (Optional).

Data Transfer

Log Shipping::T-Logs are backed up and transferred to secondary server.
Mirroring::Individual T-Log records are transferred using TCP endpoints.
Replication::Replication works by tracking/detecting changes (either by triggers or by scanning the log) and shipping the changes.

Server Limitation

Log Shipping::It can be configured as One to Many. i.e one primary server and many secondary servers. Or
Secondary server can contain multiple Primary databases that are log shipped from multiple servers.
Mirroring::It is one to one. i.e. One principal server to one mirror server.
Replication::

§  Central publisher/distributor, multiple subscribers.

§  Central Distributor, multiple publishers, multiple subscribers.

§  Central Distributer, multiple publishers, single subscriber.

§  Mixed Topology.

Types Of Failover

Log Shipping::Manual.
Mirroring::Automatic or manual.
Replication::Manual.

DB Access

Log Shipping::You can use a secondary database for reporting purposes when the secondary database restore in STANDBY mode.
Mirroring::Mirrored DB can only be accessed using snapshot DB.
Replication::The Subscriber Database is open to reads and writes.

Recovery Model

Log Shipping::Log shipping supports both Bulk Logged Recovery Model and Full Recovery Model.
Mirroring::Mirroring supports only Full Recovery model.
Replication::It supports Full Recovery model.

Restoring State

Log Shipping::The restore can be completed using either the NORECOVERY or STANDBY option.
Mirroring::The restore can be completed using with NORECOVERY.
Replication::The restore can be completed using With RECOVERY.

Backup/Restore

Log Shipping::This can be done manually or
through Log Shipping options.
Mirroring::User make backup & Restore manually.
Replication::User create an empty database with the same name.

Monitor/
Distributer/ Witness

Log Shipping::The monitor server should be on a server separate from the primary or secondary servers to avoid losing critical information and disrupting monitoring if the primary or secondary server is lost. . If a monitor server is not used, alert jobs are created locally on the primary server instance and each secondary server instance.
Mirroring::Principal server can’t act as both principal server and witness server.
Replication::Publisher can be also distributer.

Types Of Servers

Log Shipping::All servers should be SQL Server.
Mirroring::All servers should be SQL Server.
Replication::Publisher can be ORACLE Server.

SQL Server Agent Dependency/Jobs

Log Shipping::Yes. Log shipping involves four jobs, which are handled by dedicated SQL Server Agent jobs. These jobs include the backup job, copy job, restore job, and alert job.
Mirroring::Independent on SQL Server agent.
Replication::Yes. Snapshot agent, log reader agent & Distribution agent (transactional replication)
Merge agent (merge replication).

Requirements

Log Shipping::

§  The servers involved in log shipping should have the same logical design and collation setting.

§  The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.

§  The SQL server agent should be configured to start up automatically.

§  You must have sysadmin privileges on each computer running SQL server to configure log shipping.

Mirroring::

§  Verify that there are no differences in system collation settings between the principal and mirror servers.

§  Verify that the local windows groups and SQL Server logins definitions are the same on both servers.

§  Verify that external software components are installed on both the principal and the mirror servers.

§  Verify that the SQL Server software version is the same on both servers.

§  Verify that global assemblies are deployed on both the principal and mirror server.

§  Verify that for the certificates and keys used to access external resources, authentication and encryption match on the principal and mirror server.

Replication::

§  Verify that there are no differences in system collation settings between the servers.

§  Verify that the local windows groups and SQL Server Login definitions are the same on both servers.

§  Verify that external software components are installed on both servers.

§  Verify that CLR assemblies deployed on the publisher are also deployed on the subscriber.

§  Verify that SQL agent jobs and alerts are present on the subscriber server, if these are required.

§  Verify that for the certificates and keys used to access external resources, authentication and encryption match on the publisher and subscriber server.

Using With Other Features Or Components

Log Shipping::Log shipping can be used with Database mirroring, Replication.
Mirroring::Database mirroring can be used with
Log shipping, Database snapshots , Replication.
Replication::Replication can be used with log shipping, database mirroring.

DDL Operations

Log Shipping::DDL changes are applied automatically.
Mirroring::DDL changes are applied automatically.
Replication::only DML changes to the tables you have published will be replicated.

Database Limit

Log Shipping::No limit.
Mirroring::generally good to have 10 DB’s for one server.
Replication::No limit.

latency

Log Shipping::There will be data transfer latency. >1min.
Mirroring::There will not be data transfer latency.
Replication::Potentially as low as a few seconds.

Committed /
Uncommitted
Transactions

Log Shipping::Both committed and uncommitted transactions are transferred to the secondary database.
Mirroring::Only committed transactions are transferred to the mirror database.
Replication::Only committed transactions are transferred to the subscriber database.

Primary key

Log Shipping::Not required.
Mirroring::Not required.
Replication::All replicated table should have Primary Key.

New Created Database&
Stored Procedure

Log Shipping::Monitoring and history information is stored in tables in msdb, which can be accessed using log shipping stored procedures.
Replication::Creates new SPs ( 3 Sps of one table).
Distribution Database.
Rowguid column will be created.

Individual Articles

Log Shipping::No. Whole database must be selected.
Mirroring::No. Whole database must be selected.
Replication::Yes. Including tables, views, stored procedures, and other objects. Also filter can be used to restrict the columns and rows of the data sent to subscribers.

FILESTREAM

Log Shipping::Log shipping supports FILESTREAM.
Mirroring::Mirroring does not support FILESTREAM.
Replication::Replication supports FILESTREAM.

DB Name

Log Shipping::The secondary database can be either the same name as primary database or it may be another name.
Mirroring::It must be the same name.
Replication::It must be the same name.

DB Availability

Log Shipping::In case of standby mode: read only database.
In case of restoring with no recovery: Restoring state.
Mirroring::In Recovery state, no user can make any operation.
You can take snapshot.
Replication::Snapshot (read-only).
Other types (Database are available).

Warm/ Hot Standby Solution

Log Shipping::It provides a warm standby solution that has multiple copies of a database and require a manual failover.
Mirroring::When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).
Replication::It provides a warm standby solution that has multiple copies of a database and require a manual failover.

System Data Transferred

Log Shipping::Mostly.
Mirroring::Yes.
Replication::No.

System Databases

Mirroring::You cannot mirror the Master, msdb, tempdb, or model databases.

Mode Or Types

Log Shipping::

§  Standby mode (read-only)-you can disconnect users when restoring backups .

§  No recovery mode (restoring state)-user cannot access the secondary database.

Mirroring::

§  high-safety mode supports synchronous operation.

§  high-performance mode, runs asynchronously.

§  High-safety mode with automatic failover.

Replication::

§  Snapshot replication.

§  Transactional replication.

§  Transactional publication with updatable subscriptions.

§  Merge publication.

§  Pull/Push subscription.