DATABASE Configuration and Maintenance


Files and Filegroups

SQL Server to store in the database uses two different types of files: data and transaction log files. Instead of defining the storage of objects directly to a data file, SQL Server provide an abstraction layer for more flexibility called a filegroup.

File extensions – The extensions is nothing more than a naming convention, and has absolutely no effect on SQL Server itself.
SQL Server uses three file extensions: .mdf, .ndf, .ldf

Because accessing a disk drive is much slower than accessing memory, the data file design underneath a database can have an impact on performance.
Designing the data layer of database begins with the database creation. When you create a database, it should have three files and two filegroups.

.mdf – PRIMARY(filegroup name)
.ndf
–          Filegroups name(depends on you)
.ldf
tempdb Database
SQL Server uses tempdb for worktables used in grouping/sorting operations, worktables to support cursors.
Database options

  • Recovery
  • Auto Options
  • Change tracking
  • Access
  • Parameterization

Recovery every database within SQL Server instance has a property setting called the recovery model. The recovery model determines the types of backups you can perform against a database: Full, Bulk-logged, Simple.
Auto Options there are five options:
Auto_Close
Auto_Shrink
Auto_Create_Statistics
Auto_Update_Statistics
Auto_Update_Statistics_Asynch
Change tracing is to ensure that the changes of one user do not accidentally overwrite the changes of another.
Access the status of a database can be explicitly set on ONLINE, OFFLINE, EMERGENCY(db_owner, Select)
Normal mode: Online, Read_Write, Multi_User.
Parameterization when a database call is parameterized, the value are passed as variables.

© 2011 Atdhe Buja
All rights reserved.

Advertisements

About Buja Atdhe

CEO of the platform
Gallery | This entry was posted in Personal Blogs, SQL Server 2008 and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s