Disaster recovery - Use Replication or Log shipping

Hi,
I have applications using Sql Server 2000 database at a primary location. As
a disaster recovery i.e if Sql server at the primary location fails , i have
setup another setup with Sqlserver 2000 and applications at secondary
location.
I want to know what is best option for disaster recovery ?, is it
transaction replication or Log shipping.
Suppose i use log shipping , when the primary server goes down, manually i
switch the applications to secondary server . Now the seconday server will
have some trasnactions . When the primary server comes back how do i
propogate the transaction chnages of secondary server to primary server?
Thanks.
gdc
That depends on your particular requirements - here are some of the pros and
cons of each
Pros log shipping
all objects and permission are copied
simple to set up
there is a log shipping wizard that ships with SQL 2000 EE, and SQL 7
Resource kit has some scripts
conceptually is very simple to understand
cons
standby server database is in read only mode
standby server database must go off line as you apply the tlogs
latency can be as low as 1 minutes, but practical limit is 5 minutes
certain db operations break the log shipping chain
requires more space
can't partition - its all objects and all data or nothing
not feasible for large databases
Pros trepl
can partition to a single object and a subset of rows
short latency as low as 4 s or less.
only new transactions go across the wire
cons
each table needs a pk
can be complex to troubleshoot and restart, but all in all is almost as
reliable as log shipping
"rajani" <rajaninandula@.hotmail.com> wrote in message
news:eiHeEbbFEHA.3064@.tk2msftngp13.phx.gbl...
> Hi,
> I have applications using Sql Server 2000 database at a primary location.
As
> a disaster recovery i.e if Sql server at the primary location fails , i
have
> setup another setup with Sqlserver 2000 and applications at secondary
> location.
> I want to know what is best option for disaster recovery ?, is it
> transaction replication or Log shipping.
> Suppose i use log shipping , when the primary server goes down, manually i
> switch the applications to secondary server . Now the seconday server will
> have some trasnactions . When the primary server comes back how do i
> propogate the transaction chnages of secondary server to primary server?
> Thanks.
> gdc
>
|||I missed part of your question.
The only ways to reconcile changes which occur when the primary goes down is
to use merge replication, bi-directional transactional, or manually to
stitch together a consistent database.
It is possible to resync both databases manually, but you have to kick
everyone off both systems and then do binary checksums on all tables to
figure out which rows are out of sync.
I've had to do it and it is a chore.
Merge and bi-di trans replication makes life a lot easier.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u$FubBdFEHA.3372@.TK2MSFTNGP10.phx.gbl...
> That depends on your particular requirements - here are some of the pros
and
> cons of each
> Pros log shipping
> all objects and permission are copied
> simple to set up
> there is a log shipping wizard that ships with SQL 2000 EE, and SQL 7
> Resource kit has some scripts
> conceptually is very simple to understand
> cons
> standby server database is in read only mode
> standby server database must go off line as you apply the tlogs
> latency can be as low as 1 minutes, but practical limit is 5 minutes
> certain db operations break the log shipping chain
> requires more space
> can't partition - its all objects and all data or nothing
> not feasible for large databases
> Pros trepl
> can partition to a single object and a subset of rows
> short latency as low as 4 s or less.
> only new transactions go across the wire
> cons
> each table needs a pk
> can be complex to troubleshoot and restart, but all in all is almost as
> reliable as log shipping
>
> "rajani" <rajaninandula@.hotmail.com> wrote in message
> news:eiHeEbbFEHA.3064@.tk2msftngp13.phx.gbl...
location.
> As
> have
i
will
server?
>
|||Although Hilary implies this, it's worth noting that there are a couple of extra points in the list of cons for transactional replication for disaster recovery:
permissions, user-defined datatypes and users not transferred
defaults removed - this may cause code to break or return unexpected results after DR.
Regards,
Paul Ibison
|||Still not clear. Suppose i transaction replication, and the primary server
fails. I disable the replication jobs on the secondary server and point my
applications to secondary server and now my secondary server is primary
server.
The applications creates some transactions on the new primary server. When
the old primary server comes back , suppose i want to revert to old primary
server .
How do i reflect the changes done on the new primary server to old primary
server ?
Suppose by some means i reflect the changes, is there any problem in
enabling the replication which was setup earlier?
thanks
gdc
"rajani" <rajaninandula@.hotmail.com> wrote in message
news:eiHeEbbFEHA.3064@.tk2msftngp13.phx.gbl...
> Hi,
> I have applications using Sql Server 2000 database at a primary location.
As
> a disaster recovery i.e if Sql server at the primary location fails , i
have
> setup another setup with Sqlserver 2000 and applications at secondary
> location.
> I want to know what is best option for disaster recovery ?, is it
> transaction replication or Log shipping.
> Suppose i use log shipping , when the primary server goes down, manually i
> switch the applications to secondary server . Now the seconday server will
> have some trasnactions . When the primary server comes back how do i
> propogate the transaction chnages of secondary server to primary server?
> Thanks.
> gdc
>

Disabling primary key

Hi
i have a table with primary key defined on col1 and col2. now i want to
have col3 also included in primary key. when i alter the table it gives
me error for duplicate rows. there is an option for 'with nocheck' but
it only works with check or foreign key constraint. is there any option
in sql server like in oracle 'no validate' which doesnt validate the
existing data and force the data validation from new records.
thanx
Farid

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:40b49d58$0$209$75868355@.news.frii.net...
>
> Hi
> i have a table with primary key defined on col1 and col2. now i want to
> have col3 also included in primary key. when i alter the table it gives
> me error for duplicate rows. there is an option for 'with nocheck' but
> it only works with check or foreign key constraint. is there any option
> in sql server like in oracle 'no validate' which doesnt validate the
> existing data and force the data validation from new records.
> thanx
> Farid
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

No - CHECK/NOCHECK is for foreign keys and check constraints only. I'm not
entirely sure I understand your post - are you saying that you want to allow
duplicate values in a primary key? If so, then it's not possible, and
shouldn't be. Perhaps if you can post some more details (the table DDL and
sample data), someone may be able to suggest an alternative approach.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40b4d73d_1@.news.bluewin.ch...
> "Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
> news:40b49d58$0$209$75868355@.news.frii.net...
> > Hi
> > i have a table with primary key defined on col1 and col2. now i want to
> > have col3 also included in primary key. when i alter the table it gives
> > me error for duplicate rows. there is an option for 'with nocheck' but
> > it only works with check or foreign key constraint. is there any option
> > in sql server like in oracle 'no validate' which doesnt validate the
> > existing data and force the data validation from new records.
> > thanx
> > Farid
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> No - CHECK/NOCHECK is for foreign keys and check constraints only. I'm not
> entirely sure I understand your post - are you saying that you want to
allow
> duplicate values in a primary key? If so, then it's not possible, and
> shouldn't be. Perhaps if you can post some more details (the table DDL and
> sample data), someone may be able to suggest an alternative approach.

I think he wants: SET IDENTITY_INSERT.

> Simon|||>> I have a table with primary key defined on col1 and col2. Now I
want to
have col3 also included in primary key. When I alter the table it
gives me error for duplicate rows. <<

That does not make sense to me. Given this

CREATE TABLE Foobar
(col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
PRIMARY KEY (col1, col2),
..);

Then a superkey is still unique:

CREATE TABLE Foobar
(col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
PRIMARY KEY (col1, col2, col3),
..);|||i think u people didn't understand the problem.
i have created table
create table test(col1 int, col2 int, col3 int,col4...)
Primary key(col1,col2)
as there is primary key on col1 and col2 no duolicate data can exist in
them now the scenario changed i have to change the primary key on the
table. now when i alter the table
alter table test primary key (col1, col3, col4) it gives me error
duplicate rows exist.
but in my scenario i want the existing duplication to remain in the
table. and the primary key enforcement starts from new data.
in oracle there is an option of 'no validate' which doesnt check the
existing data in the table but enforce the uniqueness of data from new
records. i want to know is there any option available in sql server
which doesnt check the existing data but enforces the uniqueness of
records from new records.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Ghulam Farid <gfaryd@.yahoo.com> wrote in message news:<40b57e55$0$207$75868355@.news.frii.net>...
> i think u people didn't understand the problem.
> i have created table
> create table test(col1 int, col2 int, col3 int,col4...)
> Primary key(col1,col2)
> as there is primary key on col1 and col2 no duolicate data can exist in
> them now the scenario changed i have to change the primary key on the
> table. now when i alter the table
> alter table test primary key (col1, col3, col4) it gives me error
> duplicate rows exist.
> but in my scenario i want the existing duplication to remain in the
> table. and the primary key enforcement starts from new data.
> in oracle there is an option of 'no validate' which doesnt check the
> existing data in the table but enforce the uniqueness of data from new
> records. i want to know is there any option available in sql server
> which doesnt check the existing data but enforces the uniqueness of
> records from new records.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

It sounds like you want to place a primary key on columns which
contain duplicates, but you want to ignore those duplicates and only
enforce the primary key for new values? If so, then it's not possible
- all values in a primary key must always be unique, otherwise it
couldn't be a primary key.

Simon

create table dbo.Test (
col1 int not null,
col2 int not null,
col3 int not null,
col4 int not null,
constraint PK_Test primary key (col1, col2)
)
go

insert into dbo.Test
select 1,1,1,1
union all
select 1,2,1,1
union all
select 3,1,1,1
go

alter table dbo.Test
drop constraint PK_Test

-- This will always fail because of duplicate data
alter table dbo.Test
add constraint PK_Test primary key (col1, col3, col4)
go

drop table dbo.Test
go

Disabling constraints

Hi,
I need to disable all the foreign key and primary key constraints in a table
and reenable them at the end of my commands. I can use ALTER TABLE to do
this but I was wondering if it is a good practise. It is not maintenance
code but production code. Everything is done in a transaction but can I be
sure there is no incidence with concurent accesses ? What about the rights
for the db user ?
Thanks.
FredFrdric Mayot wrote:
> Hi,
> I need to disable all the foreign key and primary key constraints in
> a table and reenable them at the end of my commands. I can use ALTER
> TABLE to do this but I was wondering if it is a good practise. It is
> not maintenance code but production code. Everything is done in a
> transaction but can I be sure there is no incidence with concurent
> accesses ? What about the rights for the db user ?
> Thanks.
> Fred
Why would you need to disable constraints in production code?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||The reason is quite simple.
Assume we have the tables
A(pkA <PK> )
B(pkfkA <PK,FK>, pkfkC <PK,FK> )
C(pkC <PK> )
with the data
A = {1}
C = {11, 12}
B = {(1, 11), (1, 12)}
Now, I want to update the two rows in B in the following manner :
(1, 11) -> (1, 12) and (1, 12) -> (1, 11).
This operation is supposed to be atomic (that's to say in a transaction)
"David Gugick" <david.gugick-nospam@.quest.com> a crit dans le message de
news: %23$J$Y0pbFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Frdric Mayot wrote:
> Why would you need to disable constraints in production code?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Have you tried this?
UPDATE B
SET pkfkC = CASE pkfkC
WHEN 11 THEN 12
WHEN 12 THEN 11
END
WHERE pkfkA = 1 AND pkfkC IN (11, 12)
This update is atomic and as such doesn't cause a constraint violation error
.
"Frédéric Mayot" wrote:

> The reason is quite simple.
> Assume we have the tables
> A(pkA <PK> )
> B(pkfkA <PK,FK>, pkfkC <PK,FK> )
> C(pkC <PK> )
> with the data
> A = {1}
> C = {11, 12}
> B = {(1, 11), (1, 12)}
> Now, I want to update the two rows in B in the following manner :
> (1, 11) -> (1, 12) and (1, 12) -> (1, 11).
> This operation is supposed to be atomic (that's to say in a transaction)
>
> "David Gugick" <david.gugick-nospam@.quest.com> a écrit dans le message de
> news: %23$J$Y0pbFHA.3040@.TK2MSFTNGP14.phx.gbl...
>
>