Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SunnyDee
Yak Posting Veteran
79 Posts |
 Posted - 2010-01-24 : 14:34:03
|
| Hello:I am hoping for feedback on the best strategy for handling the flow of data from test and production dbs located on the same server. We have large amounts of data that need to push from production to test on a regular basis. We also have data in admin tables that we need to push from to test to production, but we also need to pull entries in these same tables from production to test.I am thinking that peer to peer replication might be the way to go, but I am not sure it has the flexibility that we need. Any feedback on general best practices in handling testing and production servers is appreciated.Thanks! |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-24 : 15:30:52
|
We use a script that does:DELETE if exists in target, but does not exist in source. (Perform this for all tables, first)UPDATE if exists (on target DB) BUT ONLY FOR ROWS WITH at least one column differentINSERT if not exists (in target)Perform update then insert table-by-table.The WHERE clause for the UPDATE is complex; it tests every column and includes tests for one, but not the other, is NULL. We mechanically generate the script - and regenerate it when we add more columns etc. We also force a Binary COLLATION so that case-sensitive changes are considered as a difference (e.g. you just capitalise once character) - only necessary for case INsensitive databases of course  We use this to preview what data has changed, where necessary, and then to actually make the "upsert".The table upserts are ordered by foreign-key "depth". The deletes are in reverse order (delete children first, then parents; insert/update Parents first then Children).Even so we sometimes hit chicken-and-egg FK situations and for those we drop/recreate the FKs (nothing to stop you doing that for all tables, we don't bother unless the script fails).We also sometimes have problems with indexes. e.g. a record has been added on both TEST and PRODUCTION, with different Identity IDs, but the same value for a UNIQUE index. We generally wipe the table!! when that happens, and then the UPSERT reinserts all the rows in the table.You could just TRUNCATE TABLE or DELETE TABLE and then re-INSERT, but that takes a lot of log space, and some time for large tables, so we prefer to upsert differences once.Worth looking at the MERGE command for this (our scripts were aroudn for SQL2000 long before MERGE command was introduced) |
 |
|
|
SunnyDee
Yak Posting Veteran
79 Posts |
Posted - 2010-01-24 : 21:51:18
|
| Thanks Kristen. This is extremely helpful. |
|
|
|
|
|
|
|
|
Test and Production Servers