Browse DevX
Sign up for e-mail newsletters from DevX


Create a Developer's Extract of a Production Database-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Drew Georgopulos is the Database Architect for GoAmerica Communications. He moonlights as an Adjunct Professor of Computer Science at Marymount College of Fordham University in Tarrytown, NY teaching systems analysis and relational database design. He holds a certification in systems analysis and relational design from Columbia University, has presented at SQL Connections on SQLDMO, and is a member of the Worldwide Institute of Software Architects.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date