Introduction
An acquaintance of mine introduced me to dbForge SQL Studio some time ago and I was absolutely blown away by the capabilities of the product. DbForge is an integrated development environment for SQL Server that extends the capabilities of SSMS significantly.
dbForge SQL Studio is particularly impressive to me having also had experience with Oracle Database Administration, where it was typical to use TOAD to interact with Oracle Databases rather than the native SQL Developer that ships with Oracle Database Software. In this article, I will highlight seven simple but interesting features I noticed in dbForge IDE.
Detailed Connection Options
dbForge provides many of the options available in SQL Server Management Studio when connecting to an instance of SQL Server. You can choose the server, authentication mode and whether to save your password or not. But you can do more than this. dbForge allows you to select the exact database you want to connection on the GUI and you can opt to NOT display other databases (See Figure 1). This is a good idea because it can help one avoid errors like running code unintentionally on a particular database because one forgot to change the database context. This error can easily happen if live and UAT databases are in the same instance or if an application has a suite of databases with similar tables such as a transaction and history tables with the same structure.
Figure 1: Basic Connection to SQL Serve on dbForge
dbForge also provides for more advanced users who may want to connect to an instance using a string (See Figure 2).
Figure 2: Advanced Connection to SQL Server on dbForge
Modify an Existing Connection
When using dbForge SQL Studio, you can change the options you selected when connecting to the instance simply by selecting “Modify Connection” from the drop down menu displayed when you right-click the connection name. This allows you to make changes to the existing connection as if you are establishing a new connection but retains the work you have already done in your script for example (See Figure 3).
Figure 3: Modify Connection in dbForge SQL Studio
Limit the List of Databases Displayed
When you uncheck the option to “show all databases” at the point of configuring your connection, you can see ONLY the specific database you opted for. This keeps you interface looking tidy and helps avoid a situation where you accidentally make changes to another database visually (See Figures 4 and 5). In this scenario you can still change database context using the USE clause.
Figure 4: Connect to AU, Show All Databases
Figure 5: Connect to AU, Do Not Show All Databases
SQL Complete and SQL Formatting
dbForge can be considered a complete SQL Manager solutions bundling many features. It also offers great auto-complete capabilities when coding SQL. The basic T-SQL commands are suggested in upper case as you type giving you a much neater output eventually. dbForge also handles indentation quite well as you type code (See Figure 6). I was hoping however that I would get commas automatically when selecting a column list, but this did not happen. Talk about Oliver Twist asking for more.
Most SQL Server database administrators are familiar with the IntelliSense feature in SQL Server Management Studio (SSMS). SQL Complete is a more robust version of this auto-complete feature giving your productivity in SQL coding a dramatic boost. SQL Complete is available as an add-in to SQL Server Management Studio (SSMS) and compatible with Microsoft Azure. You can learn more about the tool here: SQL Complete.
Figure 6: SQL Statement Formatting
Editable Data Grid
dbForge gives you the opportunity to make your result set editable when you execute a query (See Figure 7). This means you can make changes to rows that were returned by a query within the data grid (See Figure 8). These changes are committed automatically. As with SSMS, the result set is also exportable this time with provisions for thirteen different formats (See Figure 9).
Figure 7: Choose to Make Data Grid Editable
Figure 8: Editable Grid
Once again, we should mention that TOAD has similar features in terms of editing the result set and committing the change. This is a good feature, but it is also subject to human error.
Figure 9: Data Export Formats
SQL Server Management Studio also offers export but only a limited number of formats are available. Also notice in Figure 9 above that dbForge will allow one export only a limited number of rows except you purchase the paid version of the tool.
Database Diagrams in DB Forge SQL Studio
dbForge SQL Studio offers an incredibly easy way to develop quick database diagrams using drag and drop. The diagram shown in Figure 9 was developed in about five seconds! It shows tables in the WideWorldImporters sample database and the relationships among them. For each table you drag into the space, the complete column list is populated and the relationships with other tables are shown.
To begin, just go to Database>Database Diagram from the menu bar. You should know that these drawings are developed per database so you cannot have tables from different databases in the same drawing. This is a much easier way to develop database diagrams compared to what is available in SQL Server Management Studio (SSMS).
Figure 10: Database Diagrams
Monitoring
dbForge comes with a dashboard to monitor your instance. Quite a lot of information about the instance are shown on this dashboard. It is displayed by clicking Tools>Show Monitor from the menu bar. The monitor also shows many useful performance counters (see Figure 11). The scope of this monitoring is a lot broader that what is available in Utility Control Point available in SSMS.
Figure 11: SQL Studio Monitor
Conclusion
Devart’s dbForge is a very impressive tool for managing SQL Server instances. The express version of the tool is available for free on the Devart Website. More advanced capabilities are also available for the enterprise versions of the tool. Incidentally, dbForge is also compatible with Microsoft Azure. In my opinion, database administrators, developers and regular users can give their productivity a quantum leap by embracing Devart’s dbForge IDE.
