Hi guys

Just a quick couple of questions regarding the best practice(s) for
maintaining a database on two servers:

The situation: I have a database on my server which I am constantly working
on (modifying structure and content) and I have also placed a copy of the
same database on my clients server for them to test certain aspects and
begin the long process of adding all the correct data. Once in a while I
bring their copy of the database onto my server and create one good
up-to-date copy - encompassing my new tables and their old tables that
they've entered data into (by deleting the relevant relationships and tables
and importing the replacements using DTS) which I then use as my master and
put back on their server to be their master too. It is a major plain to
delete and re-create the indexes etc each time, so I am sure there must be a
replication model more suited to this, but I don't have much experience with
it, so to my questions:

Should I be using replication?

If so -
Which type of replication should I use?
Any cons?
Is it easy to break the link between the two databases after rollout without
messing things up?

If not -
Am I going about this is the best way possible?

Any input grateful received.

Regards
James
tgl

There are lots of inexpensive 3rd party tools that will compare two db's
(both data and structures) and generate scripts to keep them in sync. Have
a look at these:

http://www.aspfaq.com/show.asp?id=2209

--
Andrew J. Kelly SQL MVP


"James Leech" <james (AT) graphics-line (DOT) co.uk> wrote

Replication is not a suitable solution for what you are doing.

You have a development database and a production database and want to sync
all/parts in various directions depending upon your immediate needs.

I use the Red gate SQL Compare to compare/sync schemas and the Red Gate SQL
Data Compare to compare/sync data.

It isn't too expensive and has been worth it's wait in gold for me.


Jim.



"James Leech" wrote:

Replication between different environments (Product and anything else) is a
mariiage made in hell IMHO. Replication couples data sources so much that a
goof in one is as bad for the other. I would go with a product like the red
Gate one mentioned also.

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Jim Breffni" <JimBreffni (AT) discussions (DOT) microsoft.com> wrote