Microsoft SQL Server Database Administration.

MS-SQL Server is a Client-Server Architecture. MS-SQL Server process starts with the client application sending a request, The SQL Server accepts, process and replies to the request. SQL server Architecture contains the following major components they are Protocol Layer. Relational Engine. Storage Engine. Databases (Files). Protocol Layer This supports three type of client-server architecture            1.   Shared Memory :  SHARED MEMORY PROTOCOL  Here MS-SQL server provides and supports shared memory protocol here CLIENT and MS-SQL server are on the same machine. To get connected to the default instance on the machine we can use SSMS (SQL Server Management Studio) and to connect use any of the type in SSMS connection method ".", "localhost", "127.0.0.1", "Machine_name\Instance_name"           2.  TCP/IP :  The client and MS-SQL server is on different physical location and different machines, whic...

Topics In This Module. Responsibilities of Database Administrator  Types of DBAs  History of SQL Server  Editions of SQL Server  Tools of SQL Server  Differences between Standard and Enterprise editions  Instances types in SQL Server  SQL Server Services 

Topics In This Module. Environments Installation of SQL Server SQL Server Instances SQL Server Services Ports, Protocols and Service Accounts. Patching Migration & Upgradation. *** ============================================================================*** Environments There are two types of environments in general they are: Pre-Prod Environments. => Which Involves Dev environment and UAT environment. Prod Environments. => We do have OLAP (Online Analytical Processing) and OLTP (Online Transactional Processing) Servers in production. SQL Server ->  Instance  ->  database  ->  File groups  ->  Files  ->  as two types below The Files in SQL Server are of two types they are Data files  ->  Extends  ->  Pages  ->  Header part, Data part(8060 Bytes), Row off Set Log files  ->  Virtual logs  ->  LSN  ->           ...

Topics In This Module. Databases. Types of databases and Its Properties Database Architecture Page Extent Files File Groups Modifying Database Modifying Files and File Parameters File Movement Database Corruption Log File Architecture Recovery Models  *** ============================================================================*** Once after instance is installed and connection to instance is successful, we see some by-default databases in instance which are called system databases, and per requirement the user defined databases t load data to use it as requirement. Databases Total Number of databases that can be created in an instance are  32767 . S.no Databases Maximum Number in instance 1 System Databases 5 2 User Databases 32762 3 Total Databases (system + User) 32767 Note :  When the Services are started or restarted, below is the order of starting databases Master Database  à  Resource Database  à  MSDB Database  à  User Databas...