CLIENT – SERVER ARCHITECTURE:
CLIENT (SSMS) COLLECTS THE QUERIES, EMBED THEM INTO “TDS” PACKETS. TABULAR DATA STREAM PACKETS.
THESE PACKETS ARE ENCRYPTED [SECURED] AND TRANSFERRED TO THE SERVER USING PROVIDERS (DRIVERS).
TYPES OF DRIVER PROGRAMS FOR DATABASE CONNECTIONS IN REAL-TIME:
1. SQL NATIVE CLIENT (SNAC) : USED FOR LOCAL SQL SERVER TO REMOTE SQL SERVER COMMUNICATION
2. OLE-DB (OBJECT LINKING AND EMBEDDING DB DRIVER): USED TO COMMUNICATE SQL SERVER WITH
OTHER DATA STORAGE PLATFORMS LIKE EXCEL, ORACLE, SAP, ETC..
3. ODBC (OPEN DATABASE CONNECTIVITY DRIVER) : USED TO COMMUNICATE SQL SERVER WITH ANY TYPE
OF DATA SOURCES INCLUDING PDF, WORD, HTML, APPLICATIONS, ETC..
THE TDS PACKETS ARE SENT TO SERVER. SERVER “COMPILES” THE CONTENT IN TDS PACKET. EXECUTES. THE RESULT IS
SENT TO THE CLIENT (SSMS) IN THE FORM OF TDS PACKETS. DEFAULT PACKET SIZE : 4096 BYTES.
NOTE: THE PROCESS OF EMBEDDING THE QUERIES INTO TDS PACKETS IS PERFORMED BY USING “PROTOCOLS”
NOTE: THE PROCESS OF ENCRYPTION [HIDING THE QUERIES IN TEH TDS PACKET] IS ALSO PERFORMED BY “PROTOCOLS”
DURING SQL SERVER INSTALLATION:
A SET OF FIVE SYSTEM DATABASES ARE AUTO CREATED.
MASTER – USED TO STORE SERVER PROPERTIES AND LOCATION OF OTHER DATABASES, CONNECTIONS.
MODEL – USED AS A TEMPLATE FOR NEW DATABASES WE CREATE IN FUTURE.
MSDB – USED BY SQL DBAs FOR JOBS, ALERTS, EMAILS, REPAIRS, TROUBLESHOOTING
TEMPDB – USED BY DEVELOPERS AND TESTERS FOR TEMPORARY CALCULATIONS, TEMP STORAGE
RESOURCE – HIDDEN, READ ONLY DATABASE. USED FOR CONTROLLING ABOVE DATABASES.