Many databases capable of supporting all of the processing needs of an enterprise are complex and difficult to administer. Microsoft® SQL Server™ includes many tools and features that simplify your ability to install, deploy, manage, and use databases. SQL Server provides database administrators with all the tools required to fine-tune SQL Server installations running production online systems. SQL Server is also capable of operating efficiently on a small, single-user system with minimal administrative overhead.
SQL Server version 7.0 reconfigures itself automatically and dynamically while running. If more work is done in SQL Server, it can dynamically acquire additional resources, such as memory. As the workload falls, SQL Server frees the resources back to the system. If other applications are started on the server, SQL Server will detect the additional allocations of virtual memory, and reduce its use of virtual memory to reduce paging overhead. SQL Server can also increase or decrease the size of a database automatically as data is inserted or deleted.
The amount of dynamic reconfiguration in each SQL Server installation can be controlled by database administrators. 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 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 NT® Services, Microsoft Internet Information Servers, Microsoft SNA Servers, and SQL Servers. 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 supports alerting 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 can be highly automated, freeing database administrators to design new databases and applications.
SQL Distributed Management Objects (SQL-DMO) are a set of OLE Automation objects that can be used to code applications with the logic to administer a SQL Server system. This gives application packages the ability to embed SQL Server into their applications transparently. Experienced database administrators can also use SQL-DMO to build applications for many of the common administrative tasks unique to their site.
Routine, recurring tasks for a database 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 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.
When a SQL Server 7.0 compact disc is placed in a CD-ROM drive, the CD has an autorun application that lets the user make several choices:
The installation or upgrade of SQL Server is driven by a GUI application that guides the user through the information needed by SQL Server Setup. The Setup program itself detects automatically if an earlier version of SQL Server is present and, after version 7.0 is installed, asks the user if they want to launch the SQL Server 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 user.
Sites needing to install SQL Server on many servers can take advantage of the SQL Server unattended installation feature to install SQL Server with the same configuration on all the servers.
SQL Server 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 (RAD).
These are OLE Automation objects that can be used to write customized applications to administer a server running SQL Server. These objects were used by Microsoft to build SQL Server Enterprise Manager, proving that they are robust enough to perform any administrative task.
This component allows programmers to develop and test SQL statements interactively. It includes aids such as a graphical display of the execution path of an SQL statement and color coding of different syntax elements to increase the readability of SQL statements.
| Administration Architecture | Application Development Architecture |
| Installing SQL Server |