INSTANCE – A SUCCESSFUL INSTALLATION OF SQL SERVER.
TYPES OF INSTANCES:
DEFAULT INSTANCE NAMED INSTANCE
—————————- ————————————
INSTANCE NAME = MSSSQLSERVER INSTANCE NAME IS USER DEFINED
SERVERNAME = COMPUTERNAME SERVERNAME = COMPUTERNAME\INSTANCENAME
USED IN PRODUCTION USED IN DEV & TEST ENVIRONMENTS
DEFAULT PORT NUMBER: 1433 DYNAMIC PORT ALLOCATION DURING INSTALLATION
SERVICE – A COMMUNICATION PATH BETWEEN WINDOWS (OPERATING SYSTEM) AND SQL SERVER MAIN GATE
SERVICE ACCOUNT – A WINDOWS OR LINUX USER USED TO MANAGE / CONTROL THE ABOVE SERVICE WATCHMAN
AUTHENTICATION – A COMMUNICATION PATH BETWEEN SQL SERVER AND END USERS MAIN DOOR
LOGIN – ANY USER WHO NEEDS TO CONNECT, OPERATE ON THE SERVER END USER
FILESTREAM – A MECHANISM TO STORE BIG DATA FILES [DOC, PDF, CLOUD BASED INFORAMTION…..] IN SQL SERVER
COLLATION – A SERVER PROPERTY TO SPECIFY THE LANGUAGE (ENGLISH, CHINESE..). DEFAULT COLLATION: Latin1_General_CI_AI
SYSTEM DATABASES – THESE ARE AUTO CREATED DURING INSTALLATION OF SQL SERVER.
TOTAL OF FIVE SYSTEM DATABASES:
1. MASTER – USED TO CONTROL ALL OTHER DATABASES AND LOGIN (CONNECTIONS)
2. MODEL – USED AS A TEMPLATE FOR NEW DATABASES WE CREATE
3. MSDB – USED FOR ADMINISTRATION AND REPAIRS BY SQL DBAs
4. TEMPDB – USED FOR TEMPORARY STORAGE AND “QUERY” PROCESSING BY ANY USER
5. RESOURCE DATABASE – USED TO CONTROL MEMORY, PROCESSOR ALLOCATIONS AND SERVICE
NOTE: FOR BEST PERFORMANCE, ENSURE THE NUMBER OF TEMPDB DATA FILES = NUMBER OF PROCESSOR NODES [EX: 4]
** IN ADDITION TO ANOVE 5 SYSTEM DATABASES, WE CAN CREATE UPTO 32767 (USER) DATABASES IN EACH INSTANCE.