Database Compare Application help needed

[Guest]

I have to write a .Net application which can compare SQL Databases including
things like: -
DB structure, PK's, FK's,
indexes and types of indexes i.e. should be able to detect if the same index
has cascade set on one db and not on another, or unique on one and not the
other,
Constraints, Triggers, Stored procs, Users, Roles.
I downloaded the SQL Data Compare 3.0 application 14 day trial and it seemed
really good however does a bit more than I am required and I have to design
an application tailored to my businesses needs. At present there is a system
in VB.6.0 but I have to design a C#.net application using the latest SQL
objects available to do this. I was wondering if anyone could point me in
the direction of any source code/ solutions I could look at for .net which
allow me to compare databases. Or if anyone had any help and could point me
in the right direction I would be very greatful. Thank you very much. Even
if anyone know what objects are available in .net to compare databases I
would be very grateful

 

[John Spiegel]

Hey Stephen,

One option may be more to use C# as a reporting tool in the sense that most
of the work should be done within SQL Server. For exampley, you might
create a linked server within one SQL Server that points to the master
database of the one you wish to compare to. With this link defined, you
could then run queries comparing the two tables essentially as though they
are both right there.

You'd probably want these queries bundled into one or (likely) more stored
procedures that could then be called from a .NET app. I would envision then
returning rowsets of the differences. On the C# side, I'm sort of
envisioning a list of the comparisons that exist, allowing a user to maybe
select which to run then calling the corresponding stored procedure and
placing the results in a datagrid.

That's kind of a basic glossing over, but it sounded to me like you were
more focused on doing the work on the C# side, whereas comparing structured
data is one of the cores of an RDBMS.

HTH,

John

 

[Nick Malik]

Hello Stephen,

I do not know what open source libraries exist. I trust you can google for
yourself and see.
I did stumble across this older sample app:
http://www.c-sharpcorner.com/database/database_explorer.asp

There's no objects in the framework, that I've been able to find, that are
particularly good for the kind of comparisons you want. Sure, the DataSet
contains definitions of rows and columns, but it doesn't help you find
foreign keys, or stored proc dependencies, and the like.

If all your databases are SQL Server, and your app will run on the SQL
Server machine with the highest version number (in other words, if you are
comparing SQL Server 2000 with SQL Server 7.0, you'd need to run your app on
the SQL 2000 box)... if all that is true, you can use SQL-DMO. These are
COM components that provide a complete object heirarchy for structural
comparisons, scripting, and database manipulation. (The Enterprise Manager
uses SQL DMO to actually perform it's work, so if it can be done with EM,
you can do it with SQL-DMO).

If any DB is not SQL Server, then you are out of luck with DMO. Also, DMO
is not useful to run from a client, since it is not legal to install it
there.

If you need to compare from a client app, then you will need to inspect the
SQL Server system tables themselves. Unlike John, I do believe you should
do this work in C#. Since you are comparing two databases, there is no
guarantee that the databases can, or should, talk to each other, setting up
a linked db may not be a viable solution. Even if it is viable, it is not
likely to be something you'd be allowed to do in an evironment where
security matters even a little bit. (You should meet some of the data
security folks where I work! These guys mean business. They audit
everything and they have the power to shut off any app that doesn't abide by
the rules.)

There are probably books and online tutorials that will tell you how to make
sense of the system tables... I can't help much there. I know that some of
it can be gleaned from SQL Books Online.

Hope this helps,
--- Nick

 

[John Spiegel]

Nick makes an excellent point on security. You'll definitely need to
consider such things.

My history has been (and this is meant to give an idea of where the
suggestion came from than because I think you want to know my life story
from DNA thru this afternoon) in smaller shops where such operations are
generally done within a company umbrella where one department shares
another's data freely. If it gives you an idea of "small shop", currently,
I AM the "security guys" <g>.

Also, having "grown up" in dBase dialects, where language and data are more
closely tied, I'm probably more skewed towards doing the anaylsis directly
from stored data than may be considered best practice in an n-tier world.