1 / 54

Chapter Three

Chapter Three. Administering and Configuring SQL Server 2000. Objectives. Identify the applications installed with SQL Server 2000 Configure SQL Server 2000 with the Enterprise Manager and SQL Query Analyzer Configure SQLMail and SQLAgentMail Create a linked server.

flann
Télécharger la présentation

Chapter Three

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter Three Administering and Configuring SQL Server 2000

  2. Objectives • Identify the applications installed with SQL Server 2000 • Configure SQL Server 2000 with the Enterprise Manager and SQL Query Analyzer • Configure SQLMail and SQLAgentMail • Create a linked server

  3. Client Applications Installed with SQL Server 2000 Figure 3-1: The SQL Server 2000 program group

  4. Client Applications Installed with SQL Server 2000 • Books Online • Contains the documentation and help files associated with SQL Server 2000 • Client Network Utility • Used to determine the versions of various network libraries installed on a computer

  5. Client Applications Installed with SQL Server 2000 • Configure SQL XML Support in IIS • Opens IIS Virtual Directory Management for SQL Server, which allows SQL Server 2000 to be configured to listen for requests over the Hypertext Transfer Protocol (HTTP) • Most common protocol used to transfer information from Web servers to Web browsers • Enterprise Manager • Management tool for administering and configuring SQL Server 2000 instances

  6. Client Applications Installed with SQL Server 2000 • Import and Export Data • Invokes Data Transformation Services (DTS) Import/Export Wizard • Profiler • Diagnoses queries not running at acceptable speeds • Records set of actions that lead to a problem and replay steps to replicate problems • Monitors how queries perform under specific workloads • Performs security audits of actions occurring on a SQL Server 2000 instance for later review

  7. Client Applications Installed with SQL Server 2000 • Query Analyzer • Standard query tool for use with SQL Server 2000 • Server Network Utility • Changes network libraries for installed instance of SQL Server 2000

  8. Client Applications Installed with SQL Server 2000 • Services Manager • Allows various services associated with SQL Server 2000 to be stopped • Has access to the following services: • SQL Server service • SQL Server Agent service • Microsoft Search service • MS DTC service • MSSQLServerOLAPService service

  9. Server Groups Figure 3-2: SQL Server Enterprise Manager Figure 3-3: Creating Server Groups in Enterprise Manager

  10. Server Groups Figure 3-4: Server Groups screen

  11. Registering Servers • To complete a successful registration, you must supply the following about an instance: • Name of the SQL Server 2000 instance • Authentication mode used to connect to the SQL Server • Server group within which the new instance will reside in Enterprise Manager

  12. Registering Servers Figure 3-5: Registered SQL Server Properties screen

  13. Query Analyzers • Tool used for developing and executing T-SQL statements • Can be started in the following ways • By entering command isqlw in command prompt window or from Run prompt • By clicking Start and navigating to Programs, highlighting Microsoft SQL Server and then clicking on Query Analyzer • Clicking the Tools menu in Enterprise Manager and then clicking on the SQL Query Analyzer

  14. Query Analyzers Figure 3-6 SQL Query Analyzer

  15. Configuring a Registered SQL Server Figure 3-7: Accessing the Properties of a registered instance of SQL Server 2000

  16. Configuring a Registered SQL Server Figure 3-8: General Tab of the SQL Server Properties screen • General tab displays the various software and hardware properties of the computer on which SQL Server 2000 is installed

  17. Configuring a Registered SQL Server Table 3-1: Default startup parameters

  18. Configuring a Registered SQL Server Table 3-2: Additional startup options

  19. Server Setting Tab • Query governor threshold • Maximum query cost a query can have while still being able to run Figure 3-9: Server Settings tab of SQL Server Properties screen

  20. Server Setting Tab • Way in which SQL Server 2000 handles two-digit years is set from the Server Settings Tab • Two-digit years less than or equal to last two digits of cutoff year are in same century as that of cutoff year • Years greater than last two digits of cutoff year are in century previous to that of cutoff year • Rules are not applied for explicit four-digit years Figure 3-10: Configured values and Running values

  21. Memory Tab • Memory tab allows minimum and maximum size parameter for amount of memory that SQL Server 2000 will use Figure 3-11: Memory tab of the SQL Server Properties screen

  22. Database Settings Tab • Database Settings tab allows several global properties for databases across an instance Figure 3-12: Database Settings tab of SQL Server Properties screen

  23. Processor Tab • Thread • Operating system component that allows multiple simultaneous requests to a multiuser application to execute as separate tasks Figure 3-13: Processor tab of SQL Server Properties screen

  24. Security Tab • Security tab provides an interface to alter authentication type and security auditing level of a SQL Server 2000 instance Figure 3-14: Security tab of the SQL Server Properties screen

  25. Connections Tab • Connections tab allows maximum number of simultaneous user connections to be specified Figure 3-15: Connections tab of the SQL Server Properties screen

  26. Configuring Server Options with Sp_configure • Most configuration options modified in SQL Server properties screen are available through a system-stored procedure called sp_configure • The sp_configure procedure takes two parameters: • @configname • Name of the configuration setting to change • @configvalue • New value to assign to the specified configuration setting

  27. Configuring Server Options with Sp_configure Table 3-3: Configuration names for use with sp_configure

  28. Configuring Server Options with Sp_configure Table 3-3: Configuration names for use with sp_configure (cont.)

  29. Configuring E-mail for SQL Server 2000 • There are two services of SQL Server 2000 that provide mail functionality: • SQLMail • SQLAgentMail • Both services can connect to the following e-mail server types: • Microsoft Exchange Server • Microsoft Windows NT Mail • Post Office Protocol (POP3)

  30. Configuring E-mail for SQL Server 2000 Figure 3-16: SQL Mail Configuration screen

  31. Sending Mail with SQLMail • Extended stored procedures • Function that is part of an external software object (DLL), which is coded using the SQL Server 2000 Extended Stored Procedure API Figure 3-17: SQL Server Agent Properties

  32. Sending Mail with SQLMail Table 3-4: SQL Server 2000 extended stored procedure for e-mail

  33. Xp_sendmail Parameters • @recipients • E-mail addresses that e-mail is sent to • @message • Body of the e-mail message to be sent • @query • Valid T-SQL query • @attachments • Semicolon-delimited list of paths for files to be added as an attachment • @copy _recipients • Semicolon-delimited list of addresses to which message is copied

  34. Xp_sendmail Parameters • @blind_copy_recipients • Semicolon-delimited list of addresses to which message is blind copied • @subject • Subject of e-mail to be sent • @type • E-mail message type • @attach_results • Specifies whether query results are returned with the query in the e-mail body

  35. Xp_sendmail Parameters • @no_header • Specifies whether the column headings are returned with the query in the e-mail body • @width • Specifies the line width of the output of a query to prevent line breaks from being inserted into query output • @separator • Text column separator used to format results • @echo_error • Appends any error messages encountered from the execution of the query to the body of the e-mail message

  36. Linked Servers • OLE DB • Microsoft specification for an application programming interface used to access data in relational databases, spreadsheets, mail stores or any data repository with an OLE DB provider • OLE DB Providers • Software components that provide access to various data source types

  37. Linked Servers Figure 3-18: SQL Server linked server architecture

  38. Setting Up a Linked Server Figure 3-19: Linked Servers in Enterprise Manager • Linked Servers are set up through either a graphic interface in Enterprise Manager or by executing system-stored procedures

  39. Setting Up a Linked Server Figure 3-20: General tab of the New Linked Server screen in Enterprise Manager

  40. Setting Up a Linked Server • Linked servers can be created by using system-stored procedure called sp_addlinkedserver • Sp_addlinkedserver takes the following arguments: • @server • System name of server being added as linked server • @srvproduct • Product name of OLE DB provider • @provider • Programmatic identifier for OLE DB provider specified by @srvproduct

  41. Setting Up a Linked Server • Sp_addlinkedserver takes the following arguments (cont.): • @datasrc • Data source name as interpreted by OLE DB provider • @location • Location of data source as interpreted by OLE DB provider • @provstr • Provider-specific OLE DB connection string that identifies a data source • @catalog • Name of the database that resides on the linked server

  42. Setting Up a Linked Server Figure 3-21: Security tab of the New Linked Server screen in Enterprise Manager

  43. Setting Up a Linked Server • Security information can be set up using system-stored procedure called sp_addlinkedsrvlogin • Sp_addlinkedsrvlogin takes the following arguments: • @rmtsrvname • Name of the linked server that login applies to • @useself • Specifies the name of the login used to connect to the linked server

  44. Setting Up a Linked Server • Sp_addlinkedsrvlogin takes the following arguments (cont.): • @locallogin • Login the local server • @rmtuser • Name used to connect to the linked server • @rmtpassword • Password associated with the login supplied for the @rmtuser parameter

  45. Command Line Utilities • Osql utility • Allows T-SQL statements to be run from the command line Figure 3-22: Osql Command Line utility

  46. Osql Options • -L • Lists locally configured servers and name of servers broadcasting on the network • -U • Login name used to connect to SQL Server 2000 • -P • Password for the login • -E • Causes osql to connect to SQL Server 2000 using a trusted connection instead of requesting a password • -S server_name[\instance_name] • Name of instance of SQL Server 2000 osql will connect to

  47. Osql Options • -d database_name • Name of database the query will execute against • -l time_out • Login timeout for osql • -t time_out • Query time used by osql • -s col_separator • Specifies character used as a column separator • -w column_width • Screen width for output queries

  48. Osql Options • -q “query” • Executes query specified when osql starts but does not exit osql when query is completed • -Q “query” • Executes query specified when osql starts and causes osql to exit when query is completed • -I input_file • Full path of a file containing SQL statements or stored procedures • -o output_file • Identifies file that receives output from osql • -p • Includes performance statistics when queries are run

  49. SQLDIAG Utility • SQLDIAG utility generates informational file consisting of diagnostic information and query trace information (if a query is running) • Generates information that Microsoft Product Support will use to help diagnose problems with a SQL Server 2000 implementation

  50. SQLDIAG Utility • File generated by SQLDIAG contains: • Text of all error logs • Registry information • DLL version information

More Related