Accessing data

 By Simon Bisson

Simon Bisson checks out some of the tools you can use to build large scale database applications.

HardCopy Issue: 49 | Found In: Development | Published: 14/09/2010 | Last Revision: 17/09/2010

Applications need data. Today’s multi-tier applications are driven by all kinds of database, from the familiar large scale RDBMS platforms produced by Oracle, Microsoft and IBM, to smaller scale open source solutions like MySQL, to embedded databases like SQLite, to the new generation of NoSQL cloud services from Google and Amazon. This also means that applications need database developers, and they need the tools to help them build and tune the databases. You can work with a database’s own management tools or find a third party database development tool that does just what you need. Here we examine a few of the more significant examples.

Microsoft Visual Studio 2010

Visual Studio 2010 is designed to help teams build large complex enterprise applications, as well as simpler desktop and Web applications. Different as they are, all three share the need for data. This is why Visual Studio 2010 has made data a significant piece of the application development process, with database design and development tools in the IDE and accessible from the Visual Studio Server Explorer. As they come, Microsoft tools do specifically target SQL Server. There are options for working with any ODBC database but many key features, like the table designer and database diagram designer, need SQL Server. That said, Quest Toad Extension for Visual Studio does add considerable support for Oracle (see panel over the page). Database projects are first class citizens in Visual Studio 2010, which means you manage elements like tables and stored procedures using Visual Studios project tools. You can start by importing a database schema into Visual Studio, where it’s subject to whatever version control and release process you’re using. There’s also dependency control, so you can avoid deploying a change that breaks a dependency, even to test systems. The dependency management tools also help you refactor database projects. Database schema can be compared with earlier versions, or with active databases, ensuring that you’re working with the most current and accurate. Microsoft has improved support for LINQ, its inline query language that takes SQL into C# and Visual Basic. There are improved drag and drop data controls for WPF and Silverlight, where all you need to do is drag a dataset or an entity model onto a control to use and display data. It’s now easier to connect data to an application object, with the ability to quickly connect a data source to multiple objects. The Visual Studio IDE can also be used to author Transact-SQL queries, scripts and procedures. The biggest change are the new tools for working with Entity Data Models. A new entity data model designer lets you start with a data model, getting your concepts in place before it creates a database. There are also tools that make the resulting database and application code more readable, making it easier to maintain code, and easier for others to understand. You can use the model browser to edit and explore the resulting models, while extensibility tools mean you can also write your own code to automate elements of the database design process. ADO.NET’s Entity Framework lets you code against these conceptual models, with tools for handling n-tier applications. LINQ also supports entities. Visual Studio 2010 provides developers with plenty of test tools, and they’re also available to database developers. You can use its unit testing tools to automate database testing, and there’s support for automatically creating realistic test data that doesn’t violate privacy regulations, a situation that could well arise if you’re using a copy of live data.

Embarcadero XE

Embarcadero has several tools targeted at all parts of the database development and management lifecycles. RapidSQL XE is targeted at database developers, while DBArtisan XE is for DBAs managing and tuning production databases. The two applications work well together, helping bridge the development/operations divide. Database developers can develop database designs and queries in RapidSQL XE. It’s a multiplatform system, designed to work with the typical heterogeneous data environments found in many businesses today. The result is full native platform support through a single IDE, with no need for different versions for different databases.

Embarcadero DB Optimizer profiles the performance of database queries, working here against an Oracle database.

The RapidSQL IDE includes code completion, along with tools to help developers structure their code and meet development standards. There are also code analysis and debugging tools, making this a complete data IDE that covers everything from a visual query builder to transactional code. It also lets developers optimise and test their code, with an integral unit testing tool for full round-trip development, testing and debugging. One key feature of the latest generation of Embarcadero’s tools is what the company calls ToolCloud, a central deployment and licence management server that lets developers download and use the tools they need, when they need them. As the software is delivered on demand, there’s no need to install software locally. The ToolCloud approach also simplifies licence management, reducing the number of copies of tools installed on developer desktops and at the same time making the management of software versions easier. ToolCloud means development teams can share concurrent licences, and that can bring a significant financial saving. DBOptimizer XE is another piece of Embarcadero’s database tooling, designed to help profile and optimise database applications. Developers can use it to determine whether problems are caused by applications, by queries, or by the underlying database configuration. Bottlenecks can be found unobtrusively, and DBOptimizer will offer alternative SQL statements that improve performance. It can also be used as part of acceptance testing, ensuring that SLAs are met, and with reports that can be used to demonstrate improvements. While RapidSQL and DBOptimizer are both developer tools, DBArtisan XE is for database administrators, with tools for managing and checking databases. The tools in DBArtisan have a very deep reach, and can show exactly what is happening inside a database. There’s also support for virtualised infrastructures, with the aim of keeping enterprise databases running at peak efficiency. Like RapidSQL, this is a tool for heterogeneous organisations, with a single view into the various platforms in use, from mainframe to client-server to Web. It’s also suitable for working with data warehouses and business intelligence tools as it can handle the necessary data consolidations and transforms. Architects need to work with databases too, and Embarcadero’s ER/Studio handles entity relationship modelling, with tools for reverse engineering databases across multiple platforms, and for letting developers examine and design database structures. There’s also scope for sharing data models across a team, with tools for communicating models throughout the application development lifecycle, including reporting to business analysts and project stakeholders. Teams can use ER/Studio to document and understand the lineage of data in an application, which can be important in ensuring compliance with regulations, including those concerned with privacy. This is even more important for applications that use multi-tier databases and cloud data sources. ER/Studio has a repository that can be used as version control for data models, helping avoid duplication of effort on separate projects. The repository also holds industry standard data models, encouraging best practices and making sure that application developers avoid basic design errors.

Quest Toad

Toad from Quest has been around for a long time now. Originally purely for Oracle developers (the name comes from the acronym for ‘Tool for Oracle Application Developers’), over the years Toad has added support for other databases, including DB/2, SQL Server and Sybase. There’s also a free version for MySQL developers, and a set of tools for working with cloud databases is currently in beta. There’s a lot to Toad, with different editions for different job functions. Quest’s Michael Horne describes it as “a database developer’s Swiss army knife”, although it now supports both analysts and database administrators, as well as developers. Analysts and architects will find Toad for Data Analysts an important tool. Unlike the rest of the Toad family it’s a more user oriented tool that lets analysts browse databases, and extract schema and tables, letting users use tools like Excel to work with data. It’s also a cross platform tool, with a single user interface for working with multiple data sources which can be used to browse, extract and report on data. Toad for Data Analysts works using direct connections to Toad supported databases, and ODBC 3.0 connections to any other data source (using generic ODBC drivers). You can license it separately from the rest of the Toad suite, or alternatively if you’ve got an existing permanent licence, you can use that. While the standard Toad package gives you much of what you’ll need to develop and test SQL code, more technical users and application developers can use Toad Xpert. This adds a SQL optimiser and a data modeller. The SQL optimiser helps tune SQL for performance, running inside the editor and with automated tuning tools that avoid the need for complex and time-consuming manual tuning. The optimiser shows versions of statements that give the best performance in a specific environment (something that runs well on a standalone system may not perform as well in a RAC cluster) and helps you find new, more effective, ways of writing queries.

Quest Toad analyses your code to ensure you are making best use of database capabilities.

The latest version adds auto-optimisation, finding appropriate rewrites and testing them. The optimiser will help identify inefficient code, proactively identifying statements that can be optimised, and collecting together SQL statements and queries that are candidates for tuning. The reports it generates can be shared with stakeholders as well as the rest of the development team. The data modeller is a design and modelling tool that helps generate logical and physical models for your databases. It is best thought of as a lightweight alternative to ERWIN or Oracle Designer, with the ability to reverse engineer existing databases. Reverse engineering is a useful tool, especially when combined with Toad’s automated documentation features, as there are far too many undocumented databases out there! Xpert is the basis of two additional suites, one for developers and one for DBAs. The Development Suite builds on Xpert’s tools with a Code Tester, letting developers write unit tests for PL/SQL code. There’s also a native test harness so test code can run outside Toad (important when you’re testing deployed databases). These help developers deliver best practices, and there’s also support for regression testing as part of any database application update. Toad helps you ensure that your tests exercise as much code as possible thanks to a built-in code coverage feature that highlights tested and untested code in the Toad editor.

Another developer feature is the Benchmark Factory which gives you access to a stress and load-testing tool. Tests can be written inside Toad and then deployed to machines running agent code, letting developers test applications by scaling up the number of users over time. The Benchmark Factory can also access profiler information from inside databases, and while this is a multi-platform tool, you’ll need appropriate licences for the databases you want to test. You can also add additional test users with a licence upgrade. Benchmark Factory is also part of the DBA Suite, where it’s able to help test the results of changes in database parameters. DBA’s also get access to Spotlight, a performance diagnostic tool that can help find and display bottlenecks. Colour coded alarms pinpoint trouble spots, and the diagram view lets you see just what’s going on at all levels of your architecture. The look and feel is all very Next Generation (we weren’t surprised when Quest told us it was designed by a Star Trek fan). Toad’s strength is that all these tools are accessible from the editor, making it what Quest calls “the nine-to-five tool for a DBA”. Developers need high quality IDEs, and Toad goes to show that IDEs are for more than just application code. There’s one other important piece of the Toad story, and that’s its ability to help developers write maintainable code with automated formatting from templates, which can be delivered from a central server to ensure team-level consistency. Built-in quality reviewing tools let you run code reviews against your own code, while team level tools help with a whole project.

Be the first to comment about this article...