Microsoft SQL Server 2005 completely redefines the SQL Server database platform and provides the bedrock foundation on which small, medium, and large organizations can build their next generation IT infrastructure. At the core of SQL Server 2005, you will find:
SQL Server Database Services. Includes the core database, replication, and full-text search components. The core database, the Database Engine, is the heart of SQL Server. Replication increases data availability by distributing data across multiple databases, allowing you to scale out the read workload across designated database servers. Full-text search allows plain-language queries on data stored in SQL Server tables.
Analysis Services. Delivers Online Analytical Processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services allows your organization to aggregate data from multiple data sources, such as relational databases, and work with this data in a wide variety of ways.
Data Integration Services. Provides an enterprise data transformation and integration solution for extracting and transforming data from multiple data sources and moving it to one or more destination sources. This allows you to merge data from heterogeneous data sources, load data into data warehouses and data marts, and more.
Notification Services. Includes a notification engine and client components for generating and sending personalized, timely messages to users when a triggering event occurs. Notifications can be sent to wireless devices, such as mobile phones and PDAs, Windows Messenger accounts, and e-mail accounts.
Reporting Services. Includes Report Manager and Report Server to provide a complete, server-based platform for creating, managing, and distributing reports. The Report Server is built on standard IIS and .NET framework technology, allowing you to combine the benefits of SQL Server and IIS to host and process reports.
Service Broker. Provides reliable queuing and messaging as a core part of the database. Queues can be used to stack work, such as queries and other requests, and perform them as resources allow. Messaging allows database applications to communicate with each other.
As you get started with Microsoft SQL Server 2005, you should concentrate on these areas:
How SQL Server 2005 works with your hardware
What versions and editions of SQL Server 2005 are available and how they meet your needs
How SQL Server 2005 works with Microsoft Windows-based operating systems
What administration tools are available
Successful database server administration depends on three things:
Good database administrators
Strong database architecture
Appropriate hardware
The first two ingredients are covered: you’re the administrator, you’re smart enough to buy this book to help you through the rough spots, and you’ve enlisted SQL Server 2005 to provide your high-performance database needs. This brings us to the issue of hardware. SQL Server 2005 should run on a system with adequate memory, processing speed, and disk space. You also need an appropriate data and system protection plan at the hardware level.
Note
Having well written database applications and proper database design makes a database administrator’s job much easier. Poor performance is more often due to poor application and data structure design than to anything a database administrator can do. So in a way, this makes the overall design a fourth ingredient for success—but it’s an ingredient that’ s largely beyond your control as a database administrator.
Key guidelines for choosing hardware for SQL Server are as follows:
Memory. SQL Server 2005 requires a minimum of 512 MB of RAM for Standard Edition or Enterprise Edition, and 1 GB of RAM for 64-bit editions. In most cases, you will want to have at least twice the recommended minimum amount of memory. The primary reason for this extra memory is performance. SQL Server 2005 and standard Windows services together use about 256 MB of memory as a baseline.
Additional database features, such as Analysis Services, Reporting Services, and Notification Services, increase the baseline memory requirements (by about 30 MB of RAM each). Installation of IIS 5.0 or later and related components is required for Reporting Services, which also increases the baseline memory requirements. Running either management studio on the SQL Server uses 50 MB to 60 MB of RAM as a baseline. Also consider the number of user connections. User connections consume about 24 KB each. Data requests and other SQL Server processes use memory as well, and this memory usage is in addition to all other processes and applications running on the server.
CPU. 32-bit versions of SQL Server 2005 run on Intel x86 or compatible hardware. 64-bit versions run on Intel Itanium (IA-64) and X64 family of processors from AMD and Intel, including AMD64 and Intel Extended Memory 64 Technology (Intel EM64T). SQL Server provides solid benchmark performance with Intel Xeon 3.66 GHz, Intel Itanium 2 1.6 GHz, AMD Opteron 2.6 GHz, and AMD Athlon 2.6 GHz. Any of these CPUs provide good starting points for the average SQL Server system. You can achieve significant performance improvements with a high level on processor cache. Look closely at the L1, L2, and L3 cache options available—a higher cache can yield much better performance overall.
The primary advantages of 64-bit processors over 32-bit processors have to do with memory limitations and data access. Because 64-bit processors can exceed the 4-GB memory limit of 32-bit processors, they can store greater amounts of data in main memory, providing direct access to and faster processing of data. In addition, 64-bit processors can process data and execute instruction sets that are twice as large as 32-bit processors. Accessing 64 bits of data (versus 32 bits) offers a significant advantage when processing complex calculations that require a high level of precision. However, not all applications are optimized for 64-bit processors, which can present an implementation and maintenance challenge.
SMP. SQL Server 2005 supports symmetric multiprocessors and can process complex parallel queries. Parallel queries are valuable only when relatively few users are on a system and you’re processing large queries. On a dedicated system that runs only SQL Server and supports fewer than 100 simultaneous users who aren’t doing complex queries, a single CPU should suffice. If the server supports more than 100 users or doesn’ t run on a dedicated system, you may want to consider adding processors (or using a system that can support additional processors as your needs grow). Keep in mind that the size of the queries and data sets being processed affects how well SQL Server scales. As the size of jobs being processed increases, you will have increasing memory and CPU needs.
Disk drives. The amount of data storage capacity you need depends entirely on the number and size of the databases that the server supports. You need enough disk space to store all your data plus work space, indices, system files, virtual memory, transaction logs, and, in the case of a cluster, the quorum disk. I/O throughput is just as important as drive capacity. For the best I/O performance, FC (Fiber Channel) is the recommended choice for high-end storage solutions. Instead of using a single large drive, you should use several smaller drives, which allows you to configure fault tolerance with RAID (redundant array of independent disks). I recommend separating data and logs and placing them on separate drives. This includes the quorum disk for clustering.
Data protection. You should add protection against unexpected drive failure by using RAID. For data, use RAID 0 or RAID 5. For logs, use RAID 1. RAID 0 (disk striping without parity) offers good read/write performance, but any failed drive means that SQL Server can’ t continue operation on an affected database until the drive is replaced and data is restored from backup. RAID 1 (disk mirroring) creates duplicate copies of data on separate drives, and you can rebuild the RAID unit to restore full operations. RAID 5 (disk striping with parity) offers good protection against single drive failure but has poor write performance. For best performance and fault tolerance, RAID 0 + 1 is recommended, which consists of disk mirroring and disk striping without parity.
Uninterruptible Power Supply (UPS). SQL Server is designed to maintain database integrity at all times and can recover information using transaction logs. This does not protect the server hardware, however, from sudden power loss or power spikes. Both of these events can seriously damage hardware. To prevent this, get an uninterruptible power supply. A UPS system gives you time to shut down the system properly in the event of a power outage, and it is also important in maintaining database integrity when the server uses write-back caching controllers.
If you follow these hardware guidelines, you will be well on your way to success with SQL Server 2005.