SQL Server DBA Evolution:
In 1988, Microsoft released its first version of SQL Server. It was designed for the OS/2 platform andwas jointly developed by Microsoft and Sybase. During the early 1990s, Microsoft began to develop a new version of SQL Server for the NT platform. While it was under development, Microsoft decided that SQL Server should be tightly coupled with the NT operating system. In 1992, Microsoft assumed core responsibility for the future of SQL Server for NT. In 1993, Windows NT 3.1 and SQL Server 4.2 for NT were released. Microsoft's philosophy of combining a high-performance database with an easy-to-use interface proved to be very successful. Microsoft quickly became the second most popular vendor of high-end relational database software. In 1994, Microsoft and Sybase formally ended their partnership. In 1995, Microsoft released version 6.0 of SQL Server. This release was a major rewrite of SQL Server'score technology. Version 6.0 substantially improved performance, provided built-in replication, anddelivered centralized administration. In 1996, Microsoft released version 6.5 of SQL Server. This versionbrought significant enhancements to the existing technology and provided several new features,If you are intrested to learn SQL Server DBA please visit:learn ios app development
Whats New in Version 6.5
SQL Server version 6.5 is more than a maintenance release. It includes numerous features that further extend SQL Server. Following are several of the key features found in version 6.5:
Distributed Transaction Coordinator (DTC)
Replication to ODBC subscribers
Internet integration
Improved performance
Data warehousing extensions
Simplified administration
Distributed Transaction Coordinator (DTC)
The Distributed Transaction Coordinator (DTC)
controls transactions that span multiple SQL Server systems. This feature allows applications to update multiple databases in a distributed environment while providing transaction management. Through DTC, a data modification is guaranteed to run to completion or the modification is rolled back in its entirety. For example, if a modification updates data in two servers and the second server crashes during the update, the entire transaction is rolled back from both servers.
Replication to ODBC Subscribers
Version 6.5 has extended replication to database products other than SQL Server. Through Open Database Connectivity (ODBC), SQL Server can replicate changes to products such as Oracle, Sybase, IBM DB2, Access, and other database products. This feature offers administrators and developers a simplified and reliable method for distributing data.
Internet Integration
SQL Server provides direct Internet support through the SQL Web Assistant and Microsoft's Internet Information Server (IIS). The SQL Web Assistant is included with version 6.5; it generates HTML scripts for SQL Server data. This product allows you to create Web pages that contain SQL Server data.
SQL Server version 6.5 also provides direct support for Microsoft's IIS product, which means that complete Internet solutions can be delivered through the combination of SQL Server, NT, and IIS.
Improved Performance
SQL Server version 6.5 delivers improved performance over previous versions through enhancements such as reduced checkpoint serialization, faster sorting and indexing, and improved integration with the NT operating system. Version 6.5 also offers several new counters to help tune SQL Server for maximum performance.
Data Warehousing Extensions
SQL Server version 6.5 provides several data warehousing extensions and improved support for Very Large Databases (VLDB). These extensions include several new commands for online analytical processing (OLAP). Two of these new commands, CUBE and ROLLUP, allow a developer to create a single query that returns a recordset based on multiple dimensions and that contains aggregate information. Version 6.5 also provides improved VLDB support through single table backups/restorations and point-in-time recovery.
Simplified Administration
SQL Server version 6.5 continues to simplify database administration through improvements to the Enterprise Manager and through wizards. In version 6.5, the Enterprise Manager offers a customizable toolbar and menu system, an improved Transfer Manager, and other interface enhancements. Version 6.5 also includes a Database Maintenance wizard that automates common DBA tasks such as backups,
database consistency checks (DBCC), and index maintenance (such as UPDATE STATISTICS).
Features Common to Versions 6.5 and 6.0
Many of the features found in version 6.5 were originally released in version 6.0. Version 6.0 was a significant upgrade from the previous version of SQL Server (version 4.2x). Many of these changes were made in response to the complaint that version 4.2x was better suited to handle the needs of a department rather than an enterprise. Version 6.x meets the demanding requirements of an enterprise and also includes several other features that help differentiate it from its peers.
Enterprise Manager
The Enterprise Manager combines the functionality of version 4.2x's Object Manager and SQL Administrator into a single easy-to-use interface. From the Enterprise Manager, you can administer multiple servers, configure data replication, and develop databases.
NOTE: In addition to managing SQL Server 6.x, you can manage SQL Server 4.2x from
the Enterprise Manager. To do this, you must first run--from your 4.2x version of SQL
Server--the SQLOLE42.SQL script that ships with version 6.x.
Data Replication
Before SQL Server version 6.x, if you wanted replication, you had to buy a replication product or build your own replication services. Neither alternative was very appealing. Data replication products are expensive to purchase and building your own replication service can be complex and time consuming.
Fortunately, SQL Server 6.x provides a robust replication component that can meet the needs of an enterprise. The uses for replication are endless. Data warehousing, distributed processing, and end-user reporting are just a few examples of how SQL Server's data replication component can be used.
SQL Executive
SQL Executive helps automate many of the routine tasks a DBA must perform. Event scheduling, alert notification, replication management, and task management are some of the functions that SQL Executive provides.
NOTE: SQL Executive replaces version 4.2x's SQL Monitor.
OLE Automation
Distributed Management Objects (SQL-DMO) allow developers to tap into the power of SQL Server through the ease of OLE automation. Developers can use Visual Basic, Excel, and other products that support the VBA programming language to build custom administration scripts. These objects simplify the process of creating management scripts by allowing programs to interface with SQL Server through objects, methods, and properties.
Parallel Data Scanning and Read-Ahead Manager Through parallel data scanning and read-ahead algorithms, version 6.x has significantly improved SQL Server performance. Certain types of queries, such as table scans, execute 400 percent faster over version 4.2x.
Multithreaded Kernel
SQL Server version 6.x features a redesigned kernel that results in improved transaction performance andscalability. Previous versions of SQL Server were unable to effectively scale beyond two or three processors. Version 6.x is better suited to take advantage of multiple processors.
Optimizer Improvements
Version 6.x's optimizer has been significantly improved. The likelihood of a proper query execution plan has increased through better index usage and improved subquery support.Also new with version 6.x are optimizer hints. Now you can explicitly force the optimizer to choose an index. Before version 6.x, developers sometimes had to use nonstandard techniques to force theoptimizer to choose an appropriate index.
High-Performance Backup and Restoration
Version 6.x uses parallel optimization techniques to minimize backup and restoration times. These techniques allow Very Large Databases to be backed up and restored in a reasonable amount of time,To get more information please visit:SQl server DBA Ttraining
Very Large Database (VLDB) Support
Earlier versions of SQL Server had a practical size limitation of 50 to 60 gigabytes. Version 6.x can effectively support databases in excess of 100 gigabytes. SQL Server uses parallel optimization techniques to maximize performance. This enables SQL Server to post significant performance gains over previous versions.
Datatypes
The following three datatypes have been added to version 6.x:
Decimal
Numeric
Double-precision
Additionally, an identity property has been added. It is a value that is automatically incremented when a new record is inserted into a table. You can have only one identity column per table.
NOTE: Version 6.x is ANSI SQL 92-compliant.
Data Integrity
Several new data constraints have been added to version 6.x. These constraints relieve the developer from having to code declarative referential integrity (DRI). Constraints are defined with the CREATE TABLE and ALTER TABLE statements. See Table 3.1. for a comparison of data constraints.
Comparison of data constraints.
Version 6.x Earlier Versions
CHECK CREATE trigger or rule
DEFAULT CREATE default
FOREIGN KEY CREATE trigger, sp_foreignkey
PRIMARY KEY CREATE UNIQUE index, sp_primarykey
REFERENCE CREATE trigger
UNIQUE CREATE UNIQUE index
NOTE: In SQL Server 4.2x, the system procedures sp_primarykey and
sp_foreignkey were strictly for documenting primary keys and foreign keys. They do
not enforce data integrity and have been removed from version 6.x.
CHECK Constraint The CHECK constraint limits the range of data values a column can contain. The CHECK constraint can be created at the table or column level.
DEFAULT Constraint A DEFAULT constraint automatically enters a default value into the column when a value is not specified. The DEFAULT constraint can be created at the table or column level.
FOREIGN KEY Constraint The FOREIGN KEY constraint enforces foreign key relationships. It is used with the REFERENCE and PRIMARY KEY constraints.
PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies a primary key and enforces referential integrity. The column it references must contain unique data values and cannot be
NULL. It is used with the REFERENCE and FOREIGN KEY constraints.
REFERENCE Constraint The REFERENCE constraint is used to enforce referential integrity in conjunction with the PRIMARY KEY and FOREIGN KEY constraints.
UNIQUE Constraint The UNIQUE constraint prevents duplicate data values. This constraint is similar to the PRIMARY KEY constraint, except that it allows NULLs.
NOTE: Before version 6.x, referential integrity (RI) could be enforced only through the use of triggers. This meant that you had to build extensive code to enforce RI. With version 6.x, you can use the REFERENCE, PRIMARY KEY, and FOREIGN KEY constraints to enforce
RI. However, you must still use triggers to perform cascading updates and deletes.
Cursors
ANSI-SQL cursors and engine-based cursors are part of version 6.x. In previous versions of SQL Server, cursors could be created only by using DB-LIB or ODBC API calls. SQL Server's cursors are fully scrollable and permit data modifications. ANSI cursors (which are row oriented) are preferred to engine-based cursors (which are set oriented).
Summary
SQL Server 6.x offers significant improvements and enhancements over earlier versions.
To get more information please visit:ios onlline courses
0 Comments:
Post a Comment