aving a development environment provides excellent software change management because it ensures secure, isolated testing and production environments. However, for many IT shops, maintaining three environments is too much overhead. Besides the challenge of guaranteeing both data integrity and consistent metadata between the environments, it requires keeping the development database at a manageable size despite the large database requirements inherent in the other environments.

If these challenges aren’t met, the enhancements made during development may not have the anticipated results in testing or production. And even if they are successfully met, you still need to contend with the most important challenge: preserving the relational chain between parents and children so that developers’ prototypes produce the desired results.

With this 10-Minute Solution, you will meet the challenges of providing a manageable development environment. You will develop an extract of the production environment that is just like production in every way but size, enabling developers to work with a representation of the database in test and production?only smaller. Instead of generating or making up sample data, you’ll extract production data, allowing developers to work locally without needing to dedicate a new server for development.

The extraction solution also maintains the relational chain as well as all the other assumptions of the “real” database?the foreign key references are transported to the extract in tact. You control the size of the extract with a selectable percentage by using the Modulo operator, and even if you select just one percent of the data in production, all its consistency will be preserved. In effect, this Solution is like a data warehouse ETL procedure in reverse.



How do I preserve the metadata and data content of the test and production databases in a development environment without the database size requirements inherent in those environments?



Create an extract of the production database to support local development.

Extract, Transform, and Load in Reverse
The first step is to create a view that limits the size of the largest transaction record volume. Using the Modulo operator, copy the results of the view to a dummy table that replaces the original table (without the sp_rename). Use the Data Transformation Services (DTS) to do the heavy lifting. DTS has the intelligence to move related database objects between databases so you don’t have to manually do it yourself. Using the Northwind database, I’ll walk you through developing the extract.

First, you need to set the scene for your export. Create a view of the lowest-level transactional table in your database (in the case of Northwind, that table is Order Details). As the lowest level of transactions, Order Details has the largest volume of data. To control the size of the export while preserving the relational integrity of the copy, create a view that selects only a portion of the transactions and use DTS to mind the relational details for you.

Create a New View
Open the Northwind Database and right click Views. You’ll find a number of cool views on which you could build. For example, you could use the Order Details Extended view, but you play with enhancements only after the view details are created. For the purposes of this Solution and to ease the spoofing of the Order Details table, select New view.

The View Designer window opens with four design surfaces. In the top pane, right click to add Order Details to the view. Select All Columns. In the SQL pane, add the Where Clause as shown in Figure 1.

Figure 1. The View Designer Window: Get a close-up view of the view designer window.

Save the view as MiniOrderDetails and close the View Designer. If you return to Enterprise Manager and run the view, you’ll see that instead of returning the entire Order Details table (2,155 rows), it returns only 22 rows. You have effectively chopped the volume of data down to one percent of the table’s contents by using the Modulo operator in the Where clause, which also has the fortunate side effect of preserving all the parent records that belong to that Order Details’ keys. You get a consistent picture of the relational chain but with only one percent of the detail in the view, yet you still have all the parent records that support the records in the Order Details table. Now you can use this view as the basis for creating a mini version of the Northwind database.

Create the Extract
Right click on Northwind and select All Tasks Export Data. Use Northwind as the source database, and create a new database to hold the extract. I called mine Illwind and created it on the same server. In practice, I would point to the developer’s server to establish my copy of Illwind, the destination database.

Figure 2. Select Objects to Copy Form: View the details of the Select Objects to Copy form

On the next screen, select Copy Objects and Data Between Databases. Figure 2 shows the Select Objects to Copy screen. Leave Create Destination Object checked. Because this new database is empty, you can uncheck Drop Destination Objects. However, you should include all dependent objects so that Northwind reproduces the metadata, constraints, etc. of the source in the target. Leave Copy Data Replace Exiting Data selected to preserve the foreign key constraints in the copy.

Deselect the Copy All Objects radio button, and in the following window, pick the view you just created, MiniOrderDetails. This will inform DTS to move only the tables that are dependent on the keys in the view. It will also move the view so we can use its contents to replace the bulk of the Order Details data.

Uncheck the Default Options checkbox and relieve the target of the security constraints, but leave all the Table Options and Quoted Identifiers checked. Click OK. This setup lets the system do the work of minding the relations and constraints. Click Next when the Select Objects to Copy form returns. On the Save, Schedule and Replicate Package form, leave Run Immediately checked and save the package on the server. You can use this DTS package to create Illwind on another developer’s workstation, so that his or her development can proceed in parallel. I saved the DTS package as “Mini Northwind.” Just click OK and you have created the extract of Northwind as Illwind on the destination server.

Check the Results

Figure 3. Query Analyzer: View of the Query Analyzer with the commands needed for the extract database Illwind.

After the thermometers and status messages, you should see the DTS Import/Export Wizard message: “Successfully copied objects from MSSQL Server to MSSQL Server.” Click Done and return to Enterprise Manager. If Illwind doesn’t appear on your list of databases, right click on the Databases folder and select Refresh. Illwind should now appear in the list. Remember to back up everything that you’ve changed on the server, like Master (standard operating procedure), Northwind (to keep your new view alive), and Illwind.

Open Illwind | Tables and you’ll notice that DTS moved all the related tables based on the foreign key relationships, even though you selected only the view for export. Open Views, and you’ll see the MiniOrderDetail view you created. You’ll use this view to replace the contents of Order Details, which still has all its original records.

In the extract database Illwind, open Query Analyzer and run the series of commands shown in Figure 3.

Run sp_spaceused and Query Analyzer will reveal the results shown in Figure 4.

Figure 4. Query Analyzer Results: View the results from running the Query Analyzer.

Although the differences between the databases are not dramatic because they were both pretty small to begin with, creating an extract of the database does offer an advantage: developers can do their work in an environment identical to production and testing without having to connect to or porting around a giant database copy. Additionally, you should take note of a couple of assumptions. In order for the modulus operator to work, the primary keys need to be integers and not natural business keys because dividing characters is tough. Another assumption of this requirement is that the primary keys are surrogate keys with no business meaning.

In practice, the testing process must have the same volume of data as production so you can measure any performance differences that enhancements create. An enhancement may not have the performance characteristics required for use in production. Yet developers need a reliable mirror of the production environment to carry out their development ideas?something like a sandbox?before they are ready to release their products for testing. There is no good way to replace the size factor in the testing environment. Testing is your last chance to evaluate and correct any errors before an enhancement goes live, and as such, having identical production and testing environments is an indispensable and irreplaceable part of software change management.