1.1 - April 3rd, 2012

Today I am excited to announce that the first feature revision to ReadyRoll SQL Server projects for Visual Studio is out! Highlights of Version 1.1 include:

Improved change visibility with Side-By-Side Diff

The new diff tool window gives you the ability to view individual object changes side-by-side, prior to importing the change into your project.

The left pane displays the definition of the selected object at the source, reflecting the state of the object where the change was initiated (in this case, a column was added to the [Shippers] table on the local development SQL Server instance). The right pane displays the “current” state of the object (in this case, the Visual Studio project version of the object).

Immediately below the two-pane view is another pane containing an object synchronization script, providing a preview of how each individual object will be affected when the full script is generated.

You can access the new window through the ReadyRoll DBSync tool window by right-clicking an item in the pending change list and selecting View Object Differences:

You may also notice a couple of other new items within the DBSync context menu:

View Synchronization Script Opens a dialog containing a preview of the script that would be executed upon synchronizing with the project/database. This feature is useful if you want to confirm that destructive changes will not be included in the generated script.

View Revert Script Opens a dialog containing a preview of the script that would be executed upon executing the Revert Selected Object(s) command, which returns the selected objects to a previous state (in this case, the state of the object in the Visual Studio project). Similar to the View Synchronization Script command, this feature is useful if you want to confirm that destructive changes are not performed during a revert operation.

Less waiting around with Build Caching

Before deploying your database to a target SQL Server instance, it is necessary to first perform a Build in order to produce the SQLCMD-based deployment script.

Though ReadyRoll has a fairly lightweight build process, for larger projects/solutions time spent on simply waiting for the solution to build can quickly add up.

To reduce processing time both within Visual Studio and on your build server, ReadyRoll now makes use of MsBuild conventions to cache project artefacts as part of the build process. This means that the project only goes through the full build process if a project asset (eg. a Deploy-Once script file) is added/removed/modified within the project.

Here’s an example using a sample AdventureWorks database project, calling MsBuild directly from the command prompt. Notice the first time a build is performed it takes ~14 seconds to complete:

However upon subsequent builds this time is reduced to just ~0.1 second, because MsBuild only needs to check that all files are up-to-date with respect to the project inputs/outputs:

Extensibility with Build Events

On a recent implementation project a client had a requirement to integrate their database development process with their in-house ORM tool.

Rather than build this integration into the ReadyRoll codebase, I decided instead to add support for a new build target to the.dbproj file that would allow the ORM tool to update its object/relational map when the user performed the ReadyRoll Synchronizecommand.

Implementing the integration itself was then a simple matter of adding the AfterDatabaseSync target to the .dbproj file and consuming the meta-data passed in by ReadyRoll:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<Target Name="AfterDatabaseSync" Inputs="@(DBSyncFiles)" Outputs="OUTPUT_PLACEHOLDER">
  <PropertyGroup>
  <TablesAffected Condition="'%(ObjectCategory)' == 'Tables' and '%(ObjectOwner)' == 'dbo'">
  @(DBSyncFiles->'%(ObjectName)')
  </TablesAffected>
  <AddedObjects Condition="''%(FileOperation)' == 'Added'">
  @(DBSyncFiles)
  </AddedObjects>
  </PropertyGroup>

 <Message Condition="'$(TablesAffected)' == ''"
  Importance="High"
  Text="No Table objects were affected during Synchronization." />

 <Message Condition="'$(TablesAffected)' != ''"
  Importance="High"  
 Text="The following objects will be imported into the ORM: $(TablesAffected)" />   

 <Exec Condition="'$(TablesAffected)' != ''"
  Command="ImportObjects.exe /projectDir=c:\src\db\AdventureWorks\ /tables=$(TablesAffected)"
  IgnoreExitCode="False" />
 </Target>

The AfterDatabaseSync example above consumes the @(DBSyncFiles) metadata in order to create a semi-colon separated list of Tables that were changed during synchronization. The hypothetical ImportObjects.exe command is a utility belonging to the ORM tool which is responsible for keeping the object mappings in-sync with the database: by taking a list of changed Tables as a parameter, it updates its object/relational mappings based on the relational definitions provided by the ReadyRoll database project files.

Here is a list of the metadata passed-in to the target through the @(DBSyncFiles) item group:

• FileName: The name of the file affected, eg. dbo.Suppliers.sql
• FileOperation: Specifies whether the file related to the object was Added, Deleted or Modified
• ObjectCategory: The type of SQL object affected, eg. Tables, StoredProcedures, Views, etc.
• ObjectName: The name of the affected SQL object, eg. Suppliers
• ObjectOwner: The schema that the affected SQL object is contained in, eg. dbo

In addition to the AfterDatabaseSync target, a number of standard MsBuild targets are now supported by ReadyRoll database projects including: BeforeBuild, AfterBuild, BeforeDeploy, AfterDeploy, BeforeClean & AfterClean.

Other Changes

In all Version 1.1 contains nearly two dozen new features, improvements and general bug fixes, including:

• Encrypted objects can now be decrypted and processed (requires membership in the sysadmin server role)
• Support for built-in system objects within the project declaration files, such as the “db_datareader” role and “dbo” user
• Improved clarity of DBSync tool group headers: the text/icons within the pending object list more clearly articulate the source & destination of changes
• Increased level of detail in build progress reporting: ReadyRoll now echoes each stage of progress during project build