Databases

Manage your data

Growing data stores have resulted in the creation of sophisticated applications that give a wide range of options to DBAs. By Kushal Shah

The growing use of enterprise wide applications (EWA) in areas such as materials management (supply chain), network assets management, revenue management, human resources management, management information system, and geographical information system makes it mandatory to have a robust database application. Retail, telecom, BFSI, healthcare and defence are all big adopters of this software. The Indian market for databases has been valued at $101.7 million by Gartner for 2005. It grew by 15.8 percent from 2004 to 2005. The research firm is yet to announce the figures for 2006.

EWA developers need databases to support their product and large organisations have multiple needs to store multiple types of data. A typical company has five to 20 different content management systems and repositories. Hence they look out for different solutions for different needs. Over the years, databases have matured to be more than just pure ‘database’ offerings to data platforms. Database application developers such as Oracle, IBM, Microsoft and Sybase offer a wide range of services in this area. Apart from these paid services, free ones are also available on Open source platforms.

Information on demand is about getting the right information to the right people or processes at the right time to act on emerging opportunities and competitive threats. It is about creating business value and reducing risk by integrating, analysing and optimising all types and sources of information throughout its lifecycle to address the customer needs.

From a transaction point of view, the most important considerations for a database are performance and reliability. When looking at database platforms, and making choices around selecting a database, it’s very important to look at the functional requirements within a database that the customer will need, and match that to what the database delivers right from the start, without requiring additional customisation by a professional services team. If the choice of database also enforces a concomitant requirement for specialised skills in consulting around the technology, the customer is then forced into a situation where the amount of engineering required for the application will only increase.

Another key area of separation is the ability of the technology to provide data services within the database platform, so as to be able to disseminate information within different applications without requiring additional consulting efforts. This prevents data islands from appearing.

Securing data

Data security is among the most important aspects of using a database. Databases come with a slew of features vis-a-vis security. A database should not give access to unauthorised people. Features like data vault secure data from unauthorised access even within an organisation. One way to secure a database is to secure backups. “Backups that are secured cannot be accessed by anyone as a secure backup has many dependencies. It will not be protected by one password or one encryption- decryption method but a set of such parameters. Only a person who can solve all the dependencies can access the files,” explains Seema Ambastha, Director - Sales Consulting (Database Technologies), Oracle India Private Limited.

Auditing is another way to secure your data which is useful for organisations with large databases. Encryption is another way of safeguarding important data. According to Ambastha it is best to have encryption built into your database application and not as a standalone service since it’s the biggest additional overhead on the hardware. Some other methods are fine grained control and labelled security which is used in defence circles for maintaining confidentiality.

Most of Sybase’s clientele (the Lehmann Brothers, Fidelity etc which are even based in India) is based on Wall Street forcing the company to have comprehensive security tools implemented within its product. It comes with column level encryption, encrypted backups, separation of roles from logins (Sybase has had a ‘dbo’ role within the database since 99), and FIPS-certified algorithms to protect data from intrusion.

Microsoft SQL Server on the other hand an also do digital signing of procedures and code blocks using certificates which can be generated locally or bought from a third party. “We have deep integration for CLR (Common Language Runtime) and we also cascade the CAS (Code Access Security) for the code written in .NET. SQL Server has a reduced surface area—creating a fortress against any attack,” says Pallavi Kathuria, Director - Server Business Group, Microsoft India. SQL Server supports industry standard security algorithms such as DES, Triple DES, AES, and RSA when using keys (symmetric and asymmetric keys) in the database.

Kaushik Bagchi, Country Leader, Information Management Software, IBM India talks about label based access control (LBAC) which comes with DB2. He says, “Label Based Access Control lets you decide exactly who has write access and who has read access to individual rows and individual columns. LBAC controls access to table objects by attaching security labels to them. Users attempting to access an object must have its security label granted to them. When there’s a match, access is permitted; without a match, access is denied.”

Then there are tools such as table partitioning by which data can be spread according to a security-based partitioning scheme; multi-dimensional clustering which can be used for physical separation of data from different security levels; and data partitioning used for highly sensitive data.

Recovering data

What happens to data when a sudden hardware or software failure occurs? It has to be restored in such cases, preferably without needing to resort to the usage of third party software applications. Most databases come with inbuilt data recovery facilities.

“Auto recovery functionality is an integrated part of the Sybase Database solution offering. Any steady state recovery to crash recovery doesn’t require user intervention,” says Arun Ramachandran, Head- Presales and Professional Services India and Subcontinent, Sybase. Recovery is considered as an integral part of a database system.

The DB2 High Availability Feature provides 24x7 availability for your DB2 data server through replicated failover support and data recovery modules.

“This feature consists of the High Availability Disaster Recovery (HADR), the Online Reorganization feature and IBM Tivoli System Automation for Multiplatform (SA MP). This gives a failover package for mission critical applications,” says Bagchi.

HADR allows failover to a standby system in the event of a software or hardware failure on the primary system. Online Reorganization reconstructs the rows in a table to eliminate fragmented data and compact information for better performance while permitting uninterrupted access to the table data. SA MP provides high availability by automating the control of IT resources such as processes, file systems, IP addresses, and other resources. It can coordinate the automatic failover to a standby DB2 data server using HADR.

Efficient recovery options will benefit the company in more than one form. It enables organisations to meet strict service level agreements; no data is lost during infrastructure failures, gives continuous table access through disaster strikes, and provides autonomic reconstruction of data on revival and automatic failover co-ordination with data servers.

If we look at such services provided by Microsoft, they come in with about four options—auto recovery. Database mirroring, log shipping, replication and clustering.

Database Mirroring supports a HA and DR at a Database level. It gives support for automatic failover with applications connecting to the primary database automatically connecting to the mirrored database in case of a failure. This supports zero downtime and zero data loss with inexpensive hardware.

Using log shipping, changes occurring at the DB level are shipped to multiple sites. It creates more than one site as a backup site. This can be timed at regular intervals with support of backup time window, copy time window and apply time window.

Replication keeps the same data in sync in multiple locations; it works at the table level. Multiple tables can be published as articles and can be subscribed to either with a pull or push subscription. The primary use is for reporting from a subscriber copy and reducing server load on the main server. This also supports for a peer-to-peer topology of having more than one site having copies of data of other sites and also replication over https where there is no dedicated LAN and WAN connectivity.

Multicore and parallel computing

To augment processing power, organisations are continuously using multiple processors by implementing multi-core architectures. Parallel computing is becoming increasingly useful for faster resolution of activities. Most databases fully support both these features in their own ways using different technologies.

“SQL Server maintains a single-process, multithreaded architecture that reduces system overhead and memory use. This is called the Symmetric Server Architecture. SQL Server also supports multiprocessing at the thread level rather than at the process level, which allows for pre-emptive operation and dynamic load balancing across multiple CPUs or cores,” explains Kathuria. Oracle has features such as parallel query, parallel indexing and data loading for parallel computing which have been supported for the past two editions.

“We support multi-core processors by treating each core as a separate processor. This leverages the capacity of the hardware platform exactly as intended by the hardware vendor,” says Ramachandran.

These two features are absolutely essential in this new age of computing environments of high power.

Interoperability

Companies have spent millions to support this initiative of exchanging information across platforms, be it operating systems or databases. This is an essential feature for any organisation. Not all the machines in an organisation use the same operating system and different divisions might be using different databases; but all have the common need to exchange information.

According to IBM a typical company has five to 20 different content management systems and repositories. So there has to be a feature which enables working across the platforms. “In 2006, IBM announced a $1 billion investment and dedicated 15,000 business consultants in support of a cross-company initiative to capture an emerging growth opportunity around helping customers innovate by using information as strategic assets,” says Bagchi.

Since Oracle is ODBC compliant, it doesn’t matter which databases are used; you can easily work with other databases. On the fusion database front, Oracle provides the facility of integrating databases from various vendors such as Sybase, Oracle, IBM or Microsoft with the help of an integration tool. “One interesting feature we provide is that of transportable table spaces. If one wants to move from Linux to Solaris, traditionally a backup is taken and then data is moved. In this case, data has something called as metadata and we wrap it in such manner that it can be interpreted on any platform,” explains Ambastha.

Sybase provides a feature called as Component Integration Services, which allows Sybase users to view data stored in other databases as though they were part of the Sybase database. In addition, it allows data stored within Sybase to be replicated or federated to different databases on different platforms.

Web services and SOA

In today’s IT environment, SOA has great relevance from a database perspective. As more companies move to adopt SOA, delivering consistent information to business processes is emerging as a new challenge. Some organizations are finding that inconsistent views of data and even inconsistencies in how data is derived can put their SOA projects in peril. In order to keep SOAs up and running, databases need to support this concept.

“We have looked at SOA as something that will help one to manage their integration component in that complex enterprise environment easily. We have provided one complete set of service infrastructure component for building. You can build, deploy and manage through the SOA suite,” says Ambastha.

Sybase fully supports web services and offers a full framework for Service-Oriented Architectures, thru our various offerings.

To facilitate support for SOA, Microsoft SQL Server has features such as HTTP endpoint (for connection as Web service), service broker (asynchronous messaging model), XML Support, SQLCLR (for extensibility), event notification (monitor database changes).

Future technologies

All the database vendors are continuously trying to do something new at regular intervals to add value to their services. All have innovative ideas taking birth in their labs. Sybase is planning to come with some new features bundled such as grid computing, fully autonomous query processing, improved tooling for DB management; and new additions to our unstructured data storage and search facilities.

On the other hand SQL Server 2005 service pack 2 launched with some of their new offerings such as Data Mining Add-ins for the 2007 Microsoft Office system to enable data mining functionality from SQL Server Analysis Services (SSAS) to be used directly within Excel 2007 and Visio 2007; SQL Server Reporting Services (SSRS) compatibility with Microsoft Office SharePoint Server; Interoperability improvements which include Oracle support in the report builder feature enable customers to use its functionality on top of Oracle data sources. Customers also have access to SQL Server Reporting Services to build reports on top of Hyperion’s Essbase cubes; maximum flexibility in the use of virtualisation technology by allowing unlimited virtual instances.

Microsoft is coming up with a next major release which is currently codenamed “Katmai”.

Oracle is planning to come up with change data capture; a service that will allow real-time warehousing features. To internally benchmark the performances, they are developing a service called as workload capture. This will be part of their future deliverables.

DB2 9 “Viper” data server will be one of the next releases to transform static database technology into an interactive data server that will improve the ability to manage all kinds of information. IBM Information Server will come as a new software platform that allows clients to deliver trusted, consistent, and reusable information to applications and business processes. They are also coming up with IBM OmniFind Yahoo! Edition; free, downloadable search software built on open source technology that helps businesses easily find and use information stored within their company and across the Web.

Features will keep coming, but the question is that of satisfying all the needs of the user. Technical barriers are reducing day by day for companies to do better. It is up to the DBAs to choose the right one out the bunch of highly technically advanced applications available for securing most expensive asset of any organisation, information.