Maintaining control
By Kay Ewbank
Kay Ewbank finds out what tools are available to make the database administrator’s job easier.
HardCopy Issue: 40 | Found In: Database | Published: 01/05/2008 | Last Revision: 06/07/2010
Databases get ever more sophisticated which means they pose an ever increasing variety of management problems. If you were to show a database administrator from ten years ago the current incarnations of Microsoft SQL Server and Oracle, for instance, they’d be amazed. Features such as unstructured data, built-in business intelligence (BI) and graphical query environments add greatly to what can be achieved using just the database but, as with most things, the more features there are on offer, the more there is to control. If you’re using a database such as Oracle 11g which can spread itself across a grid of computers, how can you be sure everything is working as it should do? If you’re using SQL Server’s great BI facilities, how can you be sure you are getting the best performance?
Of course, modern database systems come with their own database management tools, and these themselves become increasingly sophisticated, so we’re starting with a look at the database administration tools that you get with Oracle Database 11g and Microsoft SQL Server 2005.
Any add-on needs to provide something more than you’d get with the standard server, and each of the add-ons we’ve looked at does just that. Some are simpler and less confusing than the increasingly sophisticated standard interfaces – if you don’t want to know how to manage a grid or set up business intelligence, a small, simple interface has a lot going for it.
Another advantage offered by add-ons is that you don’t have to buy extra database server licences if you have multiple administrators or power users who need to be able to work directly with tables and views. The final plus is that most add-ons look the same no matter what database server you’re working with, so it’s easy to switch between administering a MySQL database and an Oracle database, for example. Some even let you administer more than one server type from within a single version: for others you’ll need to buy separate copies for each server type you want to administer.
Microsoft SQL Server
Management Studio is the main administrative console for both database administrators and developers that comes with SQL Server. It runs within a Microsoft Visual Studio interface, and while this makes it ideal for use by developers, administrators also get to use good graphical tools for database management.
There’s an Object Explorer that you can use to view and select the objects in your database, and from where you can carry out actions such as viewing query plans and optimising database performance. Management Studio is where you can create new databases, tables and indexes, or edit existing ones. You can create and run queries and scripts and there are editors for Transact-SQL, MDX, DMX, XMLA and SQL Server 2005 Compact Edition queries. The designers let you drag and drop tables to select what should be included in your query, and to select columns for inclusion.
Another administration element in SQL Server is the Profiler. This is a tool that you can use to capture events from your server, so you can analyse problems such as slow-running queries.
Overall, the administration facilities in SQL Server are very good in as far as they go, but you don’t get the cross database administration or some of the more sophisticated optimisation features of the standalone products.
Oracle Database 11g
Oracle is well known for having the tools to let you do pretty much anything with your database – so long as you have the knowledge and experience to know how. However, Oracle Database 11g has been designed so that many of the tasks traditionally carried out by database administrators are now done automatically through Oracle’s self-managing capabilities.
In practical terms, you administer Oracle databases mainly from Oracle Enterprise Manager. This has sophisticated tools such as proactive management which alerts you of potential problems before they cause major upsets. You can also use Oracle’s performance analysis to see how well the database is running, carry out maintenance, set up users, create and modify databases and the objects within them, and carry out performance tuning. For example, there’s a SQL Tuning Advisor that runs automatically as a maintenance task. Each time it runs it automatically selects high-load SQL queries in the system and generates recommendations on how to tune them.
Enterprise Manager has made a huge difference to the ease with which you can administer Oracle databases, and it’s a very easy to use tool.
Altova DatabaseSpy 2008
Altova DatabaseSpy shows off its graphical Design Editor.
If you need to manage more than one type of database then Altova DatabaseSpy is an option worth considering. Described as a multi-database data management, query, and design tool, you can use it with all the major databases. It has a wizard that can be used to set up connections for IBM DB2, Microsoft SQL Server, Oracle, MySQL, Sybase and Microsoft Access, and you can also build your own ADO and ODBC connections.
The wizard is more or less a front end for the Windows ADO/ODBC connection dialogue. The advantage you get over the basic dialogue is that DatabaseSpy keeps the connections organised with the project files with which you’ve used the connection, and you can open connections to more than one database if necessary. DatabaseSpy 2008 then displays the tables, views, stored procedures and any data you’ve returned through queries or views in a set of panels.
If you want to create a new database or change the structure of an existing database, DatabaseSpy has a Database Design Editor in which you can work with the objects in your database and their relations. You get a graphical panel in which you can drag and drop and point and click, and an SQL window in which you can either type SQL directly or view (and save for later) the SQL equivalent of your pointing and clicking.
You can add new tables, edit existing ones, or even just use the Design Editor to see the structure of a table that is already present. If you want to create a new database then you can duplicate existing databases, and a nice touch here is the ability to take a structure from one server type and replicate it in another. This is very useful if you’re planning on changing database server.
There’s an SQL Editor that you can use to create queries, execute SQL statements and even edit the underlying data. It’s less point and click oriented than the Design Editor – you have to type the SQL so you need to have some vague idea what you’re doing. You can open existing SQL files and either execute or edit them, or you can right-click on a table and choose to retrieve the data as a starting point for a query, but that’s more or less as far as the pointing and clicking goes. The Editor helps with SQL syntax by providing code completion and syntax colouring, but this is definitely a tool that gives people who know SQL a quick way to create SQL scripts, rather than offering suggestions about clauses.
Other facilities in Altova include the ability to inspect XML data in database tables and to manage the XML Schemas used to validate the XML data.
Toad
There are several versions of Toad aimed at different database servers and different types of potential user. Toad is available in versions for IBM DB2, SQL Server and MySQL, but is probably best known for its range of Oracle versions. Depending on the database server, you can choose versions for development, SQL tuning, code building and database administration. We looked at the administration version. Toad has plenty of reasons to be popular, including the fact that you get a ‘ribbit’ when you run it. There aren’t many smiles on offer in database administration, so we should take what we can get!
Building a database script in Toad for Oracle.
Toad has separate products for each server type you want to work with, though the interfaces are very similar across versions and some parts of the suite can be used with multiple server types. The basic product for all servers is Toad which provides the means to build and maintain database scripts in whatever is the default language of the server (PL/SQL and standard SQL in the case of Oracle). Toad lets you create and manage database objects, and has a visual designer in which you can create queries and execute them. You can profile your code to check there are no bottlenecks, and you can record your actions and play them back later. There are facilities for team working so you can share elements of your projects.
The extension to the basic Toad product is Toad for Oracle Professional. This adds CodeXpert, a utility that you can use to automate code quality reviews. The code quality is based on rules from coding experts, and the end result is a quality and performance scorecard. The other main addition in Toad Professional is the ability to import and export information such as objects and data from an Oracle export file, rather than having to import or export the entire file.
Toad for Oracle Xpert is the next member of the family, adding an SQL scanner from SQL Optimizer. This will identify any SQL statements that are badly optimised. It then rewrites the SQL for you, and will also suggest other ways to improve performance. Finally, Toad for Oracle Xpert can be used to create virtual indexes for better code execution.
We tested the Toad DBA Suite for Oracle. The suite is made up of Toad for Oracle Xpert with the DBA Admin module, Spotlight on Oracle, Toad Data Modeler and BenchMark Factory.
The DBA Admin module automates administrative tasks and creates a workflow based on your actions that you can use to move between tasks. You can view the performance of all your databases in your Web browser, and can see the performance right down to user sessions. You can also navigate across multiple databases and perform object management with the view Oracle Performance Metrics using Oracle’s StatsPack Browser or AWR (Automatic Workload Repository) Browser.
Toad Data Modeler works with a variety of database servers, and as the name suggests, provides data modelling facilities. You can generate both logical and physical entity relationship models, synchronize models and reverse engineer any legacy databases you need to maintain.
Spotlight on Oracle Standard Edition provides another route for fixing Oracle performance bottlenecks. It gives you a graphical image of where problems in your database lie and whether those problems originate with a particular user, a particular SQL transaction, waiting for a lock to clear or similar snags.
Finally, Benchmark Factory for Databases lets you replay workloads and carry out scalability testing by simulating users and transactions on the database.
Toad DBA Suite for Oracle is a large suite giving you lots of ways to manage your databases and ensure they perform well. The basic Toad will be enough for a great many users but if you want to really fine-tune your Oracle databases then the complete suite should provide everything you need.
Embarcadero
Embarcadero has a wide range of products for working with databases, some aimed more at database developers, but some are intended for database administrators. In particular, the various versions of DBArtisan and the utilities that you can manage from its console are all intended for use by DBAs.
Embarcadero DBArtisan Workbench includes a range of Analyst tools.
I’ve heard DBArtisan described as the Excel of database management, and people who use it are great fans because it simplifies many common tasks as well as those you tackle once in a blue moon. It can be used to manage Oracle 11g, Sybase 15, SQL Server 2005 and IBM DB2 v9. It comes in three versions, Standard, Professional and Workbench, each with more features than the last.
The standard edition of DBArtisan lets you work with all the latest data types of the underlying servers, and you can point and click to initiate any action from opening a table to moving an entire database. You get a graphical explorer that you can use to browse, access and manipulate the objects in your database. The Explorer shows you an overall picture of 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.
If you need to move a database, you can move between different types of server and, if you don’t want the whole thing migrated, choose which tables and schemas should be moved. There’s a colour-coded SQL editor that lets you step through your code, and you can choose to run a SQL query against different target databases at the same time if you’re feeling brave. There are a variety of wizards to help you through common tasks such working with schemas, and creating database objects.
DBArtisan Professional adds the Embarcadero SQL Debugger and Embarcadero SQL Profiler, both helping you troubleshoot SQL code. SQL Profiler can be used to view Oracle databases to see where any performance bottlenecks are occurring. You view the database loads in a graphical interface so you can see quickly which bits of code are taking the longest and would benefit from tuning.
If your problem is in getting the SQL to work at all, SQL Debugger lets you step through your code as it is being executed to see where any errors occur. You can choose to view the expense of each line of code to find potential bottlenecks. You can watch the values of variables, see dependencies and view the call stack for stored procedures, triggers and functions.
The final version is DBArtisan Workbench. This adds Embarcadero Space Analyst and Capacity Analyst for more advanced storage management, together with Performance Analyst. Space Analyst shows you any current or potential storage problems. Capacity analyst collects database metadata and performance metrics on a regular user defined schedule. The data that is collected can then be viewed to see trends in data growth and to predict future growth. Performance Analyst shows you the overall database performance in terms of the overall workload, SQL execution patterns and any bottlenecks.
Other products of interest in the Embarcadero range include Rapid SQL and ER/Studio. Rapid SQL is essentially a set of code templates that you can use and edit with only an average knowledge of SQL, while Embarcadero ER/Studio is a graphical suite where you can model the structure of your databases to deal with the design and maintenance. It can be used across platforms and information is stored in a central repository so that teams of database designers can work on shared designs.
Embarcadero products are extremely popular with their users because they do what you need, are easy to use and save you lots of time.