Microsoft SQL Server 7.0

Customers are looking for
solu-tions to business problems. Most database solutions bring multiple layers of cost and
complexity. Microsoft's strategy is to make SQL Server an easy database for building,
managing and deploying business applications. This means providing a fast and simple
programming model for developers, eliminating database administration for standard
operations, and providing sophisticated tools for complex operations.



Microsoft SQL Server 7.0 is a defining release for Microsoft's database products, building
on the foundation established by SQL Server 6.5 and currently available only on Windows
NT. Some of the 'firsts' associated with this product include: first database to scale
from the laptop to the enterprise using the same code base; first database to support
auto-configuration and self-tuning; first database with an integrated OLAP server; first
database with integrated Data Transformation Services; and first database to provide
multiserver management for large numbers of servers.



SQL Server 7.0 lowers the total cost of ownership through features like multiserver,
single-console management; event-based job execution and alerting; integrated security;
and administrative scripting. This release also frees up the database administrator for
more sophisticated aspects of the job by automating routine tasks. Desktop, workgroup, and
enterprise environments all have their own specific requirements. At the high-end, the
scalability and power of the management framework is critical. For the low-end,
ease-of-use is critical. Some benefits from the ease-of-use improvements include:



* eliminate the requirement for a DBA for standard operations.



* hide the complexity of server configuration, DBCC, maintaining index statistics,
database backups.



* remove the need to define hardcoded configurations for the server.



* make it easy to transition from the desktop products like Access, FoxPro, and Paradox to
SQL Server.



* reduce the need for trained database administrators



* provide Wizards for normal operations to reduce learning curve



* provide the infrastructure for distributed management of large numbers of



* address the basic needs for moving data within their environment



* provide tools to monitor, profile, and analyze server performance



A description of some of the main features follows:



Relational engine

QUERY PROCESSOR: This has been redesigned to support the large databases and
complex queries found in decision support, data warehouse, and OLAP applications. The
multi-phase optimizer quickly finds the optimum plan for OLTP queries, and also includes
several new execution strategies that can improve the performance of complex queries. The
query processor now uses hash join, merge join, and hash aggregation techniques to scale
to larger databases than those supported by the nested-loop join technique, which was the
only join technique supported by SQL Server 6.5. SQL Server 7.0 uses index intersection
and union techniques on multiple indexes to filter data before it retrieves rows from the
database. All indexes on a table are maintained concurrently and constraint evaluations
are part of the query processor's execution plan. These two factors simplify and speed up
the updating of multiple rows of a table.



Information is automatically extracted from the statistics using fast sampling. This
ensures that the query processor uses the most current statistics and it reduces
maintenance requirements. The query processor uses OLE DB to communicate with the
data-storage components of Microsoft SQL Server. OLE DB provides the SQL Server 7.0 query
processor with distributed and heterogeneous query capabilities. It supports distributed
queries between multiple SQL Server 7.0 servers as well as to any OLE DB provider.



PARALLEL QUERY: SQL Server 7.0 supports parallel execution of a single query across
multiple processors. A CPU-bound query that must examine a large number of rows often
benefits if portions of its execution plan run in parallel. SQL Server 7.0 automatically
determines which queries will benefit from parallelism and generates a parallel execution
plan. If multiple processors are available when the query begins executing, the work is
divided across the processors.



DISTRIBUTED QUERRY: They access data that can be stored in multiple data sources on
either the same or different computers. These queries take advantage of OLE DB, the
emerging standard for data access of non-relational as well as relational data sources.
Distributed queries provide SQL Server users with access to data stored in: Multiple SQL
Servers, Heterogeneous databases (DB/2, Oracle), File systems (images, documents, video),
Network sources (directories, index servers). This approach, called Universal Access,
differs from the approach of exporting the data into the database. One of the problems of
the Universal Server approach is that data is immediately out of date in the database. You
must either re-export the entire data set, or find a way to export only the changed data.
SQL Server distributed queries using OLE DB technology allow you to keep data stored where
it currently resides, and still access all data in one query.



INDEXING OPERATIONS: SQL Server now uses index intersection and index union to
implement multiple indexes in a single query. Shared row indicators are used to join two
indexes on the same table. Earlier versions of SQL Server employed no more than one index
per table in a query.



TRIGGERS: You can now append multiple triggers of the same type to a single table.
For example, a single table can have one delete trigger, three insert triggers, and two
update triggers. This enhancement allows you to put different business rules into
different triggers. A database option allows triggers to call themselves recursively.



Storage Engine

DYNAMIC ROW-LEVEL LOCKING: SQL Server 7.0 now supports full row-level locking for
both data rows and index entries. The lock manager has been optimized to complete lock
requests faster and with less internal synchronization. Many OLTP applications can
experience increased concurrency, especially when applications append rows to tables and
indexes.



DYNAMIC MEMORY AN SPACE MANAGEMENT: SQL Server 7.0 automatically and dynamically
reconfigures itself while running. If SQL Server detects increased numbers of users logged
on, it can dynamically acquire additional resources such as memory. As users log off, SQL
Server frees the resources back to the system. SQL Server can also automatically increase
or decrease the size of a database as data is inserted or deleted. Database administrators
can control the amount of dynamic reconfiguration in each SQL Server. A small database
used by someone not familiar with databases can be set up to largely configure itself. A
large production database monitored by experienced database administrators can be set up
to give them full control of configuration.



NEW PAGE AND ROW FORMATS: This allows the server to easily scale from low-end to
high-end systems, improving performance and manageability. The formats support row-level
locking, are extensible for future requirements, and improve performance when large blocks
of data are accessed because each I/O operation retrieves more data. All database pages
are now 8K in size, increased from 2K. The maximum number of bytes in a row is now 8060
bytes, and the limit on character and binary data types is 8000 bytes, increased from 255
bytes. Tables can now have 1024 columns, a significant increase over the 250 columns
supported previously.



DATABASES AND FILES: SQL Server 7.0 simplifies the relationship between a SQL
Server database and the Windows file system, enabling greater scalability. Databases
reside on operating-system files instead of on logical devices. When a database is
dropped, its files are also deleted. SQL Server 7.0 allows database files to expand
automatically, eliminating the need for manual intervention. Filegroups are supported.
Filegroups allow data in a single database to be spread over multiple files. It is also
the basis for partitions, a future enhancement to SQL Server.



UNICODE: SQL Server now supports Unicode data types, which makes it easier to store
data in multiple languages within one database by eliminating the problem of converting
characters and installing multiple code pages. Unicode stores character data using two
bytes for each character rather than one byte. There are 65,536 different bit patterns in
two bytes, so Unicode can use one standard set of bit patterns to encode each character in
all languages, including languages such as Chinese that have large number of characters.
Programming languages also support Unicode data types. The fact that Unicode data needs
twice as much storage space is offset by the elimination of the need to convert extended
characters between code pages.



DATA TYPE CHANGES: The maximum length of the char, varchar, binary, and varbinary
data types is now 8000 bytes, an increase from the limit of 255 bytes in SQL Server 6.x.
The Transact-SQL string functions also support these very long char and varchar values.
The use of text and image data types can now be reserved for very large data values. The
Substring function can be used to process text and image columns. The handling of Nulls
and empty strings has been improved. A new unique identifier data type is provided for
storing a globally unique identifier (GUID).



Utilities

SQL Server 7.0's utilities run much faster and have less impact on server operations.



BACKUP AND RESTORE: These utilities are an important safeguard for protecting
mission-critical data stored in SQL Server databases. These utilities allow the complete
restoration of data after a wide range of problems such as media failure, user errors or
permanent loss of a server. Backups are fast, low-impact operations, so there is no excuse
for not implementing a comprehensive strategy for safeguarding data. Backup and restore
operations achieve maximum read/write throughput with disk and tape devices, and use all
devices in parallel. In addition, online backups have minimal impact on transaction rates,
so backups can commonly be run during normal operations. Restore is designed to get
systems up and running as quickly as possible with guaranteed data integrity. Since SQL
Server 7.0 databases are self-describing, there is no need to create the database and
associated files prior to the restore-databases are recreated automatically.



This feature offers benefits like: script maintenance and manual intervention are
minimized; using information about previous backup operations, SQL Server formulates a
plan to recover the damaged database in the minimum amount of time; differential database
backups minimize the need to roll forward a large number of transactions; if only part of
the database is damaged or lost, only the damaged files need to be restored, followed by
application of the transaction log; multiple backups can be stored on a single device, and
descriptive information can be obtained quickly and easily.



DATABASE CONSISTENCY CHECKER: DBCC statements check the logical and physical
consistency of a database, memory usage, and performance statistics and provide other
administrative functions. Running DBCC is good for peace of mind, a good standard practice
and a valuable tool and is no longer considered mandatory for normal database maintenance.




BULK DATA LOAD: This is significantly faster than previous versions of SQL Server and
offers many new features. Efficiency and flexibility is improved because bulk data loads
go through the query processor, which develops a plan just as it does for normal queries.
This provides optimized, high performance bulk loading, and other features such as full
constraint validation during load.



Dynamic self management

Many server configuration options have been streamlined and simplified. For example, by
default the server dynamically adjusts its memory and lock resource use. A database
increases allocated resources when necessary without overcommitting them and decreases the
resources used when they are no longer needed. Earlier versions of Microsoft SQL Server
required manual adjustment of these settings. Databases now reside on operating-system
files instead of on SQL Server logical devices. You can create a database and all its
files with a single Create Database statement, or the Enterprise Manager can create new
databases and modify existing databases.



Database files can automatically grow from their originally specified size. When you
define a file you can specify a growth increment. Each time the file fills, it increases
its size by the growth increment. Each file can also have a maximum size specified. If a
maximum size is not specified the file can continue to grow until it has used all
available space on the disk. This feature is especially useful when SQL Server is used as
a database embedded in an application where the user does not have ready access to a
systems administrator. The user can let the files grow automatically to lessen the
administrative burden of monitoring the amount of free space in the database and manually
allocating additional space.



Database devices and segments no longer reside on top of operating-system files. Rather, a
database consists of two or more Windows files. Each Windows file is used by only one
database. A single file cannot be shared by multiple databases. When a database is
dropped, its files are also deleted.



Data Warehousing

Microsoft's goal is to create a comprehensive data warehousing strategy to make it easier
to design and build cost-effective data warehousing solutions with SQL Server. SQL Server
provides a host of new features for data warehousing and data marts. Some of these
features include:



* new join algorithms to improve speed for complex queries



* storage Engine optimized for VLDB



* utilities optimized for handling large database



* replication, group administration, and desktop version provide wide data distribution



* parallel Query Engine



OLAP SERVICES: Designed to support Online Analytical Processing (OLAP)
applications, OLAP Services is an essential component of SQL Server that serves a wide
array of enterprise solutions. OLAP Services incorporates intelligent aggregation
selection; automatically choosing a subset of all possible aggregations from which the
remaining aggregations can be quickly calculated when they are needed. The Aggregation
Design wizard provides additional flexibility by allowing the cube designer to specify the
tradeoff between disk storage requirements and the amount of pre-calculated aggregation.



Cubes can be partitioned to spread data across several servers. Data is seamlessly
presented to the user as if it were stored in one place. This feature enables the cube
designer to make the most effective and efficient use of existing data storage facilities.



The data model provides a great deal of flexibility. OLAP Services supports full
Multidimensional OLAP, Relational OLAP, and Hybrid OLAP implementations, offering the OLAP
database designer the opportunity to choose the model most appropriate to the needs of the
organization. The underlying data model or data models chosen by the cube designer are
invisible to the client application and its user.



DATA TRANSFORMATION SERVICES (DTS): These services provide the functionality to
import, export, and transform data between Microsoft SQL Server and any OLE DB, ODBC, or
text file format. Using DTS, it is possible to build data warehouses and data marts in SQL
Server by importing and transforming data from multiple heterogeneous sources
interactively or automatically on a regularly scheduled basis (requiring no user
intervention). Custom transformation objects can be created that integrate into
third-party products.



A transformation is the set of operations applied to source data before it can be stored
in the destination. For example, DTS allows calculating new values from one or more source
fields, or even breaking a single field into multiple values to be stored in separate
destination columns. Transformations make it easy to implement complex data validation,
scrubbing, and enhancement during import and export.



DTS supports multi-step packages, where multiple files can be processed separately, then
brought together in a single, final step. Records in a file can be broken up into multiple
records in the destination, or multiple records in the source can be aggregated into
single records in the destination.

DTS is also integrated with the Microsoft Repository, where it stores metadata, the Data
Transformation packages and data lineage, including sources of all transformed data. Data
Transformation Services only moves schema and data between heterogeneous data sources.
Triggers, stored procedures, rules, defaults, constraints, and user-defined data types are
not converted between heterogeneous data sources.



PIVOT TABLE SERVICE: This is a companion to OLAP Services that provides client-side
access to OLAP data for custom applications. Microsoft PivotTable Service runs on client
workstations and enables organizations to use Microsoft Visual Basic or other languages to
develop custom applications that can make use of OLAP data from OLAP Services or data
directly from relational databases via Microsoft OLE DB technology. When it is used with
OLAP Services, PivotTable Service automatically apportions processing and caching to the
most appropriate locations and enables multiple clients to dynamically access the same
cubes.



Microsoft PivotTable Service can also store cubes locally on a client machine so you can
analyze data without connecting to OLAP Services. This mobile solution enables an analyst
to take the data home or on the road for analysis and presentation.



MICROSOFT REPOSITORY: Microsoft and its data warehousing alliance partners
developed a set of repository extensions to provide a common development infrastructure.
The repository encourages sharing of information-such as database schema, metadata, and
data transformations used in data warehousing applications. Other metadata models will
also be generated from the open-design process, and the early models continue to evolve.
The shared repository provides increased interoperability between the data warehousing and
development tools available from many different vendors.



The new repository extensions help expand the data warehousing market by providing an
open, common infrastructure across all data warehouse and database design and analysis
tool vendors. Metadata integration is one of the most important challenges that the
industry must overcome to facilitate successful data warehouse and datamart
implementations.



ENGLISH QUERRY 2.0: This environment allows developers to turn their relational
databases into English Query applications. This gives end users the ability to pose
questions in English instead of forming a query with a SQL statement.



Microsoft English Query applications are created with the English Query domain editor.
Information is provided about the database so that English Query can process English
questions about particular tables, fields, and data.

A Microsoft English Query application can be deployed in several ways, including within a
Visual Basic or Visual C++(r) based application, or on a Web page running on the Internet
Information Server. In the Web scenario, the application front-end is created with a
VBScript page, which is designed for use with Microsoft Active Server Pages server-side
scripting.



Internet, intranet and ecommerce

FULL-TEXT SEARCH: Previously, RDBMS users had to buy expensive additional
components. The full-text search facilities can be used to create special indexes of all
pertinent words in selected columns of selected tables. Extensions to the Transact-SQL
language exploit these indexes in order to support linguistic and proximity searches.
Several languages are supported.

With a full-text query, you can perform a linguistic search of character data in tables
enabled for full-text. Also, the full-text search feature can weigh query terms and report
how well a match scored or ranked against the original search term. The Full-Text Indexing
wizard simplifies creation of an indexing catalog. Administration of full-text indexes can
be managed either by using extensions to the Enterprise Manager or via a new set of stored
procedures.



WEB ASSISTANT: The SQL Server Web Assistant is enhanced in SQL Server 7.0 with a
new Wizard and formatting option. It is an extremely easy-to-use tool for generating
standard Hypertext Markup Language (HTML) files from SQL Server data.



With the SQL Server Web Assistant wizard, you can generate an HTML file once or as a
regularly scheduled SQL Server task. An HTML file can be updated whenever relevant data
changes by using a trigger.



PROXY SERVER INTEGRATION: The Windows Sockets Net-Library has been enhanced to work
with Microsoft Proxy Server and provide secure communication across the Internet.
Connections can be made to Microsoft SQL Server through Microsoft Proxy Server providing
secured access to data. Unauthorized users are prevented from connecting to private
networks. This keeps sensitive data secure by controlling all the permissions and accesses
to the listening port. Microsoft Proxy Server is integrated with Windows NT Server user
authentication. Access is blocked to restricted sites by ranges of IP addresses, domain,
or individual users to ensure that Internet permissions are used appropriately.



SQL Server also makes replicating data across the Internet easy with anonymous
subscriptions and built-in support for Internet distribution. The ODBC SQL Server 3.5
driver distributed with SQL Server 7.0 fully supports programs using Active Server Pages
and the Internet Database Connector.



Multi-site management

Microsoft SQL Server has expanded server administration capabilities to manage multiple
servers using one centralized server. In this version of SQL Server, you can: group
servers into logical functioning units, such as by departments or business units; perform
cross-server transactions, such as creating an application that reviews and transfers
product inventory from a remote warehouse to a central warehouse; create multi-step jobs,
schedule the job, manage job step flow, and store job success or failure information at a
central location.



Using SQL Server Enterprise Manager, a system administrator can define a multiserver
configuration, naming one server as the master server that communicates and distributes
jobs, alerts, and event messages to target servers named in the configuration.



Microsoft management console

This is the new user interface and framework for BackOffice server management. This shared
console provides a convenient and consistent environment for Microsoft SQL Server and
other snap-in administration tools. The SQL Server MMC snap-in console is called SQL
Server Enterprise Manager. The MMC point-and-click user interface is similar to Windows
Explorer.



Alerts can be defined to raise a response when an event occurs with a system or
user-defined error or severity level. Alert responses are one or more of the following:
sending operator notification, logging the alert in the Windows NT application event log,
raising an SNMP trap, executing a job

The SQL Server Agent monitors events in the Windows NT application event log. When an
event occurs, the SQL Server Agent compares the event details with the alerts defined for
the environment. If a match is found, the SQL Server Agent implements the specified alert
response and writes a response message in the Windows NT application log.



Security

The security architecture is better integrated with Windows NT and provides increased
flexibility. Database permissions can now be assigned directly to Windows NT users. You
can define Microsoft SQL Server roles to include not only Windows NT users and groups but
also SQL Server users and roles. A SQL Server user can be a member of multiple SQL Server
roles. This allows database administrators to manage SQL Server permissions as Windows NT
groups or SQL Server roles, rather than as individual user accounts. Database access and
permissions are now managed using Windows NT groups. New fixed server and database roles
such as dbcreator, diskadmin, and sysadmin provide more flexibility and security than the
system administrator (sa) login.



Database upgrade

Databases are easily transferred from version 6.x to 7.0, via a fully automated upgrade
utility. Customers are able to quickly get up and running on the new version and take
advantage of new features with minimal impact on operations.



The following scenarios illustrate how to carry out the upgrade process.

Side-by-side: Depending on the amount of disk space, the upgrade takes place on a single
computer using a disk-to-disk named pipe connection or a tape drive. Upgrade can be done
over a direct pipeline with enough disk space. Otherwise, the Version Upgrade wizard can
export the SQL Server 6.x catalog data, objects, and databases to a tape backup or network
share. n

Diaspark Jewel2.0 face="Arial, Helvetica, sans-serif" size="-1">



This product is meant to automate a
jewel manufacturers need for management of style, contracting, gold, return and repair,
and stone inventory.

The product has been written in
PowerBuilder 5.0 Enterprise and consultants at Diaspark Inc, New York, have used their
experience for the development of a library of object oriented features and support. While
the library is called PowerGen, the entire range of modules from Diaspark is based on this
library. The library incorporates all the major components of object orientation namely
inheritance, encapsulation and polymorphism. The source code of Diaspark Jewel has been
scripted using a commenting style that enhances the understanding of the code. Jewel
includes an online help for the source code and source objects in the application. The
developers have written the help module explain the functionality of the software.



Some of the main functional modules are described below:



Style

Style is caretaker of style related requirements. The Setup section lets the user define
vendors, vendor categories, stone parameters, stone size, stone lot number and cost, stone
packets, labor costs for setting the stones as per the shapes. The user can classify
metals into metal categories, store details of metal items, query on metal items to
calculate prices for different carat combinations. The user can define the mark-up level
and apply it to a style category or a style so that the selling price is automatically
calculated. The user can store detailed as well as summary information on styles and see
the images of your finished products through a slide show.



This is an important module for cataloguing of finished products. It allows the user to
store details about the stones used in the jewelery industry. It automatically shows the
cost of the same metal in different carats. It keeps details about the stone packets
prepared for sale or delivery to contractors. There is a separate provision for
calculating the stone setting labor and details of the finishing labor for a comparative
analysis of the charges to the contractor. It allows users to set up different price
levels of finished products depending upon the quality and weight of metal, stone, and
type of setting. A price catalogue can be printed for potential buyers.



The Syle module in Diaspark Jewel provides a reporting system which helps the user
generate easy-to-understand and informative reports. A number of reports can be generated
for the style, metal and stone details managed by the package. Some of the reports are
Stone parameter, Stone size, Stone lot #, Metal category list, Style catalogue.



Gold

This module is designed to meet all requirements when it comes to managing the precious
metal-Gold. The flow of this module starts from setting up often required information
about casters and raw gold. The user can put metal items into categories and calculate the
cost of metal for various size and weight combinations. The user can store the daily price
of gold and have comparative analysis of the price fluctuations. An interface with
Accounts Payable will generate caster's invoice automatically whereas the interface with
inventory will keep your stock up to date.



Transactions include issuing purchase order to vendors, accepting invoices, credit notes
and memos. Posting all the transactions automatically updates all accounts in the account
Payable and General Ledger modules. Reports include Gold transaction Register, Purchase
Order Register, Metal Item Invoice Register, Metal item Return Register, Caster A/c
statement and Caster Gold balance report. Efficient viewing comes with the zooming
facility wherein you can accommodate large sized reports on the screen by decreasing the
size and vice versa.



Contractor

The contractor module takes care of the work assigned to the contractors and is interfaced
with Inventory, Accounts Payable, Stone Inventory and Return & Repair modules. To
simplify the management, the user can set up contractors, style categories, standard and
specific costs. Contractor Transactions include preparing work bags for contractors,
processing and issuing memos, printing check copies of documents before finalizing them
and much more.



The reports under this section are Contractor Memo report, Balance of Open pieces,
Contractor Invoice report-Memo wise, Contractor Invoice report-Style wise etc. The user
can set different standard and specific costs for a comparative analysis and can maintain
details of the work and labor type for each contractor separately. The Weekly Contractor
report gives details of transactions with the contractor in a week. The Labor Price report
gives you specific information about the labor costs.



Stone inventory

Stone Inventory manages details of each stone in your stock. The user can store the
parameters, size and costs of individual stones in a lot. The user can record stone
receipts, issues or transfers. Stocks can be analyzed by the negative stock statement. The
module segments inventory into Setup, Transactions and Reports. Setup includes defining
the stone parameters, classifying stones with similar properties into lots and calculating
their prices accordingly. These details are helpful when recording the Receipt, Issue and
Transfer of the stones. It is interfaced with the Contractor and Sales Order and Invoicing
modules.



All transactions that you record for the items in stock find a place in the Stone
Inventory module. When an invoice is created in Sales Order & Invoicing, an issue is
automatically created in the Stone Inventory module. Reports include the Receipt register,
Adjustment register, Lot register, Lot mixing register, Stock register, and Transfer
register. The Receipt register gives all the details of stones purchased from the vendors
and those collected from the contractors.



The user can utilize Stone Inventory reports to find out details of receipts, issues,
transfer entries and adjustments made.



Return and repair

This module helps the user set up an excellent front when dealing with your customers. It
is linked with the Inventory module, which eliminates the need to make an entry in the
Inventory module every time there is a return of goods from customers. The Setup section
allows the user to categorize the customers and maintain a record of all the styles
traded. Information once set up can be used later to record the transactions. Transactions
include maintaining details of courier receipts, package details, processing memos for
customers and recording customer returns.



The user can use this information to find out the level of customer satisfaction that
their business is offering and plan for future growth. The user can view or print the
information wanted by specifying the report criteria. For example, the user can make use
of the Courier register to find out the number of packages received for delivery to
customers on a particular date or within a specified period. Reports include Memo
register, Courier register, Return register and Courier register.