You define the default collation for a SQL Server 2005 instance at installation time
during setup. SQL Server uses the collation setting to determine how non-Unicode
character data is stored and how to sort and compare Unicode and non-Unicode data.
To understand SQL Server 2005 collations, you need to start with the Windows OS.
When you install a Windows OS, you install a version for the language you want to
use, such as English, Greek, or Russian. These various language versions require dif-
ferent characters and different code pages to support the character sets and associated
keyboard layouts. A Windows locale is also set, based on the version of the Windows
OS that you have installed. This Windows locale determines the settings for numbers,
currencies, times, and dates on the server.
Although this process might initially seem complicated, determining the Windows col-
lation to use for SQL Server 2005 is generally straightforward. You should let the
Microsoft SQL Server Installation Wizard determine the default Windows collation
based on the Windows locale of the Windows OS unless one of the following condi-
tions exists:
■ The primary language supported by the SQL Server 2005 instance you are
installing is different from the Windows locale of the computer on which you are
installing SQL Server 2005.
■ The SQL Server 2005 instance you are installing will participate in a replication
scheme with SQL Server 2005 instances supporting a different language.
mymouna
Rabu, 03 Juni 2009
Kamis, 21 Mei 2009
What Is Capacity Planning?
What Is Capacity Planning?
The term capacity planning refers to the process of forecasting and preparing for future hardware needs. Capacity planning involves performing tests to measure how well a Web, application, or database server scales with existing hardware, to predict when new hardware will be needed, and then to design the hardware systems needed to run critical applications in the future. For database servers in particular, capacity planning involves, above all, determining the performance requirements of database applications for your business and then analyzing data trends to help ensure that these performance requirements can be met in the future.
Capacity planning can be divided into two main areas: preconfiguration capacity planning (or sizing) and postconfiguration capacity planning. Preconfiguration capacity planning involves anticipating the hardware you will need to process a given workload at the level of quality agreed to either in a service level agreement (
Conducting Capacity Planning Interviews
Before you begin to estimate the hardware needed to support a database server, you
need to know certain things that—if you don’t know them already—are best determined in interviews with stakeholders.
Technical Environment
The most important step of sizing a database server is to collect information about the database workload and the environment in which it will be processed. Few organizations have the budget to size a database server simply by testing this workload against a prototype server; for all other organizations, you need to determine essential information to size accurately, and interviews with technical personnel offer the best opportunity to learn this information.
The information you might need to extract from technical interviews includes historical baseline data for the current production server, details about the number of disk reads and writes generated by the database application, growth in database usage rates, and total storage requirements of the database (including associated space for tempdb, indexes, text files, and—potentially—backup files).
Business Requirements
Those who perform capacity planning must meet with management personnel to determine the goals and expectations of the capacity planning study itself and also to extract key information to be used in the planning. This information might include customer needs, operational needs, budget constraints, growth in the number of
employees, and the estimation period for which the new hardware outlined in the plan will meet the stated performance requirements.
Technical Requirements
The technical requirements of the planned installation can be learned in interviews with either technical or management personnel. For example, after meeting with operations managers, you might determine that the company’s business operations require database response times of no more than five seconds. According to these managers, any new hardware purchases must be justified by meeting this operational requirement for three years from the day of implementation.
Selasa, 19 Mei 2009
Planning a Database Server Infrastructure
Before you deploy a database server, you need to collect as much information as pos
sible about the database server environment. This information includes the business
and technical requirements driving the deployment as well as information about the
database workload and the database itself. After you’ve gathered this information, you
can begin to predict with some accuracy the hardware and software the database will
need to meet the demands of its environment.
Exam objectives in this chapter:
¦ Design for capacity requirements.
? Analyze storage requirements.
? Analyze network requirements.
? Analyze CPU requirements.
? Analyze the current configuration.
? Analyze memory requirements.
? Forecast and incorporate anticipated growth requirements into the capac
ity requirements.
¦ Specify software versions and hardware configurations.
? Choose a version and edition of the operating system.
? Choose a version of SQL Server 2005.
? Choose a CPU type.
? Choose memory options.
? Choose a type of storage.
¦ Design physical storage.
? Design transaction log storage.
? Decide where to install the operating system.
? Decide where to place SQL Server service executables.
? Specify the number and placement of files to create for each database.
sible about the database server environment. This information includes the business
and technical requirements driving the deployment as well as information about the
database workload and the database itself. After you’ve gathered this information, you
can begin to predict with some accuracy the hardware and software the database will
need to meet the demands of its environment.
Exam objectives in this chapter:
¦ Design for capacity requirements.
? Analyze storage requirements.
? Analyze network requirements.
? Analyze CPU requirements.
? Analyze the current configuration.
? Analyze memory requirements.
? Forecast and incorporate anticipated growth requirements into the capac
ity requirements.
¦ Specify software versions and hardware configurations.
? Choose a version and edition of the operating system.
? Choose a version of SQL Server 2005.
? Choose a CPU type.
? Choose memory options.
? Choose a type of storage.
¦ Design physical storage.
? Design transaction log storage.
? Decide where to install the operating system.
? Decide where to place SQL Server service executables.
? Specify the number and placement of files to create for each database.
Minggu, 04 Januari 2009
Downloading and Installing SQL Server 2008 Express
Downloading and Installing SQL Server 2008 ExpressDownload and install any edition of SQL Server Express and the prerequisite components with a single tool.
|
Which edition of SQL Server 2008 Express is right for you?SQL Server 2008 Express is available in the following 3 editions (each is available from the Install Wizard):
|
Register Your Copy
|
Additional Resources |
System Requirements
|
SQL Server 2008 Enterprise Evaluation (Advanced Professional users) |
Minggu, 28 Desember 2008
Microsoft 70-431 Braindump #2
QUESTION NO: 11
You are creating a Web-based application to manage data aggregation for reports. The application connects to a SQL Server 2005 database named DataManager. One page in the application has controls that execute stored procedures in a database named ReportingDatabase. There is an existing Service Broker connection between the DataManager database and ReportingDatabase.
You want to add two new message types to the existing service. In each database, you create message types named ProcessReport and SendResult. You need to add the two new message types to the existing service. What should you do first?
A. Create a queue on each database with the ACTIVATION argument set to DataManager.dbo.ProcessReport.
B. Create a conversation between the databases by using the following statement.BEGIN DIALOG FROM SERVICE 'ProcessReport' TO SERVICE 'SendResult'
C. Create a contract between the services by using the following statement.CREATE CONTRACT ProcessData (ProcessReport SENT BY INITIATOR, SendResult SENT
BY TARGET)
D. Create services for each database by using the following statement.CREATE SERVICE DataManager ON QUEUE ProcessReport
Answer: C
QUESTION NO: 12
You work at the regional sales office. You are responsible for importing and exporting data in SQL Server 2005 databases. The main office asks you to send them a text file that contains updated contact information for the customers in your region. The database administrator in the main office asks that the data be sorted by the StateProvince, Surname, and FirstName columns.
You need to satisfy these requirements by using the least amount of effort. What should you do?
A. Specify StateProvince, Surname, and FirstName in the ORDER hint in the bcp out command.
B. Create a format file for the export operation.
C. Specify StateProvince, Surname, and FirstName in the ORDER BY clause in the bcp queryout command.
D. Copy the data into a new table that has a clustered index on StateProvince, Surname, and FirstName. Export the data.
Answer: C
QUESTION NO: 13
TestKing.com has two SQL Server 2005 computers named SQL1 and SQL2. Both
servers take part in replication. SQL1 is both the Publisher and its own Distributor of a
publication named Pub1. Pub1 is the only publication on SQL1, and SQL2 is the only
Subscriber. Your supervisor requests a status report about the replication latencies. Using
Replication Monitor on SQL1, you need to find out the current latencies between the
Publisher and Distributor as well as between the Distributor and Subscriber.
What should you do?
A. Select the Subscription Watch List tab for SQL1. View the Latency column for the
SQL2 subscription.
B. Select the All Subscriptions tab for the Pub1 publication. View the Latency column
for the SQL2 subscription.
C. Select the Tracer Tokens tab for the Pub1 publication. Select the Insert Tracer option
and wait for the requested latency values for the SQL2 subscription to appear.
D. Select the Subscription Watch List tab for SQL1. Double-click the SQL2 subscription.
View the duration details on the Publisher to Distributor History tab as well as on the
Distributor to Subscriber History tab.
Answer: C
QUESTION NO: 14
Exhibit:
TestKing.com has two SQL Server 2005 computers named SQL1 and SQL2. A database named DB1 is located on SQL1. DB1 contains a table named TestKing4. TestKing4 is replicated to a database named DB1Repl, which is located on SQL2. Full-Text Search is not being used. Users report that the queries they run against TestKing4 in DB1Repl are very slow. You investigate and discover that only the clustered index of TestKing4 is replicated. All other indexes in DB1Repl are missing. You examine the TestKing4 article properties. The current TestKing4 article properties are shown in the exhibit.
You need to change the article properties so that all indexes of TestKing4 in DB1 are replicated when the subscription is reinitialized. Which two article properties should you change? (Each correct answer presents part of the solution. Choose two.)
A. Copy clustered index
B. Copy nonclustered indexes
C. Copy extended properties
D. Copy unique key constraints
E. Copy index partitioning schemes
F. Copy XML indexes
Answer: B,F
QUESTION NO: 15
You are creating an HTTP endpoint that will be used to provide customer data to external applications. Your SQL Server 2005 computer is named SQL1. You create a stored procedure named dbo.usp_GetPersonData to retrieve the data in the TestKing database.
You create the endpoint by using the following code.
CREATE ENDPOINT SQLEP_AWPersons AS HTTP (PATH = '/AWpersons',
AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR), SITE = 'SQL1') FOR
SOAP (WEBMETHOD 'PersonData' (NAME='TestKing.dbo.usp_GetPersonData'),
BATCHES = DISABLED, WSDL = DEFAULT, DATABASE = 'TestKing',
NAMESPACE = 'http://Adventure-Works/Persons')
The first users to connect to the endpoint tell you that they do not get any data. You connect to the endpoint and discover that it is not responding. You need to modify the endpoint so that data is returned as expected. What should you do?
A. Change the AUTHENTICATION property to KERBEROS.
B. Specify BATCHES = ENABLED.
C. Specify STATE = Started.
D. Specify WSDL = 'pr_GetPersonData'.
Answer: C
QUESTION NO: 16
You work in Dublin at the main office of TestKing.com. You are responsible for managing a SQL Server 2005 database. The sales department wants a report that compares customer activity in the previous quarter between the main office in Dublin and the branch office in Buenos Aires. They want the data sorted by surname and first name. You restore a recent backup of the Buenos Aires database onto your server. You write queries to build the report, ordering the data by the Surname and FirstName columns. You review the data and notice that the customer list from the Buenos Aires database is sorted differently. The sales department needs the revised data within 15 minutes for a
presentation.
You need to implement the fastest possible solution that ensures that the data from both databases is sorted identically. What should you do?
A. Use the Copy Database Wizard to copy the data in the Buenos Aires database to a new database with the same collation as the Dublin database.
B. Use the SQL Server Import and Export Wizard to copy the data from the Buenos Aires database into new tables, specifying the same collation as the Dublin database.
C. Modify the format file to specify the same collation as the Dublin database. Import the table again.
D. Modify the query on the Buenos Aires database to use the COLLATE setting in the ORDER BY clause. In the query, specify the same collation as the Dublin database.
Answer: D
QUESTION NO: 17
You work for a company that sells books. You are creating a report for a SQL Server 2005 database. The report will list sales representatives and their total sales for the current month. The report must include only those sales representatives who met their sales quota for the current month. The monthly sales quota is $2,000. The date parameters are passed in variables named @FromDate and @ToDate.
You need to create the report so that it meets these requirements. Which SQL query should you use?
A. SELECT s.AgentName, SUM(ISNULL(o.OrderTotal,0.00))AS SumOrderTotalFROM
SalesAgent s JOIN OrderHeader o ON s.AgentID = o.AgentIDWHERE o.OrderDate
BETWEEN @FromDate AND @ToDateGROUP BY s.AgentName
B. SELECT s.AgentName, SUM(ISNULL (o.OrderTotal,0.00))AS
SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID =
o.AgentIDWHERE o.OrderDate BETWEEN @FromDate AND @ToDate AND
o.OrderTotal >= 2000GROUP BY s.AgentName
C. SELECT s.AgentName, SUM(ISNULL (o.OrderTotal,0.00)) AS
SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID = o.AgentID
WHERE o.OrderDate BETWEEN @FromDate AND @ToDateGROUP BY
s.AgentNameHAVING SUM(o.OrderTotal) >= 2000
D. SELECT s.AgentName, SUM(ISNULL(o.OrderTotal,0.00)) AS
SumOrderTotalFROM SalesAgent s JOIN OrderHeader o ON s.AgentID =
o.AgentIDWHERE o.ordertotal = 2000 AND o.OrderDate BETWEEN @FromDate
AND @ToDateGROUP BY s.AgentNameHAVING SUM(o.OrderTotal) >= 2000
Answer: C
QUESTION NO: 18
You are creating a stored procedure that will delete data from the Contact table in a SQL Server 2005 database. The stored procedure includes the following Transact-SQL statement to handle any errors that occur.
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM Person.Contact
WHERE ContactID = @ContactID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(2000) DECLARE @ErrorSeverity int DECLARE
@ErrorState int SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity =
ERROR_SEVERITY(), @ErrorState = ERROR_STATE() RAISERROR
You test the stored procedure and discover that it leaves open transactions. You need to modify the stored procedure so that it properly handles the open transactions. What should you do?
A. Add a COMMIT TRANSACTION command to the CATCH block.
B. Remove the COMMIT TRANSACTION command from the TRY block.
C. Add a ROLLBACK TRANSACTION command to the CATCH block.
D. Add a ROLLBACK TRANSACTION command to the TRY block.
Answer: C
QUESTION NO: 19
You are creating an online catalog application that will display product information on the company Web site. The product data is stored in a SQL Server 2005 database. The data is stored as relational data but must be passed to the application as an XML document by using FOR XML. You test your application and notice that not all of the items matching your query appear in the XML document. Only those products that have values for all elements in the schema appear.
You need to modify your Transact-SQL statement so that all products matching your query appear in the XML document. What should you do?
A. Add an XML index to the table that contains the product data.
B. Add the XSINIL argument to the ELEMENTS directive in the query.
C. Add a HAVING clause to the query.
D. Add the replace value of clause to the query.
Answer: B
QUESTION NO: 20
TestKing.com has two SQL Server 2005 computers named SQL1 and SQL2. Transaction log shipping occurs from SQL1 to SQL2 by using default SQL Server Agent schedule settings.
You need to reconfigure transaction log shipping to provide minimum latency on SQL2. What should you do?
A. On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, maintain default schedule settings for both the log shipping copy and the restore jobs.
B. On SQL1, change the schedule type for the transaction log backup to Start automatically when SQL Server Agent starts. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically when SQL Server Agent starts.
C. On SQL1, maintain default schedule settings for the transaction log backup job. On SQL2, change the schedule types for both the log shipping copy and the restore jobs to Start automatically when SQL Server Agent starts.
D. On SQL1, reschedule the transaction log backup job so that it occurs every minute. On SQL2, reschedule both the log shipping copy and the restore jobs so that they occur every minute.
Answer: D
Microsoft 70-431 Braindump #1
QUESTION NO: 1
Your application must access data that is located on two SQL Server 2005 computers. One of these servers is named SQL1 and the other is SQL2. You have permissions to create a stored procedure on SQL1 to support your application. However, on SQL2 you only have permissions to select data.
You write the stored procedure on SQL1. The stored procedure accesses SQL2 by using the OPENQUERY Transact-SQL statement. However, the query fails when executed. You need to troubleshoot the cause of the error. What should you do?
A. Join the two servers by using the four-part syntax of server.database.schema.table.
B. Reference SQL2 by using an alias.
C. Add SQL2 as a remote server to SQL1.
D. Add SQL2 as a linked server to SQL1.
Answer: D
QUESTION NO: 2
You are preparing for a new installation of SQL Server 2005. You need to select the protocols that client computers might use to connect to the server.
Which two protocols can you use to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
A. Named Pipes
B. TCP/IP
C. Shared Memory
D. Virtual Interface Adapter (VIA)
E. Multiprotocol
Answer: A,B
QUESTION NO: 3
You configure a new SQL Server 2005 computer to use TCP/IP with all default settings. Your corporate policy requires that each server use a firewall. You find that you can connect to the SQL Server instance from the local computer. However, client computers cannot connect to the SQL Server instance.
You need to identify the most likely cause of the connection issues. What should you do first?
A. Ensure that port 1433 is open in your firewall.
B. Ensure that port 443 is open in your firewall.
C. Ensure that client computers connect by using Shared Memory protocol.
D. Ensure that the server is not paused.
Answer: A
QUESTION NO: 4
TestKing.com has multiple servers in a distributed environment. You work with two SQL Server 2005 computers named SQL1 and SQL2. Each server uses SQL Server Authentication and they use different logins.
You need to write a distributed query that joins the data on SQL1 with the data on SQL2. What should you do?
A. Ensure that both SQL1 and SQL2 use the same login name as the security context for each server.
B. Configure SQL2 as a remote server. Write the query on SQL1.
C. Configure SQL2 as a linked server to impersonate the remote login.
D. Configure SQL2 as a distributed server. Use pass-through authentication.
Answer: C
QUESTION NO: 5
TestKing.com uses SQL Server 2005. Users report that report execution is slow. You investigate and discover that some queries do not use optimal execution plans. You also notice that some optimizer statistics are missing and others are out of date.
You need to correct the problem so that reports execute more quickly. Which two Transact-SQL statements should you use? (Each correct answer presents part of the solution. Choose two.)
A. DBCC CHECKTABLE
B. ALTER INDEX REORGANIZE
C. UPDATE STATISTICS
D. CREATE STATISTICS
E. DBCC SHOW_STATISTICS
F. DBCC UPDATEUSAGE
Answer: C,D
QUESTION NO: 6
You are responsible for implementing maintenance jobs on a SQL Server 2005 database server. Certain jobs run every Sunday and other jobs run at the beginning of every month.
You need to schedule the jobs in the way that uses the least amount of administrative effort. What should you do?
A. Create a job schedule that runs every Sunday. Assign weekly tasks to this schedule. Create a second schedule that runs on the first day of every month. Assign monthly tasks to this schedule.
B. Create a job for each task that runs once a day. Use a Transact-SQL statement to check the date and day of the week. If the day is either a Sunday or the first day of the month, execute the code.
C. Create a job schedule that runs once a day. Assign jobs to this job schedule. If the day is either a Sunday or the first day of the month, execute the jobs.
D. Create a job for each task that runs once a week on Sunday. Add a second job schedule that runs the job on the first of the month.
Answer: A
QUESTION NO: 7
You discover that the msdb database on a SQL Server 2005 computer is corrupt and must be restored. Databases are backed up daily. The database backup files are written to a network share, but the file names do not clearly indicate which databases are in each file.
You need to locate the correct backup file as quickly as possible. The first file in the list is named DB_Backup.bak. Which Transact-SQL statement should you use?
A. RESTORE LABELONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
B. RESTORE HEADERONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
C. RESTORE VERIFYONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak
D. RESTORE DATABASE MSDBFROM DISK = N\\Server1\Backup\DB_Backup.bak
Answer: B
QUESTION NO: 8
A support engineer reports that inserting new sales transactions in a SQL Server 2005 database results in an error. You investigate the error. You discover that in one of the databases, a developer has accidentally deleted some data in a table that is critical for transaction processing. The database uses the full recovery model. You need to restore the table.
You need to achieve this goal without affecting the availability of other data in the database. What should you do?
A. Back up the current transaction log. Restore the database with a different name and stop at the point just before the data loss. Copy the table back into the original database.
B. Back up the current transaction log. Restore the database to the point just before the data loss.
C. Restore the database from the existing backup files to a time just before the data loss.
D. Restore the database to the point of the last full backup.
Answer: A
QUESTION NO: 9
A power failure occurs on the storage area network (SAN) where your SQL Server 2005 database server is located.
You need to check the allocation as well as the structural and logical integrity of all databases, including their system catalogs. What should you do?
A. Execute DBCC CHECKFILEGROUP for each filegroup.
B. Execute DBCC CHECKCATALOG.
C. Execute DBCC CHECKDB.
D. Execute DBCC CHECKTABLE for each table.
Answer: C
QUESTION NO: 10
You are responsible for importing data into SQL Server 2005 databases. Your department is starting to receive text files that contain sales transactions from stores across the country. Columns in the data are separated by semicolons.
You need to import the files into the sales database. What should you do?
A. Create a custom format file, specifying a semicolon as the row terminator.
B. Use the bcp command, specifying a semicolon as the field terminator.
C. Use the bcp command with the default arguments.
D. Use the BULK INSERT statement with the default arguments.
Answer: B
Jumat, 26 Desember 2008
MCDBA Certification
Braindumps provide the most comprehensive MCDBA training - through our online virtual MCDBA bootcamps. MCDBA certification has never been easier to study for, and complete with MCDBA notes, you are sure to pass your MCDBA exams. Each complete MCDBA course in our MCDBA bootcamp cover the next MCDBA exam, provide a complete MCDBA practice exam, and ensure your MCDBA test success with an MCDBA study guide.
Combining our detailed MCDBA braindumps with our proved MCDBA boot camps, is certain to provide you with the MCDBA training required for you to pass the MCDBA tests. There is no more complete and comprehensive method for MCDBA course preparation, than a Braindumps MCDBA boot camp.
What is an MCDBA braindump? MCDBA braindumps are an actual inside look at the MCDBA test material, providing you with advanced knowledge through our MCDBA study guide, MCDBA notes, MCDBA practice exams - all to give the best MCDBA tests and MCDBA exam preparation.
Start today with our MCDBA braindump. With so many MCDBA braindumps to choose from, you have a virtual MCDBA boot camp available to you for your best MCDBA exams study. MCDBA bootcamps, and the MCDBA boot camps at Braindumps are specially designed to allow MCDBA certification ease of preparation. Select your MCDBA bootcamp with confidence that your MCDBA training for your MCDBA tests and MCDBA exams are backed by the quality MCDBA notes, MCDBA practice exams, and MCDBA study guides.
Start down the road to MCDBA test success utilizing all of the benefits found on our MCDBA certification and MCDBA course - MCDBA bootcamp. One place, one stop, only one MCDBA braindump.
Langgan:
Entri (Atom)