What are the challenges you face when working across database platforms? Take the survey

SDK Data compare needs the dbo.<tablename>_Meta.sdcs file

ccollinsccollins Posts: 64
SDK 8, SQL Source control We are unable to use static link in SSMS, due to multiple issues.

As long as we had small recordset, <1000, the static data source controlling was usable. When we surpassed that, multiple issues arise. So we are being creative. The following was not noticed using the SSMS addon:

I am managing our static data with our own export-from-db, tfs check in, tfs get latest and import-from-script applications. I started noticing that records were missing from the build and deployments to qa test servers. I tracked it down to an issue with the data compare. While the latest dbo.<tablename>_Data.sql file contained all of the records, the compare did not produce differences and subsequently did not update the database. Once I exported the complete recordset from our build database and checked in the .sql as well as the corresponding .sdcs file to tfs, everyone was up to date. It appears that we need to source control the sdcs files as well as the sql files in order to accomplish a successful get latest of the data.

Any thoughts or comments would be appreciated.


  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1

    The SQL SDK doesn't really have any source control features. Now as far as I know, if you were managing the source control entirely using SQL Source Control, all of the necessary files would have been checked in. If you're using multiple tools to imput things into the repository, I can see these sorts of problems happening.

    There are some SQL Source Control-specific files that need to go into the repository for it to work properly.
  • Options
    The root of this discussion is the ability to compare static data sql scripts to a database. The build performs a get latest of all files. This works great and as desired with schema objects. This does not work well or as desired with static data.

    We need for each developer to edit and commit static data. This works as desired with individual development environments and databases. The build is automated as it should be, and this the issue and the question about the sdcs.

    Please reply with how we are going to accomplish the goal of automating the build, (i.e. get latest from tfs, update the build database which is using sql source control, build, package schema and data for deployment).
  • Options
    I'm afraid we are not offering that level of support at the moment. If you're writing an SDK app, then fair enough, but as it does not include a source control API, you'll have to work out some way of batching the comparison/synchronization using the SDK and the source control checkin/checkout using your own source control system's API.
  • Options
    Okay. Then I must stop using red-gate for data control.

    I am planning on exporting data, checking it in and then on the build machine get latest and compare the data file to the database.

    Can this be accomplished with the sdk? If so, I have only been able to perform half of this, export to file or write sql script. I have been unsuccessful in accurately getting data changes to the other developers as well as the build machine. I export to files, check in the files, get latest but have not been successful with the script to database data compare. I see the differences in the insert sql and new records, but the sdk data compare keeps missing it, not syncing the data to the database?

    Should I go back to bulk loading the data and then database to database compare?
  • Options
    There isn't anything in the SQL SDK to interface with Source Control - your Source Control vendor should provide you with an API to use for that. But you mention some problem comparison data.... can you elaborate? Is it because you're not getting the latest files from source control or because of some other problem? Have you got a specific example?
  • Options
    The compare does not work when using the sql data files without the corresponding metadata from the source. Even then there is a piece missing that is preventing reliable file to datatable synchronization.

    I have tried to move the metadata from the 'source', to another workstation and sometimes the data is synced, other times not.

    I don't have time to continue down this path. I am going back to a more efficient static data concept. Our process exports the table data to a file in a bulk-loadable format and checks it in. The everyone can get the latest bulk-loadable file, load into tempdatabase and sync from tempdatabase to developmentdatabase. When done, export table data back to the bulk-loadable file and check in.

    I have a SSMS gui-addon that allow you to select the entire table(s) as well as individual records for get latest and export to file for check in.

    This works in minutes, not hours like the current red-gate source control static data concept.

    Deployment is the same, deploy the bulk-loadable format file, load tempdatabase and sync from there.
Sign In or Register to comment.