Friday, March 30, 2012

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
identical table TESTTBL. I need to make sure that tables TESTTBL in
both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
following C# code snippet fails. Is it possible to execute command
across two servers or am I wasting my time? Any help on this will be
appreciated.
string sSql= "delete SERVER-2.TESTDB.TESTTBL where
SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
from SERVER-1.TESTDB.TESTTBL )";
SqlConnection thisConnection1 = null;
SqlConnection thisConnection2 = null;
SqlCommand thisCommand1 = null;
SqlCommand thisCommand2 = null;
thisConnection1 = new SqlConnection(connectionString1);
thisConnection1.Open();
thisConnection2 = new SqlConnection(connectionString2);
thisConnection2.Open();
thisCommand1 = new SqlCommand(sSql, thisConnection1);
try
{
thisCommand1.ExecuteNonQuery();
}
catch (SqlException oE)
{
MessageBox.Show(oE.Message.ToString());
}Have you consider using REPLICATION between tow servers?
<bkasmai@.gmail.com> wrote in message
news:1144070684.346122.72030@.v46g2000cwv.googlegroups.com...
> Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
> identical table TESTTBL. I need to make sure that tables TESTTBL in
> both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
> which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
> would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
> make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
> following C# code snippet fails. Is it possible to execute command
> across two servers or am I wasting my time? Any help on this will be
> appreciated.
> string sSql= "delete SERVER-2.TESTDB.TESTTBL where
> SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
> from SERVER-1.TESTDB.TESTTBL )";
> SqlConnection thisConnection1 = null;
> SqlConnection thisConnection2 = null;
> SqlCommand thisCommand1 = null;
> SqlCommand thisCommand2 = null;
> thisConnection1 = new SqlConnection(connectionString1);
> thisConnection1.Open();
> thisConnection2 = new SqlConnection(connectionString2);
> thisConnection2.Open();
> thisCommand1 = new SqlCommand(sSql, thisConnection1);
> try
> {
> thisCommand1.ExecuteNonQuery();
> }
> catch (SqlException oE)
> {
> MessageBox.Show(oE.Message.ToString());
> }
>|||Yes. Past experience prevent me from using replication.
SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.|||Well, another option is to BACKUP LOG file on the source SERVER and
RESTORE it on destination. But I see the problem here because if I remember
well the destination SERVER must be read only....
<bkasmai@.gmail.com> wrote in message
news:1144073079.759225.46870@.e56g2000cwe.googlegroups.com...
> Yes. Past experience prevent me from using replication.
> SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
> SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
>|||The sync needs to be done every hour through a application written in
c# !

No comments:

Post a Comment