Virtualising SQL Server Analysis Services (SSAS) Databases using Delphix AppData

Introduction

SQL Server Analysis Services (SSAS) deliver online analytical processing (OLAP) for business intelligence applications. Analysis Services support OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as MS SQL Server.

SSAS hosts aggregated cubed data and stores it into a SSAS database.   End users query pre-aggregated data from the SSAS database.  In many cases the SSAS database can satisfy all such queries, but some queries may require access to the underlying SQL Server database.

SSAS Pinch Points

1      Estimates suggest that the primary storage of an SSAS multidimensional database can be about one third size of the original data.

2      SSAS cannot support an unlimited number of concurrent users or queries.  A common technique is to distribute the workload across multiple dedicated query servers, which has many advantages, yet the downside is an inefficient use of expensive storage resources given that each query server requires a separate database copy.  See Scale-Out Querying for Analysis Services with Read-Only Databases

Enter Delphix

Delphix can assist with two scenarios

1      Enable SSAS database generation by provisioning virtual copies (VDBs) of the underlying SQL Server database(s)

This offloads the SSAS generation process from production databases.

2      Scale out access to SSAS databases using AppData provisioning and corresponding MS SQL Virtual Database provisioning.

This allows expansion of access to SSAS data with minimal storage overhead at maximum speed.

How do I do that?

Setup Source Environment (description based on SQL Server 2008 R2)

1. Import AdventureWorks2008R2 sample MS SQL database into a source SQL Server Instance.
2.  Install Analysis Services and Business Intelligence Development Studio on both source and target SQL Server Instances.
3. Run the SQL Server Analysis Services Tutorial on the source SQL Server Instance.

Ingest Sources

1. Ingest the AdventureWorks2008R2 MS SQL database into Delphix
2. Ingest SSAS database, located at “C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP” using AppData (virtual files).  Note since this is a database it may need to be quiesced before ingest to guarantee consistency.
3. Ingest the Visual Studio Project, located at “C:\Users\delphix_admin\Documents\Visual Studio 2008\Projects\Analysis Services Tutorial”, using AppData

Note: files can only be ingested via a server running the Delphix connector.  I used an SMB share to the target server.

Provision Clones (Virtual Copies)

All steps below apply to the target server.

1.  Provision virtual copy of AdventureWorks2008R2 as a VDB, named “vAdventureWorks2008R2”.
2. Stop the SSAS Windows service
3. Rename “C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP”
4. Provision virtual copy of SSAS database onto “C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP”
5. Start the SSAS Windows service
6. Provision virtual copy of the Visual Studio Project, called Analysis Services Tutorial into “C:\Users\delphix_admin\Documents\Visual Studio 2008\Projects\Analysis Services Tutorial”.
7. Using SQL Server Business Intelligence Development Studio, modify the data source connection string to connect to the Virtual Database, “vAdventureWorks2008R2”.
8. Test by selecting Database -> Process -> Run, to prove the cube can be refreshed from the MS SQL database

Complete.

Conclusion

Now you have a virtual copy of the entire SSAS analysis stack provisioned in minutes with almost no storage overhead.  You can collate all three components together into a JetStream container and manipulate them as a single data and application environment.

            

Delphix and Ethernet are the Future

Delphix Virtual Databases on Unix & Linux use NFS

Delphix provisions virtual database copies using NFS for Unix and Linux databases.  NFS is a protocol that uses TCP/IP and requires Ethernet, so Delphix appears like Network Attached Storage (NAS) to database servers.

Occasionally customers ask why we do not provision over Fibre Channel (FC) using FCP which is the network and protocol used by Storage Attached Network (SAN) arrays.  The short answer is because most customers are gradually shifting data traffic workloads to Ethernet for a variety of reasons.

NAS versus SAN (NFS versus FCP)

A key difference between NAS and SAN storage is that with NAS the storage owns the filesystem which provides great flexibility around managing storage allocation.  A SAN presents raw storage to a database server as if it were directly attached.  The database server will deploy a filesystem to the presented storage.   Unfortunately the SAN administrator has little visibility into the utilization of pre-allocated storage which is why its not unusual to find SANs with only 50% utilization because of the inconvenience of resizing and the difficulty in tracking what space is used where.

This rather stunning fact is capitalized on by the growing number of Software Defined Storage (SDS) solutions that pool together storage resources across storage arrays and disks and present them as a single pool of resource.   The SDS abstraction layer can achieve much better utilization by leveraging better insight into the actual storage allocation versus consumption.   It is slightly ironic that one of the promises of centralized network storage was to consolidate storage and yet lack of insight into actual usage continued if not exacerbated the previous underutilization of siloed DAS.  Neither can you share unused bandwidth on a Fibre Channel network, this translates to bandwidth underutilization which is one of the key failings addressed by virtualization.

This leads to another point of irony which is that SANs and their corresponding FC networks are now viewed as siloed technologies.

Bottom line is SAN and Fibre Channel do not play ball in a virtual world.

Hasn’t this debate got form?

A lot of bias against NAS interfaces and use of Ethernet networks for data transport stems all the way back to 2001 when the only real choices were 2Gb FC or 1Gb Ethernet.  SAN was better than NAS back then not because of the protocol itself, but because the transport frequency was higher.

People were also mistakenly concerned about lost writes and corrupted databases which does not happen when using NFS over TCP.

Even though both Fibre Channel and Ethernet bandwidth has improved the uptake of these has dragged far behind.  For example most customers using SAN storage still only use an 8Gb FC connection, even though 8Gb FC has been around since 2008.  10Gb Ethernet has been around since 2002, but most customers still only have 1Gb Ethernet which they use for non data traffic.

My Way is the Highway

You can think of Fibre Channel being like a railway system with specialist vehicles and tracks, with high utilization on the trains – standing room only – but low utilization of the tracks - not enough trains.  On the other hand Ethernet is more like a road system, more connectivity, higher utilization and if you want speed build better roads or move to Germany.

Why make the move?

So apart from being fabulously expensive, difficult to manage and not easily virtualized, why should I change from FC SAN to Ethernet NAS?

•  Ethernet speeds are advancing more rapidly that FC.  Switch vendors are skipping 20Gb Ethernet and offering 40Gb.
• Switch vendors provide FC connectors mainly for “backwards compatibility”
• FC can run over Ethernet
• You only need 10Gb Ethernet for your most important databases, few databases need more than 2Gb of bandwidth
•  If you apply the same network discipline to configuring and managing data traffic over Ethernet as you have done in the past with Fibre Channel then you will have at least comparable performance and security, but better utilization and lower costs.

You don’t have to throw away your SAN.  Delphix is storage agnostic but since SAN is so ubiquitous the majority of Delphix customers use existing SAN storage to underpin Delphix, what we call Delphix enabled storage.

If you are running Oracle

Oracle introduced direct NFS (dNFS) in 2007 with joint development and testing with EMC.   Oracle dNFS can be used with single instance and clustered Oracle databases, it improves performance, availability and scalability of Oracle databases running on NFS mounted storage, whilst reducing system CPU consumption.

Almost all customers that test Oracle dNFS decide to use it thereafter.   Every Delphix benchmark I have been involved with has benefited from dNFS, to the point we can get the same performance from a Delphix virtual Oracle database as from an Oracle database underpinned by the same physical storage.

Summary

The reasons customers are moving to Ethernet for data traffic are precisely in alignment with Delphix goals to simplify data management and lower component and operational costs.

With Delphix and Ethernet you can lower your storage costs, lower your network costs, lower your operating costs and speed up your data management and provisioning tasks.

Acknowledgements

Many thanks to Jeff Browning, the “Oracle Heretic” for some excellent background collateral.  Any mistakes or misinterpretations are of course mine, they are all mine!

Problems with Storage Snapshot based DaaS

Storage Snapshot Cloning Architecture

Customer issues with Storage Snapshot based Cloning

Many customers have mentioned several common problems with their existing storage snapshot based cloning solutions.

To make clones from storage snapshots you must have a full copy of the source database.   It must be a copy because you don’t want to create storage clones on your production storage array and potentially compromise performance.  It must be a full copy because that’s how storage snapshots work.

The major problem here is that you need a mechanism to maintain this full master copy on a different storage array.  Using database vendor specific replication technologies will require you to establish and maintain a variety of solutions if you use more than one database vendor, so most customers use storage replication.  However storage replication does not guard against physical or logical corruption making its way to the replica copy, that might have been avoided using the database vendor replication methods.  In addition we are now locked into the storage vendor for replication, snapshots and clones.

The next issue is that the performance of storage snapshots tends to decline quite rapidly.   Some customers are forced to periodically instantiate new master full copies just to restore performance.   So now we have maintenance, availability and a storage overhead issue.

Finally most storage snapshot solutions do not have automated maintenance workflows to cover the three main requirements for DaaS which are synchronization, governance and provisioning.   For many customers this makes their storage snapshot based cloning solution non-scalable from an operational and performance point of view.

How is Delphix Different

Ø  Delphix is storage agnostic

Ø  Delphix does not require a full copy of the source database(s).

Ø  Delphix automatically synchronizes with source database and retains a compressed de-duplicated copy.

Ø  Delphix can validate the integrity of the Delphix maintained copy.

Ø  Delphix works the same for all supported databases.

Ø  Delphix reduces the workload on the underlying storage by minimizing storage IO.

Ø  Delphix automates the entire DaaS workflow end-to-end.

Ø  Delphix provides self-service interfaces for operational and end-user teams.

Delphix as a smart NFS Server

Delphix AppData over NFS

Delphix is best known for its ability to automatically virtualize entire databases but Delphix can also be used to virtualize filesystem files and folders.  This capability is called AppData which is designed to virtualize application software or any data held in files and folders.

There are two methods of using AppData to synchronise with files and folders.  You can either have Delphix synchronise by periodically scanning a set of folders to capture updates or you can manage the updates yourself by using an AppData mount point.

Delphix can provision virtual storage and present that to a server as an NFS mount point, what we call a virtual mount point, vMount.  You can then copy data into that mount point and because the copied data is managed by Delphix, the data is catalogued, compressed and de-duplicated between copied versions.   Now that Delphix has one or more copies you can provision shared copies of any version of this data to another server over NFS or as a physical copy.

A common use case for this approach is to handle databases that Delphix does not yet natively support.  At the time of writing we support Oracle, SQL Server, SAP ASE (Sybase), Postgres, MySQL and DB2.

For example, Sybase IQ is one of the most commonly used columnar databases used for analytics.   Like many analytics environments it is not usually possible to expand the read capacity because the databases are by nature very large and the cost and time would make provisioning more copies to expand read capacity too expensive.

Using Delphix AppData the process to ingest, govern and provision copies of Sybase IQ are:

1.     Provision storage for IQ backups using a Delphix mount point (vMount)

2.     Restore an existing backup into the vMount, optionally rename.  Initially restore a full backup but subsequently restore either a full, incremental or incremental since full backup.

3.     Bounce the Sybase IQ residing on the vMount to check validity of the restore

4.     Take a Delphix snapshot of the vMount and optionally create a bookmark

5.     Provision a virtual copy of a chosen backup version over NFS to a separate target server

6.     Startup Sybase IQ on the target server running against IQ database files provisioned by Delphix

This method will allow you to save a set of discrete, compressed and de-duplicated versions of your IQ database in Delphix via an NFS mount.

Being a columnar database Sybase IQ gets great compression on its raw data which means the IQ database files are highly compressed.  Delphix will still get some compression on those backup copies, however the main benefits of using Delphix in this way are:

1.     Each subsequent restore is de-duplicated and hence only deltas are held for each backup version.

2.     Full read-write copies of the entire IQ database can be provisioned in minutes, expanding the read/analytics capacity.

3.     All the database maintenance activity on the source IQ database carries on independently of all the virtual copies.

4.     As and when new versions of the source IQ database are copied to Delphix users can refresh their own copies to the latest version or stick with the one they have if preferred.

5.     If desired, Delphix can provision other databases alongside Sybase IQ

Summary

Delphix can act as a very smart NFS server allowing access to private copies of almost any data of any size, near instantly with minimal storage or operational overhead.