Managing Databases and Files Presentation 2 Overview Planning

Managing Databases and Files Presentation 2

Overview Planning Databases Creating Databases Managing Databases

Planning Databases How Data Is Stored How the Transaction Log Works Considerations for File Placement What Are Filegroups? Considerations for Filegroups Considerations for Capacity Planning

How Is Data Stored? . MDF is primary data file. . NDF are secondary data files to allow spanning of data over multiple disks… Data file: . mdf or. ndf Extent: 8 contiguous 8 KB pages Page: 8 KB Log file: . ldf With very large databases it is suggested the log be on its own disk for enhanced performance…

How the Transaction Log Works 1 Data modification is sent by application Buffer Cache 3 Modification is recorded in transaction log on disk Disk 2 Data pages are located in, or read into, buffer cache and modified Disk 4 Checkpoint writes committed transactions to database

Considerations for File Placement Types of files Primary Secondary Transaction logs File placement Place files on different disks for performance and recovery

Filegroups A collection of files that has a name and are used to simplify placement of data Can be over multiple disks Can improve performance There is always one primary filegroup with the system tables and can contain other files Secondary or user-defined filegroups can contain data files that need to be grouped for administrative or other purposes A common practice is to put tables on one physical disk and put indexes on another disk for performance…

What Are Filegroups? Adventure. Works Database sys. . . sysusers sysobjects C: Adventure. Wor ks_ Data. mdf Primary Filegroup . . . Sales. Order. Header Customer Product Ord. Hist. Year 2 Ord. Hist. Year 1 E: D: Ord. Hist 1. n df Ord. Hist 2. n df Order. History. Group Adventure. Work s_ Log. Idf

Considerations for Filegroups Creating user-defined filegroups Performance Span databases over multiple disks Manageability Separate frequently updated data from data that is relatively static Data that needs to be backed up regularly vs data that does not Specifying the default filegroup This is automatically the primary filegroup unless you specify another Is thr filegroup that contains the pages for all tables and indexes that do not have a filegroup specifically set when they are created Using read-only filegroups Can mark as read-only for sensitive data that should not be modified…

Considerations for Capacity Planning Estimating the size of a database Size of model database Every db is initially created to this size Remember that every object added to a database (table, stored proc, user, etc) will add a row to some system table Predicted growth How much data will be stored in the database Indexes Size of the key values (long keys or composite keys? ) Fill factor (empty space reserved for future growth) Size of transaction log If a database is going to be updated a lot (transaction heavy) Transaction log should be 25% of the size of the main database If a database is going to be used mostly for querying (not many inserts, updates or deletes) Transaction log should be about 10% of the size of the database

Creating Databases Considerations for Creating Databases Syntax for Creating Databases What Are Schemas? Syntax for Creating Schemas How Name Resolution Works Practice: Creating Databases

Considerations for Creating Databases model database Primary Secondary Log Database files include primary, secondary, and log files Considerations for database size Initial size of data files Initial size of log files Potential growth of physical data storage

Syntax for Creating Databases CREATE DATABASE database_name [ ON [ PRIMARY ] [ filespec [, n] ] [ FILEGROUP filegroup_name [DEFAULT] filespec [, n] ] [ LOG ON [filespec [, n] ] ]

Sample Syntax – Create Database CREATE DATABASE Sales. Karen ON ( NAME = Sales. Karen_dat, FILENAME = '‘c: sqldatasaledat. mdf'', SIZE = 10, Simple MAXSIZE = 50, database creation FILEGROWTH = 5 ) specifying a main data file LOG ON ( NAME = Sales_log, and a log file. FILENAME = '‘C: sqldatasalelog. ldf'', SIZE = 5 MB, MAXSIZE = 25 MB, FILEGROWTH = 5 MB )

Another Create Database Example: • This example creates a database called Archive with three 100 -MB data files and two 100 -MB transaction log files. • The primary file is the first file in the list and is explicitly specified with the PRIMARY keyword. • The transaction log files are specified following the LOG ON keywords. • Note the extensions used for the files in the FILENAME option: • . mdf is used for primary files, • . ndf is used for the secondary files, and • . ldf is used for transaction log files.

USE master GO CREATE DATABASE Archive ON PRIMARY ( NAME = Arch 1, FILENAME = 'c: mssqldataarchdat 1. mdf', SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20), FILEGROUP SAMPLEFILEGRP ( NAME = Arch 2, FILENAME = 'c: mssqldataarchdat 2. ndf', SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20),

( NAME = Arch 3, FILENAME = 'c: mssqldataarchdat 3. ndf', SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = Archlog 1, FILENAME = 'c: mssqldataarchlog 1. ldf', SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Archlog 2, FILENAME = 'c: mssqldataarchlog 2. ldf', SIZE = 100 MB, MAXSIZE = 200, FILEGROWTH = 20) GO

What Are Schemas? Organize database objects into categories or “namespaces” Person Contact (Server 1. Adventure. Works. Person. Conta ct) Sales Customer (Server 1. Adventure. Works. Sales. Custom er) dbo Error. Log (Server 1. Adventure. Works. dbo. Error. Log) Adventure. Wor ks

What is a schema Objects in a database are created inside schemas A namespace for database objects Does not have anything to do with ownership Schemas can be granted permissions and inherited by the objects within the schema – users are given rights to the schema… Adventureworks uses the following schemas: Human. Resources Person Production Purchasing Sales The fully qualified name for database objects is: Server. database. schema. object Within a database this can be shortened to schema. object Every database has the dbo schema. Default schema for those users who do not have a default schema defined

Syntax for Creating Schemas CREATE SCHEMA schema_name | AUTHORIZATION owner_name | schema_name AUTHORIZATION owner_name [ table_definition | view_definition | grant_statement | revoke_statement | deny_statement ]

Sample Syntax for Create Schema USE Karen. Sales go CREATE SCHEMA Jasmine AUTHORIZATION Spike CREATE TABLE Dog. Food (source int, cost int, dogbiscuit. Count int) GRANT SELECT TO Spot DENY SELECT TO Buddy GO Creates a schema owned by Spike (who must have create table permissions granted) A table is created and permissions are granted to users Spot and Buddy

How Object Name Resolution Works Person SELECT * FROM Contact Lance (Default schema = Person) SELECT * FROM Person. Contact Sales SELECT * FROM Error. Log SELECT * FROM Contact Anders (Default schema = Sales) In SQL 2005 users can be assigned a default schema to avoid confusion when two tables in the same database have the same name but are in different schemas…. dbo Error. Log

Practice: Creating Databases Create a database using SQL Server Management Studio Create a database using Transact. SQL

2. Use Management Studio Expand your server Right-click on databases folder select New Database Create a database to the following specs: name: sample_msmnnnn (nnnn = some unique number) size: 2 mb auto grow the files growth size = 2 mb max size 15 mb create log w/sizes half of DB and max size of 5 MB

3. Use T-SQL statement In Query Window write a Create Database statement to create: name: sample_sqlnnnn (nnnn = some unique number) size: 2 mb path is default path. . . auto grow the files growth size = 2 mb max size 15 mb create log w/sizes half of DB and max size of 5 MB Refer to syntax demonstrated in this presentation or Transact SQL Help files for examples….

Dropping a database You can drop or delete a database using the Enterprise Manager or SQL statements when you drop a database, its logical name and its disk files are deleted permanently. Syntax is Drop databasename […n, ] Example: Drop Database mydb 1, mydb 2

Advantage of SQL command is that you can drop multiple dbs at once… Login id’s that had dropped db as the default db will not longer have a default db… Back up the Master after dropping a db Does not allow dropping of Master, tempdb or model… Cannot drop a database in the process of being restored, open for writing or publishing tables in replication

Now you do something else Drop your two databases created in the previous “Now you do…” using a SQL statement… Execute the system stored procedure sp_helpdb to insure that your databases were dropped “Sp_helpdb pubs” Gives all the information (“metdata” about pubs database) including database options set….

Lesson 3: Managing Databases Database Options Sources of Database Metadata Demonstration: Retrieving Database Metadata

Database Options Set Database Options by using: SQL Server Management Studio ALTER DATABASE statement Option Category Purpose Automatic Controls automatic behaviors such as statistics, database closing, and shrinking Availability Controls whether database is online, who can connect to it, and whether database is read-only Cursor Controls cursor behavior and scope Recovery Controls the recovery model for the database SQL Controls ANSI compliance options such as ANSI nulls and recursive triggers

System stored procedures to display information on database sp_helpdb - reports on all databases on a server sp_helpdb databasename - reports on the databasename requested… sp_spaceused [objectname] - summarizes the storage used by current database or table

Database Options Exec sp_helpdb a list of all databases Exec sp_helpdb Pubs Information on that database including database options set… Use pubs Exec sp_spaceused information about space used in pubs

Database Options Setting options Most current implementation to set database options is to use the Alter Database statement “alter database pubs set read_only” Sets the database to “read-only” status can also be set in Management Studio by right-clicking database and choosing Properties click on Options link on the left side of the window…. See SQL Server Help… Alter Database Statement….

Managing Databases Use the Alter Database statement or Management Studio to modify the database parameters file size log size adding secondary files

Increasing Size and using a secondary file: Alter Database sample modify file (name = ‘sample_data’, size = 20 mb) go Alter database sample add file (name = ‘sample_data 2’, filename = ‘c: mssqldatasample 2. ndf’, size = 10 MB, maxsize = 20 MB) go

Modifying the Database Using secondary files (filegroups) use when you want to spread db across multiple disks and do not have disk striping RAID solution implemented… To use Management Studio to add a file: Go to your database’s properties window Click the ADD button at the bottom of the window fill in the empty row of the file box and add a new file name and any additional parameters

Increase Log size To use SQL to increase the current log size to 10 MB Alter Database sample modify file (name = ‘sample_log’, size = 10 MB) go

Now you try: Using SQL statement/s increase the size of the log in your Pubs database to one MB more than it is now… Use Management Studio to change the increment to a fixed increment of 2 MB rather than a percentage. Use SQL statement/s to create a secondary file for your Pubs database put it on the default drive and path and add a ‘ 2’

More Practice – Options with T-SQL Syntax Using Pubs, set the database option that will automatically shrink the log and the database files if necessary. Using Pubs, set the option that will not allow updates, inserts or deletes to the database. Try this statement after the option has been set: Update titles set price = 10. 0 Using Pubs, set the option above so that updates CAN be made to the database Try these statements after the option has been set: Update titles set price = 10. 00 Select title, price from titles Write the T-SQL syntax to check that the options were set as indicated above. Drop YOUR pubs database and then recreate it using your “Inst. Pubs. sql” script.

Assignment Complete the Create Database assignment and turn in as described in the specifications… Specifications are contained in a Word Document on the network drive (Karen will go over in class…)