Docstoc

Visit http www gcreddy com for SQL and QTP Info DATABASES A database in Microsoft SQL Server consists of a collection of tables that contain

Document Sample
Visit http www gcreddy com for SQL and QTP Info DATABASES A database in Microsoft SQL Server consists of a collection of tables that contain Powered By Docstoc
					       Visit http://www.gcreddy.com/for SQL and QTP Info

                                   DATABASES


       A database in Microsoft SQL Server consists of a collection of tables that
contain data and other objects, such as views, indexes, stored procedures, and
triggers, defined to support activities performed with the data. The data stored in a
database is usually related to a particular subject or process, such as inventory
information for a manufacturing warehouse.

SQL Server can support many databases. Each database can store either interrelated
or unrelated data from other databases. For example, a server can have one
database that stores personnel data and another that stores product-related data.
Alternatively, one database can store current customer order data, and another
related database can store historical customer orders used for yearly reporting.

Before you create a database, it is important to understand the parts of a database
and how to design these parts to ensure that the database performs well after it is
implemented.

Database Architecture
Microsoft® SQL Server™ 2000 data is stored in databases. The data in a database is
organized into the logical components visible to users. A database is also physically
implemented as two or more files on disk.
When using a database, you work primarily with the logical components such as
tables, views, procedures, and users. The physical implementation of files is largely
transparent. Typically, only the database administrator needs to work with the
physical implementation.




Each instance of SQL Server has four system databases (master, model, tempdb,
and msdb) and one or more user databases. Some organizations have only one user
database, containing all the data for their organization. Some organizations have
different databases for each group in their organization, and sometimes a database


                                                                                   1
       Visit http://www.gcreddy.com/for SQL and QTP Info

used by a single application. For example, an organization could have one database
for sales, one for payroll, one for a document management application, and so on.
Sometimes an application uses only one database; other applications may access
several databases.




It is not necessary to run multiple copies of the SQL Server database engine to allow
multiple users to access the databases on a server. An instance of the SQL Server
Standard or Enterprise Edition is capable of handling thousands of users working in
multiple databases at the same time. Each instance of SQL Server makes all
databases in the instance available to all users that connect to the instance, subject
to the defined security permissions.

Types of Databases:

       SQL Server provides two types of Databases.

      System Databases
      User Defined Databases




System Databases:

      Along with the installation of SQL Server FOUR databases will be created
automatically called as system databases. They are:

      master

       The master database records all of the system level information for a SQL
       Server system. It records all login accounts and all system configuration
       settings. master is the database that records the existence of all other
       databases, including the location of the database files. master records the
       initialization information for SQL Server; always have a recent backup of
       master available.




                                                                                    2
       Visit http://www.gcreddy.com/for SQL and QTP Info

      model

       The model database is used as the template for all databases created on a
       system. When a CREATE DATABASE statement is issued, the first part of the
       database is created by copying in the contents of the model database, then
       the remainder of the new database is filled with empty pages. Because
       tempdb is created every time SQL Server is started, the model database
       must always exist on a SQL Server system.

      msdb

       The msdb database is used by SQL Server Agent for scheduling alerts and
       jobs, and recording operators.

      tempdb

       tempdb holds all temporary tables and temporary stored procedures. It also
       fills any other temporary storage needs such as work tables generated by SQL
       Server. tempdb is a global resource; the temporary tables and stored
       procedures for all users connected to the system are stored there. tempdb is
       re-created every time SQL Server is started so the system starts with a clean
       copy of the database. Because temporary tables and stored procedures are
       dropped automatically on disconnect, and no connections are active when the
       system is shut down, there is never anything in tempdb to be saved from
       one session of SQL Server to another.

User Defined Databases:

      The databases that are created by the users are called as user defined
databases. While creating a database the user has to specify a name to the database
and the details of data files, which are used to specify the location, where the
database data is to be stored.

Files and Filegroups
Microsoft® SQL Server™ 2000 maps a database using a set of operating-system
files. All data and objects in the database, such as tables, stored procedures,
triggers, and views, are stored within these operating-system files:

      Primary Data File (.mdf)

       This file contains the startup information for the database and is used to store
       data. Every database has one primary data file.

      Secondary Data File (.ndf)

       These files hold all of the data that does not fit in the primary data file. If the
       primary file can hold all of the data in the database, databases do not need to
       have secondary data files. Some databases may be large enough to need
       multiple secondary data files or to use secondary files on separate disk drives
       to spread data across multiple disks.




                                                                                        3
       Visit http://www.gcreddy.com/for SQL and QTP Info

      Transaction Log File (.ldf)

       These files hold the log information used to recover the database. There must
       be at least one log file for each database.

Filegroups allow files to be grouped together for administrative and data
allocation/placement purposes. For example, three files (Data1.ndf, Data2.ndf, and
Data3.ndf) can be created on three disk drives, respectively, and assigned to the
filegroup fgroup1. A table can then be created specifically on the filegroup fgroup1.
Queries for data from the table will be spread across the three disks, thereby
improving performance. Files and filegroups, however, allow you to easily add new
files on new disks. Additionally, if your database exceeds the maximum size for a
single Microsoft Windows NT® file, you can use secondary data files to allow your
database to continue to grow.

Creating a Database:

To create a database determine the name of the database, its owner (the user who
creates the database), its size, and the files and filegroups used to store it.

Before creating a database, consider that:

      Permission to create a database defaults to members of the sysadmin and
       dbcreator fixed server roles, although permissions can be granted to other
       users.

      The user who creates the database becomes the owner of the database.

      A maximum of 32,767 databases can be created on a server.

      The name of the database must follow the rules for identifiers.

   A database can be created either by using Enterprise Manager or by using Query
   Analyzer.

   Using Enterprise Manger to Create Database:

       To create a database using Enterprise Manager use the following steps:


   1. Open the Enterprise from START button as follows:

       Start -> Programs -> Microsoft SQL Server -> Enterprise Manager

   2. Expand a server group, and then expand a server




   3. Right-click Databases, and then click New Database.




                                                                                   4
   Visit http://www.gcreddy.com/for SQL and QTP Info

4. Enter a name for the new database.




   The primary database and transaction log files are created using the database
   name you specified as the prefix, for example newdb_Data.mdf and
   newwdb_Log.ldf. The initial sizes of the database and transaction log files are
   the same as the default sizes specified for the model database. The primary
   file contains the system tables for the database.

5. To change the default values for the new primary database file, click the Data
   Files tab. To change the defaults for the new transaction log file, click the
   Transaction Log tab.




6. To change the default values provided in the File name, Location, Initial
   size (MB), and File group (not applicable for the transaction log) columns,
   click the appropriate cell to change and enter the new value.

7. To specify how the file should grow, select from these options:
       To allow the currently selected file to grow as more data space is
          needed, select Automatically grow file.

          To specify that the file should grow by fixed increments, select In
           megabytes and specify a value.

         To specify that the file should grow by a percentage of the current file
          size, select By percent and specify a value.
8. To specify the file size limit, select from these options:




                                                                                5
      Visit http://www.gcreddy.com/for SQL and QTP Info

             To allow the file to grow as much as necessary, select Unrestricted
              filegrowth.

             To specify the maximum size the file should be allowed to grow to,
              select Restrict filegrowth (MB) and specify a value.


   Note: The maximum database size is determined by the amount of disk space
   available and the licensing limits determined by the version of Microsoft® SQL
   Server™ you are using.

Using Query Analyzer:

      To use the Query Analyzer tool, use the following steps.

   1. Open the tool from the start menu as follows:
      Start -> Programs -> Microsoft SQL Server -> Query Analyzer

   2. Specify the details of server from the drop down button.




   3. Select the authentication type from one of the option buttons.
   4. click “OK”.
   5. To execute any command, type the command in the empty area and press
      the “Execute Query (F5)” icon on the tool bar.



      We can also use the Enterprise Manger tool to open Query Analyzer. To do
      this, in the Enterprise Manager, Choose Tools -> SQL Query Analyzer
      menu item and the above steps as it is.


Creating a Database Using Query Analyzer:

      Once the Query Analyzer is opened, in the command window we can write
any command that has to be executed.

Syntax:


 CREATE DATABASE databasename
 [ON]
 (NAME=<logical file name>,
 FILENAME=<os-filename>,
 SIZE=<in mb’s>,MAXSIZE=<in mb’s>,FILEGROWTH=<in mb’s>)]
 [LOG ON
 (NAME=<logical file name>,
 FILENAME=<os-filename>,
 SIZE=<in mb’s>,MAXSIZE=<in mb’s>,FILEGROWTH=<in mb’s>)]


                                                                               6
       Visit http://www.gcreddy.com/for SQL and QTP Info



Note: „Logicalfilename‟ is used to identity the file for further modifications and „os-
filename‟ refers the original location where the file is stored in the hard disk.



Example:


 CREATE DATABASE emp
 ON
 (NAME=’emp-dat’,
 FILENAME=’d:\backup\data\emp_dat.mdf’,
 SIZE=15,MAXSIZE=50,FILEGROWTH=5)
 LOG ON
 (NAME=’emp-Log’,
 FILENAME=’d:\backup\log\emp_log.ldf’,
 SIZE=10,MAXSIZE=25,FILEGROWTH=5)




                                                                                          7

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:140
posted:12/9/2010
language:English
pages:7
Description: It explains SQL Server Databases, Database Architecture and Creating & Modifying Databases