Dear All,

We have a big concern in our Database system. We have 2000 transactions
daily in our database. We need to replicate some how the database for
our fail over setup. I tried transactional replication at midnight but
our all systems locked and we had a lot of complaints from the
customers and It was taking a lot of time to snapshot part and I had to
abort it because of these reasons.
I need an advice how I can create a replication or is there any other
way to replicate or is there any way to do this process without
bothering the system? Please give me some advice and help..

Thanks
asa.

"laststubborn" <arafatsalih (AT) gmail (DOT) com> wrote

Why do the transactional replication only at midnight? Better off doing it
continually throughout the day.

And btw, 2000 transactions, unless they're huge, is trivial. I have systems
doing that per minute.

The other option is to look into log shipping.

There are scripts out there that let you do it even w/o Enterprise edition,
though it is then admittedly more manual.

Greg D. Moore (Strider) wrote:
The thing is snapshot taking a lot of time to do it. Even at night we
had a problem with customers.

As you said it is manual we can not afford manual solutions.

Thanks

"laststubborn" <arafatsalih (AT) gmail (DOT) com> wrote

I'm not following, or we're not communicating effectively. A transactional
backup should not be taking that long or have that much impact. How large
are these transactions? Like I say, I have a system doing that many a
minute (though that one we don't backup). The one we do transactional
backups on every 20 minutes probably does 2000/hour or more at peak time.
(plus a very high number of pure select only queries.)


Well, what do you consider too manual. In the case of many of these scripts
the failover itself may or may not be automatic, but can generally be
scripted. The log-shipping itself is scripted.

In our case, we do log backups from our main server every 20 minutes and
restore them to our backup server every 20 minutes, but with either a 4 hour
or 6 hour delay (I forget which). To bring the backup system "up to date" I
can do in about 15 minutes.. either manually or via running a quick script.

The bigger delay (whcih we'd have using Enterprise Server also) is updating
our front end DSNs. And we have that scripted also.

All in all, a failover would probably take us 15 minutes or less to recover
from.

yep I think we had a communication problem. I was asking about the
replication not for a backup (I dont know whether you mean replication
when you use 'transactional backup') Back Up is not a problem as you
said we dont have problem on that however I will create failover
cluster but I will replicate the database first. That is my problem
transactional replication.. That is why I asked all these questions.

Thank you

"laststubborn" <arafatsalih (AT) gmail (DOT) com> wrote

Again, I'm still not sure what you mean here.

In my original post I asked why you weren't simply doing the transactional
replication throughout the day.

Then you brought up snap-shotting. This isn't a term usually used with
transactional replication.

A transactional replication setup really shouldn't lock your system, so I'm
missing something here.

Greg D. Moore (Strider) (mooregr_deleteth1s (AT) greenms (DOT) com) writes:
Not that replication is my best game, but doesn't a transactional
replication scheme usually start with the replication of a snapshot?



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Yes, but it's a one time game. And there are ways around that (not
necessarily clean ones unfortunately).

Thats the thing, I could not pass one time snapshot part. During the
initial snapshot I had problems, Our application was locked and It was
taking so long we had to cancel it. You said that there are ways around
that how ? If you give me some help on that I really appreciate it.

Also any of you did a failover clustering? I will do this first time
and I have some questions.

Thanks

"laststubborn" <arafatsalih (AT) gmail (DOT) com> wrote

Ah, now I better understand what you're talking about.

I'm a bit surpised with only 2000 transactions a day you can't fnid a quiet
time (perhaps on a weekend?) to make the snapshot.

Anyway, there's KB articles on MS on various ways of doing this (and I
believe SQL 2005 has options also that help.)

Anyway, if you CAN pause work for a shrot period of time, try something like
the following:

Do a full backup of DB on "Publisher".
Do a full restore WITH NORECOVERY on "subscriber"
Since this will take some time, you'll need to do the following (and up
until now you users could still use the system.)


Stop all access to the system
Do a transaction log backup from the "publisher"
Restore to the "subscriber", this time with RECOVERY

Setup replication, but say, "subscriber has data and schema"

Permit access to the system.

This is not guaranteed to solve your problem, but I've done it with success
before.

There's several catches to watch out for:

1) if you know your initial transaction log backup will take a long time, do
it w/o kicking folks out of the publisher and restore with NORECOVERY and
then do the transaction log backup a 2nd time (or 3rd, etc.) Trick is to
get that last transaction log backup/restore cycle as absolutely as short as
possible.

Setup as much of replication in advance as possible, i.e. setup the
publisher/distributer side of things before hand.

Script out as much as you can.

Finally.. be careful of IDENTITY columns. This is where things can break.

Note to, if you decide you need to make schema changes on the subscriber, do
this after replication is setup.

The above is not guaranteed to solve your problem but I've used it in
similar cases with success.