0

How do I synchronise a database on one server to the other? I need to do it via C#, not scripts. I was planning to use ADO.NET to retrieve the schema and dataset from one database, but how do I sync it in code?

Thanks

Improve this question
1
  • 1
    You need to define 'synchronize'. Keep schema in sync? Keep data in sync? Keep an identical copy of the database? Commented Jul 20, 2009 at 4:48

2 Answers 2

Reset to default
2

There are various options available to you:

  1. SSIS to Export/Import data between System1 & System2
  2. Mirroring to copy data between System1 & System2
  3. Replication to keep System2 in sync with System1
  4. Scripts for Database Backup/Restore between servers using C# to be the IO glue that scripts the backup on System1, copies the file to System2 and calls the restore script on System2

The easiest option to implement is #4 specially if changes occur to System1 that need to be replicated to System2 (tables, indexes, views, procedures, functions, triggers..etc..etc...)

See Database Mirroring in SQL Server 2005 @ SQL Server Performance or Database Mirroring in SQL Server 2005 @ Microsoft for mirroring information.

If you need some more enlightment on #4 just reply. Oh, it would help to specify what version of SQL server you are using. This information assumes >=2005 (Yukon, Katmai and possibly Killimanjaro)

Update: I would stay clear of trying to implement your own runtime on this as there are so many variations that just copying between 2 servers requires the ability to do diffs against the objects. Even using the SMO .NET objects this would be an ardous task that would require a lengthy development schedule.

Update 1: The poster is interested in the SSIS version so we will use those instructions.

  1. Start SQL Server Management Studio
  2. Navigate to the chosen database
  3. Right click and Tasks->Export Data
  4. Click Next
  5. Select required source
  6. Click Next
  7. Select destination settings
  8. Click Next
  9. Select either use either tables or Write a query (we will assume use tables)
  10. Click Next
  11. Select the required tables
  12. Click Edit Mappings
  13. Ensure that enable identity insert is selected if required
  14. Ensure Delete/Appends rows is selected as required
  15. Ensure Drop and re-create destination table selected as required
  16. Click Ok
  17. Click Next
  18. Now save this to an SSIS package either into SQL Server or on the filesystem
  19. You can now use DTSExec to execute this package via a scheduler or use the .NET wrapper and call from your own C# runtime

C# Code example from Microsoft Website

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace RunFromClientAppCS
{
  class Program
  {
    static void Main(string[] args)
    {
      string pkgLocation;
      Package pkg;
      Application app;
      DTSExecResult pkgResults;

      pkgLocation =
        @"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" +
        @"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
      app = new Application();
      pkg = app.LoadPackage(pkgLocation, null);
      pkgResults = pkg.Execute();

      Console.WriteLine(pkgResults.ToString());
      Console.ReadKey();
    }
  }
}

Resources

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I want to know about #1 (SSIS to Export/Import data between System1 & System2) .Can you give me some sample code for this?
0

Why would you build this when there are db tools that do it for you? Look into SSIS, or as it was previously known, DTS.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.