I have two servers 2008 R2 and 2005. The e-commerce site has been running with 2005 Server i has migrated 2005 server data with modified data schema to 2008 R2 from yesterday's date of data. My client wants to migrate the complete DB to 2005 R2 whenever he will gets the new site from developers but i need to update changes from 2005 to 2008 R2 with modified schema everyday. How can i sync the data having different data schemas of different servers.
closed as too broad by Jon Seigel, Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, Michael - sqlbot Oct 23 '13 at 11:56
There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.
It is not quite clear what you are asking. It seems you are looking for a solution that would replicate data changes as well as schema changes from the 2005 server to the 2008R2 server.
This can be achieved with SQL Server Replication. Transactional Replication is able to move all data changes as well as all schema changes from a single source system to one or more target systems. The systems do not need to be at the same version of sql server for this to work (http://technet.microsoft.com/en-us/library/ms143241(v=sql.105).aspx)
For an introduction to transactional replication check out my Replication Stairways article series.
If this does not answer your question, please try to clarify what you are looking for.