What is SQL Server?
Net Library - TCP/IP Protocol Network protocol we use driver like ODBC, OLEDB that driven call Net Library part.
- SQL Server (RDBMS) of Microsoft is a windows plateform which ensure the concurrency and a transactions.
- Various SQL Server developer performs the basic ACID pricipal to maintain dignity and accuracy of a transactions.
What is ACID Principal?
- As we know the in every transactions developer ensure the transaction should be complete and there is no breach of a data dependent on another transactions these basic rule simple called ACID principal.
Atomicity confirm the transaction either all or non will execute.
Consistency ensure in any transactions can't leave your database in half of the state.
Isolation ensure each transactions are independent.
Durability ensure in database will keep record of pending changes in such of way server can recover from an abnormal termination.
|
OLEDB (Driver) |
ODBMS (Driver) |
|
Support hierarchical and relational both such as Excel, XML. |
Support only relational data such as RDBMS. |
SQL Server Diagram
UMS (User Manual Scheduler) - UMS through which SQL control the execution of thread through his own level before submitting to the operating system. Ultimately thread execute in the CPU which control by operating system, first control by SQL UMS (User Manual Schedulaer).
Relational Engine and Storage Engine - If you submit the query syntax checked then tokenised there it will convert to the machine language before submitting to the Storage Engine to receive the data.
There Major Sub Component are :-
1. Parser
2. TSQL
3. Optimizer
Parser - As per name passing the syntax checking the semantic of the query other compiler compile the query and generate execution plan of a query.
OLEDB and Non OLEDB Communication- OLEDB and Non OLEDB communication depends to which driver call has made to connect SQL and execute the query, Once the query execute and execution plan generated and planned submmited to the storage engine to pull the data and also maintain the concurrency, durability, transactions which the driver will be modified.
Transaction Manager - As we know transaction manager will maintain logging and recovering of transaction manager in case of undo and data be see it maintain here.
Lock Manager - Ensure the concurrency apply the logs revoke then back up data will be pull from particular rows and active columns.
Utilities :
1. Bulk Load
2. DBCC (Database Console Command)
3. Backup
4. Restore
5. VDI (Virtual Backup Device Interface) - VDI call when we backup data by 3rd party tools, manipulate data in requested format.
Page Manager/ Text Manager/ Buffer Manager/ Log Manager/ Sort Manager these all just manipulate data and gives to the user in requested format.
Win32 API - I/O Manager row call to Win32 API, through I/O Manager to fetch the data then bring the data to the memory for modification is required.
SQL Server we install an instance and we can install many instances on single server.
One instance on dedicated SQL Server box for one instance that instance have multiple database first be connecting the instance where we want under on instance.
When we install SQL Server instance then certain system database installed by default and whatever we want to create application database we will create.
We Specified at one data file and log file. Data file hold tables and indexes data and log file holds transaction information which is automatically harded back to data file based on certain algorithem.
The Log File is very important in case of disaster, data corruption something like this which we take latest data to pointing time for recovery.
- Primary Data (*.mdf)
- Secondary Data (*.ndf)
- Transaction Data (*.ldf)
Note - Actually in SQL Server these data extensions doesn't matter it only for developers for easily identification.
File Group - Logical collection of files by defaulters all data files reside under primary file group but again depending on data type and what kind of data be hold like XML, nText something we can put it different file group for easy maintenance and performance prospective.
A part from different file group we have multiple file group logically segregate put in those files first storage as per our maintenance purpose.
When we are creating procedure we can specified particular file group to them to which we want to reside.
- SQL Server Network Integration
Network oriented protocols TCP/IP (Ideally we go most common protocol)
NP (Network Protocol) (Hardware based protocol)
VIA
Note :- TCP/IP disabled in SQL Server express by default due to security reason because SQL Server express are used for small application within server itself.
Within SQL Server Box trying to connect SQL Server instance mechanism called Shared Memory.
- DNS (Domain Name Specified)
SQL Server Authentication -
1. Window Authentication - User login without authentication.
2. SQL Server Authentication - At the time of installing SQL Server we provide user credential that is stored within SQL Server and it will connect using the same.
SQL Server Version and Editions -
Most popular SQL Server is 2000 series earlier SQL Server was a single user instance mostly like desktop. Multiple client architecture in SQL Server 2000 series.
- SQL Server 2012 (SP3)
- SQL Server 2014 (SP2)
- SQL Server 2016 (SP3)
- SQL Server 2017
- SQL Server 2019
SQL Server Editions:-
Enterprises :- Almost all features are available
Developer :- 70% of Enterprise features are available but only install in developer box not in production box.
Web - Encryption related features not available, Database Mirroring is not available.
Express - More limitation might very good for small application.
Note: Before using editions, We have to understand project requirement, first to be outline the features and category application is using.
Security - Login insatance/ Server level security.
Object Permission Access - Application/Database rules through which permission is granted and revoked and controlled.
Encryption-
1. TDE (Transparent Data Encryption) - TDE implemented in SQL Server in several way SQL Server offers native support also, So using certificate amd all be encrypt our SQL Server instance for the connection from client to server either one way and both way.
2. Always Encrypted (available 2015)
TDE means data in motion is encrypted but data in residing files is not encrypted.
Always Encrypted the data residing the data file is also encrypted.
Always Encrypt the data residing those data files is also Encrypted.
Centralized Account :
Auditing:
Certain auditing could be enabled by traces, there sometimes called C2 auditing enabled but auditing has a trade of on performance.
SQL Server admin tools -
SSMS Primary tools through which connect to the instance, database we have lot GUI options available to manage those objects, tables, index, logins, users, SQL Server job data collectors.
Note: Starting 2005 it was based on Visual Studio IDE, Starting 2012, 2014 whenever you launch SSMS you might be completely controlled by Visual Studio plugins and interfaces within.
SSMS itself you can execute query look at the execution plan bunch of other stuffs done.
-SQL Server Profiler - Very powerful tools this primary tool to capture traces, capture the data in order to see data behind the scene, database engine which kind of query are being executed, have that completed or not, execution plan, duration was any blocking, deadlock lot of things could be check from SQL Server profiler.
DET (Database Engine Tuning) - DET also invoked from SQL Server (SSMS) under tool menu.
Database Engine Tuning advisor frequently used for submitting queries to see the index recommendation by SQL Server engine against the queries that we have submitting and then make a decision to create those indexes all of them or some of them.
SQL Server Configuration which manage services, protocols TCP/IP, NP, VIA, protocol they call could be seen within SQL Server configuration manage this install as a program.
Go To Start - All Program (there you can find it)
Perfmon also call Performance monitor or Windows System monitor that actually a Window level tool of windows which you can see various performance, matrix of all the processes which is running on Windows but we can see about SQL Server prospective, We can see lot of details about various counters and matrix of SQL Server instances which are running and windows using Perfmon.
SQL Diaz - SQL Diaz is a native tool of SQL Server which shit with SQL Server while installation. So when we install SQL Server this will go into your tools bin folder this SQL Diaz is basically data collection tools which will capture various data including all SQL Server error log and dumb files. Performance related data which is captured by Perfmon that also capture by SQL Diaz alone. The Trace is that is captured by SQL Profile that also captured by SQL Diaz itself. Basically its a tool which capture all window and SQL level of data depending how you let it capture if troubleshooting now problem where you don't have much information then this tool can be used wide range of data so we can further drill down for the actual root cause.
SQL Server side trace trace basically not GUI basically TCP script which keep on running background so its really reduce overhead compare to GUI tools which is SQL Profiler.
SQL Profiler Event Data -
SP: Starting
SP: Completed
SP: Recompile
SP : STMT Completed
SP: STMT Starting
-Cursor Events -
Cursor Open
Cursor Exceute
Cursor Close
DMF's (Dynamic Management Functions)
- Lots of DMV'S (Dynamics Management Views) Bunch of DMV'S, DMF'S available in SQL Server to give us very detailed Views of query which are currently executing or has executed in the past, What was the statistics, and it gives good subset of data what profiler can gives.
a) Sys.dm_exec _query
b)
Deadlock - Database return 1205 error when it receives dead lock kind of scenario.
Ex- SET DEADLOCK_PRIORITY Normal
Can be set to
Low -5
Normal 0
High 5
Can also set the integer value from -10 to 10.
Deadlock victim selection criteria :-
a) If the dead lock priority is different the session as a lowest priority selected as a victim
b) If the session have the same priority, the transaction that has least expensive to rollback is selected as a victim.
c) If the session have the same deadlock priority and the same cost, a victim is chosen randomly.
Example :-
Begin tran _2
Update table A where ID in (1,2,3,4,5) - execute (This table have 5 Rows)
Update table B // Deadlock because of
Commit
-------------------------------
Begin tran _1
Update table B- execute (This table have 5 Rows)
Update table A // Deadlock because of
Commit
So, first transaction 5 data uodated to the table A' and second transaction 1 data updated to table B' now we run transaction _2 update A, so in this case 2nd transaction is costly then the first transaction because first transaction have a 5 rows to rollback were as 2nd transaction have only 1 row of table B to rollback so transaction B is less costly compare to transaction A, So 1st transaction data will updated on (A & B) table.
Explicitly SET DeadLock Priority
Example :-
Begin tran _2
Update table A where ID in (1,2,3,4,5) - execute (This table have 5 Rows)
Update table B // Deadlock because of
Commit
-------------------------------
SET DEDELOCK_PRIORITY HIGH
Begin tran _1
Update table B- execute (This table have 5 Rows)
Update table A // Deadlock because of
Commit
Trace turn on session level-
- DBCC TRACEON (1222,- 1) //Trace on session level
- DBCC TRACESTATUS (1222, -1)
- DBCC TRACEOFF (1222, -1)
Traces turn on global level-
- DBCC TRACEON (1222) //Trace on session level
- DBCC TRACESTATUS (1222)
- DBCC TRACEOFF (1222)
- sp_readerrorlog : This will give us victim id through victim id we can find the transaction which are victim for the dead lock, if its transaction through procedure we will get the procedure name as well and we have isolation level committed because we did not set explicitly dead lock and we have resource list table name and owner id that define the transaction/process id which executed successfully and waiter list id which have process id which transaction victim for the deadlock.
Comments
Post a Comment