Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Sybase

Posted December 19, 2011

Operational Scalability and Security with Sybase Adaptive Server Enterprise

by Robert D. Schneider

Introduction

Three stark operational realities afflict any enterprise reliant on database technology to thrive in today’s highly dynamic, complex business landscape. First, supporting globalized activities means that there is no longer any “slack time” when maintenance can be performed without impacting users or customers. Secondly, largely in response to the demands of worldwide operations, databases have become true 24x7 resources. These strategic enterprise assets can no longer afford delays or downtime – whether planned or otherwise. Finally, the amount of data under management is mushrooming, and this information explosion is even more daunting when you consider the added burdens of storing and providing historical data. All of these dynamics demand premium operational scalability, which can best be visualized as how well your database infrastructure keeps pace with the obligations of sustaining your daily procedures.

In this article we define the elements that comprise operational scalability, and then provide concrete examples of how Sybase Adaptive Server Enterprise (ASE) augments operational scalability via minimizing downtime, sustaining heavy workloads, and supplying enterprise-grade security.

The intended audience for this article includes executive leadership, line-of-business owners, IT management, database administrators, and anyone else with a commitment to ensure that critical database infrastructure is up to the task of supporting the enterprise.

What Constitutes Operational Scalability?

Before we illustrate how ASE helps amplify operational scalability, let’s first examine what we mean by this term. In contrast with the traditional concept of scalability – which usually describes how well your infrastructure can continue to provide acceptable response times even as load grows – there’s no single factor that exemplifies operational scalability However, there are a number of qualifying questions that can provide a window into where you rank on this key indicator. These include:

  • Are you able to support normal operations without requiring extensive downtime, especially when considering how quickly your information volumes are expanding?
  • Can you cope with large numbers of users without excessive resource overhead?
  • Is it possible to perform wide-scale changes efficiently and effectively, without encountering the numerous errors that so typically occur during repetitive procedures?
  • Do you have the capacity to recover quickly from problems without incurring substantial outages?

How Sybase ASE Supports Augmented Operational Scalability

Now that we’ve painted a picture of what constitutes a database’s operational scalability, let’s examine a series of interrelated ASE capabilities that are meant to boost this important trait.

Minimizing Downtime

Regardless of the exact amount of data you’re managing, one of your prime administrative responsibilities is to ensure that your database infrastructure reduces – or eliminates – downtime. ASE helps lessen database outages via:

  • Fully recoverable data description language (DDL) commands. These directives are used for administrative tasks such as creating new tables, altering existing table structure, and so on. ASE gives you continuous and point-in-time recoverability even for a database that has been subjected to a major DDL change. This means that there’s no need for the time-consuming DUMP DATABASE and log restoration commands if you want to roll back an already-run DDL statement.
  • Reclaiming space. For a variety of reasons, transaction logs may grow to be too large:
    • One-off fixes when encountering full log situations
    • Temporary expansion to handle large operations
    • Storage miscalculation
  • Regardless of the cause, oversized logs consume valuable storage resources. Attempting to recover wasted space has traditionally entailed considerable database downtime. ASE lets administrators reclaim transaction log space with minimal impact on active users.

  • Altering tables without copying data. Adding or dropping a column for a voluminous table can take a long time. Traditionally, these types of operation required the entire table to be copied, which usually meant significant service interruptions and required colossal amounts of extra disk storage. ASE lets administrators make these types of changes without needing to copy the table, which increases uptime and reduces storage requirements. Furthermore, this procedure is independent of the table’s size, so performance is very fast.

Sustaining Heavy Workloads

Just about every organization is experiencing significant increases in the overall number of users as well as their activity levels. Instead of requiring you to spend more money on hardware and other resources, your infrastructure should be capable of dealing with these additional obligations. ASE helps you cope with all this added work through:

  • Query plan latency optimizations. This reduces execution-time overhead, while global caching for fully prepared statements is especially useful when the same prepared SQL text is executed by many different users. At the same time, ASE incurs no optimization overhead for statements that don’t require table access.
  • Result optimizations. ASE generates result set descriptions only once: at compile time, greatly reducing overhead for shared statements. This optimization reduces execution time overhead, which is critical in today's low latency query environments. In addition, no extraneous result set metadata is communicated, which decreases unnecessary network traffic.
  • Transactions and concurrency. Cursor query plans and cursor fetch statements are cached, while developers and DBAs are also granted more control over isolation levels. ASE delivers maximum concurrency by providing enhanced, fine-grained control over lock timing.
  • Shared inline defaults. When defining columns, it’s normal to set a default value for each instance of a column when necessary to enforce a business data domain. Considering that certain enterprise applications can have tens of thousands of columns, the overhead of defining and maintaining these defaults has the potential to negatively impact scalability. ASE shares default values wherever possible, which lessens resource overhead.

Supplying Enterprise-grade Security

Security is becoming increasingly essential as enterprises grow and entrust more critical operations to their database infrastructure. Unfortunately, it’s far too common for vital security tasks to be overlooked when administrators must grapple with cumbersome, often-manual procedures. ASE streamlines critical security responsibilities via:

  • Login profiles. Managing numerous logins can be challenging, especially when considering how frequently organizational security policies change and how long it can take to manually propagate important alterations. ASE lets you define login profiles, which are SQL-defined containers of login attributes and their values, such as:
    • Default database
    • Default language
    • Login script
    • Auditing requirements
  • Users can be automatically associated with appropriate profile(s), which makes it much easier to disseminate far-reaching changes.

  • Object ownership changes. It’s quite common for the creator of an object to hand off maintenance responsibilities. This typically happens because of shifting roles, personnel changes, and outsourced development and maintenance. To help administrators cope with these dynamics, ASE offers DDL syntax that makes it easy to change object ownership.
  • Additional security-oriented capabilities. All of the following characteristics are designed to make it easy for administrators to supply outstanding security. These include:
    • Split knowledge and dual control of keys. No single person acting alone can enable decryption of encryption keys. This protects encrypted data from unauthorized access.
    • Strong password and hidden text encryption. ASE delivers enhanced password security, and is compliant with the US government’s FIPS 140-2 standard.
    • Single sign-on and end-to-end Kerberos credential forwarding. This simplifies activities for users and administrators alike by presenting a unified security experience.

Conclusion

To keep pace with continually increasing workloads, IT organizations must place unprecedented emphasis on expanding operational scalability. Failing to do so is guaranteed to have a deleterious effect on the organization’s ability to complete daily responsibilities within acceptable windows.

By reinforcing database uptime, satisfying amplified workload demands, and securing your valuable data, Sybase ASE is a key ingredient in ensuring that your technology infrastructure is able to meet your information processing obligations.

About the Author

Robert D. Schneider is a Silicon Valley-based technology consultant and author. He has provided database optimization, distributed computing, and other technical expertise to a wide variety of enterprises in the financial, technology, and government sectors.

He has written 6 books and numerous articles on database technology and other complex topics such as cloud computing, Big Data, data analytics, and Service Oriented Architecture (SOA). He is a frequent organizer and presenter at technology industry events, worldwide. Robert blogs at rdschneider.com.



Sybase Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Sybase Forum
Topic By Replies Updated
DB Error Help Prepared Statements sjulian 2 July 5th, 04:58 AM
DB Error Help Prepared Statements sjulian 0 May 31st, 07:19 AM
Test, just a test XRumerTest 0 May 5th, 04:57 AM
Execute Procedure in SQL statement vcs1161 0 August 24th, 07:38 AM