Contents
Should developers have access to production database?
Ideally developers should not have access to production database. They should have their dedicated QA/UAT/Test for all type of testing. On the DEV database they can either have DB owner access or DB_datareader and DB_datawriter. Depending on what they are doing permissions can be given.
What do I need to run SQL queries?
You’ll need these three things for running SQL queries:
- A database engine.
- An SQL client.
- An SQL query.
How does a script update the production database?
These scripts will upgrade the current production database to the new version in development. Your build process can restore a copy of the production database into an appropriate environment and run all the scripts from source control on it, which will update the database to the current version.
How to keep track of the version of a database?
Have a version table to keep track of the current database version, and only execute the scripts if they are for a newer version. Use a migration solution. These solutions vary by language, but for .NET I use Migrator.NET. This allows you to version your database and move up and down between versions.
How to keep track of database schema changes?
Script all your schema changes, and have your CI server run those scripts on the database. Have a version table to keep track of the current database version, and only execute the scripts if they are for a newer version. Use a migration solution. These solutions vary by language, but for .NET I use Migrator.NET.
How does a developer reload the database from SVN?
Currently, developers will make a change to the SQL schema and do a dump of the database to a text file that they commit into SVN. We’re wanting to deploy a continuous integration development server that will always be running the latest committed code. If we do that now, it will reload the database from SVN for each build.