LOG SHIPPING VS. MIRRORING VS. REPLICATION - DotNet Brother

Log Shipping::

It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled.

Mirroring::

Database mirroring is a primarily software solution for increasing database availability.
It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.

Replication::

It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Components

Log Shipping::Primary server, secondary server and monitor server (Optional).
Mirroring::Principal server, mirror server, and witness server (Optional).
Replication::Publisher, Subscribers, Distributor (Optional).

Data Transfer

Log Shipping::T-Logs are backed up and transferred to secondary server.
Mirroring::Individual T-Log records are transferred using TCP endpoints.
Replication::Replication works by tracking/detecting changes (either by triggers or by scanning the log) and shipping the changes.

Server Limitation

Log Shipping::It can be configured as One to Many. i.e one primary server and many secondary servers. Or
Secondary server can contain multiple Primary databases that are log shipped from multiple servers.
Mirroring::It is one to one. i.e. One principal server to one mirror server.
Replication::

• Central publisher/distributor, multiple subscribers.
• Central Distributor, multiple publishers, multiple subscribers.
• Central Distributer, multiple publishers, single subscriber.
• Mixed Topology.

Types Of Failover

Log Shipping::Manual.
Mirroring::Automatic or manual.
Replication::Manual.

DB Access

Log Shipping::You can use a secondary database for reporting purposes when the secondary database restore in STANDBY mode.
Mirroring::Mirrored DB can only be accessed using snapshot DB.
Replication::The Subscriber Database is open to reads and writes.

Recovery Model

Log Shipping::Log shipping supports both Bulk Logged Recovery Model and Full Recovery Model.
Mirroring::Mirroring supports only Full Recovery model.
Replication::It supports Full Recovery model.

Restoring State

Log Shipping::The restore can be completed using either the NORECOVERY or STANDBY option.
Mirroring::The restore can be completed using with NORECOVERY.
Replication::The restore can be completed using With RECOVERY.

Backup/Restore

Log Shipping::This can be done manually or
through Log Shipping options.
Mirroring::User make backup & Restore manually.
Replication::User create an empty database with the same name.

Monitor/
Distributer/ Witness

Log Shipping::The monitor server should be on a server separate from the primary or secondary servers to avoid losing critical information and disrupting monitoring if the primary or secondary server is lost. . If a monitor server is not used, alert jobs are created locally on the primary server instance and each secondary server instance.
Mirroring::Principal server can’t act as both principal server and witness server.
Replication::Publisher can be also distributer.

Types Of Servers

Log Shipping::All servers should be SQL Server.
Mirroring::All servers should be SQL Server.
Replication::Publisher can be ORACLE Server.

SQL Server Agent Dependency/Jobs

Log Shipping::Yes. Log shipping involves four jobs, which are handled by dedicated SQL Server Agent jobs. These jobs include the backup job, copy job, restore job, and alert job.
Mirroring::Independent on SQL Server agent.
Replication::Yes. Snapshot agent, log reader agent & Distribution agent (transactional replication)
Merge agent (merge replication).

Requirements

Log Shipping::

• The servers involved in log shipping should have the same logical design and collation setting.
• The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.
• The SQL server agent should be configured to start up automatically.
• You must have sysadmin privileges on each computer running SQL server to configure log shipping.

Mirroring::

• Verify that there are no differences in system collation settings between the principal and mirror servers.
• Verify that the local windows groups and SQL Server logins definitions are the same on both servers.
• Verify that external software components are installed on both the principal and the mirror servers.
• Verify that the SQL Server software version is the same on both servers.
• Verify that global assemblies are deployed on both the principal and mirror server.
• Verify that for the certificates and keys used to access external resources, authentication and encryption match on the principal and mirror server.

Replication::

• Verify that there are no differences in system collation settings between the servers.
• Verify that the local windows groups and SQL Server Login definitions are the same on both servers.
• Verify that external software components are installed on both servers.
• Verify that CLR assemblies deployed on the publisher are also deployed on the subscriber.
• Verify that SQL agent jobs and alerts are present on the subscriber server, if these are required.
• Verify that for the certificates and keys used to access external resources, authentication and encryption match on the publisher and subscriber server.

Using With Other Features Or Components

Log Shipping::Log shipping can be used with Database mirroring, Replication.
Mirroring::Database mirroring can be used with
Log shipping, Database snapshots , Replication.
Replication::Replication can be used with log shipping, database mirroring.

DDL Operations

Log Shipping::DDL changes are applied automatically.
Mirroring::DDL changes are applied automatically.
Replication::only DML changes to the tables you have published will be replicated.

Database Limit

Log Shipping::No limit.
Mirroring::generally good to have 10 DB’s for one server.
Replication::No limit.

latency

Log Shipping::There will be data transfer latency. >1min.
Mirroring::There will not be data transfer latency.
Replication::Potentially as low as a few seconds.

Committed /
Uncommitted
Transactions

Log Shipping::Both committed and uncommitted transactions are transferred to the secondary database.
Mirroring::Only committed transactions are transferred to the mirror database.
Replication::Only committed transactions are transferred to the subscriber database.

Primary key

Log Shipping::Not required.
Mirroring::Not required.
Replication::All replicated table should have Primary Key.

New Created Database&
Stored Procedure

Log Shipping::Monitoring and history information is stored in tables in msdb, which can be accessed using log shipping stored procedures.
Replication::Creates new SPs ( 3 Sps of one table).
Distribution Database.
Rowguid column will be created.

Individual Articles

Log Shipping::No. Whole database must be selected.
Mirroring::No. Whole database must be selected.
Replication::Yes. Including tables, views, stored procedures, and other objects. Also filter can be used to restrict the columns and rows of the data sent to subscribers.

FILESTREAM

Log Shipping::Log shipping supports FILESTREAM.
Mirroring::Mirroring does not support FILESTREAM.
Replication::Replication supports FILESTREAM.

DB Name

Log Shipping::The secondary database can be either the same name as primary database or it may be another name.
Mirroring::It must be the same name.
Replication::It must be the same name.

DB Availability

Log Shipping::In case of standby mode: read only database.
In case of restoring with no recovery: Restoring state.
Mirroring::In Recovery state, no user can make any operation.
You can take snapshot.
Replication::Snapshot (read-only).
Other types (Database are available).

Warm/ Hot Standby Solution

Log Shipping::It provides a warm standby solution that has multiple copies of a database and require a manual failover.
Mirroring::When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).
Replication::It provides a warm standby solution that has multiple copies of a database and require a manual failover.

System Data Transferred

Log Shipping::Mostly.
Mirroring::Yes.
Replication::No.

System Databases

Mirroring::You cannot mirror the Master, msdb, tempdb, or model databases.

Mode Or Types

Log Shipping::

• Standby mode (read-only)-you can disconnect users when restoring backups .
• No recovery mode (restoring state)-user cannot access the secondary database.

Mirroring::

• high-safety mode supports synchronous operation.
• high-performance mode, runs asynchronously.
• High-safety mode with automatic failover.

Replication::

• Snapshot replication.
• Transactional replication.
• Transactional publication with updatable subscriptions.
• Merge publication.
• Pull/Push subscription.

WCF Interview Question and Answer - DotNet Brother

What is WCF?
Windows Communication Foundation or just WCF is a programming framework used to build applications that communicate with each other. It is a part of the .NET Framework dedicated to communications.

What are the different WCF binding available?
• BasicHttpBinding
• WSHttpBinding
• WSDualHttpBinding
• WSFederationHttpBinding
• NetTcpBinding
• NetNamedPipeBinding
• NetMsmqBinding
• NetPeerTcpBinding
• MsmqIntegrationBinding

What is the proxy for WCF Service?
A proxy is a class by which a service client can Interact with the service. By the use of proxy in the client application we are able to call the different methods exposed by the service.

What is the difference between XMLSerializer and the DataContractSerializer?
1. DataContractSerializer is the default serializer fot the WCF
2. DataContractSerializer is very fast.
3. DataContractSerializer is basically for very small, simple subset of the XML infoset.
4. XMLSerializer is used for complex schemas

What is endpoint in WCF service?
The endpoint is an Interface which defines how a client will communicate with the service. It consists of three main points: Address,Binding and Contract.

What is the difference between WCF Service and Web Service?
1) WCF Service supports both http and tcp protocol while webservice supports only http protocol.
2) WCF Service is more flexible than web service.
3) WCF is combination of WebService, Remoting, MSMQ

What are the various ways of hosting a WCF service?
Self hosting the service in his own application domain. This we have already covered in the first section. The service comes in to existence when you create the object of ServiceHost class and the service closes when you call the Close of the ServiceHost class. Host in application domain or process provided by IIS Server. Host in Application domain and process provided by WAS (Windows Activation Service) Server.

What is service and client in perspective of data communication?
A service is a unit of functionality exposed to the world.
The client of a service is merely the party consuming the service.

List the behaviors that WCF service uses during its execution.
A WCF service uses the following list of behaviors during its execution.
1. Throttling
2. Security
3. Instancing
- PerCall
- PerSession
- Single
4. Error handling
5. Concurrency
- Multiple
- Single
- Reentrant
6. Transactions

What is WCF? Explain.
WCF (A platform for SOA)
Unified programming model, provided by WCF, helps in building Service Oriented Application (SOA) through some simple implementation. WCF Service features for usage in SOA implementation.
Uses of WCF services:
• Expose functionality using contracts to clients
• Can be deployed over various protocols to satisfy various distributed and interoperable scenarios
• Execute autonomously and do not impact another service in case of failure
• Design and implementation are separate from business logic, which eases migration to SOA design

What is the programming life cycle of WCF?
Windows Communication Foundation (WCF) enables applications to communicate whether they are on the same computer, across the Internet, or on different application platforms. This topic outlines the tasks that are required to build a WCF application. The basic tasks to perform, are, in order to:
1. Define the Service Contract
2. Implement the Service Contract
3. Configure the Service by specifying endpoint information and other behavior information
4. Host the service in an application
5. Build the client application

Define WCF data contract
- A data contract is defined by using a Data Contract Attribute on a class or structure.
- Members of the data structure which will be used by the service need to be marked with the Data Member Attribute.
- Only those members will be transferred between the service and its client. In the same way that different classes can implement the same interface, different classes can implement the same Data Contract, and can serialize and deserialize the same data.

What is the advantage and disadvantage of implementing IExtensibleDataObject?
WCF guidelines recommend enhancing all data contracts with support of IExtensibleDataObject interface, to preserve unexpected data from clients. During deserialization, superfluous data is placed in a dictionary on the service side and during serialization, the same data is written as XML as it was originally provided by the client. This is very useful to preserve data from version 2.0 services at a version 1.0 client. It is also useful in case where downstream calls from version 2.0 services go to other services handling version 1.0.
However, there is also a disadvantage of implementing IExtensibleDataObject. It carries risks of denial of service (DoS) and unnecessary use of server resources.

What is the role of WSDL in WCF?
WSDL stands for Web Service Description Language. The WCF service exposes the WSDL document for the clients, to generate proxies and the configuration file. The WSDL file provides the following information for the consumers of the WCF service.
1. Provides the information about the service contract and operations available.
2. Provides the information about all the end points exposed by the WCF service.
3. Provides the information about the messages and types that can be exchanged between the client and the WCF service.
4. WSDL also provides any information about the policies used.

What are the different types of bindings available in WCF?
Basic Binding:
Offered by the BasicHttpBinding class, this is designed to expose a WCF service as a legacy ASMX web service, so that old clients can work with new services. When used by the client, this binding enables new WCF clients to work with old ASMX services.
TCP Binding:
Offered by the NetTcpBinding class, this uses TCP for cross-machine communication on the intranet. It supports a variety of features, including reliability, transactions, and security, and is optimized for WCF-to-WCF communication. As a result, it requires both the client and the service to use WCF.
Peer Network Binding:
Offered by the NetPeerTcpBinding class, this uses peer networking as a transport. The peer network-enabled client and services all subscribe to the same grid and broadcast messages to it.
IPC Binding:
Offered by the NetNamedPipeBinding class, this uses named pipes as a transport for same-machine communication. It is the most secure binding since it cannot accept calls from outside the machine and it supports a variety of features similar to the TCP binding.
Web Service (WS) Binding:
Offered by the WSHttpBinding class, this usHTTP or HTTPS for transport, and is designed to offer a variety of features such as reliability, transactions, and security over the Internet.
Federated WS Binding:
Offered by the WSFederationHttpBinding class, this is a specialization of the WS binding, offering support for federated security.
Duplex WS Binding:
Offered by the WSDualHttpBinding class, this is similar to the WS binding except it also supports bidirectional communication from the service to the client.
MSMQ Binding:
Offered by the NetMsmqBinding class, this uses MSMQ for transport and is designed to offer support for disconnected queued calls.
MSMQ Integration Binding:
Offered by the MsmqIntegrationBinding class, this converts WCF messages to and from MSMQ messages, and is designed to interoperate with legacy MSMQ clients.

What are Various Ways of Hosting WCF Services?
Three ways of hosting WCF Services:
1. Self-hosting the service in its own application domain. The service comes into existence when you create the object of Service Host class and the service closes when you call the Close of the Service Host class.
2. Host in application domain or process provided by IIS Server.
3. Host in application domain and process provided by WAS (Windows Activation Service) Server. 

Watermark Text for Textbox by using JavaScript - DotNet Brother

This is the simple demonstration in JavaScript how to create watermark text for textbox control. This requirement is the mostly used in web application.

Here the given codes to create the watermark text for textbox control.

For Example:

<head runat="server">

    <title>Sample for Watermark Text Using JavaScript</title>

    <script language="javascript" type="text/javascript">

        function FuncWaterMark(txtEmail, event) {

            var strVal = "Enter EmailID Here";

            //Here to check textbox length and event type

            if (txtEmail.value.length == 0 & event.type == "blur") {

                txtEmail.style.color = "Gray";//setting text color

                txtEmail.value = strVal; //setting default text in textbox

            }

            // Here to check textbox value and event type

            if (txtEmail.value == strVal & event.type == "focus") {

                txtEmail.style.color = "black";

                txtEmail.value = "";

            }

        }

    </script>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <b>Email ID:</b>

        <asp:TextBox ID="txtEmailID" runat="server" Text="Enter EmailID Here" ForeColor="Gray"

            onblur="FuncWaterMark(this, event);" onfocus="FuncWaterMark(this, event);" />

    </div>

    </form>

</body>

</html>

 

Output:

How to Drop Primary Key Constraint - DotNet Brother

Here I described how to drop primary key form table. There are two way for doing this. 

One is when we are not providing constraint key name during creating table.

Second is given constraint name during creating table.

Without giving constraint key

 

First create a table in SQL server with primary key only like below code.

create table info ( id int primary key, [name] varchar(50) not null, address varchar(50) not null )

 

Copy the key value from created table ‘Keys’. This key automatic created by sql server.

Write code for drop primary key

alter table info drop PK__info__1A14E395

 

With giving constraint key

Create table like below

create table info ( id int, [name] varchar(50) not null, address varchar(50) not null constraint pk_info_id primary key clustered(id asc) )

 

Drop primary key

alter table info

drop pk_info_id

DELETE, TRUNCATE with RESEED Identity in SQL Server - DotNet Brother

There is miner difference between DELETE, TRUNCATE with RESEED Identity in SQL Server. For demonstration first we create a table with Identity column then perform DELETE and TRUNCATE operation.

-- CREATE TABLE CREATE TABLE STUDENT_INFO ( [ID] INT IDENTITY(1,1) PRIMARY KEY, [ROLL_NO]INT NOT NULL, [NAME] VARCHAR(50) NOT NULL, )   -- INSERT RECORDS INSERT INTO STUDENT_INFO VALUES(101,'HAIDAR') INSERT INTO STUDENT_INFO VALUES(102,'ARUN')   -- DISPLAY TABLE RECORD SELECT * FROM STUDENT_INFO

 

Screen Shot

Effect of DELETE statement

Delete records from table using DELETE command then insert new record and see table records.

-- DELETE RECORDS DELETE FROM STUDENT_INFO   --INSERT SOME NEW RECORD INSERT INTO STUDENT_INFO VALUES(103,'MANOJ') INSERT INTO STUDENT_INFO VALUES(104,'SAURABH')   -- DISPLAY TABLE RECORDS SELECT * FROM STUDENT_INFO

 

Screen Shot

Here we see, When the DELETE statement is executed without WHERE clause it will delete all the rows. However, when a new record is inserted the identity value (ID column) is increased from 2 to 3. It does not reset but keep on increasing.

Effect of TRUNCATE statement

Delete records from table using TRUNCATE command then insert new record and see table records.

-- TRUNCATE RECORDS TRUNCATE TABLE STUDENT_INFO   -- INSERT NEW RECORDS INSERT INTO STUDENT_INFO VALUES(105,'SANDEEP') INSERT INTO STUDENT_INFO VALUES(106,'ROHIT')   -- DISPLAY TABLE RECORDS SELECT * FROM STUDENT_INFO

 

Screen Shot

Now we see, When the TRUNCATE statement is executed it will remove all the rows. However, when a new record is inserted the identity value (ID column) is increased from 1 (which is original value). TRUNCATE resets the identity value to the original seed value of the table.

Create a user define function using SQL Server 2008 R2 - DotNet Brother

In this blog I am trying to explain how create a user define function using SQL Server 2008 R2.

In the SQL Server 2008 R2 have many predefined functions aggregate functions and Scalar functions which are worked for resolve our complex problems, and we are also known the method or functions resolve the complexity.

Aggregate functions:

SQL aggregate functions return a single value, which is calculated from values in a column. Such as

1.       AVG () - Returns the average value.

2.       COUNT () - Returns the number of rows.

Scalar functions:

SQL scalar functions return a single value, based on the input value.

1.       UCASE () - Converts a field to upper case.

2.       LCASE () - Converts a field to lower case.

UDF (User Define Function):

UDF is the concept of User-Defined Functions that allow you to define your own T-SQL functions that can allow zero or more parameters and return a single scalar data value or a table data type.

We can create three types of UDF in SQL Server 2008 R2 and they are:

1.       Scalar

2.       Inline Table-Valued

3.       Multi-statement Table-valued.

Below I am giving an example for User Define Scalar Function which is single parameterizes   function.

Function:

CREATE FUNCTION whichCountry

(@City nvarchar(15))

RETURNS varchar(30)

AS

BEGIN

declare @Return varchar(30)

select @return = case @City

when 'New Delhi' then 'India'

when 'Banglore' then 'India'

when 'Colombo' then 'SriLanka'

when 'New York' then 'U.S.A'

when 'Chicago' then 'U.S.A'

when 'Dhaka' then 'Bangladesh'

when 'Beijing' then 'China'

when 'Kyoto' then 'Japan'

else 'Unknown'

end

return @return

end

Above function will accept a value City name and return its Country name .

Now we will create a table:

Create table tblEmployeeCity

(

    vEmpID varchar(10),

    eName varchar(50),

    vCity varchar(50) 

)

Insert Values in table:

INSERT INTO [dbo].[tblEmployeeCity] VALUES ('E001','Ajay Shukla','New York')

INSERT INTO [dbo].[tblEmployeeCity] VALUES ('E002','Sanjay Shukla','Beijing')

INSERT INTO [dbo].[tblEmployeeCity] VALUES ('E003','Vijay Shukla','Banglore')

 

Use our function:

select Country=dbo.whichCountry(tblEmployeeCity.vCity), tblEmployeeCity.*

from tblEmployeeCity

Above statement will make a virtual column Country’s name and whichCountry () method accepts the value of vCity column and whichCountry () method match the vCity columns values in its code such as if value is Banglore then whichCountry () method will return the India. After that below will show on your screen:

Below I’m using the UDF whichCountry () method during creating a table:

create table tblEmployeeFullInformation

(

  vName varchar(50),

  vCity varchar(15),

  vCountry as (dbo.WhichCountry(vCity))

)

When we will insert values in the tblEmployeeFullInformation table we need only name and Employee Name and its city but country name will automatically get via whichCountry () method.

Insert Values in tblEmployeeFullInformation:

INSERT INTO tblEmployeeFullInformation (vName,vCity) VALUES ('Vijay Shukla','Banglore')

INSERT INTO tblEmployeeFullInformation (vName,vCity) VALUES ('Ajay Shukla','New York')

INSERT INTO tblEmployeeFullInformation (vName,vCity) VALUES ('Sanjay Shukla','Beijing')

Inline table Value:

Its returns a table data type and is an extravagant alternative to a view as the user-defined function, it will take parameters into a T-SQL select command and in synopsis provide us with a parameterized, non-updateable view of the underlying tables.

CREATE FUNCTION EmployeeByCountry

(@Country varchar(30))

RETURNS TABLE

AS

RETURN

  SELECT dbo.WhichCountry(tblEmployeeCity.vCity) as tblEmployeeFullInformation,

         tblEmployeeCity.*

  FROM tblEmployeeCity

  WHERE dbo.WhichCountry(tblEmployeeCity.vCity) = @Country

GO

Above code will create a table with EmployeeByCountry name. And when this table will execute with T-SQL select command then we also need to pass a parameter with the table name below is example:

select * from EmployeeByCountry('India')

Output:

Multi-statement Table-valued.

CREATE FUNCTION Employee

( @ID varchar(50) )

RETURNS

@EmployeeInfo table (

Emp_name Varchar(50),

Emp_City Varchar(20)

)

AS

BEGIN

INSERT INTO @EmployeeInfo SELECT eName,vCity FROM tblEmployeeCity WHERE vEmpID = @ID

 

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO @EmployeeInfo VALUES ('','No Enployee Found')

END

 

RETURN

END

GO

Above code will create a virtual table which is shows the employee information as Employee Name and Employee City.

Execute:

SELECT * FROM Employee('E001')