Useful features of dbForge for administering MS SQL Server databases

Foreword

This article will discuss the useful features of dbForge , which help in administering MS SQL Server databases. Also please note that not all features will be parsed.

I would be very grateful if alternative solutions appear in the comments, as well as additions to this article.

Opportunities

First, consider the synchronization of databases (in the "Database synchronization" panel):


')
1. Comparing schemas of two database instances (both on the same and on different servers):



Here you must specify the source and recipient. After that, immediately press the button "Compare"

1.1. We get the result of the comparison:



On the left it is shown that at the source, and on the right, that at the recipient. Below it is shown that different in databases. It is also possible to tick the option to transfer to the recipient. As a result of the comparison, the whole scheme is divided into four blocks:

- what is not in the receiver (by ticking, you mark the object to be created in the receiver)
- what is different, but it is in both databases (by ticking, you mark changes to transfer to recipient)
- that there is only in the recipient (by ticking, you mark the object for deletion)
- that the same

1.3. At the top right, click the button "Synchronize":



1.4. Here it is important to look at all the tabs on the left and at the end click the "Start" button. It is also better to select “Open script in internal editor”, and on the tab “Options” do not create backup copies

1.5. The created script should be analyzed and applied to the database or immediately to a group of databases where it is necessary.

2. Comparison of data of two database copies (both on the same and on different servers):



Here you must specify the source and recipient. After that, immediately press the "Compare" button. It is worth going to the tab “Compliance” only when the tables have no primary keys and you need to set up a match, and also not to compare everything, but only the specified tables

2.1. As a result, we obtain that it differs in the tables above, and at the bottom, which data differs:



2.2. Next, click on the "Synchronize" button in the upper right:



It is better to select “Open synchronization script in the internal editor”, and on the tab “Options” to remove the creation of backup copies. At the end, click "Start"

2.3. The created script should be analyzed and applied to the database or immediately to a group of databases where it is necessary.

3. Now consider the management of index fragmentation (in the “Administration” panel):



3.1. Here you need to specify the server and database:



3.2. After that, you need to go to "Options" and configure the index optimization mode:



3.3. Next, click on the "OK" button and in the list of indices click the "Analyze" button. Select the desired indices and click the "Repair" button:



3.4. As a result, a list of those indexes will be displayed again, with a higher percentage of fragmentation and the number of pages than specified in the settings on the Options tab.

Result

This article examined some of the features of dbForge for database administration, which allow you to compare different database instances by schema and data. That in turn allows you to identify problems with data replication, as well as problems with desynchronization of database schemas. Also, you can always check and defragment the necessary indexes, if necessary.

Sources:

» DbForge