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
Solved

Synchronous Vs Asynchrous mode in SQL 2008

Posted on 2011-02-15
12
483 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 rs - Sum each category by month 4 41
SQL Server Insert where not exists 24 46
SSRS  - Dropdown with Null 3 29
SSRS 2013 - Overlapping reports 2 32
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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