Microsoft 70-444 Exam - Certifysky.com
1. You are the administrator of a Microsoft Windows Server 2003 computer named Server1. SQL Server
2005 is installed on Server1 to host a database named MedDB. The MedDB database serves as the backend database for a new client/server application named MedDATA. Installation of the MedDATA application creates the database schema, application roles, and administrative accounts. It also sets all of the default permissions. You need to establish a permissions baseline for all objects in the MedDB
database. What should you do?
A. Use the SQL Server 2005 Management Studio to review the permissions of the default database objects.
B. Query the sys.server_permissions table on Server1.
C. Execute the sp_helplogins system stored procedure for each of the default users that the MedDATA application creates.
D. Query the sys.database_permissions and sys.database_principals tables in the MedDB database.
Answer: D
2. You are the administrator of a SQL Server 2005 computer named SQL1. SQL1 is a member of a Microsoft Active Directory domain. You do not have any rights or privileges to perform domain administration. However, you have been granted membership in the local Administrators group on SQL1. You perform most of the management of SQL1 from your administrative workstation. However, for security reasons, you want to track all attempts for interactive logons and network connections to SQL1. What should you do?
A. Create a Group Policy object (GPO) that is configured for success and failure auditing of the Audit account logon events setting. Ask the domain administrator to link the GPO to the object containing SQL1.
B. Configure the SQL Server service on SQL1 to audit all successful and failed logon attempts.
C. Edit the local security policy of SQL1. Then, configure success and failure auditing on the Audit logon events setting.
D. Run the SQL Server Profiler and use a standard default template.
Answer: C
3. You are the administrator of a SQL Server 2005 computer named SQL1. You create a security template that disables unnecessary services. The template is appropriate only for SQL1. You need to ensure that
the security template is applied to SQL1. Your solution must ensure that the template automatically overrides any manual configuration changes made by other administrators within an hour. What should you do?
A. Use the SQL Server Surface Area Configuration tool to import the security template.
B. Use the Local Security Policy editor to import the security template into the Local Security Policy object.
C. Configure Task Scheduler to import the security template by using Secedit.exe. Configure the task to run every half hour.
D. Configure Task Scheduler to run the Microsoft Baseline Security Analyzer tool every half hour.
Answer: C
4. You are the administrator of a Microsoft Windows Server 2003 computer named SQL1. SQL1 runs SQL Server 2005 and is a member of a Microsoft Active Directory domain named Contoso.com. The domain is configured with all of the default settings. Several members of the information security team have requested access to SQL1. The information security team uses the Microsoft Windows XP Professional, Linux, and Macintosh operating systems to perform vulnerability assessments. The information security team members ask you to create two new logon accounts for their use. They would like the logon accounts to have the following user names and passwords: User name\password for first account: pentest\P@ssw0rdUser name\password for second account: hacktest\password You need to provide the information security team with the requested access to SQL1. Which TransactSQL script should you use?
A. CREATE LOGIN pentestWITH PASSWORD = 'P@ssw0rd' CREATE LOGIN hacktestWITH PASSWORD = 'password'
B. CREATE LOGIN pentestWITH PASSWORD = 'P@ssw0rd' CREATE LOGIN hacktestWITH PASSWORD = 'password',CHECK_POLICY = OFF
C. CREATE LOGIN pentestWITH PASSWORD = 'P@ssw0rd' CREATE LOGIN hacktestWITH PASSWORD = 'password'[HASHED]
D. CREATE LOGIN [CONTOSO\pentest]FROM WINDOWS CREATE LOGIN [CONTOSO\hacktest]FROM WINDOWS
Answer: B
5. You are the administrator of a SQL Server 2005 computer named SQL1. All servers run Microsoft Windows Server 2003 and belong to a Microsoft Active Directory domain. The companys written security policy mandates strong passwords for all domain, local, and application accounts. The appropriate domain password policy has been deployed through the use of a Group Policy object (GPO). Many of the company's computers run MSDOSbased or UNIXbased operating systems. These computers require varying levels of access to a database named Services on SQL1. You need to ensure that these computers can access the Services database without violating the company's written security policy. What should you do?
A. Create a domain account for each of the company's users. Provide each domain account with access to
SQL1 and the Services database.
B. Create a local logon account on SQL1 for each of the company's users. Provide each logon account with access to SQL1 and the Services database.
C. Create a SQL Server logon account for each of the company's users. Enable the Enforce password policy option for each logon account. Provide each logon account with access to the Services database.
D. Configure a virtual directory through Internet Information Services (IIS) for access to SQL1. Direct users to the virtual directory for data access.
E. Create one SQL Server logon account for all of the company's computers running MSDOS or UNIXbased operating systems. Enable the Enforce password policy option for users' accounts. Provide the logon accounts with access to the Services database.
Answer: C
6. You are a database administrator for your company. The company runs a popular databasedriven Web site against a SQL Server 2005 computer named SQL2. You need to ensure a quick response time and appropriate audit trail in the event that SQL2 experiences excessive traffic due to denialofservice (DoS) attacks. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A. Create a new performance alert to monitor the Current Bandwidth counter.
B. Create a new performance alert to monitor the Bytes Total/sec counter.
C. Configure the new performance alert to start a SQL Server Profiler trace.
D. Configure the new performance alert to start a Network Monitor capture. Answer: B, D
7. You are a database administrator for your company. You are configuring a new SQL Server 2005 computer named SQL1. SQL1 will run Reporting Services. It will also be configured to automatically perform database backups and other maintenance tasks. There are no other SQL Server computers in the network environment. All access to SQL1 will be made by using SQL1s DNS name. You need to disable any unnecessary services on SQL1. Which service or services should you disable? (Choose all that apply.)
A. Internet Information Services
B. SQL Server Browser
C. SQL Server Agent
D. Microsoft Distributed Transaction Coordinator
Answer: B, D
8. You are a database administrator for your company. 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 TransactSQL statement should you use?
A. DBCC DBREINDEX
B. SELECT * FROM sys.dm_db_index_physical_stats
C. DBCC INDEXDEFRAG
D. SELECT * FROM sys.indexes
Answer: B
9. You are a database administrator for your company. The company uses a SQL Server 2005 database. A full backup of the database is made each night. Transaction log backups are made each morning and each afternoon. You notice that the database transaction log file grows very large throughout the day. You need to minimize the size of the transaction log file. You also need to minimize data loss in the event of a failure. What should you do?
A. Decrease the amount of time between transaction log backups.
B. Configure the database to use the simple recovery model.
C. Configure the RECOVERY INTERVAL option to be a smaller value.
D. Run DBCC SHRINKFILE on the transaction log file.
Answer: A
10. You are a database administrator of two SQL Server 2005 computers named SQL1 and SQL2. You have a Microsoft .NET application that has been modified so that it now accesses a database on SQL2 in addition to SQL1. You do not want the user application to connect directly to SQL2. You need to enable the data retrieval from SQL2 while maintaining the ability to assign different permissions to different users who use the .NET application. What should you do?
A. Change the .NET application to define a new server connection to SQL2.
B. Configure a linked server on SQL1 to point to SQL2.
C. Configure a linked server on SQL2 to point to SQL1.
D. Change the stored procedures called by the .NET application to include the OPENXML command.
Answer: B
11. You are a database administrator for your company. A database on one of the company's SQL Server 2005 computers stores transaction information for the company's Webbased order system. The database is configured to use the full recovery model. Your company receives very few orders between
the hours of 19:00 and 04:00. Your maximum acceptable risk for data loss is one hour. Database backups must be performed as quickly as possible. You need to develop a maintenance plan to manage this risk. What should you do?
A. Configure a Back Up Database task to do a full backup of the database every hour. Configure a Check Database Integrity task to run every hour.
B. Configure a Back Up Database task to do a full backup of the database every day at 19:00. Configure a second Back Up Database task to do differential backups at 09:00 and 14:00. Configure a third Back Up Database task to do transaction log backups every hour.
C. Configure a Back Up Database task to do a full backup of the database every day at 19:00. Configure a second Back Up Database task to do differential backups at 09:00 and 14:00. Configure a Check Database Integrity task to run every hour.
D. Configure a Back Up Database task to do a full backup at 09:00 and 14:00. Configure a second Back Up Database task to do transaction log backups every hour.
Answer: B
12. You are a database administrator for your company. Each night, you perform full backups of your SQL Server 2005 computer. During business hours, you perform hourly transaction log backups. You rebuild database indexes each night. You need to modify your backup strategy. You must ensure that the database recovers to a new server as quickly as possible. What should you do?
A. Schedule the full backups to run after the nightly index is rebuilt.
B. Schedule the full backup to run before the nightly index is rebuilt. Schedule a differential backup to run after the nightly index is rebuilt.
C. Schedule an additional transaction log backup to run after the nightly index is rebuilt.
D. Schedule a marker to be added to the transaction log before the nightly index is rebuilt.
Answer: A
13. You are the administrator of a SQL Server 2005 computer named SQL1. SQL1 contains a database that utilizes SQL Server log shipping. Log shipping occurs every 30 minutes. Each night, a full backup is made of the database, and all database indexes are rebuilt. The company's network administrator reports that SQL1s network utilization becomes excessive after the nightly maintenance tasks are complete. You need to reduce the network bandwidth utilization for SQL1. What should you do?
A. Configure the database to use the bulklogged recovery model.
B. Each night, rebuild only those indexes that require it.
C. Disable log shipping before the nightly maintenance. Reenable log shipping after nightly maintenance completes.
D. Increase the time between transaction log backups.
Answer: B
14. You are a database administrator for your company. The company's financial forecasting application uses a SQL Server 2005 database that enables business users to run both ad hoc and predefined queries. You suspect that some queries consume an excessive amount of server resources. You need to identify which queries consume the most resources. You want to achieve this goal as quickly as possible. What should you do?
A. Use the DBCC INPUTBUFFER command.
B. Use the sys.dm_exec_query_stats dynamic management view (DMV).
C. Use the sqldiag utility.
D. Use the SHOWPLAN session option.
Answer: B
15. You are a database administrator for your company. The company uses a SQL Server 2005 computer that runs Reporting Services. Reporting Services is used to produce hundreds of reports each day. You discover that the ReportServerTempDB database is growing. You need to minimize the size of the ReportServerTempDB database. What should you do?
A. Decrease the time between transaction log backups of the database.
B. Delete report snapshot history each week.
C. Minimize the use of session caching in the reports.
D. Minimize the use of report snapshots in the reports.
Answer: C
16. You are a database administrator for your company. A user named Susan belongs to the Windows SalesManagers group. Susan needs a Reporting Services report to display annual sales information by territory. A user named Joe reports to Susan and belongs to the Windows Sales group. He needs a report that is limited to his sales territory, but still displays the same detailed sales data that is on Susan's report. You need to design a secure solution that meets the reporting needs of the users and that consumes the minimal amount of server resources. What should you do?
A. Design a report for Susan that includes two data regions. One data region is used for summary sales data. The other data region is used for the detailed sales data for each sales representative. Then, design
a report for Joe that displays the detailed sales data.
B. Design two reports for Susan. One report includes the territory information, and one report includes the detailed sales data. Then, design a report for Joe that displays the detailed sales data for his territories.
C. Design a report for Susan that includes the territory information. Then, design a report for Joe that includes detailed sales data for his territories. Finally, configure Susans report so that it includes the data
in Joes report.
D. Design a parameterized report for Susan that includes all territory information. Then, design a linked report for Joe that links to Susans report.
Answer: D
17. You are a database administrator for your company. The company uses a SQL Server 2005 application. Each night, data is imported into a database, and the database is then set to be readonly.
During business hours, users run predefined and ad hoc queries. You need to monitor the servers performance during the day. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
A. Use the Lock Waits/sec performance counter.
B. Use the Number of Deadlocks/sec performance counter.
C. Use the Transactions/sec performance counter.
D. Use the Full Scans/sec performance counter. E.Use the Batches/sec performance counter.
Answer: D, E
18. You are a database administrator for your company. The company has two SQL Server 2005 computers named SQL1 and SQL2. SQL1 is located on the local network. SQL2 is located at a vendors site and is connected by using a VPN over the Internet. You need to write heterogeneous queries on SQL1 that access data from both SQL1 and SQL2. To ensure that your queries are functional, you must configure access to the vendors site. What should you do?
A. Configure an HTTP endpoint for SQL2 on SQL1.
B. Configure SQL2 as a linked server on SQL1.
C. Configure an HTTP endpoint for SQL1 on SQL2.
D. Configure SQL1 as a linked server on SQL2.
Answer: B
19. You are a database administrator for your company. The company uses a vendor company's application that is based on a proprietary database. You query data from the application database and import the data into a SQL Server 2005 database. The application vendor provides you with a new OLE
DB driver to be used when querying the application database. Company policy prohibits connections between the SQL Server database and the application database, except when querying this data. You need to provide a way to query data from the application database. What should you?
A. Configure a Linked Server connection to the application database.
B. Write a query that uses an OPENDATASOURCE command.
C. Write a query that uses an OPENQUERY command.
D. Configure a Remote Server connection to the application database.
Answer: B
20. You are a database administrator for your company. The company uses a SQL Server 2005 database that includes a table named Inventory. The table contains a column named Price. A company policy states that the value in the Price column cannot be decreased by more than 10 percent in any single database operation. Updates to the Price column are made by various means, including by using ad hoc queries. You need to ensure that this company policy is enforced. What should you do?
A. Create a trigger that rolls back changes to the Price column that violate company policy.
B. Create a stored procedure that disallows changes to the Price column that violate company policy.
C. On the Price column, create a check constraint that requires a specified minimum value.
D. On the Price column, create a foreign key constraint to a table that contains valid prices.
Answer: A
21. You are a database administrator for your company. Your company has 10 SQL Server 2005 computers in a distributed environment. The SQL Server computers each subscribe to a transactional replication publication. Company policy defines service level agreements (SLAs) for maximum replication latency. You need to verify that the SLAs are being met. What should you do?
A. Ping each subscriber from the server that hosts the publication database. Record the response time for each.
B. Create and execute a stored procedure that verifies the data on each subscriber. Record the execution time of the stored procedure.
C. Record the time stamps on replicationrelated events in the application event log of each subscriber.
D. Use tracer tokens to measure and record latency of each subscriber.
Answer: D
22. You are a database administrator for your company. A company vendor provides product inventory data by using a Web service. Your company needs to create several reports based on this data. You need
to ensure that the reports have access to the necessary data. Your solution must provide the best reporting performance. What should you do?
A. Create Reporting Services reports that retrieve data directly from the vendor Web service.
B. Create a SQL Server Integration Services (SSIS) package that uses a Web service task to retrieve the data and store it in the database. Create reports that utilize the data in the database.
C. Create a SQL Server Integration Services (SSIS) package that uses an FTP task to retrieve the data and store it in the database. Create reports that utilize the data in the database.
D. Create Reporting Services reports that retrieve data by using XPath queries.
Answer: B
23. You are a database administrator for your company. Your SQL Server 2005 database contains a table named Customer with a column named PostalCode in it. New PostalCode values are added regularly. You need to ensure that values contained in the PostalCode column are verified. You need to retrieve, from the Postal Service in each country in which you have customers, the domain of values for the PostalCode column. Rapid response time for data access is of primary concern. What should you do?
A. Import the Postal Service data on a nightly basis into a PostalCode table. Then, configure a foreign key on the Customer table to the PostalCode table.
B. Configure a CHECK constraint on the PostalCode field of the Customer table to allow only valid values.
C. Implement a CLR trigger that looks up the postal code data by using a Web service offered by the Postal Service.
D. Implement a database trigger that looks up the postal code data from a server maintained by the Postal Service.
E. Configure a new PostalCode data type and ensure that it conforms to the Postal Service specifications for the format for each country.
Answer: A
24. You are a database administrator for your company. 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?
A. Create a SQL Server Integration Services (SSIS) package configuration. Build a deployment utility. Copy the deployment folder for your SSIS project to your production server. Execute the manifest file.
B. Save the SQL Server Integration Services (SSIS) package to a file. Copy the file to the production server. Configure the SSIS package on the production server to use the new file.
C. Back up the master database and restore it to the production server. Rename the appropriate tables inside the master database.
D. Back up the msdb database and restore it to the production server. Rename the appropriate tables inside the msdb database.
Answer: A
25. You are a database administrator for an electronics distribution company. You are documenting your company's requirements for data quality. You need this information so that you can store part numbers, components, and stock levels from your company's trading partners in your SQL Server 2005 environment. You need to determine the standards for data quality. You need to consult with the business analysts and other database administrators (DBAs) in your organization to do so. What are three possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose three.)
A. Have your company's trading partners be responsible for their own data quality.
B. Establish metrics for measuring data quality.
C. Identify how to monitor the quality of your data.
D. Identify how to standardize data elements. E.Identify how to keep data as small as possible.
Answer: B, C, D
26. You are a database administrator for your company. The company's manufacturing floor has three automated testing areas for finished items. Each testing area uses SQL Server 2005 to store test results. You need to develop a replication strategy to ensure that test results flow continuously from the testing areas to the central SQL Server as quickly as possible. The test results are used for reporting. What should you do?
A. Create a separate snapshot publication at each of the testing areas for the test results.Create a pull subscription on the central server to each of the testing area publications.
B. Create a separate snapshot publication at each of the testing areas for the test results.Create a merge subscription on each distributor to the central server.
C. Create a separate transactional publication at each of the testing areas for the test results.Create push subscriptions on each of the testing areas to the central server.
D. Create a separate transactional publication at each of the testing areas for the test results.Create a merge subscription on each distributor to the central server.
Answer: C
27. You are a database administrator for your company. You are creating a SQL Server 2005 database that will contain many tables to store contact information for customers, partners, and vendors. You need to ensure consistency in the data types that are used in all columns in the database tables. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
A. Create a DDL trigger that alerts you when the schema in a table changes.
B. Create a CHECK constraint to enforce values allowed in the columns in the database tables.
C. Create TransactSQL userdefined data types for use in the tables.
D. Create CLR userdefined types for use in the tables.
E. Create a DML trigger that alerts you when the data in a table changes.
Answer: C, D
28. You are a database administrator for your company. A SQL Server 2005 computer named SQL1 has a database named Inventory. SQL1 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 SQL1. 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 SQL1. 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?
A. Ensure that each text file is stored in an Extensible Markup Language (XML) file. Use Extensible Stylesheet Language Transformations (XSLT) to automatically remove duplicates before data is imported into the Product table.
B. Ensure that each of your company's trading partners uses unique key values for manufacturing part numbers. Then, import the text files into the Product table.
C. Import the data from each text file into a staging table. Write a query to include a HAVING clause to remove duplicate values before inserting results into the Product table.
D. Place a unique index on the PartNum column.
Answer: C
29. You are a database administrator for your company. You administer a SQL Server 2005 computer. Customers send orders to your company over the Internet in an XML format. The XML is submitted as an input argument of a stored procedure. The stored procedure needs to produce a result set that includes
all invalid product identification numbers (IDs) from the order. A company database contains a list of all valid product IDs. You need to ensure that the stored procedure can produce the desired result set. Your solution must provide the fastest possible query performance. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
A. Write an OUTER JOIN query that includes a WHERE clause.Write the WHERE clause to remove valid product IDs.
B. Write a CROSS JOIN query that includes a WHERE clause.Write the WHERE clause to remove valid product IDs.
C. Write a query that uses a NOT EXISTS() subquery.
D. Write a query that uses the HASH JOIN query hint.
Answer: A, C
30. You are the database administrator of a SQL Server 2005 computer named SQL1. The following query is run frequently. SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName AS FullName , E.Title, E.VacationHours, E.SickLeaveHours, A.AddressLine1, A.AddressLine2, A.City, A.PostalCode, S.[Name] AS StateFROM HumanResources.Employee AS E INNER JOIN HumanResources.EmployeeAddress AS EA ON E.EmployeeID = EA.EmployeeID INNER JOIN Person.Address AS A ON A.AddressID = EA.AddressID INNER JOIN Person.Contact AS C ON C.ContactID = E.ContactID INNER JOIN Person.StateProvince AS S ON S.StateProvinceID = A.StateProvinceIDORDER BY C.LastName All tables referenced in the query are readintensive with very few inserts and updates. You cannot change existing objects in the database. You can add new objects to the database. You need to optimize the performance of the query. What should you do?
A. Create a view that contains all of the columns that are used in the query.
B. Create a partitioned table that contains all of the columns that are used in the query.
C. Create an indexed view that contains all of the columns that are used in the query.
D. Alter the current indexes by setting the FILLFACTOR option to 50.
Answer: C
More FREE Microsoft 070-444 exam questions
70-444 Exam Prep
Learn what you need to know to pass the 70-444 exam easily
Guarantee your
CertifySky offers free demo for 70-444 exam. You can check out the interface, question quality and usability of our practice exams before you decide to buy it. We are the only one site can offer demo for almost all products. If you want to try 70-444 Exam Prep Demo, Click the "Try Demo" button.
70-444 Certification Exam and Study Guide
There are many online resources for preparing for the
CertifySky is the online Certification Expert recognized by a worldwide audience of IT professionals and executives alike as the definitive source of training materials for the candidate seeking insight, updates and resources for vendor certifications.
70-444 Practice Test Questions, Printable PDF Braindumps
We are all well aware that a major problem in the IT industry is that there is a lack of quality study materials. Our 70-444 Preparation Exam Material provides you everything you will need to take a certification examination. Details are researched and produced by Certification Experts who are constantly using industry experience to produce precise, logical and verified explanations for the answers. You may get questions from different web sites or books, but logic is the key.
Our
Furthermore, we are constantly updating our 70-444 Exam. These 70-444 Exam updates are supplied free of charge to Certify Sky.com customers- hereby becoming an investment rather than a disposable product. Our clients receive the most reliable and up-to-date information when they decide to take the exam, just contact us. Our candidates walk into the Testing Room as confident as a Certification Administrator.
Like actual certification exams our 70-444 Exam is in multiple-choice questions (MCQs). After purchasing our products you are just a step away from testing for certification. Still not convinced? Try our free samples or choose to buy your 70-444 Practice Exam now!
Our 70-444 practice exams and study questions are composed by current and active Information Technology experts, who use their experience in preparing you for your future in IT.
Commitment to Your Success:
At Certifysky.com we are committed to you ongoing success. Our exams and questions are constantly being updated and compared to industry standards.
You are not about to purchase a disposable product. 70-444 practice exam updates are supplied free of charge. Regardless of how soon you decide to take the actual
The CertifySky 70-444 study guide is guaranteed to be 100% braindump free. We value the quality of training you receive through the 70-444 study guide and will never support 70-444 braindumps, or any 70-444 brain dump site. 70-444 braindump sites cannot compare to the understanding, learning and comprehension you will gain from a non-70-444 braindumps site, based on facts and case studies, like Certify Sky.
When selecting the CertifySky 70-444 exam study preparation materials, you are purchasing the highest quality CertifySky 70-444 products available through the web today. The 70-444 CertifySky practice exams and study guides are current and updated monthly, providing you with the highest 70-444 Certify Sky ROI. Start you road to 70-444 CertifySky success today, buy purchasing the CertifySky 70-444 training materials today!
Posts
