01/18/2019
by Marlon Ribunal
Comments Off on Miscellaneous Notes On System Architecture and Design

Miscellaneous Notes On System Architecture and Design

When I first joined the programming team at my previous job in an aerospace company, the first observation I made was that their Data Integration System was antiquated – architecture-wise. The system architecture was adopted from legacy mainframes. Most of the business processes have been migrated to modern servers at the time.

But as I dug deeper and deeper into the system, I slowly realized that the architecture stood the test of time and to the changing demands of the modern business world. Old ways of doing things are not necessarily a bad thing when they preserve the fundamental principles of a sound and stable architecture.

Here are some of the things I’ve learned from that experience:

Nothing Beats A Sound Architecture

Everything is anchored in the architecture. You cannot optimize a system that is based on a bad design. You might be skilled in performance tuning but you can only do so much when the problem is the architecture itself. What’s a good design? That’s open to debate. Simply put, a good design is one that allows for stability, flexibility, and scalability.

Leave the Data Validation in ETL

Cleansing of data must be done at the time of the ETL process. The database engine works well with Storing and Retrieving of data. Complicated validation and cleansing routines don’t do the database engine any good. SSIS, just like most ETL Tools, is an in-memory pipeline. Row-By-Row transformation and validations are efficiently handled in memory.

Platform is Just a Tool

One time I asked our Senior System Architect what Server to use – SQL 2008 or 2012 – he said: “I don’t care as long as it runs.” But I gathered what he really meant – that the architecture, when designed correctly, works regardless of the platform, and that a sound architecture does not have to depend on the target platform.

Cursor Doesn’t Scale

At this point, we should just agree that Cursor is a bad thing when there are other viable options that can do the job. Row-by-Row processing doesn’t scale. Period.

Beautiful SQL Doesn’t Translate To Performance

You need a gazillion of JOINs to return a dataset. Good luck with that one. You can write beautiful codes worthy of the Nobel Prize in Literature but the barometer of any codes is their performance. Readability is also a good attribute of good code. Your choice of query optimization techniques is limited by, guess what, the architecture. When you’re spending 70% – 80% of your maintenance effort on the query tuning, then maybe it’s time to re-think the design.

Errors Should Be Actionable

Error Alerts should prompt for action. The process should not just end in error logs or unactionable notifications. Push the errors to the proper stakeholders. The stakeholders should have the ability to correct the data on the fly – probably in a UI context. The corrected data should then be pushed back to the pipeline on the same batch as “late requirements” or exceptions. Don’t wait until the next scheduled batch to reprocess them. A late requirement process should be put in place as part of the system to handle such a case.

Good Design Enables

Good design is an enabler. User enablement is not a feature of the UI. It is an integral part of the system architecture. Good UI functions go as far as the flexibility of the design go – and not further. User Experience is therefore rooted in the architecture.

Institutionalize Peer Review

Let other people scrutinize your work. This is a good practice. There’s a reason peer review happens way before the deployment. Take advantage of the resources and bright minds around you. Let your teammates check your work. Peer Review may reveal potential problems that you might have overlooked during your unit testing or test cases.

You Cannot Over-Document

One cannot just over-document. Documenting is a good practice to develop. Document when necessary. And it’s almost always necessary to document in all circumstances. Sure you don’t have time to put together some quick documentation, but that’s not a good excuse to not do it. Do you want to secure your legacy in the annals of greatest employees? Yes, you got it. Document.

Building a robust system architecture involves a wide array of principles and checkboxes to tick as the project progresses from requirements to delivery. You’ll have a better chance of succeeding when you follow consistent and sensible fundamentals. Old, solid principles don’t die; they don’t fade either.

01/11/2019
by Marlon Ribunal
Comments Off on Book Review: SQL Server 2017 on Linux

Book Review: SQL Server 2017 on Linux

I don’t have any statistics but I think most IT Professionals in the SQL Server field are managing or developing solutions on their SQL Server Databases deployed on Windows Servers. If you’re planning to try SQL Server on Linux, this is a good book to get you started. Or, if your company is looking to deploy SQL Server into Linux, grab this book. Or, maybe you’re in a Linux shop that is considering SQL Server, that’s a solid reason to grab this book.

This book addresses some general groups of IT Pros:

  • Those Linux Admins new to the SQL Server implementation.
  • Oracle DB Admins migrating to SQL Server within their Linux Environment.
  • SQL Server Consultants who are looking to add another income stream.
  • Oracle Consultants who are looking to add another income stream.
  • Nix People who are interested in SQL Server.
  • SQL Server People who are interested in Linux.
  • People who want to add more tricks up their sleeve (chances are I belong to this group).

Published by McGraw Hill, SQL Server 2017 on Linux is a 285-page primer guaranteed to help you setup a Linux-Based SQL Server implementation. This is another good resource from Benjamin Nevarez (B|T). It is packed with great information, tips and tricks, and recommendations on installing, configuring, maintaining, tuning, optimizing, and securing SQL Server 2017 on Linux. I think this book accomplishes its goal – that is, provide comprehensive instruction on the subject.

Chapters 1 – 3: Cover Getting Started stuff – how to create VM, install the SQL Server, and its Components. Two particular Nix flavor mentioned are Ubuntu and SUSE Linux Enterprise Server. If you are into Containers, there is a portion dedicated to Docker.

If you want to understand How SQL Server works on Linux, Chapter 2 covers that. This chapter covers that backstory of how SQL Server-to-Linux came to be.

Not well-versed with the Linux OS? Chapter 3 provides the info to get you started using SQL Server. It covers both basic and advanced Linux Commands to get you up and running SQL Server on Linux in no time.

Chapters 4 – 6: Chapter 4 guides you through configuring SQL Server on the Linux Environment. It also provides more detail about the mssql-conf Utility – a tool that installs and configures SQL Server. Topics included are: NUMA, Swap Files, Trace Flags, Process Affinity, MAXDOP, Cost Threshold for Parallelism, and many more.

Chapter 5 is my favorite Chapter which covers SQL Server Query Tuning and Optimization. It even includes a mini-intro on the Query Processor Architecture, which is a good thing. Of course, it includes the usual suspects: Query Optimization, Execution Plans, Query Troubleshooting, Indexes, Statistics, among others.

Chapter 6 covers the New Query Processing Features of SQL Server such as Adaptive Query Processing, Batch Mode Adaptive Joins, Interleaved Execution for Multistatement Table-Value Functions and many more.

Chapter 7 – 8 Covers High Availability and Disaster Recovery and Security. Chapter 7 covers implementing Availability Groups – yes, this is available in SQL Server On Linux as noted in the book. There is also a short list of differences between availability groups on Windows vs Linux.

Chapter 7 also provides instruction and commands to setup Pacemaker, an open source high-availability resource manager to manage Pacemaker clusters, basically. Of course, the book demonstrates how to fail over an availability group in a Pacemaker cluster operation. If the tool does not work, a T-SQL workaround is also provided.

The book concludes a chapter about Security in Chapter 8. Topics covered are Transparent Data Encryption, Always Encrypted, Row-Level Security, Dynamic Data Masking, among others.

As you can see, this book provides everything that you need to get started using SQL Server on Linux. it pretty much covered the whole nine yards. It’s a 285-page book. You’re getting much meat in this book without you getting a deluge of information overload.

If you don’t think you’ll ever manage a SQL Server on Linux in the near future, you’ll still get a bang for your buck with this book. Most of the information, if not all, provided in Chapters 4 – 8 are generally applicable to any SQL Server implemented in Windows Server platform.

01/04/2019
by Marlon Ribunal
Comments Off on Looking Forward

Looking Forward

2018 has come and gone. And we’re on a new year once again. This is another opportunity for a new beginning and new goals to aspire for. For most of us, the new year brings the promise of a restart or reboot. We can start with a clean slate. The new year also brings us a more positive perspective and that confident mindset that thrusts us into yet unfamiliar territory. And, so, the new year also brings us excitement.

I started the past year with big, bold goals. It was anchored to my personal theme of “Career Year”. It’s a personal tradition that I choose a “word” (or mantra) for the year. It was Kaizen (continuous improvement) for 2017 and Daitan’na (大胆な ) which means audacious, daring or bold for 2018.

For 2019, my chosen word is Zanshin ( 残心 ). It is a state of total awareness.

Wikipedia illustrates the concept clearer:

“In kyūdōzanshin means the body posture after the loosing of an arrow; the posture is intended to reflect the higher meaning of zanshin, which is a mental aspect maintained before, during, and after an action.

In karatezanshin is the state of total awareness. It means being aware of one’s surroundings and enemies while being prepared to react.”

I have no enemies to speak of. The “enemies” referenced in the Wiki does not apply to me. For me, the concept applies to everything that I do – career, professional, personal, family, work, relationships, etc.

And so, my theme for this year, in conjunction with the “word”, zanshin, is Awareness – just being aware of my actions and how they affect my surroundings so I know what are the best options on how to react. Making adjustments is the key.

Like I said, this applies to everything that I do. One of the biggest goals I had last year was technical learning. I’d like to say that I tried everything I could to learn given the situation. But I wasn’t able to go deeper than I thought I could. As they say, ” Life is what happens to you while you’re busy making other plans.”

Kaizen has been beneficial to me on both professional and personal fronts. It has always been a part of my personal methods of improvement – that small but continuous improvement goes a long way. I have two particular systems that I integrate to my life this new year: Bullet Journal and Miracle Morning. If you’re into self-improvement stuff, I recommend these two.

Bullet Journal

It’s analog journal/planner/task manager/notebook/anything you want it to be. It’s a flexible system. Central to the system is the concept of Rapid Logging – dump everything in your brain into the page as quickly as you have them. Daily To-Do’s, Events, Notes, etc go to your Daily Log. Any future event such as appointments, birthdays, and any dated item that will happen in the future go to the Future log. The Monthly Log includes an overview of your month and tasks or events to accomplish for the month. You can have custom Collections that contain a list of books you want to read, grocery list, project to-do’s, etc.

Another nice idea built-in into the system is the concept of Migration – simply migrating undone tasks or any types of a list from one Daily Log to another or one month to another depending on the case. Threading holds the pages together although they might be on different part of your notebook.

The Index holds the system together. You don’t want to lose track of the stuff you put in the journal. You want to find that grandma’s recipe easily when you need it. I’ve always been a notebook guy – the analog type. I have multiple notebooks for different topics and lists. Bullet Journal integrates all these into one manageable system.

Miracle Morning

I am a nocturnal being. I’ve never been an early riser all my life. This changes with the Miracle Morning. Miracle Morning is a six-step routine or program that enables the practitioner to adopt healthy mental and productive states and well-being. It is based on the concept of habit stacking.

The Six-Step Routine is condensed into the acronym – SAVERS. Silence takes you to a moment of meditation or prayer. Mental priming comes easy with Affirmation. You can get in tune with your goals and wishes by Visualization. Exercise gets your blood pumped. You can use your Reading time for studying hard concepts or things that are beneficial to you. And, finally, Scribling which is a fancy term for journalling.

I will incorporate systems or methods into my self-improvement system as I see fit and adjust along the way. The Bullet Journal helps me track and measure my progress in achieving my goals. I’ve got plenty of personal and professional goals this year! The Miracle Morning turns me upside down. This is something I’ve never done before. Many of the successful figures I admire are morning people. Plus, I realize that learning technical stuff is hard after most of my willpower is exhausted throughout the day.

That said, I think much of the challenges I’ve encountered during my learning phases is that I have not learned to prioritize my learning. I would delay my reading time of technical books later in the day towards my bedtime – when I am already tired and just ready to go to bed. It didn’t work. Now, that will change.

Speaking of changes, I’ve avoided non-tech posts here in my blog since the beginning. That will change as well. I used to blog about Productivity and GTD hacks in a different blog. I want to incorporate that here. It will still be true to the original theme of this blog: SQL, Code, Coffee, etc. I will have more of that Coffee and etc. part.

This new year brings us new promises and hopes of a brighter days ahead. Few integral changes can only put us to a position where we can succeed.