architecture question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Koni - 28 Aug 2006 23:18 GMT

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,
Koni.

Reply to this Message

Hilary Cotter - 29 Aug 2006 02:08 GMT

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.

Signature

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

>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.
[quoted text clipped - 9 lines]
> Thanks in advance,
> Koni.

Reply to this Message

Koni - 29 Aug 2006 16:18 GMT

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.

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

Reply to this Message

Paul Ibison - 29 Aug 2006 09:04 GMT

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

Reply to this Message

Koni - 29 Aug 2006 16:19 GMT

> 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

Reply to this Message

Paul Ibison - 29 Aug 2006 16:40 GMT

Koni,
if you have a production system which is in use and require a copy for the
Devs to work on but whose changes will be dropped, then I'd ship the
database from sql1 to sql2 - no need to back up sql2 as this'll be
overwritten. You could use database shipping (custom solution) for this or
snapshot replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Reply to this Message

Koni - 29 Aug 2006 17:07 GMT

Paul,
Sql1 and Sql2 are both production servers that have published and
subscribed dbs and qa is the one where that replication needs to be
duplicated.
I am not sure what you mean by database shipping - custom solution...
Does this assume over database devices?
Snapshot replication of just the published db sounds like a good idea.
But will it get rid of all the changes made in qa?

> Koni,
> if you have a production system which is in use and require a copy for the
[quoted text clipped - 4 lines]
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com

Reply to this Message

Paul Ibison - 29 Aug 2006 17:24 GMT

Sorry - diodn't realize which each server was. Yes - you'll lose all the
changes in QA this way. If they need to be persisted back to the publishers,
then immediate updating/queued updating/merge are your options, otherwise
they'll be lost. The database shipping solution I was suggesting is an
alternative to snapshot where you just take the backup of the prod databases
and restore over the subscriber database, but this'll have to be done as
your own custom jobs.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Reply to this Message