Pump Up SQL Server 2000

During the past 5 years, Microsoft SQL Server has evolved from a departmental database solution to a leading enterprise platform. Well-known organizations such as Expedia, the Home Shopping Network, JetBlue Airways, NASDAQ, Nestlé, and Pennzoil-Quaker State have high-profile SQL Server 2000 implementations, and the application has become as highly regarded as IBM's DB2 and Oracle. SQL Server's quick rise to enterprise-database status began with the SQL Server 7.0 release, in which Microsoft completely rebuilt the database to make it more scalable and efficient. In the same release, Microsoft introduced SQL Server 7.0 OLAP Services and forever transformed the database marketplace by integrating business intelligence (BI) capabilities with the relational database engine. In SQL Server 2000, Microsoft continued to enhance the application's relational database engine; the company also improved OLAP Services and renamed it SQL Server 2000 Analysis Services. SQL Server 2000 has justified its reputation for enterprise scalability by holding, at various times, the top TPC-C scores for clustered and nonclustered database performance.

SQL Server 2000 ships with easy-to-use, effective tools that handle the basic requirements for database development and management. But in today's landscape of multiple platforms, your complex implementation might need more muscle than the built-in tools can provide. Fortunately, third-party vendors have come to the rescue by providing add-on products that build on the basic SQL Server toolset. In many cases, these products add depth to functional areas to address important enterprise-level requirements. In this article, I describe some areas in which third-party add-ons enhance SQL Server and provide an overview of some of these products' important characteristics. Table 1, lists the products by function and includes vendor contact information.

Management
Out of the box, SQL Server 2000 includes powerful management tools called SQL Server Enterprise Manager, SQL Query Analyzer, and SQL Server Profiler. But these tools have limitations; they don't work with other database applications or with every SQL Server release. To bridge this multiple-database gap, one class of third-party SQL Server products provides management tools that work with all the supported database platforms. In some cases, these tools' capabilities exceed those of SQL Server's native management tools.

Embarcadero Technologies' DBArtisan provides schema-, security-, and space-management tools and lets you view the procedure cache, buffer cache, and current database locks. DBArtisan manages DB2 Universal Database (UDB), DB2/390, Oracle, SQL Server, and Sybase databases. Quest Software's Quest Central for Databases provides heterogeneous and comprehensive database management and also supports DB2 UDB, DB2/390, Oracle, SQL Server, and Sybase. BMC Software's SmartDBA product suite features SmartDBA Cockpit, which lets you use a Web browser to manage the supported database platforms. The SmartDBA product suite supports DB2 UDB, DB2/390, Oracle, and SQL Server.

Some companies' enterprise-management tools support SQL Server but aren't expressly geared to the task; instead, they view SQL Server and other database products as one component in the overall enterprise-management picture. One such product, NetIQ's AppManager for Microsoft SQL Server, maximizes system availability by using automated event detection and correction. Heroix eQ Database Monitoring & Management, part of the Heroix eQ Management Suite, supports DB2, Oracle, and SQL Server. Other enterprise-management players include Computer Associates' (CA's) Unicenter CA-7 Job Management, HP OpenView Smart Plug-in for Microsoft SQL Server, and IBM's Tivoli Management Solution for Microsoft SQL Server. Expand Beyond's PocketDBA manages SQL Server on the run. PocketDBA—which runs on any mobile device, such as the Pocket PC, that has built-in browser support—lets you manage SQL Server, Oracle, DB2, and Teradata databases.

Other management tools can help you with ongoing maintenance and troubleshooting. One unique tool in this category is Lumigent Technologies' Log Explorer for SQL Server, which lets you selectively recover transaction-log entries and solve application and user problems at a fine-grained level. Log Explorer lets you identify and roll back schema and permission changes and recover data at the row level. Tools that help you automate SQL Server management include Db Maint 4.0, which schedules backups, sets up log shipping, and creates jobs for single or multiple servers. A & G Software's AGS SQL Scribe Documentation Builder is an automated documentation-creation system for SQL Server 2000 and SQL Server 7.0 that uses easily distributed HTML files to document database schema and perform cross referencing and indexing tasks.

Performance Monitoring
Performance-monitoring tools such as VERITAS Software's VERITAS Indepth for SQL Server proactively monitor your SQL Server systems and help you diagnose and correct performance problems before they affect your database applications. Quest Software's Spotlight on SQL Server features a graphical control panel that provides a real-time overview of current activity levels in the OS and SQL Server and lets you drill down into each area. NetIQ's DiagnosticManager for SQL Server provides real-time status displays that organize SQL Server information into logical groupings so that you can diagnose and correct problems. BMC Software's PATROL for Microsoft SQL Server also monitors SQL Server performance and features real-time notification and analysis of database events.

Real-time monitoring products provide one way to track down performance problems; another class of performance-monitoring tools analyzes your database structure and makes database-optimization suggestions. One such tool is FMS's Total SQL Analyzer PRO, which checks databases for more than 120 possible performance optimizations and configuration-and-design problems, then generates reports and tuning suggestions based on its findings.

Database Design
SQL Server ships with a Visual Database Tool called Database Designer, which provides basic relational-database modeling. However, a few notable third-party products offer capabilities beyond what Database Designer provides. Probably the best known of these tools is CA's AllFusion ERwin Data Modeler. Other powerful database-modeling tools include Embarcadero's ER/Studio and Quest Software's QDesigner. Like Database Designer, these products perform graphical-based database design. But they also feature advanced capabilities such as modeling and designing relational databases and data warehouses, support multiple database platforms in addition to SQL Server, and let you separate logical database and physical database design elements.

Middleware
Although SQL Server has always supported connectivity to COM- and Microsoft .NET-based data, Microsoft hasn't provided the same level of support for Java-based data connectivity. In fact, the company didn't provide a SQL Server Java Database Connectivity (JDBC) driver until 2002. Although Microsoft's native JDBC driver is a good starting point, some third-party JDBC middleware products exceed its capabilities. DataDirect Technologies, which supplied Microsoft's JDBC technology, also markets its own JDBC 3.0-compliant JDBC driver—DataDirect Connect for JDBC, which offers Java connectivity to most other database platforms. DataDirect also sells DataDirect Connect for .NET, a native data provider that supports .NET connectivity to DB2, Oracle, SQL Server, and Sybase. JNetDirect's JSQLConnect also meets the JDBC 3.0 specification and offers Linux, Windows, and UNIX client platforms Java access to SQL Server.

Auditing and Security
For basic security auditing of Windows and server products such as Microsoft Exchange Server and SQL Server, you can download Microsoft Baseline Security Analyzer (MBSA) for free from http://www.microsoft.com/downloads/details.aspx?familyid=8b7a580d-0c91-45b7-91ba-fc47f7c3d6ad&displaylang=en. MBSA is a valuable product that all SQL Server shops should employ. As valuable as MBSA is, however, several third-party products go beyond its basic functionality. Internet Security Systems' (ISS's) Database Scanner scans Oracle, SQL Server, and Sybase databases for possible security exposures, including incorrect account permissions, unauthorized object ownership, stale logon IDs, and other system-integrity threats such as buffer-overrun exposures and Trojan horses. Another powerful database-security scanner, NetIQ's VigilEnt Security Agent for SQL Server, is part of the VigilEnt Security Manager Suite and combines intrusion, policy, user, and vulnerability management into one agent. BindView's bv-Control for SQL Server is a multifaceted management product with a strong emphasis on security. bv-Control provides basic management as well as strong database-auditing capabilities, including the ability to review transaction logs, scan for unauthorized transactions, and track schema and configuration changes. Red Matrix Technologies' SQLAudit 2.0 lets you audit data changes, an important aspect of security. SQLAudit logs which users make data changes, which data they change, and how they change it.

Source-Code and Version Control
Despite its strengths, SQL Server has always lacked version control, which is an important feature if you need to isolate releases of a given database schema or enable team development. You could use Visual Studio .NET's integrated Microsoft Visual SourceSafe application for source-code and version control, but most DBAs and T-SQL developers don't use Visual Studio .NET. Instead, they typically rely on Query Analyzer, which doesn't have version-control capabilities. However, some third-party products, such as CAST Release-Builder, enable T-SQL version control. CAST Release-Builder also automates script extraction, database comparison, and script generation for updates. BMC Software's SQL-Programmer Expert, a well-known T-SQL development tool that features integrated version control, enables T-SQL development and provides stepped debugging and check-in and check-out version control. Embarcadero offers two tools for T-SQL development and source control: Rapid SQL and Embarcadero Change Manager. Rapid SQL includes code templates for improved productivity and built-in version control; Embarcadero Change Manager records and compares schema of different database versions. Red Gate Software's SQL Compare automates database comparisons and lets you push local database changes to a live database on a remote server. Adept SQL Tools' AdeptSQL Diff visually compares and synchronizes databases. Innovartis's DB Ghost lets you compare databases, report their differences, and optionally synchronize them.

Business Intelligence
Since Microsoft introduced OLAP Services in SQL Server 7.0, BI has been the fastest-growing segment of the overall database market. BI products have high strategic importance and provide a competitive advantage for the companies that implement them; they provide insight into a company's line of business and expose data relationships that foster improved understanding of business processes. These abilities let organizations recognize trends and adjust to changing conditions. ProClarity Analytics Platform is a leader in the BI market. The software supports Analysis Services and includes the ProClarity Analytics Application Development Platform (PAADP) and the ProClarity Analytics Suite. PAADP lets you build custom analytics applications; the Analytics Suite provides developer and end-user tools for analyzing BI data.

Panorama Software's Panorama NovaView BI Platform also supports Analysis Services and features a range of analytical capabilities that include bubble-up exceptions, forecasting, trend analysis, what-if analysis, and a direct Multidimensional Expression (MDX) mode that lets power users execute custom statements for Analysis Services queries. Cognos PowerPlay supports multiple OLAP data sources and lets end users create multidimensional analysis reports and share them across the organization. PowerPlay supports Analysis Services, IBM's OLAP for DB2, Hyperion Solutions' Hyperion Essbase, and SAP BW. Business Objects' Crystal Analysis also supports multiple OLAP data sources, including Analysis Services, Essbase, DB2 OLAP, and SAP BW. Crystal Analysis features a drag-and-drop design environment and uses the Crystal Enterprise tool to publish reports to the Web.

Other third-party BI products include MaxQ Technologies' eProphet Business Intelligence Series, which uses information from any source in your organization to perform multidimensional reporting and analysis. arcplan's dynaSight lets you create customized Web-based information systems without having to program. SoftPro Tetral's SoftPro Manager 4.0 uses distributed client/server architecture to provide scalability and offers a range of decision-support functionalities, from ad hoc queries to enterprise-database reporting. SPSS's OLAP Hub, a zero-client Web-based query, reporting, and analysis product, includes Report Depot, which saves and distributes reports; Relational Router, which lets users move between OLAP and relational data sources; and OLAP Hub Scheduler, which automates the production of OLAP reports. Temtec's Executive Viewer gives users interactive, Web-based access to Analysis Services and Essbase and enables dynamic ad hoc analysis and end-user reports, which you can embed in custom dashboards and Web portals.

Another class of BI products lets you integrate Analysis Services data with Microsoft Office. IntelligentApps Excel Edition integrates Analysis Services with Microsoft Excel and uses the Excel interface to create ad hoc queries, formal financial reports, and personal dashboards. MIS Plain uses familiar desktop tools to plan, report, and analyze information and delivers seamless Analysis Services integration with Excel.

Database Replication
Although SQL Server's Data Transformation Services (DTS) feature provides a powerful and easy-to-use tool for importing and exporting SQL Server data and loading data warehouses, it can fall short in enterprise-deployment scenarios. Probably the best-known tool for replicating data between relational-database systems is DataMirror Transformation Server, which supports scheduled and real-time data transfer and transformation between databases such as DB2, Oracle, SQL Server, Sybase, and Teradata.

Informatica PowerExchange also offers batch and real-time database replication and data transformation between multiple platforms, including SQL Server, Oracle, DB2, and IBM's IMS. IBM's DB2 DataPropagator runs on several host platforms, including AIX, Linux, OS/390, OS/400, Sun Microsystems' Solaris, and Windows Server, and supports DB2, IBM Informix, Microsoft Office Access 2003, Oracle, SQL Server, and Sybase replication. StarQuest Ventures' StarQuest Data Replicator (SQDR) provides bidirectional replication between SQL Server and DB2 or Oracle and horizontally and vertically filters replicated rows. Open Universal Software's Data Distributor is integrated with Microsoft's replication architecture and enables replication between DB2 or Oracle and SQL Server systems. HiT Software's DBMoto also provides real-time database replication for Access, MySQL, Oracle, SQL Server, UDB, and other databases. Red Gate Software's SQL Data Compare, which supports SQL Server 7.0 and later, enables SQL Server-to-SQL Server data replication by synchronizing the contents of two SQL Server databases.

Reporting
Although Microsoft's recent release of SQL Server 2000 Reporting Services was big news in the SQL Server community, Reporting Services is far from the only reporting solution in town. Business Objects' Crystal Reports, the de facto reporting software for most relational databases, features a graphical report designer and the ability to embed reports in COM, Java, and .NET applications and serve them from the Web. ActiveViews combines end-user report authoring with Reporting Services' report-management capabilities, lets users add and remove fields and other report criteria on the fly, and provides real-time data drilldown.

Cizer Software's browser-based Cizer Report Builder integrates with Reporting Services through its query and parameter libraries and its ability to author and run Microsoft Report Definition Language (RDL) XML reports by using SQL Server 2000. Fenestrae Communication Server lets you deliver Reporting Services reports as faxed documents or as Multimedia Messaging Service (MMS) and Short Message Service (SMS) messages.

Although these products expand the reach of Reporting Services beyond the desktop, SoftArtisans' OfficeWriter provides even deeper integration with the desktop by letting you design reports directly from Office while preserving all Excel and Microsoft Word features such as Charts, Pivot Tables, and Visual Basic for Applications (VBA) scripts. You can then use Reporting Services to deploy those reports.

Backup and Restore
The third-party products that provide backup-and-restore capabilities for SQL Server tend to fall into three areas: integrated enterprise backup, backup compression, and backup appliances. Several products integrate SQL Server backup with other enterprise data sources such as Exchange and Windows Server and use one backup operation to provide complete enterprise data protection. UltraBac Software's UltraBac 7.x Enterprise Edition has optional agents that extend the product's backup capabilities to Exchange and SQL Server. UltraBac 7.x Small Business Server can back up one Windows server, such as an Exchange or SQL Server machine, without the need for additional agents. CA's BrightStor ARCserve Backup backs up Exchange and Windows servers, and BrightStor ARCserve Backup Agent for Microsoft SQL backs up SQL Server data.

LEGATO Software's LEGATO NetWorker 7.1 backs up Windows Server systems as well as Linux, HP OpenVMS, Novell NetWare, and UNIX platforms and includes a set of snap-in modules for backing up DB2, Exchange, Oracle, SQL Server, and Sybase. VERITAS Backup Exec for Windows Servers backs up Exchange and Windows Server; an optional agent enables SQL Server backup.

Some SQL Server-specific backup products address other data-availability concerns in addition to providing SQL Server database protection. Imceda's SQL LiteSpeed Server compresses backup data and reduces database backup storage by as much as 90 percent and backup times by as much as 70 percent. Sonasoft's SonaSafe for SQL Server, an appliance-based backup solution, takes a different approach to SQL Server backup by providing real-time disk-to-disk disaster recovery, centralized management of multiple servers, full and incremental backups to disk, and incremental backup restores to standby servers.

XML Integration
XML, one of the fastest-growing database-related technologies, didn't start out as a database technology, but its open, cross-platform capabilities made it an ideal vehicle for data transfer between heterogeneous systems. XML is the plumbing that supports Web services—one of the most important new development paradigms. Altova's xmlspy 2004 is a well-established XML development product that has strong ties to SQL Server. xmlspy uses a graphical XML editor and lets you create and debug SQL Server 2000-connected XML Web services. xmlspy also offers integrated support for XML for SQL Server (SQLXML) mapping schemas, which map relational data to XML documents. Several other companies offer XML-to-relational-database mapping solutions. JNetDirect recently released JSQLMapper, which provides bidirectional data mapping between XML and relational databases. HiT Software's winAllora enables XML-to-database mapping without requiring the creation of Document Type Definition (DTD) or XML schemas.

Notification Services
In 2002, Microsoft released SQL Server 2000 Notification Services as a Web download. Notification Services, a new area for third-party product integration, essentially provides a prebuilt framework for developing notification-style applications for SQL Server. AtHoc provides software that lets you create notification applications for e-commerce businesses, finance, IT, and professional services. AtHoc 5.0 Enterprise Notifications Suite offers centralized management, profile-based subscription management, routing-based notifications, and a tracking engine.

Pump Up SQL Server's Capabilities
SQL Server 2000 is a feature-laden platform that can scale from small-business and department-level implementations all the way to the highest levels of the enterprise. Although SQL Server 2000 provides great built-in functionality, it also benefits from a strong third-party add-on market that enhances the product's Return on Investment (ROI) by extending its capabilities and making it easier to manage. If you want to pump up SQL Server's functionality and manageability, these third-party products can provide the muscle you need, and this overview gives you a head start in finding the solution that fits your specific business requirements.