Microsoft SQL Server 2008 : High Availability Always On Technologies

November 28, 2009

Here are the list of options that can help you in deciding and managing high availabilty options in SQL Server 2008

Increased Availability

  • Database Mirroring
  • Log Shipping
  • Failover Clustering
  • Geographically Dispersed Failover Clustering
  • Peer-to-Peer Replication

Decreased Downtime

  • Fast Database Recovery
  • Backup and Restore
  • Checksum on Data Pages
  • Online Index Operations
  • Online, Piecemeal, and Page-Level Restore
  • Partial Database Availability
  • Snapshot Isolation

Dynamic Configuration

  • Enhanced Manageability
  • Database Snapshots
  • Table and Index Partitioning
  • Backup and Restore
  • Dedicated Administrator Connection
  • Resource Governor

for More info please check the white paper of Microsoft SQL Server 2008 High Availabilty – Always on Technologies
http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-high-availability.aspx

Advertisements

SQL Server Recovery Model Types

June 16, 2009

SQL Server has 3 recovery model types which control types of backups so let’s discuss in more details all recovery types

when you do these operation sequential

right click any database—> properties –> select Pages –> options

you will see the dialog bellow

untitled

as you see in fig above you have 3 types

the relationship between backup types(Rows Headers) and Recovery Models(Columns Header) are

  Full Bulk-logged Simple
Full Backup Yes Yes Yes
Differential Backup Yes Yes Yes
Transaction Backup Yes Yes No

it’s very clear that you can’t make trasaction log backup in simple mode.

so what is the Differential between Full mode and Bulk-Logged mode?

Full mode log any transaction in Transaction log file but Bulk-logged can’t log huge transaction to log file like bulk insert or create index in huge tables

you can use Bulk logged for more performance to insert bulk data to database because in this mode you will not log in  transaction log.

you can use Full mode to log any small things to transaction log file to feel safe when to return to end point of disaster


SQL Server Major Backup Types

June 15, 2009

we have many types of backups but in this article i just discuses 3 types of backups

  1. Full Backup:- Backup all data and database objects
  2. Differential Backup:- Backup all data and database objects from last full backup
  3. Transaction Log Backup:- Backup all transaction from Transaction log file from last official backup official backup can be(Full backup or differential backup or transaction log backup)

let’s discuss fig bellow

Backup

F1 is full backup from database 

T1 is Transaction Log backup with difference from F1

D1 is differential backup with difference from F1

T2 is Transaction Log backup with difference from D2

D2 is differential backup with difference from F1

T3 is Transaction Log backup with difference from D2

D3 is differential backup with difference from F1

T4 is Transaction Log backup with difference from D3

D4 is differential backup with difference from F1

T5 is Transaction Log backup with difference from D4

D5 is differential backup with difference from F1