Hi, all.
I am use SQL 2k. I need to migrate data from production server to another
server for customer to review, and the data can be one day delay. Our
database size is about 35GB. Should I use Replication (whick kind will be
best) or just to Restore the backup file from Production database. We backup
the production database every evenning. If I use replication, is this too
big to go through network (just performance issue)?

Please help and Thanks!

Perayu

Hi, Paul.
Sorry for no describing my problem clear in previouse post.
We have prodcution database is currently used by internal users only and is
running at about 50% of its capacity . We are going to have a Web
application that will allow customers (we have 2 millians customers) to
logon to his/her account and review transaction hisotry or may change
account information. For performance concern, I need to create another
Server to handle Web application. But the transation history is in our
current Prodution database, and it is read only for customer. Our customer
can only update his/her account information in new database, and I will set
up a Transaction Replication to update this account information (this
information is relative small) from new database to our Production database.
My question is how should I update the transaction history from Production
db to new db? We are thinking about taking the backup file and restore it to
new database, or just use another Replication to migrate the big chank of
data to new database?

Thanks for your help.

Perayu


[quoted text, click to view]

Perayu,
assuming you have access to the customer's sql server service over the
network, I would expect transactional replication or log-shipping to be the
most appropriate solution. Please take a look at this article:
http://www.replicationanswers.com/Standby.asp.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Hi, Paul,
This article is really helpful, especially for my current Replication, which
is replicating two tables to one subscriber. Whenever I restart the
replication, it took about 2 hours to syc. I will try to change it to
"NonSyc" sometime later.

My new question is: If I setup a new replication to replicate about 200
tables, and our current Transaction log is about 1.5GB, is it possible that
it is so big that it will cause the network trafic problem? Is it a problem
for setting up different publications to be push to different subscribers.
Our current replication is replicating 2 tables to imaging database, I will
need another replication to replicate about 200 tables to our new database.
Is it a potential nightmare for maintenance because whenever I change the
table structure, it will break the replication?

Thanks again for your help.

Perayu

[quoted text, click to view]

Perayu,
I'd do a nosync initialization then use transactional replication. Please
have a look at :
http://www.replicationanswers.com/NoSyncInitializations.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Thank you.

Perayu

[quoted text, click to view]

Perayu,
the network issues are going to be particular to your setup. You could
monitor bandwidth useage to see if this'll be an issue. The size of your
transaction log is large, but this really depends on how often your log is
backed up and if there are any long-running transactions. You can use
sp_browsereplcommands to get an idea of the amount of transactions in the
queue.
As for the schema changes, sp_repldropcolumn and sp_repladdcolumn can
usually do most changes. For changes to an existing schema you have to
struggle a bit, and this is handled directly on SQL Server 2005, but it is
still possible for us (provided you're not changing the PK or identity
columns).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)