replication

[aleu]

Hi everybody,

Could you please advise me what would be the best method to synchronize
MSSQL data in the following scenario:

SiteA (live site) ----- T1 link ----- SiteB (redundant site)

Each site consists of web server and MSSQL 2005 server. SiteA is the
site that my customers normally use when accessing my store. SiteB is
the backup site to which customers will be directed, should something
happen to Internet connectivity of siteA. When the failover occurs,
client request are pointing to my redundant web servers within a minute
(this happens automatically, as I am using the hosted DNS service). I
was wondering, what is the best method of keeping the database at siteB
up to date with all the transactions that took place at siteA? I do not
want my customers to access the redundant database if it does not
contain all the transactions. What would you advise?

Thanks,
AL

[Roy Harvey (SQL Server MVP)]

The best place to ask this is microsoft.public.sqlserver.replication.

I am no replication expert, but your requirement sounds like it needs
Database Mirroring. I am guessing that a T1 would be a bit slow for
synchronous mode, also known as high-safety mode. "Under high-safety
mode, when a session begins, the mirror server synchronizes the mirror
database with the principal database as quickly as possible. Once the
databases are synchronized a committed transaction is committed on
both partners, at the cost of increased transaction latency."

That would leave asynchronous operation mode, also known as
high-performance mode. "The mirror server attempts to keep up with
the log records sent by the principal server. The mirror database
might lag somewhat behind the principal database, though, typically,
the gap between the databases is small. However, the gap can become
substantial if the principal server is under a heavy work load or the
system of the mirror server is over loaded."

"In high-performance mode, as soon as the principal server sends a log
record to the mirror server, the principal server sends a confirmation
to the client, without waiting for an acknowledgement from the mirror
server. This means that transactions commit without waiting for the
mirror server to write the log to disk. Such asynchronous operation
permits the principal server to run with minimum transaction latency,
at the risk of some potential data loss."

The quotes are straight from BOL.

Roy Harvey
Beacon Falls, CT

On Sat, 29 Sep 2007 13:38:48 -0400, "al**@vp.pl" <al**@vp.plwrote:

>Hi everybody,

Could you please advise me what would be the best method to synchronize
MSSQL data in the following scenario:

SiteA (live site) ----- T1 link ----- SiteB (redundant site)

Each site consists of web server and MSSQL 2005 server. SiteA is the
site that my customers normally use when accessing my store. SiteB is
the backup site to which customers will be directed, should something
happen to Internet connectivity of siteA. When the failover occurs,
client request are pointing to my redundant web servers within a minute
(this happens automatically, as I am using the hosted DNS service). I
was wondering, what is the best method of keeping the database at siteB
up to date with all the transactions that took place at siteA? I do not
want my customers to access the redundant database if it does not
contain all the transactions. What would you advise?

Thanks,
AL

[aleu]

Roy Harvey (SQL Server MVP) wrote:

The best place to ask this is microsoft.public.sqlserver.replication.

I am no replication expert, but your requirement sounds like it needs
Database Mirroring. I am guessing that a T1 would be a bit slow for
synchronous mode, also known as high-safety mode. "Under high-safety
mode, when a session begins, the mirror server synchronizes the mirror
database with the principal database as quickly as possible. Once the
databases are synchronized a committed transaction is committed on
both partners, at the cost of increased transaction latency."

That would leave asynchronous operation mode, also known as
high-performance mode. "The mirror server attempts to keep up with
the log records sent by the principal server. The mirror database
might lag somewhat behind the principal database, though, typically,
the gap between the databases is small. However, the gap can become
substantial if the principal server is under a heavy work load or the
system of the mirror server is over loaded."

"In high-performance mode, as soon as the principal server sends a log
record to the mirror server, the principal server sends a confirmation
to the client, without waiting for an acknowledgement from the mirror
server. This means that transactions commit without waiting for the
mirror server to write the log to disk. Such asynchronous operation
permits the principal server to run with minimum transaction latency,
at the risk of some potential data loss."

The quotes are straight from BOL.

Roy,

Thanks for the provided information. Is there any information on how
much bandwidth is required to perform the synchronous mode successfully
(I was considering purchasing second T1 link for redundancy anyway)?
Would this be sufficient? What happens in both synchronous and
asynchronous cases, when you loose the connectivity between the two
databases lets say for 10 minutes?

Could you please provide me with some links on MSSQL 2005 mirroring
options/requirements/best practices?

Thanks,
AL

[Roy Harvey (SQL Server MVP)]

On Sat, 29 Sep 2007 22:46:44 -0400, "al**@vp.pl" <al**@vp.plwrote:

>Thanks for the provided information. Is there any information on how
much bandwidth is required to perform the synchronous mode successfully
(I was considering purchasing second T1 link for redundancy anyway)?
Would this be sufficient? What happens in both synchronous and
asynchronous cases, when you loose the connectivity between the two
databases lets say for 10 minutes?

Could you please provide me with some links on MSSQL 2005 mirroring
options/requirements/best practices?

Thanks,
AL

If you loose connectivity for ten minutes it would have two impacts.
One, once the connection is re-established there is an additional
delay while the backlog is cleared. Two, you could not clear the
transactions from the log on the source system until after the
connection is re-established and the backlog cleared.

I don't have the hands-on experience with mirroring to have a good
feel for bandwidth requirements, but the major variable is the level
and nature of database activity that only you know. Reading a bit
more from some googled items I am more and more thinking you would be
restricted to asynchronous. If you take a look at your transaction
logs they should give you some idea how much data has to be pushed
over the link.

I suggest googling sql server 2005 mirroring and reading up. And
hoping someone with hands-on experience joins the discussion!

Roy Harvey
Beacon Falls, CT

[aleu]

Roy Harvey (SQL Server MVP) wrote:

If you loose connectivity for ten minutes it would have two impacts.
One, once the connection is re-established there is an additional
delay while the backlog is cleared. Two, you could not clear the
transactions from the log on the source system until after the
connection is re-established and the backlog cleared.

I don't have the hands-on experience with mirroring to have a good
feel for bandwidth requirements, but the major variable is the level
and nature of database activity that only you know. Reading a bit
more from some googled items I am more and more thinking you would be
restricted to asynchronous. If you take a look at your transaction
logs they should give you some idea how much data has to be pushed
over the link.

I suggest googling sql server 2005 mirroring and reading up. And
hoping someone with hands-on experience joins the discussion!

Thanks Roy. It is time to do some serious reading.

Regards,
AL