Replication Problem!

[debian mojo]

Hello Group,
I'm working on two SQL server databases on two different remote
servers. What client needs is that his database on one remote server
should be mirrored to the other one. The changed should be propogated at
regular intervals at the other server.

For now i'm quite sure that i'll have to use replication to resolve
this issue.

But which type of replication should i use? Transactional, Merge or
Snapshot?

Please note that the client is running his website from the main
server, which is using the source SQL Server database - the one i'll
have to replicate.

Last time when i tried to register the destination server, at my
source server Enterprise Manager, it gave me an error failing to
register the destination server. When i asked the client, all he could
tell me was that both the servers are firewalled and that might have
been the problem.

So just tell me how should i go for it?

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***

[Brian Cryer]

If you go the replicated route then your choice (of merge, snapshot or
transactional) is probably straight forward:

- Will they ever want to make changes on the remote server? If yes then you
must use merge replication.
- Otherwise got for Transactional.

Unless the database is small don't go for snapshot. Snapshot is fine for
just that, taking the odd snapshot, but isn't really suited if you want to
regularly propagate changes.

Regarding your problem connecting to the remove server. It is quite likely
that firewalls could be causing you a problem. I've not tried it, but
presumably the most secure way forward would be to create a vpn connection
between the two servers (firewalls may still be an issue) and then replicate
across the vpn link.

Hope this helps,

Brian.

www.cryer.co.uk/brian

"debian mojo" <de*********@ya hoo.com> wrote in message
news:d5******** ******@news.usw est.net...

Hello Group,
I'm working on two SQL server databases on two different remote
servers. What client needs is that his database on one remote server
should be mirrored to the other one. The changed should be propogated at
regular intervals at the other server.

For now i'm quite sure that i'll have to use replication to resolve
this issue.

But which type of replication should i use? Transactional, Merge or
Snapshot?

Please note that the client is running his website from the main
server, which is using the source SQL Server database - the one i'll
have to replicate.

Last time when i tried to register the destination server, at my
source server Enterprise Manager, it gave me an error failing to
register the destination server. When i asked the client, all he could
tell me was that both the servers are firewalled and that might have
been the problem.

So just tell me how should i go for it?

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***

[debian mojo]

Hi again!

The problem is that all the source tables dont have a pri key... and
i have learnt that Trans Rep doesnt support replicating tables without
Pri key. Besides there's no question of adding Pri Keys to the database
which is already live!

Second thing is that, the database size is in between 1 and 2GBs. That
means even Snapshot Rep is not suitable!

Shall i go for Merge Rep? I heard that there are conflicts in that?

So then what is the way out?

Thanks Brian, for replying!

Regards
Debian
*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

debian mojo (de*********@ya hoo.com) writes:

The problem is that all the source tables dont have a pri key... and
i have learnt that Trans Rep doesnt support replicating tables without
Pri key. Besides there's no question of adding Pri Keys to the database
which is already live!
Well, it want take long before the database is dead. Not having
primary keys is asking for serious problems.
Second thing is that, the database size is in between 1 and 2GBs. That
means even Snapshot Rep is not suitable!

Shall i go for Merge Rep? I heard that there are conflicts in that?

I doubt that merge replication is possible without PKs. If rows can't
be identified, it's getting darn difficult to do replication.

Seems to me you have three options:

1) Add PKs to the database, and do transactional replication.
2) Regularly backup the database and restore on the other end.
3) Log shipping.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Brian Cryer]

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** *************@1 27.0.0.1...

debian mojo (de*********@ya hoo.com) writes:

The problem is that all the source tables dont have a pri key... and
i have learnt that Trans Rep doesnt support replicating tables without
Pri key. Besides there's no question of adding Pri Keys to the database
which is already live!
Well, it want take long before the database is dead. Not having
primary keys is asking for serious problems.

Second thing is that, the database size is in between 1 and 2GBs. That
means even Snapshot Rep is not suitable!

Shall i go for Merge Rep? I heard that there are conflicts in that?

I doubt that merge replication is possible without PKs. If rows can't
be identified, it's getting darn difficult to do replication.

Seems to me you have three options:

1) Add PKs to the database, and do transactional replication.
2) Regularly backup the database and restore on the other end.
3) Log shipping.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

I fully agree with everything Erland has said.

You may not currently have primary keys, but surely there are fields that
you are treating as unique? How do you currently uniquely identify a record?
A primary key can be defined using more than one field.

Treat Erland's comment as a dire warning:
Well, it want take long before the database is dead. Not having
primary keys is asking for serious problems.

Its put very well, and spot on.

Brian.

www.cryer.co.uk/brian

[debian mojo]

Hello Erland and Brian,
Thanks for the reply!

The problem is that there are around 36 user tables in the source
database and only 5 of them have pri keys, the rest are either using
foriegn keys or dont have any kinda keys at all!

Is it good to have pri keys on all tables in the database.

Please do note that the database is for a website and it is estimated
that it will hit 10GB in the first one month!

Is there any chance of replication without compromising the database
integrity or shall i look for any other options such as log shipping?

Please advise!

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

debian mojo (de*********@ya hoo.com) writes:

Please do note that the database is for a website and it is estimated
that it will hit 10GB in the first one month!
Of course with no Pkeys, the chances for duplicates increase, and so
will the database size.
Is there any chance of replication without compromising the database
integrity or shall i look for any other options such as log shipping?

If you don't want to add primary keys (and save the database from a
disaster furtther afield) log shipping or backup/restore is what you
have to look into.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[debian mojo]

Thanks again, Erland,

Since the database has problems with Pri keys, i'm thinking about Log
Shipping as an alternative.

But as it is mentioned in BOL that for LS to work, it's necessary to
configure three servers:
1. Source Server
2. Monitor Server
3. Destination Server.

Now the destination and the source databases can be on the same
server, but it is mandatory to keep the monitor server separate.

The problem with this approach is that i've access to only two servers
- one source and one destination. I wont have the privilege of having
another one just for setting up the monitor server!

What should i do?

Thanks in advance!
Debian



*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

debian mojo (de*********@ya hoo.com) writes:

Since the database has problems with Pri keys, i'm thinking about Log
Shipping as an alternative.

But as it is mentioned in BOL that for LS to work, it's necessary to
configure three servers:
1. Source Server
2. Monitor Server
3. Destination Server.

Now the destination and the source databases can be on the same
server, but it is mandatory to keep the monitor server separate.

The problem with this approach is that i've access to only two servers
- one source and one destination. I wont have the privilege of having
another one just for setting up the monitor server!

Looks like you are starting to get some good arguments: "Either we
fix primary keys to the database, or we go and buy some more hardware,
else we can't run this show".

I will have to admit that the need for third machine was news to me,
but I have never set up log shipping myself.

Actually, add PKeys to those tables does not have to be a killer
work. If there are unique indexes, just drop these and recreate
them as primary keys. If there are not any primary keys, just add
an uniqueidentifie r with the default of NEWID() to the tables, and
make that the primary key (nonclustered!) . That does not really help
to make the data model any better, but at least you can set up
replication over this dying grace.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[debian mojo]

Thanks Erland,
That's true that moving away from the problem is not a solution. But
you see that the database i'm talking about is a 24 x 7 database, and
i'm not pretty sure how apps that are using this db.

If i do pri keys to the tables, is there any possibility that the apps
may suffer is some way.

I do agree that in most of the cases there's no harm.... but you see
the database is a production one and i'm really paranoid about it's
safety!

I hope you understand!

BTW, your advice was an eye-opener for me.

So what do you suggest? what are the other things i should take care
of before adding the pri keys?

Debian
*** Sent via Developersdex http://www.developersdex.com ***