Red Gate forums :: View topic - Awesome...but....
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation

Scream forum

Awesome...but....

Search in Scream forum
Post new topic   Reply to topic
Jump to:  
Author Message
fordc03



Joined: 02 Jun 2006
Posts: 39

PostPosted: Wed Aug 30, 2006 4:38 pm    Post subject: Awesome...but.... Reply with quote

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

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...
Back to top
View user's profile Send private message
alex.weatherall



Joined: 29 Nov 2005
Posts: 42

PostPosted: Fri Sep 01, 2006 8:38 am    Post subject: Reply with quote

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....) Very Happy 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 Smile 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!!!! Shocked
Back to top
View user's profile Send private message
fordc03



Joined: 02 Jun 2006
Posts: 39

PostPosted: Fri Sep 01, 2006 3:29 pm    Post subject: Reply with quote

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... Smile
Back to top
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 395
Location: Red Gate Software Ltd

PostPosted: Thu Jan 18, 2007 11:01 am    Post subject: Reply with quote

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 Wink
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
fordc03



Joined: 02 Jun 2006
Posts: 39

PostPosted: Thu Jan 18, 2007 3:14 pm    Post subject: Reply with quote

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

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.
Back to top
View user's profile Send private message
dwainew



Joined: 14 Sep 2005
Posts: 40

PostPosted: Thu Mar 22, 2007 6:52 pm    Post subject: Holy PETABYTES, batman! Reply with quote

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!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group