• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

Synchronous Vs Asynchrous mode in SQL 2008

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
ammounpierre
Asked:
ammounpierre
  • 7
  • 5
2 Solutions
 
EvilPostItCommented:
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
 
ammounpierreAuthor Commented:
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
 
EvilPostItCommented:
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
ammounpierreAuthor Commented:
by Mirroring  you mean I would not need a witness ?
0
 
EvilPostItCommented:
You only need a witness if you are using synchronous ("High-Safety") mode.
0
 
ammounpierreAuthor Commented:
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
 
EvilPostItCommented:
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
 
ammounpierreAuthor Commented:
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
 
EvilPostItCommented:
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
 
EvilPostItCommented:
Also, just re-read one of your comments. Log shipping and mirroring are 2 completely different things.
0
 
ammounpierreAuthor Commented:
"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
 
EvilPostItCommented:
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now