Solved

Synchronous Vs Asynchrous mode in SQL 2008

Posted on 2011-02-15
12
479 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
  • 7
  • 5
12 Comments
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 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 500 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now