When you install SQL Server, there by default system databases are created. Which are, Master … MSDB … Model … and TempDB … and Resource DB, which is not visible through SSMS (SQL Server Management Studio). All of these DBs are there for specific reason and each has particular task to perform.
- Master DB:- As name suggests, its Master Puppeteer of the game. It has all the metadata of DBMS. Including Logins, Endpoints, Configurations and all sort of information that is required to run any instance.
- MSDB:- Job of this database is to keep Jobs !!! Well, it stores all information about SQL Server Agent and its jobs, history etc. Also if DTS/SSIS packages are deployed they are stored here as well .
Note:- all these info are stored in Master if you are using Express edition as there is nothing like AGENT !!!
- Model:- Yes, you got it… its the model which is followed by each newbie :-d … well sort of … this database contains info what property a newly created database should contain by default (default recovery model for example)… ofcourse we can change it according to our need… in one word .. it acts as a Template.
- Temp DB:- And the last one is this poor soul, that anyone can mess around with it. As from name, its used Temporary calculation, storage requirements etc. purposes … at first site this DB doesn’t look to have of much importance but believe me, it can cause a huge performance impact if abused !!! And so MSFT Best Practice suggests it to store in separate dedicated storage.
- Resource DB:- It was introduced in version 90 a.k.a. SQL Server 2005. Its job is to store system objects. It was introduced to improve upgrade and rollback of SQL Server. Prior to invention of Resource DB, all system objects were stored in Master DB which caused trouble in time to time when SQL Server gets patched or upgraded.
Physically when a database (including default system databases) are created in SQL Server, it generates two files (at least 2 , it can be more then 2 too). One is Data file and another is Log file. Datafile has default extension of “.mdf” and Logfile has “.ldf”. We can have more then one datafile and logfile (but creating more then one logfile has NO performance improvement at all as it is sequentially written.). Storing data in more then one datafile which are separated by different drives can enhance performance.
(I found interesting pictorial presentation of SQL Server file system hierarchy from SQL Server 7 of BOL)
Virtually group of datafiles is called Filegroup (logfiles are not included in filegroup). Default filegroup is called Primary filegroup which can’t be deleted. It stores all metadata about table/s in that filegroup. Filegroups created other then primary filegroup, are called Seconday Filegroup. We can always set Secondary Filegroup as DEFAULT filegroup (that we usually do), so that any user created objects are stored in seconday filegroup by default (but still we need primary filegroup to store metadata :-D). Filegroups can help greatly by mean of managing data in sense of backup/restore, security and performance. For example you can have two file group out of which one filegroup contains tables which are used for reference (e.g. zip codes !!!) you can make it as “READONLY” so nobody can change it or even can be excluded from daily backup scheme which can ultimately save total time !!!.
So, this was all about basic default System databases (you may have more then these databases, but all other system databases are not essential for functioning SQL Server). We also saw file types created by SQL Server when new database is created and how can we group more then one datafile in containers called Filegroups for ease of manageability.
Its enough for this time, next time we will see in more details about some of these stuffs.
Its just a thought … 