There are several basic ways of interfacing with SQL Server.
- Embedded SQL
- CLI (Call Level Interfaces)
- Object Interfaces
Microsoft provides some sort of visual app to manage many of its server products. This Windows Explorer style interface is quite intuitive and provides multiple utilities and wizards.
Here are the rough structures of the "folder list" in two older versions of SQL Server.
|SQL Server 7.0||SQL Server 2000 (8.0) with Enterprise Manager||SQL Server 2005 (9.0) with Management Studio|
There are additional ways to manage and interface with SQL Server.
The most common and most powerful way to interact with SQL Server is by using plain old SQL (or T-SQL)
- In SQL Server 2000, the SQL Server Query Analyzer is a great for entering SQL directly.
- In SQL Server 2005, queries can be done right in Management Studio.
Here are other interfaces for SQL Server 2000
- From time to time use SQL Server's SQL Server Profiler to check data usage.
- From time to time use NT's Performance Monitor to check SQL Server performance via traces, etc. Performance Monitor is conveniently accessed through SQL Server Profiler as well. You may have to use SQL Server Client Configuration Utility first.
Here are other interfaces for SQL Server 2005
- Notification Services Command Prompt
- Reporting Services Configuration
- SQL Server Configuration Manager
- SQL Server Error and Usage Reporting
- SQL Server Surface Area Configuration. SQL Server starts out secure and you have to poke holes into the "surface" area, install stuff, activate stuff, open stuff, etc. as needed.
- Database Engine Tuning Advisor
- SQL Server Profiler
- Not quite an interface but something new in SQL Server 2005 is that nearly every resource has extended properties, custom name & value pairs that are very flexible.
- Configuration Tools
An ANSI standard interface by which SQL statements are incorporated into source code. The code is framed by EXEC SQL tags. Here are some of the languages that can use Embedded SQL:
CLI (Call Level Interfaces)
There are two CLIs that SQL Server supports:
- ODBC (Open DataBase Connectivity)
- DB Library
Both are very powerful but the former is easier to learn, more applicable industry-wide, and utilizes some of SQL Server's features more automatically. CLI is only practical for situations where a company already has a large body of legacy DB Library applications.
This is the modern way to go. Object interfaces use a model of objects, complete with methods and properties, to access SQL Server. This includes:
- SQL-DMO (Distributed Management Objects). This is an object interface specifically for performing SQL Server administrative tasks. The SQL Enterprise Manager itself is layered on top of SQL-DMO. This can be used to programmatically administer SQL Server.
- DAO (Data Access Objects) over ODBC. An older object interface closely tied to JET (Joint Engine Technology).
- RDO (Remote Data Objects) over ODBC. An older object interface closely tied to ODBC.
- ADO (ActiveX Data Objects) over OLE DB (Object Linking and Embedding DataBase). An object interface closely tied to OLE DB. ADO is the recommended SQL Server interface method, especially for Web applications.
- Enters today's date.