Sync data of Table A between SQL Server 1 and SQL Server 2
One is live and SQLSVR2 is a backup. From time to time, I may need to switch them so I am writing a sync application that uses ADO .Net to read the modified/new rows from SQLSVR1->Table A and update/insert/delete them from SQLSVR2->Table A. These tables have a lot of columns(250+) which is why I want to use ADO. I was using the MS Data Set Generator to generate the dataset/data tables for me. The problem that I am having is that I cannot copy one data table from SQLSVR1 to a data table from SQLSVR2. I thought that I could use the UPDATE, DELETE and INSERT methods in the appropriate data adapter if I could populate the data table on SQLSVR2. Has anyone ever tried to do this or can anyone suggest an alternative solution?