Appendix

3 4

Appendix

Chapter 1

Review Questions

1. You are planning to deploy SQL Server 2000 to support Internet-based sales of your products. You need this installation to handle a large volume of transactions and be available 24x7. Which edition of SQL Server 2000 should you choose?

   You should choose the Enterprise Edition because you need all of the high performance and fault-tolerant features of the Enterprise Edition.

2. You want to allow users to query SQL Server 2000 using their Internet browser via the Internet. What components of SQL Server 2000 are required?

   First of all, you need the SQL Server service. Next, you need to configure IIS to access SQL Server 2000 using some of the client communication components. The specific client communication components will depend on your specific configuration.

3. The SQL Server 2000 database environment has a physical design component and a logical design component. As a database administrator, one of your tasks is to optimize the performance of SQL Server 2000. With respect to which type of database design do you have the most ability to affect performance?

   As the database administrator, you have the most ability to affect performance by optimizing the physical database component, including choice of hardware and placement of data and log files.

4. You have an existing server application that uses SQL Server 2000 running on Windows 2000 servers. You have clients who access this server application using Windows 95 and Windows 98 client applications. You want to extend this server application to clients using an existing Novell network. What type of authentication decisions must you make?

   You must decide whether the clients who will access SQL Server 2000 from the Novell network will be authenticated by the Windows operating system prior to attempting to access SQL Server 2000. If they will not be authenticated by the Windows operating system, you must configure SQL Server 2000 to use Mixed Mode authentication and provide each user with a SQL Server login.

Chapter 2

Review Questions

1. You have decided to install SQL Server 2000 on a test computer to evaluate the new features available. You have a Pentium III 400-MHz laptop with 96 MB of memory. Will this laptop be sufficient for testing the new features of SQL Server 2000?

   Probably, but additional information is needed. The processor is fast enough for all editions and all Windows operating systems editions. However, the question does not tell us how much hard drive space is available on the laptop. Assuming enough space can be made available, this laptop could be used to install the Personal edition on any Windows operating system. This laptop does not have enough memory to properly test any SQL Server 2000 edition on any Windows 2000 Server edition. It does have sufficient memory to test any SQL Server 2000 edition on any Windows NT 4.0 Server edition.

2. You are installing SQL Server 2000. You want SQL Server 2000 to be able to use your Microsoft Exchange Server to notify you when jobs succeed or fail. What type of account should you use for the SQL Server and SQL Server Agent services?

   You should use a domain user account for the SQL Server and SQL Server Agent services. A domain user account is required for access to Microsoft Exchange Server.

3. You are installing SQL Server 2000. You have a mixed network of computers including Windows NT servers and Novell servers. Your network supports both TCP/IP and NWLink IPX/SPX. Should you perform a typical or a Custom setup?

   If you want to configure support for NWLink IPX/SPX during setup, you must perform a Custom setup. The option to configure SQL Server 2000 to listen on NWLink IPX/SPX is not available when a Typical setup is performed. However, because all network libraries are installed during all types of setups, you can also use the Server Network Utility to configure NWLink IPX/SPX after SQL Server 2000 is installed. Finally, because your network supports TCP/IP as well as NWLink IPX/SPX, support for TCP/IP might be sufficient for your needs without configuring NWLink IPX/SPX in SQL Server 2000.

4. You are installing SQL Server 2000. You have heard that SQL Server 2000 allows you to install SQL Server 2000 side by side with SQL Server 7.0. If you install SQL Server 2000 as a named instance, what issues should you be aware of?

   Installing a named instance of SQL Server 2000 on the same computer as an installation of SQL Server 7.0 will replace all of the SQL Server 7.0 client tools and utilities with the SQL Server 2000 versions of these tools and utilities. It will also install the SQL Server 2000 version of Books Online in place of the SQL Server 7.0 Books Online. However, it will leave your SQL Server 7.0 databases intact and functioning using the SQL Server 7.0 database engine.

5. You are installing SQL Server 2000. You plan to install identical configurations on multiple computers to test the configuration's performance on different hardware platforms. You do not want to click your way through the SQL Server 2000 interactive Setup program each time you install SQL Server 2000. What should you do?

   You should use the capability of the SQL Server 2000 Setup program to record an unattended .ISS file for you to use to perform unattended installations. This allows you to perform identical installations on multiple computers without having to interactively navigate your way through the SQL Server 2000 Setup program screens each time you install SQL Server 2000.

6. You have installed SQL Server 2000 on a test computer for evaluation. You had a problem initially starting the SQL Server service due to a logon failure. You solved the problem. You want to review the SQL Server error log related to the failure to start the SQL Server service. Can you do this, and if so, how?

   You can use SQL Server Enterprise Manager or any text editor to review the current error log or any of the previous six error logs.

Chapter 3

Review Questions

1. You have installed SQL Server 2000 on a test computer for evaluation. Gloria, another database administrator at your company, logged on to the SQL Server 2000 computer and attempted to review the new files that were added. She reports that she cannot view all of the files that were installed. Why might this be happening? Is there a problem?

   The SQL Server 2000 Setup program locks down certain folders within the NTFS file system to prevent unauthorized tampering. Only the service account used by the SQL Server and SQL Server Agent services and members of the local Administrators group can access the unique program and data files for each instance. The reason Gloria cannot view all of the new files that were added is most likely because she is not a member of the local Administrators group on the computer upon which SQL Server was installed. This is not a problem. This is by design. If Gloria will be administering this SQL Server 2000 installation, she might need to be added to the local Administrators group. However, she does not have to be a member of the local Administrators group to administer this SQL Server installation. Chapters 10 and 11 cover security in detail.

2. You have installed SQL Server 2000 on a test computer for evaluation. During installation, you used the local system account as the service account for the SQL Server and SQL Server Agent services. You have decided you need to configure and use a dedicated domain user account for these services. How should you change the service account for these services?

   You should use SQL Server Enterprise Manager to change the service account for both the SQL Server and the SQL Server Agent services. This will set the appropriate access permissions in the NTFS file system and the Windows registry for this dedicated domain user account. It will also update the Microsoft Search service with respect to the use of this domain user account by the SQL Server service.

3. You have installed SQL Server 2000 on a test computer for evaluation. You want to verify that you can connect to SQL Server 2000 and begin configuring objects in SQL Server 2000. What tool would you start with and why?

   You would probably begin with SQL Server Enterprise Manager. Although you could use Osql or SQL Query Analyzer to test connectivity, SQL Server Enterprise Manager is the primary tool for administering SQL Server 2000 objects.

Chapter 4

Review Questions

1. You are planning to upgrade your SQL Server 7.0 installation running on Windows NT 4.0 Server to SQL Server 2000. You want to test SQL Server 2000 on the same computer on which you currently have SQL Server 7.0 installed, and you need to keep the SQL Server 7.0 installation available for users. What issues do you need to consider?

   The first issue you need to consider is whether you need to upgrade the hardware or software to support SQL Server 2000. Windows NT 4.0 must be running Service Pack 5. Next, you must install SQL Server 2000 as a named instance. When you do this, you need to be aware that the Setup program will upgrade the SQL Server 7.0 client tools to SQL Server 2000 client tools. This includes SQL Server Enterprise Manager and SQL Query Analyzer. Finally, you must have sufficient hard drive space for the named instance of SQL Server 2000.

2. You have recently performed a version upgrade of SQL Server 7.0 to SQL Server 2000. Although overall performance has improved, full-text searches are not working. Why might this be?

   During the version upgrade, full-text catalogs were disabled. You need to repopulate the full-text catalogs manually. Maintaining full-text catalogs is covered in Chapter 12.

3. You have decided that you cannot afford the downtime associated with a version upgrade of your SQL Server 7.0 installation and have decided to perform an online database upgrade of your production databases. What settings and objects will you have to re-create manually?

   You will have to re-create any server settings and SQL Server Agent jobs and alerts. Also, you cannot upgrade any databases involved in replication.

4. You have been testing SQL Server 2000 on the same computer on which you have been running your SQL Server 6.5 installation. You decide to upgrade your SQL Server 6.5 installation. However, you cannot locate the SQL Server Upgrade Wizard. It is not located on the Start menu and you cannot find it on your hard drive. Why?

   The SQL Server Upgrade Wizard is only installed when you install an instance of SQL Server 2000 as the default instance. The reason for this is that you can only upgrade to the default instance. If the wizard does not appear, you probably installed SQL Server 2000 as a named instance, rather than the default instance.

Chapter 5

Review Questions

1. Describe the difference in the ordering of data pages from a table caused by using either a clustered index or a nonclustered index.

   A clustered index on a table causes the data pages (and the index pages) to be physically ordered in the data file based on the key value in the clustered index. A nonclustered index does not physically order the data pages. Only the index pages of the nonclustered index are physically ordered.

2. Which recovery model does not require regular backups of the transaction log?

   The Simple Recovery model does not rely on transaction log backups to truncate the transaction log. Rather, the checkpoint process automatically truncates the inactive portion of the logical log at the end of each checkpoint.

3. Why should you not create scripts that use Transact-SQL statements to directly query system tables?

   You should not create scripts that use Transact-SQL statements to query system tables directly because the underlying system tables might change between releases of SQL Server. If such a change does occur, you would have to rewrite those scripts. Microsoft sometimes modifies system tables with new releases to add new functionality.

Chapter 6

Review Questions

1. You want to create a user database containing multiple data files on separate physical disks. You are not experienced at writing Transact-SQL statements. What is the simplest method you can use to create this database?

   Use the direct method in SQL Server Enterprise Manager. The Create Database Wizard does not allow you to create a database with multiple data files on separate disks, but the direct method does.

2. Describe the two methods you can use to change the database recovery model for a database.

   You can change the database recovery model for a database on the Options tab in the Properties dialog box for the database. You can also use the ALTER DATABASE Transact-SQL statement.

3. You created a new database on your system. You used the default properties for the transaction log file. You backed up the new database and the master database. After you performed a bulk load of data into your new database, you notice that the transaction log is quite large. Why did it grow so large and what must you do to reduce the size of the transaction log?

   The transaction log file grew so large because it was set to autogrow and because the recovery model was set to full. The bulk load operation was fully logged and generated many transaction log records. To reduce the size of the transaction log, you should first back up the transaction log file and then use the DBCC SHRINKFILE or DBCC SHRINKDATABASE command. You cannot shrink the transaction log using SQL Server Enterprise Manager.

4. You are managing a small database system running on Windows 2000 Server. Although the database is under 1 GB in space, it is very busy (primarily performing writes), and you want to improve its performance. You do not have the budget for a hardware RAID system and are not aware of specific database access patterns. You have already placed the transaction log file on a separate disk from the data file. What is an inexpensive solution?

   You can procure between three and six small disks. Set up a mirror of the transaction log files using the software RAID capabilities of Windows 2000 Server. Combine the remaining disks using RAID 0 and move the data file to this array. Because you have fault tolerance on the transaction log, the lack of fault tolerance on the data files may be acceptable if the performance gain is significant.

Chapter 7

Review Questions

1. You are analyzing the data in a text file containing data that you want to import into your database. You have determined that the data is internally consistent, but contains fields that are inconsistent with existing data in your database. The text file is representative of data that you will be importing weekly. What is your best solution for achieving the necessary data consistency? You have already determined that you cannot change the original data source.

   You have a number of options. You can use DTS to perform transformations of data during the import. You can use a text editor and manually perform a search and replace. You can import the data into a temporary table and use Transact-SQL to massage and scrub the data. Although each of these methods will work, DTS provides the most automated method and, because this is a task you will have to perform repeatedly, DTS provides the best solution.

2. Describe the difference between the On Success precedence constraint and the On Completion precedence constraint.

   A task that follows the On Success precedence constraint will only execute if the preceding task completes the task it intended to complete. A task that follows the On Completion precedence constraint will execute when the preceding task completes, regardless of whether that task achieved the task it intended to complete.

3. You have created and saved a simple data import and transform package that imports data from the spreadsheets maintained by your salespeople for expense reports. However, you want to add additional functionality to the package, including notifying an administrator after the entire sales staff has uploaded their expense reports. How might you accomplish this?

   You could use DTS Designer to edit the existing package to connect to multiple data sources (each salesperson's notebook) once per week and upload the expense report. You could use Message Queuing to queue a spreadsheet upload task that occurred weekly for each salesperson. When the last salesperson has uploaded data, the package could notify an administrator using the Send Mail task.

4. You have created a package that collects completed sales information from several different spreadsheet files used by salespeople in your company. After it collects this information, it inserts the collected information in one of your sales reporting databases. You want to distribute this package to your salespeople to execute regularly as part of their weekly reports. However, you do not want them to be able to open or edit the package. How should you save this package and how should you secure it?

You should save it as a structured storage file, using both an owner and a user password. You can then mail or otherwise distribute the package to your salespeople without the package being viewable or editable.

5. You want to import a large amount of data from a text file into a table that contains a clustered and a nonclustered index. The data being inserted exists in the text file in the same order as the clustered index. As part of the process, you first truncate the existing table to replace it with this new data. Should you drop each of the indexes before you insert the new data?

   Because the table will be truncated prior to new data being inserted, you should drop the nonclustered index and rebuild it after the data insert is complete. However, you should not drop the clustered index. Rather, you should specify in your Bcp command or BULK INSERT statement that the data is already ordered.

Chapter 8

Review Questions

1. You are using RAID 1 for your transaction log and RAID 10 for your database. With this level of fault tolerance, why is it still critical to have a data restoration plan?

   Using fault tolerance for your disk subsystem does not protect your data from all forms of disaster. For example, multiple disks could fail simultaneously, your disk subsystem could be stolen, or a natural disaster could strike. In addition, you might need to roll your database back to an earlier point in time because of user or application error.

2. You are developing your data recovery plan. You have tested the length of time required to perform a full database backup and determined that you can back up the entire database in six hours. You have decided to perform full database backups every night. You have also determined that you need to perform transaction log backups every 15 minutes to minimize the risk of data loss. Should you also use regular differential database backups as part of your data recovery plan?

   It depends. The benefit of regular differential database backups is to speed the restoration process. If your database fails at 5:00 P.M. , you will need to restore each transaction log backup since the full database backup the night before. If the transaction log backups are reasonably small and are either on a network file server or only on a few tapes, the benefit of differential database backups might not be significant. However, if you must insert a separate tape for each transaction log backup or if each transaction log backup is large, performing a differential database backup every two hours could substantially reduce your data restoration time.

3. You are responsible for maintaining and restoring, if needed, a decision support database. Several different data sources regularly populate this database using DTS packages. What is the restoration benefit, if any, to using the Full Recovery model for this database given the substantial increase in the number and size of the transaction log backups required?

   There is little restoration benefit in this scenario. All of the data in this database comes from existing data sources. If the Full Recovery model is not used and the entire database is lost because of some disaster, you can restore data to the point of the most recent transaction log backup. You can regenerate any more recent data relatively easily from the original data sources.

Chapter 9

Review Questions

1. You regularly perform full, differential, and transaction log backups to disk. Are there any other backup tasks that you should perform regularly to protect your database from data loss?

   Yes, you should regularly archive the backups from disk to tape for permanent storage. This will protect your backups in case the disk containing the backup files should fail.

2. You are a new database administrator. You want to create Transact-SQL scripts to automate the backup of your database. However, the syntax is imposing. What are several good methods for familiarizing yourself with the Transact-SQL syntax and the various backup options?

   There are two excellent ways to become familiar with the available backup options and the Transact-SQL syntax. The first is to use SQL Server Enterprise Manager, including the Create Database Backup Wizard. This will assist you in understanding how each backup option works. The second is to use the sample Transact-SQL scripts in this chapter as well as SQL Server Books Online on a sample database and practice modifying and running backup scripts.

3. What is a major advantage to using SQL Server Enterprise Manager for performing database restorations, rather than Transact-SQL?

   A major advantage to using SQL Server Enterprise Manager rather than Transact-SQL (other than not having to learn the Transact-SQL syntax) is that SQL Server Enterprise Manager will use the backup history in the msdb database to assist you in selecting the necessary backup sets to perform a complete database restoration as quickly as possible.

4. What is the major difference between performing a restoration of the master database and all other databases?

   You must start SQL Server 2000 in single-user mode to perform a master database restoration.

Chapter 10

Review Questions

1. You are concerned about keeping the data stored within your SQL Server 2000 installation extremely secure. All of the users who will access this data are Windows 2000 users. Should you permit SQL Server authentication? Why or why not?

   Given the information in the question, the answer is no. The primary benefit to enabling SQL Server authentication is to permit users who will not be previously authenticated by the Windows operating system to access a SQL Server 2000 installation. The downside to permitting SQL Server authentication is significantly weaker security. This is primarily because of the lack of account policy protections that are enforced by Windows 2000 (or Windows NT 4.0) and the lack of encryption for user names and passwords on the wire (unless SSL is enabled for the entire session).

2. A member of the help desk staff has complained that although she can log on to SQL Server 2000 and access the Northwind and Pubs databases, she cannot even see the Northwind Reports database. How is this possible?

   A user cannot see databases to which he or she has no access rights. In general, for this member of the help desk staff to see and access the Northwind Reports database, she must be granted direct rights to the database, be added to a Windows group that has access, or be added to a database role in that database. Enabling the guest user account in the Northwind Reports database will also enable the member of the helpdesk staff to see and access the Northwind Reports database.

3. You are creating a new SQL Server 2000 installation. Hundreds of users will require access to several different databases on this SQL Server 2000 instance. Should you use SQL Server Enterprise Manager or Transact-SQL system stored procedures for creating these login and user accounts? Why?

   You should create Transact-SQL scripts rather than use SQL Server Enterprise Manager because you can create many users with a single script, whereas SQL Server Enterprise Manager would require hundreds of separate clicks to create this many users.

Chapter 11

Review Questions

1. You created a Windows 2000 security group for users of the SalesReporting database on your SQL Server 2000 installation and placed the sales managers in this group. You then granted this group access to SQL Server 2000 and the SalesReporting database. In addition, you made this group a member of the db_datawriter and db_datareader fixed database roles. Several members have complained that although they can access the data in each table and view in the database, they are only able to execute certain stored procedures, but not all. To make matters more complicated, one of the members of this group can execute all of the stored procedures without a problem. What are the likely causes of this problem?

   The problem begins with the fact that membership in the db_datawriter and db_datareader fixed database roles does not grant any permissions to execute stored procedures. Therefore, the problem must relate to permissions on the stored procedures themselves. It is likely that the public role has been granted execution rights on the stored procedures they are able to execute. With respect to the remaining stored procedures, either the member of the group that can execute them is receiving permission through membership in another group, or the members that cannot execute them belong to a group that has been denied the right to execute those specific stored procedures.

2. You need to grant certain users the ability to insert new data into a highly secure database. They also require very limited lookup rights to the data. You are concerned about security for this data. What is the most secure method you can use to allow the users to perform their task?

   You can create an application role with very specifically delimited rights to the database. You can work with a developer to create an application that only allows the users to perform the specific tasks they need to perform and have the custom application access the database using the application role and an encrypted password. Finally, you can ensure that none of the users of the application have any access rights to the SQL Server 2000 installation other than through the custom application.

3. You are designing a security strategy for your SQL Server 2000 installation. You are only allowing access to Windows 2000 users and groups. Is there any advantage to applying permissions to user-defined database groups rather than directly to Windows groups?

   Possibly. If each different grouping of permissions maps directly to a single Windows 2000 group and you will never allow SQL Server logins, there is no advantage. However, if you might need to permit SQL Server logins in the future, or if there are multiple Windows 2000 groups that need the same grouping of permissions, assigning permissions to a user-defined database group will ease the administrative task over the life cycle of the SQL Server 2000 installation.

Chapter 12

Review Questions

1. You are running a number of server applications on the same computer. You observe that the performance of SQL Server 2000 is initially poor after a period of low activity. What can you do to improve its responsiveness?

   You can configure a minimum memory setting guaranteeing that a sufficient minimum amount of physical memory will always be available to the SQL Server 2000 instance.

2. You want to configure the SQL Server Agent service to send mail to administrators in response to alerts. What is the first task you must perform?

   You must configure the SQL Server Agent service to use a domain user account.

3. You want to enable one of your databases to be queried using XML. What must you do?

   You must configure a virtual directory in IIS pointing to the database and specify the security context of connecting users. You must also specify the types of XML queries that will be permitted.

Chapter 13

Review Questions

1. The database administrator who has been designated as the fail-safe operator is leaving the company. What must you do before you delete this person as an operator?

   You must either assign another person as the fail-safe operator or disable the fail-safe operator feature.

2. If a job fails to execute when scheduled, what are some troubleshooting steps you can follow?

   Verify that the schedule is enabled; verify that the job is enabled; and verify that SQL Server Agent is running.

3. You have defined an alert that backs up the transaction log when it is 90 percent full. However, occasionally the transaction log fills up before the job executes. Why is this occurring and what can be done to solve this problem?

   Performance condition alerts sample the performance object counters every few seconds. If your transaction log fills up very quickly, this sampling rate is not frequent enough to back up the transaction log before it fills. Set a lower threshold for the alert to solve the problem.

4. You are in charge of managing a small database for your company. This is a part-time responsibility. You are also managing your company's domain controllers, Web site, and e-mail server. You want to automate as many tasks as possible. Where should you start?

   The first automation task is backup. Use the Database Maintenance Plan Wizard to automate the backup of all system and user databases. Next, consider the other tasks you can automate with the Database Maintenance Plan Wizard and automate those that apply. Next, evaluate the types of events and conditions for which you should define alerts. This will provide you with advance notice of potential problems.

5. You want to create a single job that backs up the system databases nightly on every SQL Server instance within your company. You want to ensure that this happens automatically, with notice to you only if there is a problem. Can this be done?

   Yes. You can create a multiserver job to perform this task and notify you as the MSXOperator only if a job fails. If you do not receive notification, you can generally assume that the job completed successfully on all servers. However, if the e-mail system within your company fails or the Messenger service stops running, you might not receive notification of failure.

Chapter 14

Review Questions

1. You have recently been hired as the new database administrator for a medium-sized database. You have been tasked with improving the performance of the database, although no specific problems are apparent. Where should you start?

   You should start by reviewing any performance baseline information that is available. You need to determine whether there are any immediate resource limitations affecting performance. Thereafter, you can begin identifying more subtle performance issues, such as inadequate indexes and long-running queries.

2. You want to be able to quickly view overall levels of resource use on a computer running SQL Server to determine whether resources are adequate. What is the most appropriate tool for the task?

   Task Manager is the most appropriate tool for this task. It can be placed in the system tray and used to quickly display overall processor, memory, and I/O activity on the computer.

3. You have been viewing current server activity through SQL Server Enterprise Manager. You have noticed a number of blocking locks. What steps should you take to determine whether this is a serious problem?

You should use SQL Profiler to determine the number and frequency of blocking locks. By performing this step over time, you can determine whether the number of blocking locks is stable or increasing (or perhaps decreasing). You also capture sufficient information to determine whether specific SQL batches or stored procedures are causing the majority of the blocking locks. You can then determine the necessary steps to improve concurrency and performance, such as rewriting scripts and stored procedures, or changing the design of the database.

Chapter 15

Review Questions

1. You have a number of users in Brazil that need to access data for the purpose of sales analysis. The data is stored in a centralized database in New York. They have been accessing the database in New York over a 56K dedicated link that is also supporting a variety of other interoffice traffic. You want to implement a replication solution between your New York office and your Brazil office. What type of replication would you implement and what additional information do you need to know?

   Because this information is for sales analysis, the users probably do not need up-to-the-minute data. Depending upon the size of the database and the number of changes, you would implement either snapshot or transactional replication. Replication could occur once a day, probably late in the evening.

2. You have implemented a merge replication solution. Each Subscriber running on Windows 2000 and Windows NT 4.0 is able to initialize the subscription and replicate data successfully with the Publisher. However, your Windows Me and Windows 98 Subscribers are unable to successfully replicate with the Publisher. What is a likely source of this problem? How would you solve this problem?

   The Windows Me and Windows 98 Subscribers probably cannot access the initial snapshot folder. By default, the initial snapshot folder is accessible only using the hidden administrative share. This share is accessible only to users that are members of the local Administrators group of the Distributor. Solve this problem by using an explicitly created share for the snapshot folder and grant the required permissions to this share.

3. You are planning to implement a merge replication solution. What is the benefit of using a dedicated Distributor?

There is little benefit to a dedicated server because the Distributor plays a very limited role in merge replication. It serves primarily as a storage location for history information.

4. You have implemented transactional replication. You have been monitoring the size of the distribution database on the Distributor and notice that its size seems to be larger than anticipated. What might be the cause of this? What Distributor setting could you modify to affect its size?

   The cause of the distribution database being larger than anticipated could be the retention of transactions in the database for longer than anticipated. By default, they are held in the distribution database until all Subscribers have downloaded the transactions or a default period of 72 hours. You should check the maximum retention period to verify the maximum retention period. You should also check whether one or more Subscribers is taking a long time to download changes. Also, if anonymous Subscribers are permitted, all transactions will be kept for the maximum length of time.

Chapter 16

Review Questions

1. You are the database administrator for a number of SQL Server installations that generally must be available 24x7. What are the major failover differences between the use of standby servers with log shipping and the use of failover clustering?

   With failover clustering, failover to a secondary node occurs automatically with only a brief delay and without the need for users to connect to another server and restart incomplete transactions. With standby servers, a database administrator must manually promote the standby server to primary and demote the primary server. During this time, the database is unavailable. In addition, users must connect to a different server (or the standby server must be renamed to the original server name) and incomplete transactions must be restarted.

2. You are administering 10 production servers. You are planning to implement log shipping to provide for a quick restore of each production server in the case of a system failure. What is the minimum number of computers you can use to accomplish this task? What is the minimum number of computers you would recommend be used?

   The minimum number of computers is one. You could copy all transaction logs to a single server, which could also function as a monitoring server. If a production server fails, you can create the necessary logins and promote the standby server to primary for the databases affected. The standby server would continue to function as the standby server for the other production servers. Obviously, the capacity of a single server to handle this load is an issue. Also, this is a single point of failure, which is not advisable. The recommended number of servers will vary depending upon additional facts not presented here, such as size of databases and capacity of each server. However, you would probably want to have at least two standby servers and a separate monitoring server to eliminate single points of failure and provide excess capacity.