Several questions regarding synchronization of a large db

j.salmon

Scenario:
Database1 is a live database with about 100 gig of data.
Database2 is a test database which needs updated nightly.

Using SQL compare, I can successfully update the schema of Database2 with no errors. There are roughly 12 tables in db1 that do not need copied which store a substantial amount of data. There are roughly 300 tables total. The schema for these 12 tables is still copied over, but I will create a script which copies over a reasonable amount of data after SQL data compare has completed for all other tables.

1. What is the best method for getting the data from db1 to db2? Should I create a job which calls the executable via commandline with an .xml file which specifies the tables to exclude? Should I be using .net to create a package or just the commandline? What are the pro's/con's between the two?

2. When running the SQL Data compare (GUI) against the new database (which at this point only has the schema) to the live database, there are 60 tables which come up as unable to be compared. When clicking on the details of each table, it says the keys could not be matched for several columns in each table. How do I work around this? The schema was copied successfully, so I would expect the columns/data for the selected tables to be mapped correctly as well.

Thanks for any suggestions or ideas.

Jon

Eddie D

Thank you for your post into the forum.

I have the following answers to your questions:

1. I recommend first off that you become familiar with the comparison and synchronization process using GUI. I personnally have no preferences on using either the Command Line or creating a .NET project. Depends your development skills if you wish to use a .NET project. Using the command line and a XML arguement file is a good way to automate your project. The SQL Data Compare help file / Command line Interface section contains advice on the possible arguements to use.

2. To match rows of data between two databases, SQL Data Compare requires a comparison key for each object.

SQL Data Compare will automatically select a comparison key, if your:

Tables - contain a matching primary key, unique index or unique constraint.

Views - contain a matching unique, clustered index.

If SQL Data Compare is unable to identify a suitable comparison key, the comparison results will inform that the object could not be compared.

You can manually set a comparison key. If you edit your project and select the 'Tables and Views' tab.

This dialog box will list all the tables and views. Locate those objects when the Comparison Key column = NOT SET. Click on the drop down box to bring up the comparison key dialog box and manually set a comparison key. Repeat all the remaining objects were the comparison is NOT SET.

This can be a long boring job if you have a large number of objects. However your project settings are saved, so in theory you should only have to perform this task once.

Also a comparison key cannot include columns whose data type is image, ntext, nvarchar(max), sql_variant, text, varbinary(max), varchar(max), or xml.

This feature was new to version 5 of SQL Data Compare.

I hope the above helps.

Eddie Davis
Red Gate Software
E-Mail: support@red-gate.com

j.salmon

Hey Eddy,

I've pretty much figured (or fumbled ) my way through and figured out the GUI pretty well. I have been able to replicate much of what I need to do through code. Thanks for the help.

Jon