Redgate schema compare

We have a system of about 300+ databases which the way the applications are designed need to be identical. Our releases include releasing objects to all the databases. But once in a while we have a production requirement or issue and have to roll out a hotfix to a particular database. You know how exciting DBA jobs get once a while and thus we tend to forget releasing those few objects to all other databases.

Hence, for that reason we wanted a database schema compare job to be run once a month or week.

Recently we bought Redgate developer suite and loved the capability to integrate with SSMS.
Also, its database schema compare was much faster than any other tools I have used previously. Along with that it also offered the capability to do source control and database compare, which we had been looking for a while.

Thus to automate the compare process, we decided to use the Redgare sql compare commandline features.
Here is a sample of what options were used/ignored and what switches were used to include and exclude objects:

1. echo Start Compare %date% -%time% >>C:testlog.txt

2. cd "C:Program FilesRed GateSQL Compare 9"
SqlCompare /S1:server1 /db1:baseDatabase /S2:server2 /db2:databaseToCompare /Exclude:Schema /Exclude:User /Exclude:Role /Options:Default,IgnoreComments,IgnoreConstraintNames,IgnorePermissions,
IgnoreFillFactor,IgnoreWhiteSpace,IgnoreQuotedIdentifiersAndAnsiNullSettings,
IgnoreWithNocheck >>C:testlog.txt

3. echo End Compare %date% -%time% >>C:testlog.txt

As you can see, the 1st and the 3rd statement is used to get time estimates for comparison. For us it took only about 40 minutes to go through 300+ databases, which is impressive for the kind of work this tool is doing in each database.

The 2nd statement is where the actual command is either run in windows command prompt or put in a bat file. As you can see it first changes directory to where sqlcompare.exe lies and then runs the compare across server1-database1 and server2-database2.

/Exclude is a switch used in comparison which lets us exclude comparison for certain objects, as in our case like user, role and schema. But you can also exclude to the level of tablename which is really cool aspect.
More information on switches can be found at:
Switches

/options are options in option tab you see in the SQL Schema compare Gui. You can use it to Ignore Permissions, whitespace, FillFactors etc during comparisons which may not be that important during the first run.
More information on options is at:
Options

The way we designed it it I ran the SQL comapre across two same databases, from GUI and commanline using same set of options at a time. Then adding more options as needed. This way I cud get the exact same result in commandline as in Gui.

We wanted this as a step by step process and initially we compared only Procedures, functions, views and tables fixing them across all databases.
Second iteration we compare only tbales and constraint names, because we had hundreds of system named default constraints and renamed them appropriate and same across all databases.

Needless to say before doing any of the object changes across all databases, we send them to our software developers for a quick review and released it using regular release process.