Once upon a time, somewhere out in La La Land, an application was written, the defects were eliminated, and then a software was implemented. Today, the practicalities of design and development, apparently unattainable software perfection, and fluid design requirements contribute to the iterative reality of software development. As if a natural law, software changes.
Software and software changes must be traceable, repeatable, and tested if the application is to be consistently and reliable. Trace ability is simply the predictable responsiveness to a set of tests that prove the software does what it is intended to do. This set of tests become the acceptance and more rigorous regression test plans for the code base. To the degree that changes can be applied to copies of that code base living in different environments, say an acceptance testing environment and a production environment, and the same code base is established irregardless of the environment is the measure of repeatability for those changes. Collectively, these qualities of tractability and repeatability, as measured and confirmed through testing of the changes within a software lifecycle setting are characteristics of a sound software development methodology.
Change management or change control elements within a methodology provide the framework for the trace ability and repeatability necessary for reliable testing. Ultimately, change control aims to assure the integrity and consistency of the production environment. In practice, change management must proliferate through the underpinnings of a development process to achieve that goal. By necessity, changes must be managed throughout the development process and must all types of change must be supported. While most change is introduced iteratively in releases it is a common business practice to introduce bug fixes, hot fixes, patches and time critical changes that must be applied across iterations with manageable disruption and predictable results. So there is the challenge for change control: elegantly manage all change.
In the beginning, it is necessary to describe the development process or, as it is also know, the software lifecycle. Theoretically, Software development includes design, development, testing, implementation, and maintenance phases. In reality, there is a significant overlap in these phases and iterative bi-directional movements between phases are necessary to develop a comprehensive business software product. Additionally, business applications try to be sensitive to business needs that demand quick times to market with a minimum number of defects from requirements that are incomplete or non-existent and ever changing. Almost always, more than one person is necessary to achieve this hearty goal. People working to solve a common task are more effective as a team than otherwise: entropy. It is important for the team that cooperative tools are in place to provide a basis for creating order from chaos and, as a result, consistently producing quality software.
A typical software lifecycle might be manifest in four environments: unit test, system test, quality assurance, and production There are other configurations in common use and a good change control system must be adaptable to a variety of lifecycle environments yet this four level lifecycle will provide a common and useful starting point from which to explain dbChangeControl practices. A small shop might have only two environments: development and production or a highly redundant development environment might need to support a mirror image of production between quality assurance and production or even a post production or archive stage. What are the environments in in your development process?
When an initial design or a design change requirement is specified, class modeling, database design, and database development must begin by prototyping and building individual components and data containers . The server side components are best created and tested in the smallest practical units to be understandable and testable. No integration with middleware or GUI interfaces is advised to complete this development step. We may appropriately term the environment for this work as unit test or more simply as unit. Once unit tested, the work can be moved to an integrated environment for testing in concert with other components of the application. We can identify this environment as system test or system. This is the true application development environment. All changes identified in system are completed and tested again in unit then reintroduced to system iteratively until a satisfactory software product is realized. Once system tested, the software is released by development to a separate business entity for well planned independent compliance testing that simulates the real world that the application will ultimately know. Not even integration and interface tweaks by developers must be undertaken in this quality assurance or QA environment else the purpose of this critical and expensive testing is undermined. It must be completely hands off to developers and all changes must be introduced to QA as if being moved to production. This step is critical, as it will provide the only opportunity to test the implementation procedure. Should the implementation fail while moving to QA this will constitute a system test failure and all defects must be resolved before the specification changes or new product can be handed off to QA. It may be obvious that any defects unearthed in QA must be corrected in unit and confirmed in system before being returned to QA where a long and exhaustive test cycle must often be completely repeated. Only after QA is satisfied with the product can the specification changes or new product be placed in the ultimate environment: production.
Change control and versioning are not transparently built into Microsoft's SQL Server as it is with the components of the Microsoft developer tool set, Visual Studio. Microsofts Visual SourceSafe 6.0, a component of the Visual Studio 6.0 Suite, provides the foundation to create a necessary level of order and is available for our use in managing effective change control of SQL Server schema and scripts in the properly licensed Microsoft centric development shop. To use SourceSafe with SQL Server the schema and scripts must be manipulated from the SQL Server format to a ASCII text format. This is easily achieved from SQL Server using the built in DMO object model to script SQL Server objects to a Windows NT File System container. SQL Server's Enterprise Manager can produce such scripts, yet the multiple switches and options of the interactive dialog make it difficult to produce scripts in exactly the same format each time a database object is scripted. This is not acceptable for the character by character difference finding functionality within SourceSafe. Furthermore, Scripting from the Enterprise Manager cannot be automated. A technique to achieve consistently formatted scripts from the database in an automated manner is required. Stored procedures that use the OLE-DB system procedures to access the DMO objects can be coded to define a consistent format and easily automated through the SQL Agent's Job Scheduler or other client process.
On the Microsoft SQL Server platform, a Desktop Edition database engine with internal functionality on a par with the primary SQL Server is available to each legitimate client. The administrative functionality of the desktop engine is less than a 'real' SQL Server in a few areas. In general, these differences will not impact the developers effort. When it does, the issues must be resolved at the next step in the software lifecycle or perhaps in a lab environment. Refer to Books on Line for details about the difference.
Some versions of the database package also offer a Developer Edition with even greater functional parity to Enterprise Edition database engine. Either the Desktop or Developer engine can be installed on an NT Workstation or Windows 2000 Professional. This affords an ideal opportunity to designate the developers desktop PC as the unit test environment for all database work. Note that while the Desktop engine will run on Windows 9x PCs, NT security is not available. For this reason alone SQL Server on Windows 9x is too crippled for our purposes.
The objectives of the unit test are to create or revise a small unit of functionality at the database level before it is placed in an integrated environment, exercise every line of code produced, identify and implement error handling, and buffer the system test environment from as much developer discovery work as possible. Coding and testing standards for unit testing are determined by the database team. It is the database developers responsibility to the team, the application, and the organization to see that all such standards are met before any work is released from that developers unit testing environment.
While GUI development tools are useful for development tasks at the unit level, the risks of procedural errors is too great to continue to recreate database work at each ensuing step in the software lifecycle in a manual process via a GUI interface, no matter how well documented. The changes must be introduced to upstream environments in an automated manner. In order to adequately automate the movement of code and objects to higher levels in the development process, all work must be scripted as T-SQL in order to move it to system test and beyond.
Scripts created from the unit environment are checked in to SourceSafe. Only from SourceSafe are they introduced - or promoted - to system, and then only if they came from unit. Likewise, only scripts checked in from system can be promoted to QA and only scripts checked in from QA can move to production. Scripts that are checked out or are never checked in cannot be moved. There are no exceptions.
In unit, the developer has the ability to create and add new script objects and to retrieve script objects from any environment for revision or reuse. All other environments must follow the basic rule that only objects from the parent environment in the lifecycle hierarchy can be moved into the environment.
Without question, SQL Server is most secure under NT/Active Directory authentication. Users are placed in the SQL Server System Administrator role in the appropriate production, quality assurance, or development SQL Server and of course, each developer is (or should be!) an administrator of their local workstation. The users then control SourceSafe project migrations having read/write access to SourceSafe database projects corresponding to the SQL servers where they have administrator rights. For example, a QA DBA can read the QA SourceSafe project and is the SQL Server System Administrator role in the QA environment only. Likewise a system test DBA can read the system SourceSafe projects and administer the system test SQL Servers. The QA and system test DBA has select/read only access to production environments. A unit developer is able to promote objects to system test and has read/write access to the SourceSafe projects associated to their desktop SQL Servers and to the system environment..
Database security is managed in system or QA just as it is in production. Developers outside the database group will have execute permission on stored procedures in system test if that is the level of permission that developer's process will have in production. This strategy says that permissions are defined as part of the development process rather than keeping everything wide open until the code hits production. Operations must be involved in defining the security model for production systems. A good rule of thumb is to grant only the permissions necessary and only to the roles or users that need the permission. Avoid anticipation and speculation when it comes to security and access. Of course, also avoid anticipation and speculation in other aspects of software development. A database is a reservoir of facts. Null is not an interesting fact.
Integrated Solution
dbChangeControl is a server side database change control tool set that provides a simple and flexible framework for database development and change migration intended to support a release based software lifecycle strategy and also accommodate hot fix class business lifecycle realities. dbChangeControl can support two or more software lifecycle stages. Many scenarios are possible
Return to the dbChangeControl index page.