Replication – A way of disaster management

What is Replication?

     Replication is another way of disaster recovery with the help of this we can recover our database and we do not need to do much manual efforts.

Replication is the way by which you can transfer data or database object from one server to another server, synchronize them, and maintain them identical.

The interesting part is you can implement replication for any location like LAN, WAN, Internet without much effort.

What is the initial requirement for Replication?

 The replication can work on SQL server 2000 2005, 2008.

What are the good points of Replication?

  There are many advantage of replication

  • High availability solution
    • Minimum error
    • Easy to implement

 What are the points which we would not like for replication?

  • Main server overhead increase
  • If there is failover  occurs then  we have to handle that manually

 What are the terminologies we used in Replication?

We used following terminologies in replication

1)   Publisher:-

The publisher is main database source which publish data or other data object to different server or we can say subscriber.

2)    Subscriber :-

The Subscriber by name it is clear that it subscribe the database from publisher server.

 3)   Distributor :-

The distribution is the server which manages flow of the data. It not mandatory to have distributor always.  A publisher sever can manage role of both (publisher & distributor).

4)   Article:-

An article can be any data object like data table, Rows, Store procedure or view. A publisher can be published number of article.

What are the different types of Replication?

 We can implement 3 type of replication

Snapshot Replication, Transactional Replication, Merge Replication.

Snapshot Replication:-

      By the name it is clear that in this publisher take snap shot of entire database and publish to the subscriber.   And when its take full snap shot obviously it taking it consumes more resource. Or we can say its resource intensive process.  It also known as simple replication. It used where database changes are rarely.

Transactional Replication:  It also known as dynamic replication.

In this the publisher and subscriber are always synchronized. It used where we need exact same copy on subscriber with immediate effect. It can be useful when subset of database or table is required.

Merge Replication:-

   It’s very interesting replication by the name it’s clear that it use merge method. In this both publisher and subscriber has freedom to work without network and when they connect the replication agents check both the database and merge each other’s changes. In merging if any conflict occurs than preference is given to publisher.

Later on in the post we will get a brief  introduction how to implement  replication tillthan enjoy definition :).

 Thanks & Regards

Rajat

Log Shipping – A way for disaster management

 Hello friends,

Today we will  see  how log shipping is helpful to us. so just checkout basic first  then we will  implement it.

What is Log Shipping?

 Log Shipping is another one of most fascinating feature provided by SQL Server for disaster management. With the help of this technique we can update keep distributed copy of our database on another server. It can backup transaction log, restore them, distribute them to different location. To achieve this automated process SQL server used SQL server job agents.

Or we can say Log shipping has following functionality

  • It backups transaction log of main server
  • It  transfers transaction log to predefine distributed location
  • It restore transaction log to secondary server

 

What is the initial requirement for achieving the Log shipping?

Log shipping is handle with SQL server 2005 version onwards no other  extra software or hardware require to achieve this.

What are the different terminologies used for Log Shipping?

For Log shipping we have used following terminologies  

  • Primary server:-

Primary server is main server from where we have to copy the database transaction log it contain main database or primary database server.

  • Secondary Server :-

Secondary server is another server or we can say its stand by server on which transaction log is restored to maintain database availability. The secondary database can have same database objects & data after Log shipping.

  • Monitor server :-

Monitor server by name it’s clear that it maintain or monitoring the activity of log shipping.

   What are the benefits of Log Shipping?

  • Easy  to implement   no extra efforts required to maintain it
  • No extra software or hardware required
  • Fast then other previous backup process
  • Multiple secondary server is available

What the unlike things with Log Shipping?

The things which we can say not like for our use is

  • No automated failover recovery  we have to manually shift the server  setting to activate secondary server  when primary server is down
  • It not reflects immediate effect on secondary server.
  • Log shipping cannot be scripted it means you cannot mimic production server logs hipping to testing server.
  • Possible Database Loss if primary server fails.
  • Difficult troubleshooting

In next few post i will show how to implement Log shipping.

Till than enjoy the  SQL Server.

Thanks

Rajat

step-by-step-sql-server-mirroring-a-way-of-disaster-recovery-part-IInd

Hello Friends,

I hope you have lovely weekend.

Today I am going to continue our second part of mirroring which is part II. In this part we are going to implement mirroring step by step. just few steps and mirroring will work  for us.

Step 1:- Just right click on primary server’s database on which we have to setup mirroring.

  Once you right click you get following option just click on Mirroring option as shown in figure.

Just click on “Configure Security option”

 Step 2:- Once you click the mirroring option you will get following option screen.

If you want witness server then check witness server yes option otherwise continue with no option

I don’t require that so I selected “no” option.

 Step 3:-

 Once you click next then you will get following screen related primary server.

You just need to click next.

 

Step 4:- In the next step you will ask for mirror server so just connect with the mirror server.

**** The point which needs to remember that on mirror server database   the database should be restored with “No recovery option” with same name.

 wintness server

Step 5:- You will get Service account screen as shown below once you clear the step 4.

Now here if you have any special credential for services then give that credential otherwise leave it blank in my case I keep it blank.

 

Step 6:- Once you pass with screen 5 you get following screen. If everything successful then you will get success status as shown in below fig.

 

Step 7:- when you click finish then you will get mirroring option as shown in fig.

Just click on start mirroring option then mirroring start if everything is right.

 sqlserver mirroring

I am 80% sure that mirroring will work with this step  but 20% not just because sometimes  few errors comes like authentication, Server URL & Port etc so In coming chapters I will explain  the errors which will you face in implementing  mirroring.

 Till than enjoy mirroring.

 Thanks 🙂

Rajat Jaiswal

Step by Step SQL Server Mirroring a way of Disaster Recovery Part -1

After reading few articles and self analysis I think SQL server mirroring can be understand by my style. So I note down all the queries which are in my mind and try to find out answer for them. Other than that
I have implemented step by step creation of mirroring. Hope it will work for other programmers, DBA also for understanding.
What is SQL Server Mirroring?
 SQL Server Mirroring is latest feature supported in SQL Server 2005 for disaster management. It assures data availability if principal server fails.  In the mirroring database transaction log transfer from one server to another server automatically. And within few minutes other stand by server is ready if main server get down or some fault occurred.
What is initial requirement for SQL Server Mirroring?
For SQL server mirroring we should have SQL SERVER 2005 with Sp1 onward.
What are the advantages of SQL Server Mirroring?

• It provides high availability of database.
• Comparatively easy to implement.
• No Extra hardware required to implement.
What are the drawbacks of SQL Server Mirroring?
Its individual choice whether they take it as a drawback or extra feature or requirement. According to me below is main point which I don’t like.
• It slowdown your main database server.
• In some case we cannot use mirror database directly because it treated as read only database or we can say Snapshot.

What are the main terminologies used with SQL Server Mirroring?
Below are main terminologies which I think used in mirroring:-
Principal Server: – The principal server is the server which is mainly responsible to give response to end user. The end user is connected with this server first. The Primary database exists on this server.
Mirror Server: – The mirror server contains the copy of main database. The copy of main database is called mirror database and the server is called mirror server.  In other words we can its stand by server which is updated by principal server by providing transaction log. Ideally it is not connected with end user.
Witness server: – The Witness server is an optional server. Witness server different from Principal server & Mirror server. It provides mechanism for automatic failover. In other words we can say that when primary server fail then end user connected to witness server instead of mirror which help out end user for database failover situation.
End points: – By the name it clear that it contain address and port number for connection between principal
Quorum – Quorum is the minimum relationship among the entire connected server.

What are the different modes of mirroring?
Basically mirroring work with 2 modes which are synchronous & Asynchronous.
1) Synchronous Mode: – This is high safety mode. In this every transaction which is committed on principal server will also need to be committed on mirror server on same time.  It acknowledges end user only when transaction is committed on both servers. It can be achieve with  2 options 
a) High safety with automatic failure-
By then name it is clear that if something going wrong means failure happened then we have to manually take care of end user connectivity.
b)   High safety without automatic failure:-
With help of this feature we don’t have to do much effort at the time of failure automatically end user pointed to another server.

2) Asynchronous Mode: – This is high availability mode. In this principle server send transaction log but he don’t wait for acknowledge response. Means principle server committed transaction without waiting for response of mirror database. It does not provide automatic failover to the end user.
 
In Next part we will take a look for  step by step implementation.

 Thanks  &  Enjoy Mirroring 🙂 

Rajat