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

You work as DBA at Certkiller .com. You administer two Windows Server 2003 computers named Certkiller A and Certkiller B. You install SQL Server 2005 on both Certkiller A and Certkiller B to host a new company database. Certkiller A hosts a read-write copy of the company database in which all changes are made. Certkiller B subscribes to a publication on Certkiller A and is only used for reporting. A Windows domain administrator provides you with a domain user account named SQLSRV to use as the security context for the SQL Server services. A password policy of 42 days exists in the Default Domain Group Policy object (GPO). You install the database on Certkiller A and Certkiller B and configure replication. Everything works fine for six weeks, but then all SQL Server services fail. You need to correct the problem. What should you do?

You are a database administrator of two SQL Server 2005 computers named Certkiller A and Certkiller B. You have a Microsoft .NET application that has been modified so that it now accesses a database on Certkiller B in addition to Certkiller

You are a database administrator for Certkiller .com. Your company uses a different company's application that is based on SQL Server 2005 Standard Edition. The application executes a query that uses an index query hint. The index query hint is not suitable for your environment, but you cannot modify the query. You need to force the application to use a different query execution plan. What should you do?

You are a database administrator for Certkiller .com. You are responsible for a SQL Server 2005 database that has several indexes. You need to write a stored procedure that checks the indexes for fragmentation. Which Transact-SQL statement should you use?

You work as DBA at Certkiller .com. You administer two SQL Server 2005 computers named Certkiller A and Certkiller B. Certkiller A and Certkiller B contain a copy of a database named Sales. The database is replicated between Certkiller A and Certkiller B by using transactional replication. A full backup of each database is performed every night. Transaction log backups are performed every hour. Replication latency is typically less than two minutes. One afternoon, the Sales database on Certkiller A becomes corrupted. You are unable to repair the database. The Sales database on Certkiller B is unaffected. You need to return the Sales database on Certkiller A to normal operation as quickly as possible. You must ensure a minimum loss of data and minimal impact to users of either server. What should you do?

You are a database administrator for Certkiller .com. One of the databases on a SQL Server 2005 computer contains a stored procedure. Users run this stored procedure to import data into a table. The stored procedure needs to use the TRUNCATE TABLE command before importing new data into the table. However, the users who run the stored procedure do not have permission to truncate the table. You need to provide a way for the stored procedure to truncate the table before it imports new data. What should you do?

You are a database administrator for Certkiller .com. You have separate SQL Server 2005 development and production environments. You use the Business Intelligence Development Studio to create a SQL Server Integration Services (SSIS) package in your development environment. Then, you use the SSIS package to import data into your development environment from one of your company's trading partners. You need to deploy the SSIS package to your production environment. Your production environment uses different table names than your development environment. What should you do?

You are a database administrator for Certkiller .com. You notice that one of the data files on a SQL Server 2005 computer is corrupted. You need to restore the database from the most recent set of backups. You want to perform this task as quickly as possible, with a minimum loss of data. What should you do first?

You are a database administrator for Certkiller .com. A SQL Server 2005 computer named Certkiller A has a database named Inventory. Certkiller A is responsible for aggregating manufacturing part numbers from your company's trading partners. The manufacturing part numbers are stored in the Product table in the Inventory database. Every night, data is sent as text files from each trading partner to Certkiller. You need to import data and ensure that no duplicate manufacturing part numbers exist in the data imported from the text files that are stored in the Inventory database on Certkiller. You want to achieve this goal with the minimum amount of impact on your company's trading partners and your IT department. What should you do?

You are a database administrator for Certkiller .com. Your company owns a chain of 10 retail stores. Each retail store maintains point-of-sale transactions on its own SQL Server 2005 computer, in a database table named Sales. The Sales table also contains sales data from other stores to enable customer returns to any of the 10 retail stores. The sales data is refreshed from the main office to each retail store hourly. A trigger named trg_Coupon on the Sales table is used to generate sales coupons based on customer sales and buying patterns. You need to configure replication between the server in each retail store and a central server in the main office by using the fewest number of steps. Replication does not have to be in real time. What should you do?