The Different Methodologies Used By Database Developers
Most of the database developers use a shared development server. This is often used where the test database has to run against large test data, sometimes even an obfuscated copy of the actual data. This can be made to work but the chances are not always good. Information in extended properties to keep a tally of the work in progress and the author of an alteration and database developers rely on structured headers and information in extended properties. The entire database script is then checked into source control at regular intervals, as well as all occasions when the team are likely to require a consistent build. The code must be put in source control as a series of table-scripts and routine-scripts, as a complete build script, or both. Unless the database developers are particularly orderly in their approach to Source Control, it is usually better to use the hybrid approach as described in the next section.
All database developers should either have a local version of SQL Server on their own workstations, or have access to a sandbox server with instances for each developer. This allows them to test out routines, database configurations, and the effects of different database/server properties on performance, even to the extent that it crashes a server, or locks up the database without affecting others. There ought to be a central database development cell accessible to all, which allows test data to be created and updated with releases apart from this. Developers can change it at will – but agreement is needed before making changes that affect structure and common routines and this environment is ‘uncontrolled’. This should be refreshed from time to time to get rid of test data.
Database developers have processes for developing against several versions of the code-base, testing builds, packaging builds for deployment to test and production, and for rolling back a build. Database developers are used to agreeing up-front on standards so that they can work together on code effectively, without friction or misunderstandings. Database developers ensure that build scripts will compile as the order is predetermined in the script. This approach fits comfortably with the idea of the schema in SQL Server.