1 / 25

Session 7

Session 7. Creating and Managing Databases. Session Objectives. Describe the system and user-defined databases in SQL Server 2005. Outline the key features of the AdventureWorks sample database. Describe how to add filegroups and transaction logs.

mattox
Download Presentation

Session 7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session 7 Creating and Managing Databases

  2. Session Objectives • Describe the system and user-defined databases in SQL Server 2005. • Outline the key features of the AdventureWorks sample database. • Describe how to add filegroups and transaction logs. • List types of database modifications and describe how to drop a database. • Describe database snapshots.

  3. SQL Server Supported Databases • SQL Server 2005 supports the following databases: • Created by SQL Server to store information about SQL Server 2005. • Also manages user-defined databases. System Database • SQL Server 2005 users create user-defined databases. • Stores user data. User-defined databases • SQL Server provides sample databases for the users to work with SQL Server 2005. • A new sample database, AdventureWorks, has been introduced in SQL Server 2005. Sample Databases

  4. System Databases SQL Server 2005 provides and supports these system databases:

  5. Modifying System Data • Users are not allowed to directly manipulate the information in system tables, system stored procedures, and catalog views. • Users can administer the system and manage all users and database objects through: • SQL Server Management Studio • SQL Server Management Objects (SMO) application programming interface • Transact-SQL scripts and stored procedures modify System data

  6. Viewing System Data • Database applications view catalog and system information by using any of the following: • System catalog views • SQL Server management objects • Catalog functions, methods, attributes, or properties of the data API • Stored Procedures and Functions Ettreeryreyr Ertetertetet tettwet Viewing System data

  7. User-defined Databases • The information needed to create a user-defined database: • Name of the database • Owner of the database • Size of the database • Files and Filegroups used to store it Syntax for creating a database: CREATE DATABASE database_name [ ON [ PRIMARY ] [ <filespec> [ ,...n ] [ , <filegroup> [ ,...n ] ] [ LOG ON { <filespec> [ ,...n ] } ] ] [ COLLATE collation_name ] ] [;]

  8. User-defined Databases User database size can be expanded, shrunk or be dropped as per the need of the user. Syntax for modifying a database: ALTER DATABASE database_name { <add_or_modify_files> | <add_or_modify_filegroups> | <set_database_options> | MODIFY NAME = new_database_name | COLLATE collation_name }[;] The ownership of a user database can be changed using spchangedbowner stored procedure. Syntax for changing the owner using sp_changedbowner: sp_changedbowner [ @loginame = ] ‘login’

  9. User-defined Databases • For each database, the database options are unique. • The database options can be set using the SET clause of the ALTER DATABASE statement. • Following table shows details of options for SQL Server 2005:

  10. User-defined Databases • Example for creating database: CREATE DATABASE [Customer_DB] ON PRIMARY ( NAME = N’Customer_DB’, FILENAME = N’ C:\Program Files\Microsoft SQL Server\MSSQL.1\ MSSQL\Data\Customer_DB.mdf’) LOG ON ( NAME = N’Customer_DB_log’, FILENAME = N’ C:\Program Files\Microsoft SQL Server\MSSQL.1 \MSSQL\Data\Customer_DB_log.ldf’) COLLATE SQL_Latin1_General_CP1_CI_AS • Example for modifying database: ALTER DATABASE Customer_DB MODIFY NAME = CUST_DB

  11. User-defined Databases • Example for changing database owner: USE CUST_DB EXEC sp_changedbowner ‘sa’ • Example for setting database options: USE CUST_DB; ALTER DATABASE CUST_DB SET AUTO_SHRINK ON

  12. AdventureWorks Databases • AdventureWorks is the new addition to SQL Server 2005 sample databases. • Adventure Works Cycles is a fictitious large, multinational cycle manufacturing company introduced as a scenario in the database. • The sample database consists of the following parts: • AdventureWorks: Sample Online Transaction Processing (OLTP) database • AdventureWorksDW: Sample Data warehouse • AdventureWorksAS: Sample Analysis Services database

  13. Filegroups and Transaction Logs 1-7 • FILES: • Actual physical storage units • Contain database objects • Files have a logical and a physical name • Filegroups: • Collections of files that are grouped for better performance and easier management. • A single logical filegroup can contain multiple physical files. • Each filegroup is used to group related files that together store a database object. • Database objects like tables and indexes can be created on specific filegroups. • Filegroups can be taken as back ups and restored separately. • The following table shows the details of filegroups supported by SQL Server 2005:

  14. Filegroups and Transaction Logs 2-7 • Adding Filegroups to database: • Filegroups can be added for a new database at the time of database creation or for an existing database. • A file cannot be a member of more than one filegroup. • Files cannot be moved to a different filegroup after files have been added to the database. • Filegroups can contain only data files and no transaction log files.

  15. Filegroups and Transaction Logs 3-7 • Adding Filegroups during creation of database Syntax for filegroup addition while creating a database: CREATE DATABASE database_name [ ON [ PRIMARY ] [ <filespec> [ ,...n ] [ , <filegroup> [ ,...n ] ] [ LOG ON { <filespec> [ ,...n ] } ] ] [ COLLATE collation_name ] ] [;]

  16. Filegroups and Transaction Logs 4-7 • Adding Filegroups to an existing database Syntax for filegroup addition to an existing database: ALTER DATABASE database_name { <add_or_modify_files> | <add_or_modify_filegroups> | <set_database_options> | MODIFY NAME = new_database_name | COLLATE collation_name } [;]

  17. Filegroups and Transaction Logs 5-7 • Viewing Filegroup Information • Database, file, partition, and filegroup properties can be viewed using a variety of catalog views, system functions, and stored procedures. • Details of these functions are summarized in the table:

  18. Filegroups and Transaction Logs 6-7 • Transaction • Transaction is a set of operations that must happen together. • Actions are first recorded to transaction log. • When that is complete, operations applied to database. • Rollback and data recovery • Recovery of individual transactions • Recovery of all incomplete transactions when SQL Server is started • Rolling a restored database, file, filegroup, or page forward to the point of failure • Supporting transactional replication • Standby server solutions Transaction Log supports

  19. Filegroups and Transaction Logs 7-7 • Adding Log files to a database Syntax for log file addition to a database: ALTER DATABASE database_name { ... } [;] <add_or_modify_files>::= { ADD FILE <filespec> [ ,...n ] [ TO FILEGROUP { filegroup_name | DEFAULT } ] | ADD LOG FILE <filespec> [ ,...n ] | REMOVE FILE logical_file_name | MODIFY FILE <filespec> }

  20. Database Modification Methods 1-2

  21. Database Modification Methods 2-2 • Dropping a database • The files and the data associated with the database is automatically deleted from the disk when the database is deleted. Syntax: DROP DATABASE { database_name }

  22. Database Snapshots • Creating a database snapshot: • Database snapshot is a new feature in Microsoft SQL Server 2005 which provides a read-only, static view of a SQL Server database. • If user makes a mistake in the source database, the source database can be reverted to the previous state when the snapshot was created. Syntax: CREATE DATABASE database_snapshot_name ON ( NAME = logical_file_name, FILENAME = ‘os_file_name’ ) [ ,...n ] AS SNAPSHOT OF source_database_name [;]

  23. Database Snapshots Database created Data gets modified Snapshot is restored Snapshot created

  24. Database Snapshots Advantages Disadvantages Snapshot backup cannot be created. Provide a convenient, read-only copy of data. Snapshot must exist on the same database server as that of the source database. When queried, no deterioration of performance. A new user cannot be granted access to the data in a snapshot. Snapshot files are small and are very quick to create.

  25. Summary • SQL Server supports system as well as user-defined databases. • System databases are used to store information about SQL Server 2005, and manage user databases. • User-defined databases are used to store user information. • User-defined databases can be created using the CREATE DATABASE statement. • SQL Server 2005 filegroups contain data files. • A transaction log present in SQL Server 2005 records all transactions and modifications in the database, made by each transaction. • A database snapshot is a read-only, static view of a source database.

More Related