Awesome...but....

fordc03

Okay...this is awesome, but it compares snapshot files...I have 48,000 databases, so creating snapshots of all of them would take 1.8 Tera-Bytes of Disk space...

Is there a way that I can do a live compare without all the sync options of SQL Compare??

That would be the best tool ever for me.

I don't need to sync them, I just need to know if they're different from our baselines.

Yes yes...I know...it's an ungodly number of databases...even Microsofts eyes get big when we tell them how many we have to manage.

So anyway, I digress...Is there a simple tool for that? I've been trying to write my own with the SQLCompare API's, but...it's kind of...slow because I don't want it to generate all the change code...just show me that I have differences, like the table name and owner or stored proc, but not tell me all the syscomments text and all the differences between our baseline and the target db. I can use SQLCompare later for that.

Thanks...

alex.weatherall

Surely your SQL Compare snapshots are only around 300k -> 1MB in size? All of ours are. A snapshot is just the schema and objects - no data.

If so then at the most you would be talking about 48GB not TBs???

Still a lot of disk space - but not Tera bytes?

Before using SQL Compare (I never want to go back....) I used to use a query to get all the key areas (names, text, datatypes) of the baseline database schema and other objects from sysobjects, syscolumns, syscomments and perform a checksum on them. I would then do an aggregate checksum on the checksums and store that result. Then if I ever wanted to check that another database was the same as a particular baseline I would run the same query against that database and compare the resulting checksums - If they were the same, then I could be reasonably confident that the database objects where the same in both databases. (I've simplified my explanation a little - I generated a binary string from a combination of different checksums).



I only used this for a short time as I discovered SQL Compare not long after.

It has holes - a few false positives :-) However it did help.
If you combine it with a UDF in each database where you store your version number (alter it in every released upgrade script) this reduces the false positives.

I am starting to look at reusing this technique as like you I can't always use SQL Compare to check all our customer's DBs. I only want to check that the DB is at the version it claims to be at - and produce a warning if it isn't - so that we can then use SQL Compare to check the diff's.

I won't be managing 48,000 DB's however!!!! :shock:

fordc03

I wish our schema's were 1mb in size. but they're not.

They're 30mb-40mb each.

So the math is right...let's average them and we'll say 34MB per schema...

34 x 48000 = 1,632,000MB

So....that right there is 1.632 TB for a snapshot.

Schema's are huge here...again...eyes go big, people fall out of chairs, etc. we get asked all the time, what were we thinking...I don't know, I don't claim the work. I just have to fix it and manage it and create an audit for it.

It existed before I got here, a plan to "fix" the mess is in the works, but currently...I have to audit all those production databases. And there's an insane number of them...

richardjm

Maybe if you post your query on the SQL Toolkit forum somebody may be able to help you. Scream is only intended to compare snapshot files that have been generated by SQL Compare.

Just had a quick chat with Andras and we can't compare the databases without retrieving the syscomments text. But you certainly don't have to generate the change code to know if there are any differences. However you do have to perform a full compare to find out if they are identical.

You've certainly got an interesting problem though

fordc03

Thanks for the reply! Since posting the above I've learned quite a bit about what I would need to do a compare, and there's not a way to make RedGate's compare any faster than it already is.

So, I've come up with a rather creative solution using the SQL Compare, SQL Packager, and SQL Data Compare API's.

It works very well.

Although still slow, it's not the fault of the toolkit...each server has about 1,000 databases, about 700 of them contain over 6,700 objects...a full sync script to a null database is 128MB if that helps in comparison.

Instead of crawling all 48,000 databases I only need to make sure 20 are sync'd. Then take those changes from the SQL script and run that across the environment. If it fails, then I rollback and do a full sync.

Originally I multi-threaded it...bad idea, the workstation I was using couldn't handle it when it hit the compares as that part was so CPU intensive.

But, so far...I couldn't be happier with the support I've received or the quality of the products I've been using from Red Gate.

dwainew

48 Servers, check.
1,000 databases each, chcheck..
6,700 objects per db, chchcheck...
1.6 TB of SCHEMA??!! Holy chchcheck!

Not a single server at your disposal to facilitate a schema management project???!!!

And not a hint of contention or bitterness!

fordc03, you deserve a MEDAL!