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.
- Query to find position in a string before keyword and extract data before that position
- Correlate these tables dynamically (or automated)
- Manage the order of updates when using a filtered unique index?
- How to use an UPDATE Query with an INNER JOIN to update fields within a table
- Can we use SQL Server STRING_AGG() in queries using GROUP BY ROLLUP ()
- What are row, page and table locks? And when they are acquired?
- Aggregate into custom groups
- Work with multiple where statements with different conditions
- Excel VBA connection to SQL Server with Microsoft Entra MFA authentication
- How can I find sql server port number from windows registry?
- SSIS OLE DB Destination Editor - Create New Table not an option?
- How to Apply Gridify Filtering on an ICollection<T> in EF Core?
- SQL Server Cursor vs WHILE loop
- How do I control parameter sniffing and/or query hints in entity framework?
- Select with like is not returning the correct results
- I am trying to get the start date ONLY from the column below using a SUBSTRING. Can someone tell me what I'm doing wrong?
- using Object_id() function with #tables
- Conditionally filter results of query based on a single flag
- Fill down from a specific row based on criteria
- Spark sending LIMIT to SQL Server on display function
- database restore failing with move
- Join column from a table in SQL Server without write permissions to a data.frame in R using dbplyr
- different deleting OUTPUT behaviour based on source table
- How to simulate a deadlock in SQL Server in a single process?
- Is "SET NOCOUNT OFF" necessary in a stored procedure?
- EF Core takes a lot of time, sometimes, to perform SELECT query
- Dynamic T-SQL - Alter column definition to max length of field
- SQL Server: Update table based on JSON
- Best index(es) to use for an OR Statement in SQL Server
- In a select with a group by, get the last value of a column not in the group by