LINKS
SITE FEED
Support Forum
The fastest and most effective to get answers to your questions quickly. Click Here for more info.
FAQs 
Quick answers to common questions. Click Here for more info.
Troubleshooting
Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.
Knowledgebase
Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.
Online Documentation
All of our help files for download or convenient viewing online. Click Here for more info.
Videos
Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.
|
ApexSQL Knowledgebase Tips and How-to Articles for Apex SQL Tools Mapping in ApexSQL Diff, Part 1: Schemas DESCRIPTION This three-article KB series discusses ApexSQL Diff's mapping ability. With ApexSQL Diff, you can map users/schemas, tables, and columns. This first installment discusses mapping schemas. The Testing Environment For this KB series, we will be using the Northwind database on a SQL Server 2005 Developer edition. The Northwind database is a good sample database because-- (1) It is shipped with SQL Server 2000 and so is readily available to a broad audience. The instnwnd.sql script which creates it is available on the Install folder of SQL Server 2000. It is also available for download here. (2) It works with all SQL Server versions supported by the ApexSQL tools (version 7.0, 2000, and 2005). Though SQL Server 2005 ships with a whole new sample database called AdventureWorks, the Northwind database continues to be popular among the SQL Server community and hence, we use it here. What does "mapping schemas" mean? ApexSQL Diff runs in two different modes: Structure mode compares and syncs database structures and Data mode compares and syncs data. Mapping schemas applies only to Structure mode. SQL Server databases consist of system and user-defined objects of different object types. These objects can be stored procedures, views, tables, etc. Common to all objects is that they are owned by an owner. These owners are called user in SQL Server 2000 and older. They're called schema in SQL Server 2005. Throughout this article, we define users and schemas basically as containers for objects and, for simplicity's sake, we will use the term "schema" to refer to both. (For a more detailed discussion of the similarities and differences of user and schema, refer to the User-Schema Separation section of SQL Server Books Online. We can map user to user, user to schema, or schema to schema. With the exception of features exclusive to SQL Server 2005, you may compare and synchronize SQL Server 2000 and SQL Server 2005 databases in one single project. Mapping schemas enables you to compare objects that belong to the same or different schemas. By default, Apex SQL Diff automatically maps schemas of the same name. That is, suppose you have a "dbo" schema in both the source and destination of your project, they will automatically be mapped by ApexSQL Diff. This is not the case when the schema of the objects for comparison are different. Mapping would then have to be done manually. Mapping Schemas manually Typically, schemas in the databases being compared are the same. In such cases, no schema mapping is required and all necessary comparison steps are carried out by ApexSQL Diff automatically. It is not uncommon however to compare databases with objects that have different schemas. To illustrate, suppose there exists a database which stores data for different clients. The database may be designed such that each client represents one schema. Suppose every client has a corresponding Employee table which has a pretty similar structure to the Employee tables of the other clients (do ignore the questionable design--this is just for illustration purposes albeit I am sure such design exists somewhere). In this case--manual mapping is necessary. The Example To reproduce the example, let's issue the following batch: USE MASTER CREATE DATABASE BOSTON GO USE BOSTON GO CREATE SCHEMA BOSTON_USERS AUTHORIZATION DBO GO SELECT * INTO BOSTON_USERS.EMPLOYEES FROM NORTHWIND.DBO.EMPLOYEES WHERE 1=0 The above batch will create a new database named Boston and then switch the database context from master to this new database. Within the Boston database, we create the schema Boston_Users and then copy the structure of the Northwind.dbo.Employees table to the new Boston_Users.Employees table. As we are not interested in data differences between the two tables, we have added the WHERE clause 1=0 which always evaluates to FALSE and therefore just executes the underlying CREATE TABLE process to create the table Boston_Users.Employees. This does not copy over any of the data. We now have two tables with identical names, identical data (zero), but different schemas. Using ApexSQL Diff We now switch to ApexSQL Diff and create a new structure comparison project. As source for the comparison we choose the Northwind database. For the destination database, we use the Boston database. In the Structure Comparison Wizard, we go to the Mapping Schemas page where we can select the schemas to be matched. The screen will look like the one below:  To establish the permanent linkage between these two schemas in this project, we click on the "Add" button,  : The schema pair is displayed in the grid below and visually represents above linkage:  We can simply repeat this process until we're finished. We can even map a schema (Source or Destination) more than once to another schema (Source or Destination). That's basically all we need to do. In summary, all we've done is tell ApexSQL Diff that during comparison, the database structures in the dbo source schema and the Boston_Users destination schema should be compared. Some helpful features There are several additional buttons in the Schema Mapping page of the Comparison Wizard and we discuss them briefly here. Delete The "Delete" button is the reverse of the mapping operation: it unmaps two schemas. To Delete a mapping, we put the cursor on the row that displays the schema pair we want to unmap and click the Delete button. Import and Export Suppose we have many SQL Server databases in our environment that we need to synchronize on a regular basis. Suppose further that these databases have very similar schemas. In this case, we can create a schema mapping that is applicable to the other databases and save it. We do this by clicking Export  after we have completed mapping our schemas: The "Export Owner Mapping" dialog appears as below:  We save  the Exported Owner Mappings File (which is in XML) to the desired location. The default filename is "Owner Mapping.axmp". To use this mapping file in a Structure Comparison project, we import it to the project by clicking the "Import" button,  : We open  an Owner Mappings File and this loads a previously saved mapping into the project. Conclusion This is how simple schema mapping is. In the next installment, we will discuss how to map tables. SEE ALSO Mapping in ApexSQL Diff, Part 2: Tables Mapping in ApexSQL Diff, Part 3: Columns AUTHOR Frank Kirkland LAST REVIEW DATE 27 August 2007 Labels: Apex SQL Diff |
© 2008 Apex SQL Tools All Rights Reserved | 1.919.968.8444 | Contact Us | Terms of Use