April 11, 2012 at 10:35 am
hi .
what is the exactly different between mirroring and replication?
and in which situation we can use them?(each of them)
thank you
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 11, 2012 at 11:48 am
1.What is mirroring and how would you configure mirroring?
•Database mirroring is a primarily software solution for increasing database availability. Mirroring is one of the disaster recovery solution and this feature came with SQL server 2005. Mirroring is done between one database to another database at a time and the both database has to have the same name. Mirroring creates a secondary copy of data.
•End Point – is a SQL server object that enable SQL server to communicate over the network. It encaptualtes a transport protocol and a port number.
•Fail over – When the principle database fails, database mirroring provides a mechanism to fail over to the mirroring database.
There are three operation modes in mirroring
•High Availability ? in high availability, there is a witness server for automatic failover and sends the data synchronously.
•High Protection ? in high protection mode, there is not witness server so, therefore no automatic failover and sends the data synchronously.
•High Performance ? in high performance, the data gets send asynchronously which means the transactions gets committed to primary database first then applied to mirror.
Configuration Set up
•Before you set up mirroring, you need to make sure the database is in full recovery model.
•Set up the Principle/Mirror server and port number and make sure they are both are in the same edition.
•If the server instances are running under different domain user accounts, each requires a login in the master database of the others.
•Initial synchronization of full/log backup and restore with no recovery.
2.What is replication and how do you configure it
Set of technologies for coping and distributing data and data objects from one database to another database and then synchronizing between database to maintain consistency. SQL Server replication allows database administrators to distribute data to various servers throughout an organization.
There are 3 types of replication
•Transactional Replication ? Offers a more flexible solution for databases that change on a regular basis. With transactional replication, the replication agent monitors the publisher for changes to the database and transmits those changes to the subscribers. This transmission can take place immediately or on a periodic basis.
•Snapshot Replication ? Snapshot replication acts in the manner its name implies. The publisher simply takes a snapshot of the entire replicated database and shares it with the subscribers. Of course, this is a very time and resource-intensive process. For this reason, most administrators don’t use snapshot replication on a recurring basis for databases that change frequently. There are two scenarios where snapshot replication is commonly used. First, it is used for databases that rarely change. Second, it is used to set a baseline to establish replication between systems while future updates are propagated using transactional or merge replication.
•Merge Replication ? Merge replication allows the publisher and subscriber to independently make changes to the database. Both entities can work without an active network connection. When they are reconnected, the merge replication agent checks for changes on both sets of data and modifies each database accordingly. If changes conflict with each other, it uses a predefined conflict resolution algorithm to determine the appropriate data. Merge replication is commonly used by laptop users and others who can not be constantly connected to the publisher.
You may wish to implement replication in your organization for a number of reasons, such as:
•Load balancing ? Replication allows you to distribute your data to a number of servers and then distribute the query load among those servers.
•Offline processing ? You may wish to manipulate data from your database on a machine that is not always connected to the network.
•Redundancy ? Replication allows you to build a failover database server that’s ready to pick up the processing load at a moment’s notice.
Regards,
SQLisAwe5oMe.
April 11, 2012 at 12:20 pm
thank you.it was so helpful.
can we do load balancing with mirroring ?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 11, 2012 at 12:37 pm
No, Mirroring is a DR solution. I believe you cannot use Mirroring for load balancing. If something goes wrong with Principle Server, you have the Mirror DB to bring back the system to current.
Regards,
SQLisAwe5oMe.
April 11, 2012 at 12:42 pm
appreciate.thanks
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 11, 2012 at 12:42 pm
SQLCrazyCertified (4/11/2012)
No, Mirroring is a DR solution. I believe you cannot use Mirroring for load balancing. If something goes wrong with Principle Server, you have the Mirror DB to bring back the system to current.
In 2005 , 2008, and 2008R2 you cannot read from the mirrored database until it is no longer the mirror; i.e. failover. However, in 2012 you CAN. In which case it may be a solution for load balancing and HADR.
Jared
CE - Microsoft
April 11, 2012 at 12:45 pm
I beleive in 2005, 2008 and 2008R2, you can read from mirror DB by doing a snapshot of mirror. Your thoughts.
Regards,
SQLisAwe5oMe.
April 11, 2012 at 12:46 pm
i am using sql server2008.and so you mean when the data is restoring we can use that ?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 11, 2012 at 12:48 pm
In mirroring, the data is not "restored." It is synchronously applied to both the primary and secondary server so that the data is always in sync. Failover happens when the primary site goes down and the secondary takes over. This is when you can read the "mirrored" database.
Jared
CE - Microsoft
April 11, 2012 at 1:27 pm
SQLKnowItAll (4/11/2012)
In mirroring, the data is not "restored." It is synchronously applied to both the primary and secondary server so that the data is always in sync. Failover happens when the primary site goes down and the secondary takes over. This is when you can read the "mirrored" database.
Is it right to use the term "Primary" & " Secondary" in mirroring? Isn't it used in Log Shipping.
Mirroring uses Principal and Mirror.
TA
Regards,
SQLisAwe5oMe.
April 11, 2012 at 1:31 pm
SQLCrazyCertified (4/11/2012)
SQLKnowItAll (4/11/2012)
In mirroring, the data is not "restored." It is synchronously applied to both the primary and secondary server so that the data is always in sync. Failover happens when the primary site goes down and the secondary takes over. This is when you can read the "mirrored" database.Is it right to use the term "Primary" & " Secondary" in mirroring? Isn't it used in Log Shipping.
Mirroring uses Principal and Mirror.
TA
I'm not that picky... 🙂
Jared
CE - Microsoft
April 11, 2012 at 1:50 pm
so when the principal server is failed, the mirroring server is coming .so is the principal sync with mirroring server after failing?or we should sync that?
---------------------------------------------------
baaaaaaaaaaaaaleh!!! (Ehs_Akb)
April 11, 2012 at 1:55 pm
Try reading through this... http://msdn.microsoft.com/en-us/library/ms189850.aspx
Jared
CE - Microsoft
April 11, 2012 at 2:20 pm
Larry Page(Ehs_Akb) (4/11/2012)
so when the principal server is failed, the mirroring server is coming .so is the principal sync with mirroring server after failing?or we should sync that?
Well, it depends on how you configure it. Like I mentioned before, there are different operation modes in mirroring. If you are new to mirroring, read the article and then if you are still confused then ask specific questions.
Thanks,
TA
Regards,
SQLisAwe5oMe.
April 13, 2012 at 12:34 pm
Larry Page(Ehs_Akb) (4/11/2012)
i am using sql server2008.and so you mean when the data is restoring we can use that ?
Larry, Whichs edition are you using?.....if it's enterprise then you are able to create a snapshot of the mirror and querying off it.
TA
Regards,
SQLisAwe5oMe.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply