User profile for Kendra_Little

Hi Godfrey, Apologies that you hit this issue. SQL Compare does not currently support external data sources / external tables. We have a user voice item here that it would be great if you upvoted for tracking. I have also noted that you hit this problem and would like this feature to work in our relevant Slack channels as well for consideration for the future roadmap. I've set up the following workaround using SQL Source Control + SCA and it worked, but I haven't tested it yet in an SCA "only"/ migrations first approach. It should also work there, however: 1) Filter out external tables in Visual Studio using a Filter.scpf file (documentation) 2) Create a re-runnable pre-deployment script that checks for the existence of the external data sources and tables and creates them as needed. A rudimentary code sample is here, I believe that you will also need to specify the Filter.scpf on your build step as well. Hope this helps! Thank you for letting us know about it here, finding out that more people want this functionality helps us a lot. Kendra / comments

Hi Godfrey,Apologies that you hit this issue. SQL Compare does not currently support external data sources / external tables. We have a user voice item here that it would be great if you upvoted fo...

0 votes

Hi all, A quick progress update on what @David Atkinson mentioned above (I'm combining two of his notes here). Unfortunately as an SSMS add-in SQL Source Control uses WinForms, which is a dated technology that doesn't lend itself working well in high DPI. We have plans in 2020 2021 to include a standalone SQL Source Control client to our portfolio, which will leverage more modern GUI components that should behave a lot better. This work is currently underway and we are close to having an early preview to share with folks. If you have a DPI issue with SQL Source Control which is blocking your work, please do open a support ticket with our team to get help finding a workaround or to see if we can make an adjustment to make this more workable for you. If you would like to join in on an early preview for the new standalone SQL Source Control client please email our team at databasedevops@red-gate.com. Cheers, Kendra / comments

Hi all,A quick progress update on what @David Atkinson mentioned above (I'm combining two of his notes here).Unfortunately as an SSMS add-in SQL Source Control uses WinForms, which is a dated techn...

0 votes

Thanks for the extra info, JTR. You can do this in TFS as well -- there is a "SQL Change Automation: Release" plug-in which has these components / is the equivalent of these PowerShell cmdlets, just in case you haven't found that yet. And yep, it's either filters or ignore additional. / comments

Thanks for the extra info, JTR. You can do this in TFS as well -- there is a "SQL Change Automation: Release" plug-in which has these components / is the equivalent of these PowerShell cmdlets, jus...

0 votes

Hi JTR, Have you checked out New-DatabaseReleaseArtifact and Use-DatabaseReleaseArtifact? New-DatabaseReleaseArtifact has an -IgnoreAdditional option which sounds possibly like what you're looking for, the description is: Specifies whether to ignore additional objects in the target database. Enabling this prevents new and unexpected objects in the target being dropped - note that the checks and tests applied to this database may not have taken those objects into account and may therefore not be valid. This parameter can't be used simultaneously with filter files. If you know what the objects are that you'd like to skip, personally I'd test using -FilterPath on New-DatabaseReleaseArtifact to specify a filter file of what to ignore first, it seems cleaner to me. The great thing about these cmdlets is that you can export and review the database release artifact to make sure it's doing exactly what you want before using it anywhere. Very nice when it comes to testing which option is right for you. Kendra / comments

Hi JTR,Have you checked out New-DatabaseReleaseArtifact and Use-DatabaseReleaseArtifact?New-DatabaseReleaseArtifact has an -IgnoreAdditional option which sounds possibly like what you're looking fo...

0 votes

I just raised a question with the folks over in the dev team to see if this is something that may have changed between your version (is it 3.0.1?) and the current 3.0.4 version. They are over in Cambridge so I may not hear back right away, but sometimes they appear when I least expect them! / comments

I just raised a question with the folks over in the dev team to see if this is something that may have changed between your version (is it 3.0.1?) and the current 3.0.4 version. They are over in Ca...

0 votes

Hi there, Interesting question! I did a few tests: I created a table named dbo.duplicate, and a table type named duplicate. Committed, ran sequence of build and deploy via powershell, they succeeded. Added a procedure, dbo.useduplicate, taking a parameter of the duplicate type as a TVP, and also using the type inline in the procedure in a writable way. Committed. Build and deploy still succeeding. Added a trigger onto the dbo.duplicate table, committed. Build and deploy still succeeded. The SQL I used is here. So something about our scenarios is different -- if you check out the SQL I'm using, any suggestion on what changes need to be made to reproduce your setup? I may just have misunderstood what you are describing. Thanks! Kendra / comments

Hi there,Interesting question! I did a few tests:I created a table named dbo.duplicate, and a table type named duplicate. Committed, ran sequence of build and deploy via powershell, they succeeded....

0 votes

That makes sense, thank you for clarifying, @PeterDaniels ! Coincidentally, I met with members of the SCA team just this morning to chat about clarifying concepts around baselining in our docs. One of my questions was, "are there times when we do NOT want to create a baseline, and what are those exceptions?" The list of exceptions I have as of this moment is: New project / greenfield development, when there is no production and we're just starting by adding objects as we go. There's really nothing to baseline in this case. When learning the SCA product for the first time, it can make sense for people to work a little with the migrations scripts first and get a feel for things first sometimes before diving into baselining (simply because there is some complexity with establishing an initial-baseline, and it can make sense to start with more daily-task style things). You have an interesting case, because you're somewhat in the middle of "new" development and "existing database". There is no production yet, but you've already got activity that's been completed in an integration environment so you aren't at the "starting from scratch point" either. (Just reiterating this for readers as the context gets lost sometimes.) So, which to do? If you choose to create a baseline from the integration environment (set it as your "target" on the baseline wizard, use an empty db as "development" as you describe in your first post here), you should be able to deploy from that to an empty database. I just re-ran through that scenario with the PowerShell cmdlets (gist of my simple script here) and it worked -- it will deploy the baseline to the empty database, and then apply incremental migrations on top of that. In this case, in TargetedDeploymentScript.sql there will be a line like "----------------- BEGIN INCREMENTAL MIGRATION: "Migrations\1.0.0-Baseline\001_20190204-1330_kendar.sql" ------------------" This will be followed by all the code from the baseline, then other migrations However, if the database you deploy to isn't totally empty --for example, if you create a user table in it (I created an empty one named "foo") -- that's when I wouldn't expect the deploy to work. SCA detects that the database isn't empty, so it doesn't want to lay down the migration script. Is it possible that this was the case in your initial testing? (The user objects in question could be getting sneakily created by accident by being in model, for example.) In this case, in TargetedDeploymentScript.sql, there will be a line like " PRINT '----- baselined: Migrations\1.0.0-Baseline\001_20190204-1330_kendar.sql (marked as deployed) -----';" The code for the baseline itself won't be in there. In my test, this caused the deployment to simply roll back when it hit errors, since I was using the default option to do it in a transaction If you do already have a lot of objects in the dev/integration environment, it might be useful to track down if this is failing in your case due to deployment target databases not being empty. In that case, I'd personally like to create a baseline, because it seems like a quite convenient way to encapsulate your starting point. As long as when you deploy to dev/qa/uat they are truly empty databases (or I suppose they could be a restored copy of your integration database, just as long as you are 100% sure the baseline doesn't need to be deployed), that should work, from my testing. As to what exactly is happening behind the scenes differently in the MSBuild task, I am not sure on that account. I believe the release artifact functionality may be more recent and may not yet be integrated into all plugins. / comments

That makes sense, thank you for clarifying, @PeterDaniels !Coincidentally, I met with members of the SCA team just this morning to chat about clarifying concepts around baselining in our docs. One ...

0 votes

Yay, glad you solved the case! One experiment I did: I tested putting an object in my model database, and then deployed using SCA to a database name that didn't exist. That worked fine, because in the compare check at the beginning it saw that the database just wasn't there. The object was created based on model and it still deployed the full baseline. Might not be useful if you want to configure the production database specially (files, filegroups, etc) in a way that isn't handled in the pre/post scripts, but just sharing in case it ever comes in handy. I'm currently working on some suggestions for how we can improve the docs about baselining overall, basically adding content that we don't have now that is more clear on what baselines are, when they are useful, and making it easier to get to existing content. Thanks for raising this issue, it's been very useful in terms of thinking through our docs and what would be helpful. / comments

Yay, glad you solved the case!One experiment I did: I tested putting an object in my model database, and then deployed using SCA to a database name that didn't exist. That worked fine, because in t...

0 votes

Hi Peter, The recommended pattern for starting is to create the baseline from your production database. Have you seen this article yet? Working with an existing database. Often the shared Dev database is not in sync with prod (either due to ongoing work or other issues), so prod is preferable to create the baseline. Just checking on this bit first before handing anything further because I'm not sure if you mean in your case that there is no prod yet / new development and ONLY a shared dev database? Kendra / comments

Hi Peter,The recommended pattern for starting is to create the baseline from your production database. Have you seen this article yet? Working with an existing database.Often the shared Dev databas...

0 votes

Hi Pete! I saw that you posted this in the SQL Server community slack channel as well, and I've responded there. No problem at all posting in both, just wanted to update you here that messages await you there [image] / comments

Hi Pete! I saw that you posted this in the SQL Server community slack channel as well, and I've responded there. No problem at all posting in both, just wanted to update you here that messages awai...

0 votes

How can we help you today?

Kendra_Little

Comments