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.