Enterprise Design for Data Services

This blueprint considers the design options for your data services. This document is primarily concerned with how these design requirements affect connected infrastructure design and configuration. This document begins by considering generic definitions of the requirements for your data service and your data service design inputs. It then identifies how SQL Server 2005 meets the design requirements, and finally discusses how connected infrastructure supports and enables your data service.

On This Page

	Service Definition
	Service Design
	Logical Design
	Physical Design
	Interoperability
	Service Dependencies
	Standards and Guidelines

Service Definition

Your data service includes data stores, network hardware such as routers, switches, and firewalls, supporting services such as Domain Name System (DNS), Public Key Infrastructure (PKI) and a Directory Service, and application software that facilitates the secure storage and management of data including the ability for clients to connect to your service.

Your data service has many roles within an infrastructure and interacts with many different applications and services. Because of this, it is imperative that your data service is secure and reliable, and satisfies requirements in the following areas:

Security

The security of your data service is a primary design issue. Your data service must secure the data and control access to it, but must also enable the required functionality and performance.

It is important to limit potential attack scenarios by reducing the attack surface. Surface area reduction involves stopping or disabling unused components to help secure a system.

You can divide data service security into two broad areas:

Access Security

Securing access to data services begins at the network edge with firewall configuration. Additional network devices such as routers and switches also control access to the data services servers.

You must identify the access required by services and applications that are part of your data service. Any accounts that you configure for the applications and services to run under should only have the capabilities required by that service or application.

For more information about firewall configuration, see the Firewall Services Blueprint document available at the following URL: http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/FirewallServices/default.mspx

For more information about configuring network devices, see the Network Devices Blueprint document available at the following URL:

http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/NetworkDevices/igndbp.mspx

Access security, configurable at the data service level, uses authentication and authorization but also includes the control of physical access to data service components.

Authentication

Authentication is a process where users attempting to use a computer identify who they are to the security sub-system by providing unique credentials. The most common credentials are a user name and password. More sophisticated credentials such as certificates or biometrics may make the environment more secure. An authentication protocol uses the credentials you provide to identify you when you logon and access network resources.

To authenticate users to your data service, you can configure your data service to use an existing authentication process that is available within the environment, such as a directory service that authenticates access to a number of network resources. Alternatively, you can create new identities within your data service that only authenticate access to the data service.

Authorization

After you authenticate and identify the user, you can control their access to the data in your data service. Access to data requires passage through a number of security levels, for example, authorization by the database server and then the database itself.

Authorization defines the allowed access to data depending on the authenticated credentials. If you authorize access, you can also specify levels of access, such as the ability to read or write data.

You authorize accounts to carry out specific tasks by assigning the relevant permissions to the account. Alternatively, you can assign the account a database role that already has permissions assigned to it.

Physical Security

Physical security controls physical access to your server or servers that are part of your data service environment. Physical security includes measures, such as door locks, security cameras, and security guards.

Remote administration tools enable a user to access the server as if they were sitting in front of it. You must secure access to any remote administration tools in use in your environment.

To secure manipulation of your data service servers, use as many of the security features built into your hardware as possible. This includes components locked into place, floppy boot prevention and unused drives such as DVDs removed.

For more information about security issues, see the Security Architecture Blueprint document available at the following URL:

http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/ArchitectureBlueprints/rbabsa.mspx

Data Security

Data security considers how to secure the data itself and includes mechanisms, such as encryption.

Your database provides a static target for attackers. The data stored in your database therefore requires a high level of protection, especially if your database is accessible from outside of the corporate network.

Encryption of data, when it is stored in your database, maintains the security of the data. Encryption of your data should also occur as it is in transit across the network. You will often have to configure separate processes to encrypt the data in your database and in transit.

Auditing

When you have configured the security for your data service, you must audit access to the data to confirm that the security mechanisms you have implemented provide the required protection.

The auditing process can produce a large amount of information, so it is important to audit specific actions, such as unsuccessful logons, and make sure that the audit logs are regularly checked.

Performance

The best way to get optimum performance from your data service is to ensure that your computer hardware, (including processor, memory, and disk sub-system) and your network infrastructure, (including network interfaces, routers and switches,) can support the processing and throughput that you require. This is termed capacity planning and is essential as part of your design process.

When you are satisfied that the hardware and network environment has the capability to produce the performance that you require, you should make sure that the logical and physical design of the database is optimal for each environment that the data is accessed from. For example, you can physically partition databases, partition tables and normalize or de-normalize data.

You can also carefully tune the operating system and database software to gain additional benefits for specific environments.

The development of your data service applications can have a great effect on performance. It is important to identify best practices and tune your application to gain best performance from the data services environment.

For more information about data application performance tuning, see the Optimizing Performance in SQL Server Solutions document available at the following URL:

http://msdn.microsoft.com/SQL/sqlperf/default.aspx

Scalability

Your data service must be able to cope with projected growth of the network and its use. Your database software must support large amounts of data and use large amounts of computer resources, such as memory and processing power, to support greater capacity while maintaining performance.

Typically for a data service, scalability means a greater amount of transactions and larger, or more, databases. Sound architectural designs scale to exceed current requirements, and are sustainable into the future without extensive redesign.

Scaling of the data service divides into two procedures:

Scale Up

Scale up indicates the addition of resources into the server. Typically, the resources are memory, processing power, or network interfaces, but can be any resources used by the computer. The data service must be able to provide additional capacity or performance relative to amount of additional resources.

Scale Out

Scale out indicates the configuration of additional servers to share the load of the data service. Scale out solutions can also provide fault tolerance capabilities for the data service.

When you scale out your data service, you must ensure the integrity of data and transactions across all systems.

Availability

Your data service affects many processes, procedures, and applications within your infrastructure, so it is imperative that your data service remains available through time.

This does not mean that your data service (or components of it) can never fail, but you must have a means of maintaining functionality in the event of a failure.

The availability of your supporting infrastructure affects the availability of your data service. Clients cannot use your data service if infrastructure functionality, including authentication, connectivity, or name resolution fails.

Availability begins with your database application and the server it is running on, but includes client connectivity, network services, network hardware, network security, and storage networks. Availability can be as advanced as mirroring a complete office environment to a remote location to use in the event of the original office becoming unavailable, or as basic as multiple power supplies in servers.

Recoverability

Your data service deployment includes availability configurations, but disasters, such as fires or floods, can negate the effectiveness of the availability solution and require the recovery of data and services.

Like availability, recoverability procedures must take into account not only the application or the server but also the network infrastructure that is required for the data service functionality and client connectivity to it.

Recoverability includes the ability to successfully backup and restore your data service.

Manageability

Managing your data service includes processes, such as:

Your data service must provide sufficient data and connectivity for management procedures to be effective.

Infrastructure Considerations

For your data service to meet these criteria, the supporting infrastructure must include software and hardware that provides necessary services and functionality, including:

Top of page

Service Design

The design process for your data service initially identifies business requirements and identifies how the data service can satisfy those requirements. Because of this, all levels of the business, including non-technical staff, have input into your design.

Design Inputs

Your data service is likely to be business critical because it interacts with many other services and applications, so successful design is vitally important.

Your initial design input is a business requirement for a feature or functionality satisfied by using IT technologies in general and a data service in particular. The identification of the business requirement is a non-technical business decision.

Your IT department designs a solution that satisfies your business requirement. Though your solution satisfies your specific business requirement, it may place additional demands on your network infrastructure for successful deployment.

Your network infrastructure must be able to provide your data service requirements. The network capabilities required by a data service provide a range of functionality including:

Your chosen database application may also dictate some design decisions. The database application may have specific deployment, configuration, or connectivity requirements of the network infrastructure.

Choose your database software to suit your design requirements.

Design Options for SQL Server Editions

SQL Server 2005 is a database application that provides all of the functionality that is necessary for an enterprise data service. The SQL Server 2005 product line includes a number of different editions.

SQL Server 2005 Enterprise Edition (32-bit and 64-bit)

Enterprise Edition scales to the performance levels required to support the largest enterprise OLTP, highly complex data analysis, data warehousing systems, and Web sites.

The Enterprise Edition includes comprehensive business intelligence and analytics capabilities, and its high availability features, such as failover clustering and database mirroring; enable it to handle the most mission critical enterprise workloads.

Enterprise Edition is the most comprehensive edition of SQL Server and is ideal for the largest organizations and the most complex requirements.

SQL Server 2005 Standard Edition (32-bit and 64-bit)

Standard Edition is the data management and analysis platform for small-sized and medium-sized organizations. It includes the essential functionality that is required for e-commerce, data warehousing, and line-of-business solutions.

The integrated business intelligence and high availability features of the Standard Edition provide organizations with the essential capabilities required to support their operations.

Standard Edition is ideal for the small-sized to medium-sized organization that requires a complete data management and analysis platform.

SQL Server 2005 Mobile Edition

Microsoft SQL Server Mobile Edition, formally SQL Server Windows CE Edition (SQL Server CE), is a mobile database solution that is designed for developers who target Microsoft Windows mobile-based devices.

New features and enhancements for SQL Server Mobile Edition focus on the following key areas:

SQL Server 2005 Workgroup Edition (32-bit only)

Workgroup Edition is the data management solution for small organizations that require a database with no limits on size or number of users.

Workgroup Edition can serve as a front-end Web server, or departmental or branch office operations. It includes the core database features of the product line, and is easily upgradeable to Standard or Enterprise Edition.

Workgroup Edition is an ideal entry-level database that is reliable, robust, and easy-to-manage.

SQL Server 2005 Developer Edition (32-bit and 64-bit)

Developer Edition includes all of the functionality of SQL Server 2005 Enterprise Edition, but the license is for use as a development and test system, not as a production server. Developer Edition is an ideal choice for independent software vendors (ISVs), consultants, system integrators, solution providers, and corporate developers who build and test applications. Developer Edition is upgradeable for production use to SQL Server 2005 Enterprise Edition.

SQL Server 2005 Express Edition (32-bit only)

SQL Server Express is a free, easy-to-use, and simple-to-manage database system. Integrated with Microsoft Visual Studio 2005, SQL Server Express makes it easy to develop data-driven applications that are rich in capability, secure in storage, and fast to deploy.

SQL Server Express is free and can be redistributed (subject to agreement), and functions as the client database, as well as a basic server database.

SQL Server Express is an ideal choice for low-end ISVs, low-end server users, non-professional developers building Web applications, and hobbyists building client applications.

SQL Server 2005 Evaluation Edition

SQL Server 2005 Evaluation Edition is a 120-day trial version of SQL Server 2005. This version is downloadable free for the purposes of testing.

For more information about SQL Server 2005 versions including a feature comparison, see the SQL Server 2005 Features Comparison document available at the following URL:

http://www.microsoft.com/sql/2005/productinfo/sql2005features.mspx

Top of page

Logical Design

The logical design of your data service considers software and service configuration options to achieve design goals. This section considers the configuration of SQL Server 2005 in the following areas:

Design Options for Security

Adequate data service security is a primary design objective. Security measures often result in a loss or disabling of features and functionality. Your data service design must consider a compromise between required levels of security and functionality.

For new installations of Microsoft SQL Server 2005, some features, services, and connections are disabled or stopped to reduce the SQL Server surface area. For upgraded installations, all features, services, and connections remain in their pre-upgrade state.

You can use SQL Server Surface Area Configuration, locally or remotely, to enable, disable, start, or stop the features, services, and remote connectivity of your SQL Server 2005 installations.

You can also use Security Templates and Group Policy to automate security configurations for the data service servers.

While you may not be part of the design team that incorporates some of the following features into the database design, it is important that the network infrastructure provide a platform for their use.

Access Security

Access security comprises the identification of individuals or groups attempting to access your data service and their allowed level of access. You should secure access by using authentication and authorization processes. Initial access to the SQL Server 2005 data service is by an endpoint.

SQL Server 2005 Endpoints

Connection management in Microsoft SQL Server 2005 uses endpoints. An endpoint is a connection point for SQL Server 2005 to communicate over the network and for users to connect to the SQL Server.

SQL Server 2005 creates a TCP endpoint during the installation process. Default authorization assigns authenticated users CONNECT permissions on the endpoint.

The endpoint listens on a specific TCP port for connection requests. The default is port 1433 for the default instance; named instances have no default port. Client applications that connect to the data service must connect to the port that the endpoint listens on.

If you require to, you can change the port that the endpoint uses or alternatively, create additional endpoints that use different ports. SQL Server 2005 services and applications that use the data service may require the creation of additional endpoints, each of which requires a unique port.

It is important that infrastructure operations teams are aware of the ports that the data services use, because network hardware, such as firewalls, routers and switches, must allow network traffic to use these ports for successful communication.

SQL Server 2005 also supports the creation of HTTP endpoints. The use of HTTP endpoints is beneficial in a heterogeneous environment because the Web services that can use them work independently of programming language and operating system. You can provide access to a Web service over HTTP, which makes firewall configuration straightforward.

The creation of HTTP endpoints is not part of the SQL Server 2005 default installation process. Creation of the HTTP endpoint must occur each time there is a requirement from a deployed service or feature.

HTTP endpoint support in SQL Server 2005 supports Simple Object Access Protocol (SOAP) messages and requires the use of HTTP.sys, which is available as part of Windows Server 2003. In the same way that Internet Information Services (IIS) registers Web addresses with HTTP.sys, the HTTP endpoint registers a uniform resource locator (URL). HTTP.sys forwards the SOAP messages for this URL to SQL Server rather than to IIS.

The HTTP endpoint enables the configuration of TCP ports that the endpoint will listen on. The URL that you use for the HTTP endpoint must resolve in DNS to the IP address of the SQL server.

This functionality enables the creation of Web services as part of your SQL Server 2005 implementation without the requirement for IIS.

For more information about the configuration of network hardware, see the Network Devices Blueprint document available at the following URL:

http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/NetworkDevices/default.mspx

You must configure authorization for each endpoint you create. You must assign CONNECT permissions to any user or role that requires access through the endpoint.

When you assign CONNECT permissions for an endpoint this does not directly enable access to data; you must also assign permissions on individual objects.

SQL Server 2005 Security Model

To control authentication and authorization, SQL Server 2005 implements a security model that comprises:

If the data service uses an infrastructure authentication process, such as a directory service, infrastructure operations must create and manage the accounts that are used to connect to the data service.

Principals

Principals are individuals, groups, and processes that can request SQL Server resources. A principal is any authenticated identity given permission to access an object in the database system. A principal can be a single identity such as a login or a collection of identities such as fixed server roles.

Securables

Securables are the resources to which the SQL Server authorization system controls access. SQL Server 2005 arranges securables in nested hierarchies called scopes. The three securable scopes are server, database, and schema.

Table 1 defines the securables in each scope.

Table 1: SQL Server 2005 Security Scopes

NOTE: In SQL Server 2005, a schema is a container for database objects, but is also part of the object namespace. When you create an object in a schema, the schema becomes part of the object's fully qualified name. The object name no longer includes the object owner as it did in previous releases, but takes the form of Server.Database.Schema.Object, which provides greater flexibility when you organize database objects and makes it possible to drop the object owner without renaming it.

Permissions

Every SQL Server 2005 securable has associated permissions. An administrator assigns permissions to a securable for a specific principal. Due to the hierarchical nature of the securable scopes, relevant permissions assigned at a higher scope can affect members of a lower scope. For example, if you assign SELECT permissions at the database scope, this can affect all securables, including schemas, within the database scope and all securables that are contained within the child schema scope, such as tables.

Authentication

Authentication is the process of identifying who is attempting to access the data service and takes place against the data service itself or against an external source, such as a directory service.

SQL Server 2005 can use SQL Server logins, which are accounts created in SQL Server, but can also integrate with Microsoft Active Directory to enable the use of accounts that are created in Active Directory for authentication purposes.

Windows accounts use a more secure authentication process than SQL Server accounts. For example, the passing of SQL Server account credentials is a non-secure process, which requires the implementation of network encryption to protect them.

For this and other reasons, you should use Windows accounts unless you have a specific reason not to, such as application support for SQL accounts or authentication to a non-Windows authentication source.

You configure the type of accounts used to access SQL Server by using SQL Server 2005 Authentication Mode. Windows Authentication mode enables Windows accounts only, whereas SQL Server and Windows Authentication mode enables both SQL Server and Windows accounts.

As an additional security measure, you can enforce Windows password policies on both SQL Server 2005 and Windows accounts.

Module Execution Context

Module execution context enables an administrator to create specific security contexts for the execution of code on the server.

For example, the administrator can create a procedure that reads or writes to a table and configure the security context for procedure execution.

This means the caller of the procedure is not required to have the permissions to carry out the functions of the procedure, though they must have EXECUTE permissions for the procedure itself.

The ability to specify the identity used by a code module, such as a stored procedure, ensures that callers can use the stored procedure to access data from underlying objects they have no permissions for, even when the ownership chain is broken.

You specify the identity for a code module with the EXECUTE AS clause of the appropriate statement. When you specify an identity with the EXECUTE AS clause, you can specify the principal for module execution.

The following table defines the options used with the EXECUTE AS clause.

Table 2: Identity options used with EXECUTE AS

Module Signing

Module signing enables secure access to data in a similar way to module execution context. To use module signing, assign a SQL Server certificate to a principal and use it to sign the module.

To use the module, a principal requires EXECUTE permissions on the module but only the certificate owner requires direct access to the data.

Unlike module execution context, the security context of the caller remains the identity that executed the module, which enables auditing of data access.

This feature makes the data available without direct permissions in the event of broken ownership chains. Module signing also ensures that the module code does not change.

SQL Server 2005 has the ability to self-assign certificates for SQL Server functions, such as module signing and data encryption.

Authorization

In general, before principals can access a securable, they must have the relevant permissions at each security scope that contains the securable.

At the server scope, SQL Server 2005 provides endpoints that enable access to the data service. You must assign GRANT, REVOKE, or DENY access permissions to principals to control access to the data service at each individual endpoint.

The principal also requires permissions at the database and schema scopes to access the required object.

Windows Accounts

To use Windows Authentication, use Active Directory. Active Directory is the network-focused directory service that is included in Microsoft Windows 2000 and Windows Server 2003.

Applications and services can use Active Directory to integrate into the network environment and provide authentication control. The use of a single directory for authentication provides single sign on capabilities. Active Directory authentication also securely passes credentials, so it provides a secure authentication process.

Active Directory supports the use of Microsoft Windows NT LAN Manager (NTLM) and Kerberos authentication protocols.

NTLM

NTLM is the authentication protocol used on networks that include systems running the Windows NT operating system and on stand-alone systems. NTLM is a more advanced challenge/response-based protocol than its weaker predecessor LAN Manager (LM).

Your applications should not access the NTLM security package directly; instead, you should develop the application to use the Negotiate security package. Negotiate allows your application to take advantage of more advanced security protocols if they are supported by the systems involved in the authentication. The Negotiate security package selects between Kerberos and NTLM and selects the more secure Kerberos protocol unless one of the involved systems cannot use it.

Kerberos

The Kerberos authentication protocol provides a mechanism for mutual authentication between entities before a secure network connection is established.

The Kerberos protocol defines how clients interact with a network authentication service. Clients obtain tickets from the Kerberos Key Distribution Center (KDC), and they present these tickets to servers when connections are established. Kerberos tickets represent the client's network credentials.

For more information about Active Directory, see the Directory Services Blueprint document available at the following URL: http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/DirectoryServices/default.mspx

SQL Server 2005 Accounts

Accounts created in SQL Server 2005 authenticate users and authorize access to the data service. Management of the account is separate from other accounts on the network because it is stored in the data service and only provides access to the data service. This separation creates an administrative overhead but may satisfy a specific business requirement.

Passing of the credentials for accounts created in SQL Server 2005 is not a secure process. To add security, use a network encryption protocol such as Secure Sockets Layer (SSL) or IP Security (IPSec) or develop the application to provide encryption functionality.

Data Security

As well as controlling access to data, you must secure the data stored in the database. Cryptographic technologies included in SQL Server 2005 enable the encryption of data, as it is stored in the database.

SQL Server 2005 database encryption uses industry standard techniques, including the use of certificates, public and private asynchronous encryption keys and symmetric encryption keys. SQL Server 2005 has the capability to create the certificates and encryption keys for data encryption.

Database encryption encrypts the data only when it is stored in the database. To protect the data in transit across a network, you can configure encryption at the endpoint or use encryption technologies such as SSL or IPSec.

You can also use multiple instances of SQL Server 2005 to secure access to the data. A user will only be able to access data in the instance to which their account has access. If you do not wish to use encryption technologies, you can distribute clients between instances to prevent access to specific data.

Application Security

The network infrastructure provides security mechanisms that integrate with the data service and applications. The developer of the application must use these mechanisms correctly.

For more information about application security, see the Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication document available at the following URL:

http://msdn.microsoft.com/SQL/sqlsecurity/default.aspx?pull=/library/en-us/dnnetsec/html/secnetch12.asp

Public Key Infrastructure

The network infrastructure may already include a Public Key Infrastructure (PKI); if not the deployment of this type of environment may be part of the design as a whole.

A PKI includes the use of certificates and public and private encryption keys for authentication and encryption of network communications.

A PKI integrates with the data service to provide authentication processes instead of using password based authentication. A PKI can use advanced authentication methods such as the use of smart cards.

For more information about certificate services, see the Certificate Services Blueprint document available at the following URL: http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/CertificateServices/default.mspx

Summary Table

The following table summarizes the information in the security section.

Table 3: Summary table for security section

Design Options for Performance

To provide optimal performance for the data service, infrastructure teams must deploy enough server hardware resources such as processor, memory and disk to the SQL Server 2005 server. This is easiest to achieve if the database server is purpose built and dedicated to that role.

If enough resources are available Microsoft SQL Server 2005 automatically tunes many of the server configuration options, so system administrators are required to do little, if any, tuning. Default settings enable SQL Server to tune itself automatically based on run-time conditions.

The underlying network infrastructure must also support the network throughputs that are required for optimal data service performance.

The Physical Design section of this document discusses capacity-planning processes for both the server and the underlying network infrastructure.

The database design will have a large impact on the performance of the data service. This will be part of the database operations team responsibilities, but infrastructure teams should be aware of its impact on overall performance.

The performance of an enterprise database system depends heavily on its physical database design, which includes indexes, indexed views, and partitioning strategies. Microsoft SQL Server 2005 provides the Database Engine Tuning Advisor, a tool you can use to tune the physical design of your database.

Database Engine Tuning Advisor uses a representative workload in the form of a Transact-SQL script, a trace file, or a trace table, to analyze the effects of running that workload on selected databases. After consuming the workload, Database Engine Tuning Advisor produces a recommended configuration of physical database design structures to improve performance.

SQL Server 2005 Performance Tuning

SQL Server 2005 performance tuning configures the utilization of the major subsystems in the server, namely processor, memory, and disk.

If optimal performance requires the tuning of SQL Server 2005, database operations teams will implement the tuning, possibly after discussion with application development and infrastructure operations teams.

One of the best ways of getting good performance from the data service is to develop the application following development best practices.

For more information about the performance configuration settings, see the Data Services Implementation Guide available at the following URL:

http://go.microsoft.com/fwlink/?LinkId=55192

Data Service Applications

The development of the application utilizing the data service plays a large part in achieving acceptable levels of performance.

For more information about application performance, see the Optimizing Performance in SQL Server Solutions document available at the following URL:

http://msdn.microsoft.com/SQL/sqlperf/default.aspx

For more information about the sp_configure stored procedure, see the Setting Configuration Options document available at the following URL:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp

Database Configuration

Database configuration is the responsibility of the database operations teams but it may place requirements on the server architecture such as processor numbers and disk storage.

Typically, you create tables to store information about an entity, such as customers or sales, and each table has attributes that describe only that entity. While a single table for each entity is the easiest to design and understand, additional optimization improves performance, scalability, and manageability, particularly as the table grows larger.

When tables and indexes become very large, partitioning can help by partitioning the data into smaller, more manageable sections.

If a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations. The performance of large-scale operations across extremely large data sets (for instance many million rows) can benefit by performing multiple operations against individual subsets in parallel.

In addition to partitioning a table's dataset, you can partition indexes. Partitioning both the table and its indexes using the same function often optimizes performance. When the indexes and the table use the same partitioning function and columns in the same order, this aligns the table and index.

If you create an index on an already partitioned table, SQL Server automatically aligns the new index with the table's partitioning scheme unless you explicitly partition the index differently.

When a table and its indexes align, SQL Server can move partitions in and out of partitioned tables more effectively, because all related data and indexes divide with the same algorithm.

If a query joins two tables that are not collocated i.e. arranged together, or are not partitioned on the join field, the presence of partitions may actually slow down query processing instead of accelerating it.

It may be tempting to map your partitions to filegroups, each accessing a different physical disk drive, to improve I/O performance. This is not an optimal solution because when a SQL Server performs data sorting for I/O operations, it sorts the data first by partition. Under this scenario, SQL Server accesses one drive at a time, and this might reduce performance. A better solution in terms of performance is to stripe the data files of your partitions across more than one disk by setting up a RAID array.

Separation of different database roles such as OLTP and data warehousing makes sure that each server can be built specifically for the role it will hold and also that the data can be transformed for each role so that it is stored in the optimal way.

Federation

Federation requires the deployment of a number of servers to serve a database distributed among the servers. Federation does not provide availability of the database, but with a correctly developed application that forwards requests to the server providing the bulk of the data, can improve performance.

To achieve the high levels of performance required by the largest data services, a multi-tier system typically balances the processing load for each tier across multiple servers. SQL Server 2005 shares the database-processing load across a group of servers by horizontally partitioning the data in a SQL Server database across multiple servers.

Management of these servers is independent, but they cooperate to process the database requests from the applications; such a cooperative group of servers is a federation.

A federated database tier can achieve very high levels of performance only if the application sends each SQL statement to the member server that has most of the data required by the statement. This is collocating i.e. arranging, the SQL statement with the data required and uses a hashing technique to send the request to the correct server.

Although a federation of servers presents the same image to the applications as a single database server, there are internal differences in the implementation of the database services tier.

The following table identifies different federation configuration options.

Table 4: Federation configuration options

Replication

Replication copies data between servers to make it available from multiple systems within the network. This may be between servers in the same environment, or to servers in separate environments such as from a corporate network to an extranet.

In addition to maintaining the servers, infrastructure operations teams must maintain connectivity between the servers.

You can use replication as a performance enhancement in itself. Making the same data available in multiple places enables the distribution of client requests among the various data stores, improving performance for each client.

This solution works best with static read-only data. If the data changes often, the replication of updates to each version of the database puts a heavy traffic load on the network and may result in the use of outdated data, negating the client performance benefits.

By default, the database engine changes its memory requirements dynamically based on available system resources, including the resources available for replication.

Use Replication Monitor and System Monitor to determine typical numbers for the following five dimensions of replication performance:

Latency and throughput are most relevant to transactional replication, because systems built on transactional replication generally require low latency and high throughput. Concurrency and duration of synchronization are most relevant to merge replication, because systems built on merge replication often have a large number of subscribers, and a publisher can have a significant number of concurrent synchronizations with these subscribers.

Common Language Runtime

The hosting of the common language runtime (CLR) within SQL Server 2005 is primarily of interest to developers because it enables them to use managed code for database modules.

In addition, the integration of the CLR and the implementation of HTTP endpoints mean that it is possible to create Web services and applications within SQL Server 2005. However, this type of deployment may create security concerns because the clients directly connect to the data store server. This does not provide the layered security model that multiple physical tiers provide.

The CLR is the heart of the Microsoft .NET Framework and provides an execution environment for all .NET Framework code. Code that runs within the CLR is managed code. The CLR provides various functions and services required for program execution, including just in time compilation, allocation, and management of memory, enforcement of type safety, exception handling, thread management, and security.

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Managed code uses Code Access Security (CAS) so that assemblies do not perform certain operations. SQL Server 2005 uses CAS to help secure the managed code and prevent compromise of the operating system or database server.

Summary Table

The following table summarizes the information in the performance section.

Table 5: Summary table for performance section

Design Options for Scalability

Scalability of the data service relies on the infrastructure and the servers to be able to facilitate optimal performance over a long period.

In addition, the database software must be able to use increasing amounts of hardware resources to maintain and improve performance.

To improve scalability, SQL Server 2005 uses large amounts of hardware resources within the server. SQL Server 2005 also provides scalability in the deployment options for the database and associated services.

SQL Server 2005 is available in 64-bit versions. The 64-bit versions have support for greater amounts of resources than the 32-bit versions, in addition to supporting faster data throughputs.

SQL Server 2005 64-bit

Microsoft SQL Server 2005 Developer Edition and Standard Editions are available in 64-bit versions for computers with supported 64-bit processors.

To run the 64-bit release of SQL Server 2005, you must use the 64-bit versions of Microsoft Windows. Currently, the support scope for the 64-bit platform is limited as follows:

SQL Server 2005 Services

You can deploy additional SQL Server 2005 services in a distributed architecture. This provides optimal performance and resource usage, and enables scalable environments.

In addition to the database service, the following services are available as part of a SQL Server 2005 deployment:

Service Broker

Service-oriented architecture (SOA) has become an important concept in the design of large-scale distributed applications. Central to SOA is the ability of services to communicate by using a reliable message-based mechanism.

SOA is a type of architecture that encourages loosely coupled communication between software services. Loose coupling means that the client and the service are not heavily dependent upon each other. For example, you can replace the logic in the service without affecting the client and vice versa.

Note: A key premise to infrastructure architecture is that designs must be capable of evolution without major redesign of the service itself or other components of the architecture.

Service Broker provides a message-based mechanism that controls transactions as part of a data service solution. This enables you to build reliable OLTP solutions into your data service.

Within a single SQL Server instance, Service Broker provides a robust asynchronous programming model but also provides reliable messaging between SQL Server instances. Between SQL Server instances, Service Broker uses TCP endpoints for communications. The network infrastructure must allow communications to take place through the ports used by the TCP endpoints.

Service Broker uses TCP endpoints, which are configured to use a specific TCP port number, to communicate with services and applications within your network environment. In addition, Service Broker endpoints provide communication security and message forwarding.

When you create a Service Broker endpoint, SQL Server accepts connections on the specified port. The network devices that control the flow of network traffic between computer systems on the network must allow traffic to pass on the port used by service broker. If the server on which SQL Server runs has a firewall enabled, the firewall configuration on the computer must allow both incoming and outgoing connections for the specified port.

In addition, a deployment of Service Broker also relies on authentication that uses digital certificates. This implementation of certificate authentication should fit in with any current PKI deployment.

Service Broker security relies on trusted certificates current on participating database servers. The general approach is to use certificates to establish the credentials of a remote database, and then to map operations from the remote database to a local user. The permissions for the local user apply to any operation on behalf of the remote service.

Understanding Service Broker security mechanisms, and how they work together, will help you to design, deploy, and administer service broker applications.

Service broker provides two distinct types of security:

Dialog Security

Dialog security encrypts messages in an individual dialog conversation and verifies the identities of participants in the dialog.

Service Broker encrypts all messages sent outside a SQL Server instance but not messages that remain within a SQL Server instance.

You can enable encryption and authentication by the use of certificates that are assigned to the service accounts involved. You must assign the certificates from a trusted source, either internal to your network or an external certificate authority.

For more information about certificate services, see the Certificate Services Blueprint document available at the following URL: http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/CertificateServices/default.mspx

In dialog security, only the database that hosts the initiating service and the database that hosts the target service must have access to the certificates used for security. That is, an instance that performs message forwarding is not required to have the capability to decrypt the messages that it forwards.

Transport Security

Transport security ensures that unauthorized databases do not send Service Broker messages to databases in the local instance. Transport security establishes an authenticated network connection between two databases.

Note: The design of dialog protocol and the adjacent broker protocol enables Service Broker to pass messages between databases, rather than execute commands on a remote database. This style of communication enables Service Broker to provide services without the requirement for databases to share SQL Server logins or Windows security credentials.

Service Broker helps your data service applications scale well, whether you scale up or scale out. When you design your Service Broker application for scalability, carefully consider how the tasks in the application relate to each other.

Services constructed with clear separation between tasks are generally most successful in both scale-up and scale-out scenarios.

In general, you can divide tasks into services by considering the data that is required to complete the task. When two related tasks do not modify the same data, you can structure these tasks as different services. For example, although both a customer management application and a shipping application require access to the customer address, only the customer management application modifies the address. In this case, messages to the shipping application can contain the address information necessary to ship an order. Because there is no requirement for the shipping application and the customer application to access the same tables, separate these tasks into different services.

Service Broker applications that involve more than one instance run in the security context of a database principal that is created specifically for the application. These database principals should have the minimum permissions that are required to accomplish the tasks that the application performs.

The following considerations apply to database principals that are created for Service Broker applications:

The user specified as the user for an activation stored procedure must have permission to execute the procedure. Frequently, the user specified has the permissions required to execute the statements in the procedure. Notice, however, that if the stored procedure itself uses an EXECUTE AS clause, the statements in the stored procedure run with the security context defined by the stored procedure. SQL Server first sets the security context to the user specified for the queue. SQL Server then executes the stored procedure and the stored procedure changes the security context to the user specified for the procedure.

When Service Broker transport security uses the security support provider interface (SSPI), the service account for the remote database must have CONNECT permission in the master database, and correspond to a login. Therefore, the account that the remote SQL Server instance runs as must have permission to log in to SQL Server by using Windows Authentication. There are no requirements for the login to have other permissions or to own objects in any database.

Reporting Services

Component services of the data service may support more than one deployment option. You should investigate all options during the design process to identify which best suits your requirements.

Microsoft SQL Server 2005 Reporting Services is a server-based platform that enables you to build enterprise reports that draw content from a variety of relational and multidimensional data sources, publish reports that are viewable in various formats, and centrally manage security and subscriptions. The reports that you create are viewable over a Web-based connection or as part of a Windows application or Microsoft SharePoint portal.

Reporting Services includes graphical tools and wizards to help you create and publish reports and report models. Reporting Services also includes report server management tools for administration of Reporting Services, and application programming interfaces (APIs) so you can program against and extend the Reporting Services object model.

SQL Server 2005 Reporting Services provides several ways to make reports available for general use:

SQL Server 2005 Reporting Services offers two basic deployment models:

Standard Deployment Model

The standard deployment model consists of a single report server instance that uses a local or remote SQL Server database engine to host server data.

Scale-out Deployment Model

The scale-out deployment model consists of multiple report servers that share a single report server database. You can install the report server database locally with one of the report servers, or remotely on an additional SQL Server instance.

You can configure a report server database to run in a SQL Server failover cluster. Whether you use a single database or a failover cluster, the configuration you use is transparent to a report server.

To run the report servers as a single virtual server or to use NLB, you must use software and tools that support that functionality. Reporting Services does not provide server cluster or virtual server management.

Note: Enterprise Edition, Developer Edition, and Evaluation Edition support the scale-out deployment model.

Disk space availability is a primary consideration when you choose where to host the report server database. If you have a remote instance of SQL Server that has extra capacity, you should use a remote server. Although the footprint of a report server database may be small initially, disk space requirements can grow significantly at run time, and depends on how you run reports and the number of users who access the report server.

The design of SQL Server Reporting Services is not directly for Internet report deployment scenarios, but you can successfully place Report Server and Report Manager on an Internet-facing Web server to disseminate general information to the public, or private corporate data to authorized and authenticated users.

To distribute public information, you can place the report server outside the firewall and push the data and reports that you want to make available. This may mean that data flows through network devices, such as routers and firewalls. You should configure the network devices, such as firewalls, to allow the traffic to pass.

For more information about security architecture, see the Security Architecture Blueprint document available at the following URL: http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/ArchitectureBlueprints/rbabsa.mspx

For more information about firewall services, see the Firewall Services Blueprint document available at the following URL: http://www.microsoft.com/technet/solutionaccelerators/wssra/raguide/FirewallServices/igfsbp.mspx    

The report server database should be in the same subnet as the report server instance and contain all the snapshots, folders, and resources that you want to make available. Configure the report server virtual directory for Anonymous access. Role assignments that you subsequently define do not vary for individual users; all users have the same access to the same content.

ASP.NET, Microsoft Internet Information Services (IIS), and Microsoft Windows provide security for Reporting Services.

Use the following security systems to ensure that only authorized users have access to a Reporting Services deployment:

IIS authenticates a user connection to a report server and to Report Manager. Although IIS supports a number of authentication options, a default installation of Reporting Services uses Windows authentication to access Reporting Services components.

The following list describes the authentication options that you can use in IIS:

Windows authentication is seldom practical for deployment models that require Internet or extranet access. If you deploy Reporting Services on an Internet-facing Web server, you should replace Windows authentication with a custom authentication scheme that gives you more control over how you grant external users access to the report server. A custom authentication extension requires custom code and expertise in Microsoft ASP.NET security.If you do not want to code a custom authentication extension, you can use Active Directory groups and accounts if you wish to reduce the scope of a report server deployment.

The following guidelines describe how to support this scenario:

Analysis Services

Data warehouses and the creation of business intelligence are major functions of corporate data services.

Microsoft SQL Server 2005 Analysis Services delivers OLAP and data mining functionality through a combination of server and client technologies.

Analysis Services have support for proactive caching. Relational OLAP (ROLAP) storage immediately enables users to browse the most recent changes in a data source, but it can have significantly poorer performance than multidimensional OLAP (MOLAP) storage. You may have applications in which your users require to see recent data, but you want the performance advantages of MOLAP storage. Proactive caching options can provide a balance between the enhanced performance of MOLAP storage and the immediacy of ROLAP storage.

With proactive caching, you can make queries against an OLAP object against either ROLAP storage or MOLAP storage, depending on whether recent changes have occurred to the data.

The query engine directs queries against source data in MOLAP storage until changes occur in the data source. After changes occur in a data source, cached MOLAP objects are dropped and querying switches to ROLAP storage while the MOLAP objects are rebuilt in cache. After the MOLAP objects are rebuilt and processed, queries switch again to the MOLAP storage. The cache refresh can occur extremely quickly for a small partition.

Analysis Services encrypts all client-server communication to reduce the risk that unauthorized users can gain access to information.

Features of Analysis Services that may compromise security, if configured inappropriately or used in an inappropriate environment, are “off” by default. For example, you can permit users to connect to Analysis Services without authentication or accept authentication submitted in clear text. However, since doing so may compromise security if used inappropriately, you must modify default settings to activate these types of features.

After you install an instance of Microsoft SQL Server 2005 Analysis Services, all members of the Administrators local group are members of the server role in that instance and have server-wide permissions to perform any task within the Analysis Services instance. By default, no other users have any access permissions to the objects in the Analysis Services instance.

Notification Services

Microsoft SQL Server Notification Services is a platform for developing and deploying applications that generate and send notifications to subscribers. The notifications generated are personalized, timely messages that are sent to a wide range of devices, and reflect the preferences of the subscriber.

Subscribers create subscriptions to notification applications. A subscription is an expressed interest in a specific type of event. For example, subscriptions might express the following preferences: "Notify me when my stock price reaches $70.00," or "Notify me when the strategy document my team is writing is updated."

You can send notifications to a wide range of devices. For example, a cellular phone, personal digital assistant (PDA), Microsoft Windows Messenger, or e-mail account. Because these devices often accompany the subscriber, notifications are ideal for sending important information.

Notification Services supports high-volume applications with thousands of incoming events per minute, millions of subscriptions matched, and thousands of notifications delivered per minute on a multiprocessor Windows–based server. Applications can scale in several ways:

An instance of Notification Services requires accounts for the Notification Services engine that runs hosted event providers, generator, and distributors; for any external applications that manage subscriptions or submit events; and for the deployment and administration of the instance. These accounts require permissions in Windows as well as in SQL Server.

The following accounts are required:

Engine Account

An engine runs the generator, distributors, and hosted event providers of an instance of Notification Services. Typically, this engine is the NS$instanceName Microsoft Windows service, which is created when you register the instance of Notification Services. In addition, however, a custom application or process can host the engine. In either case, the engine must run under a local, domain, or built-in Windows account. The engine uses this account to access local and network resources, and to access databases.

Subscription Management Accounts

Subscription management interfaces must be able to connect to the database server and execute subscription management stored procedures.

Non-hosted Event Provider Accounts

If you use a non-hosted event provider, the account used by the event provider must be able to read instance and application data and to submit event data.

Deployment and Administration Accounts

When you deploy an instance of Notification Services, you must be able to create and register the instance and configure security. When you administer an instance of Notification Services, you may be required to update or upgrade instances, manage security, and monitor performance. These tasks require different permissions, so if you divide this work between staff members, you must grant the appropriate permissions to the SQL Server account of each staff member.

Note: Each of these applications or activities has its own security requirements. Even though you can use the same account to deploy, administer, and run an instance of Notification Services, it is best to limit the permissions on each process or activity to the minimum required. You can use separate accounts for each application or component when possible, and grant minimal permissions to database user accounts through predefined database roles.

SQL Server Integration Services

SQL Server Integration Services (SSIS) is a platform for building high performance data integration and workflow solutions, such as extraction, transformation, and loading (ETL) operations for data warehousing.

The following scenarios describe typical uses of SSIS packages:

Merging Data from Heterogeneous Data Stores

Data is typically stored in many different data storage systems, and to extract data from all sources and merge the data into a single, consistent dataset is a challenge.

Integration Services can connect to a wide variety of data sources, including multiple sources in a single package. A package can connect to relational databases by using .NET and OLE DB providers, and to many legacy databases by using ODBC drivers. It can also connect to flat files, Excel files, and Analysis Services data stores.

SSIS can aggregate, merge, distribute, and modify the data by using transformations. When you have transformed the data to compatible formats, you can merge the data into one dataset.

After you apply transformations and merge the data successfully, the data is usually loaded into one or more destinations. Integration Services includes support for loading data into flat files, raw files, and relational databases. The data can also be loaded into an in-memory recordset where it can be accessed by other package elements.

Populating Data Warehouses

In a data warehouse, there are frequent updates and large data loads. Integration Services includes a task that bulk loads data directly from a flat file into SQL Server tables and views, and a destination component that bulk loads data into a SQL Server database as the last step in a data transformation process.

Tasks and transformations in Integration Services packages can process Analysis Services cubes and dimensions. When the package updates tables in the database that a cube is built on, you can use Integration Services tasks and transformations to process both the cube and the dimensions. Processing the cubes and dimensions automatically helps to keep the data current for users in both environments; users who access information in the cubes and dimensions, and users who access data in a relational database.

Integration Services can also compute functions before the data is loaded into its destination. If your data warehouses store aggregates information, the SSIS package can compute functions such as SUM, AVERAGE, and COUNT. An SSIS transformation can also pivot relational data and transform it into a less-normalized format that is more compatible with the table structure in the data warehouse.

Automating Administrative Functions and Data Loading

Administrators frequently want to automate administrative functions, such as backup and restore processes, copying SQL Server databases and the objects they contain, copying SQL Server objects, and loading data. Integration Services packages can perform these functions.

Integration Services includes tasks that copy SQL Server database objects, such as tables, views, and stored procedures; copy SQL Server objects, such as databases, logins, and statistics; and add, change, and delete SQL Server objects and data by using Transact-SQL statements.

Administration of an OLTP or OLAP database environment frequently includes the loading of data. Integration Services includes several tasks that facilitate the bulk loading of data. You can use a task to load data from text files directly into SQL Server tables and views, or you can use a destination component to load data into SQL Server tables and views after applying transformations to the column data.

If you need to perform the same administrative functions on different servers, you can use packages. A package can use looping to enumerate across the servers and perform the same functions on multiple computers. To support administration of SQL Server, Integration Services provides an enumerator that iterates across SQL Management Objects (SMO) objects. For example, a package can use the SMO enumerator to perform the same administrative functions on every job in the Jobs collection of a SQL Server installation.

SSIS packages can also be scheduled by using SQL Server Agent Jobs.

SQL Server 2005 Integration Services (SSIS) implements security on the client and on the server using the following security features:

Large Databases

Large amounts of memory are required to support large databases. SQL Server 2005 uses the Address Windowing Extensions (AWE) API to support very large physical memory sizes. SQL Server 2005 supports up to a maximum of 64 GB of physical memory on 32-bit Windows operating systems.

SQL Server 2005 dynamically allocates AWE mapped memory when running with any of the Windows Server 2003 operating system editions. In other words, the buffer pool can dynamically manage AWE mapped memory to balance SQL Server memory use with the overall system requirements.

AWE addresses a limitation inherent in 32-bit applications: they cannot access more than 4 GB of process address space. (A 32-bit pointer cannot hold a memory address larger than 4 GB.)

Using AWE, applications can directly reserve physical memory up to the maximum amount that is allowed by the operating system as non-paged memory. By using AWE you can enable SQL Server to cache more information rather than reading it from system paging files on disk. This, in turn, provides for performance gains through faster data access and reduces the frequency of disk access.

Partitioning

By splitting a large table into smaller, individual tables, queries access only a fraction of the data so can run faster, because there is less data to scan. Maintenance tasks, such as the rebuild of indexes or back up of a table, can run more quickly. This enables you to scale your tables and databases and therefore your data service solution.

You can partition data without splitting tables by physically putting them on individual disk drives. For example, if you put a table on one physical drive and related tables on a separate drive, you can improve query performance, because when you run queries that involve joins between the tables, multiple disk heads read data at the same time. You can use SQL Server filegroups to specify on which disks to put the tables.

Horizontal Partitioning

Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. For example, you can partition a table that contains 1 billion rows horizontally into 12 tables, where each smaller table represents one month of data for a specific year. Queries that require a specific month's data only reference the appropriate table.

Data analysis patterns determine how to partition the tables. You should partition the tables so that queries reference as few tables as possible. Otherwise, excessive UNION queries, which are used to merge the tables logically at query time, can affect performance.

Partitioning data horizontally based on age and use is common. For example, a table may contain data for the last five years, but you only regularly access data from the current year. In this case, you may consider partitioning the data into five tables, with each table containing data from only one year.

Vertical Partitioning

Vertical partitioning divides a table into multiple tables that contain fewer columns. The two types of vertical partitioning are normalization and row splitting.

Normalization is the standard database process that removes redundant columns from a table and puts them in secondary tables that are linked to the primary table by primary key and foreign key relationships.

Row splitting divides the original table vertically into tables that have fewer columns. Each logical row in a split table matches the same logical row in the others. For example, if you join the tenth row from each split table you re-create the original row.

Like horizontal partitioning, vertical partitioning lets queries scan less data. This increases query performance. For example, a table that contains seven columns of which only the first four are generally referenced may benefit from splitting the last three columns into a separate table.

Vertical partitioning should be considered carefully, because analyzing data from multiple partitions requires queries to join the tables. It also could affect performance if partitions are very large.

Replication

Middle tier applications often use a single database to store data, which can cause scaling limitations as the load against the database increases. When applications perform more reads than writes, such as with a Web-based catalog, you can scale out the read portion of the workload by caching read-only data across multiple databases and by connecting the clients evenly across the databases to distribute the load.

To enable access to the data from the Internet you may want to replicate the data from the internal network to your extranet environment. It is important that you do this securely by restricting access to relevant systems, ports and credentials.

Summary Table

The following table summarizes the information in the scalability section.

Table 6: Summary table for scalability section

Design Options for Availability

The design of your data service must incorporate availability features. Availability is not an attempt to design a service that never fails, but you must identify where failures are likely to occur and implement a plan to overcome the failure and continue to provide the service.

The major availability features are:

Failover Cluster

Failover clustering provides high-availability support for an entire SQL Server instance. A failover cluster is a combination of one or more server nodes with two or more shared disks.

You can install applications such as SQL Server and Notification Services into a Microsoft Cluster Service (MSCS) cluster group, known as a resource group. At any given point in time, only one node in the cluster can own a resource group.

The application service has a "virtual name" that is independent of the node names and, for this reason, is referred to as a virtual server. An application can connect to the virtual server by referencing the virtual server name, so the application does not have to know which node hosts the virtual server.

A SQL Server virtual server appears on the network as if it were a single physical server, but has functionality that provides failover from one node to another if the current node becomes unavailable. The clustered data is stored on shared storage that is accessible by all nodes.

There is a single copy of the data that is accessed by the active node. If the active node fails, a passive node becomes active and provides access to the data.

If you need to deploy multiple instances of SQL Server 2005, a node can be active for the first instance and passive for the second. This enables better use of node resources. For example, during an application failure, a non-disk hardware failure, an operating system failure, or planned operating system upgrade, you can configure a SQL Server instance on one node of a failover cluster to failover to any other node in the disk group.

A failover cluster does not protect against disk failure. You should make sure that the data is stored on a RAID array.

You can use failover clustering to reduce system downtime and provide higher application availability. Support for failover clustering exists in SQL Server 2005 Enterprise Edition, Developer Edition and, with some restrictions, Standard Edition.

Figure 1 shows a logical view of the change in data flow of a client request to a failover-clustered database in the event of a node failure.

Figure 1: Data flow through a SQL Server 2005 failover cluster
See full-sized image

Failover clustering is the recommended solution for databases that have read/write access for clients. This is because you make all changes to the same version of the data.

Load Balanced Cluster

Load balanced clusters may provide performance improvements and increased availability. You replicate a copy of the data to each member of the cluster. You then distribute client requests among the cluster members.

Because you have multiple versions of the same database, load balancing does not work well in an environment with multiple updates to the data because these would have to be replicated to every member of the cluster as each change is made. In addition, the maintenance of client state cannot occur in the database, or on the server, in the event of failure.

Load balanced clusters provide availability for read-only data. If one of the cluster members fails, the load balancing hardware or software distributes client requests among the remaining cluster members.

Figure 2 shows a logical view of the change in data flow of a client request to a load balanced cluster database in the event of a node failure.

Figure 2: Data flow through a SQL Server 2005 load balanced cluster
See full-sized image

Database Mirroring

Database mirroring is primarily a software solution that increases database availability by supporting almost instantaneous failover from the primary to the mirrored database. Mirroring keeps the databases synchronized by transporting the logs from the primary to the mirror. The automated failover process is a major difference between mirroring and log shipping.

You can use database mirroring to maintain a read-only mirror database for a corresponding read-write database, called the principal database. If the principal database fails, the mirror database becomes the principal and is read-write enabled.

You can also configure a witness server to monitor the availability of the principal database. In this case, the witness server automatically enables the mirror database in the event of a failure of the principal. If a witness server is not used, it is a manual process to enable the mirror database.

You create the mirror database by restoring a full backup of the principal database without recovery. The mirror database is inaccessible to clients. However, it is possible to use it indirectly for reporting purposes by creating a database snapshot on the mirror database. The database snapshot provides clients with read-only access to the data.

Database mirroring offers a substantive increase in availability over the level that was previously possible by using SQL Server, and offers an easy-to-manage alternative to failover clustering. The advantage of database mirroring over log shipping is that it is a synchronous "no data loss" configuration, which is supported natively as a simple failover strategy.

Each database mirroring server instance requires a unique listener port for mirroring connections. To identify the port for a server instance to use, you require a unique database mirroring endpoint for that instance. A server instance can have only one endpoint for mirroring, and all of the mirroring sessions must use the same endpoint. When system administrators create the endpoint, they specify the authentication and encryption methods of the server instance.

The network address of a server instance contains the port number of its endpoint, as well as the system and domain name of its host computer. Because each server has a distinct mirroring endpoint that uses a unique port, the port number uniquely identifies a specific server instance. This permits multiple server instances on a single server to participate in database mirroring.

Mirroring supports two levels of transaction coordination:

Synchronous

When transaction safety is set to FULL, the session operates synchronously.

To achieve synchronous operation for a session, the mirror server must synchronize the mirror database with the principal database. When the session begins, the principal server begins sending its active log to the mirror server. As quickly as possible, the mirror server commits all of the incoming log records. As long as the partners remain in communication, the databases remain synchronized.

Thereafter, every transaction committed on the principal database is also committed on the mirror server, which guarantees the protection of the data. A transaction on the principal database is not committed until the principal server receives a message from the mirror server that states that it has committed the transaction log to disk. The wait for this message increases the latency of the transaction.

Asynchronous

When transaction safety is set to OFF, the session operates asynchronously. This means the transactions are committed on the primary server before the mirror receives the changes.

This configuration provides a performance increase over synchronous sessions because the primary is not waiting for the transactions to be committed on the mirror before continuing. However, asynchronous sessions result in the mirror server being slightly out of date with the primary until the transaction is committed on both servers.

Log Shipping

Like database mirroring, log shipping operates at the database level. You can use log shipping to maintain one or more "warm" standby databases, called secondary databases, for a corresponding read-write database, called the primary database. You create each secondary database by restoring a full backup of the primary database without recovery.

Your configuration includes a single primary server, one or more secondary servers, and an optional monitor server. The monitor server records the history and status of backup and restores, and optionally raises alerts if these operations fail to occur as scheduled. Each secondary server updates its secondary database at regular intervals from log backups of the primary database.

Before failover can occur from your primary database to one of your secondary databases, you must bring the secondary database fully up to date manually. Secondary databases have limited availability during restores, so they might not be usable for reporting purposes.

Log shipping provides the flexibility to support multiple standby databases. If you require multiple standby databases, you use log shipping alone or as a supplement to database mirroring. When these solutions are used together, the current principal database of the database mirroring configuration is also the current primary database of the log shipping configuration. Furthermore, log shipping permits a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore the log backup.

Replication

You configure replication in a publish/subscribe model, which enables a primary server (the publisher) to distribute data to one or more secondary servers (subscribers).

Replication provides real-time availability and scalability across these servers. It supports filtering to provide a subset of data on the subscriber servers.

Subscribers are online and available for reporting or other functions, without query recovery. SQL Server offers three types of replication:

Snapshot

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, subscribers receive the entire snapshot.

You can use snapshot replication itself, but also use the snapshot process, (which creates a copy of all of the objects and data specified by a publication,) to provide the initial set of data and database objects for transactional and merge publications.

Snapshot replication by itself is most appropriate when one or more of the following is true:

Snapshot replication is most appropriate when data changes are substantial but infrequent. For example, if a sales organization maintains a product price list and the prices updates occur at the same time once or twice each year, you can replicate the entire snapshot of data after it has changed.

Given certain types of data, frequent snapshots may also be appropriate. For example, if updates occur to a relatively small table at the publisher during the day, but some latency is acceptable, deliver changes nightly as a snapshot.

Transactional

Transactional replication typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the publisher are usually delivered to the subscriber as they occur (in near real time).

The subscriber applies the changes in the same order, and within the same transaction boundaries, as they occurred at the publisher. This ensures that there is a guarantee of transactional consistency within a publication.

You use transactional replication in server-to-server environments. It is appropriate in each of the following cases: