Multiple Server Implementations


Multiple Server Implementations

  • Alter database objects to support replication and partitioned views.

Many high-demand implementations require the use of multiple servers to handle the workload. A variety of options exist for using SQL Server on multiple servers. Partitioning, log shipping, replication, federated servers, and clustering are all potential implementations for using multiple servers and obtaining load balancing.

Using log shipping is a way to have two separate servers that contain the same database split the query load away from the updating of data. The log information is periodically copied to a read-only, standby server that can then be used for query purposes, thereby offloading some of the work from the main production machine. For further information on log shipping implementations, see SQL Server Books Online, "Maintenance Planning Wizard." Microsoft failover clustering provides for availability by enabling a secondary server to take over activities if a primary server fails. For further information on failover clustering, see SQL Server Books Online, "Creating a Failover Cluster."

Partitioning and federated servers provide load balancing for a single application and work together to provide users with better response. This type of implementation is in place of a multiple server cluster where all machines load balance to share the workload. In a federation, each server is completely independent and it is the application and database design that implement the load balancing.

Replication places potentially updateable copies of the same data on multiple servers so that applications that allow for site independence can, at the same time, keep all copies of the data synchronized. Most models have a degree of latency or delay between the initial updates and the moment when all data is in agreement.

Use of Replication

Placing the same data on multiple servers, with each server closer to the user's location, can reduce the use of bandwidth and provide the user with faster update operations and retrieval of data. Replication is discussed in full in Chapter 11, "Implementing and Understanding Replication Methodologies," so this section focuses more or the specifics of designing a system to support the replication models.

These replication techniques can be applied to three replication models, as well as several different physical models. The physical aspects and models have no direct correlation. The replication model supplies the functionality, whereas the physical aspects lay out the placement and roles of individual servers.

Merge, snapshot, and transactional replication all involve essentially the same basic elements to begin with. However, each model has idiosyncrasies of its own that require some thought during the design of the implementation. For further information on replication, consult Chapter 11.

Partitioning to Achieve a Balance

Data partitioning as defined previously involves the horizontal division of a singular table into a number of smaller tables, each dealing with a range of data from the original and split off onto separate servers. Some configuration options can help gain performance when operating against partitions. Setting the Lazy Schema Validation option using sp_serveroption can optimize performance. Attempting to ensure that the correct query goes to the appropriate server also helps to improve performance while minimizing bandwidth use.

A partitioned view is considered to be updateable if a set of SELECT statements is combined into one resultset using UNION ALL operations, as was shown in the section "Partitioned Views." Indexes based on calculated columns are not permitted within any table definitions and all tables must have a Primary key and ANSI_PADDING set.

When you use a partitioned view to insert data, all columns must be included in the INSERT statement, even if the table definitions provide DEFAULTS or allow for NULL content. Also, IDENTITY columns cannot be referenced; therefore, no underlying tables can have IDENTITY columns nor are they permitted to have timestamp columns.

Remote partitioned views require that you keep a few additional considerations in mind. A distributed transaction is automatically initiated to ensure that integrity is maintained throughout all operations, and the XACT_ABORT option must be set to ON Smallmoney and smalldatetime data types in the remote tables are mapped to money and datetime types locally.

Partition Creation Strategy

Partitions can be designed in a symmetric or asymmetric fashion, and although it is most useful to design symmetrically , the access requirements of a lot of systems necessitate an asymmetric design.

A symmetrical design is one in which all related data is placed on the same server so that most queries do not have to cross network boundaries to access the data. It is also easier to manage the data if the division of tables can be performed in such a manner that each server has the same amount of data.

In most real-world applications, data is accessed in a random fashion that can make the designer lean toward an asymmetric implementation. The design can be configured so that one server has a larger role and/or contains more data than the others. Performance can be improved if you weigh each server's use and make one server work harder on the partitioned applications because the other servers perform larger tasks that deal with other unrelated processes.

Designing for distributed partitioned views requires appropriate planning of front-end applications to ensure that, whenever possible, data queries are sent to the appropriate server. Middleware, such as Microsoft Transaction Server or Application Server or other third-party equivalents, should attempt to match queries against data storage.

EXAM TIP

Be Comfortable with Configuration You are likely to find exam questions that will ask you to balance the work load and properly configure partitioned views. Ensure you are comfortable with the configuration required. To find out more information on these and other styles of views you can expect to see, refer to Chapter 7.


Constraint Considerations

Constraints need to be defined on each participating server so that only the data pertaining to the table(s) stored on that server is handled. Although CHECK constraints are not needed to return the correct results, they enable the query optimizer to more appropriately select the correct server to find the requested data.

REVIEW BREAK: Spreading It Out

Multiple server operations balance the load so that updates are potentially separated from queries and query load can be spread across multiple machines.

Partitioned views drastically restrict the underlying table designs and require a number of options to be set when using indexes.

The application of the initial snapshot that begins the entire replication process can be compressed and/or saved to a CD so that some of the necessary communications can be offloaded. Doing so makes more efficient use of network bandwidth in slow-link or dial-up environments in particular.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net