Reverse Engineering a SQL Server Database in Visio is a quick and easy way to document your work. These instructions refer specifically to Visio Professional 2002 SP-2, which is installed on the MIS notebook computers. Before starting this process, I recommend that you connect to the instance of SQL Server that contains your database. The process is explained in the handout Server Registration.
Start Visio. Normally, you will see the “Choose Drawing Type” dialog (see below); choose “Database” as the Category, then the Template “Database Model Diagram.”
You will see a blank drawing surface, with a set of shapes on the left.
Choose the menu item Database, then Reverse Engineer... from the drop-down list. This starts the Reverse Engineer Wizard (see following figure). In the Installed Visio drivers list, choose “Microsoft SQL Server.” The Data source for your database is probably not in the list of Data sources; therefore, click the New... button.
Choose “User Data Source,” then click Next >.
Choose “SQL Server” (at or near the bottom of the list), then click Next >.
On the following screen (not shown), click Finish.
The next screen allows you to name and describe the data source, then indicate its location. The name must be unique (vis-a-vis the other data source names on the first screen of the wizard). If you are on-campus using a wired connection, the server might appear in the drop-down list. If you are using a wireless connection or are off-campus, then enter the server URL and port, as illustrated below (this applies specifically to the Spring 2005 instance of SQL Server, modify as appropriate). Once the information is entered, click Next >.
On the next screen (illustrated below), choose to use SQL Server authentication and enter your Login ID and Password. Click Next >.
Change the default database to the database that you want to reverse engineer, as illustrated below (use your own database). Click Next >.
On the screen that appears (not shown), click Finish. You will see a summary configuration (below), click the Test Data Source... button.
A Test Results dialog appears, if you see TESTS COMPLETED SUCCESSFULLY then you can proceed, click OK on this dialog and on the summary configuration. You will be taken back to the first screen, with your new data source highlighted (see below). Click Next >.
You will have to enter your User ID and Password again, then click OK. The next screen requires you to select object types to reverse engineer. I usually uncheck Views and Stored Procedures. Click Next >.
On the next screen, Select All tables, or those that you want to include.
Add the shapes to the current page and click Next >.
Review your choices, then click Finish.
Visio will extract the database structure and arrange it automatically for you. Click the X at the lower left corner of the Output to close that window and change the magnification (near the upper right of the window) to something more legible (e.g., 75%).
To change the style of the ERD to something more familiar, choose Database, Options, Document... On the General tab, I usually set the symbol set to Relational and use Physical Names; On the Table tab I choose to Show physical data types; On the Relationship tab I click Crow's feet.
You may have to fiddle with it a bit to get a pleasing display.
Don't forget to save your work!
This is a living document that is subject to revision during the course of the class. Last revised Monday, April 11, 2005; at 8:04:01 PM.
No comments:
Post a Comment