asked on

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.

ASKER CERTIFIED SOLUTION

EvilPostIt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ammounpierre

ASKER

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 !

SOLUTION

EvilPostIt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ammounpierre

ASKER

by Mirroring you mean I would not need a witness ?

EvilPostIt

You only need a witness if you are using synchronous ("High-Safety") mode.

ammounpierre

ASKER

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 ?

EvilPostIt

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

ammounpierre

ASKER

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 ?

EvilPostIt

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.

EvilPostIt

Also, just re-read one of your comments. Log shipping and mirroring are 2 completely different things.

ammounpierre

ASKER

"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 !!)

EvilPostIt

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