Many databases capable of supporting all of the processing needs of an enterprise are complex and difficult to administer. Microsoft® SQL Server™ 2000 includes many tools and features that simplify the process of installing, deploying, managing, and using databases. SQL Server 2000 provides database administrators with all the tools required to fine-tune SQL Server 2000 installations running production online systems. SQL Server 2000 is also capable of operating efficiently on a small, single-user system with minimal administrative overhead.
SQL Server 2000 reconfigures itself automatically and dynamically while running. As more users connect to SQL Server 2000, it can dynamically acquire additional resources, such as memory. As the workload falls, SQL Server 2000 frees the resources back to the system. If other applications are started on the server, SQL Server 2000 will detect the additional allocations of virtual memory to those applications, and reduce its use of virtual memory to reduce paging overhead. SQL Server 2000 can also increase or decrease the size of a database automatically as data is inserted or deleted.
Database administrators can control the amount of dynamic reconfiguration in each instance of SQL Server 2000. A small database used by someone not familiar with databases can run with the default configuration settings, in which case it will configure itself dynamically. A large production database monitored by experienced database administrators can be set up to give the administrators full control of configuration.
SQL Server 2000 offers database administrators several tools for managing their systems:
MMC supports the management of multiple types of servers from a single console, such as Microsoft Windows® 2000 Services, Microsoft Internet Information Servers, Microsoft SNA Servers, and instances of SQL Server 2000. An administrator at a single console has the ability to manage all the servers on a worldwide network. SQL Server Enterprise Manager shares a subset of the MMC user interface for Web administration. It presents all SQL Server objects in a hierarchical console tree with an easy-to-use graphical user interface.
It also alerts administrators when certain warning conditions occur, and can even be programmed to take corrective action.
It also profiles server events such as the acquisition of locks.
The wizard makes recommendations on index changes that would speed up the SQL statements.
Administering SQL Server 2000 can be highly automated, freeing database administrators to design new databases and applications.
SQL Distributed Management Objects (SQL-DMO) is a set of Automation objects that can be used to code applications with the logic to administer an instance of SQL Server 2000. This gives application packages the ability to transparently embed SQL Server 2000 into their applications. Experienced database administrators can also use SQL-DMO to build applications for many of the common administrative tasks unique to their site. SQL Server 2000 also includes support for the Windows Management Instrumentation (WMI) API. The WMI support maps over the SQL-DMO API.
Routine, recurring tasks can be implemented as automatically scheduled jobs that run without constant supervision by an operator. For example, after a database administrator has designed a backup procedure for a server, the backups can be implemented as a set of automatic jobs.
SQL Server 2000 can also be programmed to raise alerts when specific events occur. The actions taken by alerts can take several forms:
For example, if the number of Full Scans (a scan of an entire table or index) in a server exceeds a specific number, an e-mail can be sent to the database administrator for investigation.
The SQL Server 2000 compact disc has an autorun application that enables users to make several choices, such as:
The installation or upgrade of SQL Server 2000 is driven by a graphical user interface (GUI) application that guides users through the information required by SQL Server 2000 Setup. The Setup program itself detects automatically if an earlier version of SQL Server is present and, after SQL Server 2000 is installed, asks users if they want to launch the SQL Server 2000 Upgrade Wizard to quickly guide them through the upgrade process. The entire installation or upgrade process is accomplished quickly and with minimal input from the users.
Sites needing to install SQL Server 2000 on many servers can take advantage of the SQL Server unattended installation feature to install SQL Server with the appropriate configuration on all the servers.
SQL Server 2000 has several advantages in building applications:
These APIs include powerful, low-level APIs, such as ODBC and OLE DB, that allow programmers control over the interaction between the application and database. They also include APIs such as ADO that support Rapid Application Development.
These are Automation objects used to write customized applications to administer a server running SQL Server.
This component enables programmers to develop and test Transact-SQL statements interactively. It includes aids such as a graphical display of the execution plan and performance statistics of a Transact-SQL statement. It color-codes the different syntax elements to increase the readability of Transact-SQL statements, and includes an integrated Transact-SQL debugger. It also has an Object Browser that determines the attributes of the tables, views, stored procedures, and other objects in a database, and supports templates used to speed the building of complex statements.
Analysis Services and Meta Data Services supply OLE DB Providers that support the online analytical process (OLAP) extensions to OLE DB and ADO. These allow the easy integration of OLAP and meta data processing in applications using the Microsoft data-access APIs. English Query also supports an object-model API that allows the easy integration of English Query functionality into applications accessing SQL Server 2000 databases and Analysis Services cubes through OLE DB or ADO.
SQL Server 2000 introduces several items that improve the power and flexibility of Transact-SQL, as well as increasing programmer productivity. Cascading referential integrity actions can replace the need to develop triggers to enforce referential integrity actions when you update or delete rows. INSTEAD OF triggers can be used to greatly extend the types of update actions that views can support, and you can now specify which AFTER triggers fire first or last. User defined functions can be used to introduce new functionality to Transact-SQL statements.
SQL Server supports using Windows NT and Windows 2000 user and domain accounts as SQL Server 2000 login accounts. This is called Windows Authentication. Users are validated by Windows 2000 when they connect to the network. When a connection is formed to SQL Server, the SQL Server client software requests a trusted connection, which can be granted only if validated by Windows 2000. SQL Server then does not have to validate the user separately. Users do not have to have separate logins and passwords for each SQL Server system to which they connect.
With Windows Authentication, no passwords are transmitted to the server running SQL Server, eliminating a security concern. Also, SQL Server 2000 supports the use of Secure Sockets Layer encryption of all network traffic between their client computer and an instance of SQL Server.
SQL Server 2000 also provides auditing, which allows you to trace and record the activity in an instance of SQL Server. SQL Server 2000 auditing can support the C2 level of security defined by the United States government. For more information, see the Trusted Facilities Manual.