Difference between Web Garden and Web Farm in IIS

Web Garden

By default, each application pool runs with a single worker process (w3wp.exe). When an application pool runs with multiple worker process, it is called web garden.

Application pool - shyamb4u

To Enable Web Garden - Application Pool - Advance Settings - In Process Model - Increase the Number of Worker Process from 1 to any number(max 10)

Web Farm

When a web application is hosted on multiple web servers and access based on the load on servers, it is called Web Farm.

Webfarm -shyamb4u

In web farm, a single application is hosted on multiple IIS server and these IIS servers are connected with the VIP (Virtual IP) with load balancer. Load balancer IPs is exposed to external world for accessing your web application. Hence, when a request comes to server from client, it first hits the load balancer, and then based on the traffic on each server; load balancer distributes the request to the corresponding web server.

What are differences between Always ON and SQL Server Clustering?

Always On is a commercial name for a set of High Availability features that include Fail-over Cluster Instances and Availability Groups.

The main difference is that a clustered instance has the same binaries installed and configured on two or mode cluster nodes (physical or virtual machines) and the database files are sitting on a shared disk. Only one node at a time can own the data disks and run the SQL Server instance (active node) and all other nodes are said to be passive. When the node fails for any reason, the disks are moved to one of the passive nodes and the service is started, making it the active node. All clients connecting to the clustered instance use its virtual network name, which is moved to the active node, ensuring that all clients are able to connect to the active node.

With Availability Groups, two or more copies of the same database are synchronized across multiple nodes. In this case you don't have a single copy of the database files, but you have multiple copies (replicas). One of those copies is read/write, while all other copies are read-only. To ensure that the clients are connecting to the appropriate replica, a virtual network name (listener) is used, which is similar to what the cluster virtual network name does. With an additional connection string property, you can control whether you're hitting the read/write or read-only replica

What is SaaS, IaaS & PaaS?

The cloud is a hot topic for small businesses all the way to global enterprises, but remains a broad concept that covers a lot of online territory. There are usually three models of cloud service to compare: Software as a Service (SaaS), Platform as a Service (PaaS), and Infrastructure as a Service (IaaS). Each of these has its own benefits, as well as variances, and it is necessary to understand the differences among SaaS, PaaS, and IaaS to know how to best choose one for your organization.

• SaaS: Software as a Service

Software as a Service, also known as cloud application services, represents the most commonly utilized option for businesses in the cloud market. SaaS utilizes the internet to deliver applications, which are managed by a third-party vendor, to its users. A majority of SaaS applications run directly through your web browser, which means they do not require any downloads or installations on the client side.

SaaS Characteristics :

There are a few ways to help you determine when SaaS is being utilized:

·     Managed from a central location

·     Hosted on a remote server

·     Accessible over the internet

·     Users not responsible for hardware or software updates

Postgresql Basic Commands

Postgresql Basic Commands

Login to postgresql:
psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W
psql -U myuser -h myhost "dbname=mydb sslmode=require" # ssl connection

Default Admin Login:
sudo -u postgres psql -U postgres
sudo -u postgres psql

List databases on postgresql server:
psql -l [-U myuser] [-W]

Turn off line pager pagination in psql:
\pset pager

Determine system tables:
select * from pg_tables where tableowner = 'postgres';

List databases from within a pg shell:
\l

List databases from UNIX command prompt:
psql -U postgres -l

Describe a table:
\d tablename

Quit psql:
\q

To find SQL Version,Edition & Instance Name in SQL 2008,2012,2014,etc

The below query will give the SQL Product Full Specification details.

SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Product Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'

How to find Indexes on a Table In SQL Server

Using system stored procedure sp_helpindex, system catalog views like sys.indexes or sys.index_columns methods. 

1. Find Indexes On A Table Using SP_HELPINDEX

sp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. This is the easiest method to find the indexes in a table. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.

EXEC sp_helpindex '[[[SCHEMA-NAME.TABLE-NAME]]]'
GO

Missing Index Script in SQL Server Database 2016

Performance Tuning is quite interesting and Index plays a vital role in it. A proper index can improve the performance and a bad index can hamper the performance. In this blog post we will discuss about Missing Index. Here is the script from my script bank, which I use to identify missing indexes on any database.

Last Used Database in SQL Server

In SQL Server 2005, can you easily determine the last time someone queried a database.

SELECT d.name,
last_user_seek = MAX(last_user_seek),
last_user_scan = MAX(last_user_scan),
last_user_lookup = MAX(last_user_lookup),
last_user_update = MAX(last_user_update)
FROM sys.dm_db_index_usage_stats AS i
JOIN sys.databases AS d ON i.database_id=d.database_id

GROUP BY d.name

SQL Relay

SQL RELAY

How to Find Free Log Space in SQL Server?

This post will show you how to monitor free log space in SQL Server.

Method 1: Using SQLPERF

DBCC SQLPERF ('LOGSPACE')
GO

Method 2: sys.dm_db_log_space_usage
  
SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage;

How to Validate Email Address in SQL Server?

How to Validate Email Address in SQL Server-Let us create a column called Email Address in the table named Contacts. Once we create the table, we will insert few valid and invalid email address in it.

Different Methods to Know COMPATIBILITY LEVEL of a Database

There are many methods to know the compatibility levels of a database.

1) Use system stored procedure sp_helpdb
   
EXEC sp_helpdb TEST

How to Change Compatibility of Database

There are two different ways how we can change the compatibility of database to SQL Server.

1) Using Management Studio

For this method first to go database and right click over it. Now select properties.

2) Using T-SQL Script.

You can execute following script and change the compatibility settings to 120.

USE [master] GO
ALTER DATABASE [TEST] SET COMPATIBILITY_LEVEL = 120
GO

Ms SQL Useful Stored Procedures

DBCC CHECKDB ('DB_Name')
DBCC CHECKALLOC ('DB_Name')
DBCC updateusage ('DB_Name')
DBCC CHECKIDENT ('dbo.tablename',reseed,0) 
- '0' by default,Seed Value Last Incremented value can be added

EXEC SP_help

EXEC SP_helptext

EXEC sp_renamedb 'oldname' , 'newname';

xp_fixeddrives  - To get HD available in the Server / Disk Space

To Get List of Jobs running in SQL Agent Job

How to find the SQL Agent Jobs  running in your DB Instance and its Steps, query used for the results

 SELECT J.NAME AS 'JOB NAME',

S.STEP_ID AS 'STEP',

S.STEP_NAME AS 'STEP NAME',

S.COMMAND AS 'QUERY',

DATABASE_NAME AS 'DATABASE'

FROM MSDB.DBO.SYSJOBS J

INNER JOIN MSDB.DBO.SYSJOBSTEPS S ON S.JOB_ID = J.JOB_ID

WHERE J.ENABLED = 1

ORDER BY J.NAME,S.STEP_ID

 

How to Enable Automatic Statistic Update on Database

After putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled.

Once I enabled the auto update of statistics, the database started to respond as expected. If you ever face situation like this, please do the following:

1) Enable Auto Creation of Statistics

2) Enable Auto Update of Statistics

3) Update Statistics for whole database

Display All IP address used in a LAN

A single line of a command and its arguments that can help display all computer IP addresses (those that are being used) on a LAN,

arp -a

ipconfig /all

net view /all

nmap -sn 192.168.0.0/24 (replacing the sub-net with the appropriate one for your LAN)

MS SQL Server Main Replication Models

Replication Publisher and Subscriber :

To understand how SQL replication works, we’ll have to define two replication terms – Publisher and Subscriber. In a SQL replication the Publisher is the part that offers the data for distribution. The Subscriber is the part that consumes (gets updates) the data from the Publisher.

MS SQL : Use of GUID function

If you want to generate a new Guid (Unique Identifier) in SQL server the you can simply use the NEWID() function.

Example

SELECT NEWID( )
GO
-- This will return a new random uniqueidentifier e.g.
E75B92A3-3299-4407-A913-C5CA196B3CAB

To select this Guid in in a variable

--To Assign uniqueidentifier in a variable
DECLARE @EmployeeID uniqueidentifier
SET @EmployeeID = NEWID( )

You can directly use this with INSERT statement to insert new row in table.

-- Inserting data in Employees table.
INSERT INTO Employees
(EmployeeID, Name, Phone)
VALUES
(NEWID( ), 'Shyam Sundar', '123-4567')

MS SQL : Function to Convert INR Value

Function (numberVar Amount)

numbervar RmVal:=0;

numbervar Amt:=0;

numbervar pAmt:=0;

stringvar InWords :="";

stringvar ckAmt:="";

Amt := ABS(Amount);

ckAmt := CSTR(ABS(Amount));