Dontcheff

Archive for the ‘SQL Server’ Category

Do not just Simplify with Databases: Automate, Innovate and Innovate!

In Autonomous, Database tuning, Databases, DB2 database, DBA, PostgreSQL, SQL Server on June 15, 2020 at 09:46

“Automation applied to an inefficient operation will magnify the inefficiency” – Bill Gates
“Innovation distinguishes between a leader and a follower! – Steve Jobs

In the database industry, simplification, automation and innovation have sort of become buzz words – we hear them more often in meetings and see them in power point presentations than in the real world implementations. Databases are being patched, upgraded and migrated but how often automation and innovation are part of the process?

A recent article entitled “After The Pandemic, Don’t Simply Automate. Innovate” quoted an Accenture report that surveyed 1500 C-suite executives in 16 industries, 76% of respondents said they were struggling to scale the technology across their businesses. The numbers tell the story: A full 84% of C-suite executives believe they must leverage artificial intelligence (AI) to achieve their growth objectives.

A survey of database managers and administrators shows the benefits they expect from automation, plus what they think about DBAs’ current workloads. What are the highlights:

– 63% expect faster innovation from database automation
– 62% of the data pros expect that data will grow 25% or more annually at their enterprise over the next three years
– 66% said the DBA/data team’s overwork or scheduling issues are the biggest challenge in database deployments
– The majority of companies have not made significant automation gains in any key data management processes
– 69% of the DBAs think automation will make their job more business-centric.

So, why aren’t we seeing those innovative automations being implemented in full? Is it the DBA mindset, the business is behind the technology and the innovation, lack of time and resources, lack of knowledge and trust in the new?

We can try to look into the issue from the DBA perspective. Recently, Jeff Erickson listed the 3 Can’t Miss Ways to Turn Your DBA Skills into Gold. Here is my paraphrased version of these 3 ways:

1. Expand DBAs skils towards app development – Python, PL/SQL, etc.
2. DBAs should get into the data science game – look more into Oracle Autonomous Database which comes with Oracle ML, a rich library of machine learning algorithms
3. Convert the “A” in DBA from Administrator to Architect – look into big data and data architecture – big data is the driver for innovation in databases

At the end of the article he quoted Kerry Osborne about dealing with vast and growing data volumes: “That added complexity and scale should sound like a huge opportunity knocking“.

A interesting article by Duncan Harvey entitled What is the innovation, automation dynamic?, pointed out how autonomous systems are finally giving people the headspace they need to innovate at speed in the digital era.

So, is there a Database Automation Guide? Yes indeed!

Get a cloud boost with Oracle Autonomous!

Have a look of all Automatic features that came after Oracle 9i. Oracle 7 an 8 were probably the first releases added automatic functionalities to the database but the boost started with 9i. 20c is not skipping on that part either.

From all database brands arguably the Oracle Database is most advanced in terms of tools, automation features and innovation capabilities. Only from 11.2 until Oracle 20c, there are 133 new features purely focused on Automation. But I still see detabases where even old enough automation features (such as Automatic SQL Tuning) have not been yet implemented.

Let us look into few database brands:

SQL Server:

There are dozens of Automatic features and properties embedded into SQL Server:

Automatic Tuning can do a lot things such as Automated performance tuning of databases, Automated verification of performance gains and Automated rollback and self-correction. More importantly, it is the only database besides Oracle to have Automatic Indexing. It is worth checking how Automatic index management works in the Azure SQL database. Azure SQL Database analyzes your workload, identifies the queries that could be executed faster if you create an index, identifies indexes that are not used in a longer period of time, and identifies duplicated indexes in the database.

Db2:

IBM Db2 also has a relatively good list of Automatic features including self-tuning memory (single-partition databases only), Automatic storage, Automatic database backups, Automatic reorganization and Automatic statistics collection. The Db2 documenattion claims that the Db2® autonomic computing environment is self-configuring, self-healing, self-optimizing, and self-protecting but this is far behind what Oracle ADB has to offer. Tuning SQL is more difficult in Db2 than in Oracle and the single reason for that are the tools and features offered by both systems.

Redshift:

Amazon’s Redshift has a good set of features. Looking at what automation is like, we find enough for a relatively new database brand (Redshift, not PostgreSQL).

In terms of storage, it is mostly about how Redshift automatically takes care of data formatting and data movement into S3 and how with managed storage, capacity is added automatically to support workloads up to 8PB of compressed data.

There is of course Automated provisioning and Automated backups. Plus, Automatic workload management (WLM) uses machine learning to dynamically manage memory and concurrency, helping maximize query throughput.

Also, Amazon Redshift continuously monitors the health of the cluster, and automatically re-replicates data from failed drives and replaces nodes as necessary for fault tolerance.

Very much like Oracle RAC and Oracle Exadata, as of May 2020, Amazon Redshift now leverages Bloom filters to enable early and effective data filtering. Redshift automatically determines what queries are suitable for leveraging Bloom filters at query runtime.

Recently, Amazon Redshift also introduces ATS (= Automatic Table Sort), an automated alternative to Vacuum Sort. Automatic table sort complements Automatic Vacuum Delete and Automatic Analyze and together these capabilities fully automate table maintenance. Automatic table sort is now enabled by default on Redshift tables where a sort key is specified.

Amazon Redshift automatically takes incremental snapshots (backups) of your data every 8 hours or 5 GB per node of data change. You now get more information and control over a snapshot including the ability to control the automatic snapshot’s schedule.

In terms of SQL tuning, Amazon Redshift now automatically and elastically scales query processing power to provide consistently fast performance for hundreds of concurrent queries. The database automatically shuts down Concurrency Scaling resources to save you cost. Also, Amazon Redshift now updates table statistics by running ANALYZE automatically but that is no news for Oracle database users. Amazon Redshift improves query performance by automatically moving read and write queries to the next matching queue without restarting the moved queries.

Snowflake:

Looking into the Top 10 cool things about Snowflake, we see the fact that the JSON documents are stored in a table and optimized automatically in the background for MPP and columnar access. There is Automatic Encryption of Data and Automatic Query Optimization. No Tuning! Really cool as “It is all handled “auto-magically” via a dynamic query optimization engine in our cloud services layer. So, no indexes, no need to figure out partitions and partition keys, no need to pre-shard any data for even distribution, and no need to remember to update statistics.”

I like the “auto-magical” part 🙂

My interest was caught by the deep dive of the Revolutionary features of Snowflake that sets it apart — A Deep dive: all I found on automation was “Automatic scale down”. No comment here.

PostgreSQL:

If we look into the PostgreSQL Feature Matrix, we see only one feature about automation: Automatic plan invalidation. There is a mention of WAL Buffer auto-tuning and Autovacuum.

For databases such as MySQL, PosgreSQL and MongoDB, check this article: Why is Database Automation Important?

A recent discussion Oracle vs. PostgreSQL basically tells it all – in case you have the patience to read it – here are both sides:

Pro-Oracle: “Comparing Postgres with Oracle is a bit like comparing a rubber duck you might buy your three year old, with a 300000 ton super tanker. Do they both float? Yeah, but that’s about the only similarity.”
Pro-PostgreSQL: “So bottom line, PostgreSQL beats Oracle by far in my opinion, at least as far as installing it and sizes are concerned.”

Looking into the Features and Benefits of EDB Postgres Cloud Database Service, we see Automated Notifications, Automated Monitoring, Automated Backups and Automated Replica Failover.

Oracle

Of course, Oracle ADB (Autonomous Database) has all automation features embedded into the service but not all applications are certified or fit ADB. Not yet at least. Still, I think that most automation features can be easily implemented (into a non-ADB) with some prior testing and the benefits are enormous. The list if automation features is long – here are the major ones:

Features in 19c and 20c which are worth implementing (at least looking into and testing) are:

Automatic Indexing
– Automatic SQL Plan Management
Automatic Index Optimization
Automatic Zone Maps

Automation and innovation go hand in hand. Database innovation in an enterprise involves using the database technology in new ways in order to create a more efficient organization and improve alignment between technology and business by completing the same DBA work with smaller teams by implementing automation features in the database to reduce storage and labor cost & increase system uptime and performance.

Bottom line for DBAs: regardless of the database, there is room for innovation in every database brand!

What is the best relational database?

In DB2 database, DBA, MySQL, Oracle database, PostgreSQL, SQL Server, Sybase on May 11, 2013 at 11:45

“I have the simplest tastes. I am always satisfied with the best.” Oscar Wilde

If we look at market figures, then “Gartner 2012 Worldwide RDBMS market share” reports 48.3 percent revenue share for Oracle.

RDBMS_Market_Share

Three major factors make the Oracle database a clear leader:

1. Remains #1 in worldwide RDBMS software revenue share
2. Holds a larger revenue share than four closest competitors combined
3. Leads next closest competitor revenue share by 29%

But why is that so? Let us have a look at the latest DB-Engines Ranking where Oracle is not only #1 in the DB-Engines Ranking of Relational DBMS but also in the Complete Ranking.

The DB-Engines Ranking are measured the popularity of a system by using the following parameters:

– Number of mentions of the system on websites, measured as number of results in search engines queries.
– General interest in the system.
– Frequency of technical discussions about the system.
– Number of job offers, in which the system is mentioned.
– Number of profiles in professional networks, in which the system is mentioned.

What is the best relational database? The best answer given in answers.yahoo.com is the following: “Define “best”. Oracle is like a BMW. Expensive but has all the fixings. But not everyone needs a BMW. MySQL is like a VW Beetle (the old model). Its cheap, and gets you where you need to go. But you have to tweak it to suit your needs.” Nice explanation!

Next, let us look at Gartner’s Magic Quadrant for Data Warehouse Database Management Systems from January 31st, 2013:

Gartner_Magic_DW

The reason Oracle is top on the “Ability to Execute” scale is simple. It can be described by me with just one word: Exadata. Of all the vendors in this analysis, Oracle reports the highest incidence of nontraditional analytics customers: sectors such as hospitality, energy trading, life sciences and food distribution show up in its reference base. According to Gartner, many of the vertical markets where Oracle has the greatest success contain traditional implementers or late adopters of data warehousing. Oracle’s customers range in annual revenue from $100 million to over $10 billion.

The Business Technology Forum raised the same question: Which is the best enterprise RDBMS database? The article is very much to the point coming to the conclusion that Oracle have the upper hand.

Mirror, Mirror on the Wall, which is the best RDBMS of all? You do not expect an answer from the Mirror “It depends” because anyone trying to be diplomatic will answer that way. Well, at least they say that Oracle is most scalable, most feature rich, and just a wonderful RDBMS.

More to read:

Why would you use Oracle database?
Why Oracle is the Preferred Database Platform?
Is Microsoft’s SQL Server really cheaper than Oracle?

The last one favors SQL Server over Oracle but a word of caution on the credibility on the Oracle side: the author calls PL/SQL “P-SQL” and refers to Active Data Guard as “Advanced Data Guard”.

It will take DB2, SQL Server, Sybase, PostgreSQL and all other key RDBMS players at least several more releases before they can approach the current self-management and tuning capabilities of Oracle Database 11g. But when that day comes, Oracle will not be still at version 11.2.0.

12c is knocking on the door.

Oracle12c

The Google DBA, the GUI DBA and the DBA who does all the work

In DB2 database, DBA, MySQL, Oracle database, SQL Server on June 26, 2011 at 12:36

I often neglect notes and comments on people who divide DBAs into groups and types but this DBA’s Journey into the Cloud is worth commenting on:

The exposé is so short that one does not get the impression if these 3 DBA types are mutually exclusive or not. If not, why would then someone profile DBAs into groups?

You obviously use Google and Metalink (called MOS nowadays). And you use GUIs: Grid Control, Oracle Developer, Toad? Doesn’t look impressive if you cannot make it into the last group, right?

And into which group will the guy hired for this Pre-Sales DBA job fit?

Let me change to another classification method and peek at the The DBA Corner of Craig Mullins. He says that the more common types of DBAs are:

– System DBA
– Database Architect
– Database Analyst
– Data Modeler
– Application DBA
– Task-Orientated DBA
– Performance Analyst
– Data Warehouse Administrator

Too many. I might agree that the system DBA and application DBA are different roles but all those analysts? “The performance analyst is usually the most skilled, senior member of the DBA staff, a role that he has grown into due to his experience and the respect he has gained in past tuning endeavors.” Interesting..

Next, I want to see what Don Burleson has to say. What Type of DBA Are You? Only 3 options here:

– The Scientist DBA
– The Can-do DBA
– The Empathic DBA

Looks like classification on emotional basis. Interesting reading though.

Let us look at what Wikipedia has to say. Déjà vu! 3 types again:

– System DBA
– Development DBA
– Application DBA

Development DBA? Used to be called a programmer years ago. I always thought that the Development DBA takes care of the development databases 🙂

What do people have to say about the DBA types in the internet forums. Let us visit DBA support. Funny stuff at the end! Now, the types are 4, with short descriptions listed below:

– The smart and industrious DBA
– The stupid and industrious DBA
– The smart and lazy DBA
– The stupid and lazy DBA

Up to now, it looks to me that I am always a mixture of some type. A hybrid? Brian Knight figures it out in his article DBA Job Description: What type of DBA are you? 3 types again:

– Production DBA
– Development DBA
– Hybrid DBA

I really don’t know. 10 years ago, he wrote about the Death of the Production DBA and companies still recruit SQL Server Production DBAs.

If you surf the Internet, you will find all type of DBA classifications: the Oracle DBA and the MySQL DBA; the certified DBA and non-certified DBA; the on-call DBA and the 9-to-5 DBA.

There is even a Marketing DBA: “DBA skill needs to sale out software to vendors. To convince customer in technical point of view marketing team requires DBA help. Marketing DBA tries to satisfy each technical queries of customer and help marketing team. There is no database administration and maintenance directly involve in this DBA role.”

Look once more in the text above: A database administrator role such that there is no database administration. No comment.

But eventually, it looks to me that DBAs can be divided into two groups: those who divide DBAs into groups and those who don’t..

The DBA: an architect or an administrator?

In DB2 database, DBA, MySQL, Oracle database, SQL Server on April 26, 2011 at 19:55

Most people in IT comprehend the complexity and importance of the DBA role.

Database architects and administrators are the building block of any IT organizations. The area of database management is so vast that some DBAs specialize nowadays in certain parts of the DBA profession: replication, performance tuning, clustering technologies, etc.

No surprise that CNN Money places both the software architect and the database administrator in the Top 100 for the Best Jobs in America. In fact, software architect is the best job in America and database administrator is #7.

Have a look at all IT jobs that can be found in Top 100 for 2010:

1. Software Architect
7. Database Administrator
17. Information Systems Security Engineer
18. Software Engineering / Development Director
20. Information Technology Manager
21. Telecommunications Network Engineer
24. Network Operations Project Manager
26. Information Technology Business Analyst
28. Information Technology Consultant
30. Test Software Development Engineer
31. Information Technology Network Engineer
33. Information Technology Program Manager
35. Computer and Information Scientist
37. Programmer Analyst
38. Applications Engineer
49. Systems Engineer
60. Information Technology Specialist
66. Systems Administrator
67. Web Developer
70. Technical Services Manager
77. Information Technology Systems Manager
79. Information Technology Training Specialist
88. Technical Writer
95. Information Technology Project Coordinator
96. Web Project Manager

For those of you who have spent years in database architecture and administration, this comes as no surprise perhaps.

For those who might be puzzled and for the ones who would like to read more on the subject, check this article from Datavail called Database and DBA Management.

Last year, Sloane Stricker presented the Top 10 reasons DBAs quit. I would like to add what triggers this and what managers should try to do in order to keep the DBAs in the company:

– DBAs need at least 20% white time: that is time on their own to read white papers, blogs, forums, metalink, etc. Time to test, research and debug new features and old issues. Time to attend online seminars, conferences, Oracle Open World, etc.
– Try to find what motivates each DBA. They are all individuals with own preferences and interests. Bonus intensives should be real personal. I value very much the team work but it is just one finger that presses “Enter” upon shutdown immediate.
– Keep their salaries enough competitive. Do not just tie them to the company’s job grade or job band. There is nothing wrong with you most senior DBA having higher annual income than your IT Director. Really!

For those of you who might be more deeply interested in the topic, check the Gartner/Burton paper: Realigning Database Administration to Jump-Start a Data Management Organization. Very useful for midsize and big organizations! Here is the conclusion of the article:

“The formation of a Data Management Organization (= DMO) is Gartner’s recommended solution for correcting the sorry state of data management in the majority of enterprises. Competitive pressures mean that time is of the essence, Gartner sees the reorganization of the existing database administration groups into an embryonic DMO as a fast-track, but effective, first step in this process. This guidance document provides a framework to direct this organizational transformation.”

As it might sound a bit out of context here, I recommend that you read the whole article.

And finally back to the question in the title: it is both. The DBA should nowadays be both an architect and an administrator. The time of DBA 2.0 is over, it is now time for DBA 3.0!

On the Total Cost of Database Administration

In DB2 database, DBA, MySQL, Oracle database, SQL Server on March 25, 2011 at 10:42

Have you ever read those “white papers” distributed over the internet on total costs of database administration? One claiming that “Microsoft SQL Server required significantly less effort to install and maintain than Oracle”, another one saying that “DBAs can perform typical administrative functions in 41 percent less time when using Oracle Database compared to Microsoft SQL Server”.

You can just stare in disbelief in their “facts” and “arguments”. The former quote above is from ComputerWorld “The Total Cost of Administration Champ: Microsoft SQL Server 2005 or Oracle Database 10g?”, the latter is from TechRepublic “Oracle Database 11g vs.
Microsoft SQL Server 2008”
.

If Oracle is the Lexus of relational databases, is then your Oracle DBA cost as high as the service cost of an expensive car? Should you consider switching to SQL Server? Nobody is saying that IBM’s DB2 is cheap but very often the comparison is between Oracle and SQL Server.

You might have seen Don Burleson’s article Oracle 10g v. SQL Server. I agree with Don that reducing the number of expensive DBAs is hardly an area of opportunity for the IT manager to save money. But when you often show managers figures like the ones below:

Managers will sooner or later start believing some of those “research figures”. Some of them at least. You see, somebody claims that one DBA can administer 9.9 Oracle databases/31.2 Microsoft databases , another one mentions 15.2 Oracle databases/65.4 Microsoft databases. Probably the figures are not far from reality although I have seen that with some automation the numbers can grow much higher (100-150 databases per DBA) although I can’t take seriously studies giving me numbers about users supported by DBA. Sorry, what users? Internet users, database users/schemas? Come on..

It is not up to the number of databases a DBA handles or the gigabytes per DBAs or even the brand. It is up to the skills and experience of person doing the database administrations, it is up to environment and the DBA team handling the work. It is much about the set-up of the databases and their complexity. Of course the automation involved in the DBA work is a significant factor as well. But generalizations like the ones above and below are just mildly insane. If someone tries to convince you that you will save a lot of money by putting your databases in “the Cloud”, just turn and run 🙂

About 5 years ago, Forrester did the following forecasts, decide for yourself if predictions were TRUE or FALSE. At least I disagree with the performance tuning curve.

1. DBA to databases ratio:

2. Database administration cost is declining:

3. Database administration challenges:

Database administration has been and will remain the most complex part of IT. The new features added by vendors like Oracle, IBM and Microsoft to the relation software only make administration more difficult, complex and hard to learn and master from scratch.

P.S. There is a book written in 1954 called “How to Lie with Statistics”. You can get it now for 5.99 from amazon.com