1 / 30

Database Configuration and Maintenance

Database Configuration and Maintenance. Database Configuration and Maintenance Exam 70-432. 1 / 30. Confi guring Files and Filegroups. Database Configuration and Maintenance. Confi guring Database Options. Configuring Files and Filegroups

marlin
Download Presentation

Database Configuration and Maintenance

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. Database Configurationand Maintenance Database Configuration and Maintenance Exam 70-432 1 / 30

  2. ConfiguringFiles and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options Configuring Files and Filegroups SQL Server uses two different types of files—data and transaction log files. Filegroups are a logical structure, defined within a database, that map a database and the objects contained within a database, to the data files on disk. Filegroups can contain more than one data file. The proportional fill algorithm is designed to allow a resize operation to occur at a filegroup level. In other words, all files within a filegroupexpand at the same time. Maintainig Database Integrity 2 / 30

  3. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options File Extensions (justnamingconvention) : .mdf - primaryfilegroup, .ndf - all other data files, .ldf - transactionlogs Maintainig Database Integrity 3 / 30

  4. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options You should have a file with an .mdf extension within a filegroupnamed PRIMARY, a file with an .ndf extension in a filegroup with any name that you choose, and the transaction log with an .ldfextension. Besides being the logical definition for one or more files that defines the storage boundary for an object, filegroupshave a property called DEFAULT. ALTER DATABASE <database name> MODIFY FILEGROUP <filegroup name> DEFAULT Following the initial creation of the database, you add filegroupsas needed to separate the storage of objects within the database. You also add files to filegroupsto increase the disk I/O available to the objects stored on the filegroup, thereby reducing disk bottlenecks. Maintainig Database Integrity 4 / 30

  5. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options TransactionLogs all changes are written to the transaction log prior to a commit being issued. In addition, the writes to the transaction log must occur directly to disk. SQL Server writes sequentially to the transaction log data files underneath a databaseareisolating the transaction log to a dedicated set of disks Maintainig Database Integrity 5 / 30

  6. Configuring Files and Filegroups Database Configurationand Maintenance 6 / 30

  7. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options FILESTREAM data The files are stored in a folder on the operating system, but are linked directly into a database where the files can be backed up, restored, full-text-indexed, and combined with other structured data You define the location for FILESTREAM data in a database by designating a filegroupwithin the database to be used for storage with the CONTAINS FILESTREAM property. The FILENAME property defined for a FILESTREAM filegroup specifies the path to a folder. Maintainig Database Integrity 7 / 30

  8. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options tempdbDatabase SQL Server uses tempdbfor worktables used in grouping/sorting operations, worktables to support cursors, the version store supporting snapshot isolation level, and overflow for table variables. You can also cause index build operations to use space in tempdb. Maintainig Database Integrity 8 / 30

  9. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options LessonSummary You can define one or more data and log fi les for the physical storage of a database. Data fi les are associated to a fi legroup within a database. Filegroupsprovide the logical storage container for objects within a database. Files can be stored using the new FILESTREAM capabilities. Maintainig Database Integrity 9 / 30

  10. Database Configurationand Maintenance 10 / 30

  11. Database Configurationand Maintenance 11 / 20

  12. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options Configuring Database Options The recovery models available in SQL Server 2008 are: Full Bulk-logged Simple BEST PRACTICES RECOVERY MODELS Every production database that accepts transactions should be set to the Full recovery model. By placing the database in the Full recovery model, you can maximize the restore optionsthatarepossible. Maintainig Database Integrity 12 / 30

  13. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options THE BULK-LOGGED RECOVERY MODEL BCP BULK INSERT SELECT. . .INTO CREATE INDEX ALTER INDEX. . .REBUILD Bulk-logged recovery model does not log every change to the transaction log, you cannot recover a database to a point in time Maintainig Database Integrity 13 / 30

  14. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options THE SIMPLE RECOVERY MODEL exactly as the Full recovery model But Simple recovery model cannot be recovered to a point in time. Eachtime the database checkpoint process executes, the committed portion of the transaction log is discarded. Maintainig Database Integrity 14 / 30

  15. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options ALTER DATABASE database_name SET RECOVERY { FULL | BULK_LOGGED | SIMPLE } Maintainig Database Integrity 15 / 30

  16. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options DamagedPages The PAGE_VERIFY CHECKSUM option can be enabled, which allows you to discover and log damaged pages Maintainig Database Integrity 16 / 30

  17. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options Auto Options AUTO_CLOSE AUTO_SHRINK AUTO_CREATE_STATISTICS AUTO_UPDATE_STATISTICS AUTO_UPDATE_STATISTICS_ASYNCH Maintainig Database Integrity 17 / 30

  18. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options ChangeTracking To prevent the changes of multiple usersfrom overridingeachother, each row in a table that has been enabled for change tracking Maintainig Database Integrity 18 / 20

  19. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options Access Access to a database can be controlled through several options The status of a database: ONLINE, OFFLINE, orEMERGENCY (db_ownerrole) READ_ONLY orREAD_WRITE SINGLE_USER, RESTRICTED_USER(db_owner, dbcreator, and sysadminroles), and MULTI_USER ROLLBACK, ROLLBACK IMMEDIATE, ROLLBACK AFTER <number of seconds> Maintainig Database Integrity 19 / 20

  20. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options CollationSequences SQL Server has the capability to store character data that spans every possible written language The collation sequence for an instance can be overridden at a database level by specifying the COLLATE clause in either the CREATE DATABASE or ALTER DATABASE command. Maintainig Database Integrity 20 / 30

  21. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options LessonSummary You can set the recovery model for a database to Full, Bulk-logged, or Simple. You can back up transaction logs for a database in the Full or Bulk-logged recovery model. The AUTO_SHRINK option shrinks a database fi le when there is more than 25 percent of free space in the fi le. You can track and log damaged pages by enabling the PAGE_VERIFY CHECKSUM option. Maintainig Database Integrity 21 / 30

  22. Database Configurationand Maintenance 22 / 30

  23. Database Configurationand Maintenance 23 / 30

  24. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options Maintaining Database Integrity Database IntegrityChecks PAGE_VERIFY. The page verification can be set to either TORN_PAGE_DETECTION or CHECKSUM. The PAGE_VERIFY TORN_PAGE_DETECTION option exists for backwards compatibility and should not be used BEST PRACTICES PAGE VERIFICATION You should enable the PAGE_VERIFY CHECKSUM option on every production database Maintainig Database Integrity 24 / 30

  25. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options When DBCC CHECKDB is executed, SQL Server performs all the following actions: Checks page allocation within the database Checks the structural integrity of all tables and indexed views Calculates a checksum for every data and index page to compare against the stored checksum Validates the contents of every indexed view Checks the database catalog Validates Service Broker data within the database Maintainig Database Integrity 25 / 30

  26. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options To accomplish these checks, DBCC CHECKDB executes the following commands: DBCC CHECKALLOC, to check the page allocation of the database DBCC CHECKCATALOG, to check the database catalog DBCC CHECKTABLE, for each table and view in the database to check the structural integrity Maintainig Database Integrity 26 / 30

  27. Configuring Files and Filegroups Database Configurationand Maintenance ConfiguringDatabase Options LessonSummary The PAGE_VERIFY CHECKSUM option should be enabled for every production database to detect any structural integrity errors. When a corrupt page is encountered, the page is logged to the suspect_pages table in the msdbdatabase. If a database is participating in a Database Mirroring session, SQL Server automatically retrieves a copy of the page from the mirror, replaces the page on the principal, and logs an entry in the sys.dm_db_mirroring_auto_page_repairview. DBCC CHECKDB is used to check the logical and physical consistency of a database. Maintainig Database Integrity 27 / 30

  28. Database Configurationand Maintenance 28 / 30

  29. Database Configurationand Maintenance 29 / 30

  30. Database Configurationand Maintenance Dziękuję za uwagę Tomasz Gacek 30 / 30

More Related