10 Step Checklist: Re-Architecting a SQL Server Backend System
- 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.
- 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.
- 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.
- 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.
- 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.
||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.
- 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.
- 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.
- 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.
- 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.