By: Jeremy Kadlec | Comments (2) | Related: More > DBA Best Practices
Problem
All systems generally follow the same basic life cycle from inception, development, maintenance and sun-setting. However, some systems seem to take a slightly different path where they are re-architected either in entirety or particular modules of the application are significantly re-architected. Re-architecting a production backend system that needs to continue business operations presents a significant challenge. Although the positive side of the situation is that the problems that system suffers from have already surfaced and the business processes have been tested to determine if they are beneficial or not. Now it is a matter of rolling up your sleeves re-architecting. So where do we start?
Solution
Re-architecting an existing system in many respects can follow one of many development life cycles, but at least two key differences exist when a small team is addressing the project. First is that the systems problems have already surfaced which should ease the requirements, development and testing phases. As such, you should have less of a problem nailing down the requirements. Second is the need to maintain the existing system while building a bigger and better solution. Often times these two goals move down diverging paths when a small team needs to address both the current and future needs. To hopefully alleviate some of those pressures, the following checklist can be used to set expectations for a successful project:
10 Step Checklist: Re-Architecting a SQL Server Backend System
Step 1 - Problem Identification
- Just the facts - Be sure to make the problem identification
factual. For example, here might be some problems such as:
- OLTP and reporting transactions are issued in the same database. When reports run they have a performance impact on the OLTP users.
- With the existing system it is not possible to upgrade Internet Explorer to the latest version.
- Multi-valued data is stored in a number of columns.
- No referential integrity exists between any of the tables.
- Units of measure are not consistent in the system for example in one set of tables feet are used, in another portion inches are used, in another millimeters are used, etc. so we have to convert the data to a common unit of measure.
- Consistent names are not used across the database or front end code, so changing code can be error prone.
- The existing reporting is requires a significant amount of time, is incorrect and requires manual steps.
- Security is elevated in order for users to do their job on a daily basis.
Step 2 - Management Support
- The balancing act - First and foremost, ensure you have managerial support for correcting old nagging maintenance problems versus building new technologies. From a business perspective, the problems may not be an issue because IT takes care of them. This is where you need to make the case for an ROI where IT will be able to re-architect the existing system and free up time for new functionality over a 1, 2 or 3 year period.
- Resources - Work with your IT management to let them know the issues of the current system and how you plan on resolving them. Let them know the resources needed. Whether it is training, books, no distractions, etc. Ask your management to speak with the lines of business to ensure they are in agreement with all that needs to be accomplished.
- Time - Set a reasonable time line to re-architect the system. If the current system has been in production for 5 years, hopefully much of it can be re-used. The reality is that it is not going to take a week to resolve problems that have been patched for years.
- Goals - Not only focus on your technical goals, but also obtain feedback from the users and management. In many respects these goals should be your business requirements. Be sure to balance the business requirements with the IT goals so the end result is an overall better platform.
Step 3 - Re-architecture Goals
- Balancing time - Find a consensus on the team member's time for maintaining the current system while also building the new system. Communicate management's support and be sure to follow through with it on a daily or weekly basis.
- Performance - The user experience is always a top priority, so be sure to show the value of the re-architecture with the introduction of the new technology.
- Standardization - To each system this could mean a number of different things. The standardization could be from a coding perspective, object naming, etc. This could be taken further in terms of consistent date formats, consistent monetary units, etc. When you start the system, be sure to make standardization a priority, otherwise a new or different maintenance nightmare may pop-up.
- Modules - Re-use is the big item that comes to mind. Be sure that modules can be built and re-used across the environment. This should also help from a maintenance perspective when code needs to be changed. Think ahead so code can be changed in 1 place versus 5.
- Maintenance - Maintaining code is never a career goal, so look into ways to minimize it with the new system. Whether maintenance can be minimized with lookup tables, parameters in tables, XML configurations, etc, spend time figuring out how to keep the maintenance to a minimum.
Step 4 - Technology Selection
- What's good today? - As a portion of the design phase, be sure to take a good hard look at what needs to be done and how it is accomplished today. Then with an open mind see what alternatives are available in the market place. What was good when you built the system may be inferior to new techniques or technologies available today.
Step 5 - Systems Upgrade
- Split systems - If your databases have grown organically chances are systems have wound up in the same database. Where it makes sense, be sure to split them accordingly to ease future upgrades. The short term pain may be high, but in the long term upgrades should be much easier.
- SQL Server Upgrade - Right now the big question is to upgrade to SQL Server 2005 or 2008. Balance your needs, timeline and technology experience to select the correct platform. If you are a few versions behind with SQL Server or another technology, then make sure overall upgrades are planned for with your new system.
- Upstream\downstream systems - As you work through the re-architecture process, be sure to identify dependent systems so they continue operations as expected.
Step 6 - Design, Development and Testing
- Design - No surprises here, based on your goals, design a system that will meet your expectations. Loop back with the team to make sure what is in people's heads matches what is down on paper.
- Development - Whether you traditionally use a waterfall approach or an iterative approach, be sure to show progress and get the new system in the hands of the users as quick as possible.
- Testing - With an existing system, the test cases should be easily definable, since the system has been running for years. What new opportunity is probably available is to break out of the manual testing and search for opportunities to automate the test cases. This way time can be spent building test cases versus serially conducting testing.
Step 7 - Data Migration
- Porting the data - Depending on how the SQL Server system
re-architecture is conducted, porting the data could be a significant or insignificant
exercise. In scenarios where the existing system is corrected, porting
the data should not be a big deal. If parallel systems run for a period
of time, porting the data may be a batch process as well as a real time process
to eliminate a user's double entry.
Step 8 - Parallel Systems
- Parallel systems - As systems are migrated, one key risk
aversion decision that needs to be made is whether or not to support parallel
systems for a finite period of time to validate the new platform is working
properly or not. Either users can double enter the data or a replication
solution of sorts is needed. On a regular basis the data and output from
the two systems needs to be cross checked.
Step 9 - Deployment
- Deployment - Depending on how the system will be re-architected dictates how the deployment will occur. Be sure to test this portion of the process and validate it will go off without a hitch.
- Rollback - Just like the deployment, the rollback plan is dependent on how the system will be re-architected. Make sure your rollback plan is built and tested.
Step 10 - Maintenance
- Production support - Although maintaining a system will always be needed, hopefully with all of the planning that has taken place, the production support is minimal. Now time can be spent building new features and focusing on operational needs like high availability and disaster recovery.
Next Steps
- Re-architecting a system of any kind should not be taken lightly. One key point to keep in mind from a business perspective is that although the current system is not perfect it probably works to some degree. So be sure to not only have a technology justification, but also a business one as well. Their is a cost in re-architecting a system, so be sure to have a solid ROI at the completion of the project.
- Check out these MSSQLTips.com checklists:
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
