Right to Learn @BK
Right to Learn @BK
  • 100
  • 1 405 069
Transparent Data Encryption in SQL Server | TDE in SQL Server | Encryption in SQL | Ms SQL
Transparent Data Encryption (TDE) is a special case of encryption using a symmetric key. TDE encrypts entire database using a symmetric key called the database encryption key - DEK.
TDE does real-time I/O encryption and decryption of data and log files.
This encryption is known as encrypting data at rest. Introduced with SQL server 2008.
TDE isn't available for system databases. It can't be used to encrypt master, model, or msdb.
However, tempdb is automatically encrypted when a user database enabled TDE, but can't be encrypted directly.
TDE doesn't provide encryption across communication channels.
--Video link on how to encrypt a database backup
czcams.com/video/YFA0eWUmj7s/video.htmlsi=97XhUehg-Hr9bJzl
----SQL Script used in this video----
-- Encrypt a user database
--1.Create a Master key
use master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abc@123';
--2.Create a Certificate
CREATE CERTIFICATE democert WITH SUBJECT = 'my demo cert subject';
--3.Create a Database encryption key
use demo
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE democert
--4.Backup the certificate and key
use master
BACKUP CERTIFICATE democert
TO FILE = 'F:\dbbackups\democert_cert.cer'
WITH PRIVATE KEY (FILE = 'F:\dbbackups\democert_key.key' , ENCRYPTION BY PASSWORD = 'abc@1123')
--5.Set encryption ON for the database
ALTER DATABASE demo set ENCRYPTION on
select name, database_id, is_encrypted
from sys.databases
--------------------------------------------------------------------------
-- To restore a encrypted database --
restore database demo
from disk = 'F:\dbbackups\demo2.bak'
-- 1. Create a Master key
use master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xyz@123';
--2.Create a Certificate using the backup of source certificate & key
CREATE CERTIFICATE democert_pitr
FROM FILE = 'F:\dbbackups\democert_cert.cer'
WITH PRIVATE KEY (FILE = 'F:\dbbackups\democert_key.key',DECRYPTION BY PASSWORD = 'abc@1123')
------------------------------------------------------------------------
zhlédnutí: 1 560

Video

Contained database in SQL server | How to create a contained database | Ms SQL
zhlédnutí 1,7KPřed 6 měsíci
Contained databases feature is available at instance level and it is not enabled by default. If we want to create a new database as a contained database, we have to make containment type as Partial in the Options page. Contained database feature provides two containment modes: None - By default each database has its mode set as NONE. This means there is no contained database feature being used....
Resource database in SQL server | 5th System database in SQL server | SQL interview Q&A | Ms SQL
zhlédnutí 2,3KPřed 11 měsíci
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. Changing the location of the resource database is not supported or recommended. Scripts used in this video: SELECT * FROM sys.sysaltfiles WHERE DBID = 32767 To determine the...
How to Capture deadlock in SQL server | Trace deadlock | SQL interview Q&A | Ms SQL
zhlédnutí 7KPřed rokem
This video focus on how to capture a deadlock in SQL server. If you are a beginner then this video is for you. Please refer below video reference links that are discussed in this video. What is a deadlock and how it occurs czcams.com/video/f66ehM4Gn84/video.html How SQL server choses a deadlock victim czcams.com/video/junA5eB8Ifs/video.html T-SQL script to find How many deadlock occured since s...
How SQL server choose a Deadlock Victim | Deadlock Priority in SQL Server | SQL interview Q&A
zhlédnutí 4,1KPřed rokem
When an instance of SQL Server detects that two sessions are deadlocked, it resolves the deadlock by choosing one of the sessions as a deadlock victim and roll back its transaction (error 1205) and allows other session to move forward. By default, SQL server will choose the least expensive transaction as deadlock victim and roll back its transaction. Least expensive transaction is the one that ...
What is a Deadlock in SQL Server | How a deadlock occur in SQL Server with DEMO | SQL Interview Q&A
zhlédnutí 6KPřed rokem
Deadlock is a condition where two or more processes or transactions block each other from continuing because each has locked a database resource that the other transaction needs. SQL Server database engine comes with a deadlock monitor thread that will periodically check for deadlock situations and it will choose one of the process as a deadlock victim and rollback that process. After it rollba...
What is a Blocking in SQL Server | Find blocking and troubleshooting steps | SQL Interview Q&A
zhlédnutí 17KPřed rokem
Blocking is completely normal in SQL Server. You will typically be unaware of the many short blocks happening all the time. Sometimes, blocks take longer than expected to resolve. Blocks of longer duration can create chains, where a blocked process blocks additional processes and so on. This type of blocking scenario is problematic. The concern is not with blocking, but rather excessive blockin...
What are the different locking modes present in SQL | SQL Server Locking modes | SQL interview Q&A
zhlédnutí 12KPřed rokem
While objects are locked, SQL Server will prevent other transactions from making any change of data stored in objects affected by the imposed lock. Once the lock is released by committing the changes or by rolling back changes to initial state, other transactions will be allowed to make required data changes. In this video, you will learn about the different types of locks in a SQL server which...
Locking Resources in SQL Server | Lock type | SQL interview Q&A
zhlédnutí 6KPřed rokem
When a transaction imposes the lock on an object, all other transactions that require the access to that object will be forced to wait until the lock is released and that wait will be registered with the adequate wait type. SQL Server Database Engine can lock the following resources: RID: Used to lock a single row within a table. Key: Row lock within an index. It means primary key, Candidate Ke...
Locking in SQL Server | with DEMO | Why do we need Locking ? | SQL interview Q&A
zhlédnutí 14KPřed rokem
Locking is the way that SQL Server manages transaction concurrency and it ensures the integrity of the data in the database, as it forces every SQL Server transaction to pass the ACID test. Contents in this video: 1. What is Locking in SQL server 2. Locking hierarchy 3. Why do we need locking 4. Demo on how Locking occurs 5. Locking Resources and Modes of Locking ACID properties in SQL Server c...
ACID properties in SQL server | Atomicity, Consistency, Isolation and Durability | SQL interview Q&A
zhlédnutí 19KPřed rokem
A transaction in SQL Server is a group of SQL statements that are treated as a single unit and they are executed by applying the principle “do everything or do nothing” The acronym ACID was created by Andreas Reuter and Theo Harder in the year 1983, however, Jim Gray had already defined these properties in the late 1970’s. Most of the popular databases such as SQL Server, Oracle, MySQL, Postgre...
Log Shipping Tables | Monitor Log shipping using MSDB tables | SQL interview questions | Ms SQL
zhlédnutí 4,2KPřed rokem
In this video i have explained about Log shipping tables and how to monitor Log shipping using these tables. select * from msdb.dbo.log_shipping_primary_secondaries To check for secondary server instance select * from msdb.dbo.log_shipping_primary_databases select * from log_shipping_monitor_primary Look for last backup file select * from msdb.dbo.log_shipping_monitor_error_detail Showup detail...
What is a WRK file in SQL server | .wrk file in Log shipping | SQL interview questions | SQL DBA
zhlédnutí 3,9KPřed rokem
Have you ever seen a file with extension .wrk in the log shipping copy folder ?? This video explains what is a WRK file that exists in SQL server during Log Shipping configuration, its purpose\use. Explained with a DEMO. Please do watch. Twin questions .WRK file & .TUF file TUF file : czcams.com/video/6zI7fqumdVw/video.html Log Shipping : czcams.com/video/S1SaSJZtYzA/video.html
What is a TUF file in SQL server | .tuf file in Log shipping | SQL Interview questions | Ms SQL
zhlédnutí 8KPřed rokem
What is a .tuf file in SQL server ?? ::-This is one of the frequently asked interview question and it plays a important role in one of the database high availability and disaster recovery i.e., Log shipping. Transaction Undo File - contains transactions/modifications that were not committed on the source database but were in progress when the transaction log was backed up AND when the log was r...
How to delete duplicate rows in a SQL table | Delete and Avoid duplicate entries in SQL | Ms SQL
zhlédnutí 7KPřed 2 lety
One or more rows that have identical or same data values are considered to be Duplicate rows. Watch this video to delete duplicate entries in your SQL table and also how to aviod duplicate entries in a table Below are the scripts that are used in this video. create table Table1(id int,name varchar(10)) insert into Table1 values ('101','Prasad') insert into Table1 values ('101','Prasad') insert ...
Dynamic Data Masking in SQL server | Security feature in SQL server 2016 | Ms SQL
zhlédnutí 7KPřed 2 lety
Dynamic Data Masking in SQL server | Security feature in SQL server 2016 | Ms SQL
How to Export SQL table records using a Windows Batch file (.bat) | Ms SQL
zhlédnutí 18KPřed 2 lety
How to Export SQL table records using a Windows Batch file (.bat) | Ms SQL
How to export data from SQL server to Excel | Export table records to Excel | SQL DBA
zhlédnutí 84KPřed 2 lety
How to export data from SQL server to Excel | Export table records to Excel | SQL DBA
How to import data from excel to SQL server || Import excel data to SQL || Ms SQL
zhlédnutí 45KPřed 2 lety
How to import data from excel to SQL server || Import excel data to SQL || Ms SQL
How to restore deleted transactions in SQL server | PITR | Ms SQL
zhlédnutí 21KPřed 2 lety
How to restore deleted transactions in SQL server | PITR | Ms SQL
Point in time restore in SQL Server || How to restore a database to a specific point in time ||MsSQL
zhlédnutí 17KPřed 2 lety
Point in time restore in SQL Server || How to restore a database to a specific point in time ||MsSQL
How to take Database Schema Backup in SQL server || SQL Database Schema Backup || Ms SQL
zhlédnutí 7KPřed 2 lety
How to take Database Schema Backup in SQL server || SQL Database Schema Backup || Ms SQL
SQL Database Auto close property || Do we set it to ON/OFF ?? || Ms SQL
zhlédnutí 2KPřed 2 lety
SQL Database Auto close property || Do we set it to ON/OFF ?? || Ms SQL
Merge Replication in SQL Server || Part - 5 || Replication in SQL server
zhlédnutí 7KPřed 2 lety
Merge Replication in SQL Server || Part - 5 || Replication in SQL server
Replication in SQL server - Part 4 || Peer to Peer Replication || Ms SQL
zhlédnutí 10KPřed 2 lety
Replication in SQL server - Part 4 || Peer to Peer Replication || Ms SQL
Replication Error during Configuration || Steps to Troubleshoot Replication Error || SQL DBA
zhlédnutí 5KPřed 2 lety
Replication Error during Configuration || Steps to Troubleshoot Replication Error || SQL DBA
Troubleshoot Error in Transactional Replication || Record deleted at Subscriber || SQL DBA
zhlédnutí 10KPřed 2 lety
Troubleshoot Error in Transactional Replication || Record deleted at Subscriber || SQL DBA
Replication in SQL server - Part 3 || Transactional Replication in SQL server || Ms SQL
zhlédnutí 21KPřed 2 lety
Replication in SQL server - Part 3 || Transactional Replication in SQL server || Ms SQL
Replication in SQL server - Part 2 || Snapshot Replication in SQL server || Ms SQL
zhlédnutí 28KPřed 2 lety
Replication in SQL server - Part 2 || Snapshot Replication in SQL server || Ms SQL
Replication in SQL server - Part 1 || Terms used in Replication || Types of Replication || Ms SQL
zhlédnutí 43KPřed 2 lety
Replication in SQL server - Part 1 || Terms used in Replication || Types of Replication || Ms SQL

Komentáře

  • @rajulapatikirankumar

    sir is this full sql dba course or some more concepts are there?

  • @damodhardaamu9925
    @damodhardaamu9925 Před dnem

    Well explaining bro

  • @davidblair8603
    @davidblair8603 Před 4 dny

    All worked well, but after restoring the 'demo' database , the query reports is_encrypted=1 why would i get different result?

  • @bandraluyellappa213

    Thank you! This video is very useful for everyone. Can you please do video for always on setup as well. If it was already made, would you mind please share the link of it. Thanks Again!

  • @surajmochi6875
    @surajmochi6875 Před 8 dny

    Thanks man, you saved my day :)

  • @vaibhavsingh3247
    @vaibhavsingh3247 Před 9 dny

    Just like snowflake, does SQL server not support TimeTravel???? In Snowflake, one do not need to run a query to store a backup, instead we define a retention_time of table, and you can restore any number of days of historical data within retention_time defined.

  • @sqltechzpestechz7053
    @sqltechzpestechz7053 Před 12 dny

    I Have one doubt, when u restore the full backup with NO RECOVERY but u didnt restore the Log backup with NO RECOVERY, then how in join only mode it gets added, in my case it throws error that no latest log backup is there. once i restore the log with NO RECOVERY then it gets added using JOIN ONLY

  • @satish_sql5321
    @satish_sql5321 Před 12 dny

    In order to see .wrk file can we stop restore job for some period of time. will it works..?

  • @Ganeshay-996
    @Ganeshay-996 Před 12 dny

    2019 in not showing availability on group

  • @sqltechzpestechz7053
    @sqltechzpestechz7053 Před 13 dny

    thank u so much🥰

  • @Ganeshay-996
    @Ganeshay-996 Před 22 dny

    how to vm create and configuration with ms server

  • @aniketshelke9201
    @aniketshelke9201 Před 29 dny

    Thank you bro ❤

  • @harithad1757
    @harithad1757 Před 29 dny

    need session on performance tuning and also how to identify cpu and memory issues

  • @davidnunoo804
    @davidnunoo804 Před měsícem

    Great video! your videos are so detailed and well explained with practical examples.

  • @harithad1757
    @harithad1757 Před měsícem

    Amazing

  • @Ganeshay-996
    @Ganeshay-996 Před měsícem

    how to connect network to linux vm if want sharing like linux

  • @Ganeshay-996
    @Ganeshay-996 Před měsícem

    good job brother and more vdo plz update every topic

  • @michelleurbano0809
    @michelleurbano0809 Před měsícem

    Thank you for the vid. Very helpful but is the modify file to a new location also applies to the file inside in a filegroup? Or you need to modify filegroup syntax then just change the filepath of the data file there? Or is there any extra step? When changing the location of the database file inside a filegroup

  • @AmanuelIctsolution-ro1oc
    @AmanuelIctsolution-ro1oc Před měsícem

    Thanks

  • @shivaselvan8858
    @shivaselvan8858 Před měsícem

    Excellent work! Keep rocking!

  • @meghana4284
    @meghana4284 Před měsícem

    You explained the concepts very clearly. All your videos are very informative

  • @sagarkoltharkar2656
    @sagarkoltharkar2656 Před měsícem

    Excellent

  • @Ganeshay-996
    @Ganeshay-996 Před měsícem

    but getting error i am unable to access another instance

  • @satishchowdary1390
    @satishchowdary1390 Před měsícem

    Good way of explanation very useful

  • @satishchowdary1390
    @satishchowdary1390 Před měsícem

    Great learning

  • @azlannohara1208
    @azlannohara1208 Před měsícem

    Can we export with the filename follow the current date?

  • @murghachronicles715
    @murghachronicles715 Před měsícem

    How did you create Windows Failover Cluster? Is this a separate machine or is this some kind of feature which needs to be enabled on both SQL Server nodes?

  • @bennyrokkam8753
    @bennyrokkam8753 Před měsícem

    THANKS FOR THE VIDEOS

  • @harishsagar344
    @harishsagar344 Před měsícem

    Thanks for keeping video it’s very Detailed

  • @egorkosenkov4476
    @egorkosenkov4476 Před měsícem

    Thank you very much!!! Trofimovich is going to be satisfied =))))))))))))))))

  • @SudarshanReddy897
    @SudarshanReddy897 Před měsícem

    great video..have one doubt that if we want to failover multiple databases just say like 20db's, how can we do at a time?

  • @anthonycurreri1543
    @anthonycurreri1543 Před 2 měsíci

    Good video, one correction, The Standard Edition of SQL server has basic availability groups included. That limits you to 1 database per group but you can have an unlimited number of groups.

  • @srimahesh5352
    @srimahesh5352 Před 2 měsíci

    Nice explanation but while we tired I got an error

  • @aakashlko92
    @aakashlko92 Před 2 měsíci

    Can you please explain the step of creating master key with strong password? What is exactly this step do at backend of architecture?

  • @seliii3.__
    @seliii3.__ Před 2 měsíci

    Thank U <3

  • @Virustech1999
    @Virustech1999 Před 2 měsíci

    Hii sir can u I aapka distribution database se error hatane wala vidoe dekh ke to aaj mera error solve ho gya because aaj subh se mai replication ka error leke baitha tha my manager totally fired on me but your video are save me bus ek problem hai uska bhi video bna dijiye please mera next month server migration hai aur mera subscriber database new server per move hone ja rha hai to jab hum new server per subscriber ka db restore krenge n publisher se replication bnayenge to wo publisher ka data replicate kr dega jabki publisher me mera do not delete statement hai to sara data subscriber per rhta hai aur agr migration ke time replication break krke phir se banaunga to table ka data delete kr dega to phir se publisher ka data hi rhega I want video my subscriber data are still in the table replicate only thoes data which have new on publisher table n not in subscriber

  • @gouthami_pittala
    @gouthami_pittala Před 2 měsíci

    How to get to know travel is on or off

  • @tusharkaushik79
    @tusharkaushik79 Před 2 měsíci

    Message The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2024-05-11T16:20:45). Exception Message: Could not connect to mail server. (No such host is known).

  • @tahirhanyildizoglu1461
    @tahirhanyildizoglu1461 Před 2 měsíci

    thank you for the practical information

  • @cvaz000
    @cvaz000 Před 2 měsíci

    Thank you! Helped me a lot!

  • @HiranthaIddamalgoda
    @HiranthaIddamalgoda Před 2 měsíci

    Nice. Well explained. Thank you

  • @RohitKumar-cg9ns
    @RohitKumar-cg9ns Před 2 měsíci

    Plz tell how to import excel file qsl severe

  • @oyegolii5039
    @oyegolii5039 Před 2 měsíci

    how to check which name is avilable in window level for creating a new login

  • @naveen_nanda
    @naveen_nanda Před 2 měsíci

    Big fan anna.... Big fan💗💗💗

  • @richardwaldron1684
    @richardwaldron1684 Před 2 měsíci

    Excellent demo, I finally understand their purpose and function, including how ti do a restore. Thanks for sharing

  • @saikumarkakarla9924
    @saikumarkakarla9924 Před 2 měsíci

    Hi bro How are you. Thanks for come back after long days🤝. Very thanks for this concept . Very easy to understood.

  • @BN-ys1sj
    @BN-ys1sj Před 2 měsíci

    Hi sir your explanations are very good. I appreciate you covering everything that a person would use.

  • @michelleurbano0809
    @michelleurbano0809 Před 2 měsíci

    Hi , great video with precise elaboration! In the first solution, you have deleted the record from publisher after deleting the record from subscriber because the publisher wont recognize the deletion from the subscriber thats why you have to re-add it on the publisher. Am I getting it right?

  • @skyp6051
    @skyp6051 Před 2 měsíci

    Thank you sir

  • @learncodingintamil-python8774

    Hi bro , i have configured a named instance with all tcp connection enabled with port number , however i cant able to connect to the instance in remote machine , i canable to connect in local machine. Why?