It is important to attempt to maintain a separate SQL Server environment for each phase in the development, quality control, implementation, and maintenance of a SQL Server implementation.
Depending on the organization's resources, the number of different environments might differ , but there should be at least a development environment, a quality control or test environment, and a production environment with clear boundaries between each. An organization might choose to add stress testing, user -acceptance, pre-production, and other environments, which can be considered subsets of the test environment. This chapter focuses primarily on the development, test, and production environments.
In an ideal configuration, each environment should be on a separate SQL Server, preferably on separate physical machines. As a minimum, the production environment must be on a separate server from the development and test environments. If the development and test environments are on the same server machine, they should at least be running under separate instances of SQL Server.
The least desirable environment is a single SQL Server with only separate databases for development, testing, and production ”the needs of each environment conflict with the other environments. The development environment might require the SQL Server to be rebooted often, but the production environment is likely to have continuous up-time as an important business requirement. The testing environment often is constructed to be able to gather performance statistics. The activity of the development and production environments, however, might skew these performance statistics, and testing might affect performance for production. This is why it is strongly recommended that development, testing, and production activities take place on separate SQL Servers.
The Development Environment
A development environment is by definition volatile and unstable and should be flexible enough to allow developers to modify database objects and data as needed, as well as start and stop the server as needed. For this reason, the development environment should be on a dedicated SQL Server apart from the production environment.
The development environment is the area in which developers first attempt to create new modules of an application. Development is an iterative process. Multiple revisions of code are normally created in the process of correctly satisfying design requirements. Consequently, the code might have unexpected results on tables. During the refinement of a module, database activities such as DELETE , INSERT , or UPDATE might need several modifications before they are deemed to work correctly. This requires the developer to create test conditions to test individual pieces of functionality. Additionally, new requirements might necessitate the addition or deletion of columns from tables to test the new pieces of code. These changes might become permanent modifications to the existing structures, validated by the iterative testing of an application. The development environment needs to be structured to minimize contention and conflict between developer changes while at the same time maximizing developer flexibility.
Although flexibility is at a maximum for the developer, so is developer responsibility. A developer has much more responsibility for administrative activities. Developers often create their own objects (tables, indexes, procedures, triggers, and so forth) in the pursuit of satisfying a business problem. Developers often are responsible for managing their own test data. Those responsible for database administration (the DBA group ) still have some responsibilities, however. A DBA might create a base set of objects or manage a core set of data. As always, DBAs still get calls about any problems that a developer cannot handle (killing processes, adding space, and so forth).
Occasionally, a single logical database is physically implemented as several SQL Server databases in production. The distribution of tables to databases in the development environment should match the production model. Remember that referencing tables from two different databases requires that at least one of the table names be qualified by the database name . If the development environment does not have the same database structure, the SQL has to be modified before production implementation, which is likely to introduce new bugs . Your development environment should look exactly like the production environment in the base structure (databases, tables, and objects).
If other projects share the development server, create a document to provide information about all groups. It should contain information such as project name, manager, contact name, phone, and specific instructions. Development environments are volatile. You might need to reboot the server to continue development. Because a reboot affects all users on the system, you should contact user representatives to prepare them for this situation.
Due to the nature of development, it is assumed that the development environment will use a SQL Server separate from the test and production environments. Based on that assumption, you can then determine an approach to constructing the development environment at the database and object level. Development in a SQL Server environment can be approached in three ways. The differences between the approaches are based on whether the developers share a database and whether they have their own copies of objects and data. The rest of this section covers three possible scenarios for the development environment:
Shared Database/Shared Objects and Data
In this approach, all developers share the same objects and data. This works well for small development teams with good communication channels and for maintenance projects.
Advantages of this approach include:
Disadvantages of this approach include:
Individual Database(s)/Individual Objects and Data
In the individual database(s)/individual objects and data environment, each of the developers is assigned a separate database, or in some cases, their own dedicated SQL Server instance. Because SQL Server 2000 can be installed on a Windows NT or Windows 2000 workstation, as well as on Windows 95/98 or Windows Me, these environments can be completely controlled by the individual developer. The developers are the database owners , with complete control over the database structures and data, which they can change at will.
Advantages of this approach include:
Disadvantages of this approach include:
Shared Database/Individual Objects and Data
In the shared database/individual objects and data environment, developers use the same database, but they maintain separate objects and data by creating their own objects in the database under their own database usernames rather than under the dbo user name. The new database roles of db_owner , db_ddladmin , db_datawriter , and db_datareader are applied to the developer username, and any new objects will have the username as object owner.
As long as the owner name is not explicitly specified in the SQL code, the SQL will execute within the current user context. As a developer the SQL will execute against the developer's own objects in development. As an end user, the SQL will execute against the production objects owned by dbo in the production environment.
This approach is similar to the individual database/individual objects and data approach, but it requires less maintenance for the database administrator because only one set of databases must be administered.
Advantages of this approach include:
Disadvantages of this approach include:
The Test Environment
The test environment can consist of a number of different testing scenarios, including functional, integration, user-acceptance, performance, preproduction, and production-fix.
The Functional Testing Environment
Functional testing ensures that all code functions as expected, and it is usually the first formal quality assurance (QA) test performed by a QA team.
A functional test environment should always be in a separate SQL Server instance from both the development and production environments. The hardware in which the SQL Server is running might or might not be dedicated. The functional test environment could be a separate SQL Server instance. Dedicated hardware is preferred, but not required because you are testing functionality and usability across modules, not performance. The test machine also does not need to be configured exactly as the production machine for this same reason.
On a test machine, the database administrator generally creates all objects under the dbo user account. Development logins can be added to enable developers to add specific system test data or to assist in the creation of a core set of data. Developers should not, however, have the capability to change the structure of tables or add, drop, or modify other existing objects. Database administrators should run all DDL from a central source control location that contains the DDL and installation scripts. Developers need to understand that the DDL for the objects is considered source code and must be under source code control, completely unit tested , and delivered to the QA team.
A core set of data should be used that is representative of production data. The amount of data does not need to represent production volumes , because it will be used to support feature, integration, and system testing, not performance testing.
Although developers might have access to tables to add or modify data, testing should be conducted using the logins of users with database permissions that are representative of production users. This enforces the testing of the system in the same manner as it would be used by the actual production users. By simulating real users, potential problems, such as improper permissions, can be identified.
Functional testing tests not only the stored procedures and data in the system, but also the scripts used to install them.
The User-Acceptance Testing Environment
The user-acceptance testing environment should be treated like a production environment. Developers can be given read-only access to help identify problems, but should not be able to modify the data or objects in any way. The environment should be created by database administrators using the same scripts that will be used to install the production system.
A user-acceptance test machine should, at a minimum, be in a separate SQL Server instance from any other test, development, or production environment. The hardware in which the SQL Server is running might or might not be dedicated. The test environment should be configured as closely to the production environment as possible.
The user-acceptance test environment should be populated with a core set of data that is representative of production data. The amount of data does not need to represent production volumes. It is best if full production database volumes can be used to help catch possible performance and query response time issues during testing.
The user-acceptance test environment should be backed up after it is created so that it can be re-created easily to provide a baseline environment for repeat testing.
The Performance Testing Environment/Pre-Production Testing Environment
The performance testing and pre-production testing environment should exactly reflect the production environment, including data and logins. Although an organization with limited resources can select to use only a subset of data, this is not recommended because this testing phase is considered the dry run for the final implementation, and all possible problems need to be flushed out at this time. Often, performance issues, locking contention, and other unanticipated problems might not surface until this point. Although some of these problems should appear at an earlier point in the development and testing process, the performance testing phase might be the only time that the server environment is identical to the production environment, so in practice, a number of issues tend to arise here.
The hardware used for the performance testing environment should be configured identically to the production hardware so that statistics gathered can be considered representative of production performance. This environment is used to validate the system's capability of handling production loads. This should be a dedicated machine so that activity in other databases or environments does not affect the performance statistics being gathered.
The performance/pre-production testing server should be populated regularly with data from the production server to ensure that all data and objects are up-to-date. Developers can be given read-only access to help resolve problems, but they should not be able to modify the data or objects in any way. Logins and database user permissions should be representative of production users, and all modifications to database structures should be conducted only by database administrators.
At this stage, you not only implement performance, multiuser, blocking, and other tests, but you also test the installation of all the database objects and data. This is called pre-production testing, and it is the last chance to verify an installation before it is released to production.
The Production-Fix Testing Environment
The production-fix testing environment should be an exact copy of the current production environment. This environment is used for applying hot-fixes or patches to the production system to correct critical production problems. Due to the urgency of these fixes, they usually do not go through the full software development life cycle. It is the database administrator's responsibility to ensure that the changes made in the production-fix environment are migrated back to the current development and testing environments using the same DDL scripts used to implement the changes in production.
Although the production-fix testing environment should be treated like a production environment, developers might need to be given limited access to help identify and fix problems.
A production-fix test machine should be, at a minimum, a separate SQL Server instance. It should be configured as identically to the production environment as possible.
The Production Environment
A production environment should be completely separate from the development and test environments, on a separate server, and maintained only by administrators.
The SQL Server and database hardware that are used for production should be dedicated. A dedicated SQL Server machine is preferred because it greatly simplifies analysis of performance statistics that are captured at the SQL Server or hardware level. It is not recommended that multiple SQL Server instances be installed on the production machine. Most production implementations use dedicated hardware for the production SQL Server.
An initial load of data might be required. Database administration normally is responsible for loading the core supporting data (for example, code lookup tables), as well as any other baseline production data needed to support production use of the application.
Production servers hold an organization's data and should be regularly maintained and guarded as the valuable assets that they are. For more information on backing up and maintaining SQL Server environments, see Chapter 16, "Database Backup and Restore" and Chapter 17, "Database Maintenance."
Using Source Code Control
Source code control software has been used for a number of years in the development of software to manage changes to application code (COBOL, C, Visual Basic, and so forth). Source code control software also can be used to manage changes to your DDL. Source code control software can act as a logbook, enabling only one person to check out a file at a time. Checking in a file normally requires entering information to indicate why the file was checked out. The version number of the file is incremented each time a file is checked in. Some development teams cross-reference the check-in statement with a database change request document.
Source code control software also provides the capability to "cut a version" or label a group of related files. For example, 20 different source code files (each having its own revision number based on the number of times it was changed) can be used to create a C application. Cutting a version relates these 20 files together and logically groups them. Database changes happen for a reason. These changes usually parallel application changes that can be listed as part of the check-in comment.
Source code control gives DBAs a mechanism to manage distinct versions of the database effectively. Through use of this tool, a DBA can identify what versions of DDL files to load to re-create a specific version of a database, object, or stored procedure. This can help to keep track of the proper DDL to use to rebuild a specific test environment.