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*********@yahoo.com> wrote in message
news:d5**************@news.uswest.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*********@yahoo.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****@sommarskog.se

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

[Brian Cryer]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

debian mojo (de*********@yahoo.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****@sommarskog.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*********@yahoo.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****@sommarskog.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*********@yahoo.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 uniqueidentifier 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****@sommarskog.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 ***

[debian mojo]

what are the other things i should take care of on the app side, before
adding the pri keys?

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

[Brian Cryer]

"debian mojo" <de*********@yahoo.com> wrote in message
news:ae*************@news.uswest.net...

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 ***

Don't make changes to a live production database unless you have tested it
first and are comfortable about the changes you are going to make. You NEED
primary keys, but don't make the assumption that adding a new primary key
won't break one of your applications. If all you are doing is redefining an
existing unique index as a primary key then that shouldn't hurt anything,
but if you are adding a new field as a primary (default NEWID as per
Erland's suggestion [a very good suggestion by the way]) then that does have
the potential to break something - I think its only likely to break badly
written code, but the potential is there, so test it first.

Take a copy, add your primary keys to that and then run through the whole
range of applications and ensure that everything works as you expect. Only
then, once you are entirely satisfied, introduce the changes to the live
database and even then be sure that you can roll your changes back if it all
goes horribly wrong.

Brian.

www.cryer.co.uk/brian

[Erland Sommarskog]

debian mojo (de*********@yahoo.com) writes:

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.
Sure. Apps that do SELECT * on the tables and then spit out all columns
somewhere, or assume that it has a certain width will be confused by an
extra column if you add one. If you add the new column anywhere but last,
apps that refers to columns by column number will croak. All this is
bad practice, but since this site already has proven a fondness for
bad practice...

As Brian says, you need to test any changes in a safe environment. Which
includes finding out how long time it takes to add the indexes and the new
columns, so you can determine the downtime.

I should have added that beside looking for unique indexes, also look
for existing IDENTITY columns and existing guid columns, as they can
be used for the task.
So what do you suggest? what are the other things i should take care
of before adding the pri keys?

Read Brian's article again. There was a lot of good advice there!

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

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