Get SQL Server ready for workload spikes

There is no doubt that many e-commerce sites have experienced a high number of vacation visits. For example, the 1-800-flowers.com company (1-800-flowers.com website) has had a dramatic increase in orders on Valentine's Day, Mother's Day, Christmas, Easter, Thanksgiving Day, Secretarial day, and the team's thanks week. And most of the other online retailers are also experiencing the peak of orders that lasted from Thanksgiving until December 26.

Then you can learn from these companies that they need to maintain a high availability, fast running database. Below, I'll discuss several ways you can cope with the rush, following the general concepts and checklists below.

Usability methods

Cluster

High availability typically includes clusters. When you need a higher level of uptime, you need to cluster SQL Server, which consists of several nodes that are clustered together into a single instance of the cluster, which behaves as a single node in the face of the customer. If a node in the cluster is dropped (due to a SQL Server error, hardware error, or maintenance), the other node will automatically take over its workload. Customers do not need to reconnect to other nodes at all because they are connected to a virtual server that floats on top of all active nodes.

Centralization provides automatic error tolerance for hardware and software errors, but it usually does not provide tolerance for local errors (for example, the building of a cluster or the power supply in the room is broken). Attention should be focused on eliminating individual points of failure, such as redundant power supplies or standby generator energy.

Geographic clustering and load balancing

Other high-availability methods include geographic clustering, clustered nodes in different locations, or geographic load balancing, where IP address customers can exchange between major data centers and disaster recovery sites.

EMC, Hitachi Data Systems, and today's Veritas software company, which has acquired Symantec, provides data mirroring of hardware that provides continuous replication so that disaster-recovery sites can guarantee real-time copies of your data. Hardware data mirroring tools can be used to connect to a geo-IP solution and provide automated error recovery for disaster recovery sites.

Reduce size

You can also use smaller scale methods to spread your data across multiple working machines. Instead of having 1000 users connected to a single SQL Server, you can connect 100 users to 10 separate SQL Servers. Your data access pattern must match this, and it doesn't matter which SQL Server the client connects to, or you have to activate the sticky session. In this way, each customer is connected to a single SQL Server within the length of its session.

For example, if you have 10 SQL Servers in your federation that provide classified information, and the data is the same between the 10 SQL servers, it doesn't matter which SQL Server the customer is connected to, and then to which SQL Server to reconnect. The point-to-point application in SQL Server 2005 is designed precisely for this type of scaling down.

Note that SQL Server does not automatically distribute the load to other SQL Servers. You need to balance the network load, spread the load across multiple network servers, and install one or more SQL Servers on each network server in the Federation.

Understanding Workflows

E-commerce companies are preparing their peak season sales throughout the year. The System architect studies workflows to make it easier to understand which processing in a transaction is required, which can be batch processing, and which can benefit from parallel processing of other machines.

Consider the general operation of placing orders. Enter your credit card and verify on the Web page that the number starts with a sequence and that it satisfies a certain length. This step can be done in the browser so that you do not have to occupy the network server's processor cycle. Credit card fees are usually not authorized because network service calls slow down at this point, resulting in a decrease in overall measurable solution levels. If you do not need a network service call to authenticate every credit card transaction, then the E-commerce site can support thousands of, or even more pages. The credit card will be processed in a later batch process.

As shown in the example above, by looking at the workflow carefully, the system architect identifies the processing that can be performed asynchronously, and the overall measurable scheme level increases.