User profile for way0utwest

Here's a relatively short answer. I have a longer one in draft and there also is this short description: https://www.red-gate.com/library/redgates-database-lifecycle-management-approaches-a-comparison First, in either method, branching can be problematic. You want those feature branches for db changes to be as short as possible if there is the chance of conflict. We dont' want anyone building on bad work, so this is a tradeoff, but I'd suggest as soon as you're confident of a change, merge right away and re-pull a branch if you have other things to do. Now, SQL Source Control (SOC). I like this for simplicity and additive, easy changes. Meaning no data manipulation, no renames, merges, splits. Those are a hole in the state/model/comparison approach. You've seen this with SQL Compare, which powers this process. If you're here, this works well. The migration scripts in SOC are flaky and since you can't control when they run. If you have schema changes depending on these, you can have issues. I would only use these as idempotent, run once scripts and then only if I can assume that order of execution doesn't matter. Right now there is no pre-post, but I'm hoping this will be added at some point. No news to share, but I really think this solves a lot of SOC issues, especially for the post scripts. For SQL Change Automation, SCA. This is a proven method that's been used for a long time, and it works well, but the branch thing applies more, because merges across branches are a pain. Chronology matters in migration scripts, so whenever you try to merge these, you need to not merge the code, but sort out the ordering and potentially rename scripts to control execution. At any scale of team members and changes, this is a pain. It's less if developers keep informed of other changes, say at a daily standup, and potentially rename/number their scripts early on. You may still need to idempotent architect the scripts if you get "fixes" in production that might slip by your process. If not, then anything going to prod needs merging into child branches quickly, potentially necessitating rewriting code or renaming files. The other issue I have with SCA is that I need to watch the things happening in Dev. If I add a column to a table that has 1,000,000,000 rows in prod and move data, then do some stuff, then delete that column. I DO NOT want that flowing to prod. With merges, I might need to make sure I eliminate that script, which also means I'm trying to unwind something that happened in development manually. That can be problematic with numbers of scripts. The big thing here is to watch the "undo" stuff that developers might do and ensure that they don't necessarily just add the undo migration, but really think about wiping out the earlier scripts. Depending on your flow, this creates complexity. I wish that a bunch of migration scripts gave me a good summary of what's coming. You can get this in the release testing if you flow your changes through the SCA automation process, which may help you summarize the changes and potentially go back to unwind and clean up some scripts that make and undo a change. Final word from me. I think migrations/SCA is superior to lowering risk and covering lots of scenarios. It's not perfect and there is a higher burden on the developer to capture and manage the change scripts, as opposed to more the DBA, Ops side of DevOps with SOC. However, it covers my scenarios of any change needed better. I would slightly change this if post scripts appear in SOC, but I still prefer SCA. / comments

Here's a relatively short answer. I have a longer one in draft and there also is this short description: https://www.red-gate.com/library/redgates-database-lifecycle-management-approaches-a-compari...

0 votes

Smo will allow you to do this. I assume you're doing some looping and following a pattern in C#. There are classes to connect to tables and alter them as needed. You can start with the Table.Alter method: https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.table.alter?view=sqlserver-2016 / comments

Smo will allow you to do this. I assume you're doing some looping and following a pattern in C#. There are classes to connect to tables and alter them as needed. You can start with the Table.Alter ...

0 votes

The same conflict of the image you posted above with Prompt or something else? / comments

The same conflict of the image you posted above with Prompt or something else?

0 votes

I don't see this, running these versions: VS 2017 - 15.6.2 Prompt 9.2.5.6073 SCA - latest, not sure how to check this but no updates listed. / comments

I don't see this, running these versions:VS 2017 - 15.6.2Prompt 9.2.5.6073SCA - latest, not sure how to check this but no updates listed.

0 votes

I'd be curious to see the logs with timing. Where is this slow? / comments

I'd be curious to see the logs with timing. Where is this slow?

0 votes

That's the place. The pre-script is what builds the shadow (and normal) db. Put things in there that you need done, database scoped config, multiple filegroups, etc. / comments

That's the place. The pre-script is what builds the shadow (and normal) db. Put things in there that you need done, database scoped config, multiple filegroups, etc.

0 votes

A little, busy, but let me respond to #1 for now. I get your point with VCS. We do get merge conflicts, and we catch things. The problem with checking the cache objects only is twofold. 1. The comparison engine (which is also the basis for SSDT, and some other competitor objects) can't do that. It needs a full dependency tree to work, which can be off because of changes not made on this instance, so that's most of the time. Re-reading the local file system is also slightly slow for comparison, though arguably there might be shortcuts that can be taken to check DateModified, names of objects, etc. The issue for trying to catch things in the VCS occurs with related objects. Let me give you an example. 2:00 Dan changes the proc to include a NoLock (please don't do this, Dan) 2:01 Sally opens the proc in SSMS. She has the old versions 2:02: Dan commits the proc to SVN 2:03 Sally changes the proc to add a TRY..CATCH around an update 2:04 Sally tries to commit (or push in git) - gets merge conflict. This is easy. Cache would make both commits quick, though Sally's check would be longer because there would an actual comparison between the SVN file and the object code from the server. What about this: 2:00 Dan changes the proc to include a NoLock (please don't do this, Dan) 2:01 Sally opens a table referenced in the proc in SSMS. Removed a column being used. 2:02: Dan commits the proc to SVN. I just check the cache, see no changes to worry about. 2:04 Sally tries to commit (or push in git) - works fine Downstream things are broken. Obviously CI catches this, deployments will catch this, but imagine it's dependent logical changes. Those are a problem We want to let you know that there are issues. Having a cache can help, but the cache can be really out of dates, especially in the cases where SSMS might die. We'd have to have a transactionally consistent cache. That being said, while anyone can mess up any tool, our job is to minimize the places where the tool could lead you to make mistakes. I would love to get more resources to fix some of these things in SOC, and I regularly complain, but I have limited success. I continue to push, but having more Uservoice votes for things would be good. I'll pass this along, but not confident I'll get anything fixed anytime soon. There is a lot of legacy code, and I suspect a cache based system might be really difficult to implement. As a side, my vote was to write this in a local log file, as a key-value store. If I commit, remove the items committed. If I get latest/pull, remove items as well. If I detect a change, add an item.That's slightly slower, but gives me a physical list that I can also troubleshoot instead of a cache that gets wiped on process end. Re #3 - A search by whatever would be nice. Just type "Sales" or "Dan" and get a filtered list in the changes dialog. One thing I can suggest is press the sales side for these changes. The more voices, the more chance I can convince someone to allocate resources. / comments

A little, busy, but let me respond to #1 for now.I get your point with VCS. We do get merge conflicts, and we catch things. The problem with checking the cache objects only is twofold.1. The compar...

0 votes

I can't quite speak for the team, but I've been working with SQL Source Control for years, and I feel quite a bit of pain. I've had similar issues, and discussions about these items, so let me try and explain a bit. Some of this might be different for different SSMS versions (and Source Control, SQL, etc.) #1 - I've debated this and talked deeply through the cache items. The blue dots are really a note of an event. SQL Source Control (SOC) detects a change and adds the blue dot. This is a polling process, but if you undo a change or someone else changes something, your SSMS doesn't know something changed. At least not in the moment. The same argument goes with I've changed proc x and it's marked. That's in my cache.Now another change occurs to a table referenced by proc x, perhaps removing a column that is referenced. Without a check, my SOC doesn't know this has happened. We commit a change, potentially seeing or not thinking the table change is related, and we have problems. As much as I'd like to say "don't recheck the repo v database", I also know this would likely cause no shortage of complaints and issues, especially in shared environments. Imagine SSMS crashes, which we have to account for. If you then go to commit, are we sure the cache is correct? This is really an issue. Ideally we'd be able to read an XE session of all changes that have occurred, but that's a really hard problem. Conflicts become even more of an issue with shared work. Multiple developers could end up committing the same work. I wish this were simpler,but it's not, and databases are similar to, but vastly different than app code (C# ,Java, etc). I agree with you we ought to do something, but every time this gets tackled, we struggle with finding a solution that actually protects code and scales to multiple developers. If you always work dedicated, I think this an easier thing to solve, though even then you'd have to say only SSMS can make changes. #2 - SSMS is a piece of manure in some ways. We're hacked in there and have some limits in what we can do with the UI. That's part of the issue, though I'd like to think that what we ought to do here is disconnect from OE. Just have a drop down and force you to do that every time. We've had some UI feedback that people like the "current" database to be the one SOC finds, but over time I think this is bad. I'd rather explicitly select this. I have little confidence I could make this change work, but if you can rally more votes - https://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/35143396-disconnect-the-soc-pane-from-object-explorer #3 - filtering by user would be nice, for sure. The problems this introduces are that potentially you are committing work that is incomplete and problematic. I get that you want to say "I only want to commit change x", but think if changes y and z are related/dependent? In a shared environment especially, this is dangerous. When teams work, they conflict more often than they realize, and one reason that the object locking was highly requested early on. Teams just don't communicate enough. I think the UI to easily/quickly limit objects in some filter isn't necessarily easy, but I do think that perhaps a "search" feature for commits might eb nice, allowing me to see specific changes and commit those. The hard part of implementing this is that I search for "Sales" and click the Sales table. However, I want to also commit "GetWeeklyTotals" and search for that. Keeping a running list along with a changing list of selected items is likely a larger UI change than anyone wants to make. That being said, if you wanted to propose a fix like that, I could be convinced to vote. The hard part here, and why I think a shared database with source control is a problem, is that you are potentially making changes, as are others, and until you commit, it's possible someone else will overwrite your change. It's also possible that they won't realize that their change later might conflict with yours if they can limit filter too many things. / comments

I can't quite speak for the team, but I've been working with SQL Source Control for years, and I feel quite a bit of pain. I've had similar issues, and discussions about these items, so let me try ...

0 votes

Confirmed this is a bug. / comments

Confirmed this is a bug.

0 votes

So, if you look in the folder where you've specified for the release, there should be an Update.sql file. When I run this in TFS/VSTS/Octopus, this is what is generated as a set of artifacts. You can also run this from the command line, using the parameters for your system and look at the ouput. I assume you have a build, correct? The output of the build should be a nuget of all your vcs. In my release folder, the folder that I have specified as the location for the package , here's what I see: [image] / comments

So, if you look in the folder where you've specified for the release, there should be an Update.sql file. When I run this in TFS/VSTS/Octopus, this is what is generated as a set of artifacts. You c...

0 votes

How can we help you today?

way0utwest

Comments