The SQL Server Environment Approach

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

• Individual database/individual objects and data

• Shared database/individual objects and data

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

Advantages of this approach include:

• Because all objects are shared, changes are immediately available to all developers, avoiding the often difficult process of merging different definitions together.

• This approach is simple to manage because it involves maintaining fewer objects and databases.

• Storage requirements are reduced because only one copy of the database and one copy of objects and data exist.

• If your tables are spread across multiple databases, and the development environment runs in a separate instance from the test and production environments, the names used for databases can be identical between development, test, and production environments. Therefore, code that is developed that uses fully qualified object names does not have to be modified to be promoted to the next level.

Disadvantages

Disadvantages of this approach include:

• The natural volatility of the development process causes contention for data. Developer 1 might be testing a delete activity on the same row that Developer 2 is using to test an update activity. Development will be impeded as confusion results from "unexpected" changes to data.

• The same can be true of contention for database objects, although this should be less of a problem because architectural changes are normally modeled and are part of a much larger process of impact analysis and change control. The developer's freedom to change the underlying structure to support a hypothesis or test condition is decreased significantly.

• Development flexibility is somewhat reduced.

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

Advantages of this approach include:

• No developer is dependent on another; therefore, no contention or conflict occurs over data or database structures, providing maximum flexibility.

• Because object and database names are identical in each of the environments, no changes are necessary to the SQL code as it is promoted to the next level in the development process.

Disadvantages

Disadvantages of this approach include:

• This approach requires much more space because each developer requires a separate copy of the database(s). Some organizations might not be able to support this approach.

• As the number of servers or the number of databases in a server increases , administrative tasks are complicated. A greater number of servers and/or databases need to be backed up and managed.

• Any changes to the database structures must, at some time, be integrated into the other developer's environments. This task can be time-consuming and generally requires good communication and process control.

• If the application architecture requires that tables be spread across multiple databases and the development environment consists of a single SQL Server instance, using this development approach is likely to require code changes to promote code to production. In a single database approach, code is consistent from development to production because you never need to qualify the name of a table with the database name. In a multiple database scenario, if a query is executed that refers to tables in different databases, at least one of the tables has to be qualified with the database name.

  Consider an example with five developers with their own copies of each of the databases. The first developer writes code against customerdb_1 and purchasedb_1 , the second developer writes code against customerdb_2 and purchasedb_2 , and so on. The following is an example of SQL access across databases:

   select *  from customerdb_1..customer c, purchasedb_1..purchase p where c.cust_id = p.cust_id 

  Migrating this code into the test or production environments requires changes because these environments likely would contain different database names ( customerdb , purchasedb ). You have to modify the code to arrive at the following statement:

   select *  from customerdb..customer c, purchasedb..purchase p where c.cust_id = p.cust_id 

  Migrating code from one environment to the next is much more difficult, and for this reason, the private database approach for multi-database implementations is not recommended unless each developer can be provided with their own private SQL Server instance. Database names can be the same within each of the SQL Server development instances.

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

Advantages of this approach include:

• Each developer has an individual set of data. A developer has complete freedom to update, delete, and insert data without affecting other developers.

• Migration from the development environment to the production environment is eased. All developers work with the same database and object names. This ensures that any SQL created is identical in all environments. No code changes that are due to different database or object names are necessary.

• Systems spanning multiple databases do not require modifications to the SQL to promote it to production (unlike the Private Database development approach) because the development environment database structure is identical to the production database structure.

Disadvantages

Disadvantages of this approach include:

• The number of tables in a single database can be large. A system with 100 tables and 10 developers could have 1,000 tables in the development database.

• Storage requirements are magnified by the number of developers as compared to the shared object approach.

• If a developer needs to reference an object created by another developer, that object will have to be fully qualified with the owner name. The SQL code referencing the object will need to be changed when the code is promoted to the next level where all objects will likely be owned by the dbo user account.

• Any differences between the database objects must, at some time, be integrated into each developer's copy of the structures. This task can be time-consuming and generally requires good communication and process control. Eventually, when the code is promoted to the next level, all changes must be integrated together and the objects re-created under the dbo user account.

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.

TIP

Regularly restoring backups to the production fix environment also serves to ensure that your production environment can be successfully restored from backups . The worst time for finding out that your backup and recovery strategy doesn't work is during a system disaster!

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.

CAUTION

A developer would probably never need to change or have access to a production server as an administrator. Once a system is in production, it is under the care and responsibility of the database administrators. Security standards and procedures should be in place and strongly enforced.

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.

CAUTION

Although some third-party SQL development tools can integrate with source code control software, such as Microsoft Visual SourceSafe, it is still possible to edit and change a stored procedure without going through the source code control software to check out the stored procedure code first. Any DBA or developer with the appropriate rights can use Enterprise Manager or Query Analyzer to extract the source code from the database directly and modify the stored procedure. If they do not remember to save the source code to a file and check the changes back into the source code control system, the changes will be lost when the next build is generated.

If you are using source code control for your DDL, it is essential that all developers and DBAs follow proper protocol. They must check out the DDL from the source code control system first, make changes to the source code file directly, create and test the modified code in the database, and then save the changes back to the file and check the file back in.