You are a database administrator for AIOTestKing.com. You are responsible for managing 10 SQL Server 2005 computers that run Microsoft Windows Server 2003, Enterprise Edition. The company’s Microsoft Active Directory administrators handle all Group Policy object (GPO) deployments. The Active Directory administrators have deployed a security template named SQL05_Security.inf that has the appropriate settings to meet the company’s security policy. You need to identify the existing configuration of each SQL Server 2005 computer to ensure that it meets the company’s security policy. What should you do?

You are a database administrator for AIOTestKing.com. The company hosts SQL Server 2005 databases for subscription-based customers. New customer databases are created frequently. Databases are removed when customers subscriptions end. Company managers want to track who creates each database and when each database is created. To store this information, you create a SQL Server database named Logging. You need to ensure that the appropriate information is written to the Logging database. What should you do?

You are a database administrator for AIOTestKing.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?

You are a database administrator for AIOTestKing.com. The company has a SQL Server 2005 computer named AIOTestKing A database on AIOTestKing A stores sales history data for the company’s Web-based order system. Twenty of the business analysts in the company create ad hoc queries against the database. The performance of AIOTestKing A is routinely slow. You need to find out which business analyst is causing the problem. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution.
Choose two.)

You are a database administrator for AIOTestKing.com. A SQL Server 2005 computer named AIOTestKing A has a database named Inventory. AIOTestKing 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 AIOTestKing.
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 AIOTestKing.
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 AIOTestKing.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 AIOTestKing.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 AIOTestKing.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 AIOTestKing.com. The company runs a popular database-driven Web site against a SQL Server 2005 computer named AIOTestKing B. You need to ensure a quick response time and appropriate audit trail in the event that AIOTestKing B experiences excessive traffic due to denial-of-service (DoS) attacks. Which two actions should you perform? (Each correct answer presents part of the solution.
Choose two.)

You work as DBA at AIOTestKing.com. You administer two SQL Server 2005 computers named AIOTestKing A and AIOTestKing B. AIOTestKing A and AIOTestKing B contain a copy of a database named Sales. The database is replicated between AIOTestKing A and AIOTestKing 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 AIOTestKing A becomes corrupted.
You are unable to repair the database. The Sales database on AIOTestKing B is unaffected.
You need to return the Sales database on AIOTestKing 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?