Need to reflect changes made in test to Production

Question

Post reply

Need to reflect changes made in test to Production

SQLQuest29

SSCrazy Eights

Points: 8264
More actions
April 14, 2009 at 10:27 am

#208247

Hi All,
I have a requirement: I have a user who uses a third party application. This application has a form in which the user enters some data and when he clicks "submit", the data gets entered into different SQL server tables in one database.
Now the user does this in the TEST environment. The requirement is that I have to implement a solution to migrate that changes in production.
I am thinking of 2 solutions:
1. do a data import-export and append the data by first clearing out the prod table data. ---> I think this is not a good idea
2. to implement replication ... merge or transactional replication ---> dont know if this is a good idea ?
Any inputs are appreciated !!
Environment Test and Prod: SQL 2K5, sp2, win 2k3 R2
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Replication from TEST to PROD or from PROD to TEST is never a good idea.

* Noel

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 2

noeld (4/14/2009)
Replication from TEST to PROD or from PROD to TEST is never a good idea.

Thanks for your input..

But my requirement is that what ever the change is made to one server (Prod/ test) that change needs to be made in (test/prod).

I am not sure what to implement, so did a search on transactional replication and found it fits... but as you said that it is not a good idea...... can you suggest how to move changes..... or atleast get notified of the changes by mail in either test or prod environment.

Thanks,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

noeld SSC Guru Points: 96590 More actions · Answer 3

Let me explain why I said that.

- Replication can affect the Publisher if the subscriber is down. So Do you really want to affect Production if TEST has a problem ( or vice versa? )

You can create many tricks to synch the changes across but you should keep in mind that whatever you do should be flexible enough.

Maybe SSIS package or use tablediff.exe could get you there. Iam not sure how small or large are the amount of changes you need to perform.

* Noel

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 4

noeld (4/14/2009)
Let me explain why I said that.
- Replication can affect the Publisher if the subscriber is down. So Do you really want to affect Production if TEST has a problem ( or vice versa? )
You can create many tricks to synch the changes across but you should keep in mind that whatever you do should be flexible enough.
Maybe SSIS package or use tablediff.exe could get you there. Iam not sure how small or large are the amount of changes you need to perform.

Hi ,

Thanks for your input. I am thinking of creating a data import/Export package using SSIS, but this has a limitation of wiping out the data of the test tables and inserting the new data everytime I run the package.

I do not have idea about tablediff.exe ... is it a thirdparty tool ? Is it freeware ? If not, rt now due to budget issues, my company cannot invest on it.

Also, the change I have to do is very small, not more than 100 records in a week.

The case is that I have to look for changes if only the user has manually keyed in the records, otherwise we have a third party application that is designed to load data.

So I am thinking of

1. creating an "On insert" trigger on the primary key of the main table, which will notify me through DB mail when a record is inserted.

2. create an SSIS data export task, to clear the test table data and reload the new data from production.

for SSIS package, my prod and test are in different domain, will this work ok or do I have to do some modification in the package ?

Correct me if I am wrong.

Thanks for your help,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

noeld SSC Guru Points: 96590 More actions · Answer 5

\\K

You can definitely do as planned. tablediff.exe is a tool that comes with SQL Server 2005 and you already have it in your harddrive ;-). There are many examples of it, just google it.

If you don't need to synch immediately a job that reads what the trigger does and synch at a well-known schedule would be best. Keep in mind jobs are not re-entrant.

* Noel