What is the difference between Web Farm and Web Garden ?

I have been asked this question many times by different readers of my blog. They wanted to know about the fundamentals of Web Farms and Web Garden . In this blog post  I am going to explain the what is the exact difference between web farm and web garden, what are the advantages and disadvantages of using them. I have also described how to create web garden in different version of IIS.
Overview :
Visual Studio having its own integrated ASP.NET engine which is used to run the ASP.NET Web application from Visual Studio. ASP.NET Development Server  is responsible for execute all the request and response from client. Now after the end of development, when you want to host the site on some server to allow other peoples to access, concept of web servers comes in between.  A web server is responsible for  provide the response for all the requests that are coming from clients. Below diagram showing the typical deployment structure of a ASP.NET Web application with a single IIS.

Clients request for resources and IIS Process the request and send back to clients.

Web Farm :

This is the case, where you have only one web server and multiple clients requesting for the resources from the same server. But when there is huge numbers of  incoming traffic for your web sites, one standalone server is not sufficient to process the request.  You may need to use multiple server to host the application and divide the traffic among them.  This is called “Web Farm” . So when you are hosting your single web site on multiple web server over load balancer called “Web Farm”. Below diagram showing the over all representation of Web Farms.

 

In general web farm architecture, a single application is hosted on multiple IIS Server and those are connected with the VIP ( Virtual IP ) with Load Balancer. Load Balancer IP’s exposed to external worlds to access. So whenever some request will come to server from clients, it will first hit the Load Balancer, then based on the traffic on each server LB distributed the request to corresponding web server.  These web server may share same DB server or may be they can use replicated server in the back end.
So, In a single statement, When we host a web application over multiple web server to distributed the load among them  is called Web Farm.
Web Garden :
Now, let’s have a look, what is Web Garden ?  Both the terms  sounds same,  but they are totally different with each other.  Before starting with Web Garden, I hope you have fundamental idea of what is Application Pool and what is Worker Process. 
Just to recall,  When we are talking about requesting processing with in IIS, Worker Process (w3wp.exe ) takes care all of these. Worker Process runs the ASP.Net application in IIS. All the ASP.Net functionality inside IIS  runs under the scope of worker process. Worker Process is responsible for handling all kind of request, response, session data, cache data.  Application Pool is the container of worker process. Application pools is used to separate sets of IIS worker processes and enables a better security, reliability, and availability for any web application.
Now, by default each and every Application pool contains a single worker process. Application which contains the multiple worker process called “Web Garden”. Below is the typical diagram for a web garden application.
In the above diagram you can see, on of the application containing the  multiple worker process, which is now a web garden.
So, a Web application hosted on multiple server and access based on the load on servers is called Web Farms and When a single Application pool contain multiple Worker process is called web garden.
Create Web Garden in IIS 6 and IIS 7
Now, I am going to show how you can change the Number of Worker process In both IIS 6 and IIS 7.  For IIS 6,  Right Click on Application Pool > Properties > Goto  Performance Tab.

In the “Performance Tab” Section you would have one option called “Web Garden” where worker process sets to “1”, you can set the number of worker process that you required.
For IIS 7, Right Click on Application Pool > Go To Advance Settings > In Process Model section, you will have “Maximum Worker Processes” . You can change it more than 1 to make it as web garden.

In the above image you can also check the definition of Web Garden also.


Advantages of Web Farm and Web Garden :
Now, let’s have a look in to the advantages of both the Web farms and Web Garden.
Advantages of Web Farm

• It provides high availability. If any of the server in the farm goes down, Load balancer can redirects the requests to other servers.
• Provides high performance response for client requests.
• Provides Better scalability of the web application and reduce the failure of application.
• Session and other resource can be stored in a centralized location to access by the all server.

Advantages of Web Garden:

• provides better application availability by sharing request between multiple worker process.
• Web garden use processor affinity where application can swapped out based on preference and tag setting.
• Less consumption of physical space for web garden configuration.

How to manage session in Web Farm Mode ?
While using session, requests are distributed among different servers. By default session mode is set to In Proc where session data stored inside worker process memory. But, In Web farm mode we can share the session among all the server using a single session store location my making it Out proc (State Server or SQL Server Mode). So, if some of the server goes down and request transferred to the other server by the Load balancer session data should be available for that request.
In the above diagram,  you can see we can both the IIS server sharing the same session data which is stored in out of worker process.
How to manage session in Web Garden Mode ?
When we are using Web garden where request is being taking care by different worker process we have to make the session mode as out process session mode as described earlier. For Web Garden we have configure the out process with in same server but for different worker process.

While using Web garden with your application you need do couple of configuration settings in web.config in <process Model> section where you need to set certain properties like cpuMask, RequestLimit, webGarden, ClientConnectCheck etc.
Summary : When we host a web application over multiple web server to distributed the load among them  is called Web Farm and when One application having multiple worker worker process called Web garden.

Handling Division By Zero Scenarios in SQL using NULLIF

Sometimes it is inevitable to encounter scenarios that will give division by zero errors

?

DECLARE @dividend INT DECLARE @divisor INT SET @dividend = 1 SET @divisor = 0 SELECT @dividend/@divisor /* Error: Msg 8134, Level 16, State 1, Line 7 Divide by zero error encountered. */

What you can do is you can code around it, so your users and your app do not get this error.

Alternative 1: NULLIF (preferred)

The NULLIF built in function returns a NULL if the two parameters are equal. In our case, we want to check if the divisor is zero.

?

DECLARE @dividend INT DECLARE @divisor INT SET @dividend = 1 SET @divisor = 0 SELECT @dividend/NULLIF(@divisor,0) /* Returns NULL */

Alternatively, instead of NULL, you may want to display just 0

?

SELECT ISNULL(@dividend/NULLIF(@divisor,0),0) /* Returns NULL, no error */

Alternative 2: CASE

You can also use CASE to drive what values you want to show if the divisor. The downside to this approach is your code can get really lengthy right away by having multiple CASE statements.

?

SELECT     CASE @divisor        WHEN 0 THEN 0        ELSE @dividend/NULLIF(@divisor,0)     END /* Returns 0, no error */

Alternative 3: IF/ELSE

You can also use IF/ELSE. However this means you cannot just have one SELECT statement. This needs to be in a script, a stored proc, or UDF.

?

IF @divisor = 0 BEGIN    SELECT 0 END ELSE BEGIN    SELECT @dividend/@divisor END /* Returns 0, no error */

What's the difference between a Debug vs Release Build?

When you compile your application in release mode debug information is not generated and this is the option normally used when you want to deploy your application to client.debug mode is heavy since debugging info gets generated and is used while development to find out error in your code.
When we are moving code to production then we make the mode as Release. Because there no debug is required and the execution also very fast in this mode due to the .pdb file formation will not happens.

Debug build
1. Basically this for developer
2. Complier code optimization is OFF

Release build
1. Basically this for Client to whom you want to distribute the application
2. Complier code optimization is ON

How to call a specific base constructor in C#?

How to call a specific base constructor in C#?

What is a Constructor? - It is a method that gets invoked when an instance of a class is created. In case a class has plenty of constructors, i.e. there are plenty of overloaded constructors, in such a scenario, it is still possible to invoke a specific base constructor. But there is a special way, as explicit calls to a base constructor is not possible in C#. See code below:

C# Example
public class dotnetClass
{
public dotnetClass()
{
// The constructor method here
}
// Write the class members here
}

// below shows how to overload a constructor
public class dotnetClass
{
public dotnetClass()
{
// This constructor is without a parameter
// Constructor #1
}

public dotnetClass(string name)
{
// This constructor has 1 parameter.
// Constructor #2

}
}

This constructor gets executed when an object of this class is instantiated. This is possible in C#. Calling a specific constructor will depend on how many parameters, and what parameters match a specific constructor. Note that a compile time error may get generated when 2 constructors of the same signature are created.

We may make use of the this keyword and invoke a constructor. See code example below.

this("some dotnet string");
//This will call Constructor #2 above

What is the use of the base keyword. Suppose we have a derived class named dotnetderivedclass. If this derived class is to invoke the constructor of a base class, we make use of the base keyword. See code example below on how to use a base keyword to invoke the base class constructor.

public class dotnetClass
{
public dotnetClass()
{
// The 1st base class constructor defined here
}

public dotnetClass(string Name)
{
// The 2nd base class constructor defined here
}
}

public class dotnetderivedclass : dotnetClass
// A class is being inherited out here
{
public dotnetderivedclass()
{
// dotnetderivedclass 1st constructor defined here
}

public dotnetderivedclass(string name):base(name)
{
// dotnetderivedclass 2nd constructor defined here
}
}

Note that we have used the base keyword in the sample code above. The sequence of execution of the constructors will be as follows:
public dotnetClass() method -> public dotnetderivedclass() method

The above sequence triggers when there is no initializer to the base class, and thus it triggers the parameterless base class constructor. The other base class constructor may also get invoked when we pass a parameter while defining it.

Magic Tables in SQL Server

What are Magic Tables in SQL Server?

In SQL server magic table is nothing more than an internal table which is created by the SQL server to recover recently inserted, deleted and updated data into SQL server database. That is when we insert or delete any record from any table in SQL server then recently inserted or deleted data from table also inserted into inserted magic table or deleted magic table with help of which we can recover data which is recently used to modify data into table either use in delete, insert or update to table. Basically there are two types of magic table in SQL server namely: inserted and deleted, update can be performed with help of these twos. Generally we cannot see these two table, we can only see it with the help Trigger’s in SQL server.

 

Whenever a trigger fires in response to the INSERT,DELETE,or UPDATE statement,two special tables are created.These are the inserted and the deleted tables.They are also referred to as the magic tables.These are the conceptual tables and are similar in structure to the table on which trigger is defined(the trigger table).

While using triggers these Inserted & Deleted tables (called as magic tables) will be created automatically.

The inserted table contains a copy of all records that are inserted in the trigger table.

The deleted table contains all records that have been deleted from deleted from the trigger table.

Whenever any updation takes place,the trigger uses both the inserted and deleted tables.

When we insert any record then that record will be added into this Inserted table initially, similarly while updating a record a new entry will be inserted into Inserted table & old value will be inserted into Deleted table. In the case of deletion of a record then it will insert that record into the Deleted table.

Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.Let’s see the demonstration of inserted magic table in SQL Server:

INSERTED Magic Table:

When we insert record into table then SQL server automatically created ‘inserted’ magic table and recently inserted record are available in this table, If we want to recover this data which are recently inserted then we can access this record with the help of Trigger’s. Let’s see the demonstration of inserted magic table in SQL Server.

Example:

--- CREATE TABLE TO DEMONSTRATION OF INSERT MAGIC TABLE ----

CREATE TABLE INSERT_MAGIC

(

ID INT,

TRIGGER_MESSAGE VARCHAR(50)

 )

 -------DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE

  CREATE TRIGGER TRI_MAGIC_INSERT

  ON USERLOGIN

  INSTEAD OF INSERT

  AS

  BEGIN

  DECLARE @ID INT

  SELECT @ID = (SELECT ID FROM inserted)

  INSERT INTO INSERT_MAGIC VALUES (@ID,'RECORD ADDED')

  END

  GO

Note: Here ‘inserted’ is insert magic table

DELETED Magic Table:

When we delete record from the table then SQL automatically create a deleted magic table which holds are deleted record from original table if we want to recover it then we can access that data from deleted magic table. Let’s see demonstration of recover data from deleted magic table.

Example: Creating Trigger for Deleted Magic table

-------DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE

  CREATE TRIGGER TRI_MAGIC_DELETE

  ON USERLOGIN

  INSTEAD OF INSERT

  AS

  BEGIN

  DECLARE @ID INT

  DECLARE @NAME VARCHAR(50)

  SELECT @ID = (SELECT ID FROM deleted)

  SELECT @NAME = (SELECT NAME FROM deleted)

  INSERT INTO INSERT_MAGIC VALUES (@ID,@NAME)

  END

  GO

Page Life Cycle Events

1. OnInit (Init) -- Inializes all Child controls on the Page

2. LoadControlState -- Loads the Control State

3. LoadViewState -- Loads the View State

4. LoadPostData -- Control properties are set according to the received form data

5. Load -- Actions can be performed on the controls as all the pre-activities are complete by this time

6. RaisePostDataChangedEvent -- This event is raised if data has been changed in previous and Current Postbacks.

7. RaisePostBackEvent -- This event handles the user action that caused the Postback.

8. PreRender (OnPreRender) -- This event takes place between postback and saving viewstate. Till this stage changes will be saved to the control.

9. SaveControlState -- Self Explanatory

10. SaveViewState -- Self Explanatory

11. Render -- Generates artifacts at Client side (HTML,DHTML,Scripts) to properly display controls

12. Dispose -- Releases unmanaged Resource ( Database connections, file handles)

13. Unload -- Releases managed Resources ( Instances created by CLR)

UNION vs UNION ALL?

 Why UNION ALL is faster than UNION?

 

UNION and UNION ALL are used to combine data or record sets from two different tables. One could combine more than 2 tables. Just like JOINS, UNION combines data into a single record-set but vertically by adding rows from another table. JOINS combine data horizontally by adding columns from another table.
UNION insures you get DISTINCT records from both the tables.
UNION ALL pulls out all records from both the tables with duplicates.
SQL Scripts below proves why UNION ALL is faster than UNION:

1	-- Create First table: #tempTable1

2	select FirstName, LastName into #tempTable1

3	from Person.Contact

4	where ContactID <= 100 -- Create Second table: #tempTable2 select FirstName, LastName into #tempTable2 from Person.Contact where ContactID > 100 and ContactID <= 200

Comparison:

1	SET STATISTICS PROFILE ON

2

3	-- Using UNION - Query Cost: 35%

4	select * from #tempTable1

5

UNION

6	select * from #tempTable2

Query Plan for UNION:
  |--Sort(DISTINCT ORDER BY:([Union1008] ASC, [Union1009] ASC))
       |--Concatenation
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

1	-- Using UNION ALL - Query Cost: 13%

2	select * from #tempTable1

3

UNION ALL

4	select * from #tempTable2

Query Plan for UNION ALL:
  |--Concatenation
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

1	-- Using UNION ALL with DISTINCT - Query Cost: 52%

2	select DISTINCT * from #tempTable1

3

UNION ALL

4	select DISTINCT * from #tempTable2

Query Plan for UNION ALL with DISTINCT:
  |--Concatenation
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable1].[FirstName] ASC,

[tempdb].[dbo].[#tempTable1].[LastName] ASC))
       |    |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable2].[FirstName] ASC,

[tempdb].[dbo].[#tempTable2].[LastName] ASC))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

1	SET STATISTICS PROFILE OFF

2

3	-- Final Cleanup

4	drop table #tempTable1

5	drop table #tempTable2

The Query plan for:
- UNION ALL SELECT above shows it just does a concatenation.
- UNION SELECT above shows it does a SORT on final concatenation.
- UNION ALL with DISTINCT SELECT above shows it does a SORT of individual tables than does the final concatenation.
The Query cost with:
- UNION ALL = 13%
- UNION = 35%
- UNION ALL with DISTINCT = 52%
This proves that:

• UNION ALL is faster and more optimized than UNION. But this does not mean you use UNION ALL in every scenario.
• UNION is not equivalent to “UNION ALL with DISTINCT”.

referenced from http://sqlwithmanoj.wordpress.com/2010/12/

Primary keys without Clustered Index

Primary keys without Clustered Index ...

Clustered Index :
A Clustered index determines the physical order of data in a table and is particularly efficient on columns that are often searched for ranges of values.
The leaf nodes of a clustered index contain the data pages.
There can be only one clustered index per table.
"primary key" is the ideal column for a clustered index
Clustered indexes are good for range searches.

Nonclustered Index :
Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
There can be multiple non-clustered indexes per table.
"Unique Key" is the ideal column for a nonclustered index
Non-clustered indexes are good for random searches.

As everyone of us know, by default if we create a Primary Key (PK) field in a table it would create Clustered Index automatically. I have been frequently asked by some of blog readers on "Is there a way to create PK fields without clustered Index?". Actually there are three methods by which we can achieve this. Let me give you the sample for both the methods below:

Method 1:

Using this method we can specify it while creating the table schema itself.

Create Table Empolyee
(
EmpID int Identity not null primary key nonclustered,
Name varchar(30),
PhoneNo int null
)
Go

Method 2:

Using this method also we could specify it while creating the table schema. It just depends on your preference.

Create Table Employee
(
 EmpID int Identity not null primary key nonclustered,
Name varchar(30),
PhoneNo int null
Constraint pk_parent primary key nonclustered (EmpID)
)
Go

Method 3:

If at all you already have a table which have a clustered index on a PK field then you might want to opt for this method.

Step 1: Find the contraint name

sp_helpconstraint Employee

/*
This way we could find out the constraint name. Lets assume that our constraint name is PK_Employee_22568A79
*/

Step 2: First drop the existing constaint

Alter table Employee drop constraint PK_Employee_22568A79

Step 3: Add the new nonclustered index to that field now

Alter table Employee add constraint PK_EmpIdNew primary key nonclustered (EmpID )

Difference between TRUNCATE, DELETE and DROP

Difference between TRUNCATE, DELETE and DROP?

DELETE & TRUNCATE are two TSQL commands used to remove records from a particular table. But they differ in how they execute and operate.
DELETE: (http://msdn.microsoft.com/en-us/library/ms189835.aspx)
- Removes some or all rows from a table.
- A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
- Causes all DELETE triggers on the table to fire.
- It deallocated records row-by-row in transaction logs and thus is slower than TRUNCATE.
- According to MS BOL, if a table is a heap or no clustered index is defined than the row-pages emptied are not deallocated and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deallocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
- Thus it requires more locks and database resources.
- This is a DML command as its is just used to manipulate/modify the table data. It does not change the property of a table.
TRUNCATE: (http://msdn.microsoft.com/en-us/library/ms177570.aspx)
- Removes all rows from a table.
- Does not require a WHERE clause, not allowed here.
- Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
- No triggers are fired on this operation because it does not log individual rows.
- It deallocates data pages instead of rows in transaction logs, thus is faster than DELETE.
- Thus it also requires less number of locks.
- TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
- This is a DDL command as its resets identity columns, deallocates data pages and empty them for use of other objects in the database.
Note: It is a misconception among some people that TRUNCATE cannot be rolled back. But in reality TRUNCATE operation can be ROLLED BACKED. Thus DELETE & TRUNCATE both can be rollbacked if provided inside a transaction.
DROP: (http://msdn.microsoft.com/en-us/library/ms173790.aspx)
The DROP TABLE command removes one or more table(s) from the database.
- All related data, indexes, triggers, constraints, and permission specifications for those tables are dropped by them operation.
- Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.
- Cannot drop a table that is referenced by any Foreign Key constraint.
- According to MS BOL, Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

Reverse a string using CTE in sql

DECLARE @StringToReverse VARCHAR(55)
    SET @StringToReverse = 'jagdish kholiya'   
    ;WITH cte AS (
          SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln        
           UNION ALL
          SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln
          FROM cte
          WHERE ln >= 1)
    SELECT @StringToReverse AS String, revStr FROM cte WHERE ln = 0