Friday, December 27, 2019

Fixing database Is In Use Error While Restoring Database from Backup

I have found an error while restore Sql database like "Error While Restoring Database from Backup"

So i have did below thing to resolve this issue and issue is resolved.

--use master
--Create Database *****_OLD
--alter database *****_OLD set offline with rollback immediate;

After you successful restore execute the below line to make it  available to all.

--use master
--alter database *****_OLD set online with rollback immediate;


In case above steps wont work then change the database name *****_OLD to your .bak file database Name, then i am sure it will works

Thursday, November 7, 2019

Load Balancer

Load balancers

How do load balancers distribute the web traffic? There are several algorithms:
  • Round-robin: each request is assigned to the next server in the list, one server after the other. This is also called the poor man’s load balancer as this is not true load balancing. Web traffic is not distributed according to the actual load of each server.
  • Weight-based: each server is given a weight and requests are assigned to the servers according to their weight. Can be an option if your web servers are not of equal quality and you want to direct more traffic to the stronger ones.
  • Random: the server to handle the request is randomly selected
  • Sticky sessions: the load balancer keeps track of the sessions and ensures that return visits within the session always return to the same server
  • Least current request: route traffic to the server that currently has the least amount of requests
  • Response time: route traffic to the web server with the shortest response time
  • User or URL information: some load balancers offer the ability to distribute traffic based on the URL or the user information. Users from one geographic location region may be sent to the server in that location. Requests can be routed based on the URL, the query string, cookies etc.
Apart from algorithms we can group load balancers according to the technology they use:
  • Reverse Proxy: a reverse proxy takes an incoming request and makes another request on behalf of the user. We say that the Reverse Proxy server is a middle-man or a man-in-the-middle in between the web server and the client. The load balancer maintains two separate TCP connections: one with the user and one with the web server. This option requires only minimal changes to your network architecture. The load balancer has full access to the all the traffic on the way through allowing it to check for any attacks and to manipulate the URL or header information. The downside is that as the reverse proxy server maintains the connection with the client you may need to set a long time-out to prepare for long sessions, e.g. in case of a large file download. This opens the possibility for DoS attacks. Also, the web servers will see the load balancer server as the client. Thus any logic that is based on headers like REMOTE_ADDR or REMOTE_HOST will see the IP of the proxy server rather than the original client. There are software solutions out there that rewrite the server variables and fool the web servers into thinking that they had a direct line with the client.
  • Transparent Reverse Proxy: similar to Reverse Proxy except that the TCP connection between the load balancer and the web server is set with the client IP as the source IP so the web server will think that the request came directly from the client. In this scenario the web servers must use the load balancer as their default gateway.
  • Direct Server Return (DSR): this solution runs under different names such as nPath routing, 1 arm LB, Direct Routing, or SwitchBack. This method forwards the web request by setting the web server’s MAC address. The result is that the web server responds directly back to the client. This method is very fast which is also its main advantage. As the web response doesn’t go through the load balancer, even less capable load balancing solutions can handle a relatively large amount of web requests. However, this solution doesn’t offer some of the great options of other load balancers, such as SSL offloading – more on that later
  • NAT load balancing: NAT, which stands for Network Address Translation, works by changing the destination IP address of the packets
  • Microsoft Network Load Balancing: NLB manipulates the MAC address of the network adapters. The servers talk among themselves to decide which one of them will respond to the request. The next blog post is dedicated to NLB.
Let’s pick 3 types of load balancers and the features available to them:
  • Physical load balancers that sit in front of the web farm, also called Hardware
  • ARR: Application Request Routing which is an extension to IIS that can be placed in front of the web tier or directly on the web tier
  • NLB: Network Load Balancing which is built into Windows Server and performs some basic load balancing behaviour
Load balancers feature comparison
No additional failure points:

This point means whether the loadbalancing solution introduces any additional failure points in the overall network.

Physical machines are placed in front of your web farm and they can of course fail. You can put a multiple of these to minimise the possibility of a failure but we still have this possible failure point.
With ARR you can put the load balancer in front of your web farm on a separate machine or a web farm of load balancers or on the same web tier as the web servers. If it’s on a separate tier then it has some additional load balancing features. Putting it on the same tier adds complexity to the configuration but eliminates additional failure points, hence the -X sign in the appropriate cell.
NLB runs on the web server itself so there are no additional failure points.

Health checks
This feature means whether the load balancer can check whether the web server is healthy. This usually means a check where we instruct the load balancer to periodically send a request to the web servers and expect some type of response: either a full HTML page or just a HTTP 200.
NLB is only solution that does not have this feature. NLB will route traffic to any web server and will be oblivious of the answer: can be a HTTP 500 or even no answer at all.

Caching
This feature means the caching of static – or at least relatively static – elements on your web pages, such as CSS or JS, or even entire HTML pages. The effect is that the load balancer does not have to contact the web servers for that type of content which decreases the response times.
NLB does not have this feature. If you put ARR on your web tier then this feature is not available really as it will be your web servers that perform caching.

SSL offload
SSL Offload means that the load balancer will take over the SSL encryption-decryption process from the web servers which also adds to the overall efficiency. SSL is fairly expensive from a CPU perspective so it’s nice to relieve the web machine of that responsibility and hand it over to the probably lot more powerful load balancer.

NLB doesn’t have this feature. Also, if you put ARR on your web tier then this feature is not available really as it will be your web servers that perform SSL encryption and decryption.
A benefit of this feature is that you only have to install the certificate on the load balancer. Otherwise you must make sure to replicate the SSL certificate(s) on every node of the web farm.

If you go down this path then make sure to go through the SSL issuing process on one of the web farm servers – create a Certificate Signing Request (CSR) and send it to a certificate authority (CA). The certificate that the CA generates will only work on the server where the CSR was generated. 

Install the certificate on the web farm server where you initiated the process and then you can export it to the other servers. The CSR can only be used on one server but an exported certificate can be used on multiple servers. 

There’s a new feature in IIS8 called Central Certificate Store which lets you synchronise your certificates across multiple servers.

Geo location
Physical loadbalancers and ARR provide some geolocation features. You can employ many load balancers throughout the world to be close to your customers or have your load balancer point to different geographically distributed data centers. In reality you’re better off looking at cloud based solutions or CDNs such as Akamai, Windows Azure or Amazon.

Low upfront cost
Hardware load balancers are very expensive. ARR and NLB are for free meaning that you don’t have to pay anything extra as they are built-in features of Windows Server and IIS. You probably want to put ARR on a separate machine so that will involve some extra cost but nowhere near what hardware loadbalancers will cost you.

Non-HTTP traffic
Hardware LBs and NLB can handle non-HTTP traffic whereas ARR is a completely HTTP based solution. So if you’re looking into possibilities to distribute other types of traffic such as for SMTP based mail servers then ARR is not an option.

Sticky sessions
This feature means that if a client returns for a second request then the load balancer will redirect that traffic to the same web server. It is also called client affinity. This can be important for web servers that store session state locally so that when the same visitor comes back then we don’t want the state relevant to that user to be unavailable because the request was routed to a different web server.

Hardware LBs and ARR provide a lot of options to introduce sticky sessions including cookie-based solutions. NLB can only perform IP-based sticky sessions, it doesn’t know about cookies and HTTP traffic.

Your target should be to avoid sticky sessions and solve your session management in a different way – more on state management in a future post. If you have sticky sessions then the load balancer is forced to direct traffic to a certain server irrespective of its actual load, thus beating the purpose of load distribution. Also, if the server that received the first request becomes unavailable then the user will lose all session data and may receive an exception or unexpected default values in place of the values saved in the session variables.

Other types of load balancers

Software
With software load balancers you can provide your own hardware while using the vendor-supported software for load balancing. The advantage is that you can provide your own hardware to meet your load balancing needs which can save you a lot of money. 

Above valuable information I have copied from following link "https://dotnetcodr.com/2013/06/17/web-farms-in-net-and-iis-part-1-a-general-introduction/" for more details you can check this link

Monday, July 15, 2019

Eazy way to understanding Sql Join Concept

CReate table A(Code varchar(50), emp_no varchar(50))
Insert into A values ('101',12222)
Insert into A values ('102',23333)
Insert into A values ('103',34444)
Insert into A values ('104',45555)
Insert into A values ('105',56666)


CReate table B(Code varchar(50), City varchar(50), Country varchar(50))

Insert into B values ('101','Mumbai', 'India')
Insert into B values ('101','Delhi', 'India')
Insert into B values ('101','Hyderabad', 'India')
Insert into B values ('102','Chennai', 'India')
Insert into B values ('103','Kolkata', 'India')


Select * From A;
Select * From B;

Select * From A Inner Join B On A.Code = B.Code; -- Total Rows (5) =>   1(3),2,3
Select * From A Left Join B On A.Code = B.Code; -- Total Rows (7) =>   1(3),2,3,4,5
Select * From A Right Join B On A.Code = B.Code; -- Total Rows (5) =>   1(3),2,3
Select * From A Cross Join B; -- Total Rows (5) =>   1 to all (Repeat)

Thursday, May 30, 2019

Password validation on Asp.Net using regular expration

Case 1: Validation for 8-10 characters with alphabets,numbers and no special characters.

<asp:regularexpressionvalidator controltovalidate="txtPassword" display="Dynamic" errormessage="Password must be 8-10 characters long</br> with at least one numeric character." forecolor="Red" id="RegularExpressionValidator3" runat="server" validationexpression="(?!^[0-9]*$)(?!^[a-zA-Z]*$)^([a-zA-Z0-9]{8,10})$"> </asp:regularexpressionvalidator></asp:textbox>  

Case2: Validation for 8-10 characters with characters,numbers and special characters.
 
<asp:textbox id="txtpasswordwithNoUpperCharacter" runat="server" /> <asp:regularexpressionvalidator :="" display="Dynamic" errormessage="Password must be 8-10 characters long with at least one numeric,</br> one alphabet and one special character." forecolor="Red" id="RegularExpressionValidator2" validationexpression="(?=^.{8,10}$)(?=.*\d)(?=.*[a-zA-Z])(?=.*[!@#$%^&*()_+}{">.<,])(?!.*\s).*$" controltovalidate="txtpasswordwithNoUpperCharacter" runat="server"> </asp:regularexpressionvalidator>  

Case3: Validation for 8-10 characters with characters,numbers,1 upper case letter and special characters.

 <asp:textbox id="txtPasswordWithSpecialCharacter" runat="server"/> <asp:regularexpressionvalidator :="" display="Dynamic" errormessage="Password must be 8-10 characters long with at least one numeric,</br>one upper case character and one special character." forecolor="Red" id="RegularExpressionValidator1" validationexpression="(?=^.{8,10}$)(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%^&*()_+}{">.<,])(?!.*\s).*$" controltovalidate="txtPasswordWithSpecialCharacter" runat="server"/>

Important Notes in Csharp and SQL

The break, goto, continue, return and throw statements are known as jump statements.

An Abstract class can has access modifiers like private, protected, internal with class members. But abstract members cannot have private access modifier.

Constant fields or local variables must be assigned a value at the time of declaration and after that they cannot be modified. By default constant are static, hence you cannot define a constant type as static.

Abstract Class :Need to to provide default behaviors as well as common behaviors that multiple derived classes can share and override
http://www.dotnettricks.com/learn/csharp/a-deep-dive-into-csharp-abstract-class

Unlike fields, properties do not denote storage locations and you cannot pass a property as a ref or out paramete

Auto-implemented properties was introduced with C# 3.0, which make property declaration more concise. Unlike standard property, in auto-implemented property, wrapped or backing field is automatically created by the compiler but it is not available for use by
public int Name { get; set; }

A try block is used to throw multiple exceptions that can handle by using multiple catch blocks

More specialized catch block should come before a generalized one. Otherwise specialized catch block will never be executed

Implicit conversion of derived classes to base class is called Upcasting and
Explicit conversion of base class to derived classes is called Downcasting.

Once a delegate is created, the method it is associated will never changes because delegates are immutable in nature

delegate which holds the reference of more than one method is called multi-cast delegate. A multicast delegate only contains the reference of methods which return type is void. The + and += operators are used to combine delegate instances

There are following types of decision making statements in C#
If statement
If-Else statement
If-Else-If statement or ladder
Switch statement

C# introduces a new concept known as Indexers which are used for treating an object as an array. The indexers are usually known as smart arrays in C#

--==============================------------

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example : Primary key, Unique key, Alternate key are subset of Super Keys

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key

A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key

Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values

Views are virtual tables that are compiled at run time. The data associated with views are not physically stored in the view, but it is stored in the base tables of the view. We make views for security purpose since it restricts the user to view some columns/fields of the table(s)

-------=================================-----------------
Data Annotation Validator Attributes 
DataType, DisplayName, DisplayFormat, Required, ReqularExpression, Range, StringLength,
MaxLength, Bind, ScaffoldColumn(specify fields for hiding from editor forms)

 Partial view is like as user control in Asp.Net Web forms that is used for code re-usability. Partial views helps us to reduce code duplication

The main difference between two methods is that the Partial helper method renders a partial view into a string while RenderPartial method writes directly into the response stream instead of returning a string

We can enable the jQuery intellisense support in Visual Studio with MVC Razor by adding vsdoc.js (jquery-1.7.1-vsdoc.js)

Asp.net MVC Request Life Cycle
Browser
Request
Routing: Pattern matching system (RouteTable)
Mvc Handler: initiating the real processing (IHttpHandler)
Controller: MvcHandler uses the IControllerFactory instance and tries to get a IController instance (IControllerFactory )
Action Execution: Controller's ActionInvoker determines which specific action to invoke on the controller (ActionInvoker)
View Result: receives user input, prepares the appropriate response data, and then executes the result
View Engine: xecution of the View Result involves the selection of the appropriate View Engine
View: Action method may returns a text string,a binary file or a Json formatted data.
for more details https://www.dotnettricks.com/learn/mvc/aspnet-mvc-request-life-cycle

Procedure to Search an object in a database, Find a table used in how many Procedures




CREATE PROCEDURE [dbo].[spssearchcode]            
  @text varchar(250),            
  @dbname varchar(64) = 'admin'           
AS BEGIN           
SET NOCOUNT ON;            
            
if @dbname is null           
  Begin           
    --enumerate all databases.            
  DECLARE #db CURSOR FOR Select Name from master..sysdatabases            
  Declare @c_dbname varchar(64)            
            
  OPEN #db FETCH #db INTO @c_dbname            
  While @@FETCH_STATUS <> -1 --and @MyCount < 500            
   Begin           
     execute spssearchcode @text, @c_dbname            
     FETCH #db INTO @c_dbname            
   End              
  CLOSE #db DEALLOCATE #db            
 End --if @dbname is null            
Else           
 begin --@dbname is not null            
  declare @sql varchar(250)            
  --create the find like command            
  select @sql = 'select ''' + @dbname + ''' as db, o.name,m.definition '           
  select @sql = @sql + ' from '+@dbname+'.sys.sql_modules m '           
  select @sql = @sql + ' inner join '+@dbname+'..sysobjects o on m.object_id=o.id'           
  select @sql = @sql + ' where [definition] like ''%'+@text+'%'''           
  execute (@sql)            
 end --@dbname is not null            
END

REDIS Cache with C#, Learning Redis Cache, Capture DataTable into Redis Server

Redis Cache is a key-value cache that stores the information in a hash table format, providing the possibilities to store different types of structured data like strings, hashes, lists, sets, sorted sets, bitmaps and hyperloglogs

1. Install Redis server (redis-server.exe) on your machine

2. Create RedisConnectorHelper class file

    public class RedisConnectorHelper
    {
        static RedisConnectorHelper()
        {
            RedisConnectorHelper.lazyConnection = new Lazy(() =>
            {
                return ConnectionMultiplexer.Connect("localhost");
            });
        }

        private static Lazy lazyConnection;

        public static ConnectionMultiplexer Connection
        {
            get
            {
                return lazyConnection.Value;
            }
        }
    }


3. You will noticed here ConnectionMultiplexer is not found in your application, so add using NuGet Package manager, search "StackExchange.Redis"

4. After installation you are ready to work in Redis application

5. Create the connection object of Redis server like this
    var cache = RedisConnectorHelper.Connection.GetDatabase();

6.  Store your values in this way
     cache.StringSet($"Device_Status:{i}", "value");

7. Retrive Redis/Cached values this way
     var value = cache.StringGet($"Device_Status:{i}");

8.  Some time we need to store DataTable into session/cache object so same we can do in following way.

    Store Values in Redis server:
   
   string TableName = "GetEmpData";
   foreach (DataRow dr in GetEmpData().Rows)
   {
      cache.StringSet($"Device_Status:"+ TableName + "_" +dr["id"].ToString(), string.Format("{0} : {1} : {2} : {3}", dr["Id"].ToString(), dr["Name"].ToString(), dr["Mobile"].ToString(), dr["Date"].ToString()));
   }

    Retrive Values from Redis server:
 
  foreach (DataRow dr in GetEmpData().Rows)
   {
     var value = cache.StringGet($"Device_Status:" + TableName + "_" + dr["id"].ToString());
     Console.WriteLine($"New Valueeeeee={value}");
   }
 
9. Sample Code Download Here

10. For Details information you can also visit the below link,

https://www.c-sharpcorner.com/UploadFile/2cc834/using-redis-cache-with-C-Sharp/