Comments
Sort by recent activity
Hi Greg, Apologies in advance, you've asked a question about one of my favorite topics, so I'll probably write way more info than you actually want [image] I would do this in a pre-deployment script in a rerunnable format, something like what is in the gist here: https://gist.github.com/LitKnd/ae231bcd669a9a6c2cc23aacc0429974 Note: i believe the CURRENT syntax for alter database is 2012+ only, so you may need a variation if you're on a lower version. As soon as you allow snapshot isolation, SQL Server will begin creating versions in tempdb for modified rows, and an extra 14 bytes becomes needed on the rows themselves in the database. For existing production databases where performance matters, I recommend releasing the ability to allow snapshot isolation in its own change window, before you actually begin using it. This allows you some time to observe whether or not the row versioning itself has any negative effect before you add in any changes to actually use the snapshot isolation. Part of the reason for this is that if you have connections running SET TRANSACTION ISOLATION LEVEL SNAPSHOT and then you change the database setting so that snapshot isn't allowed, those connections are going to start getting an error when they try to run queries. So it's worth first enabling the setting and letting that burn in for a bit before you start using it -- just simplifies troubleshooting if anything looks weird. I'm a huge fan of snapshot isolation, please do discuss and follow up if you'd like more info! Kendra / comments
Hi Greg,Apologies in advance, you've asked a question about one of my favorite topics, so I'll probably write way more info than you actually want I would do this in a pre-deployment script in a ...
Nice! Yeah, that sounds like a scenario where it will really shine. Glad to hear you love the feature, it's a favorite of mine and can be so powerful! For an Azure VM that didn't have great disk under tempdb, I might still test out Snapshot Iso, TBH, would just use a little caution. The versioning overhead can vary quite a bit, and might be quite small if there aren't a ton of updates and deletes, or if updates and deletes happen against relatively narrow rows. For inserts, it generally doesn't need tempdb (no previous version of the row to capture), and it's also generally smart enough to not version off-row LOB data if you have updated other columns on the row. So the tempdb impact is quite small for many applications depending on the write pattern. I just always figure the time I don't mention it is the time it's likely to be a big deal, hahaha. Anyhoo, cheers! Have a great weekend. / comments
Nice! Yeah, that sounds like a scenario where it will really shine. Glad to hear you love the feature, it's a favorite of mine and can be so powerful!For an Azure VM that didn't have great disk und...
No problem, and makes sense. For excluding filter file from build, it would help to know the exact combo of tools that you are using so that I can set up a repro. Are you using SQL Source Control for the state first version-control tie in, and then using SCA to deploy? Or are you using SQL Change Automation for the whole thing (migrations first approach to version control)? My memory of testing this was actually that you had to specify the filter path on the build to use a filter file at all (and that it didn't carry through filters set up in source control), but it may be that I'm mis-remembering or was using a different product combo than you are, so I'd like to re-check real fast with your setup. / comments
No problem, and makes sense.For excluding filter file from build, it would help to know the exact combo of tools that you are using so that I can set up a repro. Are you using SQL Source Control fo...
Hi JTR, Is it possible for you to put the temporary objects into their own schema, and to have your filter file ignore that schema? I've used this approach in the past for objects like that which needed to be in the database itself rather than tempdb, due to needing to persist through a restart, etc. Putting them in their own schema helped make it clear that they didn't need to be checked into source and also made it easy for operations folks to identify, plus it simplified our cleanup scripts should some of them be orphaned. I am personally more of a fan of filter files than I am of -IgnoreAdditional, just because you're explicitly saying where you don't mind drift with a filter file pattern, and -IgnoreAdditional could allow unintendend drift to creep in. Kendra / comments
Hi JTR,Is it possible for you to put the temporary objects into their own schema, and to have your filter file ignore that schema?I've used this approach in the past for objects like that which nee...
Hello! Great question. One quick note: you're in the "discontinued and previous versions" section under DLM Automation, I am not sure if you meant to be in the SQL Change Automation section? With the migrations-first approach in SQL Change Automation, you are able to put multiple projects in a single Visual Studio solution if you wish. However, personally, my experience is that it is valuable and important to de-couple your database changes from your application changes when it comes to important. A simplified model is:
First, deploy initial database changes to "stage" the scene, but do not change customer-facing functionality. Build and deploy these independently, verify they are in place. (Example: add a column that will be used later, but it's not used yet.) This may be multiple changes rolled through the pipeline over time.
Later, deploy application changes that add the feature, but include a "feature flag" which determines whether the feature is visible to customers or not. Again, this may be multiple changes.
Eventually turn on the feature flag, and expose features to the customer
There's a lot of benefits to decoupling the changes this way: if you need to do a rollback, it's quite easy (simply turn the feature flag off). And the design of staging incrementally allows you to make minimal risk incremental changes. Is your question also about building databases with cross-database dependencies, such as a view in one database which references external databases? If so, I can speak to some approaches for that as well, just not sure about the scope of your question. Hope this helps, Kendra / comments
Hello!Great question. One quick note: you're in the "discontinued and previous versions" section under DLM Automation, I am not sure if you meant to be in the SQL Change Automation section?With the...
Hey, Frens! Just want to second Roseanna's mention that please let her know if you have any requests for learning materials:
Is there a devops or automation related topic you'd like to start presenting on, but need help getting going?
Are there tools that we've got that seem awesome, and you'd like to learn about them, but not sure where to start?
Since starting at Redgate seven months ago, I've gotten to come up to speed on a lot of our products that I didn't know well before, so the odds are very good that I can help get you started if you want to learn new things and start sharing them. Kendra / comments
Hey, Frens!Just want to second Roseanna's mention that please let her know if you have any requests for learning materials:
Is there a devops or automation related topic you'd like to start present...
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...
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...
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...
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...