Microsoft-PRO Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005 Set 4

You are a database administrator for Certkiller .com. You have many complex SQL Server Integration Services (SSIS) packages that move data among your servers. The SSIS packages import and export data to and from your company's trading partners. The SSIS packages can only run after certain conditions are met that exist outside of SQL Server. Your department uses a third-party tool to schedule the running of nightly scripts to test for these conditions. You need to run SSIS from your scripts as seamlessly as possible. What should you do?

You are the database administrator for a SQL Server 2005 computer named SQL1. Your company has five databases running on a single SQL Server 2005 instance. All indexes are created by using the SORT_IN_TEMPDB option to reduce the amount of time required and the amount of space used in the user databases to create and rebuild indexes. You need to design the tempdb database for optimal performance. What should you do?

You are the administrator of a SQL Server 2005 computer named SQL1. The SQL Server transaction log files are stored on an 80-GB mirrored volume. New databases have been added to SQL1, and the 80-GB volume is 85 percent full. You do not currently have the ability to upgrade the volume or to add more disk space. You need to receive an alert if the unused disk space on the 80-GB volume falls below 5 GB. What should you do?

You are the administrator of a SQL Server 2005 computer named SQL1. The SQL Server transaction log files are stored on an 80-GB mirrored volume. New databases have been added to SQL1, and the 80-GB volume is 85 percent full. You do not currently have the ability to upgrade the volume or to add more disk space. You need to receive an alert if the unused disk space on the 80-GB volume falls below 5 GB. What should you do?

You are the administrator of a SQL Server 2005 computer named SQL1. SQL1 contains two user databases. SQL1 also contains the publisher database for a transactional replication publication. Every night, a full backup is made of all databases. Transaction log backups are made twice each day for both user databases. SQL1 is configured to use SQL Server and Windows authentication. Client applications connect by using SQL Server login accounts. The disk subsystem on SQL1 experiences a complete failure. You replace the failed hardware, reinstall Microsoft Windows Server 2003, and reinstall SQL Server 2005. You need to ensure that SQL1 is operating normally as quickly as possible and with a minimum loss of data. Your solution must make the user databases available to client applications as soon as those databases are restored. What should you do first?

You are a database administrator for a financial services company. You are configuring replication between two SQL Server 2005 computers named SQL1 and SQL2. SQL1 stores real-time transactional data. SQL2 is used for reporting. SQL2 must not change data on SQL1. The publication contains a large amount of data. You need to ensure that during replication, data is copied from SQL1 to SQL2 in as near real-time as possible to ensure accurate reporting. You need to ensure that this data is copied securely. What should you do?

You are a database administrator for Certkiller .com. Users report that a SQL Server 2005 application is performing slowly. You need to identify the queries that are experiencing slow performance due to blocking. What should you do?

You are a database administrator for Certkiller .com. You manage 10 SQL Server 2005 computers. The computers reside on an isolated portion of the company's network that has been restricted from accessing the Internet. Only intranet client computers have access to the SQL Server 2005 computers. You need to audit the SQL Server computers to identify only the missing operating system or SQL Server patches, hotfixes, and service packs. What should you do?

You are a database administrator for Certkiller .com. You create a SQL Server Integration Services (SSIS) package that contains confidential information. The package will be used by only two company employees. The package is stored in the msdb database. You need to ensure that only the appropriate employees can execute the package. You must accomplish this goal by using the least amount of administrative effort. What should you do?

You are a database administrator for Certkiller .com. Users report that a SQL Server 2005 application is performing slowly. You need to identify the queries that are experiencing slow performance due to blocking. What should you do?