Setting up shared database

bruce21

I am looking for some help getting setup. I have 3 SQL instances that are shared between my developers. What is the best workflow from moving from Dev instance to Stage instance, then Stage instance to Production instance? 

bobprobst

What's your source control on the back end? 

I assume all the Development takes place in Dev - Is Stage like a Test\QA area?  Assuming so,  take away all the developers change rights in Stage and Prod.  Assign a gatekeeper for promoting changes from Dev to Stage and Prod.

Have your devs use Object Locking in SSMS to ensure they're not overwriting each other's code.  It only takes one person making a mistake and losing hours of work for them to remember in the future.  (been there!)

When Dev has functioning changes, use the Migrations tab to generate a migration script on the objects you want to promote. Track the changeset numbers generated by this.  If you're using TFS, you can associate it to TFS work items with #A[item number] in the comments during check in.  Never check in individual objects.  Always build a migration script to capture the change.

When you're ready to promote Dev changes to Stage, run the migration scripts in the order created - ideally each migration script should result in functioning code. Even when an object is included in multiple Migration Scripts, if you run those scripts in the creation order, Stage will be guaranteed to match Dev.  This now becomes your migration plan.  The collected set of migration scripts can be run in Prod and yield the exact results as they did in Stage

AlexYates

Hi Bruce,

Sorry to differ from Bob, but I wouldn't recommend using the 'migration script' feature of Redgate SQL Source Control in the way that Bob has suggested.

The 'migration script' feature is intended only for data migration and table refactors that SQL Compare cannot handle using generated code. Use it like pin-hole surgery to help SQL Compare only when it needs it, rather than for every change. Using a Redgate SQL Source Control 'migration script' for every change will cause significant performance issues with the Redgate tools and may lead you into complicated and difficult to troubleshoot issues.

If you would like a migration script for every change that's perfectly reasonable. However, you should use a migrations first tool (such as ReadyRoll) rather than a model first tool (SQL Source Control). For more info on the Model vs Migration decision:

http://dlmconsultants.com/model-vs-mig/
http://workingwithdevs.com/delivering-databases-migrations-vs-state/

If you would like to stick with a model based approach I recommend letting DLM Automation generate your deployment scrips within an automated build/release pipeline.

I would also strongly encourage you to move to dedicated databases rather than a shared dev database. More info here:
http://workingwithdevs.com/shared-vs-dedicated/

Those points aside, I would agree with Bob in pretty much every other aspect. :-)

Since this is a massive topic, if you would like to schedule a free 30 minute skype chat etc let me know. You can contact me using the details in my signature or by using the email address on http://dlmconsultants.com/. (Ask for Alex.)