?
Solved

Synchronous Vs Asynchrous mode in SQL 2008

Posted on 2011-02-15
12
Medium Priority
?
491 Views
Last Modified: 2012-05-11
Hello Gurus,

I would like to understand from the technical point of vue the difference from having a SQL Server in Synchronous mode Vs Async mode.
What would be the most efficient ? how does it work in details... which is faster ?
I am talking about a system that would be on a LAN ... (and what if on a WAN ?)

Thanks for all the details that u could send me.

0
Comment
Question by:ammounpierre
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 2000 total points
ID: 34894689
I would assume that you a refering to mirroring. In which case the main difference is quite simple.

With synchronous mirroring transactions are not commited at the principal until they are committed at the mirror database. Which would obviously slow down the principal database but would ensure that in the event of a disaster no data was lost.

Whereas asyncronous will just commit transacations to the principal without them having to be committed to the mirror. There would be room for possible data loss here.

Other than this they both work in pretty much the same way.
0
 

Author Comment

by:ammounpierre
ID: 34894831
Maybe I need to re-phrase my question.

From my understanding (and this is where I would need your comments Gurus) is that in Synchronous mode, data will be saved and committed to server2 then when server1 gets a confirmation from server2 that data has been saved, then data is committed to server1.
In this case, suppose that to save the data it would take 2sec, then with this scheme, it would take 4 seconds since the data has to be saved in 2 servers one after the other... that would be UNLESS the data is saved in both and NOT COMMITTED to Server1 untill waiting a response from Server2. In that case I would presume that the "extra" time would be then less than 2X....right ???


Now in Asynch mode, there is a transaction Log shipping... but how frequently can I schedule this ? is it on a time basis or transaction basis ?

thanks gurus !
0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 2000 total points
ID: 34894997
Mirroring works by effectivly syncing new information in the transaction log. A new transaction ready to commit in the transaction log of the principal would wait for comfirmation from the mirror before committing. Its just a lag in the commit rather than applying the change to the mirror and then to the principal.

The process would be more like...

- Transaction happens on principal, takes 2 secs to run the query and updates 1 record.
- Principal is ready to commit
- Transaction log record sent to mirror
- Mirror applies this record directly to the transaction log
- Mirror confirms success
- Principal finishes the transaction

Also bear in mind that SQL Server 2008 actually compresses the data stream for mirroring so this wont be the same amount of data as what has been added to the transaction log.

For more information have a look at the following.

http://msdn.microsoft.com/en-us/library/ms189901.aspx
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:ammounpierre
ID: 34895176
by Mirroring  you mean I would not need a witness ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34895201
You only need a witness if you are using synchronous ("High-Safety") mode.
0
 

Author Comment

by:ammounpierre
ID: 34895375
what then would be the difference ? in all cases my users that are connected to server1 when the server1 goes down...they need to disconnect and connect to server2 (using the right shortcut...) right ?
and that would be in sync and asyn modes ? or there is a difference here ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34895422
See now your talking connection strings. With SQL Server native client you can setup the failover partner so that the users dont need to change connection strings. For example here is a connection for ADO.NET using SQL Server Native Client.

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

Open in new window

0
 

Author Comment

by:ammounpierre
ID: 34895536
but my situation is as follows.  I have server1 with ip 192.168.0.1 and server2 with Ip 192.128.0.2
but I have my application (exe file) on both servers... server1 in shared folder \\server1\myapp.exe
and server2 under \\server2\myapp.exe (folders in sync)

now it is true that the connection string can take the partner...but the exe has to be somewhere... so if server1 fails...
then I need to "re-create a shortcut that would point myapp from the server2

right ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34895570
Probably best to have a load balanced app server then. Either that or setup some kind of load balancing or clustered access between the two shares.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34895576
Also, just re-read one of your comments. Log shipping and mirroring are 2 completely different things.
0
 

Author Comment

by:ammounpierre
ID: 34895655
"Probably best to have a load balanced app server then. Either that or setup some kind of load balancing or clustered access between the two shares."

care to explain more ?
(wouldnt mind to open a new question with it !!)
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34895682
From your perspective that may be a good idea, if you opened a new question in the Windows (Whatever version you are using) / SQL Server Zones and say what your setup is with regards to exe files and shares etc and how to make it highly available. You should get a much better response than i can give.

As an overview of Microsoft Network Load Balancing have a look at this.

http://technet.microsoft.com/en-us/library/cc780254(WS.10).aspx
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question