Risk with column modifications

logic

Hello,

I was pleasantly surprised to find that renaming a BIT column "Enabled" to "IsEnabled" generates an "sp_rename" for the column. However, if the column's data type is also changed from BIT to INT in the process, the resulting script will kill all existing data in the column.

Ideally, I would like the ability to detect these mapping problems before/during the generation of the ExecutionBlock using Work.BuildFromDifferences. I would like the comparison SDK to make it possible for me to identify columns (and tables, for that matter) that are going to be dropped and allow me to choose an alternative course of action, such as ignoring the change or explicitly mapping it to a new name.

However, based on what I have read, I suspect this is not a "simple" change for the current architecture and would require a fair bit of juggling to be possible. In other words, it is an "expensive" feature request.

What I really want to avoid is lost data under any circumstance. We are going to be inviting our developers to make database changes to an enormous and complicated schema by simply modifying a "live" database (non-client -- dedicated to the purpose); these are then propagated to client databases (indirectly) using the SQL Comparison SDK. Some of our developers are less detail-oriented than others and I think the risk of someone eventually making a rename that doesn't get mapped properly is high. If this got out to a client database, we would be spending the better part of a day restoring their database and making sure that every place the update script is stored gets fixed up.

Is there a mode for the SQL Comparison SDK to cause it to raise an error instead of proceeding if the detected changes involve data loss (dropped tables or columns, specifically)? If not, could you add such a setting?

Thank you,

Jonathan Gilbert
iQmetrix Software Development

Chris Auckland

Thanks for your post.

SQL Compare will attempt to map columns so they can be renamed, but there isn't anywhere in SQL Server that tracks a renamed column, so it's hard to tell if a column has been renamed, or if it's a new column. We do try to guess based on the similarity of the names, but it isn't an exact science.

You could possibly check the migration script for any drop statments prior to running, and if any drops are found the script must be reviewed before it is executed. If columns are dropped, you can use SQL Data Compare to populate the new column with data from the souce.

I can't think of any way to do this during or before the generation of ExecutionBlock though.

logic

Thanks for your reply.

Scanning the generated SQL is going to be a very inexact science. For instance, for the example that I mentioned in my post, the generated output does not include an ALTER TABLE DROP COLUMN. Instead, it creates a new temporary table that includes all the other columns, SELECTs all the data into that, drops the original table, recreates it with the altered column schema, and then SELECTs the data from the temporary table back into the newly-created table.

What I'm looking for is some way to block the generation of that code in the first place -- perhaps a boolean, or a flag in the Options enumeration that is used to initialize various parts of the SQL Comparison SDK. When set, if a column failed to map as a rename, the engine would raise an exception. As a more complicated, but more flexible alternative, it could raise an event to allow the consuming code to supply an explicit action -- abort, permit the drop, map to a specific column name or ignore the change.

Here is a code example that shows how the new feature might be used (window creation in-line only to avoid having to show multiple files):

Work work = new Work();

work.MapColumn +=
  delegate (object sender, MapColumnEventArgs mapArgs)
  {
    Window dialog = (Window)XamlReader.Parse(@"
<Window xmlns=""http://schemas.microsoft.com/winfx/2006/xaml/presentation""
        xmlns:x=""http://schemas.microsoft.com/winfx/2006/xaml""
        xmlns:rg=""clr-namespace:RedGate.SQLCompare.Engine,assembly=RedGate.SQLCompare.Engine""
        SizeToContent=""WidthAndHeight"">
  <StackPanel>
    <Label>Could not automatically map column $ColumnName$ to a new name</Label>
    <Button Tag=""{x:Static rg:ColumnMappingAction.KeepColumn}"">Keep the column</Button>
    <Button Tag=""{x:Static rg:ColumnMappingAction.DropColumn}"">Drop the column</Button>
    <Button Tag=""{x:Static rg:ColumnMappingAction.Abort}"">Cancel script generation</Button>
    <DockPanel LastChildFill=""True"">
      <Button TabIndex=""2"" Content=""Map"" Tag=""{Binding ElementName=txtNewName}"" />
      <TextBox TabIndex=""1"" Name=""txtNewName"" />
    </DockPanel>
  </StackPanel>
</Window>".Replace("$ColumnName$", e.ColumnName));

    dialog.AddHandler(
      Button.ClickEvent,
      (RoutedEventHandler)delegate (object innerSender, RoutedEventArgs clickArgs)
      {
        Button button = (Button)clickArgs.OriginalSource;

        if (button.Tag is TextBox)
        {
          mapArgs.Handled = true;
          mapArgs.MappingAction = ColumnMappingAction.Rename;
          mapArgs.NewColumnName = ((TextBox)button.Tag).Text;
        }
        else if (button.Tag is ColumnMappingAction)
        {
          mapArgs.Handled = true;
          mapArgs.MappingAction = (ColumnMappingAction)button.Tag;
        }

        dialog.DialogResult = true; // hide the dialog
      });

    dialog.ShowDialog();
  };

work.BuildFromDifferences(differences, schemaOptions, invertDirection);

As a feature request, is there any chance of something like this being added to the SQL Comparison SDK? If not the full-blown event shown above, then perhaps the more minimal flag to have work.BuildFromDifferences (or database.CompareWith, if that's where the column mapping is performed) throw an exception if a column is being dropped?

Thanks,

Jonathan Gilbert
iQmetrix Software Development

Chris Auckland

This really isn't possible at the moment because columns are not currently treated as objects in their own right.

With the current version, you either sync the table object or you don't. If the migration script includes a drop for a particular column, then the only way to prevent this is to manually edit the script.

However, it is currently planned that in the next major version of SQL Compare, the user will be given a greater deal of control, and will be able to treat columns, triggers, indexes etc as objects and include/exclude them accordingly.

I don't know exactly when the new version is planned, but the feature request for this is SC-2447. I have added this post as another vote for it.

I hope this helps explain the situation.

logic

Okay. Thanks for doing that for me. I would like to point out that neither of the feature requests I made requires that columns be represented as objects in the collection of differences. I'm glad to hear that this is planned, but it sounds as though it won't be ready for use for some time.

In the mean time, is it possible to add, as a minor update for SQL Comparison SDK version 8, the simpler of the two features I requested -- specifically, a flag that the engine processes internally by aborting the generation of a script that drops a column? At the very least, I could warn our developers that it is happening.

Sample usage:

schemaOptions |= Options.ErrorOnDroppedColumns;

When set, a column that doesn't map results in an exception.

Is this something you would consider?

Thanks,

Jonathan Gilbert
iQmetrix Software Development

Chris Auckland

It's a good suggestion, and I can see why you would want to do this.

I've logged the request as feature SC-4674. I'll let you know when I find out if/when we might be able to add this to the SDK.

logic

Awesome. :-) Thanks very much.

If you do fit it in, please make sure it is possible to provide feedback on specifically which column of which table is getting lost.

Thanks again,

Jonathan Gilbert
iQmetrix Software Development