4

I've been using the Red Gate SQL Comparison API in a project that's getting long in the tooth. We use a fraction of its abilities and I am evaluating other options.

Our scenario is basically this:

  • We have two SQL Server 2008 databases that can be assumed to have identical schemas.
  • The databases are tiny. Most tables have less than 10K rows; some have less than 100.
  • DB1 can be considered "primary." We only make changes to DB1 directly, and those changes are propagated to DB2.
    • A small set of five tables, when modified in DB1, must be replicated to DB2 immediately.
    • Occasionally the entire database — less a set of exclusion tables (e.g. logs) — must be replicated at once, again from DB1 to DB2. This is triggered from a web form.

At the moment all of this is handled in code using Red Gate's SDK to do the dirty work.

How else might I go about this? I'm open to pure SQL Server solutions (replication?) if you think it's the right choice, though a pure C# solution would require a bit less interaction with my IT department, if you catch my drift.

roufamatic
  • 18,187
  • 7
  • 57
  • 86
  • Is there any reason why you've not tried SQL Server replication? – David Atkinson Jun 17 '10 at 23:17
  • I inherited this project as-is, but that said I personally haven't tried it before. If replication works within the parameters I've described above -- one table at a time or some tables, kicked off by a user interacting with a website -- write me an answer! – roufamatic Jun 17 '10 at 23:39
  • I've been on a training course where they described it, but don't have personal experience, so I wouldn't feel confident giving you advice. I found this though, which may help: http://msdn.microsoft.com/en-us/library/ms151870.aspx – David Atkinson Jun 18 '10 at 16:56
  • By stating `We only make changes to DB1` do you mean schema, data or both? – Joshua Drake Apr 10 '12 at 20:55

3 Answers3

2

The only other programmability product I know of would be ApexSQL's Diff API - Apex SQL Diff is a similar product, and they also publish an API to do these things programmatically in your own app.

I've never used it myself, but I have used (and really enjoyed) other ApexSQL tool - highly recommended.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
2

I know I am a bit late to the party, but I've just released the source code to Atlantis.SchemaEngine - which does the schema part of the DB, and was considering whether to release Atlantis.DataEngine (no prizes for guessing what that does). Is that something that would be of interest? SchemaEngine is here.

Matt Whitfield
  • 6,436
  • 3
  • 29
  • 44
1

You could try using FluentMigrator. It's a database migration library for .NET. You could use it to define your schema in C# and then push changes out to both databases.

John Mills
  • 10,020
  • 12
  • 74
  • 121