replication problems with structure/trigger/identity

Hello alltogether,
i'm working on a database-plattform which should work with
fail-over-technics.
Therefor 2 identic machines with internal RAID are available, which work
in single-host-netloadbalance on win2003-server.
Because of the internal RAID and caused by costs, a solution without
external SCSI-RAID and win2003-clustering-mechanism is looked for.
First i tried to use merge-replication but the rowguid-column, which is
added, makes the application unable to use the database (sql-statements
without column-specification). The application is bought and not easy
adaptable.
Second i tried transaction-replication, which seamed to work, but no
key- and identity-attributes are replicated. When the first machine now
stops and second gets master, there are problems caused by the missing
identities (auto-increment). I got errors caused by triggers too.
Manual copy of the structure with key- and identity-attributes and
triggers causes new problems when the replication is active (i.e. a
replication insert datas in a table with a trigger, the trigger on the
master takes effect, the replication tries to copy the effected datas,
but the trigger on the slave already work with the datas).
Actual way is a differential copy periodical from master to slave, but
is not nice and just a periodical actual slave.
Any ideas for more solutions?
PS: Sorry for X-Post, but i'm not sure, if there are "active" reader of
"m.p.s.r"
Thanks & Greets
Marko Damaschke
trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
student of applied computer science at Chemnitz University of Technology
Hello,
i got an answer from Paul Ibison via a website-based newsforum, but
don't want to get a profile there - so the answer here.
Thanks for the idea via Log Shipping but i see there a problem with the
state of standby-database. The database has to be in "NORECOVERY-Mode",
when i understood it correct, while the whole Log-Shipping-process.
But how to "trigger" it, when suddenly the primary machine crashes?
Somehow the recover-mode has to be started.
Write-access is necessary and without manual actions the db is read-only.
We need automated failover without manual administrator actions.
Thanks & Greets
Marko Damaschke
Trainee at T-Systems-MMS GmbH, Dresden, Saxony, Germany
Student of applied computer science at Chemnitz University of Technology
|||Marko,
the only automatic failover is from clustering. Log-
shipping, replication each have a manual failover. SQL
Server 2005 has database mirroring, but this is a few
months away from release yet. There are 3rd party tools -
DataMirror, but I don't know offhand if these offer
automatic failover.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)