User profile for way0utwest

Try a -Force here. I think that will help, but not in a place to test, and everyone in the UK is gone for the weekend. Can you check the version of SCA on your build server? / comments

Try a -Force here. I think that will help, but not in a place to test, and everyone in the UK is gone for the weekend. Can you check the version of SCA on your build server?

0 votes

There is a need to create and destroy databases for sure. The "Build" actually wants to run the pre, which does contain a create db, and run all the code to ensure it compiles correctly, as that's the only SQL Server compiler we have. In terms of using a test instance, we usually go with SA, but I suspect that you could get by with: dbcreator, which hopefully would give you db_owner in the build db and the shadow db. For the release part, you need rights to read the target db. Note that builds are not usually to a specific name, unless you are sure there aren't collisions with other developers. Since we may want concurrent builds from checkins at similar times, the build task will generate a random name. / comments

There is a need to create and destroy databases for sure. The "Build" actually wants to run the pre, which does contain a create db, and run all the code to ensure it compiles correctly, as that's ...

0 votes

You can also use a repo for the snippets folder. I have mine as a git repo, so that if I change things, I can commit and store in VCS, pulling down on other machines. / comments

You can also use a repo for the snippets folder. I have mine as a git repo, so that if I change things, I can commit and store in VCS, pulling down on other machines.

0 votes

Yes, the hosted agent needs to install the plugins. The hosted agent is rebuilt each time from scratch and doesn't include the SCA cmdlets. / comments

Yes, the hosted agent needs to install the plugins. The hosted agent is rebuilt each time from scratch and doesn't include the SCA cmdlets.

0 votes

confirmed in Prompt v.9.2.1.5713. This is a valid query hint https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017 / comments

confirmed in Prompt v.9.2.1.5713. This is a valid query hinthttps://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017

0 votes

No, the SCA projects really use the migration scripts. The set of schema items is really for you to view, but the changes are in the migrations. If you make simple schema changes, this might work, but I think there are some missing items that preclude this. I'll ask the question, but I think the idea is that this won't quite work. / comments

No, the SCA projects really use the migration scripts. The set of schema items is really for you to view, but the changes are in the migrations. If you make simple schema changes, this might work, ...

0 votes

Whoa, that's cool. And also, #$%##@ developers. I didn't see an update for when this was going live. I'll have to give it a go as well. Let me know if this helps or you have more questions. If they're related, post here. If not, start a new thread. / comments

Whoa, that's cool. And also, #$%##@ developers. I didn't see an update for when this was going live.I'll have to give it a go as well. Let me know if this helps or you have more questions. If they'...

0 votes

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

How can we help you today?

way0utwest

Comments