Datasheet

ManualsBrandsWiley ManualsSoftware978-0-470-24796-9

Knight c01.tex V2 - 09/30/2008 6:52am Page 1

SQL Server 2008

Architecture

The days of SQL Server being merely a departmental database are long gone. SQL Server can now

easily scale to databases dozens of terabytes in size. (For details see the results of the Winter survey

www.microsoft.com/sql/prodinfo/compare/wintercorp-survey.mspx

.) In this chapter, we lay

some of the groundwork that will be used throughout the book. We ﬁrst discuss how the role of the

database administrator (DBA) has changed since some o f the earlier releases o f SQL Server, a nd then

quickly jump into architecture and tools available t o you as an administrator. This chapter is not a

deep dive into the architecture but it provides enough information to give you an understanding of

how SQL Server operates.

The Expanding Role of a DBA

The role of the database administrator has been changing slowly over the past few versions of the

SQL Server product. Beginning with SQL Server 2005, this slow transition of the DBA role has

been accelerated immensely. Traditionally, a DBA would ﬁt into one of two roles: development or

administration. It’s much tougher to draw a line now between DBA roles in SQL Server 2008. In

addition, the new role of Business Intelligence DBA is on the rise. As lines blur and morph, DBAs

have to quickly prepare themselves to take on different roles. If you don’t position yourself to be

more versatile, you may be destined for a career of watching SQL Server alerts and

backups.

Production DBA

Production DBAs fall into the traditional role of a DBA. They are a company’s insurance policy

that the production database won’t go down. If the database does go down, the company cashes

COPYRIGHTED MATERIAL

Summary of content (34 pages)

PAGE 1
V2 - 09/30/2008 AL Knight c01.tex MA TE RI SQL Ser ver 2008 Architecture IG HT ED The days of SQL Server being merely a departmental database are long gone. SQL Server can now easily scale to databases dozens of terabytes in size. (For details see the results of the Winter survey at www.microsoft.com/sql/prodinfo/compare/wintercorp-survey.mspx.) In this chapter, we lay some of the groundwork that will be used throughout the book.
PAGE 2
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture in its insurance policy in exchange for a recovered database. The Production DBA also ensures that the server is performing optimally, and he or she promotes database changes from development to quality assurance (QA) to production. Other tasks performed by a Production DBA include the following: ❑ Install SQL Server instances and service packs. ❑ Monitor performance problems. ❑ Install scripts from development.
PAGE 3
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture never have modification access to a production database. They should, however, have read-only access to the production database to debug in a time of escalation. Business Intelligence DBA The Business Intelligence (BI) DBA is a new role that has evolved due to the increased capabilities of SQL Server. In SQL Server 2005, BI grew to be an incredibly important feature set that many businesses could not live without.
PAGE 4
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture Ideally, for regulatory reasons and for stability, the DBA’s backup DBA should install the change into production. That way, you can ensure that the DBA who installed the script didn’t make ad hoc changes in order to make the change work. We cover much more about this change-management process in Chapter 10. The only role of a Hybrid DBA that’s questionable is development of stored procedures.
PAGE 5
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture SQL Ser ver Architecture In older editions of SQL Server, you had to use many different tools depending on the function you were trying to perform. In SQL Server 2008, the challenge for Microsoft was to avoid increasing the number of management tools while increasing the features and products that ship with SQL Server.
PAGE 6
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture 8KB data pages. You can specify how full each data page should be with the fill factor option of the create/alter index T-SQL command. (We go much more into this in Chapter 14.) In SQL Server 2008, you have the capability to bring your database partially online if a single file is corrupt.
PAGE 7
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture System Databases The system databases in SQL Server are crucial, and you should leave them alone most of the time. The only exception to that rule is the model database, which allows you to deploy a change such as a stored procedure to any new database created. If a system database is tampered with or corrupted, you run the risk that SQL Server will not start.
PAGE 8
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture in master by running the following query, which returns information about the databases that exist on the server: SELECT * FROM sys.databases The main difference between the Resource and master databases is that the master database holds data specific to your instance, whereas the Resource database just holds the schema and stored procedures needed to run your instance, but does not contain any data specific to your instance.
PAGE 9
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture Schemas Schemas enable you to group database objects together. You may wish to do this for ease of administration, as you can apply security to all objects within a schema. Another reason to use schemas is to organize objects so the consumers may find the objects they need easily. For example, you may create a schema called HumanResource and place all your employee tables and stored procedures into it.
PAGE 10
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture Dynamic Management Views Dynamic management views (DMVs) and functions return information about your SQL Server instance and the operating system. DMVs simplify access to data and expose new information that was not available in versions of SQL Server prior to 2005. DMVs can provide you with various types of information, from data about the I/O subsystem and RAM to information about Service Broker.
PAGE 11
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture When you convert to a data type that may be incompatible with your data, you may lose important data. For example, if you convert from a numeric data type that has data such as 15.415 to an integer, the number 15.415 would be rounded to a whole number. You may wish to write a report against your SQL Server tables which displays the data type of each column inside the table.
PAGE 12
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture Unicode consumes 2 bytes per character. If you were to store the value of ‘‘Brian’’ in an nvarchar column, it would use 10 bytes, and storing it as an nchar(20) would use 40 bytes. Because of this overhead and added space, do not use Unicode columns unless you have a business or language need for them. Next are text and ntext. The text data type stores very large character data on and off the data page.
PAGE 13
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture Data Type Description bigint Whole numbers from –9,223,372,036,854,775,808 to Storage Space 9,223,372,036,854,775,807 8 bytes Numbers from –1,038 +1 through 1,038 –1 Up to 17 bytes money –922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes smallmoney –214,748.3648 to 214,748.
PAGE 14
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture varbinary(max), which can hold more than 8KB of binary data and generally performs slightly better than an image data type. New in SQL Server 2008 is the capability to store varbinary(max) objects in operating system files via FileStream storage options. This option stores the data as files, and is not subject to the 2GB size limit of varbinary(max).
PAGE 15
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture Data Type Description Storage Space Date January 1, 1 to December 31, 9999 3 bytes Datetime January 1, 1753 to December 31, 9999, Accurate to nearest 3.
PAGE 16
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture Data Type Description Storage Space Timestamp or Rowversion Unique per table, automatically stored value. 8 bytes Generally used for version stamping, the value is automatically changed on insert and with each update. Uniqueidentifier 16 bytes Can contain Globally Unique Identifier (GUID). guid values may be obtained from the Newid()function. This function returns values which are unique across all computers.
PAGE 17
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture Compact (32-bit Only) SQL Compact is a free edition which is intended to be an embedded database for mobile and other compact devices with occasionally connected users. SQL Express (32-bit Only) SQL Express is the free version of SQL Server meant for installation on laptops or desktops to support distributed applications such as a remote sales force application.
PAGE 18
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture The Evaluation Edition of SQL Server is a variant of SQL Server Enterprise Edition that expires after 180 days. After the allotted evaluation period, SQL Server will no longer start. This edition has the same features as the Enterprise Edition and may be upgraded for production use. It is not licensed for production use. The Developer Edition of SQL Server is intended for development and testing of applications using SQL Server.
PAGE 19
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture the edition of SQL Server you purchase. In some cases, your scalability is restricted only to the operating system’s maximum memory or number of processors. This is where 64-bit becomes really useful. (We cover 64-bit scalability in much more detail in Chapter 15.
PAGE 20
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture High Availability Keeping your data online and ready to use is of primary importance to most facilities. These are the functions and features associated with high availability.
PAGE 21
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture Security As more data governance, auditability, and accountability is imposed, security features become more important. SQL Server 2008 included auditing, and new encryption capabilities which help meet those requirements.
PAGE 22
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture Manageability While SQL Server databases have historically been easy to manage, Microsoft is adding improvements in this area to allow DBAs to easily manage larger groups of servers. Particularly interesting and important in this release are the policy-based management features.
PAGE 23
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture Feature Express Advanced Express Database mail Database migration tools ✓ ✓ Web Workgroup Standard Enterprise ✓ ✓ ✓ ✓ ✓ ✓ ✓ ✓ Management Tools These are the management tools which come with each edition of SQL Server 2008. SQL Express Advanced now includes SQL Server Management Studio.
PAGE 24
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture Feature Express Advanced Express Web ✓ ✓ ✓ Workgroup Standard Enterprise ✓ ✓ ✓ SQL query, edit, and design tools ✓ ✓ ✓ Intellisense(TransactSQL and MDX) ✓ ✓ ✓ Version control support ✓ ✓ ✓ Business Intelligence Development Studio ✓ ✓ MDX edit, debug and design tools ✓ ✓ Visual Studio Integration Programmability While notification services goes away in this release, service broker remains.
PAGE 25
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture Feature Express Advanced Specification of language in query ✓ Service broker(messaging) Client only Express Web Workgroup Standard Enterprise ✓ ✓ ✓ ✓ Client only ✓ ✓ ✓ XML/A support ✓ ✓ Web services (HTTP/SOAP endpoints) ✓ ✓ Client only Spatial and Location Services SQL Server 2008 has added geospatial libraries and data types, included with all editions.
PAGE 26
Knight c01.
PAGE 27
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture Data Warehouse Creation New designers and auto-generation of staging schemas, new to 2008 are included in the Standard and Enterprise edition.
PAGE 28
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture Multi-Dimensional Analytics Special aggregations and intelligence, and semi-additive measures are available. General performance improvements are included everywhere SSAS is supported.
PAGE 29
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture Data Mining Serious data-mining efforts will require the Enterprise edition of SQL Server.
PAGE 30
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture Reporting Reporting Services (SSRS), supported in many environments is one of the most popular SQL Server features. Particularly helpful in SQL Server 2008 is the ability to run SSRS Service outside of IIS.
PAGE 31
Knight c01.
PAGE 32
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture a license fee for each device (client computer) that accesses the SQL Server functionality. This model is good when you have a small number of clients that access SQL Server, inside the firewall, and when there are many users accessing the same device. An example of multiple users on a device might be a kiosk in a shopping center, or a call center that runs around the clock.
PAGE 33
Knight c01.tex V2 - 09/30/2008 6:52am Chapter 1: SQL Server 2008 Architecture Enterprise license exists for the physical server, there are no additional licensing requirements for other instances running within virtual environments on this physical server. To recap, an Enterprise Edition requires a single license for each physical server, and includes all virtual servers. The Standard and Workgroup edition require a separate license for each virtual server.
PAGE 34
Knight c01.tex V2 - 09/30/2008 Chapter 1: SQL Server 2008 Architecture Summar y In this chapter, we covered the basic architecture for SQL Server, including how it stores its data and how it communicates. We also addressed the various data types and when to use one type rather than another. Last, we answered some of the many dreaded SQL Server edition and licensing questions that we often hear from users.