SQL Velocity

Scalable Shared Data Base Part 4

Directing Traffic

Scalable shared data base enables the merging of a scale up computer science problem (the loading and reporting of data) with a scale out solution. The scale out solution is made possible because the data is synchronized across nodes as a read only copy. This same scale out approach has been used for years in large web sites.

Each web server in a farm contains the same set of read only data. A technology called load balancing allows the web site to display a single name, say EMC.COM, and direct user connections to one of many web servers hosting the web site. Advanced load balancers can direct new users based on the number of existing connections maintained by each web server. Web servers with low numbers of users receive incoming users until each server in the site is balanced. To ensure a great user experience, the load balancer will return existing users new requests to the same server they have already been using.

Load balancing technology used for web sites is based on network connections. A web browser opens a connection to the load balancing device or service. The load balancer forwards this request to a web server based on appropriate parameters. Simple load balancers rely on a round robin approach:

• Request 1 is directed to server A
• Request 2 is directed to server B
• Request 3 is directed to server A
• Request 4 is directed to server B

These network load balancers can be either a software service, such as a DNS server that sends requests to different addresses, or a hardware appliance. Both software services and hardware appliances can offer advanced features.

• Least Busy – This technology tracks parameters on the server and directs incoming connections based on usage. The most common parameter is the number of network connections already open on a given server.
• Drain stop – Drain stop allows an administrator to gracefully remove servers from the site. When the drain stop is initiated, new connections are not sent to the draining server. Any existing connections are allowed to be closed by the user. As soon as all connections are closed the server can then be maintained without affecting the overall web site.

Wikipedia has an interesting article detailing network load balancers that covers the topic in great detail: http://en.wikipedia.org/wiki/Load_balancing_%28computing%29

The reader should note that I have described load balancing and Web sites, but the article is about integrating SQL server and SQL Server Analysis Services with load balancing. Most hardware and software load balancers are designed to run large web sites. These systems work extremely well! Unfortunately they are not designed with long running queries in mind.

As a security measure, many load balancers limit the time that a given request can process on a given host. An average SQL or Analysis Services query can exceed these timeout values. For this reason I have often used load balancer service from Microsoft called Network Load Balancer (NLB). NLB is easily adaptable to SQL server and Analysis Services. This is not to say that another load balancer won't work, however, configuration specifics for any load balancer other than NLB are beyond the scope of this post. Check with the manufacture of your load balancer for recommended SQL configuration settings.

In the white paper; http://www.emc.com/collateral/hardware/white-papers/h8129-performance-scaling-sql-wp.pdf I described an Analysis services system that uses NLB to load balance three servers that share a single read only reporting volume.

Once these first servers are load balanced then we can really start to scale the solution. Because we are using the storage to replicate changes in the data we can create more than one shared volume. If we attach half of the servers to one shared read only volume (update pair 1) and the second set to a second read only volume (update pair 2) we can update half of the cluster at a time. This will allow the creation of a solution with absolute minimum downtime for data updates.

The solution implementation would look like:

• Perform a data refresh on the factory node.
• Remove update pair 1 from service
  • All clients will now connect only to update pair 2
• Detach the reporting volume from update pair 1 and sync the changes.
• Attach reporting volume 1 to update pair 1 and put update pair 1 into service.
• Now take update pair 2 out of service
  • All clients now connect only to update pair 1 – hitting the latest version of data.
• Detach the reporting volume from update pair 2 and sync the changes.
• Attach reporting volume 2 to update pair 2 and put update pair 2 into service.
• Now all the data has been refreshed. As clients connect – they are directed to constantly refreshed data with minimal or no downtime.

In addition to network based load balancers, a developer can create what I will call an Application Metadata Redirector (AMR). Application based load balancers can take into account the application state of a given server. Why is this important? If a single user creates a large workload on a given server, this single user is only using a single network connection. If a load balancer only accounts for the number of network connections, then a load balancer can inadvertently direct traffic to a server that is already extremely busy!

An AMR can take into account the load that is being placed on the processors and memory of a server, re-directing clients accordingly. Remember that one of the great features of Analysis Services is the cache. Make sure returning clients get re-directed back to servers they have already executed queries against (unless you have executed a data refresh – then it does not matter as the cache will be reset).

 

Unfortunately I am not aware of a commercially available product that will perform redirection for either SQL server or Analysis Services. If anyone knows of such a product please post in the comments.

If you decide to take on the task of creating a customer Application metadata redirector then the following blog post will be a great starting point (thanks Denny!): http://bloggingabout.net/blogs/mglaser/archive/2008/08/15/configuring-http-access-to-sql-server-2008-analysis-services-on-microsoft-windows-server-2008.aspx

It is far easier to build a redirector using HTTP as the access method, as opposed to direct connections from EXCEL.

For more information on setting up NLB:

Windows Server 2008 NLB Deployment guide: http://technet.microsoft.com/en-us/library/cc754833(WS.10).aspx

Scalable Shared Data Base Part 3:

Managing Data Replication

 

The key to Scalable Shared Data Base is role separation. Reporting tasks are moved from the primary processing node to scaling reporting nodes. This is accomplished by means of data distribution. The fully processed cube is copied from the Factor to the reporting nodes. I have used several techniques to accomplish this task, from good old xcopy to my favorite – SAN based replication.

I will fully admit bias in this area, I work for EMC Symmetrix engineering and I get to work with VMAX arrays every day. Our in array replication technology is called EMC TimeFinder (http://www.emc.com/products/detail/software/timefinder.htm). TimeFinder facilitates snapshots and clones. Both technologies create a storage based point in time data copy. The storage (often called a block based copy) copy is accomplished inside the SAN array, with little or no impact to the host. The block copy has two modes; snap and clone.

A snapshot uses a save area to hold changed data. When the first Snapshot is taken the array begins copying each changed data block to the save area. The new data is then written to the source volume. The snapshot can be mounted to any SAN connected host. The SAN array will send data to the snapshot as it appeared at the time of the snap.

The snap draws data from both the save area and the original source volume. This double (or more depending on the number of snapshots in use at one time) use of the source disks can negatively impact performance. Since the entire point of scalable shared database is to separate processing steps and isolate performance, it is desirable to separate and isolate storage performance.

TimeFinder clones fit this role nicely. Clones are full data copies and therefore operations performed against the source volume will not affect the full-volume clone copies.

TimeFinder clones have several copy options, I will not go into all of the different options here, and rather, I recommend the pre-copy option for use with scalable shared data base. A pre-copy replicates all changes to the clone copy, and then fractures the copy. While the copy is ongoing the reporting hosts will lose access to the volume. The alternative is faster access, with the copy operation taking longer and impacting both the source and the clone copies.

This brings up a useful feature of clones, which is the ability to have more than one clone volume attached to a single source volume. The clone copies can be synced at different times. If you need to have less down time, you can attach half of the reporting servers to one clone, and the other half to a second clone. Simply alternate client availability to the servers connected to the clone volume that is being updated (this also works with snaps – one clone can be presented to multiple servers via snapshots).

Microsoft has two technologies that ensure data consistency in both snapshots and clones; VSS and VDI. VSS or Volume Shadow Service <check this> is a Windows service that guarantees file consistency. When used with SQL server, VSS calls SQL VDI. VDI is a legacy service contained within SQL Server that was first designed for tape backups. Both VSS and VDI communicate with a third party provider, in this case EMC Solutions Enabler (SE).

The provider coordinates the hardware operations carried out in the array, and the software operations in Windows and the application. The EMC Providers all depend on the Solutions Enabler application programing interface for full operation.

NOTE: SQL Server Analysis Services writes directly to the NTFS file system and therefore uses the NTFS portion of VSS. For the purposes of scalable shared data base I recommend detaching the cube on the factory nodes to guarantee a consistent split (as there is not a mechanism to ensure the data cashed in cube memory is flushed to NTFS, VSS will only flush NTFS data).

SE is a series of services, applications, and API's provided by EMC for the purpose of administering storage. SE coordinates the freeze and thaw actions of VSS/VDI and the corresponding clone split of TimeFinder in the array. Without coordinating the clone and snap split with the server memory flushing it would not be possible to guarantee consistent data on the clone. Mounting and inconsistent clone would at best result in the DB entering recovery mode, and at worst having an unusable data base.

SE controls the Symmetrix array through executable commands. In the case of cloning the following commands are used:

• Symdg.exe – Command used to create a control group for the SAN source and clone devices.
• Symld.exe – Command used to add the devices to the control group.
• Symclone.exe – Used to manipulate the clones, including initially synchronization,
• Symntctl.exe – Allows Windows to manipulate EMC Disks.

Rather than going through an exhaustive overview of the setup and operation of clone volume in scalable shared data base I have co-authored a white paper that overviews an environment in use at Microsoft adCenter called: "Real practices: Performance scaling Microsoft SQL Server 2008 Analysis Services at Microsoft adCenter" http://www.emc.com/collateral/hardware/white-papers/h8129-performance-scaling-sql-wp.pdf. Pages 13 through 19 detail setup and operation of clones used as part of an Analysis Services Scalable Shared Data Base environment.

For EMC Symmetrix customer's; detailed information about Solutions Enabler TimeFinder can be found by searching https://powerlink.emc.com for "EMC® Solutions Enabler Symmetrix® TimeFinder® Family CLI"

If you are going to implement Scalable Shared Data Base on SQL Server (as opposed to Analysis services), EMC offers the TimeFinder/SQL Integration Module. Search https://powerlink.emc.com for "TimeFinder/SQL Integration Module"

Scalable Shared Data Base Part 2:

Setting up the Scalable Shared Data Base

 

Start setting up Scalable Shared Data Base (SSDB) by answering a question; Is my database compatible with Scalable Shared Data Base? The computer science problem that a Scalable shared data base solves is to distribute data reporting systems. If you have an Online Transaction Processing system (OLTP) that is the backend for a web sales site that just can't keep up with the number of orders you are trying to process, scaling out your DB across reporting nodes is not going to help you. If, on the other hand, you are having problems processing orders and generating business reporting; then gentle reader you have hit the mother lode.

Note: Scalable shared data base supports both SQL Server (2005, 2008, and 2008R2) and Analysis Services. The major difference between the platforms is that SQL Server has native support for Microsoft VSS and VDI. VSS (Volume Shadow Copy Service) and VDI (Virtual Backup Device Interface) are Microsoft technologies that coordinate the process of making consistent point in time data copies (more on this in Part 3). Analysis Services relies on NTFS implementations of VSS. This makes the data copy process more challenging on Analysis Services than SQL server.

From msdn; requirements for Scalable Shared Data Base:

http://msdn.microsoft.com/en-us/library/ms345392.aspx

Scalable shared databases have the following limitations:

• The database must be on a read-only volume.
• The data files are accessible over a SAN.
• The databases are supported by Windows Storage running only on Windows Server 2003 SP1 or later.
• We recommend that you limit your scalable shared database configurations to eight server instances per shared database.
• Scalable shared databases do not support database snapshots.

Ensuring a Correct Environment for a Scalable Shared Database: http://msdn.microsoft.com/en-us/library/ms345523.aspx

My good friend over at Microsoft SQL CAT, Denny Lee, collaborated on a white paper that is required reading for anyone contemplating setting up a Scalable Shared Data Base:

http://sqlcat.com/technicalnotes/archive/2008/10/21/reporting-services-scale-out-deployment-best-practices.aspx

Planning the Shared Reporting install will require the following:

• Network Load balancer: This can be hardware or software (NLB). More on this in Post 4
• One Factory Server: This server will handle write operations. Scalable Shared Data base offers an excellent scale out solution for reporting. The Factory server is the exception. It remains a scale up solution. Make sure the Factory has enough memory and processing power to complete data processing with time to spare. It is a wise idea to only occupy less than half of allotted processing time with actual processing, lest errors put the system in a state where it can never catch up.  Have a plan that enables re-processing of data, lest errors be detected.  Not being able to catch up after an error is a very bad thing!
  • One other word about servers. Processing data is all about moving bits. The speed and bandwidth of memory and system busses can be much more important that the number of processors or the processor speeds. If your Factory can't move data as fast as the processors can consume it, then you have a bunch of expensive processors spinning while waiting for data.  Do your homework!
• At least two identical reporting nodes: You could use one node, but that would defeat the purpose of having a scale out solution.
• We are going to cover data consistency in part 3. Windows caches data about file systems in memory. In order to tell windows that underlying data has changed on a read only volume (think about this – a read only volume does not change) the volume needs to be taken offline and brought back online again.
• A reporting Data Base, or Data Cube, that lends itself to point in time processing. The process of taking a volume offline, updating data, and bringing the volume online again does not happen instantly. If the system needs to be constantly processing data and reporting in real time, or near-real time, Scalable Shared Data Base may not be optimal.
• Analysis Services makes a really great candidate for Read only Scale out. The Analysis Services Factory can spend its time loading data and processing aggregations. The reporting servers will quickly answer aggregated questions, while being able to process queries that are not covered by the aggregation.
• SAN storage: Here is my shameless plug. I work for EMC Symmetrix Engineering. As you will see later in post 5, the VMAX array really shines at random I/O (shared db's are all about random I/O).
• Data Replication: My second shameless plug. Time Finder Clones for VMAX are ideal for replicating lots of data in minimal time with little or no server impact. See Post 3.

Hardware P.O. firmly in hand, we will now vault on to the minutia of moving data; On to Part 3!

Scalable Shared Data Base Part 1

Processing data is like driving a truck. Assume there are no posted speed limits, our example truck can only travel at 50 miles per hour. If a given destination is 100 miles away and the truck can only reach the maximum 50mph it is going to take two hours to reach the destination. If you need to reach your destination faster you might spend money to buy a bigger truck, or you might be able to split your load into smaller faster trucks. Having the load split would enable all of the trucks to cary more cargo in the same time.

Think of data processing as a truck. There are only 24 hours in a day. Once processing time exceeds the processing time window then we have a problem. One simple solution to this problem is to simply throw more hardware at the problem. Add more processors and ram to a server. Make the storage faster, and buy more of it. This solution is called scaling the compute platform up.

Eventually the data processing problem will grow so large that a single server platform can no longer grow to accept more processors and ram. Just like a truck that can't possibly go any faster, the only solution is to break up the load. Add more servers until the given problem can be completed in the allotted time window. There are many computer science problems that can be solved using this scale out approach. Unfortunately, scaling out a data processing (also known as distributed processing) system will not solve every problem.

One excellent solution to this performance problem is a shared disk approach. Each server would have instant access to every memory register and disk block located within the processing grid. Unfortunately this approach can be just as expensive and as impractical as a scale up approach. How does the application in this scenario keep data consistent across processing nodes? What happens in the event of failure?

Microsoft SQL server uses a shared nothing architecture to ensure data consistency between servers, memory, and disks. Only one server has access to a data file at a time. SQL server only sends and receives data via the SQL engine using the server network. SQL first writes data a log file and into server memory. Data is written from server memory to physical disks on a regular basis. This process is known as flushing unwritten (or dirty) data pages from server memory to physical disks, therefore ensuring data consistency. Other servers are prevented from having simultaneous access to a data file because this access might miss data changes in held in server memory, but not yet written to disk.

Fortunately there is a solution to this thorny problem of sharing data between nodes. Microsoft has engineered SQL 2005, 2008, and 2008R2 with a technology called scalable shared data base (SSDB). SSDB allows a read only data copy to be mounted to one or many secondary reporting servers.

The SSDB hybrid approach allows processing to be completed on a single scale up node while reporting can be separated onto one or more reporting nodes.

 

The following posts will cover the implementation of Scalable Shared Data Base in detail:

Part 1: Scalable Shared Data Base overview (what you are reading now).

Part 2: Setting up the Scalable Shared Data Base (next week).

Part 3: Managing Clones

Part 4: Configuring a load balancer

Part 5: Performance Considerations

For more information on Scalable shared Data base check out:

MSDN: Deploying a Scalable shared data base SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms345584.aspx

SQLCAT: Scale-Out Querying for Analysis Services with Read-Only Databases http://sqlcat.com/whitepapers/archive/2010/06/08/scale-out-querying-for-analysis-services-with-read-only-databases.aspx

Choice and Confidence for SQL Server

A quick note!

My team here at EMC has a brand new page dedicated to Microsoft SQL Server:

http://www.emc.com/campaign/global/sql-server/index.htm

There is an excellent best practices guide to SQL Server running on EMC Symmetrix Storage Systems:

http://www.emc.com/collateral/software/solution-overview/h2203-ms-sql-svr-symm-ldv.pdf

We work extremely hard to provide guides that help you implement excellent systems.  Be sure to post any topics you would like to see us write about, or any issues you are having!

Happy Reading,

Mike

SQL Data Layout Best Practices

Hello and welcome to the first installment of SQL Velocity!

 

Over the coming weeks and months I will be documenting best practices for high performance database installations.  The focus of this material will be running SQL server on EMC Symmetrix arrays. 

 

This first installment covers separating SQL Functions to optimize storage I/O.  For the purposes of this installment we will separate databases into three basic types:

• Online transaction Processing (OLTP)
• Data Warehousing
• Analysis Services (OLAP Cubes)

These database types are separated because they generally access their data files in very different ways. 

• Online Transaction Processing systems are assumed to issue random read/write I/O to their data devices.
• Data warehousing systems (often called Business Intelligence systems) often load data into the data warehouse using sequential writes, and access the data using Sequential read I/O’s
• SQL Services Analysis Services (SSAS) utilizes the NTFS file system directly.  SSAS reads and writes these many files in a highly random pattern.  Many times SSAS will randomly read many files while creating new files sequentially.  SSAS can be an excellent candidate for high performance Enterprise Flash Drives.

Each database utilizes four distinct file types.  Each file type has its own data access pattern:

• Data - Each different type of database performs either random or sequential I/O access patterns, as noted above.
• Log - Log access is mostly sequential.  The database writes data to the log, and then writes the data into the database.  Note that SSAS does not use Log files.
• Temporary database (TempDB) - TempDB is used when the system needs to perform temporary data base operations.  TempDB access is assumed to be highly Random.  SSAS does not use a TempDB device.  As a side note, TempDB can often benefit from high performance Enterprise Flash Drives (EFDs).
• Backup - Database backup files should be stored on a separate backup volume.  Backup devices are accessed sequentially.

Best practices:

• Aggregate Random I/O:  Random workload disk drive performance is based on rotational latency and seek times.  For example, a 10,000 RPM drive spins 166 times per second.  It takes some time to physically move the drives read/write head (this is measured as seek time) therefore I conservatively use 100 I/O’s per second (IOPS) to calculate how many drives are needed to support a given work load.  If I need to perform 1000 IOPS then I need 10 drives (Note:  There are a significant number of variables in these calculations; that data is beyond the scope of this post).  You will find that issuing I/O serially will not be able to achieve this performance threshold.  In order to reach optimal performance many I/O’s should be issued to the RAID set (the number of I/O’s the system can sustain is based on many factors, again beyond our scope today).
• Separate Sequential I/O:  A single physical hard disk can perform about 10 times the number of sequential IOPS as random IOPS.  Sending two sequential data streams to the same drive becomes a random operation thus conceivably reducing performance by a factor of ten.  By isolating sequential operations, fewer drives are needed.  Using the same 10 drives listed above; sequential access will yield 10,000 IOPS vs. 1,000 random IOPS, based purely on performance requirements. Separating two sequential files onto 4 total drives will yield 4,000 IOPS vs. only 400 IOPS when the sequential operations are combined.

Based on these rules the log and backup devices should be isolated. 

That sounds really easy.  Dedicate a few drives to a small DB log file and a whole lot of drives to the backup drive, ensure that nothing else shares these drives and you are home free.

Note:  There is one other really good reason to separate database functions and that is Business Continuance.  If all data base functions are placed on the same physical disks then one disaster can render all data lost.  If, on the other hand, the backup and log files are on separate physical media there is a better chance at recovery. 

Imagine that someone manages to drive a forklift through your storage array.  If you separate the backup and log files then there is an excellent chance of recovering all data.  Since the SQL Server instance writes to the transaction log before updating the database, keeping the log files along with the backup files will enable a full data recovery.  Shame on the fork lift driver – lucky for you – you planned for that.  Planning for your datacenter falling into the ocean, catching fire, or losing power; well you should implement remote storage replication, but that is a topic for another time!

This is where things get a bit complicated.

One easy way to separate I/O should be to simply create different storage devices (called Logical Units or LUNS), and on each of those LUNs create a single separate NTFS volume, such as the following:

• E$ - Backup
• H$ - Data
• O$ - Log
• T$ - Temp DB

A Symmetrix array has two technologies that greatly enhance performance:

• Hyper Volumes
• Virtual Storage (often called virtual provisioning).

While these technologies will enhance performance they can make it more challenging for the average user to clearly identify where their storage actually resides.

The Symmetrix array splits each physical disk into pieces called Hyper Volumes.  The hyper volume offers an excellent performance boost over using a single physical disk.  Using 400GB physical disks a single volume of 1 terabyte would only utilize 8 physical disks (assuming that the disks are configured using RAID mirroring).  Utilizing 50GB hyper volumes this same 1TB volume can be spread across 40 mirrored volumes.  This offers 5 times the performance when compared to utilizing the basic 8 disks.

Figure 1: Hyper volumes

Understanding a specific volume layout is important.  An administrator may separate data files onto unique volumes, only to later discover that the underlying storage is amalgamated.

Figure 2 shows an example of SQL server sharing all Physical Disks.  This configuration will induce a condition known as excessive head seek.  This causes the disk read write heads to move all over the disk surface whenever an operation is initiated.  A worst case example would be running a production backup.  The backup operation will cause the disk heads to move rapidly, back and forth, as data needs to be read from the Data and Log volumes and written to the Backup volume.  This head seeking will cause I/O that is much slower than random I/O.

Figure 2: Poorly configured SQL layout

Whenever possible do not allow a SQL data file to “wrap” back onto the same physical disk.  Figure 3 is an example of a configuration that will cause excessive head seeking.  Ensure that enough physical disks are allocated to each volume so that it can support the necessary I/O load.  Balance highly utilized hyper volumes with less frequently accessed data.

 

Figure 3: Data volume wrapping

Many Symmetrix deployments will take advantage of Virtual Provisioning, also known as Thin Provisioning.  A thin data pool can greatly improve storage utilization and work significantly increase performance.  Ideally the administrator may create one pool for data and temporary data base files, and a second pool for log and backup files. 

Best Practices

·     Utilize SQL best practices to separate data files.  Visit www.SQLCAT.com for best practices from the Microsoft SQL Customer Advisory team and www.EMC.com/collateral/software/solution-overview/h2203-ms-sql-svr-symm-ldv.pdf for our EMC SQL server best practices tech book.

·     Using Standard Hyper Volumes:

o   Ensure that heavily used SQL volumes are paired with less frequently accessed data (such as an archive). 

o   This will dedicate maximum performance to the SQL workload, while ensuring storage capacity is well utilized.

·     Using Virtual Provisioning:

o   When possible create enough thin pools to ensure that backup and log data are separated from the database files.  This should boost performance and add an extra layer of reliability to the design.

o   Size the thin pool for performance and capacity.  Add enough physical disk resources to a thin pool to absorb the entire peak IOPS load that will be generated.  I like to use a worst case scenario, assuming all I/O is random, to determine the number of physical disks that are needed in every pool.

o   Balance data files that have high performance requirements with less frequently accessed data.

·     For the Symmetrix array:

o   Ensure that workloads are balanced across

§  Physical Disks

§  Disk Groups

§  Front End Ports

Figure 4: Well-balanced configuration

I hope you enjoyed installment number one.  Please feel free to post feedback, good or bad!

-Michael