- ptg SQL Server high availability provide some expert advice in these areas. - ing and notification using SQL Server Agent and using the new Policy Based Management feature are also discussed in this part.. - Part IV, “SQL Server Database Administration”—This part delves into the admin- istrative tasks associated with creating and managing a SQL Server 2008 database, including the creation and management of database objects, such as tables, indexes, views, stored procedures, functions, and triggers. - It also provides coverage of the Database Snapshots and an overview of database maintenance tasks and responsibil- ities.. - Part V, “SQL Server Performance and Optimization”—This part provides informa- tion to help you get the best performance out of SQL Server. - then it finishes with a methodology for monitoring and optimizing SQL Server performance.. - Part VI, “SQL Server Application Development”—This part includes a comprehen- sive overview of what’s new in T-SQL in SQL Server 2008 and SQL Server 2008 R, T- SQL programming guidelines, tips, and tricks, and advanced stored procedure programming and optimization. - In addition, chapters in this Part provide an overview of .NET integration with SQL Server and creating .NET CLR objects infor- mation, working with XML in SQL Server, and working with additional SQL Server components that are not part of the core database engine such as Web Services, Service Broker, and Full-Text Search.. - Part VII, “SQL Server Business Intelligence Features”—This Part includes a comprehensive overview of SQL Server 2008 R2’s built-in business intelligence features: Analysis Services, Integration Services, and Reporting Services, with a specific focus on the enhancements to Reporting Services introduced with the R2 release.. - Bonus Chapters on the CD—This Part provides a few chapters for which there just wasn’t room enough to include elsewhere in the book. - These chapters provide expert advice and information on managing remote and linked servers, configuring, tuning and optimizing SQL Server, and planning for SQL Server Disaster Recoverys.. - Book Materials on the CD—Also included on the CD are many of the code sam- ples, scripts, databases, and other materials that supplement various chapters. - This has always been one of the most valuable reasons to buy books in the Unleashed series. - You must choose at least one of the enclosed options.. - Parentheses are part of the command.. - You can select only one of the options listed.. - You can select any of the options listed.. - However, because the default installation of SQL Server doesn’t make a distinction between upper- and lowercase for SQL keywords or object names and data, you might find some of the examples presented in either upper- or lowercase.. - in the square brackets following user_or_group_name indicates that multiple user or group names can be specified, separated by commas. - Some of the examples presented in this book make use of the AdventureWorks2008 and AdventureWorks2008R2 databases, which are not automatically installed with SQL Server 2008 or SQL Server 2008 R2. - To install the AdventureWorks sample databases, you must first download the installer from the Microsoft SQL Server Samples and Community Projects website at http://www.codeplex.com/sqlserversamples.. - Most of the examples presented in this book that use the AdventureWorks database can be run in either AdventureWorks2008 or AdventureWorks2008R2. - AdventureWorks2008R2 have different data values for some of the columns:. - If any of the examples use any of these tables, you may see different results depend- ing on whether you run them in AdventureWorks2008 or AdventureWorks2008R2.. - When necessary, it will be stated in the chapter which version of the AdventureWorks database was used to generate the results displayed.. - Although it is not necessary to install both versions of the AdventureWorks database, it is possible to install both versions in the same SQL Server instance if you wish.. - For many of the examples presented in Part V, larger tables than what are available in the AdventureWorks database were needed to demonstrate many of the concepts with more meaningful examples. - For many of the chapters in this part, as well as some other chap- ters throughout the book, the examples come from the bigpubs2008 database. - A copy of the database, along with an Entity-Relationship (ER) diagram and table descriptions, is also on the CD.. - Copy the bigpubs2008.mdf file into the SQL Server data folder where you want it to reside.. - After copying the file to the destination folder, ensure that the Read-Only property of the bigpubs2008.mdf file is not enabled (this can happen when the file is copied from the CD). - Alternatively, you can attach the database by using SQL Server Management Studio. - To do this, right-click the Databases node in the Object Explorer and select Attach. - In the bottom window pane, click the transaction log file entry (it should say Not Found in the message column) and click the Remove button. - Next, click the OK button to attach the database. - A new transaction log file is automatically created in the same folder as the bigpubs2008.mdf file. - In addition to the bigpubs2008 database, the .mdf file for the database used for examples in Chapter 51, “SQL Server Analysis Services,” is also provided. - Copy the CompSales.mdf file into the SQL Server data folder where you want it to reside.. - Ensure that the Read-Only property of the CompSales.mdf file is not enabled.. - Attach the CompSales database by using a command similar to the following (edit the path to match the location of the CompSales.mdf file on your system):. - If you have purchased this book, you are on your way to getting the most from SQL Server 2008 or SQL Server 2008 R2. - Many of us who worked on this book have been using SQL Server since it was first released. - Writing about each new version challenges us to reassess our understanding of SQL Server and the way it works. - SQL Server 2008 Overview. - SQL Server Components and Features. - SQL Server 2008 R2 Editions . - SQL Server Licensing Models. - E xactly what is SQL Server 2008? When you first install the product, what are all the pieces you get, what do they do, and which of them do you need?. - At its core, SQL Server 2008 is an enterprise-class database management system (DBMS) that is capable of running anything from a personal database only a few megabytes in size on a handheld Windows Mobile device up to a multi- server database system managing terabytes of information.. - However, SQL Server 2008 is much more than just a data- base engine.. - The SQL Server product is made up of a number of differ- ent components. - This chapter describes each of the pieces that make up the SQL Server product and what role each plays. - Each of these topics is dealt with in more detail later in the book. - In addition, this chapter looks at the environ- ments that support SQL Server 2008 and SQL Server 2008 R2 and the features available in each of the various SQL Server editions.. - The main component of SQL Server 2008 is the Database Engine. - Before you can use the other components and features of SQL Server 2008, which are discussed in the following sections, you need to have an instance of the Database Engine installed.. - The SQL Server Database Engine. - The Database Engine is the core application service in the SQL Server package for storing, processing, and securing data with SQL Server 2008. - The SQL Server 2008 Database Engine is a Windows service that can be used to store and process data in a relational format, as XML documents, and new for 2008, as spatial data. - The following are the main responsi- bilities of the Database Engine:. - For now, this chapter provides just a brief overview on each of these points to show how Microsoft SQL Server fulfills these core responsibilities.. - This isn’t the responsibility of the Database Engine, but it’s a necessary part of a well-built database. - For more information on the reliability characteristics and performance implications of the various RAID configurations and guidelines for implementing RAID configurations with SQL Server, see Chapter 38, “Database Design and Performance.”. - Using whatever hardware you have decided to make available, the Database Engine manages all the data structures necessary to ensure reliable storage of your data. - Eight pages make up an extent, and the Database Engine keeps track of which extents are allocated to which tables and indexes.. - A page is an 8KB chunk of a data file, the smallest unit of storage available in the database. - Another key feature the Database Engine provides to ensure reliable storage is the transac-. - It is not strictly true that the transaction log records all changes to the database. - SQL Server allows the creation of indexes, enabling fast access to data. - Excess memory for a SQL Server instance is used as a data cache. - When pages are requested from the database, the SQL Server Database Engine checks to see if the. - requested pages are already in the cache. - If they are not, it reads them off the disk and stores them in the data cache. - If there is no space available in the data cache, the least recently accessed pages (that is, those that haven’t been accessed in a while since they were read into memory) are flushed out of the data cache to make room for the newly requested pages. - With sufficient memory, an entire database can fit completely into memory, providing the best possible I/O performance for the database.. - SQL Server follows a set of rules to ensure that the data you receive from queries is consistent.. - The general idea with consistent data access is to allow only one client at a time to change the data and to prevent others from reading data from the database while it is undergoing changes. - Data and transactional consistency are maintained in SQL Server by using trans- actional locking.. - Transactional consistency has several levels of conformance, each of which provides a trade-off between accuracy of the data and concurrency. - SQL Server controls access by providing security at multiple levels. - ptg using a SQL Server username and password or through integrated network security, which. - SQL Server security is examined in greater detail in Chapter 11, “Security and User Administration.”. - A corporate database that contains valuable information might have a dozen different departments wanting to access portions of the database for different needs.. - The best way to keep everybody sane in this environment—and to ensure that the data stays consistent and usable by everyone—is to enforce a set of data integrity rules within the database itself. - SQL Server 2008 Administration and Management Tools. - SQL Server 2008 and SQL Server 2008 R2 provide a suite of tools for managing and admin- istering the SQL Server Database Engine and other components. - The following sections provide an overview of the primary tools for day-to-day administration, management, and monitoring of your SQL Server environments.. - SQL Server Management Studio (SSMS). - SSMS is examined in more detail in Chapter 4, “SQL Server Management Studio.”. - Figure 1.1 shows a list of registered servers in the upper-left pane. - Below that is the Object Explorer, which lets you browse the contents of the databases within a SQL Server. - FIGURE 1.1 SSMS, showing a list of columns for the authors table in the bigpubs2008 ptg database.. - Following are some of the tasks you can perform with SSMS. - Most of these tasks are discussed in detail later in the book:. - Set server options and configuration values, such as the amount of memory and number of processors to use, default language, and default location of the data and log files. - Create, edit, and schedule automated jobs through the SQL Server Agent . - Define, implement, manage, and invoke SQL Server Policies
Xem thử không khả dụng, vui lòng xem tại trang nguồn hoặc xem
Tóm tắt