Offical team blog for SSDT, a tool for on and off-premise database development
Schema Compare is an incredibly useful tool, providing a visual head over SSDT’s model differencing and update engine. It can be used to compare any combination of database, project or dacpac, and allows selective update of the target schema (via an update script in the case of a dacpac). We’ve made some significant changes to the tool for the RTW release, improving its look and feel, particularly to make it easier to digest and process comparison results. This post describes many of Schema Compare's key features – some of which surfaced in CTP4 – with a screen shot at the bottom that highlights several of them.
First, the visual comparison ‘language’ of the results grid:
Differences-Only by Default: By default the grid contains differences only (with empty folders removed) – if there is only one difference you will see just one item. And the grid always contains all the actions resulting from the comparison – while you can hide an action temporarily within a contracted group it is always present in the grid and will apply to the update or script unless you exclude it by unchecking the action.
Equal Objects filter:A toolbar button adds equal objects to the grid. Enabling this is useful if you want to review, for example, unchanged columns alongside the changed columns in a table.
Unsupported Actions filter: You can also choose to see unsupported actions – these result from differences for which there is no supported action that can be taken on the target. These typically result from differences in server objects or built-in types between schema versions.
Action Icons: Actions (Add, Change, and Delete) are visualized using icons, making it easier to absorb a set of changes at a glance. The checkbox alongside an icon indicates if the action will be included in the update or generated script. If there is no icon the item will not be included in an update or script.
Grayed Items: Items that do not contribute to the update are grayed – excluded actions, unsupported actions and equal objects are all grayed. Folders are grayed when all their contents are grayed making it easy to see when a group of differences have all been excluded without you needing to drill in.
Grouping: By default, items are grouped by action so you can quickly assess what changes will be made on update. You can also group the results by object type or by schema. You can expand or collapse a group to temporarily hide detail, and you can exclude all or include all objects in a group.
Refactor Highlighting: Schema Compare processes the refactor log if present when targeting a database. Refactoring is indicated in the grid as a change action with the source name bolded to highlight the new schema and/or name. Refactoring will cause objects to be renamed in the database. Refactoring sometimes also shows up as a second order effect on other objects that SQL Server will modify when applying the rename. These will not be marked as actions in the grid as you cannot exclude them, but you will see the changed script if you select the affected object.
Probably the biggest set of changes affects the script difference pane. While the grid provides a great overview, to see all changes to an object in the grid you have to fully expand it, which, can quickly clutter the view if you're reviewing many objects. To address this we’ve focused more attention on the script differencing experience – after all, you are writing and editing object scripts to begin with. Changes include:
Expanded Object Scripts: The script difference pane now shows the combined scripts for an object and its hierarchical children. This gives a complete picture of all the changes affecting an object in one easy-to-scan place. To complement this, the Next and Previous buttons step between top-level objects only. Together, these two changes can dramatically simplify scanning through the results of a comparison.
Enhanced Script Differencing: The script difference algorithm now treats child objects as discrete entities, more effectively highlighting those that have been added, deleted or changed. The color scheme is now more subtle and better reinforces the direction of changes. And remember that you can expand the script pane or swap it to the top – so you can easily optimize the layout to better focus on reviewing scripts.
The screen shot below highlights many of these improvements.
Hopefully we’re headed in the right direction – as always we’d love to hear your feedback.
We can't compare .dbschema files anymore??? So how am I supposed to do a schema compare of the remote SQL 2005 databases that I have to support? Up till now, I've been using the VSDBCMD to get the remote schema off the remote SQL 2005 database and then compare the .dbschema file with my local project.
What can I install on the remote SQL 2005 server that will allow me to generate some kind of file I can use to compare the schemas?
This is absolutely terrible. The "Extract Data Tier Application Wizard" changes the syntax and formatting of objects. This makes comparison with my database project just about impossible. The previous VSDBCMD method that created a dbschema file did not have this behavior. Why can't you support comparing dbschema files any longer? I'm starting to regret investing my time and money into a Vistual Studio database project. I really thought that I could make it work.
Apologies for missing your comments!
Regarding your first question, .dacpac files are replacement to the .dbschema files created by vsdbcmd.exe. You can create .dacpac files using SSMS (the wizard referred to in your next question), SSDT, or SQLPackage.exe which replaces vsdbcmd.exe. SQLPackage.exe is installed with SSDT and located by default on a 64 bit machine here: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin. If you want to install SQLPackage stand-alone you will need to install DACFx plus its dependencies. These are available as part of the Microsoft SQL Server 2012 Feature Pack, downloadable here: www.microsoft.com/.../details.aspx.
Regarding your second question, when extracting a dacpac, it contains the normalized code stored by the database engine, which may be formated differently than the code in your project. Schema Compare will not normally report format differences which are not semantically significant. In some case (for example script of a computed column) where the semantics cannot be reliably determined it may report a difference. In this case you can update your project to match the code stored in the database using Schema Compare. If this is not consistent with what you're seeing then let us know.
In future, I recommend using our forum to post questions like these. The forum is actively followed by the team and the community. The forum is located here: social.msdn.microsoft.com/.../threads.
Until Data Compare not available for SQL Server 2012, Business Intelligence Tools and SSDT tools not available in VS 2012 shell, these don't buy anything to migrate to SQL Server 2012.
I used sql schema compare for several days, and it just make me CRAZY!!
The first problem is the checkbox (of "Action" column) can't be unchecked for no reason. I dig this for several hours, I tried settings , and searched at internet, but nothing helps. To simply re-create this issue, I created a new database in my localdb, and set it to "target", then choose an existing sql server database as "source", after compare, I exclude everything of "Add" folder, and ....most tables, users(which I don't need), schemas, are still be checked, and the checkbox is gray, so, why it compel me creating those tables? I think a tool should never treat users as kids.
The second problem is , after some compare and updates, the localdb database(target) is all the same with source, except missing column description(extended properties), the compare will found the difference, and even I can see sql code in "Object Definitions" panel , but , when I click update, nothing will happen, if I compare again, the difference is still there, I checked the scripts it created, just...nothing. The scripts reported "update successful", but in fact it simply ignored the extended properties, again, I tried settings, searched at internet for hours , nothing helps.
so, what's wrong ?
Hi Scott, sorry to hear you've been having problems with Schema Compare. I have not been able to repro either problem using the current (December 2012) release of SSDT based on your description with databases I have tried. The first problem sounds like a dependency analysis issue - there are some known issues with this code, but your scenario is not normally a problem. I can exclude all Add objects on a database that I'm looking at. Would it be possible for you to send a copy of your database schema(s) and projects and detailed repro steps for both problems . If so, could you create a dacpac using the latest version of SSDT and verify that a database created from the dacpac exhibits the same problem. If it does, could you send the dacpac file to me at bill dot gibson at Microsoft dot com.
In future, I recommend using our forum to post questions or report issues like these. The forum is actively followed by the team and the community, whereas comments on blogs, especially old posts like this, might get overlooked. The forum is located here: social.msdn.microsoft.com/.../threads
Again, apologies for the inconvenience, and thanks for reporting the issue.
What about the ability to filter out objects by name? My example is I have a database which dynamically generates individual stored procedures for applications to use (not to go into too much detail but basically users can pick a set of filters which get turned into a stored procedure, I know I know we are working on making the filters table driven, but it is what it is at the moment). So we have shifting list of 4000+ stored procedures that are only in our production environment. When I compare between our dev and prod environments I want to be able to completely ignore those stored procedures.
I'm using VS 2010 Professional version but i dont see the "Schema Compare" menu item in "Data" menu. Please advise if i need to install any optional tools to see these options.
Sai, you don’t indicate if you have installed SSDT which needs to be installed independently of Visual Studio in order for you see Schema Compare and the other SSDT tools. You can find out more information about SSDT including download links here: msdn.microsoft.com/.../hh297027.
If you still have problems please post your question on the SSDT forum: social.msdn.microsoft.com/.../home .
Why doesn't vs2012 Schema Compare not recognize case changes? Yes, SQL is not case sensitive, but tools that use it might be (CodeSmith for instance). I am trying to correct some casing in a SQL Server database so I can regenerate with corrected casing in my Library and Schema Compare says the files are equal with no option to "include" them anyhow. GAWD what a stupid feature.
Case sensitivity in SQL Server is determined by your choice of collation for the database. If you use a case sensitive collation, Schema Compare will report schema differences based on case and you will be able to apply changes to the target. Try the following. Change the collation on the project to a case sensitive collation (project > Properties | Project Settings and press Database Settings button). Then in Schema Compare, select Options from the toolbar or menu and enable 'Compare using target collation' on the General tab. Now run your comparison again and you should see the expected differences and be able to apply the updates to the project.
Finally, if you don't want the database to be case sensitive, change the collation on the project back to a case insensitive collation.
In future, I recommend using our forum to post questions/comments like these, especially on old posts. The forum is actively followed by the team and the community. The forum is located here: social.msdn.microsoft.com/.../home.
Hope this helps.
Thanks for making this fine product and functionality freely available. Undoubtedly, quite a bit of Engineering went into it; and the community applauds your un-equal dedication to growing and sustaining it.
Regarding the results of the schema compare; is there not a way to export the list of affected objects only? I know you can generate a script of changes but is there a way to report only on what objects are being affected? This would be good for high-level documentation. I just don't see a way to do it here.
I am using VS2013 and I frequently use Schema Compare from Database to Database directly.
Until a week ago, I made a mistake of table constraint and then the 'Generate Script' button and 'Update' button disabled (grey out)
So I updated my target database manually, refreshed comparison, but those buttons remained disabled.
Restarting Schema Compare, restarting VS, couldnot help.
So I decided to create Database Project. When I need to update my target database, I compare my database to my database project first. And then I compare my database project to my target database.
Everything seemed right, until a day ago, I made a mistake of table constraint (again) and then the 'Generate Script' button and 'Update' button disabled (grey out)... Again !!
Now I am using Dacpac file of Source Database, to update schema of Target Database. If this fail again someday. I do not know what to do.