Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Checklist to Re Architect a SQL Server Database


By:   |   Read Comments (2)   |   Related Tips: 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 Phase Activities
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.
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.
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.
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.
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.
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.
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.
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.
9 Deployment
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

 



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, January 18, 2008 - 2:24:31 PM - admin Back To Top

aprato,

Thanks so much for the input.  That is a great idea to maintain the backward compatibility.

Thank you,
The MSSQLTips.com Team


Monday, December 31, 2007 - 12:29:22 PM - aprato Back To Top

Just adding a little bit of my practical experience to this topic. I inherited a weakly developed data model with direct table access scattered throughout front end and middle tier code.   Imagine my frustration when a new business request came in that required new columns be added to a user table.  My first thought was "How the heck can I add these new columns and not screw up the application at the same time?".  I don't know where every single nook and cranny is and SELECT * calls were all over the place.  In fact, there had been so many developers who had passed through over the years,  no one knew where all the land mines were.  My solution was to mimic what Microsoft did to their SQL Server 2000 system tables in SQL Server 2005 - I turned these core tables into backward compatability views   I then created new versions of the tables with the new columns and used the backward compatability views to reference the new table which returned a resultset and column list consistent with the original version of the table.


Learn more about SQL Server tools