View advantage and disadvantage ?

                               View in Sql

view is a virtual table based on the result-set of an SQL statement.

syntax :

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

What are the advantages and disadvantages of View? 

Advantages of views:

1. View the data without storing the data into the object.
2. Restict the view of a table i.e. can hide some of columns in the tables.
3. Join two or more tables and show it as one object to user.
4. Restict the access of a table so that nobody can insert the rows into the table.

Disadvatages:

1. Can not use DML operations on this.
2. When table is dropped view becomes inactive.. it depends on the table objects.
3. It is an object, so it occupies space.

 

C# Static Class

A static class is never instantiated. The static keyword on a class enforces that a type not be created with a constructor. In the static class, we access members directly on the type. This eliminates misuse of the class.

Note: A static class cannot have non-static members. All methods, fields and properties in it must also be static.

Example

To start, there are two classes in this program: the Program class, which is not static, and the Perl class, which is static. You cannot create a new instance of Perl using a constructor. Trying to do so results in an error.
Constructor

And: Inside the Perl class, we use the static modifier on all fields and methods. Instance members cannot be contained in a static class.

Program that demonstrates static class: C#

using System;

class Program
{
    static void Main()
    {
 // Cannot declare a variable of type Perl.
 // This won't blend.
 // Perl perl = new Perl();

 // Program is a regular class so you can create it.
 Program program = new Program();

 // You can call static methods inside a static class.
 Perl._ok = true;
 Perl.Blend();
    }
}

static class Perl
{
    // Cannot declare instance members in a static class!
    // int _test;

    // This is ok.
    public static bool _ok;

    // Can only have static methods in static classes.
    public static void Blend()
    {
 Console.WriteLine("Blended");
    }
}

Output

Blended

Public static members. The bool _ok and the method Blend() are the public static members on the Perl type. Often, public static members are used in helper classes or utility classes throughout a project.
Public Static Readonly Field Public Bool

Discussion

Conceptually, a static class is a form of information hiding. You can use regular classes and static classes in the same way, but the static modifier imposes an extra restriction. The constructor is eliminated.

So: You can think of a static class as a regular class with its constructor eliminated.

Summary

With static classes, we can enforce coding standards and expectations with a minimum of effort. By eliminating the constructor or the ability to create variables of a type, we introduce global variables and single-instance fields.

                               Best practices for SSIS

SSIS stands for SQL Server Integration Services. I have included few guidenlines which will help beginners of SSIS. Its always better to follow certain best practices while using SSIS so that performance tuning can be achieved with less efforts.

1] Components usage Use SSIS components only when it is extremely required.

2] Creation of SQL job If you are creating any SQL job then divide tasks in to smaller steps and create multiple steps so that readability will be more and maintenance is also easy.

3] child package call If you are executing child package from master package then parameters which are passed from master need to be configured in the child package.

4] Typecast usage You can change it to source level to avoid unnecessary type casting.

5] Sort operation: Its good practice to do sort operation at database query because it takes much more time if we do sort operation in SSIS.

6] Atomicity of transaction: Its always better to use SSIS where atomicity of transaction is present even though ETL can process large amount of data.

7] Trimming of package names: If your package name is more than 100 characters then it gets truncated when that package is deployed in to SQL server. You may get execution failure error in such scenario so to avoid this, limit the package name to maximum 100 characters.

8] Select query Whenever there is necessary to pass column names from the source to the downstream, avoid select * from table name and write necessary column names instead of * 

9] Package protection level : Its always better to set the package protection level to 'DontSaveSensative' so that when packages gets deployed from one system to another system, deployment errors gets resolved.

10] Package name as configuration filter Its good habit to use a particular package name as a configuration filter for all the configuration items that are specific to the package. 

11] WITH (NOLOCK) on tables It prevents keeping locks on the tables in SQL queries so that concurrency and performance will be improved.

12] Support of managed code There is no unmanaged code in SSIS. The Script component, the script task and the custom components give users power of managed programming language with less infrastructure code to write. 

Deploying ASP.NET Websites on IIS 7.0

Introduction

1. Introduction to IIS 7.0
2. Features of IIS 7.0
3. Basic Architecture of IIS 7.0
4. How to Deploy ASP.NET Websites on IIS 7.0
5. How to Create Application Pool
6. Assign Application Pool To Your Application
7. Configure Web Garden on IIS 7.0
   • What is Application Pool?
     • Types of Application Pool
     • Identity Of Application Pool
   • How To Create An Application Pool and Assign To a Web Application
   • What is Web Garden?
   • How To Create Web Garden?
   • Is it Recommended to use Web Garden ?
8. IIS 6.0 Vs IIS 7.0
9. Where Do I Get IIS 7.0

Introduction to IIS 7.0

Microsoft Internet Information Services (IIS) 7.0 in Windows Server 2008 and Windows Vista provides a secure, manageable platform for developing and administrating and hosting Web applications and services. It has been completely redesigned and Restructured .IIS 7.0 provides features and functionality for administrators to effectively manage Web infrastructures; developers to rapidly build Web applications and services; and hosters to Web hosting.

Features of IIS 7.0

Following are some features of IIS 7.0:

• IIS 7.0 provides features and functionality that enable administrators to reliably and effectively manage Web infrastructures.
• IIS 7.0 has a distributed file-based configuration system that enables IIS settings to be stored in web.config files along with the ASP.NET settings.
• IIS 7.0 provides a cost-effective, more scalable Web server platform for delivering reliable Web hosting to a broad set of customers.

Major innovations in IIS 7.0:

• A modular, extensible core Web server
• A unified, distributed file-based configuration system
• Integrated health monitoring and diagnostics
• A set of new administration tools with delegation support

Basic Architecture of IIS 7.0

The following diagram shows the Overall Architecture of IIS 7.0 which contains HTTP.Sys, SvcHost.exe, Application Pool and Worker Process(W3Wp).

The main components of IIS 7.0 are HTTP.Sys, Svchost.Exe, Application Pool , Worker Process (W3WP.exe) andConfiguration Store.

HTTP.Sys: It the Kernel mode Protocol stack which listens to the HTTP and HTTPS Request. W3SVC and WAS are the parts of Svchost.exe. W3SVC is the Listener of Request from kernel mode that is passed by the HTTP.Sys. W3SVC also interacts with Windows Activation Process which is managed by the worker process by starting, stopping and recycling the application pool. It was also responsible for Health Monitor of Application Pool during runtime. Configuration stores all web.config and ASP.NET Settings and other configuration in XML Hierarchy form. W3wp.exe is a long-running process that processes requests and generates responses.

The following diagram shows you the process flow of IIS 7.0. This is the flow of User Request to IIS and gets the Response from IIS.

User Request Pass from Kernel Level to User Level Via Http.Sys and then passes to svchost, and then goes to Application Pool.

How to Deploy ASP. Net Websites on IIS 7.0

From now onwards, I will describe one example to deploy your ASP.NET websites on IIS 7.0.

Step 1: From Visual Studio, publish your Web application.

Step 2: Copy the published application folder to "C:\intepub\wwwroot" [default] folder.

Step 3: From RUN - > inetmgr -> OK

The following screen will come. This is the main page for any application. There are three panels.

"TestWeb" is a recently pasted webapplication on your wwwroot folder.

Step 4: We need to convert it to an application, just right click and then Click on "ConvertToApplication" as shown in the following picture:

After converting it to application, its icon will be changed and then you can set the property for your web application from the middle pane. You can set IIS Authentication Mode, Default Page Just like IIS 6.0:

You can change Security Settings on Authentication Section. By default Setting will be set from your web.config itself. As in my web.config Form Authentication was set that's why, Forms Authentication has been Enabled. If we want to change the status, just double click and update status to Enabled to Disabled or Vice Versa.

Now one most important part is to set the Application Pool for your application.

How to Create Application Pool

A site can contain many applications including that site’s default application, which is called the root application. In addition to belonging to a site, an application belongs to an application pool, which isolates the application from applications in other application pools on the server .

Step 1: Right Click on Application Pool and give the name of your pool. Here, I have given "pool" and select Framework and click on OK:

Step 2: If you are writing some thing on the server [may be writes Error Events Logs], you need to change the Pool Identity to Local System. Right click on Pool Identity setting as shown in the following image:

There are three identities of Application Pool:

• Network Service
• Local Service
• Local System

Assign Application Pool To Your Application

Now, we need to assign the Application Pool to our web application.

Step 1: Right Click on "TestWeb" Application and Select "Advanced Settings":

Step 2: Finally assign your created Application Pool "pool" to "TestWeb" Application.

Now you can run your application by just typing http:\\localhost\testweb and it can be accessible on network by IP also.

Configure Web Gardens in IIS 7.0

I have split this section into some parts. These are:

• What is Application Pool?
• How To Create An Application Pool and Assign To a Web Application
• What is Web Garden?
• How To Create Web Garden?
• Is it Recommended to use Web Garden ?

What is Application Pool ?

Application pools are used to separate set of IIS worker processes that share the same configuration. Application pools enable us to isolate our web application for better security, reliability, and availability. The worker process serves as the process boundary that separates each application pool so that when one worker process or application is having an issue, other applications or worker processes are not affected.

Figure: Application Pool With Worker Process On IIS Server

In your IIS, there may be more on Web sites hosted and by creating Application Pool, you can just assign a Separate Worker Process to your application. I have already discussed how you can assign Application Pool to your web application.

Types of Application Pools

There are two predefined Application Pools is IIS 7.0:

1. DefaultAppPool (Integrated)
2. ClassicAppPool

None of the Application Pools are Installed by default when IIS 7.0 is installed. Classic Application Pool is installed when ASP.NET 2.0 is installed in the System.

Identity Of Application Pool

Application pool identity configuration is an important aspect of security in IIS 7.0, because it determines the identity of the worker process when the process is accessing resource. This Settings comes form IIS 6.0. in IIS 5.0. There the Application pool runs under the local system account. In IIS 7.0, there are three predefined Identities that are the same as IIS 6.0

Applicationpool Identity	Description
LocalSystem	LocalSystem is a built-in account that hasadministrative privileges on the server. It can access both local and remote resources
LocalServices	LocalServices built-in account has privileges of an authenticated local user account. It does not have any network access permission.
NetworkServices	This is the default Identity of Application Pool.NetworkServices has privileges of authenticated local user account and it can have access remote resource as machine account.

How to Create An Application Pool and Assign It to a Web Application?

This has already been discussed.

Creating Application Pool and Assigning it to a Web Application

What is Web Garden?

By default each Application Pool runs with a Single Worker Process (W3Wp.exe). We can assign multiple Worker Processes With a Single Application Pool. An Application Poll with multiple Worker process is called "Web Gardens". Many worker processes with the same Application Pool can sometimes provide better throughput performance and application response time. And each worker process should have their own Thread and Own Memory space.

Fig: General Block Diagram of Web Garden.

As given in the picture, in IIS Server there may be multiple Applicationpool and each application pool has at least a single Worker Process. Web Garden should contain multiple Worker processes.

There are certain restrictions to use Web Garden with your web application. If we use Session Mode to "in proc", our application will not work correctly because session will be handled by different Worker Process. To avoid this type of problem, we should have to use Session Mode "out proc" and we can use "Session State Server" or "SQL-Server Session State".

Main Advantage: The worker processes in a Web garden shares the requests that arrive for that particular application pool. If a worker process fails, another worker process can continue to process requests.

How to Create Web Garden?

Create a New Application Pool. Right Click -> Advance Setting -> Go To Process Model Section, set the Value for Maximum Worker Process.

Is It Recommended to use Web Garden?

It not always recommended to use Web Garden to your Application. It effects the performance of your sites except in very specific cases like some long running synchronous requests or application is very unstable.

IIS 6.0 Vs IIS 7.0

The main advantages of IIS 7.0 is Modular Design which gives some benefits to IIS 7.0 over IIS 6.0. The following table shows you some differences of IIS 6.0 and IIS 7.0.

Features	IIS 6.0	IIS 7.0
Architecture	Monolithic	Modular
Setup	Most Features Installed	Minimum installation based on role
Extended Features	ISAPI Filter and ISAPI Extension	Added Module and handler in managed or native code
Customize UI	Normal	Customize for .NET

There are many more differences.

Where Do I Get IIS 7.0

IIS 7.0 will not be available with all Operating Systems. There are some specific OS and specific Versions for IIS 7.0.

Operating System With Edition	Available
Windows Server 2008	Yes
Windows Vista (Home Basic)	No (Default) need to install
Windows Vista (Home Premium)	No (Default) need to install
Windows Vista (Business)	Yes
Windows Vista (Ultimate)	Yes

   How to save your website from getting hacked

Hacking of websites are very common these days. Most of the websites are being built using CMS applications like WordPress, Joomla, Drupal and Zencart. We always forget about the security features and it will lead to website hacking. This article will give you an idea about hacking types and the steps to avoid hacking.

Hacking is an attack on a website that can deface the front page of your site. Your login details will not work for admin section and sometime you will see Google malware warning on your website. These are typically the work of system cracker, who breaks into web server and replace the hosted content with malware content.

Hacking can be done in many different ways. The two most common methods are below:

Account passwords harvesting

In this method, hackers will try to collect account passwords. Some vulnerable software or other vulnerabilities allow hackers to access your computer data. They will then sniff around and find data such as FTP usernames and passwords that are stored in some other programs or files. Also the use of simple password like 123456 or use of a spyware infected PCs at home are the main reasons of hacking.  Keeping strong password will not help if key logger is installed on your computer.

PHP vulnerabilities

PHP has got a lot of  vulnerable and potentially exploitable functions. Most of the website hacking is being done using vulnerabilities in PHP applications. Hackers have been enjoying these security lapse for a long time. We offer PHP 5 in our Linux servers after closing down most of the security holes of PHP.

Most of the PHP applications such as Joomla, PhpBB, PhpNuke etc are community developed. These applications may have potential security vulnerabilities and hackers exploit them.

All community developed PHP applications are patched as & when new vulnerabilities are discovered. So you should upgrade/patch PHP applications in your website from time to time. Failing to upgrade/patch PHP applications in your website is equal to opening a backdoor entry for hacker in your website.

Mass modification of website files

Once the hacker discovered a backdoor entry in your website either using an account password or a vulnerable PHP application, he will modify your files. We call this stage as “mass modification of website files”. In this stage they use a special tool, called MPACK, to install malicious IFrames code in website. Usually only main index pages are targeted (i.e. index.php, index.html, index.shtml, etc.). Malicious IFrames are usually installed at the beginning or at the end of the document.

You can refer these URls for more information:

Code injection – http://en.wikipedia.org/wiki/Code_injection

Remote File Inclusion – http://en.wikipedia.org/wiki/Remote_File_Inclusion
Given below are few recommended precautionary steps:

* Use strong Password:

We recommend use of strong password containing 8 or more random letters and numbers. Password should not be simple. (use http://strongpasswordgenerator.com to generate passwords). Some secure password tips would be:

•  Don’t use a dictionary word

• Don’t use part of the user-name

• Keep the password at least 8 characters long

• Have a combination of at least three of:

- lowercase characters (a, b, c)
- uppercase characters (A, B, C)
- numbers (1, 2, 3)
- non-alphanumeric characters (!, %, *, {, £, )

• Change your passwords periodically (say, at least once a month)

• Don’t share the same password for all accounts.

• Should not store passwords in email client, browser and FTP client.

• Do not share passwords with any one.

* Any 3rd party or custom PHP, Perl and other web applications should be kept up to date at all times. Subscribe to the software vendors security or update notifications mailing list. If an application is no longer required or in use, remove it completely. Disabling the application is not always a sure means of disallowing intrusion attempts.

* Check File permissions in your server (Ex: Any anonymous, Internet User access to be restricted). By Default files should have 644  and  directories should have 755 permission.

* Scan your PCs/Workstation that you use for logging into your Web using good anti-virus or anti spy ware  and clean bad programs. We recommend to regularly scan your website using “Virus Scanner” available in your cPanel.

* Ensure your computer is not having any keyloggers or spyware.

http://en.wikipedia.org/wiki/Keystroke_logging
http://www.actualspy.com/articles/keyloggers.html

* Review your hosting accounts/sites periodically and ensure that nothing has been uploaded or changed. Make sure there is no old file with .bak or .txt extensions lying around.

* For securing word press site, please refer the link given below:

http://codex.wordpress.org/Hardening_WordPress

For securing Joomla site, please refer the links given below:

http://docs.joomla.org/Category:Security_Checklist

For any queries, please contact our Technical support team.

backup and restore database

How to backup and restore database on Microsoft SQL Server 2005

Introduction

This post will give you a brief introduction of SQL Server backup. As we know, SQL Server backup software is springing up nowadays. Take EaseUS Todo Backup Advanced Server as an example, it’s an all-in-one SQL server backup software for MS SQL Server 2000/2005/2008, exchange server and server 2000/2003/2008, and provides proven database backup solution for Server administrators.

But in the example below, I will show a step-by-step database backup from SQL Server 2005 and restore the database to another SQL Server 2005 Express Edition using Microsoft SQL Server Management Studio.

Step-by-step

Backup a database.

Now I will backup AdventureWorks database on BKKSQL2005 which runs Microsoft SQL Server 2005 to a file.

1. Connect to source server. Open Microsoft SQL Server Management Studio and connect to BKKSQL2005.
2. Right-click on the AdventureWorks database. Select Tasks -> Backup…
   
3. On Back Up Database window, you can configure about backup information. If you’re not familiar these configurations, you can leave default values. Here are some short descriptions.
   1. Database – a database that you want to backup.
   2. Backup type – you can select 2 options: Full and Differential. If this is the first time you backup the database, you must select Full.
   3. Name – Name of this backup, you can name anything as you want.
   4. Destination – the file that will be backup to. You can leave as default. Default will backup to “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
   5. Click OK to proceed backup.
   
4. Wait for a while and you’ll see a pop-up message when backup is finished.
   
5. Browse to the destination, you’ll see a backup file (.bak format) which you can copy to other server for restore in the next step. Default backup directory is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
   

Restore the database.

Next, I will restore the AdventureWorks database from a file that I’ve created above to BK01BIZ001 which runs Microsoft SQL Server Express Edition.

1. Copy the backup file from source server to destination server. I’ve copied into the same directory as source server.
   
2. Connect to destination server. Open Microsoft SQL Server Management Studio Express and connect to BK01BIZ001.
3. Right-click on Databases. Select Restore Database…
   
4. Restore Database window appears. On Source for restore, select From device and click [...] buttton to browse file.
5. On Specify Backup, ensure that Backup media is “File” and click Add.
   
6. On Locate Backup File, select the backup file. This is the backup file that was created in Backup a database section and was copied to this server. Click OK. OK.
   
7. Back to Restore Database window.
   1. On Destination for restore, select “AdventureWorks”.
      Note: If you haven’t added the backup file on Source before (step 4-6), you won’t see the database name on Destination.
   2. On Source for restore, check the box in front of the backup name (in Restore column).
   3. Click OK.
   
8. Wait until restore finish and there’ll be a pop-up message notify.
   
9. Now you’ll see the restored database on the destination SQL Server.

1. 

Tips for Query Optimization

                                 SqlServer Query Optimization Tips

Tip 1: Always use WHERE Clause in SELECT Queries while we don’t need all the rows to be returned. This will help to narrow the return rows else it will perform a whole table scan and waste the Sql server resources with increasing the network traffic. While scanning the whole it will lock the Table which may prevent other users to access the table. Tip 2: It is seen many times developers use codes like   SELECT * FROM OrderTable WHERE LOWER(UserName)='telsa' Instead of writing it like the below SELECT * FROM OrderTable WHERE UserName='telsa' Infact both the queries does the same work but the 2nd one is better and retrieves rows more speedly than the first query. Because Sql Server is not case sensitive Tip 3: While running a query, the operators used with the WHERE clause directly affect the performance. The operators shown below are in their decreasing order of their performance. = >,>=,<, <= LIKE <> Tip 4 : When we are writing queries containing NOT IN, then this is going to offer poor performance as the optimizer need to use nested table scan to perform this activity. This can be avoided by using EXISTS or NOT EXISTS. When there is a choice to use IN or EXIST, we should go with EXIST clause for better performance. Tip 5: It is always best practice to use the Index seek while the columns are covered by an index, this will force the Query Optimizer to use the index while using IN or OR clauses as a part of our WHERE clause.  SELECT * FROM OrderTable WHERE Status = 1 AND OrderID IN (406,530,956) Takes more time than  SELECT * FROM OrderTable (INDEX=IX_OrderID) WHERE Status = 1 AND OrderID IN (406,530,956)

Tip 6: While we use IN, in the sql query it better to use one or more leading characters in the clause instead of using the wildcard character at the starting. SELECT * FROM CustomerTable WHERE CustomerName LIKE 'm%' SELECT * FROM CustomerTable WHERE CustomerName LIKE '%m' In the first query the Query optimizer is having the ability to use an index to perform the query and there by reducing the load on sql server. But in the second query, no suitable index can be created while running the query. Tip 7: While there is case to use IN or BETWEEN clause in the query, it is always advisable to use BETWEEN for better result. SELECT * FROM CustomerTable WHERE CustomerID BETWEEN (5000 AND 5005) Performs better than SELECT * FROM CustomerTable WHERE CustomerID IN (5000,5001,5002,5003,5004,5005) Tip 8: Always avoid the use of SUBSTRING function in the query. SELECT * FROM CustomerTable WHERE CustomerName LIKE 'n%' Is much better than writing SELECT * FROM CustomerTable WHERE SUBSTRING(CustomerName,1,1)='n' Tip 9 : The queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written. So certain things should be taken care of like Provide the least likely true expressions first in the AND. By doing this if the AND expression is false at the initial stage the clause will end immediately. So it will save execution time If all the parts of the AND expression are equally like being false then better to put the Complex expression first. So if the complex works are false then less works to be done. Tip 10: Its sometimes better to combine queries using UNION ALL instead of using many OR clauses. SELECT CustomerID, FirstName, LastName FROM CustomerTable WHERE City = 'Wichita' or ZIP = '67201' or State= 'Kansas' The above query to use and index, it is required to have indexes on all the 3 columns. The same query can be written as SELECT CustomerID, FirstName, LastName FROM CustomerTable WHERE City = 'Wichita' UNION ALL SELECT CustomerID, FirstName, LastName FROM CustomerTable WHERE ZIP = '67201' UNION ALL SELECT CustomerID, FirstName, LastName FROM CustomerTable WHERE State= 'Kansas' Both the queries will provide same results but if there is only an index on City and no indexes on the zip or state, then the first query will not use the index and a table scan is performed. But the 2nd one will use the index as the part of the query. Tip 11:  While the select statement contains a HAVING clause, its better to make the WHERE clause to do most of the works (removing the undesired rows) for the Query instead of letting the HAVING clause to do the works.  e.g. in a SELECT statement with GROUP BY and HAVING clause, things happens like first WHERE clause will select appropriate rows then GROUP BY divide them to group of rows and finally the HAVING clause have less works to perform, which will boost the performance. Tip 12: Let’s take 2 situations A query that takes 30 seconds to run, and then displays all of the required results. A query that takes 60 seconds to run, but displays the first screen full of records in less than 1 second. By looking at the above 2 situations a developer may choose to follow the 1st option, as it uses less resources and faster in performance. But actually the 2nd one is more acceptable by a DBA. An application may provide immediate feedback to the user, but actually this may not be happening at the background. We can use a hint like SELECT * FROM CustomerTable WHERE City = 'Wichita' OPTION(FAST n) where n = number of rows that we want to display as fast as possible. This hint helps to return the specified number of rows as fast as possible without bothering about the time taken by the overall query.