Redgate SQL Source Control recommended workflow for dev-test-live databases
We are trying to get started with SQL Source control and have some questions.
This is what I am aiming towards. Does this look like it will work?
- Modify dev database tables/procs
- Commit to dev git branch on dev PC
- Push changes to central repo
- Repeat steps 1-3 for each change
- Merge dev branch into test branch
- Use SQL Source ConGtrol "Get Latest" on test branch
- Apply changes to test DB
- Repeast steps 5 - 8 but from test to live
Note: - Using "Shared database" development model.
Questions:
- Does this look like it will work?
- Can SQL Source Control apply the changes to the test and live databases
- or do I need to purchase a copy of SQL Compare for the dev server to perform this task?
- Fix the diagram here: http://goo.gl/l2mJdt
- More notes & experiences in my Google Doc
Great that you're deploying a versioned copy of your database changes from the repository, that's really good continuous delivery practise in my eyes.
Have a few suggestions about your questions (I have my Red Gate hat on)
Usually wouldn't recommend connecting SQL Source Control to your live environment. It polls to look for changes, and that is may not something you want on your live system. The recommendation is to use SQL Compare instead to make one off deployments to UAT/Production systems. Alternatively the Red Gate Deployment Manager product may be of interest to you.
You ask above about Shared/Dedicated mode in test. It doesn't matter if you are using a shared database for your developers in your dev branch, and then a dedicated model in your test branch. If the only changes to the test database are coming from one place (e.g. your git deployments) then it's probably better to run that database in dedicated mode.
I've drawn a diagram with some tweaks to yours. Not sure if you are using a CI server, but I've added in where that could fit into the process too. This diagram assumes dedicated modes for the two developers, but that could be a shared database instead.
- Using Dapper one-to-many (spliton) with generics
- How to join two SELECT queries
- Join two SELECT queries having same column names but different row values in both tables
- How can you represent inheritance in a database?
- String_agg for SQL Server before 2017
- How can I remove accents on a string?
- SQL - order by list order
- Convert varchar list to int in Sql Server
- "č" converted to "c" when querying through SQL Server 2008
- SQL Server 2016, Invalid object name 'STRING_SPLIT'
- SQL: Data Transpose
- How do I create a unique key that is case sensitive?
- ODBC Data Sources; can't add MySQL Driver
- SQL Server - how to query JSON string which has numeric value as keys
- BizTalk 2020 WCF-Sql Polling Receive Location no Response
- Pyodbc: Login Timeout Error
- Unable to upload rdl SSRS 2016
- Database's backup location that created by Azure Data Studio on Mac M1
- Entity Framework: The instance of entity type '<T>' cannot be tracked because another instance with the key value '{Id: x}' is already being tracked
- Check if a connection is in a transaction
- Can I have multiple rows of tabs in SQL Server Management Studio
- SQL Server: Make all UPPER case to Proper Case/Title Case
- How to disable transactions for individual migrations in Flyway on SQL Server
- Natural (human alpha-numeric) sort in Microsoft SQL 2005
- SQL how to do a LIKE search on each value of a declared variable
- Roll Up with column changes
- How to open a huge .sql file
- ExecuteReader TimeOut solved by changing the name of the stored procedure
- How do I get list of all tables in a database using TSQL?
- SQL Database in restoring mode