SQL Server Concept for understanding as Oracle DBA

Database:It is a container which store data and data objects. It manages data and allows fast storage and retrieval of that data.

There are 2 Types of databases:

- Advertisement -

• System databases
• User databases

A).System Database:

The system databases are the most basic building blocks of your database system ,
and unlike other internal components such as CPU queries or storage engine , you can directly view via your connection SSMS . System databases will be created as a part of SQL Server installation. SQL Server internal operations will be perform using system databases.

There are 6 System databases:

• Master
• MSDB
• Model
• Tempdb
• Resource
• Distribution

1.Master:

The master database stores all information in the system for a SQL Server system.
This includes wide instance metadata such as logon accounts , extremities, linked servers, and system configuration settings. In SQL Server , system objects are no longer stored in the master database. The main database is the backbone of the whole operation.
SQL Server can not start if the primary database is unavailable. It contains the entire mother load of your server information. Think of the master database as the Govt. of a country, it knows everything, sees everything and has lots of subdivisions to rule over your ass.

There are some things you can do with a normal database , but not with the master :

- Advertisement -

• Add Files or file groups.
• Modification of the snack. The default collation is the server collation.
• Change the owner of the database. Master is owned by Dbo.
• Creating a full-text catalog or full-text index.
• Create triggers on system tables in the database.
• Dropping the database.
• Dropping the guest user from the database.
• Enable capture data changes.
• Participate in the mirror database.
• Removing the primary filegroup, primary data file, or log file.
• Rename the database group or a primary file.
• Setting the database to OFFLINE.
• Setting the database or a group of primary READ_ONLY files.

Here are some interesting facts about the main database:

Default, the master is defined in the simple recovery model. You can change FULL or bulk-logged, but it makes no difference. You can not take log and differential backups master database. When SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the master of recovery model is automatically set to simple. If you move the master database, you must move the base resource data in one place up to any version only The system databases, and so the master can be restored only from backups that are created on the SQL Server version that the server instance is running. You can not restore a master database backup taken on SQL Server machine.

2.MSDB:

It is used by SQL Server Agent for scheduling alerts and jobs.
It contains an online backup and restores the history of the proceedings.
It is used by other features such as SSMS, Service Broker and Database Mail.
Storage DTS package in SQL Server 7.0 and 2000
Storage SSIS package in SQL Server 2005.

NOTE:SQL Server Agent is not available in SQL Server Express editions installations or working group, even if does not exist the SQL Server Agent service, the authority still has the database MSDB

Like all system databases, you can not perform certain functions on msdb :

• Remove the database.
• Dropping the guest user from the database.
• Enabling the capture of data changes.
• Participate in the mirror database.
• Removing the primary filegroup, primary data file , or log file.
• Renaming the database or primary filegroup .
• Setting the database to OFFLINE.
• Setting the READ_ONLY primary filegroup.

Here are some interesting facts about the main database:

By default, msdb is defined in the simple recovery model. You can change FULL or bulk logged. When SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to simple.
The system databases, and therefore the msdb, can be restored only from backups that are created on the SQL Server version that the server instance is running. You can not restore a database backup msdb taken on SQL Server machine. The most critical function of msdb is to maintain a complete history of backup and restore on that instance. It is with this information that you are only able to restore backups, including backups of the transaction log.

3.Model Database:

The model database is used as a template for all databases created on an instance of SQL Server. User-defined tables, stored procedures, data types defined by the user, etc. can be created in the model database, and exist in all future user-defined databases.
The settings such as the recovery model for the database from the database model are applied to future databases user-defined, but some of the model parameters are used only for the creation of a new Tempdb during startup, so that the model database must always exist on a SQL server system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying the contents of the model database.
The rest of the new database is then filled with empty pages.

Like all system databases, you can not perform certain functions on model database same as the MSDB. Here are some interesting facts about the main database:

• Default, the master is defined in the simple recovery model.
  You can change FULL or bulk-logged, but it makes no difference.
  You can not take log and differential backups model database.
  When SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the model of recovery type is automatically set to the simple
  The system databases, and so the model can be restored only from backups that are created on the SQL Server version that the server instance is running.
  You can not restore a model database backup taken on SQL Server 2005 to SQL Server 2008 machine.

4.TEMPDB:

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server Temporary user objects that are explicitly created, such as : global or local temporary tables, temporary stored procedures , table variables, or cursors.
Internal objects that are created by the SQL Server database engine,for example, work tables to store intermediate results for spools or sorting.

Row versions that are generated by data modification transactions in a database that uses read committed by means of line operations versioning isolation or snapshot isolation .
Row versions that are generated by data modification transactions for features , such as online index operations, set of multiple active results (MARS) , and after triggers.
All the transformation, internal calculations, and conditions sorting operations will be performed in Tempdb. Whenever SQL Server started Tempdb will be deleted and new copy will be created. Backup and restore operations are not allowed on tempdb. The size of tempdb can affect the performance of a system. The size of tempdb can affect the performance of a system. For example, if the tempdb size is too small, the treatment system might be too busy with autogrowing database to support your requirement workload every time you start SQL Server. You can avoid this overhead by increasing the size of tempdb.
We can reduce tempdb. But steadily shrinking the files are not a recommended practice because these files can probably grow again. Also reduce data fragmentation causes operations .

We have some limitations on shrinking tempdb :
Shrink operations will shrink the version store or internal objects . In some cases, the DBA may need to restart the server in single-user mode to allow the narrowing of the tempdb.

5.DATABASE RESOURCE:

SQL Server has four system databases visible ie master, model, tempdb and msdb.
It also contains a fifth, “hidden” database resource you will ever see using one of normal SQL commands that list all your databases. It is a read-only database that contains system objects that are included with SQL Server. Objects SQL Server system, such as sys.objects, are physically persisted in the database of resource data, but they logically appear in the sys schema of every database.

The resource base contains no user data or the data the user metadata. As the resource database makes upgrading to a new version of SQL Server an easier and faster procedure.
In earlier versions of SQL Server, upgrading required fall and creating system objects.
Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying single database file resources on the local server.

6.DISTRIBUTION DATABASE:

The distribution database stores metadata and history data for all types of replication, and transactions for transactional replication.

B).USER DATABASES:

User databases are created based on organization requirement.
We are creating externally and stores data.

Creating database in GUI:
Open SQL Server–>connect to management studio–>select sql server instance–>go to database–>right click–>new database.

- Advertisement -