Several techniques exist to efficiently connect to and manipulate data from Microsoft SQL Server. SQL Server supports a variety of approaches for integrating and optimizing your client application with local or distributed database servers.
ODBC/DRDA Drivers to Access Host Data
Many applications use the Open Database Connectivity (ODBC) programming interface to access local and server-based databases, while distributed databases on IBM hosts are usually managed through the Distributed Relational Database Architecture (DRDA) protocol defined by IBM.
SQL Server includes the StarSQL ODBC/DRDA drivers for Windows and Windows NT clients. With these drivers, applications designed to use the ODBC interface and structured query language (SQL) can access databases located on IBM hosts that use the DRDA protocol to manage distributed data, without requiring a host-based database gateway. These drivers allow ODBC-enabled applications (such as Microsoft Excel and Microsoft Access) to query, create, delete, and update tables in the following host databases: DB2 for MVS, SQL/DS for VM and VSE, and DB2/400 for OS/400.
Stored Procedures, Triggers, and Cursors
Microsoft SQL Server provides several mechanisms that can be used to optimize your application as well as take advantage of SQL Server's inherent functionality.
You can refine your applications by using stored procedures, which are compiled collections of SQL statements and control-of-flow language that execute very quickly. Stored procedures greatly enhance the power, efficiency, and flexibility of SQL Server, and dramatically improve the performance of SQL statements and batches. Stored procedures on other SQL Servers to which the client process is not in direct connection can be executed if the remote server has been set up to allow remote logins.
A trigger is a kind of stored procedure that goes into effect when you modify data in a specified table. Triggers are often created to enforce business rule consistency among logically related data in different tables. Triggers are automatic, working no matter what caused the data modification—a user's entry or an application action.
Server cursors allow individual row operations to be performed on a given results set or on the entire set. In SQL Server version 6.0 and later, ANSI SQL cursors are server-based. In earlier releases, cursors were provided only through the DB-Library and Open Database Connectivity (ODBC) cursor APIs. The addition of cursors to the server provides an efficient way for single-row operations to occur within a given results set. ANSI SQL cursors support only single-row cursors. Each fetch (move) will return only one row from the results set. Server cursors enhance the general use of relational databases by allowing row-level operations when set-oriented operations are impractical. In relational systems, set operations are preferred, to which cursors provide a powerful complement. Using cursors, multiple operations can be performed row by row against a results set with or without returning to the original table. In other words, cursors conceptually return a results set based on tables within the database or databases.
For more information about using stored procedures, triggers, and cursors, see "Microsoft SQL Server Programmer's Toolkit" in the "Database and Messaging Services" section of the Microsoft Platform SDK.
SQL Distributed Management Objects
SQL Distributed Management Objects (SQL-DMOs) are 32-bit Component Object Model (COM) objects for the Windows 95 and Windows NT operating systems. The SQL-DMO objects are Automation compatible. The SQL-DMO object model includes objects, properties, methods, and collections that are used to write programs that can administer multiple SQL Servers distributed across a network. SQL-DMO programs can range from simple Visual Basic scripts to complex Visual C++ applications. Using SQL-DMO you can access objects such as SQL Server devices, logins, language, remote servers, configuration, and security. You can also manage application environments such as backups and permissions, databases, indexes, tables, stored procedures, and triggers.
Integrated Security with SQL Server
Integrated security combines SQL Server login security with Windows NT security. This allows a network user to log in to a SQL Server without supplying a separate login identifier or password. Users can maintain one login identifier and password for both Windows NT and SQL Server. Integrated security lets SQL Server applications take advantage of Windows NT security features, including encrypted passwords, password aging, domain-wide user accounts, and Windows-based user administration. (For more information about these features, see your Windows NT Server documentation.)
Login security integration operates over network protocols that support authenticated connections between clients and servers. Such connections are referred to as trusted connections. Trusted connections include multiprotocol and named pipes sessions from other Windows NT-based workstations, from Windows for Workgroups-based clients, from Windows 95-based clients, and from Microsoft LAN Manager clients running under the Microsoft Windows or MS-DOS operating systems. Using the multiprotocol Net-Library, trusted connections can also be made over the NWLink IPX/SPX protocol from Windows version 3.1 clients using Novell software. (In this case, the user will be prompted for a Windows NT username and password at SQL Server connect time.)
For more information about Windows NT system security, see the "Windows Base Services" section of the Microsoft Platform SDK.