User profile for way0utwest

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

What is your source for the Jenkins package? Are you using SQL Source Control or SCA in Visual Studio to do this? The SCA project should produce a set of all migration scripts, but also a package of just changes, based on the target db being set to the downstream environment. / comments

What is your source for the Jenkins package? Are you using SQL Source Control or SCA in Visual Studio to do this?The SCA project should produce a set of all migration scripts, but also a package of...

0 votes

If you're using SQL Source Control, the output of the build is the entire repo, because this is the state of the database. Once you use an SCA cmdlet to produce a deployment package that is just the changes. / comments

If you're using SQL Source Control, the output of the build is the entire repo, because this is the state of the database. Once you use an SCA cmdlet to produce a deployment package that is just th...

0 votes

How would you identify this? I'm guessing you have a value in a parent tableof the PK(s) that you need to delete, but want those removed, along with cascades. Is there a reason you can't turn on cascading deletes? / comments

How would you identify this? I'm guessing you have a value in a parent tableof the PK(s) that you need to delete, but want those removed, along with cascades. Is there a reason you can't turn on ca...

0 votes

We don't. Data Compare won't do this unless you select all the child tables, and had the data already removed. You can certainly turn on cascading deletes and then run your delete, and then disable this. Is there a reason you can't do this? Or maybe I misunderstand your issue. What is the "junk data"? Is it all data? / comments

We don't. Data Compare won't do this unless you select all the child tables, and had the data already removed. You can certainly turn on cascading deletes and then run your delete, and then disable...

0 votes

How are you doing the deployment? Are you seeing this folder discrepancy when running the PoSh from a cmd window? / comments

How are you doing the deployment? Are you seeing this folder discrepancy when running the PoSh from a cmd window?

0 votes

How can we help you today?

way0utwest

Comments