Microsoft SQL Server 2008 : High Availability Always On Technologies

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

How to migrate data from Excel Sheet to any type of database using sqlserver2008 integration services

My Issue

• When I try to make the packages responsible for migrating the lookups tables from the source database to my data warehouse  for my bi-solution I found that some lookups tables are not  located in database but in the code as ENUMS

Solution

1. I fill the lookups tables in Excel sheets and make package responsible for migrating data from the excel to my data warehouse database

Steps

1. I fill the lookups tables from the ENUMS  file in to Excel sheet every sheet in excel file present  one table

2. Sample for ENUMS  file

namespace REMS.Business

{

public enum UnitBookingResult

{

CanBeBooked = 0,

BookingExists = 1,

ContractExists = 2,

SalesBookingExists = 3,

SalesContractExists = 4,

NotMarkedForSale = 5,

IsVirtual,

NotEnoughArea,

SubMergedUnit,

InActiveUnit

}

2. The Excel Sheet Screenshot I Create

3. Open new integration services project from SQL Server Business Intelligence Development Studio

4. Select Excel source

5. Select Excel File Path

6. Select Table or View and choose the table you want it as your source from excel file

7. Add OLEDB Destination and connect it with the Excel source

8. Create  A New Connection

9. Select the database you want to transfer  the data to it

10. Choose the destination table

11. Map the columns from source table to the destination table

12. Make previous steps for the rest of the lookups table

Finally Run the Package

Change Data Capture -Part2

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.

Logging

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)

as

begin

set nocount on;

declare

@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)

else

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

(capture_instance,start_time,min_lsn,max_lsn)

values

(@capture_instance,getdate(),@begin_lsn,@end_lsn)

select cast(scope_identity() as int) cdc_capture_log_id

end

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

as

begin

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

end

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

as

begin

set nocount on;

declare

@begin_lsn binary(10)

, @end_lsn binary(10)

– get the lsn range to process

select

@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 ;

end

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

go

insert into dbo.customer

(name,sales_rep,region,credit_limit)

values

(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

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

Solution

 

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

The Query

 

CREATE TABLE dimDate

(

dateID int IDENTITY(1,1) PRIMARY KEY NOT NULL,

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

–loaded

IF EXISTS(SELECT fullDate FROM dimDate)

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

dimDate ORDER BY dateID DESC)

ELSE

– 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)

BEGIN

– 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’

END)

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’

END)

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

END)

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

end)

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))

PRINT ”

–*/

– Load row into database

INSERT INTO dimDate

(fullDate, dayNumOfWeek, dayNameOfWeek, dayNumOfMonth, dayNumOfYear,

monthNumOfYear, monthNameOfYear, monthOfQuarter, calendarQuarter,

calendarSemester,

calendarYear)

VALUES

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

@dayNumOfYear,

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

@calendarSemester,

@calendarYear)

– Increase the date to load by one

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

END

SQL Server Recovery Model Types

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

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

1. What is Partition table?

Data is partitioned horizontally by range

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

****************************************************/

CREATE DATABASE [TestDb] ON PRIMARY

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

FILEGROUP [fg1]

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

FILEGROUP [fg2]

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

FILEGROUP [fg3]

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

FILEGROUP [fg4]

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

LOG ON

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

GO

USE [TestDb]

GO

/***************************************************

3)Create Partition Function

****************************************************/

Create Partition Function pf_Date(Date)

as range right

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

go

/***************************************************

3)Create Partition scheme

****************************************************/

Create Partition Scheme ps_Date

AS Partition pf_Date

to(fg1,fg2,fg3,fg4)

go

/***************************************************

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)

GO

/***************************************************

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′)

go

/***************************************************

5)Query Table to know where did rows save

****************************************************/

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

SQL Server 2008 Vs MySQL

visit bellow link to know more information about SQL Server compared to MySQL

http://www.microsoft.com/sqlserver/2008/en/us/compare-mysql.aspx

SQL Server 2008 Vs IBM DB2

visit bellow link to know more information about SQL Server compared to IBM DB2

http://www.microsoft.com/sqlserver/2008/en/us/compare-ibm-db2.aspx

SQL Server 2008 Vs Oracle

visit bellow link to know more information about SQL Server compared to Oracle

http://www.microsoft.com/sqlserver/2008/en/us/compare-oracle.aspx