User profile for AlexYates

That is not how SQL Source Control is designed to work. It sounds like what you want is DLM Dashboard? www.red-gate.com/products/dlm/dlm-dashboard Technically, there are things you can do. For example, you could write a script to run SQL Compare to sync a Dev database with a scripts folder and check those scripts into source control. Then you could run that script on a schedule or following a DDL trigger. However, I've not yet seen a scenario where that's been a wise choice. / comments

That is not how SQL Source Control is designed to work. It sounds like what you want is DLM Dashboard?www.red-gate.com/products/dlm/dlm-dashboardTechnically, there are things you can do. For exampl...

0 votes

No probs. Good luck and happy deployments! / comments

No probs. Good luck and happy deployments!

0 votes

SQL Change Automation has two main components: 1. A Visual Studio Extension (previously called ReadyRoll) which is an alternative format for source controlling your database changes to SQL Source Control. The Visual Studio extension creates a project based on migration scripts rather than a desired end state (like SQL Source Control). When you deploy this package against a target database only the required scripts are run. This sounds a bit like what you want. <a rel="nofollow" href="https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/sql-change-automation-projects" title="Link: https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/sql-change-automation-projects">https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/sql-change-automation-projects</a> 2. A set of PowerShell cmdlets that can be used to automate the deployment of either a SQL Source Control project or a SQL Change Automation Visual Studio Extension project. If deploying a SQL Source Control project you can create a "DatabaseReleaseArtifact" which contains, amongst other things, a generated upgrade script that can be used to deploy a given version from source control to a given target database. This upgrade script can be eyeballed, tested and documented in advance however you see fit before deployment. This also sounds a bit like what you want: <a rel="nofollow" href="https://documentation.red-gate.com/sca3/reference/powershell-cmdlets/new-databasereleaseartifact" title="Link: https://documentation.red-gate.com/sca3/reference/powershell-cmdlets/new-databasereleaseartifact">https://documentation.red-gate.com/sca3/reference/powershell-cmdlets/new-databasereleaseartifact</a> So using either SQL Source Control or SQL Change Automation Visual Studio Extension project types for development, combined with the SQL Change Automation PowerShell cmdlets for deployment, you can generate your upgrade scripts in advance, store them however you wish and automate deployment. The question is whether you like the SQL Source Control or the SQL Change Automation Visual Studio Extension project type for development and the way each gets deployed by the PowerShell cmdlets. Please correct me if I'm wrong, but I think you are asking to combine the two project types because you prefer the development experience of SQL Source Control, dealing with desired end states in SSMS, but you prefer the SQL Change Automation Visual Studio Extension project format for deployment, and you are looking for a best of both worlds solution. My advice is that you are making life more complicated for yourself by combining both project types. If I were you I would accept that there a pros and cons of either project format, but that either is better than a complicated hybrid of the two. The folks at Redgate would urge you to consider switching to the SQL Change Automation Visual Studio Extension project type for CI and CD use cases - but to make that switch you need to consider whether you are happy to drop SQL Source Control. If you would like to spend 30 minutes discussing the pros and cons in more detail let me know. We offer a free 30 minute consultation to everyone. After that we can discuss rates if that's what you want. <code><a rel="nofollow" href="http://www.dlmconsultants.com">www.dlmconsultants.com</a> / comments

SQL Change Automation has two main components:1. A Visual Studio Extension (previously called ReadyRoll) which is an alternative format for source controlling your database changes to SQL Source Co...

0 votes

+1 :-) / comments

+1 :-)

0 votes

However, to answer your question more directly. Consider using a "release branch". For example, in Vincent's "GitFlow" model feature 1 and 2 would have been merged to develop. Then a release branch could be created and the testers would work on the release branch until they are happy to sign it off. Then they would merge it to master and deploy to production. Of course, you might prefer to develop your own strategy that does something similar. Or not. / comments

However, to answer your question more directly. Consider using a "release branch".For example, in Vincent's "GitFlow" model feature 1 and 2 would have been merged to develop. Then a release branch ...

0 votes

Hi Mary, Moving from TFS to Git is an excellent choice and your question is a very good one. The short answer to it, however, is "it depends". Everyone has their own views on the best branching strategy and it's a minefield of flame wars. For example, first read this article by Vincent Driessen. This is probably the most widely respected and referenced blog post about git branching on the internet. This branching model has become known as "gitflow": https://nvie.com/posts/a-successful-git-branching-model/ However, once you have read it, also read this blog post by Jussi Judin which calls out some of the common problems that some people see with gitflow. Essentially Jussi is arguing for trunk-based development because he feels it's better aligned with the principles of continuous integration and more natural for developers: https://barro.github.io/2016/02/a-succesful-git-branching-model-considered-harmful/ I'm sorry I don't have a simple answer for you - there is a big gap between theory and practice and ultimately you need to figure out how to bridge it for yourself. However, I would like to leave you with an exercise that I have run with various clients to help them figure out the best strategy for their team. I believe in two things: - You should avoid cherry picking changes from one branch or another. Branches should be tested and deployed in their entirety for effective, repeatable testing and simpler deployments. - Your branching strategy should mirror your project management style. You can read all the books you like about DevOps, continuous delivery and branching models, but ultimately if the way you manage your code does not match the way you manage your projects, you will forever be cutting corners, breaking your own rules and creating headaches for yourself. For this exercise, I want you to begin by leaving your theoretical ideas about good and bad branching strategies at the door and consider the reality of the way you manage your development process and map that out as if it were a branching strategy. Since, in a perfect world, your project management style and your branching strategy would be aligned, you should consider whether the diagram you have drawn would be in any way sane if you used it as your git branching strategy. This gives you a starting point - then you should think about whether you could make any simplifications in your branching or project management strategies to make life simpler - considering everything you know about DevOps, continuous delivery and widely accepted branching good and bad practices - while also considering the preferences and abilities of your team. (E.g. avoid long-running feature branches, break down bigger tasks into smaller chunks, don't ask a developer who has never used git to follow a horribly complicated branching model.) For more info about my "reality branches" exercise: http://workingwithdevs.com/branching-reality/ I hope that's useful. Good luck! / comments

Hi Mary,Moving from TFS to Git is an excellent choice and your question is a very good one. The short answer to it, however, is "it depends". Everyone has their own views on the best branching stra...

0 votes

The folks at Redgate use GitHub. Microsoft use Git in Azure DevOps Services. https://docs.microsoft.com/en-us/azure/devops/learn/devops-at-microsoft/use-git-microsoft The Redgate tools will work fine with Git hosted in GitHub, Bitbucket, Azure DevOps Services (hosted) or Azure DevOps Server (TFS / on prem). The de facto source control technology these days is Git and it pays to use the most widely supported and understood tools. / comments

The folks at Redgate use GitHub. Microsoft use Git in Azure DevOps Services.https://docs.microsoft.com/en-us/azure/devops/learn/devops-at-microsoft/use-git-microsoftThe Redgate tools will work fine...

0 votes

Sounds like a sensible decision. Using pre/post should mean the "deploy to all workstations" step is just a simple "get latest"/"apply changes". / comments

Sounds like a sensible decision.Using pre/post should mean the "deploy to all workstations" step is just a simple "get latest"/"apply changes".

0 votes

Good morning, This is a tricky unusual case that isn't handled well out of the box. The problem is ordering. 1. SQL Compare tries to add the new column, but fails because of the NOT NULL constraint. 2. Data Compare squirts in the data, but it never gets to this point because step 1 has already failed. Possible work arounds: UNLINK/RE-LINK STATIC DATA 1. Unlink static data 2. Deploy new column as NULLABLE all the way up to prod 3. INSERT static data manually all the way to prod, or relink static data to source control and then deploy all the way to prod 3. Add NOT NULL constraint, commit to source control and deploy all the way to PROD 4. Re-link static data (if not already done so) This option is probably the simplest, but it requires a multi-step deployment. If you have short release cycles and a good DevOps maturity this should not be too big an issue, but if you have long release cycles and poor release management processes this can be a pain to manage. PRE/POST DEPLOYMENT SCRIPTS SQL Source Control has recently released a new feature which, at the time of writing, is still only available via the frequent updates release channel: https://documentation.red-gate.com/soc6/common-tasks/working-with-pre-post-deployment-scripts 1. Unlink static data and add a filter to ignore the table schema 2. Add a pre-deploy script to add the column as NULL, insert appropriate data and then apply the NOT NULL constraint. Your script needs to be rerunnable, so consider adding an IF EXISTS statement at the top and using a MERGE statement to squirt in the data. 3. Deploy the change all the way to production. 4. Remove the table filter and re-link the static data. Also, remove the pre-deploy script. Once the update has been deployed everywhere you don't need it any more. This option requires less manual intervention/project management but it does rely on more complex and bleeding edge features that have not yet been released on the regular SQL Source Control release channel. / comments

Good morning,This is a tricky unusual case that isn't handled well out of the box. The problem is ordering.1. SQL Compare tries to add the new column, but fails because of the NOT NULL constraint.2...

0 votes

Fair point, i suppose my instructions for option 1 should have read: UNLINK/RE-LINK STATIC DATA 1. Unlink static data 2. Deploy new column as NULLABLE all the way up to prod ****AND TO EVERY DEV WORKSTATION**** 3. INSERT static data manually all the way to prod ****AND TO EVERY DEV WORKSTATION****, or relink static data to source control and then deploy all the way to prod ****AND TO EVERY DEV WORKSTATION**** 3. Add NOT NULL constraint, commit to source control and deploy all the way to PROD ****AND TO EVERY DEV WORKSTATION**** 4. Re-link static data (if not already done so) In retrospect, perhaps the following is a better solution all round: PRE/POST DEPLOYMENT SCRIPTS (v2) 1. Unlink static data table 2. Add a pre deploy to: a. Check if target table is in the before state and that it already holds data b. If so, create a new table called OriginalTableName_Temp c. Copy all data to new temp table d. Truncate original table 3. Add a post-deploy to: a. check if OriginalTableName_Temp exists b. if so, copy all data, including new default data for new NOT NULL col into original table (by the time this script runs, the new col should exist). c. delete OriginalTableName_Temp 4. Commit your new pre- and post-deploy script, along with your new NOT NULL column as a single commit. 5. Deploy this change to all environments, including prod and all dev workstations 6. Re-link static data * For now, you probably need to manually patch all the other dev workstations. Sorry I forgot to include dev workstations in my original answer. Forgive me, I'm a only fallible human. :-) / comments

Fair point, i suppose my instructions for option 1 should have read:UNLINK/RE-LINK STATIC DATA1. Unlink static data2. Deploy new column as NULLABLE all the way up to prod ****AND TO EVERY DEV WORKS...

0 votes

How can we help you today?

AlexYates

Comments