Chapter 4 – MANAGING DATABASE

Leave a comment (1) Go to comments

As a database developer, you need to create a database or delete the database when it is not required. In this chapter we will learn how to create a database, database objects like table & deleting a database or table.

System Database in SQL Server 2005

System Database is the default database that exists in every instance of SQL Server 2005. This database contains tables which store the server specific configurations or templates for some other database.

These are the following System Database in SQL Server 2005:

  1. master
  2. tempdb
  3. model
  4. msdb
  5. Resource

The master Database

This database is the prime & most important database in SQL Server 2005. This database is the head of the entire related database. It is also recommended not to give permission of master database to any user & it is also important to update the backups of master database which may take effects on other databases related to this master database. Therefore it has its own importance

Importance:

  • This database contains very important data which controls the SQL Server operations
  • This database stores all the authorized users information, database, system configuration settings, remote servers & server specific configuration
  • This database also stores the initializing information of the SQL Server 2005, this means if master database is not available SQL Server database engine will not start.

The tempdb Database


This database is a temporary database that holds all temporary tables and stored procedures. Stored Procedures are the temporary set of SQL statement which results in faster output. This database created every time whenever the SQL Server 2005 starts, so you should not store any database object in tempdb database. All the temporary tables & result generated by GROUP BY, ORDER BY & DISTINCT clauses are stored in tempdb database.

The model Database

This model database provides templates or prototype for a new database. This means whenever a database is created the contents of model database will be copied to new database. For example: I want a particular database object in every new database, I will simply add that database object to model database & after that whenever a new database is created that database object will automatically add to it.

The msdb Database

This database supports the SQL Server Agent. SQL Server Agent is a tool that schedules the periodic activities of SQL Server. This means I can set the periodic activities like database backup & database mailing. This database contains task scheduling, exception handling, alert management & system operator information needed for SQL Executive Service. For example: I can query the how many emails have been sent to administrator, scheduled backup for next time & also the history of previous scheduled backups.

The Resource Database

The Resource database is a read-only, hidden system database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

Advantages of Resource Database

Prior to SQL Server 2005, whenever service packs are applied all the system objects that are residing within the system and user databases gets updated which makes it very difficult to rollback the changes.

  • The only way to rollback the changes is to uninstall the instance of SQL Server and reinstall SQL Server followed by applying any Service Packs or Hotfixes to revert back to the previous version of the SQL Server Instance and attached all User databases.
  • In SQL Server 2005 onwards the changes will be made to the Resource Database, which will indeed reflect the changes in all the system and user database of the particular instance
  • If the DBA needs to apply a Service Pack to multiple instances, then the mssqlsystemresource.mdf and mssqlsystemresource.ldf needs to be copied by the DBA to the target instance
  • If the DBA needs to rollback the changes performed by the Service Packs then they need to replace the Resource Database related MDF and LDF files with that of the previous backed up version

Identify Database Files

Every database is store on a hard disk as a set of files. These files include:

  • Primary data file: The extension for primary data file is ".mdf". The primary data file contains the database objects & used to store system tables and objects.
  • Secondary data file: The extension for secondary data file is ".ndf". The secondary data file also store database objects. A large database may need many secondary data files which can be spread over many hard disks. If the primary data file is large enough to store the database then there is no need of secondary data file.
  • Transaction log file: The transaction log file record all the modification, changes made in database & also record the transaction that caused the modification. At least one transaction log file must exist for a database. A database may have more than one transaction log file & the minimum size of this file could be 512K.

As now you are familiar with SQL Server 2005 & its features. Let’s see how to start & create a database in SQL Server 2005.

· First install SQL Server 2005, after installing go to start>all programs>Microsoft SQL Server 2005>SQL Server management studio.

· Then select server type as Database Engine, Analysis services, Reporting services, Integrating services or SQL Server Mobile, we will select Database Engine because we want to create, store or manipulate data.

Chapter 4   MANAGING DATABASE System Database in SQL Server 2011 System Database in SQL Server 2008 System Database in SQL Server 2005 System Database in SQL Server how to create a database in SQL Server

· As shown in this above picture I have used HOMW\ROXY in server name & Windows Authentication in Authentication, this is because I am not connected to a server & my local pc is acting as a server right now, it means now database server is my pc only.

· If you want to connect to a server using SQL Server 2005 you will type the name of the server to whom you want to connect & SQL Server Authentication in AUTHENTICATION. User name to access the database server in LOGIN & user password in PASSWORD

Chapter 4   MANAGING DATABASE System Database in SQL Server 2011 System Database in SQL Server 2008 System Database in SQL Server 2005 System Database in SQL Server how to create a database in SQL Server

· This following screen will appear after pressing the connect button.

Chapter 4   MANAGING DATABASE System Database in SQL Server 2011 System Database in SQL Server 2008 System Database in SQL Server 2005 System Database in SQL Server how to create a database in SQL Server

This means that I am connected with database sever on my PC. And now I can create, store or manipulate data.

Now click on New query below the file button in the left corner of the above page to type the command for the operation you want.

EOF - Chapter 4 – MANAGING DATABASE, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.