by Kay Ewbank
Database management software is pretty sophisticated these days, but there’s always room for a helping hand.
HardCopy Issue: 64 | Published: October 30, 2014
Database management servers are large, complex applications, so how can you be sure you’re getting the best out of them in terms of performance and productivity? Many database administrators spend their time just keeping the thing up and running; there just aren’t the resources for fine-tuning performance or documenting changes in structures and schemas.
The big name modern servers come with their own increasingly sophisticated administration interfaces, but alongside the ‘native’ tools and utilities there’s a thriving set of third party software companies that offer tools to help you get the best out of your database. Given the eye-watering price of some of the database servers, your finance director will probably expect the management tools that come with it to do everything, including making the tea. If you’re going to justify spending more money, then the products you’re buying have to be seriously impressive.
Of course, if you’re using an open-source database such as MySQL, the built-in tools are much less impressive, and your company won’t have spent thousands on the server, so you may find it slightly easier to justify the outgoings.
The Toad family of software was originally written for use with Oracle databases, but now has versions for most database servers, and covering a variety of database user roles. In addition to Oracle, databases covered by Toad include SQL Server, IBM DB2 and MySQL, along with cloud databases, NoSQL and Hadoop, extending Toad to modern big data environments. There are versions for everybody from single users to large corporations, and for both specific database servers and heterogeneous environments running different servers.
It’s a tribute to the strengths of Toad that it has very loyal users, particularly on the Oracle platform. There are user groups and forums that are active and helpful, and if you get stuck the chances are you’ll find someone to help.
Toad’s main purpose is to let you create, edit, and format code that works as efficiently as possible. It has automated SQL optimisation and gives you version control, code reviews and reports. If you choose the suite for database administrators, you can create reports on hundreds of aspects of your database performance, schemas and security settings.
No matter which version you work with, Toad has some excellent options. Toad Xpert, for example, will analyse your PL/SQL, T-SQL or SQL code and make suggestions as to how it can be optimised. Suggestions start with any errors, but go much further in proposing ways to make it more readable and easier to maintain. It also offers code-complete and code snippets while you’re writing code.
You can also view execution plans for code that you’re writing, or code that’s already running, and see where there are performance problems. Toad will then suggest alternatives to improve the execution plans. Such performance tuning includes index optimisation and benchmarking.
Toad is excellent if you have to manage many databases, with good tools for change management and task automation. You can compare and synchronise servers and schemas, and the data to track changes, and it’s easy to migrate a database between development, test and production systems. You can execute scripts against multiple servers to repeatedly make the same changes. You can also view a calendar showing all the jobs running on your databases, helping you avoid and sort out job conflicts.
Another strong point for Toad is the way it lets you customise the user interface. It’s easy to add, move and remove items from the menus and toolbar so you get the elements you want and avoid what’s not needed.
You can also organise everything for particular projects – databases and database objects, connections, data models, scripts, specific commands, and housekeeping features such as to-do items and local folders and files – so they’re all visible to an entire project team. Being able to access specific database objects from the main interface can be particularly useful, so that team members don’t have to search the entire database for what they’re looking for.
When you load an object such as a stored procedure or table, you see context-specific metadata such as the object sizes, constraints, permissions, and dependencies. Toad can also be used to roll back transactions based on the server’s log files.
In summary, Toad gives you excellent help with creating efficient SQL. It has a nice visual query builder and good custom reporting, and overall, it’s a mature and very fully functioned product.
CA ERwin Data Modeler
ERwin Data Modeler is a tool for designing database schema that has moved increasingly into the area of database management over the years because of its well-designed visual view of the databases that you’re managing. It has a reputation of being expensive but very good at the data modelling options it offers.
Taken purely as a data modeller, you can use ERwin to design a logical data model, and then generate the database schema from your design. ERwin supports a wide array of database servers for which you can generate database schema.
You can also create a data model from an existing database, and the model you create contains a lot more than just the data structures; it can define your stored procedures and triggers too. One strong point of ERwin is that it will keep your development database and your production database’s structures synchronised. If you make a change in development, ERwin will create Alter scripts that change your production database to match.
ERwin comes in a range of versions from the free-to-use Community Edition all the way up to Workgroup Edition for collaborative design by teams of modelers. It is very popular with large enterprises. One reason for this is the fact you can use it to manage everything from a departmental database all the way up to a data warehouse. It has tools that let you link multiple databases to create a data warehouse, and also for working with warehouse fact tables to make the information available to business users.
An increasingly important role for ERwin is as a data visualisation tool. The ERwin Web Portal is a web-based interface that you can customise to show users the metadata information that you’ve created in the Data Modeler. You can set up different views for different types of user so they see only the information relevant to their job role. Database structures are displayed graphically using blocks and lines to show the tables, columns, indexes and relations between the objects in your database.
Alongside the web portal, ERwin has a built-in report designer. The report designer can be used to create HTML and text-based reports for diagrams and metadata which can either be exported or viewed within ERwin using its grid browser. ERwin comes with an embedded copy of Crystal Reports that your business users can use either with the predefined reports or via their own customised versions.
A recent addition to the ERwin line-up is Data Modeler for SQL Azure. This can be used to manage Microsoft SQL Azure-only environments, or as an add-on to an existing edition of CA ERwin Data Modeler to show data from both local and cloud-based databases.
ERwin is particularly useful in large heterogeneous environments where its data modeling helps manage large numbers of databases.
Red Gate SQL tools
Red Gate’s collection of SQL tools can be bought individually or as collections in the form of SQL Toolbelt (for developers) or SQL DBA Bundle (for database administrators). At the heart of both collections is SQL Compare, a product that can save you weeks of work by letting you compare everything in one database to everything in another database. SQL Compare works through all the database objects such as tables, views and stored procedures, shows you the differences between the two databases, and generates scripts to make one database the same as the other. The databases being compared can both be live production databases, or they can be database snapshots, SQL scripts in source control, or backups. Although Red Gate SQL Compare started life as a product for SQL Server, there is an Oracle version that can be used for similar tasks in Oracle.
SQL Compare is easy to use, and if you’re using the SQL Server version you can install it as an add-in for SQL Server Management Studio (SSMS) so you can use its features from within the main management tool. Once you’ve compared two databases, the tool generates a set of scripts that you can view and edit. You can either run the scripts or cut and paste into your own SQL code. You can also generate reports showing the results of the compare operation.
Alongside SQL Compare for schemas, Red Gate also has SQL Data Compare which performs a similar role for the data in your databases. You can work with live databases, backups or SQL scripts, and can replace missing or changed data down to the row level without having to carry out a full database recovery.
There’s a sizeable overlap in the products that come as part of Red Gate’s SQL DBA Bundle and SQL Toolbelt, the bundle aimed at developers. Both include a backup utility which can generate compressed backups that are verified and encrypted. A key element in both is the performance monitor which lets you monitor the servers that are running, and alerts you to problems when they occur.
The web-based interfaces have a good collection of built-in alerts and criteria that can give you a full picture of your server health. You can set thresholds at which alerts should be generated, and the alerts cover areas such as long-running queries, blocking and deadlocks, fragmented indexes and cluster failovers. You can also set alerts for overdue maintenance such as backups and integrity checks. The health of your servers is monitored using both Transact-SQL queries and system information.
Another useful product that comes in both bundles is SQL Prompt. This provides code completion for SQL Server editors, with keyword formatting and the ability to integrate code snippets. You can set up shortcuts for SQL phrases you use frequently, and define code snippets for your own use or for sharing with team members. The refactoring support in SQL Prompt means you can rename objects in your code, and the name changes will be made in such a way that any code depending on those names continues to work. You can also encapsulate SQL code as stored procedures.
The other products in the bundles cover such things as documentation generation, running scripts to multiple databases, and generating test data. All in all Red Gate provide a simple-to-use and effective set of tools.
Embarcadero has a number of database management tools, the best of which come in two editions of DB PowerStudio for SQL Server, namely Developer Edition and DBA Edition. Developer Edition includes a SQL editor, database optimiser and change manager, while DBA Edition adds the administration tool DBArtisan along with the enterprise edition of the change manager.
Embarcadero’s SQL editor is Rapid SQL for SQL Server. This is a well-designed visual tool that you can use to create and modify SQL code. It will automatically correct code that’s inefficient, and has a ‘quick fix’ flag that suggests modifications to problem code. Your code is validated as you write it, and there’s a code assist feature to help you complete statements. Rapid SQL also has a visual query builder for point-and-click query creation.
Alongside Rapid SQL you get DB Optimizer for SQL Server. This is an optimisation tool that will discover and optimise poorly performing queries within your database server.
The final element of the Developer Edition of PowerStudio is DB Change Manager for SQL Server. As the name suggests, this tracks changes to your databases, comparing differences to schemas and data between different SQL Server databases.
The enterprise version, which is included in the DBA bundle, adds a wizard that will generate batch or ANT scripts that you can save and run on pre-defined schedules. The other element of the DBA version of PowerStudio is DBArtisan for SQL Server, an administration tool that makes it easier to administer SQL Server using graphical editors and wizards.
Although the version included in PowerStudio is for SQL Server, DBArtisan is also available on its own for other databases including IBM DB2, MySQL, Oracle, Sybase, PostgreSQL and SQL Azure. It can also be used for cross-platform database management. The bundled version is the top-end Workbench Edition, but Standard and Professional versions are also available.
DBArtisan’s most ardent supporters are those who have to manage heterogeneous databases. If you have SQL Server, MySQL, and Sybase all running in your organisation, for example, that could mean three interfaces to learn and use. DBArtisan’s fans argue that you are better off using just one product that’s better than all three. My own view is that if you’ve already got to grips with Oracle’s Administration Assistant or SQL Server’s SMSS, then you’ll find them equally as usable as DBArtisan. Where it really scores is when you are working with a database server that you don’t know as well as your ‘home’ environment.
DBArtisan combines database management with the ability to develop SQL for multiple database servers in a single environment. You can write and execute SQL code from the SQL development window, and debug problems, and if you want to avoid work there’s also a graphical query builder. There’s a colour-coded SQL editor that lets you step through your code, and you can run a SQL query against different target databases at the same time if necessary.
On the management front, DBArtisan’s analysis tools let you monitor performance, analyse your storage use, and carry out capacity planning and trend analysis. There’s a good Database Search Wizard that you can use to find objects across database servers, and the VisualDiff tool makes it easy to compare database objects. If you’re managing SQL Azure alongside other databases, DBArtisan makes it easier to treat it just like any other server, especially when moving data between local servers and Azure.
Users claim the point and click nature of the interface is one of its strong points. There’s a graphical explorer that shows you the contents and configuration of your data sources, and you can use it to connect, disconnect and browse the objects in all of the supported data sources on your network. This extends to operations such as moving a database from one type of server to another, either as an entire database or by choosing a subset of the schemas and tables.
DBArtisan helps you work out where performance problems are happening, letting you view the load that a database is experiencing as a graph so that you can see which code is taking the longest and would benefit from tuning. There’s also a SQL Debugger that lets you step through your code as it is being executed to see where any errors occur. You can view the expanse of each line of code to find potential bottlenecks, watch the values of variables, see dependencies, and view the call stack for stored procedures, triggers and functions.