In the Server Properties window, click Connections in the left navigation pane. Verify that the Remote query timeout setting matches the value that was set using the

sp_configure procedure. You can switch between viewing Configured and Running values by toggling the option buttons at the bottom of the window. See Figure 3-48.

It is important to note that advanced configuration settings can only be modified using the sp_ configure procedure.

3

8. You have now completed Activity 3-5. Close all open windows by clicking the X in the upper-right corner of each application.

Chapter Summary

• The primary purpose of a database is to organize and securely store data while

providing a flexible set of access methods for data retrieval and manipulation by client applications.

• Each SQL Server instance consists of five system databases (master, tempdb, model, resource, and msdb) used by the database management system to store configuration information and perform other functions.

• The basic building blocks of a database are tables and indexes.

Figure 3-48 Validate the new Remote query timeout settings using Object Explorer 

Microsoft SQL Server 2012

• A row is a logical instance, commonly called a record, within a table of the real-world object that is being modeled, and it may contain several attributes that are defined as columns.

• Data types define what type of data a column can store; the main data types include unique identifiers, numbers of varying precision, dates and times, and text strings.

• A database index is a logical database structure used to improve the speed and efficiency of data access by storing a pointer to each record based upon a key. Each table should have a default index that is typically based upon a primary key—the column or combination of columns that uniquely identify each record.

• Other logical components include views (a type of saved query), procedures (scripts that query and manipulate data), and security objects such as users and roles.

• SQL Server 2012 uses messages called Tabular Data Stream (TDS) packets to communicate with client applications.

• When a remote client is conmmunicating with a SQL Server instance, TDS packets are encapsulated within a network protocol packet to enable transmission over a network.

• SQL Server provides direct support for three network protocols: shared memory, Named Pipes, and TCP/IP. Each protocol supports Kerberos, which is a widely used and highly secure network authentication mechanism.

• Shared memory is a local procedure call and requires the client application to be running on the same host as the SQL Server instance.

• Named Pipes is an older interprocess communication mechanism that enables a client ap-plication to connect to a server process on the same or different hosts.

• TCP/IP is the most common protocol used for remote connectivity. The default instance of SQL Server 2012 is configured to listen on TCP port 1433; ports for other named instances are either assigned dynamically when the database engine service starts or configured as static ports. Fixed ports should be used whenever a firewall is present.

• SQL Server Browser is a service that uses SQL Server Resolution Protocol (SSRP) to perform lookup and resolution of network protocol settings for named instances.

• Linked server objects use components called data providers to establish an interface with external sources of data and enable distributed queries to be run.

• SQL Server configuration settings can be managed using the sp_configure procedure, SQL Server Configuration Manager, or the Properties window in Object Explorer. Advanced settings can only be modified using the sp_configure procedure.

Key Terms

binary format A data format in which data is represented as a series of zeros and ones.

CodePlex Microsoft’s open source project hosting site where you can find useful sample code for a wide range of Microsoft products.

column A representation of an attribute of the object being modeled by a table; defined with a unique column name and data type.

constraint A condition that imposes a limitation on a value or action; a column constraint specifies the allowable range of values that a column may contain, such as specific data types.

database index A logical database structure used to improve the speed and efficiency of data access. A database index is constructed based on a unique key, and it contains pointers to the underlying rows within the table.

data file A file that stores database objects and their underlying data; these files are given a .mdf file extension. Data files are subdivided into physical units of storage called pages that are grouped together into extents for space management.

3

data type A column attribute that defines the type of data a column can store; acts as a constraint because it limits the type of values that can be stored. The main data types include unique identifiers, numbers of varying precision, dates and times, and text strings.

extent A physical storage unit that is a collection of eight pages, grouped together for space management.

linked server A server that provides the capability to execute queries against a remote data source for running distributed queries. A linked server uses shared software components (called data providers) from the dynamic-link library (DLL) on the server to manage the interface between SQL Server and the external data source.

listener A service that enables a client to connect to the SQL Server instance over a network.

log file A file that records the series of changes that occur to a database over time, and they enable a database to be restored to a specific point in time.

logical structure A way of organizing data, with defined rules for storing, manipulating, and retrieving the data; client applications interact with logical objects within a database rather than the physical files.

loopback address An IP address that sends outbound packets of data directly back to the host computer to simulate a physical network.

master database A system database that stores the configuration settings that describe the structure and security of all the other databases.

model database A system database that is used as a template when creating a new user database.

Named Pipes An older interprocess communication mechanism that enables a client application to connect to a server process on the same or different hosts. Each SQL Server instance is able to listen on one named pipe.

Object Explorer A component of SQL Server Management Studio that provides a tree view of the different database schema objects organized as a series of folders.

Online Transaction Processing (OLTP) A method of data processing that is geared toward transaction-oriented applications that have a substantial write component in their interaction with the database; an OLTP database is defined by a high volume of individual read and write transactions.

page A physical storage unit in a data file.

primary key The column or combination of columns that uniquely identify each record within a table.

procedure A precompiled block of code that enables frequently performed tasks for querying and/or manipulating data to be stored as objects within the database.

Query Editor A component of SQL Server Management Studio that enables you to create SQL queries to be executed against a database.

row A logical instance of an object, commonly called a record, that is modeled by a table (e.g., an Employee); may contain several attributes that are defined as columns (e.g., first name, last name, Social Security number).

shared memory A local procedure call that requires the client application to be running on the same host as the SQL Server instance. No configuration options are available with a shared memory connection.

sp_configure A procedure that can be used to view and alter global configuration settings.

SQLCMD A utility that enables you to connect to the database from a command prompt and execute SQL commands and scripts.

SQL Server Browser A service that performs instance lookup and name resolution for remote clients.

SQL Server Management Studio A management tool used to configure, manage, and administer the Database Engine Services, Analysis Services, Integration Services, and Reporting Services.

SQL Server Network Interface layer The network protocol layer that manages the handoff of TDS packets between the SQL Server and the operating system–supported network protocol at an endpoint.

SQL Server Resolution Protocol (SSRP) A protocol used by SQL Server Browser for performing instance lookup and name resolution for remote clients.

table A collection of data stored in a database. In a relational database, tables are typically designed to model real-world objects and the relationships that exist between them. Tables are organized as a two-dimensional structure consisting of rows and columns.

table scan A scan of every record within a table, undertaken by a database engine in the absence of a suitable index for a query.

Tabular Data Stream (TDS) packet A message used by SQL Server for communication;

when SQL Server is connected to a remote client, TDS packets must be encapsulated within a standard network protocol packet to enable the data to be sent over the network between the client and server.

TCP/IP The most common protocol used for client/server connectivity.

trace A log of events on the database management system that are captured using the SQL Server Profiler and commonly used for troubleshooting SQL Server performance issues.

trigger A procedural mechanism that can be configured to automatically fire an event when a row is inserted, modified, or deleted; often used to satisfy compliance aspects of the business requirements by cascading details of a change into a related table or autopopulating metadata within a record.

view A type of saved query.

Review Questions

1. Which of the following are objects within a database?

a. Triggers b. Indexes c. Tables

d. All of the above

2. What additional step must be taken to propagate changes made to the supported network protocols in SQL Server Configuration Manager into production?

a. Execute the RECONFIGURE command in Query Editor.

b. Restart IIS.

c. Restart the SQL Server service from SQL Server Configuration Manager.

d. Flush the DNS cache using the IPCONFIG utility.

3. Which shortcut key should be used to access context-sensitive help within Microsoft SQL Server Management Studio?

a. F5 b. F1 c. F12 d. F8

4. Which utilities could you use to activate a database trace?

a. Activity Monitor b. NETSTAT c. SQL Profiler

d. Both options a and c

3

5. For what purpose might a linked server be most useful?

a. Limiting dependency upon a single data center

b. Reducing the security footprint and associated management overhead c. Optimizing query performance over the network

d. Integrating distributed data sources within a single query

6. SQL Server advanced configuration settings can be modified using ____________.

a. the sp_configure procedure

b. SQL Server Configuration Manager

c. Object Explorer within SQL Server Management Studio d. All of the above

7. Which of the following protocols is not directly supported by the SQL Server database engine for client connectivity?

a. Shared Memory b. HTTP

c. Named Pipes d. TCP/IP

8. Which shortcut key should be used to execute a SQL query from a Query Editor window?

a. F5 b. F1 c. F12 d. F8

9. What is the name of the authentication protocol that is supported by the different network protocols used by SQL Server?

a. RADIUS

b. Host Identity Protocol c. Kerberos

d. All of the above

10. What is the most likely impact of a corrupt data file for the master system database?

a. The SQL Server instance may fail to start.

b. Processes may fail as they will be unable to store temporary data.

c. Scheduled jobs will be unable to run.

d. The template used to create new databases will be lost.

11. Why should you consider using an index?

a. To improve speed and efficiency of data access b. To prevent full table scans

c. To improve performance of data updates d. Both options a and b

12. Which of the following applications could you use to execute a SQL query?

a. SQLCMD.exe

b. SQL Configuration Manager

c. Query Editor in SQL Server Management Studio d. Both options a and c

Case Projects