Appendix A -- Questions and Answers | Microsoft SQL Server 7.0 System Administration Training Kit

[Previous] [Next]

Chapter 1 -- Overview of SQL Server

Review Questions

You have an existing application that uses SQL Server and Windows 95 and Windows NT Workstation client computers. Another department that uses a Novell network wants access to the database. Is this possible?

Yes. You would install the appropriate Net-Library for Novell IPX/SPX on SQL Server and on the client computers on the Novell network.

You want to develop a SQL Server application using ADO or OLE DB. What are some of the factors to consider?

ADO is easier to implement and is usually more appropriate for business application development. OLE DB provides more control over application behavior and better performance but is more difficult to implement.

Chapter 2 -- Installation

Review Questions

You are installing several SQL Servers. You want your SQL Server services to connect to network resources with a trusted connection. In what security context should the SQL Server services run? Why?

The SQL Server services must run in the context of a Windows NT domain user account in order to use a trusted connection. The local system account cannot establish a trusted connection with any resources on a remote computer.

You are installing SQL Server in an environment that has both Windows and Novell clients. You want to use Windows NT authentication. Which network libraries must you install?

NWLink IPX/SPX and Multiprotocol.

You installed SQL Server with the default settings. Later, you decide to add a database that requires characters that are not part of the default character set. What must you do to support the new character set?

Your first option is to install a separate SQL Server to support this new character set. The character set in the installed SQL Server is used for all databases on the existing server. You cannot have different character sets for different databases.

Another option is to use Unicode data types for this new database on the existing server to support the characters that are not part of the default character set.

Chapter 3 -- Upgrading to SQL Server 7

Review Questions

You have a SQL Server 6.5 database running on a Windows NT Server. Both SQL Server and Windows NT Server have Service Pack 2 installed. The size of the tempdb database on SQL Server 6.5 is 8 MB. After installing SQL Server 7 on the same computer, there is 100 MB of free disk space. The size of the SQL Server 6.5 database you want to upgrade is 90 MB. What must you do in order to upgrade this database?

First, install SQL Server 6.5 Service Pack 3 or later. Set the size of the SQL Server 6.5 tempdb database to at least 10 MB. Because there is limited disk space available, you should use a tape upgrade to transfer the data from SQL Server 6.5 to SQL Server 7 and delete the SQL Server 6.5 devices when you use the SQL Server Upgrade Wizard. Because the original devices will be deleted during the upgrade, you should back up all of the SQL Server 6.5 databases before starting the upgrade process.

During the upgrade process, the SQL Server Upgrade Wizard cannot upgrade a stored procedure in the SQL Server 6.5 user databases. What could cause this failure?

It is possible that the stored procedure directly modifies a system table or references a system table or a column in a system table that does not exist in SQL Server 7. It is also possible that the object owner is not listed as a user of the database you want to update. SQL Server cannot re-create the stored procedure if the login for the object owner is missing.

You just upgraded a credit card database to SQL Server 7, and you have a client application that contains the following query:

 SELECT t.title AS cross FROM titles t

You want to use the BACKUP and RESTORE commands as part of maintenance jobs that you create. To allow these commands, you set the database compatibility level to 70. What impact does this setting have on your application?

You can issue BACKUP and RESTORE statements because SQL Server 7 supports these commands. However, the query in your application no longer executes correctly, because it uses the word cross as a column alias. The word cross is a reserved word for SQL Server 7. You must rewrite the query before setting the compatibility level to 70.

Chapter 4 -- System Configuration and Architecture

TO verify and edit your SQL Server registration in SQL Server Enterprise Manager

Right-click your server, and then click Edit SQL Server Registration Properties.

What type of authentication is used by default to connect to your SQL Server?

Windows NT authentication.

In the console tree, expand your server to verify that you can connect to your SQL Server.

How can you tell whether your SQL Server is started and whether you are connected to your SQL Server?

The green arrowhead on the SQL Server icon indicates that your SQL Server is started. The vertical red zigzag line indicates that you are connected to your SQL Server.

TO create shared registration information

Expand SQL Server Group.

Are any servers registered? Why or why not?

No servers are registered. When the Store User Independent check box is cleared, the shared registration information is displayed. Until you create shared registration information, there is no shared information.

TO view the SQL Server error log

Scroll through the error log.

What caused all of the entries in this file?

Starting SQL Server caused all of the entries. There are entries for the server starting and for each of the databases being opened and started.

TO view the Windows NT system and application event logs

On the taskbar, click the Start button, point to Programs, point to Administrative Tools, and then click Event Viewer.

Does the system log contain any entries that were generated by the installation or startup of SQL Server?

If the installation of SQL Server was successful, the Windows NT system log contains no entries for SQL Server.

On the Log menu, click Application.

Does the Windows NT application event log contain any entries that were generated by the installation or startup of SQL Server?

The Windows NT application event log contains numerous entries associated with the startup of SQL Server and SQL Server Agent. These entries relate to the entries in the SQL Server and SQL Server Agent error logs.

Review Questions

You want to view metadata about objects in a SQL Server database. What methods would you use?

You could query the information schema views, execute system stored procedures, or use system functions. You also could query the system tables directly, but this is not advised because they may change in later product versions.

What tool can be used to register remote SQL Servers in the Enterprise Manager?

The Register SQL Server Wizard.

Is it possible to have two tables named "Authors" in a database?

Yes; however, a table name is actually a combination of the owner name, the database name, and the table name. As long as the two tables are owned by two different users the names are considered unique. For example, pubs.dbo.Authors and pubs.carl.Authors are two different tables. The object name of each table is Authors, but the full table name of each table is different. While this scenario is possible, it is not a recommended practice because you cannot reference either table by the table name Authors, you would always have to use at least the object name and the owner name, so the tables would have to be referenced as dbo.Authors and carl.Authors in all queries that use them.

Which system stored procedure can be used to retrieve information about a particular database?

Sp_helpdb <database_name>.

Which system table has a row for each database object?

The sysobjects table.

Chapter 5 -- Database Files

Review Questions

You are creating a database that you expect will have a high level of INSERT, UPDATE, and DELETE activity. Should you accept the default transaction log size of 25 percent of the total database size? What factors must you consider if the database is going to be used primarily for queries?

For a heavily updated database, you should either increase the size of the transaction log manually or specify enough free disk space for the log to grow automatically. Databases that are used primarily for queries typically do not require a very substantial transaction log. You might want to reduce the log size to 10 percent of the total database size.

You are creating a database on multiple disks that will be queried extensively by users. What are some steps that you can take to improve performance and avoid disk contention?

You could use disk striping (RAID) to increase performance.

A second strategy would be to use filegroups to improve performance. In particular, place the transaction log files on a separate disk from the data files to avoid disk contention as SQL Server queries the database and records actions in the log. Also, filegroups could be used to separate a portion of the data files that require frequent backups because they are frequently modified.

During a routine monitoring of the data files and transaction log, you notice that the transaction log is extremely close to being full. What would happen if the log filled? What steps can you take to avoid running out of transaction log space?

If the transaction log fills, you are unable to modify the data in the database until the log is archived or expanded. To avoid a full transaction log, monitor the log space regularly, expand the log whenever necessary, set the log to grow automatically, and set an alarm to notify you when the available log space falls below a specified level. Scheduling or performing frequent transaction log dumps is the best way to reclaim available space in the transaction log.

Chapter 6 -- Transferring Data

TO import data using the bcp utility

From a command prompt, execute the C:\Sqladmin\Exercise\Ch06\Runbcp.cmd file. You are prompted for a password. Enter the password for the sa login and press Enter, or just press Enter if your sa password is blank.

How many rows were copied?

1341.

In Notepad, review the output from the C:\Sqladmin\Exercise\Ch06\Newprods.err error file.

Did any errors occur?

Yes. Two errors were intentionally introduced into the Newprods.txt file on rows 26 and 27.

Review Questions

You want to create a DTS package using a basic query. What tool is most appropriate?

Use the DTS Import and DTS Export Wizards for creating simple transformations.

You want to be certain that a DTS package is secure so that no one can copy it or view sensitive information. What can you do to secure this DTS package?

You can assign an owner password to the package when it is created so that no one can view or edit it. Someone can still execute the package. If you want to require a password when the package is executed, add an operator password.

You plan to upgrade the hardware that currently runs SQL Server 7. The new hardware will be faster. Which tool would you select to transfer the database and all of its objects to the new hardware?

You could select the DTS Import Wizard, BACKUP and RESTORE, or copy and attach the database files. Each of these methods allows you to transfer all data and objects to the new hardware. The DTS Import Wizard allows you to perform the transfer in a single operation.

You are required to recommend a solution for an organization that has an existing Oracle database and a new SQL Server database. The applications using the SQL Server database need access to a table on the Oracle server. Which of the following would provide the best solution?

A. Set up SQL Server replication to replicate the table from Oracle to SQL Server.

B. Create a DTS package and schedule it to transfer the contents of the table from Oracle to SQL Server once every hour.

C. Add the Oracle database as a linked server on SQL Server.

D. Install the Oracle and SQL Server client software on every user's computer and access the Oracle table directly from the application.

Option C, adding a linked server, would work well in this scenario. Users do not need to have the Oracle client software installed on their computers, but applications can use Transact-SQL to access the table in the Oracle database directly via the linked server.

Chapter 7 -- Web Publishing and Full-Text Indexing

TO view the generated Transact-SQL script

Open the C:\Temp\Invtrig.sql file and review its contents.

What is the meaning of the parameters @whentype = 10 and @datachg = N'TABLE = Products COLUMN = UnitsInStock'?

These parameters specify that the Web Assistant job and the necessary triggers should be created to update the Web page when data in the Products.UnitsInStock column changes.

TO view the trigger

Examine all triggers on the Products table by selecting the various triggers in the Name drop-down list.

What triggers exist?

There are three triggers named Web_Trigger_x, one each for INSERT, UPDATE, and DELETE. These triggers use the sp_runwebtask system stored procedure to execute the Web Assistant job, named Web_Trigger, when the data in the Products.UnitsInStock column changes.

Review Questions

When the season changes, the supplier sets different prices on certain items. How can you use Web Assistant Wizard to republish the price list in order to reflect these changes?

Create a Web task to regenerate a Web page by using a trigger that determines when the underlying data tables have changed.

Does the Web Publishing Wizard create dynamic Web pages for which users can specify variable parameter values and see real-time data?

No. For creating dynamic Web applications, you must create Web pages that execute code on the Web server to query the database. You can do this with Active Server Pages or the Internet Database Connector running on Microsoft Internet Information Server.

You have created a Web Assistant job and scheduled it to update your HTML Web pages weekly. If you receive a new price list from the marketing department and update the database with the new information, do you have to wait until the Web Assistant job executes on schedule, or can you execute the job immediately to update the Web pages?

You can update the Web pages immediately using SQL Server Enterprise Manager or the sp_runwebtask system stored procedure.

The marketing department at your firm has been entering a large amount of free-text information about customers into the customer database for many months. The marketing manager says that her staff is struggling to create reports based on customer profiles. Can you suggest a way to make it possible to create more effective queries of this information?

Create full-text indexes on the free-text data in the customer database. This will allow full-text queries on words, phrases, and derived words in all of the customer data.

When trying to define full-text indexing on a table using SQL Server Enterprise Manager, you find that all of the full-text indexing menus are grayed (not available). What would cause this?

The Microsoft Search service is not started. Start the service and the menus will become available.

Chapter 8 -- Backup and Restore Overview and Strategy

Review Questions

Your database consists of 5 gigabytes (GB) of data and is stored in one database file. This database is used as an order-taking system for a mail-order catalog company. Operators take orders 24 hours a day. The company typically receives about 2000 orders each day. Describe an appropriate backup plan for this database.

SQL Server backups can occur while the database is online. However, avoid scheduling backups during times of high database activity.

Because the database exists in a single database file, you cannot back up individual parts of the database. You must back up the entire database as a single unit.

Consider a backup plan that includes database and transaction log backups. You may want to add differential backups, which shorten the backup and restore time.

Your database contains image data that is gathered from a weather satellite and is continually being updated. The database is 700 GB. Each table exists in a separate filegroup in the database. If you were to perform a database backup, the process would take about 20 hours. How can you minimize the amount of time that is spent performing backups each day and still ensure good data recoverability in case of a system failure?

Use a backup plan that starts with one database backup. A database backup will be done infrequently. Perform a backup of one of the database files each day on a rotating basis. Perform differential backups in addition to transaction log backups to minimize recovery time.

Chapter 9 -- Backing Up Databases

Review Questions

You have a database for which you generally perform only database backups. The transaction log exists on a separate physical disk from the secondary data files. It is allowed to accumulate changes but is periodically cleared. The disk that contains the secondary data files is damaged. After you replace the disk, what can you do to minimize data loss?

Try to back up the undamaged transaction log by using the NO_TRUNCATE option. This captures some of the activity since the last database backup. After you restore the database, apply the transaction log backup and recover the database.

What are the advantages and disadvantages of using differential backups as part of your backup strategy?

Differential backups save time in the restore process. You can recover a database by restoring the database backup and the latest differential backup only. It is not necessary to apply all of the transaction logs or previous differential backups in order to bring the database to a consistent state.

A disadvantage of differential backups is that because differential backups do not capture intermediate changes to the database, you cannot use them to recover data from a specific point in time. You must use transaction log backups to perform point-in-time recovery.

Chapter 10 -- Restoring Databases

TO simulate damage to the database

Open Windows NT Event Viewer and examine the contents of the Application Log.

You should find an information message stating that there was a device activation error for the C:\Mssql7\Data\Nwcopy_data2.ndf file.

What should you do to restore and recover the nwcopy database?

If possible, back up the transaction log of the damaged database, using the NO_TRUNCATE option to capture the latest activity in the log. Determine which backups are available and usable. Restore the complete database backup. Restore the latest differential backup. (This will include the changes in the first transaction log backup.) And restore the last set of transaction log backups, and recover the database.

TO examine available backups

Click View Contents to examine the contents of the nwc3 device. Notice the type, description, and date and time of the backup set on the device.

What does the nwc3 device contain?

The nwc3 device contains a full database backup. The date and time reflect the fact that it was created before the backups on the nwchange device.

Repeat steps 3, 4, and 5 to examine the contents of the nwchange device. Notice the type, description, and date and time of each backup set on the device.

What does the nwchange device contain?

The nwchange device contains two transaction log backups, as well as a differential backup. The time stamps show that a transaction log backup was created first, followed by a differential backup and then another transaction log backup.

TO review the suggested restore strategy

The Restore Database dialog box appears. Verify that the nwcopy database is selected in the Restore As Database list.

Notice that four backup sets are listed. SQL Server automatically selects the most recent complete database backup and the corresponding differential and/or transaction log backup sets that should be restored to return the database to a consistent state. Three out of four backups are selected (full database, differential, and one transaction log).

Do you agree that the selected backups should be restored?

Yes.

Why is the first transaction log backup not selected?

The changes recorded in this transaction log backup are reflected in the differential backup and therefore do not need to be restored from the transaction log backup.

TO examine the contents of the database

Open a query window, open C:\Sqladmin\Exercise\Ch10\Listcust.sql, review its contents, and then execute it.

This script determines whether the three new customers that were previously added to the Customers table were recovered.

Have all three new customers been recovered?

No, only the rows for Health Food Store and Volcano Coffee Company are in the database. Both of these rows were recorded in the differential backup. The row for The Wine Cellar was recorded in the transaction log backup, taken after the database failure. This backup set was not restored.

Review Questions

What is the automatic recovery process, and when is it initiated?

Automatic recovery occurs whenever SQL Server is restarted. It rolls transactions back or forward to maintain database integrity after a system failure.

What steps should you take before you restore a database?

Set the database to dbo use only. If the transaction log files and the primary data file are available, back up the transaction log so that it can be applied at the completion of the restore operation.

You have a complete database backup and several transaction log backups. Your database is spread among four files. The disk on which the third file resides fails. What should you do to restore and recover the database?

Set the database to dbo use only. If possible, back up the transaction log so that it can be applied at the completion of the restore operation. Replace or fix the disk. Restore the third backup file with the complete backup as the source. Restore all of the transaction log backups, specifying the NORECOVERY option for all but the last one. Restore the last transaction log backup with the RECOVERY option specified.

You have a complete database backup and several transaction log backups. A malicious update to the database occurs at 9:21 a.m. The time is now 9:30 a.m. What should you do to restore and recover the database to a consistent state?

Set the database to dbo use only. Back up the transaction log. Restore the database, specifying the REPLACE and NORECOVERY options. Apply all but the last transaction log with the NORECOVERY option. Apply the last transaction log, specifying RECOVERY, STOPAT = 'month, xx, year, time', where time is 9:20 a.m.

In the scenario presented in question 4, will any changes be lost due to the restore process?

If any activity occurred between 9:20 and 9:21 a.m., these changes are lost.

You have set up a standby SQL Server that functions as a read-only server. What must you do to replace the production server with this standby SQL Server?

If possible, back up the transaction log of the production server without truncating it. Take the production server off line, and change the name of the standby SQL Server to that of the production server. Restore all available transaction logs to the standby SQL Server, and recover the database.

Chapter 11 -- Logins, User Accounts, and User Roles

TO test the logins, users, and roles that you have created and assigned

Start SQL Server Query Analyzer and, by using SQL Server authentication, connect as Carl with the password password.

To what database are you connected, and why?

You are connected to StudyNwind because it was specified as the default database when you created the account.

Execute a query to retrieve data from the Products table. For example,

 SELECT productname FROM Products

Did you receive any results? Why or why not?

You did receive results. Carl is a member of the db_datareader database role. All members of this role are allowed to query data in all tables in the database.

Execute a query to change data in the Products table. For example:

 UPDATE Products   SET productname = 'Tofu, unsalted'   WHERE productname = 'Tofu'

Was the update successful? Why or why not?

The update was not successful. Carl is not a member of a role that has permission to update data in StudyNwind.

Select Connect from the File menu and open a new connection logged on as Cathy. What happens if you perform steps 2 and 3 logged on as Cathy?

Because Cathy is a member of the db_datareader and db_datawriter database roles, she is able to query and update the data. All members of these roles are allowed to query and modify data in the StudyNwind database.

Start SQL Server Query Analyzer and connect with Windows NT authentication.

Notice that you cannot provide a login name or password when you connect to SQL Server with Windows NT authentication and that your user name is displayed in the title bar of the query window.

How did Carl connect to the database when his Windows NT login was not authorized to use SQL Server?

Carl is a member of the STUDYSQL\Customer_mgmt group in Windows NT, which has been authorized to use SQL Server.

Start SQL Server Query Analyzer and connect with Windows NT authentication.

What happens and why?

Max is denied access to SQL Server. He is a valid Windows NT user, but he has not been authorized to use SQL Server and does not belong to a Windows NT group that has been authorized to access SQL Server.

Review Questions

What can users do after they have been authenticated if their logins do not have permissions in any database?

They can connect to the server and query some system tables but cannot gain access to any user databases.

What type of authentication mode would you implement in an environment that contains users who connect from both UNIX and Windows NT? Why?

Mixed Mode because the UNIX connections are not trusted.

Chapter 12 -- Permissions and Security Planning

TO test the statement permissions

Execute the following SQL statements to create a view:

 USE StudyNwind GO CREATE VIEW test_view as SELECT firstname, lastname FROM Employees

Were you able to create the view?

Yes, because permissions have been granted to Cathy to create views.

Execute a CREATE TABLE statement:

 USE StudyNwind CREATE TABLE testtable (column1 INT NOT NULL, column2 CHAR(10) NOT NULL)

Did the statement execute successfully? Why or why not?

The CREATE TABLE statement failed because Cathy does not have statement permissions that allow her to execute it.

TO test the object permissions

Execute each of the following Transact-SQL statements to test permissions for Carl:

 USE StudyNwind SELECT * FROM Customers SELECT * FROM Categories SELECT * FROM Products SELECT * FROM Orders

Which tables can Carl query? Which tables is he not able to query? Why?

He can query all of the tables because he is a member of the db_datareader fixed database role.

Execute each of the following Transact-SQL statements to test permissions for Umberto:

 USE StudyNwind SELECT * FROM Customers SELECT * FROM Categories SELECT * FROM Products SELECT * FROM Orders

Which tables can Umberto query? Which tables is he not able to query? Why?

He can query only the Categories and Products tables. He can query these tables because permissions have been granted to the public role. He cannot query any other tables, because permissions have not been given to the public role, his own account, or any roles or groups to which he belongs (they are in the revoked state).

TO test the object permissions

Execute each of the following Transact-SQL statements to test permissions for Carl:

 USE StudyNwind SELECT * FROM Customers SELECT * FROM Categories SELECT * FROM Products SELECT * FROM Orders

Which tables can Carl query? Which tables is he not able to query? Why?

He can no longer query the Customers table because he is a member of the Cust_Mgmt role, which has been denied permissions, overriding the db_datareader permissions. He can no longer query the Categories table because the public role has been denied permissions, overriding the db_datareader permissions. He can still query the Products and Orders tables because he is a member of the db_datareader fixed database role.

Execute each of the following Transact-SQL statements to test permissions for Umberto:

 USE StudyNwind SELECT * FROM Customers SELECT * FROM Categories SELECT * FROM Products SELECT * FROM Orders

Which tables can Umberto query? Which tables is he not able to query? Why?

He can no longer query the Categories table because permissions have been denied to the public role. He cannot query the Customers or Orders tables because permissions have not been given to the public role, his own account, or any roles or groups to which he belongs (they are in the revoked state). He can query the Products table because permissions have been granted to the public role.

TO test the object permissions

Execute each of the following Transact-SQL statements to test permissions for Carl:

 USE StudyNwind SELECT * FROM Customers SELECT * FROM Categories SELECT * FROM Products SELECT * FROM Orders

Which tables can Carl query? Which tables is he not able to query? Why?

He can again query the Customers table because the deny to the Cust_Mgmt role has been revoked, allowing him to have the db_datareader permissions. He can again query the Categories table because the deny to the public role has been revoked, allowing him to have the db_datareader permissions. He can still query the Products and Orders tables because he is a member of the db_datareader fixed database role.

Execute each of the following Transact-SQL statements to test permissions for Umberto:

 USE StudyNwind SELECT * FROM Customers SELECT * FROM Categories SELECT * FROM Products SELECT * FROM Orders

Which tables can Umberto query? Which tables is he not able to query? Why?

He can no longer query the Products table because permissions have been revoked from the public role and permissions have not been given to the public role, his own account, or any roles or groups to which he belongs (they are in the revoked state). He still cannot query the Categories table. Although permissions are no longer denied to the public role, permissions have not been given to the public role, his own account, or any roles or groups to which he belongs (they are in the revoked state). He still cannot query the Customers or Orders tables because permissions have not been given to the public role, his own account, or any roles or groups to which he belongs (they are in the revoked state).

TO test the permissions on the view and stored procedure

Execute the following statement to query the Employee_View view:

 SELECT * FROM Employee_View

Were you able to query the view? Why or why not?

Yes, you can query the view, even though Umberto does not have permissions on the underlying table. This is because the table and the view have the same owner.

Execute the Employee_Proc stored procedure:

 EXEC Employee_Proc

Were you able to execute the stored procedure? Why or why not?

Yes, you can execute the stored procedure, even though Umberto does not have permissions on the underlying table. This is because the table and the stored procedure have the same owner.

Execute the following statement to query the Employees table:

 SELECT * FROM Employees

Were you able to query the table? Why or why not?

No. Umberto has permissions that allow him to use only the Employee_ View view and the Employee_Proc stored procedure because those permissions have been granted to the public role. SELECT privileges have not been granted to the public role for Employees.

TO activate an application role

Execute SELECT statements to query the Employees and Customers tables.

 SELECT * FROM Employees SELECT * FROM Customers

What permissions does Carl have after the Order_Entry application role is activated?

Carl has only the permissions that are associated with the Order_Entry role. An application role is exclusive; any other permissions that Carl has directly or in other roles of which he is a member will be ignored (except fixed server roles). For example, Carl can query the Employees table through his membership in the db_datareader role, but these permissions are ignored after an application role is activated.

How long will the Order_Entry role be activated for Carl?

The Order_Entry application role remains active until the session (connection) is closed or until the user changes to another database.

Review Questions

When should you assign permissions to a login account directly?

When that login account maps to a Windows NT group that requires common permissions.

When should you avoid using the sa login?

At all times. Use the sa login only for installing SQL Server and for recovery in the event that the members of the sysadmin role are dropped inadvertently.

If permissions to update a table are granted to a user, but the permissions were denied to a role in which the user has membership, does the security account retain permissions to update the table?

No. Denial of permissions to the role supersedes granting of permissions to the security account.

Chapter 13 -- Automating Administrative Tasks

TO execute the job manually

In the details pane, right-click the StudyNwind Log Backup job, and then click View Job History to verify that the job completed successfully.

What information is displayed in the job history when Show Step Details is selected? What information is displayed when this option is cleared?

Show step details provides information (error messages and notifications) on each job step and the outcome. When the option is not checked, only the outcome is displayed.

TO simulate a failure and verify that a job step failed

In the details pane, right-click the StudyNwind Monthly Data Transfer job, and then click View Job History to verify that the job did not complete successfully. The job will appear in the history only after the retry, but the initial failure will be seen immediately if the Show Step Details option is checked.

What do you notice in the history?

After the first step failed, it executed again according to the retry interval. The error message from step 1 is "Unable to open BCP host data-file."

Open the Windows NT application log to confirm that the job failure was logged.

What information is displayed in the log?

SQL Server Agent is the event source. The event category is Job Engine. The description includes the name of the job, who invoked the job, the job failure message, and the step number of the last step to run.

Open Notepad, and then open C:\Temp\Prodcopy.txt.

What information is displayed in the output file?

The command-prompt output, including the SQLState error number, native error number, and error message "Unable to open BCP host data-file."

TO start the SQL Mail session

Right-click SQL Mail, and then click Start.

Did a SQL Mail session start successfully?

No. An error was received indicating that it failed to start.

Review the messages in the current log in SQL Server Logs in the Management folder in the console tree.

What was the source of the error messages associated with SQL Mail?

ODS reported a MAPI login failure.

Right-click SQL Mail, and then click Start.

Did the SQL Mail session start successfully?

Yes.

TO send a query result using SQL Mail

Switch to Inbox and open the new message.

What was the query result?

Product Total of 77.

TO create an alert by using the Create Alert Wizard

Right-click the alert, and then click Properties to review the alert definition.

What additional responses to the alert can be defined that were not included as part of the wizard?

Execute a job, notify additional operators, specify additional notification message to send to operator, and delay between responses.

TO raise a user-defined error message

Execute the reorder stored procedure by supplying any valid product ID value. For example,

 USE StudyNwind EXEC reorder @prodid = 2

Did you receive the defined notifications in response to the alert?

Yes. A net send command message and e-mail message were received.

TO correct a problem and verify that a job works

Open Notepad, and then open C:\Temp\Prodcopy.txt.

What information is displayed in the output file?

The command prompt output from the successful BCP, stating that five rows were copied.

Review Questions

You want to back up the transaction log of your production database every hour during peak business hours (8:00 a.m. to 6:00 p.m.) and every three hours during non-peak hours (6:00 p.m. to 8:00 a.m.). What is the most efficient method for automating these tasks?

Create one job to back up the transaction log, and specify two schedules.

The customer account manager has asked to be notified whenever a customer's credit limit is changed (increased or decreased). In addition, she wants the name of the customer representative who updated the customer's account, as well as any remarks about why the change was made. How would you accomplish this task?

The first step is to create a custom error message that specifies the customer account name, credit limit, remarks (assuming that the column already exists in the database), and the name of the customer representative who made the update.

The next step is to modify the stored procedure or trigger that changes customer credit limits to raise the custom error with the RAISERROR statement.

Then you would create the customer account manager as an operator.

Finally, you would create an alert on the custom error message that sends an e-mail message to the customer account manager when the alert is fired.

Your new database application is now in production, and you want to execute tests to review its performance. Specifically, you want to know whether the lock wait time is over 20 seconds. How can you be notified automatically when this event occurs?

Create a performance condition alert on the Lock Wait Time (ms) counter of the SQLServer:Locks object.

Chapter 14 -- Monitoring and Maintaining SQL Server

TO simulate server activity

Switch to the SQL Server Performance Monitor window.

Watch the Chart window while the monitoring batch files execute. What trends do you notice?

Observations will vary. The cache-hit ratio may rise almost immediately and remain high. Index searches and full scans are taking place. The number of batch requests will rise and then stabilize. The Percent Log Used and Total Transactions counters will rise.

TO add counters to the chart

Watch the Chart window while the monitoring batch files continue to execute. (Press Ctrl-H to highlight the currently selected counter.)

What trends do you notice?

Observations will vary. The Memory/Faults/sec counter remains high, while the Memory/Pages/sec counter remains low. Windows NT is paging, but much of the data is already in main memory (on a standby list) and does not need to be brought in from disk. The Process/Faults/sec/SQL Server counter is low, indicating that SQL Server is performing some paging. The Process/%Processor Time/SQL Server counter indicates that SQL Server is using only a portion of the total processor time.

TO detect login attempts

Expand the node labeled Duration = 0.

What information is recorded?

You should see the failed login attempts for Maria and sa.

Switch to the SQL Server Profiler window, and expand all nodes on the tree.

What information is recorded?

You should see all of the events associated with making a connection from SQL Server Query Analyzer. Notice that SQL Server Query Analyzer submits several queries as part of the startup process. It uses the information that it requests to establish the session and populate the user interface. For example, the query "select name from master..sysdatabases order by name" is used to populate the DB drop-down box in SQL Server Query Analyzer.

TO detect long-running queries

Expand Duration for each event, and examine the events with long run times.

What events are included?

You should see disconnect events, as well as the execution of several queries. The queries include system stored procedures (sp_help), as well as some complex queries that use tables from the nwcopy database.

Expand SQL:BatchCompleted to see the text of the query.

What is the query text?

Answers may vary; however, the following query will be among the worst performers:

 SELECT e.lastname, p.productname, avg(d.quantity * d.unitprice) FROM employees e JOIN orders o ON e.employeeID = o.employeeid JOIN [order details] d ON o.orderid = d.orderid JOIN products p ON d.productid = p.productid GROUP BY e.lastname, p.productname

TO execute a query and generate an execution plan and statistics

Click the Execution Plan tab to display the graphical execution plan for this query. Note that statistics about each step are given if you move your mouse pointer over the step icons. The Table Scan steps may show a message stating that statistics are missing for the table. This is correct because the tables in nwcopy do not have indexes or statistics created for them.

Were table scans used to process this query? What can be done to improve performance?

This query requires several table scans in order to join the Products, Order Details, Employees, and Orders tables. Creating appropriate indexes will likely improve the performance of this query.

Execute the following queries:

 EXEC sp_helpindex Employees EXEC sp_helpindex Orders EXEC sp_helpindex [Order Details] EXEC sp_helpindex Products

What indexes exist on the Employees, Orders, Order Details, and Products tables?

None; the nwcopy database has no indexes.

TO hold locks on the server

Switch to connection 1, execute the sp_lock system stored procedure, and then review the lock information.

Using the spid recorded in step 3, identify what locks were granted to the transaction that was issued by connection 2.

Locks that are held should include the following: a shared database lock (Type _ DB, Mode _ S), an intent page lock (Type - PAG, Mode - IX), and an exclusive row lock (Type - RID, Mode - X).

TO detect lock blocking

Open and execute C:\Sqladmin\Exercise\Ch14\Lock.sql, using connection 3.

Does the query complete?

No, the query is waiting.

Switch to connection 1 and execute the sp_lock system stored procedure.

Is the spid for connection 3 waiting for any resources? (Look for WAIT in the Status column.)

Yes, it is waiting for a lock to be granted on the row to be updated.

Switch to SQL Server Enterprise Manager, refresh Current Activity, and review the locking information. In particular, note the information under Locks / Process ID.

Why can't connection 3 complete the query?

The row to be updated is exclusively locked by connection 2. Both the sp_lock procedure and the Current Activity window show that the spid for connection 3 is waiting for the row lock to be released.

Review Questions

Users complain that the server slows down considerably every day at 2 p.m. How can you find out what is causing this delay?

First, determine whether the problem is due to a load on the system as a whole or on SQL Server alone. To do this, use SQL Server Performance Monitor to compare the resource use of Windows NT with that of SQL Server.

If the problem is due to SQL Server activity, create a trace by using SQL Server Profiler to record the activity that occurs around 2 p.m. every day. This trace should look at events that might increase the load on the server, such as user connections, Transact-SQL statements, stored procedures, or use of the tempdb database. Consider grouping this information by duration and by application or user.

With this information, you can determine which events are placing the greatest load on the system.

You want to find out the locks that are being held on a specific SQL Server table. What tools would you use?

On a table level, you can use SQL Server Profiler, the sp_lock and sp_who system stored procedures, and Current Activity in SQL Server Enterprise Manager.

You want to see a query plan and the statistics for a specific query in SQL Server Query Analyzer. What steps must you take?

First, generate the estimated execution plan for the query. This does not actually execute the query. Statistical information cannot be generated unless the query is executed. You then must execute the query with statistics turned on. You can show the execution plan when you execute the query to see the actual execution plan.

Chapter 15 -- Introducing Replication

Review Questions

Your company has been experiencing contention problems. This occurs when the sales and marketing departments run their daily reports while the order-processing department is processing orders. Your company has decided to implement some form of data replication. To choose the appropriate method for making copies of the company data, which features and characteristics should you consider?

At a minimum, you should consider transactional consistency, latency, site autonomy, security, existing data sources, data update frequency, performance, administration, and whether to allow updating Subscribers.

Your company, a large, international manufacturer with many vendors around the world, has decided to distribute data by using replication. You currently are in the planning and design phase of your replication strategy. Your goal is to receive all of the sales information from the vendors at the company headquarters on a daily basis. Because most of your vendors are remote, you are concerned about the high long-distance costs. Which replication model or models would you implement? Why?

The best way to address these requirements would be to combine two of the replication models. You could have a Central Subscriber in each region that would collect all of the daily sales information for the region. The regional Central Subscribers could then publish the data to company headquarters at night. By using a single Central Subscriber/Publisher in each region, you minimize the long-distance communication costs.

Chapter 16 -- Planning and Setting Up Replication

TO create a subscription

After the subscription has been created, in the console tree, expand your server, expand Management, expand SQL Server Agent, and then click Jobs.

The job called SQLSERVER-StudyNwind-StudyNwind_Products_Pub-SQLSERVER-PullSubs- 0, with a category of REPL-Distribution, is the job that starts the Distribution Agent for your new subscription.

Which server's system resources will this job consume? Would a push subscription change the resources being used?

This job will consume the system resources of the Subscriber. If a push subscription were used, the system resources of the Distributor would be used. For the purposes of this exercise, the Subscriber and the Distributor are on the same computer, but normally they would be on different computers.

TO create a push subscription to a publication

In the details pane, after the Status column indicates that the Merge Agent has succeeded, right-click StudyNwind_Merge_Publication, and then click Agent History. In the history list, select the oldest entry (this is the entry for the subscription initialization and should have about 38 actions). If there are other entries, they represent the Merge Agent running every 2 minutes according to the schedule you created for the subscription. Click Session Details. Review the history of the Merge Agent.

Whose system resources does this Merge Agent consume? Would a pull subscription change the resources that are used?

This Merge Agent consumes the system resources of the Distributor. A pull subscription would use the system resources of the Subscriber. For the purposes of this exercise, the Subscriber and the Distributor are on the same computer, but normally they would be on different computers.

TO update the source table on the Publisher

Type and execute the following script:

 USE StudyNwind UPDATE Customers SET ContactName = 'Maria Anders-Smith' WHERE CustomerID = 'ALFKI' SELECT * FROM Customers

How long will it take for this update to be replicated?

It will take up to two minutes for these updates to be replicated, based on the schedule that was set in the previous exercise.

TO update simultaneously on a Publisher and a Subscriber

Switch to SQL Server Query Analyzer and execute the following query in each of the query windows:

 SELECT * FROM Customers

Which update was applied? Was it the update made on the Publisher server (in the StudyNwind database) or on the Subscriber server (in the nwrepl database)?

The update made on the Publisher server was applied. Updates made at the Publisher will always "win" over conflicting updates made at Subscribers. Which Subscriber will win when conflicting updates are made at different Subscribers depends on Subscriber priorities or custom resolvers.

To resolve the conflict

Switch to SQL Server Query Analyzer and execute the following query in both of the query windows:

 SELECT * FROM Customers

Was the revised data applied to both servers?

Yes.

Review Questions

You are trying to configure replication between two servers. Both servers appear to be set up correctly, but replication does not work. What do you think is the problem? What should you do to fix it?

First make sure that the network between the two servers is running. Then check the account that SQL Server is using for replication (the default is SQL Server Agent). Make sure that this account has access to the other server. Finally, check that the schedule for the various agents is correct, and possibly start the initial synchronization manually.

When you set up the distribution server, what should you consider when estimating the appropriate size of the distribution database?

The number of Publishers, publications, and Subscribers; the amount of data that will be changing; the frequency of data changes; the type of replication that is used; the latency; and whether Subscribers are anonymous.

Your company has decided to use SQL Server replication. The publication server runs an intensive application and does not have the capacity to manage any replication overhead. You will have a large number of Subscribers. Some Subscribers may be offline periodically, and all Subscribers must have the ability to update data. Which model and method of replication would best address these requirements? Why?

The best model and method would be a single Publisher using a remote Distributor (on a separate computer) that allows read-only Subscribers and Subscribers with the Immediate Updating Subscribers option. For offline Subscribers, you should allow read-only Subscribers or merge replication. For online Subscribers, you would enable the Immediate Updating Subscribers option. Using this option will add some extra processing on the Publisher, however.

Chapter 17 -- Managing Replication

TO review the properties of the Miscellaneous Agents

Right-click the agent, and then click Agent Properties.

What is the function of this agent and how often does it run?

Agent	Function	Schedule
Agent history clean up: distribution	Removes replication agent history from the distribution database	Every 10 minutes
Distribution clean up: distribution	Removes replicated transactions from the distribution database	Every 10 minutes
Expired subscription cleanup	Detects and removes expired subscriptions from published databases	Daily at 1:00 a.m.
Reinitialize subscriptions having data validation failures	Reinitializes all subscriptions that have data validation failures	On demand, or in response to an alert
Replication agents checkup	Detects replication agents that are not actively logging history	Every 10 minutes

Review Questions

How can you determine the number of transactions that are marked for replication in the transaction log, waiting to be read by the Distribution Agent?

Use SQL Server Performance Monitor and view the Repl. Pending Xacts counter of the SQLServer:Databases object.

What would you check first if all of your publications on a server stopped working?

Check the SQLServerAgent service to make sure that it is running and configured properly. Also check the distribution database and the SQL Server Agent logs.

You are finished setting up replication. You make changes to data in a publication, but the changes are not replicating to the Subscriber. How would you determine which replication agent is failing?

View the agent histories and check each agent to determine whether it was successful.