Toolbox
Manage databases, easier FTP, and clustered caching
Scott Mitchell
Contents
Effectively Manage Your Database Projects
FTP Made Easy
Take Caching to the Next Level
Effectively Manage Your Database Projects
There are many tools available for managing databases, exploring and modifying schemas, and writing and executing queries. One such off-the-shelf tool worth investigating is DatabaseSpy 2007 by Altova. DatabaseSpy can connect to a wide variety of different data stores—Microsoft® Access®, Microsoft SQL Server™, Oracle, MySQL, Sybase, and so on—and provides a uniform user experience regardless of the database.
When first connecting to a database through DatabaseSpy, a new project is automatically created. Projects serve as repositories for connection details, credentials, commonly used queries, and so forth. The contents of a project can be quickly searched, and frequently used elements can be stored in the Favorites folder. Moreover, the project manager's menus and toolbars can be customized and configured to launch external tools.
DatabaseSpy's SQL Editor also offers a modern query editor. For starters, the Autocomplete feature gives the editor IntelliSense®-like behavior. When typing in a query, a drop-down list of possible SQL keywords, functions, and table names appear. When executing a query, the results are displayed in a grid. Unlike the result grid in SQL Server Management Studio, however, DatabaseSpy's grid can be sorted by clicking on a column header. The SQL Editor also includes tools for exporting data to a variety of formats, including XML, CSV, HTML, and Excel®.
In addition, DatabaseSpy includes a rich graphical Design Editor that enables developers to create and modify tables with a few points and clicks. As the table is being created or modified, DatabaseSpy maintains a database structure change script and displays it on-screen. This information is handy to verify what specific changes have been made and for applying the same changes to another database.
If you are looking for top-of-the-line tools for managing databases and executing queries, take the time to explore alternative, third-party options such as DatabaseSpy 2007.
Price: $129
DatabaseSpy's Design Editor Helps You Create and Modify Tables (Click the image for a larger view)
FTP Made Easy
The File Transfer Protocol (FTP) is an open and well-known protocol for exchanging files between a server and a client over a network. Web browsers are one of the most popular and ubiquitous FTP client applications available, but the FTP features and user experience—while straightforward—typically lack the more advanced features that are found in most stand-alone FTP client applications.
One such stand-alone FTP client application is FileZilla (version 2.2), an open-source project started by Tim Kosse. Launching FileZilla displays the Site Manager, which stores FTP server account information. Here you can add and categorize the FTP servers you commonly access, storing authentication credentials, connection options, and other configuration information. FileZilla supports both the native FTP implementation—which is not secure as it sends credentials over the network in plain text—as well as the two standardized secure implementations: FTP over SSL and SFTP.
Once connected to an FTP site, the FileZilla user interface consists of four panes. The first one lists the FTP commands sent to the FTP server and the corresponding response. This low-level information is quite helpful for developers and other savvy users when troubleshooting. The second pane lists the files and folders on the local computer while the third pane lists those on the FTP server. To upload files or folders from one location to the other, simply select the items from one pane and drag them over to the other pane. The current activity and pending list of actions are listed in the transfer queue, which is displayed in the bottom pane.
In addition to the client application, the FileZilla project includes a free, open-source FTP server implementation.
Price: Free; source code available (Visual C++)
sourceforge.net/projects/filezilla
As a Diagnostic Tool, FileZilla Lists FTP Commands and Responses (Click the image for a larger view)
Take Caching to the Next Level
Caching is a commonly used technique for improving application performance by copying frequently used data from a slow data source to a faster one. For data-driven applications, this usually entails caching data retrieved from databases or Web services into the local computer's memory.
Caching is easiest to implement when the cache is specific to each application, but becomes much more challenging if multiple applications need to work with a common cache. For example, large Web sites typically use a server farm that consists of several computers all serving the same content. As each request arrives, it is assigned to one of the computers in the farm. However, if information is cached in the memory of one computer in the farm, it is not accessible to the cache in another, thereby reducing the effectiveness of the cache. One solution to this problem is to move the cache to a centralized data store, such as a database, or to designate one of the servers in the farm as the cache server, which stores the only copy of the cache. The problem with centralized cache stores is that they introduce a single point of failure and can serve as a bottleneck.
To overcome the limitations of a single, centralized cache store, consider using NCache 3.1 from Alachisoft. NCache is a clustered caching framework that seamlessly manages a cache distributed across multiple computers. An administrator starts by specifying the cluster topologies and cache policies through the NCacheManager tool. The administrator can specify whether the cache is stored in memory or on disk, the eviction policies, and the maximum cache size, among other settings; these settings, as well as the topology, can be modified at runtime. In addition, NCacheManager includes an array of statistics that can be used to monitor the cache's health and as metrics to fine-tune the cache's settings for optimal performance.
When defining a caching cluster, you can specify whether the cache should be replicated or partitioned across the cluster. A replicated cache cluster has the same cache contents on each of the machines in the cluster. Since there is no single point of failure, a replicated cache improves reliability; however, any updates to the cache must be replicated to all of the clusters. Therefore, replicated clusters are ideal for read-dominated caches or small clusters. A partitioned cache, on the other hand, divides the cache contents among the computers in the cluster and boasts better performance for updates and large clusters.
When evaluating frameworks like NCache, I am always interested in the learning curve and ease of integration. Mastering the NCacheManager tool and fully understanding the variety of configuration settings will undoubtedly take time, but plugging in the NCache API into your applications is a painless process. The classes in the NCache API closely match those of the caching API classes in the Microsoft .NET Framework and include HTTP Modules that make upgrading an ASP.NET application as simple as updating its configuration file. NCache also includes a provider for integration into Microsoft's Caching Application Block (CAB).
In short, enhancing caching for your applications is a relatively straightforward process: existing code can be upgraded to using NCache with little or no changes to the code.
Price: $995 per CPU for the Standard Edition
Specify Cache Policies with NCacheManager (Click the image for a larger view)
Send your questions and comments for Scott to toolsmm@microsoft.com.
Scott Mitchell author of numerous books and founder of 4GuysFromRolla.com, is an MVP who has been working with Microsoft Web technologies since 1998. Scott is an independent consultant, trainer, and writer. You can reach him at Mitchell@4guysfromrolla.com or via his blog at ScottOnWriting.NET.
All prices were confirmed at press time and are subject to change. The opinions expressed in this column are solely those of the author and do not necessarily reflect the opinions of Microsoft.
MSDN Magazine does not make any representation or warranty, express or implied with respect to any code or other information herein. MSDN Magazine disclaims any liability whatsoever for any use of such code or other information.