Replicating a database in sql2000

[flyjetta]

We are trying to replicate databases on windows 2000. We were thinking of just doing dumps and restores on the other server daily, but I was wondering if there are better alternatives. This is a special project I was given, and I'm not an sql admin by any means, but I would like to offer the best possible solution. I saw that 2005 offers replication, but I can't find any info on 2000. Any help on this would be really appreciated!

 

[jbenson001]

Here is a 2 part article to get you started:

http://www.devarticles.com/c/a/SQL-Server/Replication-SQL-Server-2000--Part-1/

 

[TWillard]

We use replication because we want to have a copy of a production database avaialable to provide real-time accessibility for reporting needs, while at the same time preventing unnecessary reporting queries and connections against the production. However the setup and maintenance is a challenging especially in SQL 2000 vs SQL 2005. In 2000, using transactionaly replication changes to tables are not carried over. Therefore, if you add or drop a column from a table or need to add a view, etc. these changes are not replicated. I think SQL 2005 provides better options for handling these issues. Anytime you have new tables created, you will need to manually configure your replication to work with the new table.

Another alternaive is log shipping. It is part of the SQL 2000 database maintenance plans. It can also be challenging to setup, but it does provide another alternative. In sql 2005, log shipping is discourgaed, but in SQL 2000 it works quite well. For reporting needs, it can work well. Just keep in mind that the secondary database is read-only, so no actions can be taken in the database. The transaction logs from the primary database *.trn dumps are copied over and restored to the secondary database.

If your databases are realively small in size and you are compfortable with all the steps envolved, backups and restores can work well.