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)
–          Filegroups name(depends on you)
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:
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.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

Create a website or blog at

Up ↑

%d bloggers like this: