Comments
Sort by recent activity
Hi there, Not sure if this bit is clear or not -- Azure SQL Managed Instances simply don't allow you to put the database into restricted user or single user mode. This is one of the limitations of Managed Instances. We don't have a way to work around that because Microsoft doesn't allow it to happen. Generally I would recommend that people work to be able to run deployments online without killing off all other transactions when possible. Is this an option in your case? If not, I would be curious to know what the specific pattern you have which prevents it -- there might be a more graceful way to work around the problem. If you do wish to kill off all active transactions, one workaround you may find suggested on the internet is to find another statement which supports ROLLBACK IMMEDIATE (Microsoft refers to this as the termination clause), which is supported on Managed Instances and to use that other commands and to turn that option on and then off. A list of commands that support the termination clause is here. However, this is definitely a hack and I would be wary of this approach. I personally have sometimes experienced a deadlock on some "ALTER DATABASE" commands which weren't pleasant to handle, and you can't put multiple ALTER DATABASE commands inside a transaction. So if you did hit a weird deadlock on the second command, you could end up putting the database into an odd state. I know this isn't a complete answer, but hoping to get more info on why you aren't able to do online deployments. Kendra / comments
Hi there,Not sure if this bit is clear or not -- Azure SQL Managed Instances simply don't allow you to put the database into restricted user or single user mode. This is one of the limitations of M...
Thanks, Peter. Andrew on our dev team has recorded an item related to this on our status board which prevents us from forgetting about it, too [image] We are continuing to think about how which source control elements are key to put in our tooling's UI, while not trying to completely recreate a Git client -- because there is so much functionality you can put in a Git client that it fairly quickly becomes overwhelming. It's definitely a balancing act. I'm sharing this post to our design team internally as well, as I think they'll find the info on this valuable for future product planning. Thanks again for all your feedback, it is very valuable to us! Kendra / comments
Thanks, Peter. Andrew on our dev team has recorded an item related to this on our status board which prevents us from forgetting about it, too We are continuing to think about how which source con...
Hi Peter, I suspect that documentation page intended that "uncommitted changes" include changes in the database which hadn't been imported yet, but I can see how that wording is unclear. I'll follow up on that (I'm out all next week so it may be a little while before I complete it). I'll also chat with the team and propose that we move the "Create new branches" section at the bottom up to the top of the page and give a little more advice about how using feature branches makes it easier to work with Git. My preferred methodology with Git is to have each developer working in a feature branch:
Import changes from your database to your feature branch as you are ready. Push your feature branch up to the central repo as often as you like as a way to back up your changes from your local machine -- - optionally building these branches whenever they are pushed if you like
If your feature branch needs to live longer than a day, do at minimum a daily pull from master and merge into your branch
When your changes are ready to go into a main line, use a pull request to merge in
Effectively, most folks find it more straightforward to always merge changes into a branch when using Git, rather than to have shared branches where you have people pushing and pulling changes into the same active branch. I find that the Azure DevOps Release Flow model often work for people when they are getting started as it is not overly complex, but some people still prefer the older Git Flow model which maintains a separate develop branch. Hope this helps! Kendra / comments
Hi Peter,I suspect that documentation page intended that "uncommitted changes" include changes in the database which hadn't been imported yet, but I can see how that wording is unclear. I'll follow...
Hi Peter, Thanks for reporting this. I've seen this issue before, and I was able to resolve it by manually deleting the shadow database (with the option to close existing connections when I do so). That is a workaround and not something we want users to have to do regularly, so if this issue persists after yesterday's release, we should look into it. I'll keep an eye out for it as well. Kendra / comments
Hi Peter,Thanks for reporting this. I've seen this issue before, and I was able to resolve it by manually deleting the shadow database (with the option to close existing connections when I do so). ...
Update: I did a quick livestream here which I hope answers your question: https://youtu.be/Y56X9H4XG08 The demo there is for working with an existing project in SSMS, but the workflow is quite similar to what you would do in visual studio. The TSQL I used to create simple objects to test the filter is: CREATE ROLE DoYouSeeThisRole; GO CREATE USER DoYouSeeThisUser WITHOUT LOGIN; GO --DROP ROLE IF EXISTS DoYouSeeThisRole; --DROP USER IF EXISTS DoYouSeeThisUser; / comments
Update: I did a quick livestream here which I hope answers your question: https://youtu.be/Y56X9H4XG08The demo there is for working with an existing project in SSMS, but the workflow is quite simil...
Hi @CAT_Keith, Couple of questions for clarification -- Are you using SQL Change Automation in SSMS, or Visual Studio? And is this a new project you are setting up, or an existing one where you have already baselined, and you want to add the filter later? (I am thinking perhaps the second from the context of your question but I'm not 100% sure.) Thanks, Kendra / comments
Hi @CAT_Keith,Couple of questions for clarification -- Are you using SQL Change Automation in SSMS, or Visual Studio? And is this a new project you are setting up, or an existing one where you have...
Hi fforgoso , The best way to do this is to create multiple deployments. Each deployment can contain the exact combination of changes that you would like. There are a couple of different techniques you could use to selectively split migrations (of any kind) into multiple deployments. Are you currently using a practice of branching and merging? If so, that type of strategy helps you control the flow of changes into your release pipeline. An example is described here: https://littlekendra.com/2020/01/10/why-i-like-the-release-flow-branching-strategy-with-git-for-database-devops/ Hope this helps, Kendra / comments
Hi fforgoso ,The best way to do this is to create multiple deployments. Each deployment can contain the exact combination of changes that you would like.There are a couple of different techniques y...
Hi Ben, Some first places I would look... Is it possible some code was accidentally deployed to the model database and created that schema there? If you're using a dynamically named database to build (you're not specifying the name and it automatically creates one with a guid-like name), when SQL Server creates the db it will automatically have what's in model before any code is deployed and it could cause this issue. If that's not it, could some code have been added to a pre-deployment script that is doing this? Hope this helps, Kendra
/ comments
Hi Ben,Some first places I would look...Is it possible some code was accidentally deployed to the model database and created that schema there? If you're using a dynamically named database to build...
Hi @Melissa7 , If you upgrade the plugin past version 4.0.19288, you get more control over this behavior. Prior to this version the SCA extension would always open in SSMS. On this version and in later versions, the SCA extension will only open by default if you had it open when you last closed SSMS -- so if you close the tab when you are done using it, it won't automatically load when you next open SSMS. Hope this helps! Kendra / comments
Hi @Melissa7 ,If you upgrade the plugin past version 4.0.19288, you get more control over this behavior. Prior to this version the SCA extension would always open in SSMS. On this version and in la...