dbForge Team Blog

Data comparison is a difficult and resource-intensive process. For convenience, this process can be divided into several steps.
First, you should compare tables from one database on one server with the database on the other server. You should choose columns for data comparison, and also choose a column that will be a comparison key.
The next step is to choose all data from these tables or some specified part of the data.
The third and the most important step is comparison of the two tables by the selected comparison key itself. During this process the status of each record is set to “only in source”, “only in target”, “different”, or “equal”.
The final steps of the data comparison process are including records to the synchronization and synchronization itself. During these steps records needed for synchronization are chosen, update script is created, and after that the script is executed.
You can read a detailed description of the comparison process here.

Now let’s look at the third step (data comparison) thoroughly.

There are several ways of data comparison that differ only by the side where data comparison is going to be performed – on the server side or on the client PC.

Data comparison on the server side is performed using the resources of the server.
The algorithm of comparison is the following:
1. For each record of each of the two tables its checksum is calculated;
2. Then the checksum of every record from one table is compared to the checksum of the corresponding record from another table and conclusion if the records are equal or different is made;
3. The comparison result is stored in a temporary table on the server.

Performance indicators:
1. The speed of data comparison directly depends on the server capacity and occupancy;
2. The maximal size of database for comparison is limited by the resources of the server itself.

Advantages:
1. There is no need to transfer large amounts of data for comparison to the client PC through network. This way we save network traffic;
2. The speed of comparison does not depend on the client PC resources;
3. Ability to compare blob data of any size.

Disadvantages:
1. Because of the record checksum calculation algorithm in some cases different data can result in equal checksum, and instead of the expected “different” status the “equal” status will be received;
2. There is no flexibility in the synchronization and comparison options usage;
3. There is no possibility to view records differences and exclude a part of the records from the synchronization manually;
4. During the synchronization script creation you should perform data transfer from the server to the client side;
5. The control checksum calculation of a large amount of records consumes all server resources;
6. One should provide extra space on the server for the comparison results storage in the temporary table.

As we can see, this way of comparison has more disadvantages than advantages, that’s why this way is rarely used.

Data comparison on the client PC is performed using the client machine resources, and the server only provides data for comparison. In turn, this way of comparison can be divided into several more ways depending on the way how comparison information will be stored.

Comparing Data on local PC when comparison result is stored in RAM.
The comparison algorithm is the following:
1. Server passes all data from both tables to the local PC;
2. Every record of every table is placed to RAM and is compared without checksum calculation;
3. If a record gets “only in source”, “only in target” or “equal” status, only comparison key is stored in RAM. If records get “different” status, they are placed to RAM for storage completely.

Performance indicators:
1. The speed of data comparison directly depends on the client PC resources and on the speed of data transfer through the network;
2. Maximum size of the database for comparison is limited by the size of RAM on the client PC, and this maximum size also depends on the degree to which the databases that should be compared are different – the smaller is the amount of different records, the larger databases can be compared.

Advantages:
1. Minimal server occupancy – server performs only simple data selection;
2. The simplest algorithm of data comparison because records are sorted on the client side;
3. Flexibility in the comparison options usage;
4. Minimal size of the comparison data store;
5. Status of every record for any data is always correct.

Disadvantages:
1. To view records with “only in source”, “only in target”, or “equal” status an extra data selection is needed;
2. An extra data selection is needed to create a synchronization script;
3. OutOfMemory Exception may be arisen when there are a lot of differences in data in databases;
4. Possibility to compare blob data only of the size that equals to the size of free RAM.

This way of comparison is implemented in dbForge Data Compare for SQL Server v1.10, dbForge Data Compare for MySQL v2.00 and allows to compare databases of any size if data in these databases does not differ a lot.

Comparing Data on local PC when comparison result is stored as a cashed file on the disk.
The algorithm of comparison is the following:
The server passes all data from both tables sorted by comparison key to a local PC. Data is read by bytes, compared without checksum calculation and written to a file on the disk.

Performance indicators:
1. The speed of data comparison directly depends on the client PC resources and on the speed of data transferring through the network;
2. The maximum size of a database to compare is limited by free disk space and does not depend on the degree of data difference in databases.

Advantages:
1. Medium server occupancy – server performs data sorting and selection;
2. To view records and synchronization script creation extra requests to the server are not necessary;
3. The status for every record is always correct for any data;
4. Possibility to compare blob data of the size equal to the size of free space available on the disk.

Disadvantages:
1. Difficult algorithm of data comparison for the records comparison key of which is of the string data type;
2. Difficult algorithm of disk cash for temporary information storage creation.

We can see that in this case the only disadvantage of this way of comparison is the difficulty of implementation. There are more advantages than in the ways of comparison listed above. That’s why this way of comparison will be used in the new version of dbForge Data Compare for SQL Server v2.00 and dbForge Data Compare for MySQL v3.00 for data comparison.

The most important characteristic of any software is its quality. The quality, according to the model specified in the ISO 9126 standard, consists of the following characteristics:

• Functionality
• Reliability
• Ease of use
• Efficiency
• Maintenance
• Portability

Of course users are not concerned about any quality models and just tell us their suggestions and wishes. It’s these suggestions and wishes that are the most preferred description of any quality characteristic.

Below we give the list of main aspects we paid special attention to when developing dbForge Schema Compare for SQL Server in order to deliver software of the highest quality to our users.

• Dependencies. One of the key difficulties during object synchronization is caused by dependencies between objects and inside them. These dependencies are built basing on the information received from the server, but frequently there is not enough of it. To simplify the situation, we’ve implemented and tested a parser that looks for missing dependencies. Owing to the parser, all existing dependencies between database objects are found.
  So users can be sure that dbForge Schema Compare for SQL Server will be able to synchronize any object regardless of the number of objects it is referenced by, how they reference it, and the objects it references.
• Describe queries optimization. After we implemented support for all scheduled objects, we’ve decided to study describe queries properly (these are queries sent to the server in order to obtain object information) and, if possible, optimize the speed of their execution in order to avoid performing extra actions and to do the necessary ones as quickly as possible. During the work, we have found many defects and errors, rewrote some queries or their parts that increased the describe productivity several times.
  So users can be sure that dbForge Schema Compare for SQL Server does not perform any unnecessary actions and does not spend his or her precious time as well as server and desktop resources.
• Load Testing (Performance Testing). After query optimization a goal to learn full capabilities of dbForge Schema Compare was set, and we decided to make load testing. To perform such testing we have created an application that could generate scripts of different objects in unlimited numbers according to the preset template. Due to the load testing we can claim that dbForge Schema Compare for SQL Server can manage to synchronize large amounts of data.
  So users can be sure that dbForge Schema Compare for SQL Server will cope with synchronizing large amounts of objects, comparing large backups, generating large synchronization scripts etc.
• Autotesting. Because of the constant growth of the amount of data for testing and because of the growth of requirements for product quality we’ve decided to create autotests. To accomplish this task we’ve created a special application that integrates into dbForge Schema Compare for SQL Server and performs preset actions automatically. For example, it creates specified databases, generates synchronization script, checks if the script was generated according to the model. For the time being there are approximately 4000 of such autotests, and their amount is constantly growing. These autotests are performed for each build.
  So users can be sure that every build of dbForge Schema Compare he or she downloaded became more reliable and stable.
• Using users databases for testing. We have a set of databases sent by our users. Some users send us their own databases to help us reproduce a problem they encountered and fix the bug. We perform thorough application testing on users’ databases and make a new build with fixes of all bugs, if any were found.
  So users can be sure that, when synchronizing his or her database all bugs found in dbForge Schema Compare for SQL Server will be fixed and the needed database will be synchronized successfully.
• Usability testing. The convenience of dbForge Schema Compare for SQL Server usage also means a lot to us. That’s why we discuss the ways of improving UI and its elements. We often use the experience of Microsoft, a member of Cambridge Usability Group that has its own laboratories for studying such problems and is an absolute leader in usability questions. The new interface is created according to the Microsoft recommendations – for example, arrangement of elements in wizards, dialogue windows, the structure of main and popup menu etc.
  So users need minimal time to learn how to work with dbForge Schema Compare for SQL Server, and, of course, it looks very familiar to the Microsoft Visual Studio users.
• Configuration testing. We’ve performed configuration testing on the operation systems of the Windows family (2000, XP, 2008 Server, Vista, 7) of different capacity (x32 and x64), and on different SQL Server versions (2000, 2005, 2008 including Express Edition). Also dbForge Schema Compare for SQL Server was tested on different hardware – processors, monitors etc.
  So users can be sure of the reliability of the dbForge Schema Compare for SQL Server work regardless of the kind of hardware, OS, its capacity and settings, server version and edition he or she is using. dbForge Schema Compare for SQL Server also works with large fonts that is very important for disabled users.
• Help. This is the only document where you can find descriptions of all options and settings. Full and comprehensive product description is guaranteed by thorough testing performed by our specialists. For example, to get help on any comparison or synchronization option, it’s enough to press F1 in the window it is situated in, and find it in the help window that was opened.
  So users can be sure that, owing to the full and comprehensive help system of dbForge Schema Compare for SQL Server he or she will be able to find an answer to any question and learn the product functionality in the shortest period of time.
• Support. We appreciate users’ wishes, because we are working for them. No user wishes or suggestions are ignored. In dbForge Schema Compare for SQL Server v 2.0 all users’ wishes received since its first release were taken into account.
  So users can be sure that we appreciate and implement their wishes and are looking forward to receiving feedbacks to make dbForge Schema Compare for SQL Server even better.

Schema Compare for SQL Server 2.00 compares schema snapshots, database backups, and database changes in version control systems to accelerate the work of advanced database developers.

Devart today announced the public availability of  Schema Compare for SQL Server 2.00 that delivers new capabilities to help customers more effectively compare databases and more quickly migrate schema changes across different SQL Server versions.

Featuring new functionality, the tool is better equipped to span a full spectrum of database comparison and synchronization tasks and allows completing them at a professional level.

The highlights of Schema Compare for SQL Server 2.00 include:

• Support of native SQL Server backups. The users can use native SQL Server backups as a metadata source
  and compare them. It adds a new way to compare and update database structures without manual work.
• Database comparison and synchronization via command line. This is especially useful for the users, who do repeated schema comparison and synchronization. It is enough to set a comparison once
  and the tool will prepare a file with required command line arguments – Source and Target
  connection strings, comparison options and settings. The users can schedule the launch time
  for the processes specified in the file and escape any waiting.
• Tracking database changes using version control systems. This prevents several developers from changing database objects simultaneously. Now it is easy to revert
  to earlier revisions and quickly identify who and when changed the objects. Comparison takes
  less time as schema snapshots are compared. dbForge Schema Compare uses two most popular version
  control systems — SVN and TFS.
• Generating comparison and synchronization reports. Now publishing differences between databases is automated and offered as clear and professionally looking reports.

• Synchronization of database properties. A database will be synchronized as an object and you will see what changes have been made to the database.
• Character-oriented comparison. Even a single different character is highlighted in the text compare to save the user’s time and efforts.
• Standard and Professional product editions available. You can select only required tools and save your money.

Check the benefits yourself, download dbForge Schema Compare for SQL Server 2.00 now for free.

Tell us what you think about the new version at the product feedback page. We are looking forward to your comments and suggestions.

Devart today previewed dbForge Studio for MySQL 4.00, scheduled for release this December, and announced the immediate availability of a beta release that supplies database-dealing people with the very data management capabilities they want.

Having accumulated extensive knowledge in data management preferences and routines, Devart has created dbForge Studio for MySQL 4.00 Beta that predicts and delivers expected capabilities in any phase of dealing with data. The users can manage the data from soup to nuts in one place and with the same little effort as usual.

The highlights of dbForge Studio for MySQL 4.00 Beta include:

• Data Import. Guided by a click-and-pick wizard, the users can easily import data from seven widely-popular formats (CSV, DBF, MS Access, MS Excel, ODBC, Text, XML) with a multitude of settings to meticulously tune the data.
• Improved Data Editor. Up to 40 improvements were implemented to create quicker, easier, and more delighted ways for data management. The Data Editor spares tens of mouse clicks a day for you saying nothing of tens of frowns.
• Pivot Grid. The users can convert large amounts of data into compact and informative summaries – pivot tables. They can rearrange any obscure data by a simple drag of a mouse to get the layout best for understanding data relations and dependencies.
• Data Reports. Every user can benefit from a modern way of generating reports. Less time and manual work is required – from getting required data from a database, analyzing, to printing it as a smart, clear, and stylish report tuned for each particular case.
• Virtual Relations on Database Diagram. Besides physically existing foreign key relations, dbForge Studio for MySQL allows creating virtual relations. They help create relations between those tables which storage engine do not support foreign keys. Later virtual relations can be easily converted into physical foreign keys.
• Command Line Launch for Data and Schema Synchronization Tools. This gives more freedom for the users who perform repeated synchronizations. It takes only to set a desired time to launch the process and then come back to check the desired result at a proper time.
• Schema Comparison Reports. Keeping records of schema changes is now automated. dbForge Studio for MySQL can generate a clear and attractive comparison reports upon the comparison completion.
• Improvements to Better Serve Specific Users’ Needs. The new version contains many essential improvements that increase the efficiency in working with data. Here are some of them: addition of active database selector to SQL Document, SQL formatting improvements, redesign of stored procedure/function editor and SQL execution parameter editor.

Download a fully-functional 30-day trial version of dbForge Studio for MySQL 4.00 Beta and add its features to the arsenal of your tools.

Tell us what you think about the new version at dbForge Studio for MySQL Feedback Page.

We are looking forward to your comments and suggestions.

Need to move databases from SQL Server 2000 to SQL Server 2005 – 2008 or vice versa?

dbForge Schema Comparer for SQL Server can successfully do any of these tasks. This tool generates a script in the same syntax that is used by the Target database. For example, if the Target database is on SQL Server 2000, User-Defined Data Type in the script will be written like EXEC sp_addtype ‘State’, ‘bit’, ‘NOT NULL’, whereas for SQL Server 2005-2008, it could be CREATE TYPE dbo.[State] FROM bit NOT NULL.

Different syntax

But how about some data types and objects appeared in latter SQL Server versions? For example, tables containing columns of time data type have no matches in SQL Server 2000.

How will they be converted? Such objects will get Inconsistent status and won’t be included into synchronization.

Inconsistent Object

Some objects, mainly containing custom code, such as stored procedures and functions, may have incompatibility in their syntax. This will required conversion of Target database into other compatibility level, for example, 80. dbForge Schema Compare for SQL Server doesn’t control incompatibility of such objects, so you need analyze your databases for these conditions before synchronization.

Learn more and get a free 30-day evaluation of dbForge Schema Compare for SQL Server at http://www.devart.com/dbforge/sql/schemacompare/download.html.

Recently we’ve got a letter from a customer asking how to alter all float datatype to double datatype in one “Find and Replace all” shot. Good news! dbForge Studio can do this.  Let’s see how Find and Replace functionality,  Schema Compare tool,  and a database project of dbForge Studio will do their best to change the required properties at a heat.
Read the rest of this entry »

Preserving data in tables after synchronization is one of requirements for successful schema synchronization. dbForge Schema Compare for SQL Server offers two mechanisms of preserving tables data during synchronization, they are warning the users about data loss and restoring data in recreated objects.

But it is not all, dbForge Schema Compare for SQL Server goes on and offers table data verification during synchronization.

How it works

Read the rest of this entry »

Devart, a vendor of native connectivity solutions and development tools for Oracle, SQL Server, MySQL, PostgreSQL, InterBase, Firebird, and SQLite databases, has announced the release of dbForge Schema Compare for SQL Server 1.50, a sophisticated tool specially designed to meet a diversity of comparison tasks, help analyze schema differences at a glance, and synchronize them correctly, saving time and efforts.

With the new release, Devart continues its dedication to providing a line of safe as well as powerful tools for SQL Server database synchronization.

The highlights of Schema Compare for SQL Server 1.50 include:

* Table data verification after synchronization

dbForge Schema Compare for SQL Server 1.50 moves forward in delivering safe synchronization. The present-day market, saturated with all sorts of speedy over-featured tools for schema comparison and synchronization, leaves many developers insecure while managing their schemas. This proves that capability to synchronize any types of schemas does not always guarantee the correct results, and speed is nothing if any data is lost.

Following best practices in database synchronization, dbForge Schema Compare for SQL Server 1.50 checks table data after synchronization and notifies if any data loss has happened. This approach eliminates stumbling blocks on the table modification path giving more control and safety. With data verification capability developers can be sure that table changes as common as table recreation or changing data types in table columns are protected with timely detection of any data losses.

* The full comparison and synchronization – all database objects are supported

The latest version of dbForge Schema Compare for SQL Server can compare and synchronize all database objects in SQL Server 2000, 2005, and 2008 databases preserving high performance and safety. The expanded object support facilitates cross-version comparison and exempts developers from limitations based on the list of supported objects.

* Support of schema snapshots

One more major novelty in dbForge Schema Compare for SQL Server 1.50 is capability to make schema snapshots and compare them with each other or with a live database. Snapshots open up possibilities to develop databases without a direct connection to SQL Servers saving time and traffic. In addition, snapshots greatly contribute to version control of databases, producing a simple way for any member of the development team to compare and analyze the changes made to the database.

Pricing and Availability

Download dbForge Schema Compare for SQL Server 1.50 now and check the benefits yourself.

The license price of dbForge Schema Compare for SQL Server 1.50 is $149.95, it comes with a free 1-year technical support.

Go to the Feedback Page and tell us what you think about the new version. We are looking forward to your comments and suggestions.

Devart announced the release of dbForge Query Builder for MySQL v 1.10, a professional tool for quick creation of any queries without code and extended management of query results. The highlights of Query Builder for MySQL v 1.10 include:

Unlimited database connectivity

To provide more flexibility, dbForge Query Builder for MySQL v 1.10 offers two more approaches to connect to a database in addition to a direct connection. When you can’t connect to the MySQL Server directly or need secure transmitting of private data, you can leverage secure connections based on SSL and SSH protocols.

The latest version takes care about web developers working with remote databases and offers HTTP tunneling with simple tunneling script uploading to the web site. Employment of remote connections becomes as simple as local ones. No need to remember multi-step procedures using other tools.

Efficient traffic consumption

In contrast to the prior version dbForge Query Builder for MySQL v 1.10 reduced network traffic up to several times for performing such common tasks as opening connections, managing tables and views, retrieving data from tables, etc. This optimization makes possible to use dbForge Query Builder for MySQL in cases of traffic limitations.

Support of correlated sub-queries

While building or executing queries, you can use correlated sub-queries and be sure they are evaluated correctly.

Optimized approach in working with large SQL scripts

dbForge Query Builder for MySQL v 1.10 provides a special Execute Script Wizard to enable quick and convenient execution of large SQL scripts without waiting for their opening in the editor. It takes only to select the script and enjoy the executed result.

Other improvements

A number of bug fixes and minor improvements have enhanced the product performance targeting better users’ satisfaction.

Download dbForge Query Builder for MySQL 1.10 and expand your freedom while working with MySQL databases.

Use the dbForge Query Builder for MySQL Feedback Page to tell us what you think about the new version. We are looking forward to your comments and suggestions.

Optimized tool facilitates database developers and web masters to efficiently work with remote databases at a lower cost with a greater speed.

Devart today announced the release of dbForge Studio for MySQL v 3.60, a cutting-edge tool for administration and development of MySQL databases.

The new upgrade is the contemporary answer for common bottlenecks originated from employing remote
MySQL Servers while developing, updating, and using modern large databases. dbForge Studio for MySQL v 3.60 offers new freedom for those who work with large schemas and data amounts especially in cases of using remote MySQL servers, for example serving web sites.

Unlimited database connectivity

It is good news for web developers working with remote databases, as employment of remote connections becomes as simple as local ones. dbForge Studio v 3.60 offers totally redesigned HTTP tunneling with simple tunneling script uploading to the web site. No need to remember multi-step procedures using other tools. To provide more flexibility while using SSH connection, public key authentication is supported.

Higher performance on large databases

Having passed multiple performance tests on large databases with all database objects, including hundreds of tables, thousands of records, possessing various data types, dbForge Studio for MySQL v 3.60 proved marked performance improvement as compared with the previous version and beneficial leading among competitive programs.

The results of performance tests show that dbForge Studio for MySQL v 3.60 combines high performance with accuracy and correctness to ensure high quality and efficient database management. For example, dbForge Studio v 3.60 has 3 times faster data comparison and 4 times faster schema export compared to the previous versions.

One more improvement is asynchronous execution of stored procedure. It is a great benefit when working with slow procedures.

Optimized database connectivity

In contrast to the prior version dbForge Studio v 3.60 reduced network traffic up to several times for performing such common tasks as opening connections, managing database objects, performing schema and data export, retrieving data from tables, executing procedures or stopping the execution. This optimization makes possible to use dbForge Studio v 3.60 in cases of traffic limitations.

Download dbForge Studio for MySQL 3.60 and expand your freedom while working with MySQL databases.

Use the dbForge Studio for MySQL Feedback Page to tell us what you think about the new version. We are looking forward to your comments and suggestions.