Microsoft Exam 070-228
Installing, Configuring and Administering Microsoft
SQL Server 2000, Enterprise Edition
For reference, you canread this book:
MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server
2000 System Administration, Exam 70-228, Second Edition. from Microsoft Press.
Here is Chapter and Appendix Overview:
This book combines text, hands-on procedures, and review questions to teach you database administration using SQL Server 2000. It is designed to be completed from the beginning to the end, but you can choose to complete only those sections that interest you. If you choose the customized track option, see the “Before You Begin” section in each chapter. Any hands-on procedures that require preliminary work from preceding chapters refer to the appropriate chapters.
This self-paced training book is divided into the following chapters:
■ The “About This Book” contains a self-paced training overview and introduces the components of this training. Read this section thoroughly to get the greatest educational value from this self-paced training and to plan which lessons you will complete.
■ Chapter 1, “Overview of SQL Server 2000,” introduces SQL Server 2000. It defines some of the important characteristics of SQL Server 2000 and explains the environments in which it is designed to work. You will be introduced to the different parts of the product and given some idea as to the role played by these parts.
■ Chapter 2, “Installing SQL Server 2000,” explains how to install SQL Server 2000. It lists the hardware and software requirements of the program, and it explains the specific information you have to supply and the decisions you have to make during the installation process. This chapter covers using default, named, and multiple instances of SQL Server 2000. The chapter also covers performing unattended and remote installations of SQL Server 2000.
■ Chapter 3, “Preparing to Use SQL Server 2000,” reviews the results of installing SQL Server 2000. This chapter explains starting, stopping, and pausing SQL Server services. The chapter also covers working with OSQL, SQL Query Analyzer, and SQL Server Enterprise Manager.
■ Chapter 4, “Upgrading to SQL Server 2000,” shows you how to perform a version upgrade of a complete SQL Server 6.5 or 7.0 installation to SQL Server 2000. This chapter also explains how to perform an online database upgrade from SQL Server 7.0.
■ Chapter 5, “Understanding System and User Databases,” explains SQL Server 2000 databases. This includes the structure of a data file and the architecture of the transaction log. This chapter concludes with a discussion about system tables, including the querying of system and database catalogs.
■ Chapter 6, “Creating and Configuring User Databases,” teaches how to create a new user database, set database options for new or existing databases, and manage the size of data and transaction log files. This chapter concludes with a discussion about placing database files on multiple disks for recovery, fault tolerance, and performance.
■ Chapter 7, “Populating a Database,” discusses the population of user databases with existing data. It shows you how to transfer and transform data. This chapter focuses on the use of DTS, Bcp, and the BULK INSERT Transact-SQL statement.
■ Chapter 8, “Developing a Data Restoration Strategy,” presents an overview of the SQL Server 2000 backup and restore processes and discusses the issues that you should consider when planning a backup and restore strategy for a SQL Server 2000 installation.
Chapter 9, “Backing Up and Restoring SQL Server,” teaches you how to perform
database backups using disk and tape. It also teaches you how to perform
database restorations. This chapter explains how to use SQL Server Enterprise
Manager and Transact-SQL statements.
■ Chapter 10, “Managing Access to SQL Server 2000,” discusses controlling
access to SQL Server 2000, including the authentication process and the authorization
process. The chapter concludes by showing you how to create and manage
logins.
■ Chapter 11, “Managing SQL Server Permissions,” shows how to grant database-
specific permissions. It also discusses the use of application roles. The
chapter concludes by teaching you how to design an access and permissions
strategy.
■ Chapter 12, “Performing Administrative Tasks,” teaches you how to perform a
variety of configuration tasks and setup tasks. These include the configuration
of the SQL Server service, the SQL Server Agent service, SQL Mail,
SQLAgentMail, and XML. The chapter concludes with a discussion of the performance
of periodic maintenance tasks.
■ Chapter 13, “Automating Administrative Tasks,” shows you how to automate routine
tasks using operators, jobs, and alerts. It also teaches the creation of a database
maintenance plan. It concludes by teaching you to create multiserver jobs.
■ Chapter 14, “Monitoring SQL Server Performance and Activity,” discusses the
development of a performance monitoring methodology. This chapter introduces
each of the monitoring tools and shows you how to use each tool. The
chapter concludes by teaching you how to perform specific monitoring tasks,
such as for memory use or slow-performing queries.
■ Chapter 15, “Using SQL Server Replication,” introduces planning for, implementing,
monitoring, and administering replication. This includes covering the
types of replication that are available for automatically replicating data between
instances of SQL Server or with heterogeneous data sources.
■ Chapter 16, “Maintaining High Availability,” discusses the use of standby servers,
including the use of log shipping to automate the process of maintaining a
standby server. The chapter also discusses the use of SQL Server failover clusters
using the Microsoft Cluster Service.
Following Part 2 you will find:
■ The Appendix, “Questions and Answers,” lists all of the review questions from
the book, showing suggested answers.
■ The Glossary provides definitions for many of the terms and concepts presented
in this training kit.
Sample Question and Answer (Q&A) for exam 70-228:
QUESTION 3:
You are the administrator of two Microsoft Windows 2000 advanced server computers. On these servers, you are configuring a database that will store accounting information for your company. The data must be available at all times. Interruptions in data connectivity should not last longer than five minutes. Any changes to the database should not require you to reconfigure the client computers. How should you configure the database?
A. Configure the database on the two servers as a SQL Server 2000 cluster.
B. Configure the database on one server, and then configure a standby database on the second server.
C. Configure the database on each server. Use Microsoft Distributed Transaction Coordinator to keep the two servers perfectly synchronized.
D. Configure the database as a federated database, and then partition half the data on each server.
Answer: A.
QUESTION 4:
You are the administrator of a new Microsoft Windows 2000 server computer named HQSQL5. HQSQL5 is a member server in your company's Windows NT 4.0 domain, which is named HQMAIN. After installing SQL Server 2000 on HQSQL5, you configure the MSSQLServer service account to use the HQMAIN\sqladmin user account, which is a member of the local administrators group on HQSQL5. You can use the HQMAIN\sqladmin user account to log on the HQSQL5. However, the MSSQLServer service fails to start. You need to start the service. What should you do?
A. Revoke Log on interactivity rights for the HQMAIN\sqladmin user account.
B. Add the HQMAIN\sqladmin user account to the HQMAIN\Domain Admins group.
C. Select the Password Never Expires check box for the HQMAIN\sqladmin user account.
D. Grant Log on as a service rights to the HQMAIN\sqladmin user account.
Answer: D.
QUESTION 5:
You are the administrator of a SQL server computer. The server is running SQL Server 6.5 and SQL Server 7.0. You install a named instance of SQL Server 2000, and then run the SQL server upgrade wizard. On the database selection screen, some of the SQL Server 6.5 databases are not listed. You cancel the SQL Server upgrade wizard. You need to ensure that the SQL Server 6.5 databases are listed in the wizard. What should you do?
A. Uninstall SQL Server 7.0, and then rerun the SQL Server upgrade wizard.
B. Run the Microsoft SQL Server-switch application, and then rerun the SQL Server upgrade wizard.
C. Create a data transformation services package that imports the databases from SQL Server 6.5 to SQL Server 2000, and then execute the package.
D. Uninstall SQL Server 2000, and then reinstall SQL Server 2000 as the default instance.
Answer: D
QUESTION 6:
You are the administrator of a SQL Server 2000 computer. Your company uses the server to store service contract information for its customers. You are also the administrator of Oracle relational database management system (RDBMS) server. This server is used to store your company's financial information. The financial information is updated frequently throughout the day. You need to create a series of reports that combine the service contract information
and the financial information. These reports will be updated several times a day. You want to create reports on the SQL Server computer by using the minimum amount of disk space. What should you do?
A. Set up SQL server replication to replicate the data from the oracle server to the SQL server computer.
B. Set up the oracle server as a linked server. Create a view that joins the service contract information and the financial information.
C. Set up data transformation services (DTS) package that imports and transforms the database from the oracle server to the SQL server computer. Use SQL server agent to execute the DTS package throughout the day as needed.
D. Set up Microsoft ActiveX script that connects to the oracle server and imports the financial information into SQL server temporary table. Create a view that joins the service contract information and the temporary table.
Answer: B.
QUESTION 5:
You are the administrator of a SQL Server 2000 computer named SQL1. You want to perform ad hoc distribution queries against a database that is stored on a SQL Server 2000 computer named SQL2. SQL2 contains several databases, and each of these databases uses a different collation. You want to ensure that comparisons in distributed queries are evaluated correctly. You also want to minimize administrative overhead.
How should you configure SQL1?
A. Use the ALTER DATABASE statement to change the collation of the databases on SQL1.
B. Add SQL2 as remote server.
C. Add SQL2 as a linked server. Select the Use Remote Collation check box, and do not specify a collation name.
D. Add SQL2 as a linked server. Select the Use Remote Collation check box, and specify a collation name. Repeat this process once for each database on SQL2.
Answer: C.
Installing, Configuring and Administering Microsoft
SQL Server 2000, Enterprise Edition
For reference, you canread this book:
MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server
2000 System Administration, Exam 70-228, Second Edition. from Microsoft Press.
Here is Chapter and Appendix Overview:
This book combines text, hands-on procedures, and review questions to teach you database administration using SQL Server 2000. It is designed to be completed from the beginning to the end, but you can choose to complete only those sections that interest you. If you choose the customized track option, see the “Before You Begin” section in each chapter. Any hands-on procedures that require preliminary work from preceding chapters refer to the appropriate chapters.
This self-paced training book is divided into the following chapters:
■ The “About This Book” contains a self-paced training overview and introduces the components of this training. Read this section thoroughly to get the greatest educational value from this self-paced training and to plan which lessons you will complete.
■ Chapter 1, “Overview of SQL Server 2000,” introduces SQL Server 2000. It defines some of the important characteristics of SQL Server 2000 and explains the environments in which it is designed to work. You will be introduced to the different parts of the product and given some idea as to the role played by these parts.
■ Chapter 2, “Installing SQL Server 2000,” explains how to install SQL Server 2000. It lists the hardware and software requirements of the program, and it explains the specific information you have to supply and the decisions you have to make during the installation process. This chapter covers using default, named, and multiple instances of SQL Server 2000. The chapter also covers performing unattended and remote installations of SQL Server 2000.
■ Chapter 3, “Preparing to Use SQL Server 2000,” reviews the results of installing SQL Server 2000. This chapter explains starting, stopping, and pausing SQL Server services. The chapter also covers working with OSQL, SQL Query Analyzer, and SQL Server Enterprise Manager.
■ Chapter 4, “Upgrading to SQL Server 2000,” shows you how to perform a version upgrade of a complete SQL Server 6.5 or 7.0 installation to SQL Server 2000. This chapter also explains how to perform an online database upgrade from SQL Server 7.0.
■ Chapter 5, “Understanding System and User Databases,” explains SQL Server 2000 databases. This includes the structure of a data file and the architecture of the transaction log. This chapter concludes with a discussion about system tables, including the querying of system and database catalogs.
■ Chapter 6, “Creating and Configuring User Databases,” teaches how to create a new user database, set database options for new or existing databases, and manage the size of data and transaction log files. This chapter concludes with a discussion about placing database files on multiple disks for recovery, fault tolerance, and performance.
■ Chapter 7, “Populating a Database,” discusses the population of user databases with existing data. It shows you how to transfer and transform data. This chapter focuses on the use of DTS, Bcp, and the BULK INSERT Transact-SQL statement.
■ Chapter 8, “Developing a Data Restoration Strategy,” presents an overview of the SQL Server 2000 backup and restore processes and discusses the issues that you should consider when planning a backup and restore strategy for a SQL Server 2000 installation.
Chapter 9, “Backing Up and Restoring SQL Server,” teaches you how to perform
database backups using disk and tape. It also teaches you how to perform
database restorations. This chapter explains how to use SQL Server Enterprise
Manager and Transact-SQL statements.
■ Chapter 10, “Managing Access to SQL Server 2000,” discusses controlling
access to SQL Server 2000, including the authentication process and the authorization
process. The chapter concludes by showing you how to create and manage
logins.
■ Chapter 11, “Managing SQL Server Permissions,” shows how to grant database-
specific permissions. It also discusses the use of application roles. The
chapter concludes by teaching you how to design an access and permissions
strategy.
■ Chapter 12, “Performing Administrative Tasks,” teaches you how to perform a
variety of configuration tasks and setup tasks. These include the configuration
of the SQL Server service, the SQL Server Agent service, SQL Mail,
SQLAgentMail, and XML. The chapter concludes with a discussion of the performance
of periodic maintenance tasks.
■ Chapter 13, “Automating Administrative Tasks,” shows you how to automate routine
tasks using operators, jobs, and alerts. It also teaches the creation of a database
maintenance plan. It concludes by teaching you to create multiserver jobs.
■ Chapter 14, “Monitoring SQL Server Performance and Activity,” discusses the
development of a performance monitoring methodology. This chapter introduces
each of the monitoring tools and shows you how to use each tool. The
chapter concludes by teaching you how to perform specific monitoring tasks,
such as for memory use or slow-performing queries.
■ Chapter 15, “Using SQL Server Replication,” introduces planning for, implementing,
monitoring, and administering replication. This includes covering the
types of replication that are available for automatically replicating data between
instances of SQL Server or with heterogeneous data sources.
■ Chapter 16, “Maintaining High Availability,” discusses the use of standby servers,
including the use of log shipping to automate the process of maintaining a
standby server. The chapter also discusses the use of SQL Server failover clusters
using the Microsoft Cluster Service.
Following Part 2 you will find:
■ The Appendix, “Questions and Answers,” lists all of the review questions from
the book, showing suggested answers.
■ The Glossary provides definitions for many of the terms and concepts presented
in this training kit.
Sample Question and Answer (Q&A) for exam 70-228:
QUESTION 3:
You are the administrator of two Microsoft Windows 2000 advanced server computers. On these servers, you are configuring a database that will store accounting information for your company. The data must be available at all times. Interruptions in data connectivity should not last longer than five minutes. Any changes to the database should not require you to reconfigure the client computers. How should you configure the database?
A. Configure the database on the two servers as a SQL Server 2000 cluster.
B. Configure the database on one server, and then configure a standby database on the second server.
C. Configure the database on each server. Use Microsoft Distributed Transaction Coordinator to keep the two servers perfectly synchronized.
D. Configure the database as a federated database, and then partition half the data on each server.
Answer: A.
QUESTION 4:
You are the administrator of a new Microsoft Windows 2000 server computer named HQSQL5. HQSQL5 is a member server in your company's Windows NT 4.0 domain, which is named HQMAIN. After installing SQL Server 2000 on HQSQL5, you configure the MSSQLServer service account to use the HQMAIN\sqladmin user account, which is a member of the local administrators group on HQSQL5. You can use the HQMAIN\sqladmin user account to log on the HQSQL5. However, the MSSQLServer service fails to start. You need to start the service. What should you do?
A. Revoke Log on interactivity rights for the HQMAIN\sqladmin user account.
B. Add the HQMAIN\sqladmin user account to the HQMAIN\Domain Admins group.
C. Select the Password Never Expires check box for the HQMAIN\sqladmin user account.
D. Grant Log on as a service rights to the HQMAIN\sqladmin user account.
Answer: D.
QUESTION 5:
You are the administrator of a SQL server computer. The server is running SQL Server 6.5 and SQL Server 7.0. You install a named instance of SQL Server 2000, and then run the SQL server upgrade wizard. On the database selection screen, some of the SQL Server 6.5 databases are not listed. You cancel the SQL Server upgrade wizard. You need to ensure that the SQL Server 6.5 databases are listed in the wizard. What should you do?
A. Uninstall SQL Server 7.0, and then rerun the SQL Server upgrade wizard.
B. Run the Microsoft SQL Server-switch application, and then rerun the SQL Server upgrade wizard.
C. Create a data transformation services package that imports the databases from SQL Server 6.5 to SQL Server 2000, and then execute the package.
D. Uninstall SQL Server 2000, and then reinstall SQL Server 2000 as the default instance.
Answer: D
QUESTION 6:
You are the administrator of a SQL Server 2000 computer. Your company uses the server to store service contract information for its customers. You are also the administrator of Oracle relational database management system (RDBMS) server. This server is used to store your company's financial information. The financial information is updated frequently throughout the day. You need to create a series of reports that combine the service contract information
and the financial information. These reports will be updated several times a day. You want to create reports on the SQL Server computer by using the minimum amount of disk space. What should you do?
A. Set up SQL server replication to replicate the data from the oracle server to the SQL server computer.
B. Set up the oracle server as a linked server. Create a view that joins the service contract information and the financial information.
C. Set up data transformation services (DTS) package that imports and transforms the database from the oracle server to the SQL server computer. Use SQL server agent to execute the DTS package throughout the day as needed.
D. Set up Microsoft ActiveX script that connects to the oracle server and imports the financial information into SQL server temporary table. Create a view that joins the service contract information and the temporary table.
Answer: B.
QUESTION 5:
You are the administrator of a SQL Server 2000 computer named SQL1. You want to perform ad hoc distribution queries against a database that is stored on a SQL Server 2000 computer named SQL2. SQL2 contains several databases, and each of these databases uses a different collation. You want to ensure that comparisons in distributed queries are evaluated correctly. You also want to minimize administrative overhead.
How should you configure SQL1?
A. Use the ALTER DATABASE statement to change the collation of the databases on SQL1.
B. Add SQL2 as remote server.
C. Add SQL2 as a linked server. Select the Use Remote Collation check box, and do not specify a collation name.
D. Add SQL2 as a linked server. Select the Use Remote Collation check box, and specify a collation name. Repeat this process once for each database on SQL2.
Answer: C.
0 Comments:
<< Home | << Add a comment