SQL Server

SQL Azure : Sample Design - Application SLA Monitoring

12/13/2010 8:47:54 AM
To put a few of the patterns in perspective, let's create a formal design around a system that monitors application performance service-level agreements (SLAs). In this design, a company already has a monitoring product that can audit activity in existing SQL Server databases at customer sites. Assume that the company that makes this monitoring product wants to extend its services by offering a SQL Azure storage mechanism so it can monitor customers' database SLAs centrally.

1. Pre-Azure Application Architecture

First, let's look at the existing application-monitoring product. It contains a module that monitors one or more SQL Servers in an enterprise and stores its results in another database located on the customer's network.

In this example, Company A has implemented the monitoring service against an existing ERP product to monitor access security and overall SLA. The monitoring application performs the auditing based on live activity on the internal SQL Server storing the ERP data. When certain statements take too long to execute, the monitoring service receives an alert and stores an audit record in the local auditing database, as shown in Figure 1.

Figure 1. Onsite monitoring implementation

On a monthly basis, managers run reports to review the SLAs of the ERP system and can determine whether the ERP application is still performing according to predefined thresholds as specified in the ERP vendor contract. So far, the benefits of this implementation include the following:

  • Visibility. The customer has visibility into its internal database's performance.

  • SLA management. Measured SLAs can be used to negotiate contract terms with the ERP vendor.

However, the customer needs to store the auditing data internally and manage an extra SQL Server instance; this adds database management overhead, including making sure all security patches (operating system and database) are up to date. In addition, the local auditing database running on SQL Server isn't readily accessible to the ERP vendor, so the ERP vendor can't take proactive actions on any SLA issues and must wait to be informed by the customer about serious performance issues. Finally, the customer doesn't know how its internal SLA measures compare to other customers running the same ERP product.

2. Azure Implementation

The monitoring provider has created an enhanced version of its monitoring system and includes an optional cloud storage option, in which the monitoring service can forward performance events in a centrally located database in the cloud. The monitoring provider has decided to implement an asynchronous smart branching pattern so that events can be stored in a SQL Azure database.

Figure 2 shows the implementation architecture that lets the monitoring service store data in a cloud database. Each monitoring service can now store SLA metrics in the cloud in addition to the local auditing database. Finally, the local auditing database is an option that customers may choose not to install. To support this feature, the monitoring provider has decided to implement a queuing mechanism in case the link to SQL Azure becomes unavailable.

Figure 2. Azure monitoring implementation

The monitoring provider has also built a portal on which customers can monitor their SLAs. Customer B, for example, can now use the portal to monitor both its CRM and ERP application database SLAs. The customer can prepare reports and make them available to the ERP and CRM vendors for review online, with complete drilldown access to the statements from the same portal.

In this implementation, additional benefits include the following:

  • Improved sharing. Sharing information with vendors becomes much easier because drilldown access to issues is provided through a cloud-enabled portal.

  • Local storage optional. With the improved solution, customers may decide to implement the cloud storage only if they're short staffed to handle the necessary internal database-management activities.

  • External monitoring. Customers A and B also have the ability to use the monitoring provider to proactively monitor their ERP products remotely with specific escalation procedures when the SLAs aren't met. The monitoring provider can, for example, manage performance issues directly with the ERP provider.

Other -----------------
- SQL Azure : Combining Patterns
- SQL Server 2008 Analysis Services : Understanding the SSAS Environment Wizards (part 2)
- SQL Server 2008 Analysis Services : Understanding the SSAS Environment Wizards (part 1)
- SQL Server 2008 Analysis Services : Understanding SSAS and OLAP
- SQL Azure : Design Patterns (part 3)
- SQL Azure : Design Patterns (part 2) - Sharding
- SQL Azure : Design Patterns (part 1)
- SQL Azure : Design Factors (part 2)
- SQL Azure : Design Factors (part 1)
- Limitations in SQL Azure
- SQL Server 2008 : Performance Data Collection (part 2)
- SQL Server 2008 : Performance Data Collection (part 1)
- SQL Server 2008 : Performance Tuning - Partitioning
- SQL Server 2008 : Guide to the DYNAMIC Management Views (DMVs)
- SQL Server 2008 : Managing Security - Service Accounts and Permissions
- SQL Server 2008 : Managing Security - Security and SQL Agent
- SQL Server 2008 : Implementing Transactions - Transaction Traps
- SQL Server 2008 : Implementing Transactions - Deadlocking
- SQL Azure Primer (part 4) - Creating Logins and Users
- SQL Azure Primer (part 3) - Connecting with SQL Server Management Studio
Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Disable Windows 8 Metro UI

- How To Change Account Picture In Windows 8

- How To Unlock Administrator Account in Windows 8

- How To Restart, Log Off And Shutdown Windows 8

- How To Login To Skype Using A Microsoft Account

- How To Enable Aero Glass Effect In Windows 8

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen
programming4us programming4us
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8