How can we help you today? How can we help you today?

Compare replicated tables

It would be nice if there was an option to ignore replication objects. Our Development database is not replicated, but the Production one is. When I compare the two (Scripts Folder to Database), the following object types are returned:

Objects that exist in Production, but not Development
- Views with names like MSmerge_contents_<merge article name>
- Stored procedures with names like sp_sel_<merge article guid>_pal

Objects that exist both, but are different.
- All tables that are replicated, because of the "msrepl_tran_version" or the "rowguid" uniqueidentifier columns that are added when publishing
- These same tables can have constraints to check for identity ranges, named "repl_identity_range_pub_<merge article guid>"
- The "rowguid" columns all have a UNIQUE NONCLUSTERED INDEX

The scripts that are generated to synchronize the tables do not take into account that some tables are replicated. We're still using SQL Server 2000. In that version, to add or remove columns from published tables, you have to call the special stored procedures sp_repladdcolumn and sp_repldropcolumn.

Other remarks:

It would also be nice if the Foreign Keys would be scripted in the same order (by name?). This would make the visual comparison easier. (The synchronization scripts don't care about the order)

When exporting the Data Source to an existing Scrips folder, it would be nice to have an option to delete the existing scripts. That way, an existing project can be re-used for comparison.
Comparing the Data Source to the script folder also finds the changes, but the Synchronization Wizard wants to generate a change script. I'd like it if it added / deleted or changed the scripts in the folder.

The link "Red Gate Software Support Forum" in the Help menu goes to a non-existing page (http://www.red-gate.com/SQLCompare/Forum/v8)
Turambar
0

Comments

4 comments

Sort by
  • David Atkinson
    You should be able to create your own filter rule that excludes the replications objects that you mention. To ignore replication triggers you'll need to set the appropriate project option. You can also ignore NOT FOR REPLICATION. The one thing we can't ignore for now is replication rowguid fields. You will have to add these to your non-replicated database as a workaround to ignoring them.

    Once you've saved a filter rule, you can load and re-use this for different projects. Please could you give this a go and let us know if this works for you.

    We are aware of the lack of SQL Compare support for sp_reply...column and it is unlikely that we will ever support this given that it is deprecated in SQL Server 2005+.

    Thanks,

    David Atkinson
    Red Gate Software
    David Atkinson
    0
  • David Atkinson
    Turambar wrote:

    When exporting the Data Source to an existing Scrips folder, it would be nice to have an option to delete the existing scripts. That way, an existing project can be re-used for comparison.
    Comparing the Data Source to the script folder also finds the changes, but the Synchronization Wizard wants to generate a change script. I'd like it if it added / deleted or changed the scripts in the folder.

    Would it be possible for you to give more detail on this problem as I'm not quite sure what you're trying to do here. What do you mean "re-used for comparison".

    When you compare a data source to a scripts folder you should be able to generate a SQL Script OR change the files directly. The latter is the correct way to update your scripts folder. Is this not working for you?

    Thanks!

    David
    David Atkinson
    0
  • Turambar
    Good call on the Filters. I hadn't looked at those yet, and they seem to address my problems with the replication stored procedures and views.

    To answer your other question:

    I have an existing project that compares the scripts in the "C:\QDev\SQL Compare\Dev" directory to the live Production database.
    After making some changes in the Development database, I want to re-script the database to the scripts folder to compare to Production.

    If I choose "File > Export a Data Source" and point to the "C:\QDev\SQL Compare\Dev" folder, no files are overwritten. New files are created named "<objectname>1.sql".
    I could script to another directory, but then I would have to modify the project before I can re-use it for comparison. That's why I would like to delete the existing scripts before exporting a Data Source.
    When you compare a data source to a scripts folder you should be able to generate a SQL Script OR change the files directly. The latter is the correct way to update your scripts folder. Is this not working for you?

    Thanks for pointing that out. That does fix my problem! I had some trouble finding that option. It wasn't totally clear that I needed to choose "Synchronize using SQL Compare" in the Synchronization Wizard. Maybe you could add some help text or an explicit option "Synchronize Scripts Folder"?

    I found a small bug in the Wizard. When you go from "step 2 - Review Dependencies" to "step 3 - Review Script", you can see the Affected files. If you go back to step 2 and go to step 3 again, the files will be in the list twice.

    Thanks for the help!
    Turambar
    0
  • Simon C
    Hi Turambar,
    We don't really allow a scripts database to be exported to an existing folder, as there could be files in that folder already that cause the resulting scripts folder to represent an invalid database.

    To do what you need, you can synchronize from the live database onto the scripts folder, which will make the scripts the same as the live database.

    Simon C
    Simon C
    0

Add comment

Please sign in to leave a comment.