I am trying to duplicate our production environment in QA. It has a
number of fairly large published and subscribed databases on 3 different
servers. I am planning to put all of them on one QA server.
Would like to be able to do this with nightly refresh of all data from
production. Production has to stay up 24*7. The refresh of the
subscribers with bcp or dts type initialization would take too long as
databases are of descent size (about 100 GB). The Litespeed backups and
restores take a lot less time - about 2-3 hours. My challenge is to
backup and restore published and subscribed databases in a consistent
manner (to make sure the resulting dbs are in sync).
Does anyone have good ides?
Thanks in advance,

This is hard. Make the QA publisher a subscriber to production. Then have
subscribers hanging off it.

The hard part is handing all the identity columns, constraints and triggers.
You will have to make everything NFR.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

I'd look at transactional replication, database mirroring (SQL 2005) and log
shipping. I have an article which compares replication and log-shipping
which'll help a bit: http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Thanks for the response, Hilary and Paul.
Your suggestions would prevent many various actions that our developers
might like to perform in QA environment, as it is not supposed to be a
read-only system. They need to be able to change the data and
experiment while debugging or testing. So I don't think any kind of
replication or log shipping is possible - it will likely break.
Do you see anything wrong with this approach:

1. Backup ProdPublishedDb1 on sql1
2. Stop distribution agent for it
3. Backup ProdSubscribedDb2 on sql2
4. Restart the agent
5. Drop Db1/Db2 publication-subscription on QA
6. Make sure there are no connections to Db1 and Db2 on Qa
7. Restore both Dbs from backups above
8. Recreate Db1/Db2 publication-subscription on QA with no sync
9. Validate pub-sub

This will likely work during very low activity hours, even though there
are some timing holes, right?

Thanks again,
Koni.

[quoted text, click to view]