Managing Databases in SQL Server

Discussion in 'SQL Server' started by MinalS, Dec 23, 2014.

  1. Several databases are supported by the SQL Server. There are some standard system databases provided by SQL Server. The system database contains some server specific configurations and templates for the database. They have some temporary storage for querying the database.

    The list of databases provided by the SQL Server is described below:

    1. Master database

    The database consists of system tables that help user to track the server installation. The server specific configuration information containing the authorized users, system settings, databases and remote servers are provided.

    The user is not permitted to access the master database. The master databases are updated and the backup of the database for updating the user with the specific changes is kept. The initialization information of the server is kept in the master database.

    2. The tempdb database

    The tempdb database is the temporary database used to store the temporary tables and stored procedures. The nested queries and sorting of data is done through the temporary database.

    The database is re-created every time the server starts the system. A new copy of the database is provided. The stored procedures and the tables are dropped once the system is shut down. User cannot restore or backup the information in the temporary databases.

    3. The model database

    The model database can be used as a template or prototype for the new database. Once user has created the new database, the contents are copied to the new database. If the user wants to modify the changes, the entire database adapts those changes. Whenever user creates a new database, the object is added to the new database.

    4. The msdb database

    It is used for supporting the SQL Server Agent. The Agent is a tool used to schedule periodic changes of the server. The activities can be database mailing and backup. The job is scheduled to run in response to event. If there is an issue in the backup, the agent will encounter the problem and notify the user. Some of the system defined tables are present in the database

    5. The Resource database

    The resource database is used as hidden, read only database that contains the system objects. The user data or metadata is not present in the resource database. The new version of the server can be upgraded at a faster pace. The master database does not contain any entry for the resource database.

    6. The ReportServerTempDB database

    The ReportServerTempDB database is installed with the SQL Server Reporting Services. The database is used to store the session information, working tables and cached reports used for the reporting services.

    7. The Distribution Database

    The distribution database is used when the replication is configured for the instance of the SQL Server. All the information related to the configuration files is stored.

    Database Files

    The server maps the database over the system files. Every database is stored as the set of files on the computer hard disk. The files can be as mentioned below:
    1. Primary data file: The primary data file consists of database objects. The file can be used for system tables and objects. It is considered as the initial point and is used for pointing to the other database files. Each database consists of one database file. It has an .mdf extension.
    2. Secondary data file: It is the secondary file used for storing the user defined database. If the databases are very large, multiple secondary files can be used. The secondary file has an .ndf extension.
    3. Transaction log file: It is used to log the transaction file records for all the modifications present in the database. It consists of all the information and can be used to recover the system loss. There must be at least one transaction log for a database. The minimum size of the log file is 512 KB. The log files have .ldf extension.

    User defined Database

    SQL Server contains user defined database used for storing and managing information. The set of files are stored on the database. The CREATE DATABASE statement is used for creating the database. The syntax for creating a database is as shown below:
        CREATE DATABASE database_name 
        [ ON [ PRIMARY ] [ <filespec> ] ] 
        [ , <filegroup> [ ,…n ] ]
        [ LOG ON [ <filespec> ] ]
        < filespec> : : = 
        ( [ NAME = logical_file_name, ]
          FILENAME = ‘os_file_name’ 
          [ SIZE = size ]
          [ , MAXSIZE = ( max_size | UNLIMITED ) ]
          [ , FILEGROWTH = growth_increment ] ) [ ,…n ]
        <filegroup> : : = 
            FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [ DEFAULT ] 
            <filespec> [ ,…n ]

    database_name is the name of the new database.

    ON is used to describe the disk files for storing the data portion

    PRIMARY is used to specify the associated <filespec> list

    LOG ON is used to check the disk files for storing the log files

    NAME = logical_file_name is the logical name for the file

    FILENAME = os_file_name is the operating system file name

    SIZE = size is the initial size of the file defined

    MAXSIZE = max_size is the maximum size of the file defined in the specification

    FILEGROWTH = growth_increment is the increment of the file in the list

    CONTAINS FILESTREAM is the filegroup for storing the FILESTREAM data in the file system

    DEFAULT is the default filegroup in the database

    The following code is used for creating the database in SQL Server.

    CREATE DATABASE EmployeeData
    Filegroups in database

    The database files are grouped together to form the filegroups for the allocation of the files and administrating them. It is a collection of files. The database contains user defined and primary file group.

    A primary filegroup consists of the primary data files. The system tables are available in the filegroup. If the user does not define the filegroup, the default filegroup is assigned. There can be only one filegroup per database to be defined as the default filegroup.

    A user defined filegroup is the filegroup created by the users. The user defined filegroup can be distributed in more than one filegroups.

    Creating databases and defining the filegroups

    User can specify the name of the database during the creation of the database. The default filegroup is used for the creation. User can specify the name of the filegroup while creating the database.

    The following statements are used for creating database along with the filegroups.

    ( NAME = ‘DB1_Primary’ ,
      FILENAME = ‘ C:\FileData\DB1_Prm.mdf’,
      SIZE = 5MB,
      MAXSIZE = 20MB,
      FILEGROWTH = 2MB ) ,
      ( NAME = ‘DB1_FG1_Dat1,
        FILENAME = ‘C:\FileData_FG1.ndf’,
        SIZE = 2MB,
        MAXSIZE = 15MB,
        FILEGROWTH = 2MB),
    LOG ON
       ( NAME = ‘DB1_log’,
         FILENAME = ‘C:\FileData\DB1.ldf’,
        SIZE = 2MB,
        MAXSIZE = 10MB,
        FILEGROWTH = 1MB )
    In the above statement, the database named as DB1 is created. It is the primary and the default filegroup where the files are created. The secondary file named as FileData_FG1 is created. The log file named as DB1.ldf is created.

    The default filegroup can be modified using the following statement.

    FILESTREAM enabled databases

    User needs to store the unstructured data in a database. The unstructured data can be graphic files, videos, clips and word documents. The server provides with the feature to enable user to store the data efficiently.

    The database contains a FILESTREAM filegroup. A table containing the column with the FILESTREAM attribute is created. The feature is enabled during the installation. Once it is enabled, the user can configure it to the access level.

    The access level specifies the accessibility of the FILESTREAM data on the SQL Server instance. The possible values for the accessibility are as mentioned below:
    1. Access Level 0: It states that the FILESTREAM data is disabled. It is defined as the default value.
    2. Access Level 1: It states that the FILESTREAM data is enabled for the T-SQL access.
    3. Access Level 2: It states that the data is enabled for T-SQL and local file system access.
    4. Access Level 3: It states that the data is enabled for T-SQL, local file system access and remote access
    User can execute the following statement in the editor window.

    EXEC sp_configure filestream_access_level , 3 
    The configuration changes can be applied using the following statement.

    User can now create the FILESTREAM enabled database for storing the unstructured

    PRIMARY ( NAME = NewData,
    FILENAME = ‘C:\Data\NewData.mdf’ ),
    FILEGROUP FileStreamGroup CONTAINS FILESTREAM ( NAME = NewData1, FILENAME = ‘C:\Data\NewData1’)
    LOG ON ( Name = Log1,
    FILENAME = ‘C:\Data\NewData.ldf’)
    In the above code, the CONTAINS FILESTREAM clause is used to specify that the database has the FILESTREAM data.

    Renaming the database

    The renaming of the database is possible in SQL server. The system administrator can rename the database. The sp_renamedb is used for renaming the database. The syntax for renaming the database is as shown below:

    sp_renamedb old_database_name, new_database_name

    old_database_name is the current database name.

    new_database_name is the name of the new database.

    The following statement is used to rename the database.

    sp_renamedb ‘Employee’, ‘Company’
    Dropping the database

    The database can be deleted if it is no longer required by the user. The database files and the data is deleted. The users with the sysadmin role are capable for deleting the database. The DROP DATABASE statement is used for deleting the database.

    The syntax for DROP DATABASE is as shown below:
    DROP DATABASE database_name
    The following statement deleted the database from the system.
        DROP DATABASE Employee

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice