Designing SQL Server databases( 2012 SQL Server)

  Designing SQL Server databases

SQL Server database structure

Create a database.

Add and alter file groups.

Add files to file groups.

Detach and attach databases.

Understand database recovery models

Start creating Microsoft SQL Server 2012 databases, you should have a good understand­ing of the system databases that are created by default when you install an instance of SQL Server. Each of the following databases serves a specific purpose and is required to run SQL Server

Ø  Master

Ø  TempDB

Ø  model

Ø  MSDB

Ø  Resource

Ø  Distribution

Master database

The master database, as its name suggests, is the primary system database. Without it, SQL Server cannot start. The master database contains the most important information about objects within the SQL Server instance.

Ø  Databases

Ø  AlwaysON

Ø  Database mirroring

Ø  Configurations

Ø  Logins

Ø  Resource Governor

Ø  Endpoints

 Tempdb database

The tempdb database is a global playground for temporary objects created by the internal processes that run SQL Server and temporary objects that are created by users or applications. These temporary objects included temporary tables and stored procedures, table variables, global temporary tables, and cursors. In addition to temporary objects, tempdb stores row versions for read-committed or snapshot isolation transactions, online index operations, and AFTER triggers. One important thing to note about tempdb is that it is re-created every time SQL Server is restarted.

Model database

The model database is exactly what its name implies: a model for all databases that are created on an instance of SQL Server. In other words, it’s used as a template each time you create a database.

Msdb database

The s serves primarily as the back-end database for Microsoft SQL Server Agent. Whenever you create and/or schedule a SQL Server Agent job, the metadata for that job is stored in this database.

Ø  Service brokers

Ø  Alerts

Ø  Log shipping

Ø  SSIS packages

Ø  Utility control point (UCP)

Ø  Database mail

Ø  Maintenance plans

Resource database

The resource database is a hidden, read-only database that is usually not discussed very often. The resource database's primary purpose is to improve the upgrade process from one version of SQL Server to the next. All system objects for an instance of SQL Server are stored within the resource database. This database cannot be backed up or restored.

Distribution database

The final system database is the distribution database. This database exists only when you have con­figured this instance as a distributor for replication. Prior to configuring replication, you must perform this configuration. All metadata and history for the various types of replication are stored within this database.

View system databases
 

1.      Open SQL Server Management Studio (SSMS) and connect to a server.

2.      Object Explorer should be open. If it is not, press F8 to open it.

3.      In Object Explorer, expand Databases.

4.      You will see a folder labeled System Databases. Expand it.

Understanding the SQL Server database structure

The database creation process, while very simple, always requires careful thought relating to the struc­ture. Databases can be created using many different technologies and techniques.In this chapter, you will focus on using T-SQL and SSMS. By default, every SQL Server database consists of two files

1.     The data file contains data and database objects such as tables, views, and stored procedures.

2.      The log file contains information that assists in the recoverability of transactions in the database.

Srikanth Goud - Virtualization Administrator