Replication Architecture/Design advise

I have been asked to set-up replication in the current two server
enviornment. My understanding is that Log-Shipping requires
the Enterprise version of Sql Server 2000 (Which they do not
have the budget for).
They have 70 Databases that have a total space of aprox 30gb.
They want to replicate the data to the standby server so that
in the case of a failure they can redirect their Web Sites
to the standby Server.
Both servers will be in a co-lo facility so bandwidth between
the servers should be adequate.
Do I do central publisher with the distributor on the standby
server? I am not sure what kind of load this will have. I
understand that people recommend to put it on the publisher
server and only move it if load becomes an issue.
I understand that security (ie, users and permissions) will
not replicate. Other than having to set things up twice is
their other solutions to minimize this?
Sincerely,
Dave
you do not need the Enterprise Edition of SQL Server for log shipping. This
is a mistake in BOL.
Ideally what you want is automatic failover which means hardware or software
data mirroring - however it can be accomplished using log shipping, but you
will have to redirect your clients to the failed over server. You can then
log ship back to the original primary.
If you choose to use replication - I would use bi-directional transactional
replication keeping in mind
1) that every table you are replicating needs a pk
2) system objects and logins are not replicated
3) bi-directional transactional replication is not resilient to schema
changes (i.e. you have to drop your subscriptions and publication when you
want to make schema changes). Other replication types support partial schema
changes using sp_repladdcolumn and sp_repldropcolumn.
For log shipping you will find that
1) its not really scalable - I think you will find the logistical burden log
shipping 70 databases to be a bit overwhelming
2) your standby servers' databases will be read only
For replication you would be using a single publisher and subscriber. It is
a central publisher with one subscriber.
"David Gresham" <gresham@.panix.com> wrote in message
news:d50tct$5je$1@.reader1.panix.com...
>I have been asked to set-up replication in the current two server
> enviornment. My understanding is that Log-Shipping requires
> the Enterprise version of Sql Server 2000 (Which they do not
> have the budget for).
>
> They have 70 Databases that have a total space of aprox 30gb.
> They want to replicate the data to the standby server so that
> in the case of a failure they can redirect their Web Sites
> to the standby Server.
>
> Both servers will be in a co-lo facility so bandwidth between
> the servers should be adequate.
>
> Do I do central publisher with the distributor on the standby
> server? I am not sure what kind of load this will have. I
> understand that people recommend to put it on the publisher
> server and only move it if load becomes an issue.
>
> I understand that security (ie, users and permissions) will
> not replicate. Other than having to set things up twice is
> their other solutions to minimize this?
>
> Sincerely,
>
>
> Dave
>
|||In article <umaTbQdTFHA.2172@.tk2msftngp13.phx.gbl>,
Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>you do not need the Enterprise Edition of SQL Server for log shipping. This
>is a mistake in BOL.
>Ideally what you want is automatic failover which means hardware or software
>data mirroring - however it can be accomplished using log shipping, but you
>will have to redirect your clients to the failed over server. You can then
>log ship back to the original primary.
>If you choose to use replication - I would use bi-directional transactional
>replication keeping in mind
>1) that every table you are replicating needs a pk
>2) system objects and logins are not replicated
>3) bi-directional transactional replication is not resilient to schema
>changes (i.e. you have to drop your subscriptions and publication when you
>want to make schema changes). Other replication types support partial schema
>changes using sp_repladdcolumn and sp_repldropcolumn.
>
Can you expand on what replication model this is? Is this Merge?
Does this mean that the standby server can update the primary?
My situation for this project is that the primary will update
the standby server and will only be used when the primary fails.
My other question is: Lets say we need to rebuild the primary
server. We re-build it and need to bring the databases back
from the standby server. Do we have to start over from scratch?
Or do we need to some-how rebuild everything? I am going to
be ordering your book today. I have worked with replication
before when all we were doing was creating a reporting database
which was read-only.
The standby concept seemed to follow this except for when we
needed to switch over to it. The client hosts several
web sites.
Thanks
Dave

>For log shipping you will find that
>1) its not really scalable - I think you will find the logistical burden log
>shipping 70 databases to be a bit overwhelming
>2) your standby servers' databases will be read only
>For replication you would be using a single publisher and subscriber. It is
>a central publisher with one subscriber.
>"David Gresham" <gresham@.panix.com> wrote in message
>news:d50tct$5je$1@.reader1.panix.com...
>
|||This is transactional, bi-directional transactional replication to be
specific, not to be confused with transactional replication with immediate
updating, transactional replication with queued updating, transactional
replication with immediate updating with queued failover, or merge
replication. However merge replication might work for you, although it will
add a guid column to each column you are replicating.
It does mean that your standby could update the primary, and it is an
excellent choice when the primary goes offline and all database activity
occurs on the standby server then.
Ideally you would use bi-directional replication in a case where your
publisher goes offline for an extended time period. When it comes back
online you should backup the standby by restore it on the publisher,
recreate the publications and subscriptions on the publisher and then do a
no sync,
Review this link for more info on these options.
http://support.microsoft.com/default...b;en-us;820675
"David Gresham" <gresham@.panix.com> wrote in message
news:d52qja$4j4$1@.reader1.panix.com...
> In article <umaTbQdTFHA.2172@.tk2msftngp13.phx.gbl>,
> Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> Can you expand on what replication model this is? Is this Merge?
> Does this mean that the standby server can update the primary?
> My situation for this project is that the primary will update
> the standby server and will only be used when the primary fails.
> My other question is: Lets say we need to rebuild the primary
> server. We re-build it and need to bring the databases back
> from the standby server. Do we have to start over from scratch?
> Or do we need to some-how rebuild everything? I am going to
> be ordering your book today. I have worked with replication
> before when all we were doing was creating a reporting database
> which was read-only.
> The standby concept seemed to follow this except for when we
> needed to switch over to it. The client hosts several
> web sites.
> Thanks
>
> Dave
>
>
|||In article <eOqh7CmTFHA.544@.TK2MSFTNGP15.phx.gbl>,
Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>This is transactional, bi-directional transactional replication to be
>specific, not to be confused with transactional replication with immediate
>updating, transactional replication with queued updating, transactional
>replication with immediate updating with queued failover, or merge
>replication. However merge replication might work for you, although it will
>add a guid column to each column you are replicating.
>It does mean that your standby could update the primary, and it is an
>excellent choice when the primary goes offline and all database activity
>occurs on the standby server then.
>Ideally you would use bi-directional replication in a case where your
>publisher goes offline for an extended time period. When it comes back
>online you should backup the standby by restore it on the publisher,
>recreate the publications and subscriptions on the publisher and then do a
>no sync,
>Review this link for more info on these options.
>http://support.microsoft.com/default...b;en-us;820675
>
In reading this document I got a 'little' intimidated by the need
for all these custom stored procedures. Since the server will
only be used in such a way that there would never be any
conflicts do I need to worry about this?
I also wanted to clarify where the distributor is in this set-up,
the Subscriber?
Thanks
Dave
sql

replication

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,
ALThe 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, "aleu@.vp.pl" <aleu@.vp.plwrote:

Quote:

Originally Posted by

>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) wrote:

Quote:

Originally Posted by

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|||On Sat, 29 Sep 2007 22:46:44 -0400, "aleu@.vp.pl" <aleu@.vp.plwrote:

Quote:

Originally Posted by

>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|||Roy Harvey (SQL Server MVP) wrote:

Quote:

Originally Posted by

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