Synchronize SQL Server Databases
e-dule Software has released DB SynchroComp v. 3.4.0, a tool for Microsoft SQL Server 7.0, and 2000, that compares and synchronizes two database schemas.
DB SynchroComp helps you to perform the error prone and annoying job of synchronizing the two database structures.
DB SynchroComp detects the differences between two database schemas and generates script to match the structure of these schemas. At the same time, the DB SynchroComp maintains all data integrity in the database that is a subject of synchronization.
Steps to Synchronized Databases - Product Tour
Before any change to the database schema is made, data in tables affected by the synchronization are saved into the temporary data storage (not in tempdb, but in the current database). After creating the tables with a new internal structure, the data will be loaded back to these database tables.
The program script will execute within the transaction, so if an error in execution occurs, if any (which might be caused by the user interference on the script code), it will not cause any loss of data.
Normally, when you are developing and maintaining information system, you are changing the database structure. Since at the end of the process the 'live' database must have the same structure as the developing one, you could, for example, note all the changes you have made to the developing database and you could manually repeat them to the 'live' database later. Practically, it could be a huge problem. You could lose a lot of time as well as the important data. How can you be sure that all the changes you made to your developing database are made to the live database too? Without the appropriate tool, you simply can't.
DB SynchroComp is the right solution for you. Using DB SynchroComp, the database synchronization task becomes an easy one. All you need is to connect to your databases (or load the source schema, for example, from the db schema file), generate the synchronization script and run it. All your data will be preserved, and both databases will have the same structure.
DB SynchroComp compares two databases, determines the differences between them and generates the script that will change the target database structure to match the source database structure. After you execute the synchronization script, both databases will have the same structure, and the existing data in the target database will remain intact.
DB SynchroComp will not harm the existing data. All the data that were in the target database will remain unchanged. DB SynchroComp will change only the database schema, but not the data. The only case when data will be changed is when creating a new field or changing the field type. If the new column cannot contain null values, if it is a part of a primary key and if the default value is not defined the identity value will be inserted into that column This identity value is first generated like integer identity value, and then converted to underlying column type. With the New fields editor, the user can change this default value by inserting the SQL script that will generate the values for that column.
Some Extra Features
The comparison will be done on an object basis. This means that all the relevant things (indexes, collation order, white spaces, etc.) regarding the database object in question will be considered in the comparison process. There is a possibility to change the default comparison options. It is possible to compare only chosen types of database objects. White spaces or comments might be excluded when you compare the stored procedures or functions. Collation order is a novelty introduced in MS SQL Server 2000 version. Because of that, when you compare two databases from which one is MS SQL Server 7 and the other is MS SQL 2000, the comparison of collation order will be excluded. Since the sysdepends system table might be inaccurate, DB SynchroComp has and relies on its own built-in algorithm for determining the dependent objects. That makes the database schema more accurate, and enables the correct creation of objects in the target database. In order to properly manage the relationships between the objects that do not exist in the sysdepends table, and hence MS SQL Server is not aware of them, with this property of the DB SynchroComp, the user can generate the database script. DB SynchroComp will not affect sysdepends table in any way.
DB SynchroComp is aware of objects created with the schema binding option and knows how to handle them.
Functions, stored procedures etc., can be functionally the same, but they can have white spaces or comments that make them different. There is an option to exclude white spaces or comments from comparing, so only semantically important differences will be detected. Further on, a problem with different collation order by which some columns are created might occur. If two char columns are created with the different collation order, joining tables through these columns will lead to errors. If stored procedure or function contains such joins, even if both procedures are exactly the same (according to the script criteria), the DB SynchroComp will find that functions and procedures are different and warn you that the collation order is different through the comment in synchronization script.
Stored procedures with the same name will be handled appropriately.
You probably tried to change the ordinary type of a user defined type and found out that it is impossible until you delete all the references to that type. Using DB SynchroComp, you can easily solve this problem on the target database, since all the changes made to the source database will be made to the target database too.
If, for some reason, you need your target database to be created in the same file groups like the source database, you can check ‘Include file groups in script’ option. In that case you will have to clarify what you mean by 'the same file groups'. This means that they either have the same name; same logical name or same logical as well as physical name.
DB SynchroComp is able to compare and synchronize objects that are encrypted in the target database. These objects will be encrypted after the synchronization, if the user selects the appropriate option.
Absolutely unique features of DB SynchroComp:
• copes with the object dependences successfully since it does not rely on the sysdepends table, but on its own algorhytm.
• detects all new fields, which do not allow null values and the generation of the sync script will not be possible without defining them first
• Connection Manager (site only: helps you to organize your connections; to classify servers into groups, divide groups into subgroups etc. It has a history of connections for recomparing the databases without reentering the connection parameters)
• extremely simple user interface, reliable and lightning fast!
New features added to DB SynchroComp 3.0.0 are:
• it is now possible to save the database schema in the file and to use it later instead of connecting to the SQL Server
• support for extended properties added
• a possibility of saving the differences report in the txt file added. The basic information about the objects that differ: type, name, side and the difference type (in the tables and views) are included in the report
• sp_helpdb stored procedure could not be executed on all the MS SQL Servers (?). Workaround is made to avoid this problem in the future.
• the script generation option for the entire database is now more intuitive
• handling of the unique constraints added
If you are looking for a professional, high quality, high performance , unique and reliable tool that will help you manage your MS SQL Server database work, use DB SynchroComp. This is not a tool that replaces Microsoft Query Analyzer, or any other Microsoft tool. It is designed with aim in helping database administrators, developers and modellers to keep two database structures synchronized. It is designed to assist the database administrators, developers and modellers in keeping their database structures synchronized.