Can’t connect to remote sql server

Sometime we fail to connect  SQL Server(both by application and by local sql server) installed on remote machine . We do try by chnaging connection string and enbling and disabling a lot of option in the server but can’t fix.

I my self face that problem and after trying a lot of things solve that. I found there are two settings related to that by enbling these we can access the server remotely.

1. Go to the server machine open SQL server management studio and log in to the server and open the server properties window and check the ‘Allow remote connections to this server’ option as by the figure below :


2. Second one is to enable TCP/IP protocol for the SQL Sever, we can do this from the computer management studio. Go to controlpanel enter into Administrative Tools click and open the computer management studio and click on the ‘Protocols for MSSQLSERVER’ node and right click on the TCP/IP from the right side studio and make TCP/IP enable as shown by the picture below :

Can't connect remote sql server

Now try to connect the SQL server remotely, Hope now it will be available to remote clients.

How to identify website wise resource consumption

Here I will like to share “how to identify website wise resource consumption from task manager”/ “how to assign application pool wise user identity in IIS”.

 Suppose we have two sites using individual application pool running on an IIS server, sometime it may need to know the resource consumption of the sites we can find that from task manager process tab as below picture: 

Resource consumption of websites          Resource consumption of websites

 The problem is that it contains two worker processes for the two sites there is no way to identify which one belongs to which website.

 So we need a way to differentiate the websites in the task manager, we can do this by assigning different users for each website application pool (have to create and use website wise application pools and application pool wise users).The following steps will help to do this : 

  • First have to create user and this user have to add to the groups IIS_WPG and Administrator. The procedure to create user and adding them to the groups are given below:

              ü      Go to computer management expand the node system tools, expand Local Users and Groups , right click  on Users and select New User a window will open provide the necessary information and click the create  button the user will create. 

Create User

 Create User

                ü      Right click on the newly created user select Properties and go to the Member Of tab click the add button a window will come click Advanced button now click find and select Administrator and IIS_WPG groups and click ok. Click ok to all other window to close. So now the user is created and added to the groups Administrator and IIS_WPG

Assigning roles to user Assigning roles to user
  • Go to the application pool for which you want to assign the user right click and go to the properties go to the identity tab and select the radio button labeled Configurable and in the User name text box provide the newly created user name with the computer name (Example: Computername\UserName) and in the Password text box provide the user’s password and apply the changes. 
Assigning a user to a application pool Assigning a user to a application pool
  • Restart the application pool.
  •  Browse the two sites that use the application pools.
  • Go to task manager there should be some w3wp.exe process and its user name should be the newly created user. So now it’s possible to identify the website application pool using the user name and it possible to find out how much resource each website consuming. 
Now can indentify webisites by the users Now can indentify webisites by the Users

  So now it’s very easy to identify which site is taking how much resources.

Losing object refference/How object refference changes

We know object(instance of a class) is refference type.  But sometimes we found that we create object assigne some value and later when we go to use this object we don’t get the assigned value this happens because of some mistakes that we do unconciously. The below example shows how that happens :

             Class1 obj1 = new Class1();

What happens here  a new object is created in memory in other words memory is allocated for the members of Class1 and obj1 got a refference no to point that.

Suppose obj1 got refference no “Refference-1”

            obj1.val1 = 5;
            obj1.val2 = 10;

            Class1 obj2 = obj1;

So, obj2 got refference no “Refference-1” (Both obj1 and obj2 have same refference no)

            obj2.val1 = 25;

 At this stage we know that both obj1 and obj2 have same refference no  so any changes between these object will affect other so values of obj2.val1 and obj1.val1 would be 25.

             obj1 = new Class1(); 

 What happens here  a new object is created in memory in other words memory is allocated for the members of Class1 and obj1 got a new refference no. Suppose obj1 got new refference no “Refference-2”

 So now obj1 and obj2 have different refference no and any changes between them will not affect other.

At this stage refference no of obj1 is “Refference-2” and of obj2 is “Refference-1”

             obj1.val2 = 15; this will not affect the value of obj2.val2.

at this stage the value of  obj2.Val2 would be 10 and the value of obj1.val2 would be 15

How to clear SQL server cache / Database Query optimization task consideration

Sometime we developers assigned for query optimization tasks or performance improvment of system, and we start with it, We found some query takes very low time in the  Sql server query management studio but taking more time when calling from application, even some time we see that a query execution time varies time by time. And it seems to us like a puzzle and we go around but could not solve that problem. And it hampers our optimization task.

This happens because of some cache of SQL server, Each time a query or procedure runs, SQL server caches the query with the result, so if second time this query comes again to SQL server it returs the cache data so its take less time than before.

So to get over from this problem and to get actual execution time run the following  commands before running a query,

DBCC   FREEPROCCACHE  /* Remove all elements from the procedure cache.*/

DBCC    FREESESSIONCACHE  /*Flush the distributed query connection cache.*/

DBCC   FREESYSTEMCACHE(‘ALL’)  /* Release all unused cache entries from all caches.*/

DBCC   DROPCLEANBUFFERS  /*Remove all clean buffers from the buffer pool.*/

DBCC   PROCCACHE /*Display information about the procedure cache*/