User profile for way0utwest

Incredibly different than .NET. Here are some thoughts, mostly distilled from experience (pre-SCA, DLM) and from working with some clients. First, branching is problematic in databases, mostly because chronology matters. The order of operations for single objects matters, but also potentially across objects (dependencies, name resolution, etc.). Therefore we need to manage the order of migration scripts. Second, switching branches for a state based entity, like a table, is an issue. This is because we may have holes (columns /tables removed in the new branch) or buckets (columns added in the new branch) and we have to decide what to do with data. How do we populate or save the data. Switching back is a bigger issue. Therefore, branches for databases need to be super short lived. We limit .NET branches to 1 week, but I'd really limit database branches to 1 day or less. Otherwise, the changes made by other developers can grow to be unmanageble in merges. My advice here would be this. FB1 -> commit triggers FB1 build. -> failure, let developer know 0-> success, initiate PR for dev branch. on success, close branch, re-initiate, with new db build if new work needed. FB2 -> commit triggers FB2 build. -> failure, let developer know 0-> success, initiate PR for dev branch. Dev PR approved, meaning code review -> triggers integration build -> failure, notify developers to resolve potential merge conflicts. This could be altering the FB1 or FB2 commit or renaming/renumbering migrations scripts. Must trigger new PR/DevBranch build. --> success, scripts now are fairly immutable. I say fairly, as there might be a need to cherry pick commits from DevBranch-> Master for release. Why? Perhaps I need an index rebuild moved before/after other changes. Or I want a risky change separated. When I move from DevBranch -> Master, I pull over (merge) certain changes as needed to move inflight work to a release. Personally, I think we should really develop on master, with branches pulled for features AND releases. At points in time we create a release branch, potentially chrry picking, but we know which branches give us a point in time review of either work being done (short lived) or release state (medium lived). This potentially lets me go back to a release branch for a hot fix, while there are still in-flight changes in master. Does this make sense? I think you need a second CI build to manage this. / comments

Incredibly different than .NET.Here are some thoughts, mostly distilled from experience (pre-SCA, DLM) and from working with some clients.First, branching is problematic in databases, mostly becaus...

0 votes

Ah, that makes sense. What version of SCA do you have? I assume you see the spinning arrows when clicking Refresh, but does the cursor not go to a waiting image when the Import and Generate is clicked? I wouldn't expect that for this part of the actions. If this a large procedure? This should be a simple scripting once the change is detected. / comments

Ah, that makes sense. What version of SCA do you have? I assume you see the spinning arrows when clicking Refresh, but does the cursor not go to a waiting image when the Import and Generate is cli...

0 votes

Thanks, I'll ask about this. I've got 3.0.18 as well. The generation should be very quick / comments

Thanks, I'll ask about this. I've got 3.0.18 as well. The generation should be very quick

0 votes

I'm slightly confused, but maybe you can help. When you say modestly sized, I assume you have a number of tables, views, procs, etc. When you say the import and Generate Scripts take a long time to complete, I assume you mean that only one object changed, so why was this taking so long? If that's right, the reason is that we need to build a dependency tree. You change one proc, but does that cause other issues in a chain? When we generate a script, we need to determine not only what changed, but what the potential issues are. If you're changing one procedure, that's usually all that's affected but we can't know that, especially if the change is from another client SSMS/VSCode/ADS. So we walk a tree to see what's changed and then need to compare that to the existing view to capture the change. At times generating the script is non-trivial, and it can be hard to do this accurately without doing a full compare. / comments

I'm slightly confused, but maybe you can help. When you say modestly sized, I assume you have a number of tables, views, procs, etc. When you say the import and Generate Scripts take a long time to...

0 votes

Can I ask what changes you're making here? Specifically? Adding a column ought not to do this, but if you are doing other things, this might be an issue. There's no magic to gettting close to zero downtime. We are bound by the rules of SQLServer, and what we want to do with RG tools is make it easy to do what you'd do manually. Recommending a temp table is sometimes good, sometimes bad,but I'd have to know more here about what to do. However, you need to understand the way that your system works and what method for making changes is apporpriate. I do think that some of the more complex changes are better handled with pre/post scripts, and I'm glad these got added for this reason. I also like the SQL Change Automation client in VS, as this gives me complete control and replays the changes in the same way for downstream environments. This lets me control the way we make changes, as I might tackle different column adds in different ways, depending on the table. You won't get zero downtime. You can get very close, and a table rename is often the quickest way. You can front this with a view as well, and recompiling the view to point to a new table is often even quicker, but there are always some locks being held. Here I'd actually do this if I needed the temp table for the Sales table. 1. Make dbo.Sales2 as the new table, with new schema 2. Copy data from dbo.Sales to dbo.Sales2 (this could be one step 3. Rename dbo.Sales to dbo.Sales_Old 4. Rename dbo.Sales2 to dbo.Sales 5. cleanup any data added between step 2 and 3. If you try to make this one transaction, you won't get zero downtime. If you are trying to add a column and populate all rows with data in the same step, you won't get zero downtime. Actually, you don't get downtime, but blocking. If you provide more information, we can help determine how to attack this problem. / comments

Can I ask what changes you're making here? Specifically? Adding a column ought not to do this, but if you are doing other things, this might be an issue.There's no magic to gettting close to zero d...

0 votes

Apologies, the target is really a release term. That's where you pick the end database. For build, you need a temporary place to compile the code. The temporary server is where the code would run. This needs to support TRIM() [image] / comments

Apologies, the target is really a release term. That's where you pick the end database. For build, you need a temporary place to compile the code. The temporary server is where the code would run....

0 votes

What's the build target? The only compiler the build process has is the SQL Server engine. If you've targeted LocalDB or a version that doesn't support TRIM(), then things might not work. / comments

What's the build target? The only compiler the build process has is the SQL Server engine. If you've targeted LocalDB or a version that doesn't support TRIM(), then things might not work.

0 votes

The developers are aware this is an issue and they're looking into it. No word on when this might be fixed, but I'm hoping soon. / comments

The developers are aware this is an issue and they're looking into it. No word on when this might be fixed, but I'm hoping soon.

0 votes

Is there broken code after the area you're trying to format? If I get my code into a batch, it seems to format. If you have an example of the code for me to test, I'd be happy to. I have 9.2.7 on this machine, but 9.2.9 on another. / comments

Is there broken code after the area you're trying to format? If I get my code into a batch, it seems to format. If you have an example of the code for me to test, I'd be happy to. I have 9.2.7 on t...

0 votes

I've noticed this as well. I've sent a note off to see if this is a fundamental behavior change or a bug. I have been able to get this to work by putting a "GO" before my statement/query and then formatting it. / comments

I've noticed this as well. I've sent a note off to see if this is a fundamental behavior change or a bug. I have been able to get this to work by putting a "GO" before my statement/query and then f...

0 votes

How can we help you today?

way0utwest

Comments