MASTERING SQL SERVER 2000- P8

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

63
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'mastering sql server 2000- p8', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: MASTERING SQL SERVER 2000- P8

320 CHAPTER 9 • USING SQL SERVER ENTERPRISE MANAGER • New maintenance plan • Maintenance plan history • Backup database • Restore database • Truncate log • Shrink database • Modify data file sizes • Modify log file sizes The Table Info page of the taskpad lists all of the tables and indexes within the database. For each table, this page also shows the number of rows of data that the table currently contains. A bar graph shows you the amount of space occupied by each table and index. The Wizards page of the taskpad offers another way to invoke any of the SQL Server Wizards. From any database node, you can perform common database tasks by using the shortcut menu. These include: • Create a new database • Create new database objects • Delete an existing database • Import data • Export data • Create maintenance plan • Generate SQL scripts • Back up database • Restore database • Shrink database • Detach database • Copy subscription database • View replication conflicts
THE SQL SERVER ENTERPRISE MANAGER TREE 321 NOTE You’ll learn more about databases in Chapter 10. Diagrams When you click a Diagrams node, the right pane of SQL Server Enterprise Manager shows all of the database diagrams that have been created for the database. A single database might have no database diagrams, a single database diagram, or multiple data- base diagrams representing its structure. Double-clicking a database diagram will open it in the database diagram designer. From the Diagrams node, you can create and delete database diagrams. You can create new database diagrams with the node’s shortcut menu, and you can delete database diagrams with the individual diagram’s shortcut menu. This is typical of how all the objects in Enterprise Manager work. NOTE You’ll learn more about database diagrams in Chapter 11. Tables When you click a Tables node, the right pane of SQL Server Enterprise Manager shows all of the tables in the current database, as you can see Figure 9.11. For each table, SQL Server Enterprise Manager lists the table name, the owner name, the type of table (System or User), and the date on which the table was created. PA R T III Digging into SQL Server
322 CHAPTER 9 • USING SQL SERVER ENTERPRISE MANAGER FIGURE 9.11 Listing of tables in SQL Server Enterprise Manager From the Tables node, you can create and delete tables, as well as import and export data. Double-clicking a table opens the property sheet for that table. By right-clicking a table, you can perform other table operations: • Design table • Rename table • Delete table • Copy table • Open table (all rows or top n rows) • Open query based on the table
THE SQL SERVER ENTERPRISE MANAGER TREE 323 • Add a full-text index to the table • Manage indexes • Manage triggers • Manage permissions • Import data • Export data • Create a publication (for replication) • Generate SQL scripts • Display dependencies The Dependencies dialog box is especially useful if you’re considering modifying an object. This dialog box (shown in Figure 9.12) tells you which objects the selected table depends on and which objects depend on the selected table. Both direct and indirect dependencies are shown. For example, in Figure 9.12, the CustOrderHist stored procedure has a sequence of 2, indicating that it depends on another object that depends directly on the Orders table. Checking the Show First Level Dependency Only box will limit the display to objects that have a sequence of 1. FIGURE 9.12 The Dependencies dialog box PA R T III Digging into SQL Server
324 CHAPTER 9 • USING SQL SERVER ENTERPRISE MANAGER NOTE You’ll learn more about tables in Chapter 11. Views If you select a Views node in SQL Server Enterprise Manager, the right-hand pane will display a list of all the views in the current database, along with their owner, type, and creation date. Figure 9.13 shows this list for a typical database. FIGURE 9.13 Views in SQL Server Enterprise Manager From the Views node, you can create new views and delete existing views. You can also choose to hide some of the columns that are normally shown for each view. The shortcut menu for individual views lets you perform basic operations: • Design view • Open view (all rows or top n rows) • Open query based on the view • Delete view • Copy view • Rename view
THE SQL SERVER ENTERPRISE MANAGER TREE 325 • Manage triggers • Manage permissions • Generate SQL scripts • Display dependencies Double-clicking a view will open the property sheet for the view. On the property sheet, you can modify the permissions for the view, check the syntax of the view, or even change the SQL statement that creates the view. Figure 9.14 shows the property sheet for a view. FIGURE 9.14 Property sheet for a view PA R T III NOTE You’ll learn more about views in Chapter 13. Digging into SQL Stored Procedures As you’d expect by now, if you select a Stored Procedures node in SQL Server Enter- prise Manager, the right-hand pane will display a list of all the stored procedures in Server the current database, along with their owner, type, and creation date. Figure 9.15 shows this list for a typical database.
326 CHAPTER 9 • USING SQL SERVER ENTERPRISE MANAGER FIGURE 9.15 Stored procedures in SQL Server Enterprise Manager From the Stored Procedures node, you can create new stored procedures and delete existing stored procedures. You can also choose to hide some of the columns that are normally shown for each stored procedure. The shortcut menu for individual stored procedures lets you perform basic operations: • Copy stored procedure • Delete stored procedure • Rename stored procedure • Manage permissions • Create new publication • Generate SQL scripts • Display dependencies Double-clicking a stored procedure will open the property sheet for that stored pro- cedure, which includes the SQL statements that make up the stored procedure, as well as the ability to edit permissions and check syntax.
THE SQL SERVER ENTERPRISE MANAGER TREE 327 NOTE SQL Server Enterprise Manager does not provide a way to display any rows that might be retrieved by a stored procedure. You’ll learn more about stored procedures in Chapter 14. Users If you click a Users node, you’ll see a list of all the users for the current database. Users are specific to a database (unlike logins, which apply to entire servers) and are the basis for permissions within a database. As you can see in Figure 9.16, the user list shows the name, associated login name (if any), and whether that user is permitted in the database. FIGURE 9.16 User list in SQL Server Enterprise Manager You can create and delete users from the Users node. The shortcut menu associated with an individual user object lets you manage the permissions associated with that user. NOTE You’ll learn more about users (and the other facets of SQL Server security) in Chapter 18. PA R T III Roles Clicking a Roles node will show you a list of all the roles in the current database. Roles are another part of the SQL Server security mechanism. They allow you to manage Digging into SQL permissions for groups of users rather than for individual users. There are two types of roles: application roles (designed for client-side validation of user identity) and stan- dard roles (containing SQL Server users). Figure 9.17 shows a typical list of roles. Server
328 CHAPTER 9 • USING SQL SERVER ENTERPRISE MANAGER FIGURE 9.17 List of roles in SQL Server Enterprise Manager From the Roles node itself, you can create and delete roles. Double-clicking a role shows you the properties of that role, including the users in the role and the permis- sions that they are assigned. NOTE You’ll learn more about roles in Chapter 18. Rules Clicking a Rules node will show you all the rules in the current database. Rules are conditions expressed in T-SQL syntax (for example, @salary < 20000) that can be used to limit the data contained in columns of a table. TI P You usually won’t find any rules in SQL Server 2000 databases. Rules are now con- sidered to be obsolete and have been largely replaced by constraints. You’ll find further information about rules in Chapter 4.
THE SQL SERVER ENTERPRISE MANAGER TREE 329 Defaults If you click a Defaults node, the right-hand pane of SQL Server Enterprise Manager will show you all the defaults in the current database. Figure 9.18 shows such a list of defaults. FIGURE 9.18 Defaults in SQL Server Enterprise Manager A default is a default value that can be attached to one or more table columns for use when a value is not explicitly supplied for that column in a new row of the table. From the Defaults node, you can create and delete defaults. Double-clicking an individual default will show you the properties for that default. TI P Like rules, defaults are largely obsolete. For the most part, you should use default constraints instead of defaults in your database designs. There’s further information on defaults in Chapter 4. User Defined Data Types When you click a User Defined Data Types node, SQL Server Enterprise Manager shows you all of the user-defined datatypes in the current database. You can think of PA R T user-defined datatypes as aliases for built-in datatypes. Figure 9.19 shows the user- defined datatypes in a typical database. III FIGURE 9.19 User-defined datatypes Digging into SQL Server
330 CHAPTER 9 • USING SQL SERVER ENTERPRISE MANAGER You can use the shortcut menu for a user-defined datatype to perform basic opera- tions on the user-defined datatype: • Copy datatype • Rename datatype • Delete datatype • Generate SQL script • Display dependencies Double-clicking a user-defined datatype will show you the properties for that user- defined datatype. NOTE You’ll learn more about user-defined datatypes in Chapter 11. User Defined Functions When you click a User Defined Functions node, SQL Server Enterprise Manager shows you all of the user-defined functions in the current database. User-defined functions provide callable subroutines for T-SQL code. You can use the shortcut menu for a user-defined function to perform basic opera- tions on the user-defined function: • Copy function • Delete function • Manage permissions • Generate SQL script • Display dependencies Double-clicking a user-defined function will show you the properties for that user- defined function. NOTE User-defined functions are covered in more detail in Chapter 5. Full-Text Catalogs When you click a Full-Text Catalogs node, SQL Server Enterprise Manager shows you in its right-hand pane a list of all full-text catalogs in the current database.
THE SQL SERVER ENTERPRISE MANAGER TREE 331 From a Full-Text Catalogs node, you can create, repopulate, rebuild, or remove all catalogs. The individual full-text catalog nodes let you perform these operations for an individual catalog, as well as modify the schedule for automatic operations. Double- clicking a full-text catalog object will show you all the properties for that catalog. TI P You’ll find a Full-Text Catalogs node only if the server has had full-text indexing enabled. You can find more information on Full-Text Search in Chapter 6. Pull Subscriptions When you click a Pull Subscriptions node, SQL Server Enterprise Manager shows you all of the pull subscriptions for the current database. A pull subscription is a replication task that pulls in data from another server to the current database. From a Pull Subscriptions node, you can create a new pull subscription or delete an existing subscription. You can also view any replication conflicts in this database’s subscriptions. Individual pull subscriptions let you perform basic replication operations: view conflicts, reinitialize, synchronize or stop synchronizing, and view the job history. Double-clicking a pull subscription will open the property sheet for that subscription. TI P You’ll find a Pull Subscriptions node only if the database is subscribing to any repli- cated publications via pull subscriptions. You’ll learn about replication in Chapter 27. PA R T The Data Transformation Services Folder III Each SQL Server has a Data Transformation Services folder in the SQL Server Enter- prise Manager tree. Data Transformation Services (DTS) is a component of SQL Server that can perform complex import and export operations from a variety of data Digging into SQL sources (not just SQL Server data sources, but any OLE DB data sources). Within this folder, you’ll find three nodes: • Local Packages Server • Meta Data Services Packages • Meta Data
332 CHAPTER 9 • USING SQL SERVER ENTERPRISE MANAGER NOTE You’ll learn more about Data Transformation Services in Chapter 22. Local Packages A DTS package is a set of instructions for SQL DTS. These instructions might specify, for example, a data source and a data destination together with the steps necessary to transform data from the source to the destination. The Local Packages node shows all of the DTS packages that are stored on the local SQL Server. The shortcut menu for a package will let you design the package, execute the package, or schedule it for later execution. Figure 9.20 shows a local package open in the DTS Package Designer. This particu- lar package exports data from a SQL Server database to a text file. FIGURE 9.20 The DTS Package Designer Meta Data Services Packages DTS packages may also be stored in a Meta Data Services database—if you click the Meta Data Services Packages node, you’ll see these packages. Meta Data Services is an object-oriented repository that’s designed to be used by many applications to store metadata. Meta Data Services is primarily a modeling tool, optimized for use by tools
THE SQL SERVER ENTERPRISE MANAGER TREE 333 and development applications. A Meta Data Services database holds objects that expose interfaces and can be extended through the use of information models. Meta Data Services is an advanced topic that we don’t cover in this book. If you’ve installed SQL Server, you can find the complete Meta Data Services documentation in Books Online under the Meta Data Services node. NOTE The previous version of Meta Data Services was known as the Microsoft Repository. Meta Data The Meta Data node holds a taskpad that lets you browse the information stored in the local repository. This interface, shown in Figure 9.21, lets you view information about databases, tables, columns, and so on. You can easily jump from the informa- tion on a particular column to any DTS packages that use that column. FIGURE 9.21 Browsing repository metadata in SQL Server Enterprise Manager PA R T III Digging into SQL Server
334 CHAPTER 9 • USING SQL SERVER ENTERPRISE MANAGER The Management Folder Each SQL Server in SQL Server Enterprise Manager contains a Management folder. This is the folder that provides access to traditional database administrator informa- tion, including: • SQL Server Agent • Alerts • Operators • Jobs • Backup • Current activity • Process info • Locks per process ID • Locks per object • Database maintenance plans • SQL Server logs Figure 9.22 shows this portion of the SQL Server Enterprise Manager tree. FIGURE 9.22 Information contained in the Management folder SQL Server Agent The SQL Server Agent node is primarily a container for the objects managed by the SQLServerAgent service. SQLServerAgent is a separate component of SQL Server that’s
THE SQL SERVER ENTERPRISE MANAGER TREE 335 responsible for managing alerts, jobs, and operators, and there are nodes of the tree underneath the SQL Server Agent node for each of these objects. From the SQL Server Agent node itself, you can start and stop the SQLServerAgent service, or create a new operator, job, or alert. You can also view the SQLServerAgent error log, or make this a master or target server for multiserver administration. N OT E The SQLServerAgent error log contains only errors directly related to the SQLServerAgent service, not to the operation of SQL Server as a whole. When you click an Alerts node, SQL Server Enterprise Manager shows you in the right-hand pane a list of all alerts configured on the current server. An alert is a condition that SQLServerAgent can respond to (for example, an error of a particular severity), together with an action SQLServerAgent should take if the alert’s condition occurs (for example, to run a particular job). The list of alerts lets you see how often each alert has occurred, as well as which alerts are configured to send notification by e-mail, pager, or Net Send. From the Alerts node, you can create and delete alerts, or generate SQL scripts for alerts. The shortcut menu for an individual alert lets you refresh the statistics dis- played for that alert or generate a SQL script for the alert. Double-clicking an alert opens the property sheet for that alert. When you click an Operators node, SQL Server Enterprise Manager shows you a list of all operators for the current server. An operator is a user who should be notified in the case of certain alerts. From the Operators node, you can create and delete operators, or generate SQL scripts. The shortcut menu for an individual object lets you refresh the information for that oper- ator, which includes the operator name and the last time that operator was notified of any alert. Double-clicking an operator opens the property sheet for that operator. PA R T When you click a Jobs node, SQL Server Enterprise Manager shows a list of all jobs III on the server. A job is a set of actions that SQLServerAgent can run in response to alerts or on a schedule. For each job, SQL Server Enterprise Manager displays the job name, category, whether the job is enabled and currently able to be run, whether the job is scheduled, its current status, and the last and next run dates. Digging into SQL From a Jobs node, you can create and delete jobs, modify the list of job categories, and create SQL scripts for local jobs. The shortcut menu for individual job objects gives you complete control over jobs: Server • Create job • Start job
336 CHAPTER 9 • USING SQL SERVER ENTERPRISE MANAGER • Stop job • Disable job • View job history • Refresh job • Script job • Delete job Double-clicking a job opens the property sheet for that job. NOTE For more information about alerts, operators, and jobs, see Chapter 17. Backup When you click a Backup node, SQL Server Enterprise Manager displays information on all backup devices known to the current database. A backup device is a tape drive or a disk file that can be used to hold a backup copy of a database. From a Backup node, you can create and delete backup devices, as well as create an actual backup job to run immediately or on a scheduled basis. The shortcut menu on a backup device lets you run a backup. Current Activity The Current Activity node for a server is a container of three other nodes that show the activity information: • Process Info • Locks/Process ID • Locks/Object The Process Info node for a server, shown in Figure 9.23, provides detailed infor- mation on current processes. If you’re an administrator, this is the node that will let you monitor minute-to-minute activity most easily.
THE SQL SERVER ENTERPRISE MANAGER TREE 337 FIGURE 9.23 Monitoring current process information For each process, the Process Info node shows the following information: • Process ID (this is the unique ID that SQL Server assigns to each process when it’s started—also known as a spid) • Context ID (a unique ID for each subthread in a particular process) • Username • Database • Current status • Number of open transactions • Most recent command • Application that owns the process PA R T • Most recent time spent waiting III • Current wait type • What resources the process is waiting for • CPU used Digging into SQL • Physical IO used • Memory used Server • Initial login time for the process
338 CHAPTER 9 • USING SQL SERVER ENTERPRISE MANAGER • Time last batch was submitted • Host name • Network library in use • Network address • Any processes that are blocked by this process • Any processes that are blocking this process Double-clicking a process lets you see the most recent SQL batch submitted by that process. You can also send a message to the owner of the process using Net Send from this property dialog box. The Locks/Process ID node contains one node for each process running on the server. Clicking one of these nodes will cause SQL Server Enterprise Manager to show information on all of the locks being maintained by the process. You can double-click an individual lock to see the detailed properties for that lock. The Locks/Object node contains one node for each database that’s in use. Clicking one of these nodes will show all of the locks that apply to objects in that database. You can double-click an individual lock to see the detailed properties for that lock. NOTE You’ll learn more about locking in Chapter 25. Database Maintenance Plans When you click a Database Maintenance Plans node, SQL Server Enterprise Manager shows you all of the database maintenance plans that are stored on the current server. A database maintenance plan contains a schedule for operations such as checking database integrity, shrinking bloated files, and backing up databases. From a Database Maintenance Plans node, you can create and delete database maintenance plans. You can also view the history of the plans, which tells you when they were most recently executed and provides details on the activities that they car- ried out. The shortcut menu for an individual database maintenance plan lets you view the history of that plan or delete that plan. Double-clicking a database maintenance plan opens the property sheet for that plan. NOTE Chapter 16 contains more information about database maintenance.
THE SQL SERVER ENTERPRISE MANAGER TREE 339 SQL Server Logs The SQL Server Logs node for a server holds nodes for the current activity log and for the six most recent activity logs before that. Whenever you start SQL Server, it starts writing events to the Windows NT application event log. These events are also avail- able in the SQL Server log. When you select one of the individual log nodes, SQL Server Enterprise Manager shows the individual log entries in the right-hand pane, as shown in Figure 9.24. For each entry, SQL Server Enterprise Manager displays the date, the source of the entry, and the message it contains. You can double-click an entry to view an entire message if it’s truncated in the default display. FIGURE 9.24 Entries in a SQL Server activity log PA R T III Digging into SQL NOTE You’ll learn more about interpreting SQL Server logs in Chapter 16. Server