The redesign of the server architecture results in new and improved server functionality. Changes have been made to the server configuration options and to the server utilities such as backup, restore, DBCC, and bulk copy. Microsoft® SQL Server™ version 7.0 provides for parallel query execution, more powerful security, and improved stored procedure performance. With SQL Server 7.0, many advancements have been made to SQL Server replication:
Many server configuration options have been streamlined and simplified. For example, by default the server dynamically adjusts its memory and lock resource use allocated. SQL Server 7.0 increases resources when necessary without over-committing them and decreases the resources used when they are no longer needed. Earlier versions of SQL Server required manual adjustment of these settings.
SQL Server 7.0 server utilities, such as backup and restore, run much faster and have less effect on server operations. SQL Server 7.0 also includes a variety of new features designed to support the underlying database architecture and to provide more flexible system management.
SQL Server 7.0 also includes a number of new features designed to reduce database backup and recovery times. A differential backup captures only those data pages that have changed after the last database backup. Many times, differential backups can eliminate much of the time the server spends rolling transactions forward. With SQL Server 7.0, a portion of the database can be restored or rolled forward to minimize recovery time in the event of media failure. Restoring a backup is easy because the restore process automatically creates the database and all necessary files. SQL Server 7.0 supports backups using the Microsoft Tape Format, allowing SQL Server backups to share the same tape media with other backups, such as those written by the Microsoft Windows NT® Backup program.
The DBCC statements have been redesigned to provide substantially improved performance. In addition, bulk copy operations now have the option to validate constraints and fire triggers as the data is loaded.
The bulk copy utility (bcp) uses ODBC to communicate with SQL Server and supports all SQL Server 7.0 data types. The SQL Server 7.0 server uses improved index maintenance strategies that make loading data into tables with indexes more efficient than earlier versions. Bulk copy operations are also faster.
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. Parallel query execution is enabled by default.
SQL Server 7.0 includes an enhanced security architecture that is better integrated with Windows NT and provides increased flexibility. Database permissions can now be assigned directly to Windows NT users. You can define SQL Server roles to include not only Windows NT users and groups but also SQL Server users and roles. In addition, 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. You can now optionally manage database access and permissions using Windows NT groups. New fixed server and database roles such as dbcreator, db_owner, and securityadmin provide more flexibility and improved security than the system administrator login.
The stored procedure model has been enhanced in SQL Server 7.0 to provide improved performance and increased application flexibility. When a stored procedure is compiled and placed in the procedure cache, that one copy of the compiled plan is shared by all users of the stored procedure. In previous releases, each concurrent user would have a separate copy of the compiled plan. SQL statements submitted through the ODBC SQLPrepare function and the OLE DB ICommandPrepare interface can also share plans. Simple ad hoc plans are shared as well. Prepared and ad hoc plans reuse is supported further by passing parameter markers all the way through to the database. Deferred name resolution allows you to create stored procedures referencing objects that don’t yet exist. This provides more flexibility for applications that create and then use tables as part of their processing.
SQL Server 7.0 builds on SQL Server’s compliance with the SQL-92 standard by focusing on SQL-92 as the preferred SQL dialect. SQL Server 7.0 addresses several inconsistencies in earlier versions of SQL Server, including several differences between actual and documented behaviors. SQL Server 7.0 also fixes several problems in earlier versions of SQL Server on which an application might have accidentally relied. In the small number of cases where a change in the default behavior of SQL Server 7.0 may affect existing applications, options have been provided to retain the pre-7.0 behavior. These options are controlled by the sp_dboption or sp_dbcmptlevel stored procedures.
SQL Server 7.0 includes many new capabilities and improvements to the publish and subscribe replication services that were provided by SQL Server 6.5. The most notable feature is update replication. Using update replication, data replicated by SQL Server 7.0 can be modified at multiple sites. Update replication is a relatively advanced topic with different solutions being appropriate for different applications. Thus, SQL Server 7.0 includes several styles of update replication, providing a spectrum of replication solutions with different features and benefits.
The features and architecture of SQL Server 6.5 replication remain at the core of SQL Server 7.0 replication services. As always, replication is built directly into SQL Server 7.0 and SQL Server Enterprise Manager, and is not a separate add-on. SQL Server 7.0 replication offers many usability improvements and enhancements, making replication significantly easier to set up, administer, deploy, monitor, and troubleshoot. Wizards are included for most common replication tasks. SQL Server 7.0 also includes enhancements for Internet replication. Anonymous subscriptions and built-in support for Internet distribution simplify data replication to the Internet.
SQL Server 7.0 also includes COM interfaces that open up the store-and-forward replication services. This allows data providers other than SQL Server to use the SQL Server 7.0 replication infrastructure to publish their data. SQL Server 7.0 now provides completely heterogeneous data-replication services.
SQL Server Enterprise Manager is based on a new common server management environment called Microsoft Management Console (MMC). This shared framework provides a consistent user interface for Microsoft server applications.
Using SQL Server Enterprise Manager, you can examine and configure your SQL Server 7.0 system by setting server properties, database properties, remote server properties, and security properties. You can create and alter tables, views, stored procedures, rules, defaults, and user-defined data types, as well as manage alerts and operators, view error logs, create Web Assistant jobs, create and manage full-text indexes, and import and export data.
SQL Server Enterprise Manager includes full-featured job creation and scheduling. Jobs can be simple, single-step commands that are scheduled to run on a regular basis. Alternatively, they can be complex, multistep jobs that completely control flow and notification options. You can even establish multiple schedules for a single job. Job steps can be created using Transact-SQL, Microsoft Visual Basic® Scripting Edititon, Microsoft JScript®, or operating-system commands.
SQL Server 7.0 includes these major SQL Server Enterprise Manager features:
SQL Executive is now called SQL Server Agent. SQL Server Agent manages jobs, alerts, operators, and notifications, as well as replication jobs. SQL Server Agent allows multitasking, multischeduling, and multiserver and idle-time jobs.
SQL Server Service Manager is now a taskbar application. SQL Server Service Manager enables you to start, stop, and pause the MSSQLServer, MSDTC, MSSearch (only start and stop are applicable), and SQLServerAgent services, and to view their status at any time. When you select SQL Server Service Manager from the Start menu, the Service Manager icon appears minimized in the taskbar by default. Multiple instances of the Service Manager are represented by multiple icons in the taskbar.