Minimizing Database Outages with Replication

A primary goal of most organizations is to minimize downtime and improve the availability of their IT systems and applications. According to a recent study on achieving 2018 database goals by Unisphere Research, “Enterprises seek to base most, if not all, decision making on insights drawn from data, which means there is no margin for error if the flow of that data is ever disrupted.” This means that DBAs are charged with keeping databases up and running to meet the business need for non-stop access to data.

If the DBMS is down, data cannot be accessed. If the data is not available, applications cannot run. And if your applications cannot run, your company is not making crucial decisions, or worse yet, making decisions without the data to back them up. At any rate, the Unisphere study indicates that customer service will suffer the most when a database goes down (cited by 74% of data managers).

All of these things can translate to losing business, which means lower earnings and perhaps even a lower stock valuation for your company. These are all detrimental to the business and therefore, the DBA is called upon to do everything in his or her power to ensure that databases are kept on line and operational. One respondent to the Unisphere study said ““Management already considers databases to be an integral part of the infrastructure that is always up. Any downtime reflects poorly on the DBA team and time (however much) must be put in to restore the systems until they are up again.” Does that sound familiar?

Assuring availability becomes that much more difficult as organizations expand the amount of data that is generated, captured, and managed. Although putting an exact figure on how fast data is growing can be difficult, there are surveys that show the growth of data on premises to range between 10% and 50% for most organizations. And that does not even take into account cloud data, which is growing rapidly and still must be managed.

Further complicating the issue is that fewer DBAs are being asked to manage more data. Although data continues to expand that is not translating into additional DBAs being hired. According to research firm Computer Economics, data management staff as a percentage of IT staff has risen only .5% in four years, and IT spending per user continues to decline.

Nevertheless, DBAs are required to provide uninterrupted availability for many, probably most of their systems. There are many tactics at the DBA’s disposal to improve data availability and performance. One of the most powerful tools for supporting availability is data replication.

When data is replicated, it is stored in more than one site or node, thereby improving the availability of the data. With full replication, where a copy of the whole database is stored at every site, rapid failover from a failing system to an operational one can be achieved to avoid outages. You can also implement partial replication, whereby some fragments of the database are replicated and others are not.

Replication improves the reliability of your applications because your data will be replicated across multiple machines, instead of on a single machine (aka single point of failure). With replicated data you may be able to achieve improved performance with parallel access. And by locating replicas closer to where transactions are executing, data replication may be able to decrease the need for data movement.

Nevertheless, implementing data replication is not free. Concurrency control and recovery techniques tend to be more complicated and therefore more expensive. And in some cases you may need to buy additional hardware to support it properly. That said, replication can be cost effective because it is much more likely that the cost of an outage on mission critical data and applications will dwarf the cost of data replication.

Of course, data replication is not a panacea. You cannot stop taking backups or cease performance management efforts by replicating data. For example, consider a human error that pushes the wrong data to the database. The replication system will dutifully replicate the wrong data out to all replicated nodes. So you can’t rely on replicated data to recovery from such a scenario. I mention this not because it is a fault of data replication, but because some folks wrongly think that replication solves recovery issues.

The Bottom Line

So the bottom line here is that organizations are adding more data and relying on that data to be available around-the-clock. DBAs are charged with assuring the availability of database data and cannot be caught having a hardware error cause a prolonged outage. Replication, therefore, is a useful technology for ensuring that downtime is for vacations, not databases!

The days are gone when organizations enjoyed a long batch window where databases could be offline for extended periods to perform nightly processing and maintenance tasks. Replication and intelligent failover is the modern way to eliminate unplanned downtime.

Advertisements

Supporting Multiple DBMS Products with Navicat Premium

DBAs are tasked with various and disparate responsibilities that consume their day. I’ve written DBA roles and responsibilities (What Does a DBA Do?) here on the blog before, so if you need a refresher, check out the link and meet us back here to hear about automating and simplifying database administration.

With such a varied list of duties, automation of DBA tasks makes sense. Add to the mix that many DBAs are required to support multiple DBMS products and the job becomes more complicated. As such, most DBAs develop scripts and jobs to automate daily tasks. But in-house developed scripts require maintenance and upkeep to stay current.  And some tasks are quite complicated to requiring a lot of effort to develop and maintain. For this reason, and many others, many organizations groups rely on DBA tools that are developed, maintained and supported by vendors dedicated to keeping the software current and efficient.

One such tool worth considering for automating and simplifying a myriad of database administration tasks is Navicat Premium from PremiumSoft CyberTech Ltd. The tool is available for Windows, macOS and Linux.

Navicat Premium provides support for simplifying a plethora of DBA tasks from database design through development and implementation. And it supports a wide range of different database management systems, including MySQL, MariaDB, Oracle Database, PostgreSQL, Microsoft SQL Server, SQLite and multiple cloud offerings (including Amazon, Oracle, Microsoft, Google and Alibaba). You will need to do some verification of specific features as supported features and platforms vary a bit for specific database systems and operating systems.

Perhaps one of the best things that Navicat Premium brings to the table is an easy-to-use GUI that enables users to connect simultaneously to MySQL, MariaDB, SQL Server, Oracle, PostgreSQL, and SQLite databases from a single application. If you are a DBA that has to manage multiple different DBMS products and installations then you know just how handy that can be. Unfortunately, Navicat does not offer support for IBM’s Db2, so keep that in mind if your organization uses Db2.

More importantly, however, is what you can do using Navicat. Let’s start at the beginning, with database design. Using the Navicat Premium intelligent database designer DBAs can create, modify and manage all database objects with professional built-in guidance. The tool provides physical database modeling capabilities complete with forward and reverse engineering capabilities. That means you can go from model to DDL or from a live database instance to a model. Using Navicat Premium’s graphical database design and modeling tool provides DBAs can model, create, and understand complex databases with ease. Automatic layout capabilities make it easy to create and print readable and useful data models, including to PDF files

And Navicat Premium makes populating and moving data to and from your database a snap. Data can be imported and exported to and from most of the formats DBAs use regularly including TXT, CSV, XML, JSON, DBF, and ODBC. A data viewer and editor make it possible to view and edit the data in your databases using a grid view or a form. This comes with the ability to filter the data as needed and to find/replace data in the table as needed. And you can even navigate and select data as needed based on your referential constraints.

It is important to note that Navicat Premium adds intelligence to data movement, as DBAs can compare and synchronize data between databases and schemas with a detailed analytical process. This means that DBAs can quickly and easily compare the structure of two database instances to identify differences and generate the needed DDL to make them identical. But it is not just for the database structures, Navicat Premium also lets the DBA compare the data, too, so that you can reliably ensure that two tables that are supposed to have the same data, actually do have the same data!

Furthermore, Navicat Premium facilitates database backup and restore for your availability and recovery requirements. That means DBA can use Navicat Premium to design, populate, and synchronize database structures.

Many organizations are adopting cloud databases these days and Navicat Premium can be used to manage cloud databases like Amazon RDS, Amazon Aurora, Amazon Redshift, SQL Azure, Oracle Cloud, Google Cloud and Alibaba Cloud. Additionally, Navicat Cloud adds collaboration and synchronization features including the ability to create projects and add member to collaborate, and to synchronize connections, queries, models and virtual groups.

Using Navicat Premium DBAs can setup automated tasks as needed, including scheduling backups, query execution, and data movement tasks like import, export, transfer and synchronization. There are numerous reports that can be generated by Navicat Premium and DBAs can schedule printing reports as needed. And notification emails can be sent automatically as part of the automation.

Application and SQL development tasks are also available using Navicat Premium. With the SQL editor and visual SQL builder you can create and modify efficient error-free SQL statements to query and modify data across your databases. The SQL console provides an integrated method for testing and executing SQL statement. And Navicat even provides debugging capabilities for PL/SQL and PL/PGSQL in stored procedures.

If you are looking for a tool that can assist your DBAs in supporting a complex, multi-DBMS environment, Navicat Premium is worth considering. It delivers an intuitive and well-designed interface that can simplify your database administration and development efforts.

Data Modeling Enhances Metadata Management

I have been saying for quite some time now that a resurgence in metadata management should be a component of every IT organization. In the 1980s metadata management, data dictionaries, and repositories were all the rage for a period of time. But the complexity of keeping the data current, along with management difficulty in understanding the return on the investment, caused many implementations to wither on the vine and become obsolete. That is unfortunate.

Oh, I know that for a some of you, managing your company’s metadata continues to be an ongoing concern. And that is great… it should put you ahead of the curve to manage the onslaught of data to support governance and compliance projects, as well as big data analytics efforts. Nevertheless, it seems to me that over the course of the past few decades companies have lessened the importance of data administration and metadata management.

Some of you might even be asking “what is metadata?” But I don’t really want to get into that in this blog post. Instead, I refer you to an earlier column of mine titled The Growing Importance of Metadata, which offers a good explanation.

OK, everybody back and with at least a high-level understanding of what metadata is? Good… let’s move on. So to summarize, you cannot really use data appropriately without good metadata. The metadata helps us to understand our data, which in turn helps us know what data we have, to assure that we are using it properly, and that we are in compliance with any industry or governmental regulations that apply to the data.

I think most people reading this will be in violent agreement that metadata is important. But how can we best manage metadata? The promise of metadata is that it will, if managed properly, provide corporate IT departments with a well-documented, up-to-date view of their data assets. But corporate data environments are riddled with redundancy and undocumented data which can easily lead to questionable data integrity and countless hours of wasted time. Much of the knowledge worker’s or business analyst’s time is spent chasing down data lineage to understand things like, “which customer record is most accurate, the one in the CRM or in the finance application?” And even if organizations manage to document the origins of their data, new development projects take off with no regard for standards adherence, leaving data managers right back where they started.

So how do you ensure that you are exploiting the metadata you are collecting to the fullest, possible extent? How do you make sure that your metadata is easily accessible and effectively used across your organization? Well, this is where data modeling comes in to play. Modeling is important to metadata management.

Effective communication is at the heart of the metadata value proposition. Data managers must be able to interpret the data coming into their organization and then provide a roadmap to everyone else so that they too can reach their destination. Modeling adds value to metadata management much the same way it does for data itself — by serving as a lingua franca, a standardized language, easily understood by everyone from business users to application developers to DBAs.

Proper modeling requires an integrated system incorporating tools, process, and people. Attacking the proper setup of your data architecture is beyond the scope of this blog entry, but we will discuss this in future postings. For now, let’s just focus on the top five advantages of using modeling to enhance metadata management. Starting with number 5 and counting down “Letterman”-style:

#5 Data Structure Quality. Models ensure that the business design of a data architecture is appropriately mapped to the logical design, providing comprehensive documentation on both sides.

#4 Data Consistency. By having standardized nomenclature for all data — including domains, sizing, and documentation formats — the risk of data redundancy or misalignment is greatly reduced.

#3 Data Advocacy. Models help to emphasize the critical nature of data within the organization, indicating direction of data strategy and tying data architecture to overall enterprise architecture plans, and ultimately to the business’s objectives.

#2 Data Reuse. Models, and encapsulation of the metadata underpinning data structures, ensure that data is easily identified and is leveraged correctly in the first place, speeding incremental tasks through reuse and minimizing the accidental building of redundant structures to manage the same content.

#1 Data Knowledge. Models, combined with an efficient modeling practice, enable the effective communication of metadata throughout an organization, and ensure all stakeholders are in agreement on the most fundamental requirement: the data.

Good luck tackling your company’s data and metadata – and be sure to share any thoughts, stories or feedback on the topic with us here on the blog.

 

On the High Cost of Enterprise Software

Enterprise Software Should Not Cost an Arm and a Leg

I believe that enterprise software is too costly and, indeed, it would seem that many others agree with me or we wouldn’t see the explosion of open source software being adopted industry-wide these days. Now don’t get me wrong, I am not saying that open source software is free to run – of course there are maintenance and management costs. But if commercial enterprise software provided needed functionality and was reasonably-priced then we wouldn’t really need open source, now would we?

Before the open source zealots start jumping down my throat, yes, I acknowledge that open source is more than just cost. The creativity and group-development aspects also led to its strong adoption rate… but I am getting off topic… so let’s go back to the high cost of enterprise software – that is, the software that runs the computing infrastructure of medium to large businesses.

It is not uncommon for companies to spend multiple millions of dollars on licenses and support contracts for enterprise software packages. This comprises not only operating systems, but database systems, business intelligence and analytics, transaction processing systems, web servers, portals, system management tools, ERP systems, and so on.

Yes, there is intrinsic value in enterprise software. Properly utilized and deployed it can help to better run your business, deliver value, and sometimes even offer competitive advantage. But what is a fair value for enterprise software?

That is a more difficult question to answer.

But let’s look at something simple, like a performance monitor. Nice software, helps you find problems, probably costs anywhere from tens of thousand dollars to over a million depending on the size of the machines you are running it on. Sometimes the software used to monitor is more expensive than what it is being used to monitor! Why does it cost that much? Well, because companies have been willing to pay that much. Not because the software has to cost that much to develop. I mean, how many lines of code are in that monitor? Probably less than Microsoft Excel and I can get that for a hundred bucks or so. And I can almost guarantee that Excel has a larger development and support team than whatever monitor you choose to mention.

So the pricing is skewed not based on what it costs to develop, but what the market will bear. That is fine, after all we live in a free market economy (depending on where you live, I guess). But I don’t believe that the free market will continue to support such expensive software. And the open source movement is kind of bearing that out. Nevertheless, there are still companies that prefer to purchase commercial software rather than to rely on open source software, at least for some things.

As I think about enterprise software a bit further… In many cases, enterprise software vendors have migrated away from selling new software licenses to selling maintenance and support. For some companies, more than half of their revenue comes from maintenance and support instead of selling new software. This is especially true for some mainframe software vendors.

Viewed another way, you could be excused for thinking that some of these companies are doing little more than asking their customers to pay for the continued right to use the software because their is little maintenance going on. Sounds like a nice little racket… you know what I’m talking about? So you pay several million for the software and then hundreds of thousands, maybe millions more for the continued right to use it and get fixes.

Another problem with enterprise software is feature bloat. Enterprise software can be so expensive because vendors want to price it as if all of its feature will be used by the enterprise. But usually only a few features are needed and used on a regular basis. Part of the problem, though, is that those few features can be (and usually are) different for each organization. One way vendors deal with this is to offer many separately-priced features enabled by key, but that is complicated for the user (as well as the software vendor).

So what is the answer? Gee, I wish I knew… if you have any ideas, please share them in the comments section… I’ve got some ideas and thoughts and perhaps I’ll share them with you all in a later blog post. But I think I’ve babbled on enough for today…

Implementing a Data Governance Initiative?

I recently received the following question and I think it is interesting enough to blog about.

Q: My company is looking to implement a data governance initiative. We want to be careful to avoid merely employing a series of extemporized data quality projects and are taking great pains to involve executive management, so we have formed a structure that segregates the responsibilities and activities into layers: a strategic level, tactical level, and execution level. What specific responsibilities should be assigned to each level?

Here is my answer:

The strategic level needs to involve upper level management and high-level technicians. Of course, this should include the CIO and CTO, but should include their lieutenants as well. Additionally, a senior data administrator or data architect should be included. A high level consultant could be used to help steer this committee and keep it on track. The strategists will map out the purpose and intent of the data governance initiative. Why is it needed? What problems will it solve? What will its impact be on the business?

The tactical level needs to involve the folks most literate on data and database systems. This should include data architects, database administrators, and technical end users; perhaps senior programmer/analysts, as well. Consultants may be needed to help flesh out the needed tasks and requirements. These folks will outline the necessary components of the initiative to meet the strategy as outlined by the executive committee. Budgeting goals will need to be set as guided by the executive committee and streamlining or adjusting the tactics may to occur to stay within the budget guidelines as this group works on its mission

The execution level needs to be staffed with the appropriate folks who can actually implement the tactics outlined by the tactical committee. This will likely include members of the tactical committee, as well as more junior DBA, DA, and programming staff.

Finally, I would suggest that you should engage the services of a skilled consultant in the area of data governance for advice on setting up your organization. I can recommend both Bob Seiner  who is quite knowledgeable on the topic of data governance, as well as his book: Non-Invasive Data Governance: The Path of Least Resistance and Greatest Success by Robert S. Seiner (2014-08-22), which describes a way to introduce data governance without adopting onerous, intrusive processes.

A High-Level Guide to SQL Tuning

SQL tuning is a complicated task and to cover it adequately requires a full-length book of its own – actually, perhaps several if you use multiple DBMS products. That said, there are some good high-level SQL tuning suggestions that should apply regardless of the DBMS you are using. Well, as long as it supports SQL!

Here are some helpful rules of thumb:

• Create indexes to support troublesome queries.
• Whenever possible, do not perform arithmetic in SQL predicates. Use the host programming language (Java, COBOL, C, etc.) to perform arithmetic.
• Use SQL functions to reduce programming effort.
• Look for ways to perform as much work as possible using only SQL; optimized SQL typically outperforms host language application code.
• Build proper constraints into the database to minimize coding edit checks.
• Do not forget about the “hidden” impact of triggers. A DELETE from one table may trigger many more operations. Although you may think the problem is a poorly performing DELETE, the trigger is really the culprit.

Furthermore, a large part of the task of tuning SQL is identifying the offending code. A SQL performance monitor is the best approach to identifying poorly performing statements. Such a tool constantly monitors the DBMS environment and reports on the resources consumed by SQL statements.

Some DBMSs provide rudimentary bundled support for SQL monitoring, but many third-party tools are available. These tools provide in-depth features such as the ability to identify the worst-performing SQL without the overhead of system traces, integration to SQL coding and tuning tools, and graphical performance charts and triggers. If you find yourself constantly being bombarded with poor performance problems, a SQL monitor can pay for itself rather quickly.

At a high-level then, the guidance I want to provide is as follows:

1. Ingrain the basics into your development environment. Make sure that not just the DBAs, but also the application developers understand the high-level advice in the bulleted list above.
2. Build applications with database performance in mind from the beginning.
3. Make sure that you have an easy, automated way of finding and fixing poorly performing SQL code.

Sound simple? Maybe it is, but many organizations fail at these three simple things…

Data Technology Today in 2017

Here we are in the first week of 2018 so it is time, once again, to look at what happened this past year in the blog. First of all, there were 16 new blog posts this year so I averaged a little more than one a month. That is less than what I would like to accomplish, but also a rate I can live with. I just want to make sure that I have enough new content here to keep you guys interested!

And it seems like there is continued interest. The graphic below shows 2017’s blog activity. There were over 46 thousand page views by over 36 thousand visitors.

The most popular post this year on the blog was one I posted a few years ago titled: An Introduction to Database Design: From Logical to Physical. There were 8,233 views of this particular post. The second most popular was a post on backup and recovery from 2014 that received about half as many views.

Who are you, out there, reading this blog? Well, I know where most of you live! Almost 20 thousand of you from my home country, the United States. But I also have a lot of great readers in India, as well as many others across the world, as can be seen here…

And how did most people find the blog? Unsurprisingly, it was by using a search engine, with terms like ‘types of database design’… ‘logical design to physical implementation’… ‘production data’… and many other data-related search terms. Twitter was the second most popular way for readers to find the blog, followed by my web site, LinkedIn, and Planet Db2.

So to end this brief synopsis of 2017, thank you to all of my regular readers – please keep visiting and suggesting more topics for 2018 and beyond. And if this is your first visit to the blog, welcome. Take some time to view the historical content – there are several informative posts that are popular every year… and keep checking back for new content on data, database, and related topics!

Happy Holidays 2017!

Just a short post to end the year wishing all of my readers everywhere a very happy holiday season – no matter which holidays you celebrate, I hope they bring you joy, contentment, and let you recharge for an even better year next year!

So enjoy the holidays and come back in January as we continue to explore the world of data and database technology…

SQL Coding and Tuning for Efficiency

Coding and tuning SQL is one of the most time consuming tasks for those involved in coding, managing and administering relational databases and applications. There can be literally thousands of individual SQL statements across hundreds of applications that access your many production databases. Although the DBA is ultimately responsible for ensuring performance of the database environment, there is quite a lot that application developers can do to help out. Frequently, developers are only concerned with getting the right answer (which is, of course, required) but not with getting it in the most efficient way.

When coding SQL statements, the following steps need to occur for each and SQL statement that you write:

1. Identify the business data requirements
2. Ensure that the required data is available within existing databases
3. Translate the business requirements into SQL
4. Test the SQL for accuracy and results
5. Review the access paths for performance
6. Tweak or re-write the SQL for better access paths
7. Possibly code optimization hints
8. Repeat steps 4 through 7 until performance is within the required range.
9. Repeat step 8 whenever performance problems arise or a new DBMS version is installed
10. Repeat entire process whenever business needs change

SQL tuning is a complex, time consuming, and sometimes error-prone process. Furthermore, it requires cooperation and communication between the business users and application programmers for the first three steps, and between the application programmers and the DBA for the remaining steps.

It is imperative that developers learn more about SQL performance and take steps to be proactive about coding their programs with performance in mind. This is especially the case in the modern DevOps, continuous delivery, agile world where code is moved to production rapidly and numerous times a week.

If developers are not concerned about performance – or only marginally so – then it is a certainty that your organization will experience performance problems in production. There are simply not enough DBAs and performance analysts available to examine every program before it is moved to production these days.

How can you become a performance-focused developer? Here are a few suggestions:

• Read the manuals for your DBMS of choice (Oracle, Db2, etc.), especially the one that focus on performance. Find the SQL-related items and concentrate there, but the more you understand about all elements of database performance the better coder you will be.
• Purchase books on SQL performance. There are several good ones that talk about performance in a heterogeneous manner and there are also many books that focus on SQL for each DBMS.
• Talk to your DBAs about SQL techniques and methods that they have found to be good for performance.
• Learn how to explain your SQL statements and interpret the access path information either in the plan tables or in a visual explain tool.
• Use all of the performance tools at your disposal. Again, talk to the DBAs to learn what tools are available at your site.

And always be tuning!

IT Through the Looking Glass

Sometimes I look for inspiration in what may seem — at first glance — to be odd places.  For example, I think the Lewis Carroll “Alice in Wonderland” books offer sage advice for the IT industry.  I mean, how many times have you watched a salesman grin as he spoke and then expected him to simply disappear the way the Cheshire Cat does?

Which Way Should We Go?

But perhaps that is a bad metaphor.  The Cheshire Cat was actually a pretty smart cookie (no disrespect to salespeople intended)!   Recall the passage where Alice comes to a fork-in-the-road and first meets the Cheshire Cat up in a tree.  She asks, “Would you tell me, please, which way I ought to go from here?”  And the cat responds, “That depends a good deal on where you want to go.”  Alice, in typical end-user fashion replies “It doesn’t much matter where.”  Causing the cat to utter words that we should all take to heart — “Then it doesn’t matter which way you go!”

Of course, you could follow Yogi Berra’s advice.  He said, “When you come to a fork in the road, take it!”  But, then where would that leave you.  The bottom line is that planning and understanding are both required and go hand in hand with one another.

If you have no plan for where you want to go, then at best you will just be going around in circles; at worst, you’ll be going backward!  Planning and keeping abreast of the latest technology is imperative in the rapidly changing world of information technology (IT).  As Alice might put it, IT just keep getting curiouser and curiouser.

It Means What I Mean!

But a true understanding of the IT industry, which is required to accurately and successfully plan, can be difficult to achieve because, invariably we will stumble across Humpty Dumptys.

You remember Humpty, don’t you?  He’s that egg who sits on the wall and spouts off about everything under the sun, sometimes without the requisite knowledge to back up his statements.

Humpty Dumpty is famous for saying “When I use a term, it means whatever I choose it to mean — nothing more, and nothing less.”  There are too many Humpty Dumptys out there.  Perhaps they have good intentions, but we all know what road is paved with those, don’t we?

There are too many people in the IT world laboring under false impressions and definitions. Whenever a new trend or technology begins to gain traction, then you can bet that almost every vendor will claim that their product should be a part of the trend. Even if the trend is completely new and the product in question was created 30 years ago!

Of course, products can be adapted and trends can change. So what is the point of this little blog post? I guess it would be to keep up with trends, don’t believe everything you read, always be learning and create your plans based on sound research.

Does anybody out there disagree with that?