I am learning to write stored procedures. What is best practice for where to store / save them until they are ready to be used in production? I don't want to add them to a huge list of existing stored procedures on the database I will be writing them for. Should I create a sandbox database and create them there, using the tables from the production database?
CodePudding user response：
A good practice is to have 3 databases. One for developing, one for QA and one for production.
Your new code should be written in the developing system. When your stored procedures are finished you move them to QA. Your users should validate the changes you implemented and when everything works as expected you can put your changes in the production instance.
You can use tools like SQL Compare to help you in the migrations from developing instace to QA and QA to production.
CodePudding user response：
The ideal environment in my opinion is:
- Dev database (SQL Server Development Edition - check license)
- Test database (edition depends on company licensing)
- Prod database (edition depends on company licensing)
- Source control
- Comparison tool
Dev can be either a common database for all developers (common data sources) or individual developer centric database (more trouble during reviews due to different data states).
Test as a common database with realistic (preferably copy of production with relevant data masking/replacement for sensitive data). Prod naturally as a common database.
Source control is just essential to keep track of changes and approvals. Comparison tool is rather cool in case of (serious) bugs and can save a lot of hours. ( Hint: Red Gate has a very nice, cost effective tool box. In my experience the cost saves a lot of hours working without. )
Dev is naturally just for development. If Test is a very good database it can run just for the developers side-by-side with Production and allows changes to be evaluated for a few days / months. This allows to capture most edge cases before change goes into Production.