SQL Server 2000 Administrator's Pocket Consultant: Administration Overview
Administration Overview
As you get started with Microsoft SQL Server 2000, you should concentrate on these areas:
| • | How SQL Server 2000 works with your hardware |
| • | What versions and editions of SQL Server 2000 are available |
| • | How SQL Server 2000 works with Microsoft Windows-based operating systems |
| • | What administration tools are available |
On This Page
SQL Server 2000 and Your HardwareSuccessful database server administration depends on three things:
The first two ingredients are covered: you're the administrator, you're smart enough to buy this book to help you through the rough spots, and you've enlisted SQL Server 2000 to provide your high-performance relational database management system (RDBMS) needs. This brings us to the issue of hardware. SQL Server 2000 should run on a system with adequate memory, processing speed, and disk space. You also need an appropriate data and system protection plan at the hardware level. Key guidelines for choosing hardware for SQL Server are as follows:
If you follow these hardware guidelines, you'll be well on your way to success with SQL Server 2000. SQL Server 2000 and SQL Server DesktopSQL Server 2000 is distributed in three main editions: Standard, Enterprise, and Developer. In all of these editions, you'll find a server installation and a desktop installation. The server installation includes the full version of SQL Server. The desktop installation includes a scaled-down version of SQL Server with some limitations. The most widely deployed edition is the Standard Edition, which is designed for the average-sized organization. The Standard Edition
While the Standard Edition is a strong database server solution, large organizations will want to consider the Enterprise Edition. The Enterprise Edition adds
As you might expect, the SQL Server 2000 Enterprise Edition runs on Windows NT 4.0 Server Enterprise, Windows 2000 Advanced Server, and Windows 2000 DataCenter. The Developer Edition supports all the features of the Enterprise Edition but is licensed for development use only. Other editions of SQL Server 2000 are available. These editions are the Windows CE Edition, the Personal Edition, and the Desktop Engine. The Windows CE Edition allows you to use SQL Server as the data store on Windows CE devices. The Personal Edition is the version you run when you want to work with the SQL Server desktop. The Desktop Engine is a version of the SQL Server 2000 database engine that can be distributed with third-party applications. All editions of SQL Server 2000 automatically and dynamically configure user connections. This is different from previous versions, where specific limitations were placed on the number of simultaneous user connections. So you don't have to worry about managing user connections as much as you used to. Just keep in mind that as the number of user connections increases so does the amount of resource usage on the server. The server has to balance the workload among the many user connections, which can result in decreased throughput for user connections and the server as a whole. Whether you use the Standard or Enterprise Edition, you have four options for working with SQL Server. You can
The Personal Edition provides a full SQL Server database, but it has some limitations. Features not supported by the Personal Edition include
Additionally, the Personal Edition has limited support for transactional replication and Analysis Services. Personal Edition databases can only be subscribers for transactional replication. Personal Edition databases don't support user- defined OLAP (online analytical processing database) partitions, linked OLAP cubes, custom rollups, and other key analytical functions. Note: As you can see, most of the differences between various editions of SQL Server are below the surface and don't affect the interface. Because of this, I refer to specific editions or differentiate between the server and the desktop installation only when necessary. SQL Server and Windows 2000When you install SQL Server on server operating systems, SQL Server makes several modifications to the environment. These modifications include new system services, additions to the taskbar, integrated authentication, new domain/workgroup accounts, and registry updates. Services for SQL ServerWhen you install SQL Server on Windows NT or Windows 2000, several services are installed on the server. These services include
Note: SQL Server Desktop doesn't use services or service accounts. You'll learn more about managing services and configuring service- related options in Chapter 3, "Managing the Enterprise." Taskbar Extras for SQL ServerA taskbar shortcut is available for SQL Server. You can use this shortcut to manage services and display service status by completing the following steps:
Note: To permanently remove the SQL Server shortcut from the taskbar, you must remove SQL Service Manager as a startup option. Right-click Start on the taskbar and then select Explore All Users. You'll see Windows Explorer with the Start Menu folder selected. Double-click Programs, select Startup, right-click Service Manager, and then select Delete. When prompted to confirm the action, click Yes. Authentication EnhancementsSQL Server security is completely integrated with Windows domain security, allowing for authentication based on user and group memberships as well as standard SQL Server user accounts. These authentication techniques make it much easier to manage logons and security. You can
Service AccountsOn Windows NT and Windows 2000, the MSSQLServer and SQLServerAgent services are configured to use Windows logon accounts. You can also configure additional SQL Server services to use Windows logon accounts. Two types of accounts are supported:
Caution: With Windows accounts, make sure the SQL Server service has Administrator privileges on the local system. If you don't do this, the tasks you can perform with the server are restricted. You'll learn more about accounts and security in Chapter 5, "Microsoft SQL Server 2000 Security." SQL Server and Active DirectoryA directory is an important part of any computing environment. Businesses use directories to store information about objects on the network, such as users, contacts, and computers. You access information in a directory using a directory service. The directory service in Windows 2000 is called Active Directory. Active Directory service is used to store information in the directory and to make the information available to authorized users, services, and applications. SQL Server 2000 includes enhancements for integrating with Active Directory service. Instances of SQL Server and their attributes are registered in Active Directory service during installation. Additional entries are created each time a database, server cube, or data mining model is created. These entries allow users and applications to search the directory for a particular instance of SQL Server and to search for characteristics of the server instance. Custom applications can use Active Directory service to manage replication objects and to analyze directory data using Microsoft SQL Server 2000 Analysis Services. Active Directory service is also used with replication services to provide the ability to browse for and subscribe to publications. For example, you can search for SQL Server publications in Active Directory Users And Computers. Right-click the domain node, select Find and then in the Find Users, Contacts, And Groups dialog box select SQL Server Publications on the Find selection list. Custom applications use the OLE DB Provider for Microsoft Directory Services to access information in Active Directory service. This OLE DB Provider exposes an LDAP and a SQL object interface. The Lightweight Directory Access Protocol (LDAP) interface is used to search Active Directory service using LDAP paths. The SQL interface is used to search Active Directory service using SQL syntax. Using the OLE DB Provider for Microsoft Directory Services, applications can also use Active Directory service as a data source for Data Transformation Services (DTS). The key to using the OLE DB Provider for Microsoft Directory Services is to create a linked server using ADSDSOObject as the provider name and adsdatasource as the data source argument for the sp_addlinkedserver stored procedure. An example follows: EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource GO Results from an Active Directory query are returned in a tabular format that can be queried using SQL Server distributed queries. Linked servers and the sp_addlinkedserver stored procedure are discussed in depth in Chapter 8. SQL Server Administration ToolsSQL Server provides many administration tools. The tools you'll use the most are
The following sections provide brief introductions to these administration tools. Additional details for key tools are provided throughout the book. Keep in mind that you may need administration access to SQL Server to use these utilities. Graphical Administration ToolsSQL Server 2000 provides several types of tools for administration. The graphical administration tools are the ones you'll use the most. You can access these tools by selecting Start, choosing Programs, and then using the Microsoft SQL Server menu. SQL Server 2000 is designed for local and remote management. You can use most of the tools to manage local resources as well as remote resources. For example, in SQL Server Enterprise Manager you can register a new server and then connect to it. Afterward, you can remotely manage the server and all its databases from your system. Table 1-1 provides a list of the key graphical administration tools and their uses. Table 1-1 Quick Reference for Key SQL Server 2000 Administration Tools
Administration Wizards and the TaskpadAdministration wizards are designed to make database administration easier. Wizards guide you through common database administration tasks using a series of dialog boxes, and they free you from having to remember what to enter when. SQL Server provides more than 20 wizards. You start most Administration wizards by selecting Taskpad entries in SQL Server Enterprise Manager (see Figure 1-1). The Taskpad is a key feature in SQL Server 2000. You use the Taskpad to navigate through SQL Server options by clicking hypertext links. While some links access other Taskpad pages, others launch  Figure 1-1: SQL Server Enterprise Manager provides many wizards to make database administration easier. wizards or display database information pages. Buttons on the toolbar allow you to move backward or forward as well as refresh the display if necessary. The Taskpad is disabled by default in full installations and disabled by default in desktop installations. To enable or disable the Taskpad, complete the following steps:
You'll learn more about SQL Server Enterprise Manager in Chapter 3. SQL Server also provides an additional wizard for migrating from previous versions. This wizard is called the SQL Server Upgrade Wizard. Use this wizard to migrate databases from a SQL Server 7.0 database and import the data into an existing SQL Server 2000 installation. You run the SQL Server Upgrade Wizard by selecting Start, choosing Programs, and then choosing the SQL Server Upgrade Wizard option from the Microsoft SQL Server-Switch submenu. During the installation you'll need to stop and restart both database servers; consequently, you'll probably want to perform this operation after regular business hours. Command-Line ToolsThe graphical administration tools provide just about everything you need to work with SQL Server. Still, there are times when you may want to work from the command line, especially if you want to automate installation, administration, or maintenance with scripts. Key command-line tools you'll use include BCP.EXE, ISQL.EXE, OSQL.EXE, TEXTCOPY.EXE, ODBCPING.EXE, and REBUILDM.EXE. These command-line executables are usually stored in the \Mssql\Binn directory. BCP BCP is the bulk copy program. You can use BCP to import and export data or copy data between instances of SLQ Server 2000. BCP's major advantage is speed. It's much faster than standard database import/export procedures. Unfortunately, its command-line syntax makes it much harder to use. The syntax for BCP is shown in Sample 1-1. Sample 1-1. BCP Syntax
bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w Unicode characters]
[-N keep non-text native] [-V file format version] [-q quoted id]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name\instance name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
ISQL ISQL is a SQL query tool that you can run from the command line. ISQL communicates with SQL Server through the DB Library and is used primarily because it runs within scripts. You'll find that ISQL has very little overhead, making it a good choice when system resources are a concern. Sample 1-2 shows the syntax for ISQL. Sample 1-2. ISQL Syntax isql [-U login id] [-P password] [-S server] [-H hostname] [-E trusted connection] [-d use database name] [-l login timeout] [-t query timeout] [-h headers] [-s colseparator] [-w columnwidth] [-a packetsize] [-e echo input] [-x max text size] [-L list servers] [-c cmdend] [-q "cmdline query"] [-Q "cmdline query" and exit] [-n remove numbering] [-m errorlevel] [-r msgs to stderr] [-i inputfile] [-o outputfile] [-p print statistics] [-b On error batch abort] [-O use Old ISQL behavior disables the following] <EOF> batch processing Auto console width scaling Wide messages default errorlevel is -1 vs 1 [-? show syntax summary (this screen)] Note: ISQL does not support connecting to named instances of SQL Server 2000. ISQL will always connect to the default instance of SQL Server. When you start ISQL, you can issue Transact-SQL statements to run queries, execute stored procedures, and more. Because you're working at the command line, these commands aren't executed automatically and you need to use additional commands to tell ISQL when to execute statements, when to ignore statements, and so on. These additional statements must be entered on separate lines and are summarized in Table 1-2. Table 1-2 Commands for Use with ISQL and OSQL
OSQL An alternative to ISQL is OSQL. You'll use OSQL to execute queries using ODBC connections instead of DB Library. The syntax for OSQL is shown in Sample 1-3. With OSQL, you can also use the commands listed in Table 1-2. Sample 1-3. OSQL Syntax osql [-U login id] [-P password] [-S server name\instance name] [-H hostname] [-E trusted connection] [-d use database name] [-l login timeout] [-t query timeout] [-h headers] [-s colseparator] [-w columnwidth] [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers] [-L list servers] [-c cmdend] [-D ODCB DSN name] [-q "cmdline query"] [-Q "cmdline query" and exit] [-n remove numbering] [-m errorlevel] [-r msgs to stderr] [-V severitylevel] [-i inputfile] [-o outputfile] [-p print statistics] [-b On error batch abort] [-u outputfile stored in Unicode format] [-R ODBC use client settings when converting currency, date, and time] [-O use Old ISQL behavior disables the following] <EOF> batch processing Auto console width scaling Wide messages default errorlevel is -1 vs 1 [-? show syntax summary] Textcopy Textcopy is a command-line utility for importing and exporting text or images files with SQL Server. When you import with Textcopy, data from a specified file is copied into SQL Server, replacing an existing text or image value. When you export with Textcopy, a text or image value is copied from SQL Server into a specified file. The syntax and switches for Textcopy are shown in Sample 1-4 and Table 1-3, respectively. Sample 1-4. Textcopy Syntax
TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W "where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]
Table 1-3 Textcopy Switches
Odbcping If you need to verify an ODBC connection between a client and server, you can use Odbcping. You can use Odbcping to perform two types of tests. You can test the ability of a client to connect directly to a server, and you can test the integrity of an ODBC data source. Odbcping is a command-line utility with the following syntax:
odbcping [ {-Sserver_name [\instance_name] | -Ddata_source]
[-Ulogin_id] [-Ppassword]
Note: SQL Server 2000 doesn't install the Odbcping utility during setup. It's located in the \x86\Binn directory on the SQL Server 2000 compact disc. Rebuildm Rebuildm is a utility for rebuilding the master database. Rebuilding the master database removes all database objects and data. Because of this, you'll need to re-create database objects and reload data by restoring backups of system and user databases, or use sp_attach_db to reattach the data and log files if recent copies are available. You need to rebuild the master database only in these situations:
Note: Unlike SQL Server 7, SQL Server 2000 allows you to specify collations at several levels. The default collation is the setting for all system databases, and you can't change this without rebuilding the master database. You can set a different collation when you create databases, tables, parameters, and literal strings. To start the Rebuild Master utility, type rebuildm on the command line. Then follow the prompts.
|
In This Article
|
