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


Change Data Capture -Part2

June 17, 2009

How To Process Change Data Capture (CDC) in SQL Server Integration Services SSIS 2008

Assume that we have a customer table and we want to store all changes to our customer table in a customer_audit table.  We’ll write an SSIS package to query the CDC data and copy it to the customer_audit table.  We would like the option of running the SSIS package on demand and/or on a schedule (e.g. a SQL Agent job).  Each time we run the SSIS package we want to pickup whatever changed since the last time we ran the package.

Customer Tables

We will use the following customer table:

create table dbo.customer (

customer_id int identity primary key not null

, name nvarchar(50) not null

, sales_rep nvarchar(50) not null

, region nvarchar(50) not null

, credit_limit int not null


We will use the following customer_audit table to store changes to the customer table:

create table dbo.customer_audit (

customer_audit_id int identity primary key not null

, customer_id int not null

, name nvarchar(50) not null

, sales_rep nvarchar(50) not null

, region nvarchar(50) not null

, credit_limit int not null

, effective_date datetime not null

, __$start_lsn binary(10) not null

, __$seqval binary(10) not null

, __$operation int not null

, __$update_mask varbinary(128) not null


Enabling CDC

As we see before.


In order to allow our SSIS package to pickup just the changes since the last time it was run, we’ll populate a log table with the data we need.  We’ll use the following log table:

create table dbo.cdc_capture_log (

cdc_capture_log_id int identity not null

, capture_instance nvarchar(50) not null

, start_time datetime not null

, min_lsn binary(10) not null

, max_lsn binary(10) not null

, end_time datetime null

, insert_count int not null default 0

, update_count int not null default 0

, delete_count int not null default 0

, status_code int not null default 0


We’ll create two stored procedures to maintain the log:

  • init_cdc_capture_log will create a new row.
  • end_cdc_capture_log will update the row.

The init_cdc_capture_log is called at the beginning of our SSIS package.  It is shown below:

create procedure dbo.init_cdc_capture_log

@capture_instance nvarchar(50)



set nocount on;


@begin_lsn binary(10)

, @end_lsn binary(10)

, @prev_max_lsn binary(10)

— get the max LSN for the capture instance from

— the last extract

select @prev_max_lsn = max(max_lsn)

from dbo.cdc_capture_log

where capture_instance = @capture_instance

— if no row found in cdc_capture_log get the min lsn

— for the capture instance

if @prev_max_lsn is null

set @begin_lsn = sys.fn_cdc_get_min_lsn(@capture_instance)


set @begin_lsn = sys.fn_cdc_increment_lsn(@prev_max_lsn)

— get the max lsn

set @end_lsn = sys.fn_cdc_get_max_lsn()

insert into dbo.cdc_capture_log




select cast(scope_identity() as int) cdc_capture_log_id


The end_cdc_capture_log stored procedure updates the row created  by the init_cdc_capture_log stored procedure.  It is shown below:

create procedure dbo.end_cdc_capture_log

@cdc_capture_log_id int

, @insert_count int

, @update_count int

, @delete_count int



set nocount on;

update dbo.cdc_capture_log set

end_time = getdate()

, insert_count = @insert_count

, update_count = @update_count

, delete_count = @delete_count

, status_code = 1

where cdc_capture_log_id = @cdc_capture_log_id


Creating the SSIS Package

We will create an SSIS package that has the following control flow:


The main points about the above control flow are:

  • Init Log is an Execute SQL task; it calls the init_cdc_capture_log stored procedure (described above) and saves the identity value of the created cdc_capture_log row in a package variable.
  • Process Changes is a Data Flow task that retrieves the latest changes from the CDC table and copies them to our audit table.
  • End Log is an Execute SQL task that calls the end_cdc_capture_log stored procedure (described above) to update the cdc_capture_log row.

The Process Changes Data Flow task is implemented as shown below:


The main points about the above data flow are:

  • Extract Customer Changes is an OLE DB Source that executes the stored procedure extract_customer_capture_log to retrieve the customer changes since the last run.
  • Count Inserts Updates and Deletes is a Script Component Transform task that just counts the number of inserts, updates and deletes in the change data.
  • Save Customer Changes to Custom Audit Table is an OLE DB Destination used to insert each change row into the customer_audit table.

The extract_customer_capture_log stored procedure is shown below:

create procedure dbo.extract_customer_capture_log

@cdc_capture_log_id int



set nocount on;


@begin_lsn binary(10)

, @end_lsn binary(10)

— get the lsn range to process


@begin_lsn = min_lsn

, @end_lsn = max_lsn

from dbo.cdc_capture_log

where cdc_capture_log_id = @cdc_capture_log_id

— extract and return the changes

select m.tran_end_time modified_ts, x.*

from cdc.fn_cdc_get_all_changes_customer_all(

@begin_lsn, @end_lsn, ‘all’

) x

join cdc.lsn_time_mapping m

on m.start_lsn = x.__$start_lsn ;


The main points about the above stored procedure are:

  • The cdc_capture_log_id parameter value is the value returned from the call to the init_cdc_capture_log stored procedure (described above in the Logging section).
  • Retrieve the LSN range from the cdc_capture_log table row.  The LSN range represents all changes that have occurred since the last run of the SSIS package.
  • The cdc.fn_cdc_get_all_changes_customer_all function is generated when you enable CDC.  The function name includes the capture instance.  The function returns the changes that occurred in the LSN range.
  • The cdc.lsn_time_mapping table is populated by CDC with the mapping of transaction times to LSNs.  The join retrieves the transaction time.  This alleviates the need to manually track this in the source table.

Testing the SSIS Package

Before running the SSIS package, we need to execute a script that performs some inserts, updates and deletes.  We’ll use the following script:

use SqlHero


insert into dbo.customer



(N’BGM Systems’, N’Cane’, N’South’, 2500)

update dbo.customer

set sales_rep = N’Smith’

where [name] = N’BGM Systems’

update dbo.customer

set credit_limit = 3000

where [name] = N’BGM Systems’

delete dbo.customer

where [name] = N’BGM Systems’

delete dbo.customer

where [name] = N’BGM Systems’

After running the above script, execute the SSIS package, then check the customer_audit table to see that there are four rows; one for each change made in the script.  The partial contents of the customer_audit table are shown below:


The main points about the above table are:

  • effective_date is the date and time of the transaction, as retrieved from the cdc.lsn_time_mapping table.
  • Row 1 shows the insert; __$operation=2 for an insert.
  • Row 2 shows the update of the sales_rep; __$operation=4 for the update showing the values after the update.
  • Row 3 shows the update of the credit_limit.
  • Row 4 shows the delete; __$operation=1 for a delete.

The effective_date column provides the ability to query the customer_audit table and see the customer values at any point in time by filtering on the maximum effective_date that is less than or equal to some value.

How to create time Dimension Table using T-SQL Query between two years

June 16, 2009

How to create time Dimension Table using T-SQL Query from year 2000 to 2029

My Issue

  • I try to create a new olap solution  using  Microsoft sqlserver 2008 and I need to create the time dimension table  by using the time dimension in adventure works  but I find that  it’s from year 2000 to 2007 and we are now 2009



  • I Write a simple query to create the time dimension table  frbetween two years

The Query





fullDate datetime NOT NULL,

dayNumOfWeek tinyint NOT NULL,

dayNameOfWeek char(9) NOT NULL, — dayName is reserved word

dayNumOfMonth tinyint NOT NULL,

dayNumOfYear smallint NOT NULL,

monthNumOfYear tinyint NOT NULL,

monthNameOfYear char(12) NOT NULL,

monthOfQuarter tinyint NOT NULL,

calendarQuarter tinyint NOT NULL,

calendarSemester tinyint NOT NULL,

calendarYear char(4) NOT NULL



DECLARE @maxDateAlreadyLoaded datetime

DECLARE @dayNumOfWeek tinyint

DECLARE @dayNameOfWeek char(9) — dayName is reserved word

DECLARE @dayNumOfMonth tinyint

DECLARE @dayNumOfYear smallint

DECLARE @monthNumOfYear tinyint

DECLARE @monthNameOfYear char(12)

DECLARE @monthOfQuarter tinyint

DECLARE @calendarQuarter tinyint

declare @calendarSemester tinyint

DECLARE @calendarYear char(4)

DECLARE @endDate datetime

— Preload the date by check the data in the table for highest date already



SET @maxDateAlreadyLoaded = (SELECT TOP 1 DATEADD(day,1,fullDate) FROM

dimDate ORDER BY dateID DESC)


— here you Initialize the startdate of the Time Table

SET @maxDateAlreadyLoaded = ‘1/1/2000’

— Initialize the endDate to one year beyond the start value

— here you can Initialize the end year from the startdate in these case it will be 2029

SET @endDate = DATEADD(year, 29, @maxDateAlreadyLoaded)

— Initialize loop to load one year worth of data

WHILE (@maxDateAlreadyLoaded < @endDate)


— Calculate each of the fields (simplifies INSERT statement)

SET @dayNumOfWeek = DATEPART(weekDay, @maxDateAlreadyLoaded)

SET @dayNameOfWeek = (SELECT CASE

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 1 THEN ‘Sunday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 2 THEN ‘Monday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 3 THEN ‘Tuesday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 4 THEN ‘Wednesday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 5 THEN ‘Thursday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 6 THEN ‘Friday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 7 THEN ‘Saturday’


SET @dayNumOfMonth = DAY(@maxDateAlreadyLoaded)

SET @dayNumOfYear = DATEPART(dayOfYear, @maxDateAlreadyLoaded)

SET @monthNumOfYear = MONTH(@maxDateAlreadyLoaded)

SET @monthNameOfYear = (SELECT CASE

WHEN MONTH(@maxDateAlreadyLoaded) = 1 THEN ‘January’

WHEN MONTH(@maxDateAlreadyLoaded) = 2 THEN ‘February’

WHEN MONTH(@maxDateAlreadyLoaded) = 3 THEN ‘March’

WHEN MONTH(@maxDateAlreadyLoaded) = 4 THEN ‘April’

WHEN MONTH(@maxDateAlreadyLoaded) = 5 THEN ‘May’

WHEN MONTH(@maxDateAlreadyLoaded) = 6 THEN ‘June’

WHEN MONTH(@maxDateAlreadyLoaded) = 7 THEN ‘July’

WHEN MONTH(@maxDateAlreadyLoaded) = 8 THEN ‘August’

WHEN MONTH(@maxDateAlreadyLoaded) = 9 THEN ‘September’

WHEN MONTH(@maxDateAlreadyLoaded) = 10 THEN ‘October’

WHEN MONTH(@maxDateAlreadyLoaded) = 11 THEN ‘November’

WHEN MONTH(@maxDateAlreadyLoaded) = 12 THEN ‘December’


SET @monthOfQuarter = (SELECT CASE

WHEN MONTH(@maxDateAlreadyLoaded) IN(1,4,7,10) THEN 1

WHEN MONTH(@maxDateAlreadyLoaded) IN(2,5,8,11) THEN 2

WHEN MONTH(@maxDateAlreadyLoaded) IN(3,6,9,12) THEN 3


SET @calendarQuarter = DATEPART(quarter, @maxDateAlreadyLoaded)

SET @calendarSemester = (SELECT CASE

WHEN MONTH(@maxDateAlreadyLoaded) IN(1,2,3,4,5,6) THEN 1

WHEN MONTH(@maxDateAlreadyLoaded) IN(7,8,9,10,11,12) THEN 2


SET @calendarYear = YEAR(@maxDateAlreadyLoaded)


— Used the PRINTS to test values as script designed

PRINT ‘———– New Row ———–‘

PRINT ‘@maxDateAlreadyLoaded ‘ + CAST(@maxDateAlreadyLoaded as varchar(20))

PRINT ‘@dayNumOfWeek ‘ + CAST(@dayNumOfWeek as varchar(20))

PRINT ‘@dayNameOfWeek ‘ + CAST(@dayNameOfWeek as varchar(20))

PRINT ‘@dayNumOfMonth ‘ + CAST(@dayNumOfMonth as varchar(20))

PRINT ‘@dayNumOfYear ‘ + CAST(@dayNumOfYear as varchar(20))

PRINT ‘@monthNumOfYear ‘ + CAST(@monthNumOfYear as varchar(20))

PRINT ‘@monthNameOfYear ‘ + CAST(@monthNameOfYear as varchar(20))

PRINT ‘@monthOfQuarter ‘ + CAST(@monthOfQuarter as varchar(20))

PRINT ‘@calendarQuarter ‘ + CAST(@calendarQuarter as varchar(20))

PRINT ‘@calendarYear ‘ + CAST(@calendarYear as varchar(20))

PRINT ‘@endDate ‘ + CAST(@endDate as varchar(20))



— Load row into database


(fullDate, dayNumOfWeek, dayNameOfWeek, dayNumOfMonth, dayNumOfYear,

monthNumOfYear, monthNameOfYear, monthOfQuarter, calendarQuarter,




(@maxDateAlreadyLoaded, @dayNumOfWeek, @dayNameOfWeek, @dayNumOfMonth,


@monthNumOfYear, @monthNameOfYear, @monthOfQuarter, @calendarQuarter,



— Increase the date to load by one

SET @maxDateAlreadyLoaded = DATEADD(day, 1, @maxDateAlreadyLoaded)


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


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


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

Partition Table in SQL Server 2008

June 13, 2009

1. What is Partition table?

Data is partitioned horizontally by range

Partition Table

2. How to Create Partition Table?

You can create partition table by 2 ways

  1. programmability
  2. wizard in SQL Server 2008 New

But you must understand 2 things before creation Partition Tables

Partition Function: Partition functions define partition boundaries and Boundary values can be assigned to LEFT or RIGHT


Partition Scheme: A partition scheme assigns partitions to filegroups


3. How to Create Partition Table by wizard in SQL Server 2008?

Right click on table then go to storage then select Create Partition as in fig bellow


Select a column you want to use it in partition as in fig bellow


Create Partition function as in fig bellow


Create Partition Scheme as in fig bellow


Determine ranges as in fig bellow


Create Partition by select run immediately as in fig bellow


Congratulation your table now partitioned

4. How to Create Partition Table programmability?


1)Create database TestDb



( NAME = N’TestDb’, FILENAME = N’C:\db\TestDb.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),


( NAME = N’f1′, FILENAME = N’C:\db\f1.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),


( NAME = N’f2′, FILENAME = N’C:\db\f2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),


( NAME = N’f3′, FILENAME = N’C:\db\f3.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),


( NAME = N’f4′, FILENAME = N’C:\db\f4.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )


( NAME = N’TestDb_log’, FILENAME = N’C:\db\TestDb_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)


USE [TestDb]



3)Create Partition Function


Create Partition Function pf_Date(Date)

as range right

For values(’01/01/2006′,’01/01/2007′,’01/01/2008′)



3)Create Partition scheme


Create Partition Scheme ps_Date

AS Partition pf_Date




3)Create Partition Contact Table


CREATE TABLE [dbo].[Contact](

[ContactId] [int] IDENTITY(1,1) NOT NULL,

[FullName] [nvarchar](50) NULL,

[CreationDate] [date] NULL

)on ps_Date(CreationDate)



4)Insert rows to table


insert into dbo.Contact values(‘amosa’,’01/01/2006′)

insert into dbo.Contact values(‘mmosa’,’01/01/2007′)

insert into dbo.Contact values(‘zemo’,’01/01/2008′)

insert into dbo.Contact values(‘test1′,’05/01/2005’)

insert into dbo.Contact values(‘test2′,’05/01/2006’)

insert into dbo.Contact values(‘test3′,’05/01/2007’)

insert into dbo.Contact values(‘test4′,’05/01/2008’)

insert into dbo.Contact values(‘test5′,’05/01/2009’)



5)Query Table to know where did rows save


Select *,$partition.pf_Date(CreationDate)FileGroupNo From dbo.Contact

Be Ready for Microsoft SQL Server 2008 R2

June 4, 2009

to get more information about Microsoft SQLServer 2008 R2 you can visit SQLServer2008 R2