SQL Server Performance Forum – Threads Archive

Databases Synched

I have three databases DEV, TST and STG. I have the same schema in all of these. I have a nightly process to populate DEV with data. I then need this same data in TST and STG on a daily basis. I was going to create a DTS package to drop all foreign keys, pump data in, put foreign keys back. But if there is a data issue then a foreign key may fail, thus failing the DTS package. Whats the best solution for me?

---

If you don’t want to go into the complexity of replication, then DTS is the best option. In your DTS package, if you make sure that you are pumping the data into primary key tables before pumping into foreign key tables, you may not get any errors.

---

if I have the same schema in all 3, is snapshot replication easy to setup? all 3 db’s are on the same server currently. Is replication a good option when there are table schema changes hapenning?

---

If the frequent schema changes are happening on the database then REPLICATION is not an option to use, it may create few issues while sync. between the databases. In such case using legendary BACKUP/RESTORE statements job would help to keep in the sync. those databases. If the schema is unchanged between the database then you can opt any replication which may be suitable to your environment, in your question now yes you can use SNAPSHOT. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

]]>