Microsoft SQL 2000 Product Review

By Anjana C.S.

Ever since the Sybase version of SQL (Standard Query Language implements a standardized way to ask questions of databases) Server was licensed to Microsoft in the mid-1990s, Microsoft has been working on a dream database and SQL Server 2000 is a result of a decade-plus effort in this evolution. An element of Microsoft's .NET strategy, it leverages SQL Server 7 and creates a complete database and analysis package.

With a maximum database size of roughly 1,000,000 terabytes, and up to 16 simultaneous instances of the Server that can be run on a single computer, SQL Server 2000 is ready to sweep the depths of enterprise-level database management.

• New Features in SQL Server 2000
• Web-enabled enterprise database
• Introduction of built-in support for the eXtensible Markup language, XML
• Improved distributed query capabilities
• Replication and Administration enhancements
• High speeds in application development and transaction processing
• English Query tools and Full text Search features
• Improved security tools
• Support for file and network encryption
• Enhances Analysis Services including Data Mining support
• Data Transformation Services
• Cascading Declarative Referential Integrity (DRI)
• User defined functions and administration tools like Enterprise Manager and Query Analyzer
• Indexed views and support for distributed partition views enhancements

Benchmark records for scalability and reliability
Today, SQL Server 2000 can claim to be the fastest database in the world with groundbreaking TPC-C (Transaction Processing Performance Councils) benchmarks (www.tpc.org). These benchmarks have demonstrated that "SQL Server 2000 offers the industry's best price-to-price performance ratio on clustered hardware". A SAP endorsement for SQL says, "SAP is convinced that SQL Server 2000 on Windows 2000 would now meet the needs of every R/3 customer around the world."

Coolest features of SQL Server 2000
Powerful e-commerce tool:
Apart from online retailers and web publishers, most businesses today have become web-centric and have set foot in to the domain of e-commerce quite willingly. SQL Server 2000 provides all the tools needed to create powerful e-commerce applications.

XML or eXtensible Markup Language, the language of e-commerce affords the generation and exchange of data between non-compatible technologies. The built-in XML support in SQL Server 2000 simplifies the integration of back-end systems and enables a seamless transfer of data even across firewalls. Easy to use and flexible XML support useful in validating purchase orders, entering information in to a database and exchanging it within heterogeneous systems ensures interconnectivity, interoperability and round-the-clock availability of data.

Biz Talk Server and Microsoft Commerce Server 2000 part of Microsoft's .Net Enterprise Servers, when leveraged with SQL Server 2000 provide the all the infrastructure necessary for any e-business community.

Analysis services:
Entering and exchanging data or OLTP (Online transaction processing) is only one part of database management. OLAP or Online Analytical Processing services make it possible to analyze high-level of aggregation of data and trace patterns. SQL Server 2000 Analysis Services is a direct descendant of SQL Server 7 OLAP Server but with vastly enhanced services.

What's new in Analysis Services in SQL Server 2000:

• Enhanced Data mining tools and storage options
  
• Client Connectivity
  
• Security enhancements
  
• Cube enhancements (Cubes are the basic unit of storage and analysis in Analysis Services)

Data mining:
Data mining helps users analyze data in voluminous relational databases and multidimensional OLAP cubes to uncover hidden patterns that can be used to predict future trends. SQL Server 2000 allows the use of clustered algorithms that help record data that exhibit similar and predictable characteristics into clusters. For example, you could record the behavior of a potential buyer and base your marketing campaign on these results.

By using the OLAP's Action Wizard feature of Analysis Services, developers can use sophisticated rendering tools to integrate feedback loops and analyze the patterns based on specific factors.

Web Based Analysis:
SQL Server 2000 expands all its features on to the web. Remote users who are not connected to the Intranet can access data through HTTP (Hypertext Transfer Protocol).

OLAP cubes can be defined and stored on other analysis servers, even outside firewalls, and then linked with their source cubes.

Enhanced Security:
A source cube can be defined at the owner's end and sensitive information can be stored in it securely, but still made available to other sources through linked cubes.

Additional Security for individual bits of data (cells and dimensions) control the access to data at virtually every level. SQL Server 2000 also incorporates special permissions for databases that require secure logins and authentication.

Indexed views:
Views can be indexed in SQL Server 2000(Views are another means of seeing data in a table). If you create an index on a view, SQL Server will not need to materialize it each time a viewer queries it, because the result is stored in a database. You can create a unique clustered index on a view and store it in the database. The Query Processor automatically recognizes and leverages these indexed views.

Distributed partitioned views:
Distributed partitioned views make tables on multiple servers look like one table. When tables in your database are extremely large, you can partition them by splitting them up and assigning them to multiple servers. The base tables can be updated directly through these views. This facilitates data location independence and makes data distribution and planning a lot easier.

Scalability:
SAP R/3 Sales and distribution Benchmark test on Microsoft Windows found "SQL Server 2000 to afford 53% more scalability than Oracle and double the performance levels of Oracle and Unix at half the total system cost."

Scalability features -

• Applications can be run on Windows 2000 multiprocessor systems, providing support for up to 32 processors in a single instance
• Support for symmetric multiprocessing hardware and indexed views
• Database and operating system integration
• Partition workload across servers
• Multiple instance support of up to 16 simultaneous instances on one single computer

The last feature enables Application Service Providers (ASPs) greater flexibility in hosting multiple applications on a single server. In the case of hardware budget constraints, independent teams working can maintain distinct SQL Server environments on the same machine.

Queries:
When you save a view, the database server also saves information on how it will find records for this view in a query plan. SQL Server Query Analyzer furnishes tools to inspect and modify query plans and fine tune the performance of queries. It executes Transact - SQL (or T-SQL is Microsoft's implementation of the Standard Query Language) queries and analyzes them.

Full Text Search:
This is a separate program (Microsoft Search Service) that indexes all sorts of information from most of the Back Office products. Digitally stored information is in the form of unstructured textual data and saved in a plain text file or formatted documents. Full text Search enables access to data in a uniform manner.

English Query:
The user can now pose questions in plain English instead of complex SQL. English Query is a tool that builds specialized applications in relational databases and makes data available to all types of users.

Replication:
It is designed specifically to copy data between servers and then updating these copies. SQL Server allows direct database download from the server or in the form of email. Three types of replications are possible -

Transactional - copies data changes to subscribers

Snapshot - copies entire data to subscribers each time it replicates, but does not monitor updates to data

Merge - allows changes to be made to data at both publisher and subscriber thus ensuring continuous synchronization of data between servers

Along with these replication methods the use of differential backups that record all changes made to a database since the last full backup ensure high consistency and autonomy of data.

Log Shipping:
Log shipping or transmitting transaction logs across physically separated databases improves reliability and increases availability. The Log shipping feature in SQL Server 2000 backs up transaction logs from the source database, copies and restores it to a destination database. Also, with the help of a warm standby server you can offload query processing from the source server to read-only destination servers.

Data Transformations (DTS):
It is a flexible tool for moving and transforming data. SQL Server Enterprise Manager allows the performance of simple DTS tasks using the DTS Import and Export Wizards and the DTS Package Designer. Automated data transport with the manipulation of data while its being moved make it a very useful feature.

Cascading Declarative Referential Integrity:
SQL Server 2000 is the first version to offer Cascading Declarative Referential Integrity, a feature that while reserving referential integrity of tables implements cascading to allow a wider range of operations.

Some of the Awards that SQL Server 2000 received are -

• CRN Channel Champions Competition
• EWeek's 2000 eXcellence Award for Enterprise Systems Development
• Datamation Product of the Year 2000 for Data Ware Housing and Business Intelligence

The SQL Server Profiler allows you to perform and monitor traces in novel ways and the Object Browser helps navigation and viewing of database objects easily. The Auto tuning and management features along with the Copy Database Wizard (copy databases without taking servers offline) and User Defined Functions (T-SQL statements that allow the reuse of code) enable you to design databases that can be managed from a single location alongside all your enterprise servers.

With the release of SQL Server 2000 (and its consequent annual sales of $1 billion!) undoubtedly helps Microsoft gain leadership in the Windows database market.

Links:

www.microsoft.com/sql - The Home Page of SQL Server gives you all the information about the product, documentation, system requirements and upgrades, including trial software downloads.

www.sqlwire.com - Furnishes news, product reviews, books, seminars and SQL Server Web announcements.

www.sqlservercentral.com - Book and product reviews and useful articles on how to implement SQL Server.

www.swynk.com - An ultimate resource for latest information about SQL Server and SQL Server scripts, discussion groups and a wide knowledge database.

Back to top