Can you in anyway help me in writing the Sync program or that complex SSIS package?
I've just finished doing one for a client. It took me an entire week do do for about 1/4 of his database. I meant it when I said not simple. Plus it will have a significant
impact on your production server.
Basically, for SSIS you'll need a data flow per table in the database. Each data flow will have a data source which reads the entire table off production, a lookup that checks test to see if it's a new row or an existing row, then a data destination (which is the table on test) for new rows, a second data destination (a table in a staging database) for updated rows. Then you need a SQL task for each table in the database that will update from that table in the staging DB to the table in the test DB.
For a standalone app, much the same thing. For each table in production you'd need to insert new rows into test, update changed rows and delete rows that are no longer in prod.
Also, if replication is the real solution, can it be setup from the test server rather than the production server? This way we will keep the production server untouched.
No. The publication has to be set up on the publisher.
Why can you not change production? Is there anyone you can speak to to try and get permission?
Yes, I need the test server updated with at-least one hour old updates.
Then your options are basically restore full backup plus latest diff ever hour, or spend a couple weeks writing a program to synchronise dev with prod. I would recommend the former as it's far less impact and far less work, but it does make the test server hard to use as all users will have to disconnect before the restore happens
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass