Best Practice Considerations for Database Management System (DBMS) Migration
With the seemingly endless efforts by organizations to migrate their data storage to environments that are less expensive and infinitely scalable, certain Best Practice Considerations for Database Management System (DBMS) Migration need to be addressed. Many organizations use SAS Software and its respective architecture to deliver important insights for decisions by pushing their analytics into a DBMS. Of course, there are some who are just using the DBMS to capture/store the data and then move sub-sets of the data to their SAS environment for analysis with SAS Software. For a comprehensive guide on SAS and databases, see my colleague Jeff Baily's article: An Insider's Guide to SAS and Database Support Information.
Best practice considerations
Understanding the approach to where the data should be processed is a critical best practice. The depiction in Figure 1 illustrates processing guidance in respect to where the data resides.
The story in Figure 1 is especially relevant with Big Data, where not moving large volumes of data any more than necessary, is very important. As a best practice, push the analytics to the database for processing. Once the majority of data manipulation completes, move a smaller result set to the SAS environment (SAS9 or Viya) for the remaining analytics. If the SAS environment happens to be architected within the Big Data platform (co-located), then the movement and processing of the data is highly efficient with parallel and in-memory execution.
When working with customers and their Big Data environments, it is evident that being satisfied with a single data storage platform type indefinitely is never going to happen. Technology and data growth is advancing at a rate that drives enterprise storage platform migration to occur every three to seven years. I remember the days when data was punched on 80-byte cards and then transferred to magnetic tape (early-mid 1980’s). Now, in the interest of supporting data Volume, Variety, Velocity, and Veracity (V4) there is a never-ending migration from one platform to another constantly in play.
That’s why I developed the table in Figure 2 as a general guide (please reference the attached PDF for relevant active links). It seems every customer I have visited over my past six years of working at SAS has been involved in a database migration effort.
The point of the table in Figure 2 is to help customers remember some key Best Practice Considerations when migrating from one DBMS to another. I have helped many customers with this, so I decided to try capturing it in a single page for use as a checklist and resource reference. The PDF version of the table containing active links to relevant material to help migrate SAS processes is attached to this article. It is grouped in such a way to address three components of migration that seem to be most relevant: hardware, software, and data. Hopefully, this will add value to the tedious effort of migration from one DBMS environment to another. It is also critically important when running validation tests during the migration process. The lack of consistency during any comparison of performance between DBMS environments is “apples to oranges” if there are significant variations in the categories noted within the comparison table.
Memory Lane Moment: This is nothing new. My time at SAS is not my only experience with database migration. For me, it was happening when I was first introduced to using business technology to migrate manually captured data to computer database technology in 1982. WOW! That brings back fond memories…LOL!
Goals
The goal is to deliver trusted analytics for critical business insights and decisions from the data stored in the DBMS. A DBMS migration can have undesired results if the data ends up Dirty. To help remediate this potential Dirty Data result, the "Best Practice" data management process flow depicted in Figures 3 and 4 should be considered.
In order to manage the data properly through the entire process from access to delivery, it is extremely important to leverage the Best Practice Considerations for DBMS Migration. Figures 3 and 4 illustrate the importance of paying attention to the details and maintaining appropriate control and validation "Best Practices" throughout the process.
Of course, collaboration among the DBMS and SAS Administrators, along with the SAS Users is extremely important as illustrated in Figure 5.
Take the time to establish the necessary relationships while managing reasonable expectations to ensure success. Over time the results of collaboration among technical resources will be a huge value return from the analytics being executed against the DBMS data, regardless of where it is migrated.
Finally
Thank you for taking the time to consider these best practices and I would be happy to clarify anything that is shared in this article. My objective is to help organizations (Administrators and SAS Users) achieve success using SAS to deliver valuable insights for decisions in their organization.
