Skip navigation.

Pythian Group

Syndicate content
Love Your Data
Updated: 11 hours 37 min ago

Oracle’s CREATE INDEX command can take a hint

Thu, 2016-02-25 15:39

Here’s something really useful I discovered by accident when playing with Auto DOP (parallel_degree_policy) in 12c.

The “create index” command can take a hint – in particular the PARALLEL hint. The syntax is as you would expect:

create /*+PARALLEL*/ index tab_ind1 on tab(col1);

Of course, you can specify the number of parallel servers to be used by specifying PARALLEL(24) for example for 24 threads. The really interesting thing about using a hint vs. the documented syntax ("create index tab_ind1 on tab(col1) parallel 24;") is that once created – the index doesn’t have a default degree of parallelism. So you don’t need a second command to make the index noparallel.

Note that if you put the hint and use the “noparallel” attribute like so:

create /*+PARALLEL*/ index tab_ind1 on tab(col1) noparallel;

Then no parallelism will be used.

I tried using hints like FULL(t) to force an index create to use a full table scan instead of an existing index – but that doesn’t seem to work.

I discovered this under really interesting circumstances. I was testing some unrelated functionality that required some indexes created on my play table called CKK.

Here’s the SQL for the CKK table, which will create a 40 GB table with 2 rows per block:

create table ckk nologging tablespace ckk as
select rownum id, mod(rownum,5) mod5_id, mod(rownum,5000) mod5000_id, sysdate dt_fixed, sysdate - rownum/24/60 dt_dec, sysdate + rownum/24/60 dt_pos, sysdate + ora_hash(rownum,65,535)/24 dt_rand, sysdate+mod(rownum,10) dt_mod10, rpad('x',3500,'x') filler
from (select rownum r from dual connect by level <= 10000) r1, (select rownum r from dual connect by level <= 1000)

Then when I attempted to create an index on the table in parallel, Oracle refused to do so:

create index ckk$id on ckk(id) parallel 24; --DOES NOT run in parallel

Instead it created the index with 1 thread only, and then set the parallel degree policy to 24. I have tracked this problem down to the Auto DOP feature. If I turn it off via parallel_degree_policy=manual – the problem goes away. But I never expected this feature to turn off parallelism for index creation when explicitly requested.

Here’s the kicker – once any index is created on the table, future index creations will be automatically done in parallel, regardless if parallel was requested.

For example, this index would be now created in parallel:

create index ckk$mod5_id on ckk(mod5_id);

While before creating the index “ckk$id” – this index would refuse to get created in parallel – when using the parallel attribute.

That’s when I said to myself, “it’s almost like there’s a hint.” I took the hint, and discovered it does work, and it works more consistently than the attribute.

Categories: DBA Blogs

Log Buffer #462: A Carnival of the Vanities for DBAs

Thu, 2016-02-25 15:07

This Log Buffer Edition covers Oracle, SQL Server and MySQL blog posts listing down few new tricks, tips and workarounds plus the news.


Displaying CPU Graphs For Different Time Ranges in Enterprise Manager

One of the cool things in 12c is that (finally after all these years) a sequence can be assigned as the default value for a column.

Jonathan Lewis demonstrates connect by after receiving an email.

Oracle 12c – PL/SQL “White List” via ACCESSIBLE BY

Oracle DBA, IT Manager, or Something Else

SQL Server:

Using R Machine Learning Script as a Power BI Desktop Data Source

SQL Authentication Via AD Groups Part II: Who has what access?

Using R Machine Learning Script as a Power BI Desktop Data Source

Connect to on premise data sources with Power BI Personal Gateway

Exploring SQL Server 2016 Dynamic Data Masking – Part One – Creating a Table that uses Dynamic Data Masking


Data Encryption at Rest

Planets9s: Download the new ClusterControl for MySQL, MongoDB & PostgreSQL

Automate your Database with CCBot: ClusterControl Hubot integration

Loading JSON into MariaDB or even MySQL – mysqljsonimport 2.0 is available

Privileges in MySQL and MariaDB: The Weed Of Crime Bears Bitter Fruit

Categories: DBA Blogs

Learning To Be Present

Thu, 2016-02-25 10:06


“Realize deeply that the present moment is all you ever have.”  – Eckhart Tolle

You’re writing an email, reading instant messages, updating your LinkedIn status, listening to music, taking a call, and thinking about what you’re going to order for dinner tonight, all within the same five minutes.

You think, “Gosh I’m productive!” when in fact, you’re really quite the opposite. We now live in a world where information is at our finger tips and we are no longer spending countless hours reading and/or watching TV. Instead, the majority of us are getting lost in the vortex of surfing the web, and as a result it’s becoming increasingly challenging to remain present.

Being present took on a new meaning for me when I became a parent. Throughout his small but vast life to-date, my son has been inadvertently teaching me to keep me calm, clear, and focused on the present moment. Kids don’t care what time it is, or that you have an important call to make. They’re oblivious to your fast approaching project deadline, or that your favorite TV show is starting in five minutes. They exist in their own ‘now’ and generally focus on one moment and experience at a time…even if that moment or experience only lasts 60 seconds.

My eighteen-month-old son consistently reminds me of the importance of being truly present.  He requires me to be focused on one thing, and hearing what he is trying to express within the scope of his current 30 word vocabulary. He doesn’t waste time when he expresses his wants, needs, and feelings. He’ll call me out on my distractions when other people in my life won’t. He acts with purpose when he does things that he knows he shouldn’t, just to bring me back to the moment that we’re in. It’s a very effective method (to say the least), and has helped me gain perspective on being mindful, while also reminding me how important my time is with him. This valuable lesson has spilled over into my day-to-day life.

Now, when I’m on a call, or have one of my team members in my office, I’m listening and hearing them. I make a conscious effort to turn my computer monitor, turn off my email notifications and transfer my phone calls to voicemail until I’ve completed the task at hand. When I’m writing an email, I don’t reply to instant messages until I click ‘send’. When I’m hosting a meeting, I’m fully present in it, which often leads to a reasonably early wrap-up, and a beneficial gift of time to those in attendance.

I still have a distance to go in my efforts to master being present. My level of self-awareness and my relationship with my son are what give me the focus to continue to up my game. The next time you are juggling five tasks at once, challenge yourself to focus on only one, with a heartfelt intent to be truly present.

“Wherever you are, be all there.” – Jim Elliot

Categories: DBA Blogs

Security in the age of cloud computing: How IT leaders are mitigating risks

Tue, 2016-02-23 13:37


As the world becomes more connected, and more systems are moving to the cloud, security and data privacy are major considerations for IT leaders. But with the ever-present possibility of security threats, vulnerabilities and privacy exposures, how are today’s CIOs and IT leaders securing their systems?


According to a recent survey by the Cloud Security Alliance, despite concerns about the security of corporate data moving to the cloud, just 35.0 percent of IT leaders said that cloud-based systems of record are less secure than their on-premises counterparts. And 64.9 percent said that the cloud is either more secure than on-premises software or equally secure.


This is not to say that these same IT leaders don’t see security as a major concern as they evolve their systems onto the cloud. While businesses have to focus on innovation and agility to gain competitive advantage, the question of security has to be addressed alongside innovation. But the key is to address security without slowing the business down.


So what are IT leaders doing to secure their systems as they move forward with business and IT innovations? I had the opportunity to discuss this with IT leaders from RMS and Mozilla, during Pythian’s CIO panel discussion in November 2015.


Cory Isaacson, CTO at RMS has been working on bringing big data and scalable systems together to create a new cloud-based platform, and says his customers — some of the world’s largest insurance companies — are constantly concerned about threats to their data. This is an industry rife with concerns over privacy exposures because of the nature of data being handled. RMS runs catastrophe models for their customers, like scenarios that will tell insurance companies what a disaster like an earthquake or hurricane might cost them.


One of the biggest fears on the minds of Isaacson’s customers is about the security of their data. “The best idea is to not have anything that’s worth stealing. We’re looking at techniques that will keep the data encrypted from the user’s browser all the way into the database. If we can solve the data security issue simply by not having anything worth stealing, then that’s much better and much safer. Just take all the confidential and proprietary information and encrypt it end-to-end, and work with it on an encrypted basis,” he said.


RMS is betting on this encryption strategy for the longer term. But, it’s not an easy one to implement. Isaacson admits that it’s going to take some doing, and he hopes that after following ISO standards, going through quality gates and adhering to all of the industry prescribed protections and processes, that he will have some robust security in place.


Sean Rich, director of IT at Mozilla, is leading their application services group, and is facing the question of how to automate security within their day to day processes.  “Just like agile development found ways to build quality assurance into the process and DevOps found ways to build operations into the process, we now need a way to build greater security into the process. The definition of working software has evolved to include all three: quality, runtime and security,” said Rich.


Aaron Lee, the Chief Data Officer at Pythian, believes that we all need to think about automating security, just as we do with things like QA.  “When it comes to security, the cost of inaction is super high and the risk of inaction is super high,”  Lee said.


According to Lee, many IT leaders think the idea of automating security is not feasible. “I think the idea of depending on humans to do the same thing over and over again is nuts,” he said, referring to the manual effort that goes into security.


“The idea that a static security design can secure a dynamic engineering environment is an incredible mismatch,” he added.


Lee’s team at Pythian spends a lot of time with clients trying to figure out how to parse the regulatory requirements to automate as much as possible.


And Lee asserted that companies don’t always know what they’re getting into when they host their data with a third party.


“My favorite example is single tenant versus multi tenant. Single tenant sounds safer because all your data is in one place. But it’s all multi tenant in the end, you’re all sharing the building, you’re all sharing the earth so it’s hard to make that distinction. For a certain part of the SaaS industry, this is an important marketing distinction. But in reality, it’s meaningless. A data isolation strategy that might be implemented hypothetically could involve my business being a tenant on some multi tenant infrastructure in which I have a set of keys that I control and that are specific to my business. My third party doesn’t have access to those keys, and they are not shared by any other tenant. But in reality, are we all sharing the same set of keys?  And how are those keys and secrets managed? Are they sitting on a high risk security module somewhere on somebody’s laptop? Is it some combination of all that? When you start looking at what vendors promise from a security standpoint and what the industry talks about, it doesn’t always match reality,” he said.


These are the issues Pythian faces every day when assessing the vulnerability of a company’s IT systems.


Companies that are serious about security aren’t just checking compliance boxes to make auditors happy, according to Lee.  They’re getting experts like Pythian to address their issues in a constructive way.


“My observation is that the enterprise world at large has gone from needing to have people in place to check boxes to being at true risk of fundamental intellectual property leakage and for that matter, business continuity problems,” Lee said.


But most companies aren’t putting resources on automating. They’re hiring. And according to the Cloud Security Alliance survey, IT leaders see the biggest barrier to mitigating threats to their systems and data is not a limitation in security technology — it’s a human resource limitation. The survey results suggested that companies just can’t find enough security professionals to fill their hiring needs.


In keeping with that trend, The Wall Street Journal recently reported that JPMorgan expected to spend $500 million on cyber security in 2015, double its 2014 budget of $250 million.


While companies continue to spend their security budgets on hiring, Mozilla’s Sean Rich agrees that there’s a need for automation to deal with vulnerability.


“This need has driven transformation. We’ve moved from maintaining compliance to actually dealing with practical threats and with real consequences, looking at every single attack vector and how to mitigate them,” said Rich.


“Finding the answer to mitigating IT risks won’t be easy, and will continue to be costly,” said Pythian’s Aaron Lee.


“The best, most sophisticated zero day vulnerability attacks are utterly widespread in a month, so we all eventually get to feel the enormity of those attacks. Security is the biggest expense and it’s rising. It’s only going to continue to get worse because there are a lot of people who make a lot of money by hacking into systems, and they keep getting better at it.”

Categories: DBA Blogs

Google Cloud Dataproc in ETL pipeline – Part 1 (Logging)

Mon, 2016-02-22 11:16


Google Cloud Dataproc, now generally available, provides access to fully managed Hadoop and Apache Spark clusters, and leverages open source data tools for querying, batch/stream processing, and at-scale machine learning. To get more technical information on the specifics of the platform, refer to Google’s original blog post and product home page.


Having access to fully managed Hadoop/Spark based technology and powerful Machine Learning Library (MLlib) as part of Google Cloud Platform makes perfect sense as it allows you to reuse existing code and helps many to overcome the fear of being “locked into” one specific vendor while taking a step into big data processing in the cloud. That said, I would still recommend evaluating Google Cloud Dataflow first while implementing new projects and processes for its efficiency, simplicity and semantic-rich analytics capabilities, especially around stream processing.


When Cloud Dataproc was first released to the public, it received positive reviews. Many blogs were written on the subject with few taking it through some “tough” challenges on its promise to deliver cluster startup in “less than 90 seconds”. In general the product was well received, with the overall consensus that it is well positioned against the AWS EMR offering.


Being able, in a matter of minutes, to start Spark Cluster without any knowledge of the Hadoop ecosystem and having access to a powerful interactive shell such as Jupyter or Zeppelin is no doubt a Data Scientist’s dream. But with extremely fast startup/shutdown, “by the minute” billing and widely adopted technology stack, it also appears to be a perfect candidate for a processing block in bigger ETL pipelines. Orchestration, workflow engine, and logging are all crucial aspects of such solutions and I am planning to publish a few blog entries as I go through evaluation of each of these areas starting with Logging in this blog.


Cloud Dataproc Logging

Cluster’s system and daemon logs are accessible through cluster UIs as well as through SSH-ing to the cluster, but there is a much better way to do this. By default these logs are also pushed to Google Cloud Logging consolidating all logs in one place with flexible Log Viewer UI and filtering. One can even create custom log-based metrics and use these for baselining and/or alerting purposes. All cluster logs are aggregated under a “dataproc-hadoop” tag but “structPayload.filename” field can be used as a filter for specific log file.


In addition to relying on Logs Viewer UI, there is a way to integrate specific log messages into Cloud Storage or BigQuery for analysis. Just to get an idea on what logs are available by default, I have exported all Cloud Dataproc messages into BigQuery and queried new table with the following query:



structPayload.filename AS file_name,

count(*) AS cnt





AND metadata.labels.value = ‘cluster-2:205c03ea-6bea-4c80-bdca-beb6b9ffb0d6’




  • hadoop-hdfs-namenode-cluster-2-m.log
  • yarn-yarn-nodemanager-cluster-2-w-0.log
  • container_1455740844290_0001_01_000004.stderr
  • hadoop-hdfs-secondarynamenode-cluster-2-m.log
  • hive-metastore.log
  • hadoop-hdfs-datanode-cluster-2-w-1.log
  • hive-server2.log
  • container_1455740844290_0001_01_000001.stderr
  • container_1455740844290_0001_01_000002.stderr
  • hadoop-hdfs-datanode-cluster-2-w-0.log
  • yarn-yarn-nodemanager-cluster-2-w-1.log
  • yarn-yarn-resourcemanager-cluster-2-m.log
  • container_1455740844290_0001_01_000003.stderr
  • mapred-mapred-historyserver-cluster-2-m.log


Google Cloud Logging is a customized version of fluentd – an open source data collector for unified logging layer. In addition to system logs and its own logs, fluentd is configured (refer to /etc/google-fluentd/google-fluentd.conf on master node) to tail hadoop, hive, and spark message logs as well as yarn application logs and pushes them under “dataproc-hadoop” tag into Google Cloud Logging.

Application Logging

You can submit a job to the cluster using Cloud Console, Cloud SDK or REST API. Cloud Dataproc automatically gathers driver (console) output from all the workers, and makes it available through Cloud Console. Logs from the job are also uploaded to the staging bucket specified when starting a cluster and can be accessed from there.


Note: One thing I found confusing is that when referencing driver output directory in Cloud Dataproc staging bucket you need Cluster ID (dataproc-cluster-uuid), however it is not yet listed on Cloud Dataproc Console. Having this ID or a direct link to the directory available from the Cluster Overview page is especially critical when starting/stopping many clusters as part of scheduled jobs. One way to get dataproc-cluster-uuid and a few other useful references is to navigate from Cluster “Overview” section to “VM Instances” and then to click on Master or any worker node and scroll down to “Custom metadata” section. Indeed, you can also get it using “gcloud beta dataproc clusters describe <CLUSTER_NAME> |grep clusterUuid” command but it would be nice to have it available through the console in a first place.


The job (driver) output however is currently dumped into console ONLY (refer to  /etc/spark/conf/ on master node) and although accessible through Dataproc Job interface, it is not currently available in Cloud Logging.


The easiest way around this issue, which can be easily implemented as part of Cluster initialization actions, is to modify /etc/spark/conf/ by replacing  “log4j.rootCategory=INFO, console” with “log4j.rootCategory=INFO, console, file” and add the following appender:


# Adding file appender




log4j.appender.file.layout.conversionPattern=%d{yy/MM/dd HH:mm:ss} %p %c: %m%n


Existing Cloud Dataproc fluentd configuration will automatically tail through all files under /var/log/spark directory adding events into Cloud Logging and should automatically pick up messages going into /var/log/spark/spark-log4j.log.


You can verify that logs from the job started to appear in Cloud Logging by firing up one of the examples provided with Cloud Dataproc and filtering Logs Viewer using the following rule:




If after this change messages are still not appearing in Cloud Logging, try restarting fluentd daemon by running “/etc/init.d/google-fluentd restart” command on master node.

Once changes are implemented and output is verified you can declare logger in your process as:

import pyspark

sc = pyspark.SparkContext()

logger =

and submit the job redefining logging level (INFO by default) using “–driver-log-levels”.

Learn more here.

Categories: DBA Blogs

PRKO-2002 : Small Things, Large Shadows

Mon, 2016-02-22 09:28

This quick and short blog post is a reminder to remember the syntax. I was just trying to bring down local listeners at half rack of Exadata (11gR2) to perform some maintenance. The best way to shutdown resources in RAC is to use srvctl so was trying to run following after setting Grid home as Oracle home, from where the local listeners were running:

[oracle@mytestexa1:~]$ srvctl stop listener LISTENER
PRKO-2002 : Invalid command line option: LISTENER

[oracle@mytestexa1:~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): mytestexa2,mytestexa4,mytestexa1,mytestexa3

Hmmmm. ok, then I tried this:

[oracle@mytestexa1:~]$ srvctl stop listener LISTENER -n mytestexa1
PRKO-2002 : Invalid command line option: LISTENER

Well, ummm ok, then I tried this one:

[oracle@mytestexa1:~]$ srvctl stop listener -n mytestexa1
[oracle@mytestexa1:~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): mytestexa2,mytestexa4,mytestexa3

And it worked. Yes, if the default listener name is not in use, then -l listener_name should be used.

So need to remember the syntax :) . Small things, but in emergencies they cast large shadows for a while.

Categories: DBA Blogs

A View From Down Under: What IT Innovation and Agility Mean to IT Pros in Australia

Mon, 2016-02-22 06:38

The impact of today’s disruptive technologies on how we do business led Pythian to introduce Velocity of Innovation, a series of panel discussions for senior IT professionals. These are Pythian’s exclusive thought-leadership events where a moderator-led panel engages in lively conversations around today’s emerging technologies.

We recently took our Velocity of Innovation event series to Sydney, Australia to find out what’s on the minds of CIOs and other IT leaders in that region. Our moderator for this event was Tom McCann, senior customer experience analyst with Forrester. You may be thinking: Why would a customer experience analyst be moderating a CIO panel? But here’s why we chose to put a customer experience expert at the centre of this discussion. With the commoditization of products and services, delivering a quality customer experience is one of the few ways that companies now have of truly differentiating themselves from their competitors. From attracting prospects, to acquiring customers, to onboarding and ongoing support, companies need to have the right tools and processes in place to ensure products or services perform as promised, and that every interaction customers have with them is satisfying. They also need to be able to use data to respond to customer needs and market trends. IT plays a critical role in of all of these areas.

The panel consisted of IT leaders with a range of backgrounds and experiences:

  • Francisco Alvarez, vice president, APAC at Pythian
  • Chris Mendes, executive consultant big data and analytics from Industrie IT
  • Tim Sheedy, principal analyst with Forrester Research

As we do to start off many of these events, we began the Sydney discussion by asking this question: Innovation and agility are very common buzzwords that seem to describe what everyone wants. What have you explicitly done to get better at innovating or to make your organization or your customer’s more agile? How has this push for agility impacted your enterprise architecture?

Here’s an excerpt from the discussion that followed.

Chris: I actually don’t think there’s enough innovation in Australia, in particular. There’s a lot of talk about it, people are actually doing a lot of experiments and there are some companies who’ve set up business purely giving them the tool sets to use their data to innovate. You’ve got a few things that seem to be working against that at the moment and I think one of the things working against it is that it doesn’t stand on its own.

It doesn’t matter what the technology is, and in fact what ends up happening is it all comes down to the “Why?” Because you really need to have the technologists interacting with business people so that when they come up with an idea they get immediate feedback. I’ve found that a lot of businesses don’t have time for innovation. They run pretty lean, they are very focused on day-to-day operations and don’t have time to come up with new ideas or to use their data to actually innovate. There are a lot of roadblocks there. I don’t see it as a technology problem, I actually see one of the roadblocks as a management issue. The other one is sort of the culture of no failure, and I think we’ve got a big issue with that in Australia.

Tim: As you are saying, I’ve presented a lot on innovation and Forrester actually has a really good innovation model. We spoke to about 130 companies around the globe who did bits of innovation well and we took all those bits and made them into an end-to-end process that can be used by our clients. We learned a lot from putting this together. We spoke to Telstra in Australia and did the piece on deciding what to innovate with, and where to invest. Other companies told us about how they come up with ideas. I speak to a lot of clients who told us that they had an innovation program in place where they gathered ideas from staff. But now those employees don’t trust their companies anymore because they never got back to them on how they would implement their ideas.

Chris: I think the other thing is I think there’s also a very big misunderstanding at board levels about innovation because boards are there to actually stop you changing your business. The fundamental tenant is: “We’ve got a great business model here, it’s running well, we’ve got to make sure that any change to it doesn’t damage that.” There’s a natural caution at board levels and it’s totally understandable.

Audience: One of the issues I guess that you just raised is that the boards are risk adverse, however if their business is failing, then there is obviously a need for them to do something about it.

Tim: But that’s a bad time to be innovating, right? When everything is going wrong, “We should try to do things differently.” The market’s probably left you behind by that point.

Francisco: The main problem that most of you have hit upon is that innovations equal risk. But take a step back and look at the companies that are really doing well in the market. They’re doing really well because of one factor that differentiates them: they were not afraid to try to innovate. And because of that innovation they are getting their share of the market and gaining ground. Just look at the financial market. CBA was considered crazy a few years ago for all the investment they were doing in technology, social media, apps and so on. They got ahead. And now everybody is trying to do the same.The problem is they need to have a balance. Even with choosing vendors, companies will avoid risk. The will keep going with IBM because they don’t want to take a risk. Or they keep Oracle and let Oracle do everything for them because there might be a risk with moving to another vendor. If they already own a technology, it doesn’t matter if they are not getting good service. They think that for risk capacity, there is insurance. Sometimes you’re not saving money or not improving, but you don’t want to innovate simply because you don’t want to increase the risk to the business.

Tim: Innovation is risk management, this is it. The discussion went further on this topic, and explored areas like the future of IT, security and more.

Interested in being a part of a discussion like this one? Velocity of Innovation is a series of thought-leadership events for senior IT management hosted by Pythian. Pythian invites leading IT innovators to participate in discussions about today’s disruptive technologies: big data, cloud, advanced analytics, DevOps, and more. These events are by invitation only.

If you are interested in attending an upcoming Velocity of Innovation event in a city near you, please contact To view our schedule of upcoming events visit out Velocity of Innovation page.

Categories: DBA Blogs

The Value of Pythian’s Internship Program

Fri, 2016-02-19 07:35

I have been working as a software development intern at Pythian for more than three months. As a second year student in electrical engineering, it is both unsurprising and somewhat expected that I would be working in the software industry during my first co-op term. I’ve heard complaints from aspiring electrical engineers that most of the available co-op positions are for software. This is especially true for first or second year students, because the knowledge base required for advanced electronics projects has not yet been attained.

I would discourage students from adhering to this viewpoint. Knowledge of programming and software development in the 21st century is invaluable to any kind of engineering. Furthermore, with the high level of involvement and learning involved in working as a software developer at Pythian, I am confident that someone with a thirst for knowledge would find this to be a worthwhile experience.

It began at the interview. As soon as I began talking to my current manager and the human resources representative, I could tell that the position was one I’d be very interested in. They both struck me as very kind, understanding, and most importantly, knowledgeable individuals. It was also very clear to me that the goal of the interview was to determine certain key traits, other than the necessary technical requirements. They were seeking someone who had a drive to participate and learn, as well as someone who was sociable, and who could fit with the team dynamic. Needless to say, after learning what kind of work I would be doing on the team and receiving the job offer, I quickly accepted the position.

After two weeks of orientation, I was immediately tasked with a very interesting project. The project involved creating a script to upgrade the database used by the core code base, which would eventually be used on the actual product that is “Adminiscope”. It was very fulfilling to know that my work, at the beginning of my work term, would be used on a production system to apply new features.

After this project, I was assigned more interesting projects, which required even more learning. At this point of my co-op term, I have touched almost all aspects of the Adminiscope software, and my abilities in software development have grown to a significant degree. It is very important to note, that learning all these new skills would have been much more difficult if not for the constant guidance of the friendly, knowledgeable, and experienced people I have had the pleasure to work with.

If what I have already stated is not enough to encourage you to work at Pythian, then their unique corporate culture should convince you. While working here, I never once felt that my work or input was less valuable then others just because I was a co-op student. Participation is actively encouraged. Learning more about the company as a whole is also supported through “BORG” meetings with various senior level staff. I have had a few conversions with the CEO himself, and have learned something of significance every time. It is also a very good feeling to be told by someone of such status in the company that the work you are doing is meaningful, appreciated, and most of all, why it’s important.

I have absolutely no regrets, and I am grateful that I have had the opportunity to work in such a stimulating environment. I can say without doubt that in my three and a half months at Pythian, I have learned more about programming and software development than I have in my entire academic career. It is truly a worthwhile learning experience for those seeking a challenge. I hope my story will encourage you to apply for Pythian’s internship program, and I wish you luck in getting the position.

Categories: DBA Blogs

Data Encryption at Rest

Thu, 2016-02-18 14:31

This blog post was co-authored by Peter Sylvester and Valerie Parham-Thompson

Introduced in version 10.1.3 (and with substantial changes in 10.1.4), the MariaDB data encryption at rest feature allows for transparent encryption at the tablespace level for various storage engines, including InnoDB and Aria.

Before now, there have been only two widely accepted encryption methods for MySQL/MariaDB: encryption at the file system level, or encryption at the column level. For comparison, we’ll do a brief overview of how these work, as well as the pros and cons typically associated with each option.

File System Encryption

This is performed by setting a file system to be encrypted at the block level within the operating system itself, and then specifying that the encrypted volume should be the location of the data directory for MySQL/MariaDB. You can also use encrypted volumes to store MariaDB binary logs.

  • One-time setup and then no additional management required.
  • There is a large degree of overhead at the CPU level. Every time an InnoDB page/extent is retrieved and stored in the InnoDB buffer pool, the data has to be decrypted. The same issue occurs when dirty pages are flushed back to persistent storage, be it data or a log file.
Column Encryption

You can encrypt data at the column level by using a binary data type (varbinary/BLOB) and then encrypt the data as it goes into or out of the the page at the application or code level. Typically this is done using the AES_ENCRYPT and AES_DECRYPT functions in MySQL/MariaDB.

  • You’re only encrypting the data that needs to be secured. All other data has no encryption-related overhead.
  • This provides a higher degree of security then file system encryption. If the data is encrypted at the file system or by the data encryption at rest feature, if you can get into the running MariaDB instance you can still see the unencrypted version of the data. With column-level encryption, the data is stored in a secure fashion and you need to supply the encryption key every time it is accessed by the MariaDB instance.
  • The crypt key needs to be stored somewhere that allows the application to easily provide it when running queries against MariaDB.
  • You may be able to see the crypt key in statement-based binary logging, or in the process list.
  • Data that is encrypted should not be used for reverse lookups. For example, if you are encrypting a column that stores a name, and you need to search that column for a specific name, you have to specify the search using the AES_DECRYPT function, which will force all the table records to be scanned, decrypted, and compared as part of the “where” operation of the query.
MariaDB Data Encryption at Rest

This solution sits somewhere between the aforementioned file system level and column level encryption, allowing you to encrypt data at the table level. This allows for encryption that is easier to manage and work with, while also allowing for a narrower focus so you are encrypting only the data or logs that you wish to encrypt. Although, it should be noted that like file system encryption, if you can get to the launched MariaDB instance, you can get access to the encrypted data.

Now let’s walk through a test of the functionality of the MariaDB data encryption at rest feature.


Preparation included cloning a Centos7 base VM in VirtualBox, adjusting the IP and hostname, and installing MariaDB 10.1.11 using their repository with instructions here.

Create Keys

The first step was to create keys. The output of the openssl command below (with example output) was used to edit a new file /var/lib/mysql/keys.txt.

The command was:
openssl enc -aes-256-cbc -P -md sha1
enter aes-256-cbc encryption password:
Verifying - enter aes-256-cbc encryption password:

Sample output:

iv =C6A3F3625D420BD19AF04CEB9DA2D89B

Sample contents of keys.txt using that output:


(You can take the additional step of encrypting the keys, but that was not done here.)

Don’t lose the key file, or you won’t be able to start the server:

2016-02-13 20:37:49 140334031026304 [ERROR] mysqld: File '/var/lib/mysql/keys.txt' not found (Errcode: 2 "No such file or directory") 
2016-02-13 20:37:49 140334031026304 [ERROR] Plugin 'file_key_management' init function returned error. 
2016-02-13 20:37:49 140334031026304 [ERROR] Plugin 'file_key_management' registration as a ENCRYPTION failed. 
2016-02-13 20:37:49 140334031026304 [ERROR] InnoDB: cannot enable encryption, encryption plugin is not available 
2016-02-13 20:37:49 140334031026304 [ERROR] Plugin 'InnoDB' init function returned error. 
2016-02-13 20:37:49 140334031026304 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 
2016-02-13 20:37:49 140334031026304 [Note] Plugin 'FEEDBACK' is disabled. 
2016-02-13 20:37:49 140334031026304 [ERROR] Unknown/unsupported storage engine: InnoDB 
2016-02-13 20:37:49 140334031026304 [ERROR] Aborting

You can of course remove the relevant configs and restart successfully, but we have found at least two issues when trying to remove this configuration after it has been put in place on the MariaDB instance.

  1. If you encrypt the InnoDB log files (redo, not binary logs), then remove the encryption configuration and restart MariaDB, it will not be able to start until you re-enable the data at rest encryption feature.
  2. If you enable default encryption by putting innodb-encrypt-tables in the my.cnf, and then create a table, remove the feature, and restart MariaDB, the server will crash irrecoverably when selecting data from the table (bug filed as
Install Plugin

Next step was to install the plugin and use this file. The clearest path to doing this is to add the following two lines in /etc/my.cnf within the [mysqld] section:
file-key-management-filename = /var/lib/mysql/keys.txt</pre>

Restart MariaDB, and confirm the plugin is installed. The file_key_management plugin should display as “active.”

show all_plugins like '%file%';
Testing Encrypted Tables

As the documentation indicates, you can encrypt all tables when they are created (specify innodb-encrypt-tables in the my.cnf) or individual tables (by adding the settings to a create or alter table statement). (See further below for result of using the third option, innodb-encrypt-tables=force.)

Here are the results if you encrypt a single table.

First, create a table:

mysqlslap --concurrency=5 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --auto-generate-sql-write-number=1000 --no-drop

And encrypt it:

alter table mysqlslap.t1 encrypted=yes encryption_key_id=1;

Here’s the table definition after encrypting:

show create table mysqlslap.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`intcol1` int(32) DEFAULT NULL,
`intcol2` int(32) DEFAULT NULL,
`charcol1` varchar(128) DEFAULT NULL,
`charcol2` varchar(128) DEFAULT NULL,
`charcol3` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes `encryption_key_id`=1

Looking at the .ibd file directly via xxd, you can see some text data before encryption:

0084570: 0001 8000 0002 7661 6c65 7269 6570 6172 ......valeriepar
0084580: 6861 6d74 686f 6d70 736f 6e00 0000 0000 hamthompson.....

And after encryption:

0085470: fdf4 7c27 d9cb 5d33 59b1 824d 4656 b211 ..|'..]3Y..MFV..
0085480: 7243 9ce0 1794 7052 9adf 39a1 b4af c2fd rC....pR..9.....

Once that table was encrypted, to test moving encrypted tablespaces, the files were copied from the source to a destination server as follows. The destination server had no encryption plugin, configs, or key installed.

The following process is typical for moving tablespaces: create a similar empty table on the destination server, without encryption. (It throws an error on that unencrypted server if you try it with `encrypted`=yes `encryption_key_id`=1.)

create database mysqlslap;

use mysqlslap

`intcol1` int(32) DEFAULT NULL,
`intcol2` int(32) DEFAULT NULL,
`charcol1` varchar(128) DEFAULT NULL,
`charcol2` varchar(128) DEFAULT NULL,
`charcol3` varchar(128) DEFAULT NULL

Then start the move process. First, discard the tablespace on the destination server. This leaves you with just the .frm file there.

-rw-rw----. 1 mysql mysql 65 Feb 13 13:15 db.opt
-rw-rw----. 1 mysql mysql 932 Feb 13 13:15 t1.frm

Prepare the table on the source server:

flush tables t1 for export;

Now you have a .cfg file on the source server:

-rw-rw----. 1 mysql mysql 65 Feb 13 13:13 db.opt
-rw-rw----. 1 mysql mysql 620 Feb 13 13:16 t1.cfg
-rw-rw----. 1 mysql mysql 976 Feb 13 13:14 t1.frm
-rw-rw----. 1 mysql mysql 557056 Feb 13 13:14 t1.ibd

Send the .cfg and .ibd files from the source to the destination server:

scp /var/lib/mysql/mysqlslap/t1.cfg root@
scp /var/lib/mysql/mysqlslap/t1.ibd root@

Free to unlock on the source server now:

unlock tables;

You’ll get an error on import if you don’t make them usable by mysql:

chown mysql:mysql /var/lib/mysql/mysqlslap/t1*

With the .cfg and .ibd files in place on the destination server, import the tablespace there:

alter table t1 import tablespace;

As intended, the encryption prevents importing the table:

MariaDB [mysqlslap]&gt; alter table t1 import tablespace;
ERROR 1296 (HY000): Got error 192 'Table encrypted but decryption failed. This could be because correct encryption management plugin is not loaded, used encryption key is not available or encryption method does not match.' from InnoDB

If you set innodb-encrypt-tables=force in /etc/my.cnf, attempting to create a table with encryption=no fails:

create table t3 ( 
`intcol1` int(32) DEFAULT NULL, 
`intcol2` int(32) DEFAULT NULL, 
`charcol1` varchar(128) DEFAULT NULL, 
`charcol2` varchar(128) DEFAULT NULL, 
`charcol3` varchar(128) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encryption_key_id`=1 `encrypted`=no;
ERROR 1005 (HY000): Can't create table `mysqlslap`.`t3` (errno: 140 "Wrong create options")

The error message could be more clear, but the setting would save future create statements from undoing desired encryption set up by a DBA.

Encrypted Binlogs

Binlogs can also be encrypted.

Start by adding this to the my.cnf, and restart server.


Before encryption, the binlogs look like this:

008dfb0: 494e 5345 5254 2049 4e54 4f20 7431 2056 I NSERT INTO t1 V
008dfc0: 414c 5545 5320 2832 3132 3635 3538 3138 ALUES (212655818
008dfd0: 352c 3737 3332 3733 3731 382c 2759 3838 5,773273718,'Y88
008dfe0: 4e30 3774 6f30 3333 6d32 5845 497a 487a N07to033m2XEIzHz
008dff0: 4d4a 7348 7558 544c 3247 6543 6865 4334 MJsHuXTL2GeCheC4
008e000: 574a 7149 436c 4471 6f6c 3479 634d 7071 WJqIClDqol4ycMpq
008e010: 5a68 374b 3463 5a79 7442 4251 684e 4d42 Zh7K4cZytBBQhNMB
008e020: 6234 4c6e 7161 6457 425a 5366 7649 544c b4LnqadWBZSfvITL
008e030: 7a64 5a77 3536 7571 4835 4771 5466 7477 zdZw56uqH5GqTftw
008e040: 6a36 6a5a 5943 336b 6c4f 4e5a 616c 6d50 j6jZYC3klONZalmP
008e050: 454a 4c4a 5047 4161 4c49 4f27 2c27 6970 EJLJPGAaLIO','ip

After restarting the server with encryption, newly generated binlog files look like this:

011b860: 69c5 cc00 5cb0 1581 0217 2d3f 728c 77ff i...\.....-?r.w.
011b870: a6ca e6e3 a041 0f26 ee39 c398 eecd 4df9 .....A.&amp;.9....M.
011b880: 5bef 53e0 bf0a 96bd 7b61 bfcc c074 6151 [.S.....{a...taQ
011b890: 208b 63fc 4efd ee91 b2bc 0a90 1009 76a1 .c.N.........v.
011b8a0: bf18 84e3 f444 82a1 e674 b44b 7754 2cc9 .....D...t.KwT,.
011b8b0: b63f 946c 821d 222a ae57 a251 451c 8332 .?.l.."*.W.QE..2
011b8c0: d030 1c5f 3997 db77 96f1 4da5 a03e 55a9 .0._9..w..M..&gt;U.
011b8d0: a882 3980 f81f 9fa9 7b45 27c1 2f51 34ad ..9.....{E'./Q4.
011b8e0: b8bf e5e6 4b1e 6732 11a1 1b00 0000 c049 ....K.g2.......I
011b8f0: b2a9 ad08 ed95 4c5c 5541 05b4 a256 14d3 ......L\UA...V..
011b900: 045b e74f 2526 0000 009f 921c 1482 d621 .[.O%&amp;.........!

Note that also you can’t use mysqlbinlog on encrypted binlogs:

mysqlbinlog /var/lib/mysql/maria101-bin.000006
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#160213 10:49:27 server id 1 end_log_pos 249 Start: binlog v 4, server v 10.1.11-MariaDB-log created 160213 10:49:27
# at 249
# Encryption scheme: 1, key_version: 1, nonce: 4caf0fe45894f796a234a764
# The rest of the binlog is encrypted!
# at 285
/*!50521 SET skip_replication=1*//*!*/;
#620308 22:02:57 server id 3337593713 end_log_pos 2396907567 Ignorable
# Ignorable event type 118 (Unknown)
# at 324
ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 42, event_type: 204
ERROR: Could not read entry at offset 366: Error in log format or read error.
# End of log file
ROLLBACK /* added by mysqlbinlog */;

As a test of replication, encrypted binlogs were sent from an encrypted master to an unencrypted slave. The master had encrypted binlogs, but the slave had no encryption plugin, configs, or keys.

Nothing special in the replication setup, and replication did not break. No issues were detected in multi-master replication with replication filtering. Also, if the slave is set up for encryption, the encryption key in use on the slave does not need to be identical to that of the key that is in use on the master.

Of special note for security, while the master’s binlogs were encrypted, the slave’s relay logs were not. Change statements on an unencrypted slave are easily viewed at the file level or using mysqlbinlog on the relay logs. Watch those user permissions! Relay logs on the slave can be encrypted using the ‘encrypt-binlog’ setting on the slave having the plugin installed.

  1. Binlog encryption prevents viewing change statements in raw format or via mysqlbinlog.
  2. Replication from an encrypted master to a nonencrypted slave works. Note that the relay logs on the nonencrypted slave make the change statements visible.
  3. Encrypting a table prevents copying the tablespace to another server.
  4. Once implemented, the steps to unencrypt multiple tables in your schema require careful planning. It is recommended you test this feature carefully before implementing in production.
Categories: DBA Blogs

Log Buffer #461: A Carnival of the Vanities for DBAs

Thu, 2016-02-18 14:08

This Log Buffer browses through Oracle, SQL Server and MySQL spaces and brings forth some of the useful blog posts for this week.


Conner throws it out of the park. As we all (hopefully) know, we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic.

Pythian’s Gleb talks about Azure on RAC. Microsoft Azure provides an acceptable and affordable platform for a training environment.

There are some performance improvements that require physical storage options to be set on tables or indexes. One particular technique that I will take as an example for this article is index compression.

To call Oracle Reports from Oracle Forms application, the most secure approach is to use the RUN_REPORT_OBJECT built-in.

Introducing Asset Physical Inventory with PeopleSoft Asset Tracking

SQL Server:

Amazon Web Services (AWS) : Relational Database Services (RDS) for SQL Server

SSIS: Case sensitivity may expose issues with change deployment

Foreign Key Indexes and Binding

In SQL Server 2016 we have new compression functions to compress and uncompress specific data.

Database Continuous Integration


VividCortex is all about optimizing your queries. Many of our users asked us to analyse their queries, searching for common errors and mistakes.

RocksDB vs the world for loading Linkbench tables

Measuring Docker IO overhead

MariaDB 5.5.48 and Connector/J 1.3.5 now available

EXPLAIN FORMAT=JSON: buffer_result is not hidden!

Categories: DBA Blogs

The Little Big Company With The Huge Heart

Wed, 2016-02-17 10:26

February is a drag. Maybe like me you have already experienced profound failure on following-up with New Year’s Resolutions. Instead of “taking yoga” I ended up “severely injuring my back”. Ah, c’est la vie. Sometimes it’s harder than other times to be positive, and when it’s hard to be positive it’s even harder to be grateful (another one of my New Year’s Resolutions). The good news (or bad news if you’re making excuses for your negativity) is you can practice gratitude from wherever you are, in whichever state of mind or body you’re in.

Pythian exists because of the people who work here. This isn’t a commentary on the Company’s success, the Company’s Brand or Culture, but rather the actual employees who make this place a great place to be, by doing one thing: practicing kindness.

This building is full of people who are going out of their way on a daily basis to:

a) be generous with their time (and their baking)

b) offer support to a colleague in need

c) tell a joke, take a joke, share a laugh

d) provide you with directions when you inevitably become confused and disoriented on the 5th or 6th floor.

Thankfully I have the life and work experience to know that this is not the norm. Something special is happening here. People are supporting each other, providing personal and professional insights, and I must say that while technical genius is the grease that allows the wheels to spin, there is a ton of emotional intelligence being exchanged by a ton of people, all the time.

If you wouldn’t characterize your experience here in the same light I encourage you to wander around. Get lost looking for finance and smile with the Accountant who keeps chocolate in his desk. Tell someone in L&D about a new hobby you’ve taken up and she might just tell you she’s proud of you. Stumble into the IT room and ask an inane question about your new cell phone that you can barely turn on, it will be met with patience. Tell our CRO a joke, the driest you’ve got, or a dad-joke, he’ll love it.

Talk to our CEO about heavy metal, art, poetry, books (highly recommended points of interest if like me you started here not ever having heard of “Unix”). Ask our VP of Transformation where the printer is, seriously he told me three times and I still don’t know so please also let me know when you know. Tell one of the DBAs that you’re learning French and then avoid them for the next month or so because you’re too nervous to say anything beyond “je ne comprends pas”.

I’ve been given hugs, have given out hugs, one of my colleagues actually made me lunch a few times when I was moving places and my life was in disarray. There are a lot of smiles here and they are contagious.

Happy to be here with all of you. Thanks for being kind.

Categories: DBA Blogs

How to build a Multi-domain cluster with AlwaysOn Availability Groups – SQL Server 2016

Wed, 2016-02-17 09:30

SQL Server 2016 is making lots of news, and promises to be a revolutionary SQL Server version. In talking about AlwaysOn Availability Groups, a very good number of improvements were already announced as part of the Basic Availability Groups. By the way, Hemantgiri, my friend and former Data Platform MVP, already talked about this on his post.

One of the improvements that got my attention was the ability to set up the AlwaysOn Availability Groups sit in a multi-domain cluster, or even a domain-detached cluster. I tested both and indeed this works very well, but the maintenance is slightly different (read: more difficult).

We need to consider the following points:

  • The cluster manager is not supporting this, everything should be done by using PowerShell.
  • To perform the setup, you need to be connected as a local Administrator.
    • The “Administrator” user password should be the same on all involved nodes.
  • For some tasks, like SQL Server upgrade/update, you will need to run the setup using the local administrator account.
    • You don’t need use the Administrator account to log in. Use your regular account, select the Run as different user menu item, and press the SHIFT key while you right-click the file.
  • You must be using Windows Server 2016 and SQL Server 2016.

The new capability is a group effort between SQL Server and the Windows development team and is, in my opinion, a very good option. I see the multi-domain capability as a better option than a detached-domain, and I already have customers who can benefit very well from this option.

The following approach is the same; it doesn’t matter if you are making a multi-domain setup or domain-detached cluster; however, there are some particular details:

For Domain-detached cluster

In this particular case, the Failover Cluster is created without any associated computer objects, and for this reason, each involved node needs to have a defined primary DNS suffix.


For Multi-domain cluster

Make sure that you can reach all the nodes, using IP, server name, and FQDN. Take care with firewall rules as this may influence the cluster creation. Make sure that the trust relationship between the domains is well defined.


Creating the cluster

In my case, I created a multi-domain cluster, which was also a multi subnet one. The following command is the base to create the cluster:

New-Cluster –Name <Cluster Name> -Node <Nodes to Cluster> -StaticAddress <Cluster IP> -AdministrativeAccessPoint DNS

The catch here is the -AdministrativeAccessPoint DNS. Check the PowerShell New-Cluster command for more options.

To perform this command, open the PowerShell console as Administrator (you can either log in as local Administrator or open the console using the method that I explained earlier in this article), and execute the command. That’s it!

In my test laboratory, I created a cluster containing three nodes:

  • SQLCAFE.local (
  • LISBON.local (
  • SQLCAFE.local (

I executed the following command:

New-Cluster –Name W2016CLT01 -Node W2016SRV06.SQLCAFE.local , W2016SRV07.LISBON.local , W2016SRV08.SQLCAFE.local  -StaticAddress, -NoStorage -AdministrativeAccessPoint Dns


After the cluster is created, use PowerShell to perform all the administrative tasks. The following commands are useful:

  • Get-Cluster
  • Get-ClusterNode
  • Get-ClusterGroup
  • Get-ClusterGroup <Group Name> | Get-ClusterResource


Creating an Availability Group

The process of creating the availability group is the same as mentioned previously. Actually, we are lucky, because the SQL Server Management Studio has all we need to manage the AG, without the need to touch PowerShell. For basic management, SSMS is enough.

After the cluster is created, you can go ahead and enable the AlwaysOn Availability Groups feature. To perform this, just go the SLQ Server Configuration manager and access the SQL Server engine properties. In the AlwaysOn High Availability tab, select the Enable AlwaysOn Availability Groups check box. Click OK and restart the SQL Server engine.


After enabling the feature, just follow the usual procedure and use the wizard in the SSMS, T-SQL, or PowerShell, to create the Availability Group.


More Details

As this is a complex configuration, a careful DNS and Quorum configuration is recommended to ensure the highest service availability. As a result, a DNS replication might be needed. For the quorum, the new Cloud Witness (supported from Windows Server 2016) is recommended.



When installed on Windows Server 2016, more flexibility is given to SQL Server 2016, including DTC support for AG, and Cloud Witness. Multi-domain and domain-detached cluster increases the architecture options when using SQL Server. This is just one of the improvements coming with SQL Server 2016. Stay tuned for more news!


Categories: DBA Blogs

Pythian-led Events: Embracing Disruptive Technologies

Tue, 2016-02-16 14:24

Today’s senior IT professionals are expected to be experts in everything from emerging technologies, to new regulations, and business innovation. Keeping up with the latest advances in technology, and understanding how they impact your business can be challenging.

The impact of today’s disruptive technologies on how we do business, led Pythian to introduce Velocity of Innovation, a series of events for senior IT professionals. These are Pythian’s exclusive thought-leadership events where a moderator-led panel engages in lively conversations around today’s disruptive technologies.

From our Velocity of Innovation series, to CIO summits and CIO forums, Pythian is leading the conversation around technologies that can transform business. Pythian focuses on creating positive outcomes for our clients, such as better, faster software development; high-performing, cost-effective systems; data-driven insights; and the adoption of disruptive technologies for competitive advantage.

Pythian will continue to lead conversations around disruptive technologies, bringing our vendor agnostic approach to a range of IT challenges. On March 16,  Pythian will be participating in the Global Business Intelligence CIO UK Event .

This event brings senior IT professionals together for one day to focus on topics ranging from digital transformation to cyber security.


If you are interested in attending an upcoming Velocity of Innovation event in a city near you, please contact

Categories: DBA Blogs

Step-by-Step Guide to January 2016 PSU Patch Apply on 12c Grid and RDBMS Homes in Linux

Fri, 2016-02-12 07:52

Following step by step action plan is for single instance database stored on ASM in on Linux (OEL 6 64 bit in this case.)

StepDescriptionETA1Update the OPATCH utility:


For Database home:


$ unzip -d /u01/app/oracle/product/12.1.0/db_1

$ /u01/app/oracle/product/12.1.0/db_1/OPatch/opatch version


For Grid home:


$ unzip -d /u01/app/oracle/

$ /u01/app/oracle/ version15 min2Create ocm.rsp file:


Note: Press Enter/Return key and don’t provide any input and say Yes.


$ export ORACLE_HOME=/u01/app/oracle/

$ $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /stage/ocm.rsp5 min3Validation of Oracle Inventory


Before beginning patch application, check the consistency of inventory information for GI home and each database home to be patched. Run the following command as respective Oracle home owner to check the consistency.


For database home:


$ /u01/app/oracle/product/12.1.0/db_1/OPatch/opatch lsinventory -detail -oh /u01/app/oracle/product/12.1.0/db_1


For Grid home:


$ /u01/app/oracle/ lsinventory -detail -oh /u01/app/oracle/


If this command succeeds, it lists the Oracle components that are installed in the home. Save the output so you have the status prior to the patch apply.5 min4Stage the Patch:


$ mkdir /stage/PSUpatch

$ cp /stage/ /stage/PSUpatch


Check that the directory is empty.

$ cd /stage/PSUpatch

$ ls


Unzip the patch as grid home owner.


$ unzip p22191349_121020_<platform>.zip5 min5One-off Patch Conflict Detection and Resolution:


Run it with root user:


/u01/app/oracle/ apply /stage/PSUpatch/22191349 -analyze -ocmrf /stage/ocm.rsp


It will ask to rollback identical patches like this:


Analyzing patch(es) on “/u01/app/oracle/” …

Patch “/stage/PSUpatch/22191349/21436941” is already installed on “/u01/app/oracle/”. Please rollback the existing identical patch first.

Patch “/stage/PSUpatch/22191349/21948341” is already installed on “/u01/app/oracle/”. Please rollback the existing identical patch first.

Patch “/stage/PSUpatch/22191349/21948344” is already installed on “/u01/app/oracle/”. Please rollback the existing identical patch first.

Patch “/stage/PSUpatch/22191349/21948354” is already installed on “/u01/app/oracle/”. Please rollback the existing identical patch first.


So first rollback above 4 patches by going to their directory and issuing with grid owner from grid home:


opatch rollback -id 21948354 -local -oh /u01/app/oracle/ (Repeat for all 4 patches)


Note: In some cases, weirdly, I had to shutdown the has services with root user before patch rollback by using:


/u01/app/oracle/ stop has -f


After this again run:


/u01/app/oracle/ apply /stage/PSUpatch/22191349 -analyze -ocmrf /stage/ocm.rsp


If analyze command fail then use this with root user:


$ORA_GI_HOME/crs/install/ –postpatch


It will start the has services too.


Then again run the analyze command as given above:


It will show something like:


Analyzing patch(es) on “/u01/app/oracle/” …

Patch “/stage/PSUpatch/22191349/21436941” successfully analyzed on “/u01/app/oracle/” for apply.

Patch “/stage/PSUpatch/22191349/21948341” successfully analyzed on “/u01/app/oracle/” for apply.

Patch “/stage/PSUpatch/22191349/21948344” successfully analyzed on “/u01/app/oracle/” for apply.

Patch “/stage/PSUpatch/22191349/21948354” successfully analyzed on “/u01/app/oracle/” for apply.


Now you are good to apply the patch. Proceed to next step.




 10 min6Apply the Patch: (Note: This should apply patch in both GI and RDBMS Home but its unreliable in that sense so after this completes, we need to check opatch lsinventory to make sure that it also applied patches in RDBMS Home)


As root user, execute the following command:


# /u01/app/oracle/ apply /stage/PSUpatch/22191349 -ocmrf /stage/ocm.rsp


In case if it doesn’t apply in RDBMS Home, then run:


/u01/app/oracle/product/12.1.0/db_1/OPatch/opatchauto apply /stage/PSUpatch/22191349 -oh /u01/app/oracle/product/12.1.0/db_1 -ocmrf /stage/ocm.rsp


Make sure the above applies both OCW and PSU patches. You can verify that from opatch lsinventory. If only OCW patch is present in output and no PSU (which is likely the case), then issue following from Oracle home with oracle database owner after shutting down database:


/u01/app/oracle/product/12.1.0/db_1/OPatch/opatch apply -oh /u01/app/oracle/product/12.1.0/db_1 -local /stage/PSUpatch/22191349/2194835460 min7Loading Modified SQL Files into the Database:


% sqlplus /nolog

SQL> Connect / as sysdba

SQL> startup

SQL> quit

% cd $ORACLE_HOME/OPatch

% ./datapatch -verbose60 min8Check for the list of patches applied to the database.


SQL> select action_time, patch_id, patch_uid, version, status, bundle_series, description from dba_registry_sqlpatch;5 min

Categories: DBA Blogs

Log Buffer #460: A Carnival of the Vanities for DBAs

Thu, 2016-02-11 14:34

This Log Buffer Edition covers blog posts from Oracle, SQL Server and MySQL for this week.


APEX shuttle item with one direction

Wondering about which tasks and work products are essential for your project?

Using Spark(Scala) and Oracle Big Data Lite VM for Barcode & QR Detection

Cloning Oracle Home on fully patched 11.31 HP-UX hangs

An UNDO in a PDB in Oracle 12c?

SQL Server:

SQL Azure Performance Benchmarking

Monitoring In-Memory OLTP: What’s Important?

Find and Remove Duplicate Records SQL Server

A Database to Diagram For

Getting started with R scripts and R visuals in Power BI Desktop


MySQL Support People – Percona Support

How to Install Redmine 3 with Nginx on Ubuntu 15.10

The magical abandoned .ibd files after crash recovery with MySQL 5.7.10

How To Speed Up MySQL Restart (hint: just like before, but this time for real)

OmniSQL – Massively Parallel Query Execution

Categories: DBA Blogs

Oracle RAC on Azure

Thu, 2016-02-11 14:31

Microsoft Azure provides an acceptable and affordable platform for a training environment. I am an Oracle DBA, and use it to test functionality, new technologies and features of different Oracle products. Azure supplies a template for Oracle linux and it can be used to run a single database, but when we try to create an Oracle RAC, we hit two major issues.

In the first, the Azure virtual network doesn’t support multicast and, as result, cannot be used for interconnect. The second issue is shared storage. Azure provides shared file storage, and you can access it using SMB-2 protocol, but it isn’t exactly what we need for RAC. How we can solve or workaround those problems? I will share my experience and show how I can setup a RAC on Azure.

For a two node RAC we first need to create at least two virtual machines for the cluster nodes. I’ve chosen Oracle Linux 6.4 from Azure Marketplace. I decided to create the machines with 2 network interfaces where one will be used for public, and another will be used for private interconnect. Here is my blog post how to create a VM with 2 network interfaces. It may not be necessary since you can fork a virtual interface out of your only public network, but I decided to go this way and create cluster nodes with two interfaces.

Here is output for the network from the first node:

[root@oradb5 network-scripts]# ifconfig
eth0 Link encap:Ethernet HWaddr 00:0D:3A:11:A3:71
inet addr: Bcast: Mask:
inet6 addr: fe80::20d:3aff:fe11:a371/64 Scope:Link
RX packets:776 errors:0 dropped:0 overruns:0 frame:0
TX packets:789 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:96068 (93.8 KiB) TX bytes:127715 (124.7 KiB)

eth1 Link encap:Ethernet HWaddr 00:0D:3A:11:AC:92
inet addr: Bcast: Mask:
inet6 addr: fe80::20d:3aff:fe11:ac92/64 Scope:Link
RX packets:2 errors:0 dropped:0 overruns:0 frame:0
TX packets:9 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:722 (722.0 b) TX bytes:1166 (1.1 KiB)

We need to install oracle-rdbms-server-12cR1-preinstall-1.0-14.el6.x86_64 rpm. It will install all required packages and set up kernel and limits for oracle user on our boxes :

yum install oracle-rdbms-server-12cR1-preinstall-1.0-14.el6.x86_64

The next step is to enable multicast support on the network for interconnect. You can read how to enable the multicast support in my other blog. As result you are getting a network interface edge0 which can be used now for our private network. Here is output of the ifconfig after crating virtual interface with support of multicast:

[root@oradb5 ~]# ifconfig
edge0 Link encap:Ethernet HWaddr 9E:1A:D8:0B:94:EF
inet addr: Bcast: Mask:
inet6 addr: fe80::9c1a:d8ff:fe0b:94ef/64 Scope:Link
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:3 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:500
RX bytes:0 (0.0 b) TX bytes:238 (238.0 b)

eth0 Link encap:Ethernet HWaddr 00:0D:3A:11:A3:71
inet addr: Bcast: Mask:
inet6 addr: fe80::20d:3aff:fe11:a371/64 Scope:Link
RX packets:118729 errors:0 dropped:0 overruns:0 frame:0
TX packets:62523 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:143705142 (137.0 MiB) TX bytes:20407664 (19.4 MiB)

eth1 Link encap:Ethernet HWaddr 00:0D:3A:11:AC:92
inet addr: Bcast: Mask:
inet6 addr: fe80::20d:3aff:fe11:ac92/64 Scope:Link
RX packets:9 errors:0 dropped:0 overruns:0 frame:0
TX packets:271 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:1274 (1.2 KiB) TX bytes:43367 (42.3 KiB)

I’ve used multicast tool from Oracle support document

Grid Infrastructure Startup During Patching, Install or Upgrade May Fail Due to Multicasting Requirement (Doc ID 1212703.1)

The check was successful:

[oracle@oradb5 mcasttest]$ ./ -n oradb5,oradb6 -i edge0
########### Setup for node oradb5 ##########
Checking node access 'oradb5'
Checking node login 'oradb5'
Checking/Creating Directory /tmp/mcasttest for binary on node 'oradb5'
Distributing mcast2 binary to node 'oradb5'
########### Setup for node oradb6 ##########
Checking node access 'oradb6'
Checking node login 'oradb6'
Checking/Creating Directory /tmp/mcasttest for binary on node 'oradb6'
Distributing mcast2 binary to node 'oradb6'
########### testing Multicast on all nodes ##########

Test for Multicast address

Nov 24 16:22:12 | Multicast Succeeded for edge0 using address

Test for Multicast address

Nov 24 16:22:13 | Multicast Succeeded for edge0 using address
[oracle@oradb5 mcasttest]$

So, we have solved the first obstacle and need to get shared storage for our RAC. We have at least a couple of options here, and I believe somebody can advise us on others. We can use NFS based shared storage, or we can use iscsi for that. You may choose something from the Azure Marketplace like SoftNAS or Stonefly or you may decide to create your own solution. In my case I just faired another Oracle Linux VM, added couple of storage disks to it using portal, and then set up NFS server on that machine. Here is the high level description for that:
We create a linux based VM on Azure using Oracle Linux 6.4 template from Marketplace. The size will be dictated by your requirements. I called the machine oradata.
I’ve added a 20 Gb disk to the oradata machine through the Azure portal, and created a partition and filesystem on it:

[root@oradata ~]# fdisk -l
[root@oradata ~]# fdisk /dev/sdc
[root@oradata ~]# mkfs.ext4 /dev/sdc1
[root@oradata ~]# mkdir /share
[root@oradata ~]# mkdir /share/oradata1
[root@oradata ~]# e2label /dev/sdc1 sharedoradata1
[root@oradata ~]# vi /etc/fstab
[root@oradata ~]# mount -a
[root@oradata ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 7.4G 1.4G 5.7G 19% /
tmpfs 1.7G 0 1.7G 0% /dev/shm
/dev/sda1 485M 50M 410M 11% /boot
/dev/sda2 2.0G 67M 1.9G 4% /tmp
/dev/sdc1 20G 4.2G 15G 23% /share/oradata1
/dev/sdb1 60G 180M 56G 1% /mnt/resource
[root@oradata ~]#

Installed necessary utilities using yum:
[root@oradata ~]# yum install nfs-utils
Configured NFS server on the box:

[root@oradata ~]# chkconfig service nfs on
[root@oradata ~]# vi /etc/exports
[root@oradata ~]# cat /etc/exports
[root@oradata ~]# service nfs restart
[root@oradata ~]# showmount -e
Export list for oradata:

Configure or stop firewall(You may need to do it on your cluster nodes as well) :

[root@oradata ~]# service iptables stop
iptables: Flushing firewall rules: [ OK ] iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Unloading modules: [ OK ] [root@oradata ~]# chkconfig iptables off
[root@oradata ~]#

On your cluster nodes you need add the mountpoint for your shared storage to /etc/fstab and mount it.

[root@oradb5 ~]# vi /etc/fstab
[root@oradb5 ~]# cat /etc/fstab | grep nfs
oradata:/share/oradata1 /u02/oradata nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,noac,actimeo=0,vers=3,timeo=600 0 0
[root@oradb5 ~]# mount -a
[root@oradb5 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 7.4G 2.5G 4.6G 36% /
tmpfs 3.5G 0 3.5G 0% /dev/shm
/dev/sda1 485M 69M 391M 15% /boot
/dev/sda2 2.0G 86M 1.8G 5% /tmp
/dev/sdc1 60G 12G 45G 21% /u01/app
/dev/sdb1 281G 191M 267G 1% /mnt/resource
20G 4.2G 15G 23% /u02/oradata
[root@oradb5 ~]# mount | grep /u02/oradata | grep -v grep
oradata:/share/oradata1 on /u02/oradata type nfs (rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,noac,actimeo=0,vers=3,timeo=600,addr=
[root@oradb5 ~]#

Now we have the required storage for OCR and Voting disks, network for public and interconnect, and can install our cluster.
We need to correct /etc/hosts file on both nodes (you may choose to use Azure DNS service instead).

[oracle@oradb5 ~]$ cat /etc/hosts localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 oradb5 oradb6 oradb5-vip oradb6-vip oradb-clst-scan oradb5-priv oradb6-priv oradata
[oracle@oradb5 ~]$

You can see I setup the public,VIP and SCAN in the hosts file. Of course it is not acceptable for any production implementation or if you want to have more than one scan. As I’ve already mentioned above you can use DNS for proper installation.
We copy required software to one of the nodes, unpack it and create a response file for installation like:

[oracle@oradb5 ~]$ cat grid.rsp

The file can be used for silent installation. You may choose instead to use runInstaller in GUI mode.
To run installation in silent mode you just need to go to your unpacked software and run:

[oracle@oradb5 grid]$ ./runInstaller -silent -responseFile /home/oracle/grid.rsp -ignoreSysPrereqs -ignorePrereq
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 415 MB. Actual 1350 MB Passed
Checking swap space: 0 MB available, 150 MB required. Failed <<<>> Ignoring required pre-requisite failures. Continuing…

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-02-01_09-41-01AM. Please wait …

You’ve of course noticed that I’ve run the installation ignoring requirements. As a matte of fact, I ran it without ignoring, checked the failing checks, made necessary adjustments for those checks, and then I decided they were important and left other as they were. As example my /etc/resolve.conf file was different due to settings on dhcp server and so on. I advise to apply common sense and your knowledge to decide what checks are important for you and what can be ignored.
Your installation will be completed and all you need to run is a couple of scripts to finish the installation.

As a root user, execute the following script(s):
1. /u01/app/12.1.0/grid/

Execute /u01/app/12.1.0/grid/ on the following nodes:
[oradb5, oradb6]

Run the script on the local node first. After successful completion, you can start the script in parallel on all other nodes.

Successfully Setup Software.
As install user, execute the following script to complete the configuration.
1. /u01/app/12.1.0/grid/cfgtoollogs/configToolAllCommands RESPONSE_FILE=

1. This script must be run on the same host from where installer was run.
2. This script needs a small password properties file for configuration assistants that require passwords (refer to install guide documentation).

We run the on each node one by one as user root and execute configToolAllCommands script as user oracle on the node we have run our installation. The response file would be required if we specified password for ASM,ASM monitoring or for DBCA. Here is an example of the file contents:


Change permission for the file to 600 before running the script:

[oracle@oradb5 grid]$ vi /home/oracle/
[oracle@oradb5 grid]$ chmod 600 /home/oracle/

We don’t have any ASM in our installation or BMS console but I will leave the file nevertheless just for reference.
Here is an output what we ran on our system :

[root@oradb5 ~]# /u01/app/12.1.0/grid/
Check /u01/app/12.1.0/grid/install/root_oradb5_2016-02-01_10-21-07.log for the output of root script

[root@oradb6 ~]# /u01/app/12.1.0/grid/
Check /u01/app/12.1.0/grid/install/root_oradb6_2016-02-01_10-38-50.log for the output of root script

[oracle@oradb5 grid]$ /u01/app/12.1.0/grid/cfgtoollogs/configToolAllCommands RESPONSE_FILE=/home/oracle/
Setting the invPtrLoc to /u01/app/12.1.0/grid/oraInst.loc

perform – mode is starting for action: configure

Keep in mind the configToolAllCommands should also create the management database in your cluster. If somehow it was failed you can try to recreate it using dbca in silent mode like :

/u01/app/12.1.0/grid/bin/dbca -silent -createDatabase -createAsContainerDatabase true -templateName MGMTSeed_Database.dbc -sid -MGMTDB -gdbName _mgmtdb -storageType FS -datafileDestination /u02/oradata/ocr/oradb-clst/mgmtdb -datafileJarLocation /u01/app/12.1.0/grid/assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -skipUserTemplateCheck -oui_internal

The RAC is created and now it can be used for application high availability or for databases tests. You may install a database software on the RAC either using GUI installer or silent mode, but don’t forget to specify cluster nodes during installation. I would also like to mention that I would not recommend installing it as production system, but it is quite suitable for tests or to experiment if you want to verify or troubleshot some RAC specific features.

Categories: DBA Blogs

Resolving Update-Update Conflict in Peer-to-Peer Replication

Mon, 2016-02-08 15:20

Recently I had received a hand-off ticket which was about a replication issue. The system has been configured with the replication of Peer-to-Peer type.

One of the subscribers was throwing an error which was reading like A conflict of type ‘Update-Update’ was detected at peer 4 between peer 100 (incoming), transaction id 0x000000000011a1c3 and peer 100 (on disk). While I was working on this issue and trying to resolve it, I noticed that it wasn’t showing any records in msrepl_errors table or conflict_dbo_table. p2p1 p2p2

Here again, the Error Logs help, as they have the complete details logged in, which help us identify the table name and exact error with the record. If that hadn’t been the case, I would have followed the Replication Troubleshooting method describe in KB 3066750 to fix the issue.

At this time, with the information we had in hand, we reached out to the customer and resolved the issue by fixing it manually. I would like to mention that there are always two ways conflicts are handled in P2P replication:

1) Manually fix the conflict/data issue
2) Let the winner node have precedence about the data/conflict In P2P replication

At the time of configuration, we will have an option to choose which node will have precedence and can be declared the winner. This is decided by the way of originator_i; the highest originator_id will win. We will have to decide this carefully, as once the setup is done, orginator_id is allotted it can not be altered later.

Here are few reference article that will help you understand this topic better:


Categories: DBA Blogs

New ORAchk beta

Mon, 2016-02-08 15:17


Oracle recently released new beta version for the ORAchk utility. If you are an Oracle DBA and still not friendly with the utility, I advise you to try it out. In short, the utility is a proactive tool and scan your system for known issues providing an excellent report in html format. In addition to that, you are getting collection manager to manage reports for multiply databases, check for upgrade readiness and other features. I strongly recommend trying the utility and using it regularly.
You can download the new version of the ORAchk, Health Check Catalog and all related support files and guides from Oracle support (Document 1268927.2). Simply unzip the ORAchk to a directory and run the orachk preferably as root since it allows to execute all system wide checks. Here is an example:

[oracle@bigdatalite u01]$ mkdir orachk
[oracle@bigdatalite u01]$ cd orachk/
[oracle@bigdatalite orachk]$ unzip ../distr/
Archive: ../distr/
inflating: CollectionManager_App.sql
inflating: sample_user_defined_checks.xml
creating: .cgrep/
[oracle@bigdatalite orachk]$ su -
[root@bigdatalite ~]# cd /u01/orachk/
[root@bigdatalite orachk]# ./orachk

At the end you are getting an html report and zip file with results of all executed checks:

Detailed report (html) – /u01/orachk/orachk_bigdatalite_orcl_012816_151905/orachk_bigdatalite_orcl_012816_151905.html

UPLOAD(if required) – /u01/orachk/

The report is really good looking, split to different sections, and allows you to hide or show checks based on their status.

I compared the new version against The execution time for the new version was 3 minutes versus 8 minutes for the old one. The new format for report was way more usable; you don’t need to jump back and forth since result for every check expand on the same place.
If you haven’t used the utility so far I highly recommend you download and try it out.

Categories: DBA Blogs

Log Buffer #459: A Carnival of the Vanities for DBAs

Mon, 2016-02-08 14:40

This Log Buffer Edition arranges few tips and tricks from the blogs of Oracle, SQL Server and MySQL.


Oracle ® Solaris innovation is due in part to the UNIX® the standard (1), the test suites (2) and the certification (3). By conforming to the standard, using the test suites and driving to certification, Oracle ® Solaris software engineers can rely on stable interfaces and an assurance that any regressions will be found quickly given more than 50,000 test cases.

Building on the program established last year to provide evaluation copies of popular FOSS components to Solaris users, the Solaris team has announced the immediate availability of additional and newer software, ahead of official Solaris releases.

Tracing in Oracle Reports 12c.

Issues with Oracle Direct NFS.

An interesting observation came up on the Oracle-L list server a few days ago that demonstrated how clever the Oracle software is at minimising run-time work, and how easy it is to think you know what an execution plan means when you haven’t actually thought through the details – and the details might make a difference to performance.

SQL Server:

Manipulating Filetable Files Programatically

Auto-suggesting foreign keys and data model archaeology

Create/write to an Excel 2007/2010 spreadsheet from an SSIS package.

Tabular vs Multidimensional models for SQL Server Analysis Services.

The PoSh DBA – Towards the Re-usable PowerShell Script.


MyRocks vs InnoDB with Linkbench over 7 days.

MySQL has been able to harness the potential of more powerful (CPU) and larger (RAM, disk space.

Setup a MongoDB replica/sharding set in seconds.

MySQL 5.7 makes secure connections easier with streamlined key generation for both MySQL Community and MySQL Enterprise, improves security by expanding support for TLSv1.1 and TLSv1.2, and helps administrators assess whether clients are connecting securely or not.

While EXPLAIN shows the selected query plan for a query, optimizer trace will show you WHY the particular plan was selected. From the trace you will be able to see what alternative plans was considered, the estimated costs of different plans, and what decisions was made during query optimization.

Categories: DBA Blogs

SQL On The Edge #8 – SQL Server Mobile Report Publisher

Fri, 2016-02-05 14:07

One of the cool things about SQL Server is that it comes bundled with all the Business Intelligence services with the core database engine license. Reporting Services (which includes the Mobile Report Publisher), Analysis Services, and Integration Services are all integrated, and are ready to get going as soon as you install SQL Server. This has made it not only cost-efficient for many organizations to deploy BI, but it has also contributed to a wide adoption among the SQL Server customer base.


What is the Mobile Report Publisher?

Currently in preview, the Mobile Report Publisher is a new report and dashboard editor that publishes reports to Reporting Services, and it’s part of the bigger road map that Microsoft has for their Business Intelligence On-Premises story. We all know that in the cloud, Power BI has been getting a large amount of investment, but with on-premises there was a big gap that was getting wider and wider, until now.

With this upcoming SQL 2016 release, the Microsoft team is focusing on bringing Reporting Services into the future as a one-stop integrated solution for BI deployment so that cloud BI or a competitor’s product (*cough*Tableau*cough) are not the only modern alternatives.

This Reporting Services refactor is the biggest change made to the product since SQL Server 2005 was released over 10 years ago. Leveraging the best parts of the Datazen acquisition, the Microsoft team is looking to provide a cohesive BI story that integrates web-based and mobile reports, Report Builder reports, and Power View style of modern visuals.

How is it different?

You’re probably used to working with SSRS Report Builder and are wondering what the idea is with Mobile Report Publisher. The demo below will make it very clear, but let’s just say that reports have now been split in two types:

1. Paginated reports: this is the ‘legacy’ style report that is built by Report Builder. It looks more flat, has the same controls as before, and is fully compatible with all the existing reports you have already deployed.

2. Mobile reports: Even though the name says ‘mobile’ these reports work just as well on desktop, tablet and mobile. If you’re familiar with web design, then the appropriate term would be that these reports are “responsive”. They can be done once, and will display nicely across devices. This is the new experience that the Mobile Report Publisher is targeting.


Where do we go from here?

Right now the current version is SQL Server 2016 CTP 3.2. We still have several different versions that will be released before SQL 2016 goes RTM later on this year. Currently, a lot of the planned functionality is not entirely there for the main SSRS interface, and you will be asked to switch to the ‘”classic” view often.

The Mobile Report Publisher experience is also very much targeted towards creating a visualization, and there’s no data modeling to be found. You pretty much need to have your datasets pre-made and in the format that the tool will expect, or you won’t get very far. Hopefully, at some point the team will add modeling capabilities like the robust experience we already have on the Power Bi desktop tool.

If you want to practice and get your feet wet with this release you can do it now, and for free by visiting SQL Server 2016 CTP 3.2. You can also preview the Mobile Report Publisher. Like I said before, this is a big departure so most people will want to train up to be ready when the new versions go live.



In the demo below I’m going to walk through the new SSRS interface preview in CTP 3.2, and then walk through creating a quick dashboard for both desktop and mobile consumption using the publisher. Let’s check it out!

Discover more about our expertise with SQL Server in the Cloud.

Categories: DBA Blogs