Synchronising databases SQL Server via C#

Question

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

Answer 1

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.

Answer 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.