SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Recreating entire schema + reporting from scratch
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

112 Posts

Posted - 12/26/2013 :  15:03:08  Show Profile  Reply with Quote
Hi all,

As a Christmas present, I'm currently going to migrate my girlfriend's legacy martial arts school system to a beefier platform, so this is very much a labour of love, but I hope it will introduce me to the guts of normalising data, SSRS/AS, as well as a bit of SSIS.

Size: 36Mb;
Schema: 92 tables, requiring normalisation;
Architecture: 3 clients - 1 server (4 pc's).
Concurrency: Very low, unlikely for >2 users viewing/updating at a given time.

We want to move to an online hosted solution, with a local copy being available at checkpointed periods in case of network outage. We also want enhanced reporting, with the ability to slice/dice the data as wanted.

I'd like any and all advice about workflow, currently here's a skeletal list of how I see the plan progressing:

1) Gather requirements (reports/update functionality);
2) Create test environment (in SQL);
3) Load latest copy of data;
4) Study schema, identifying inefficient/inappropriately located columns;
5) Determine where above data should be relocated/regrouped;
6) Determine appropriate new constraints, PK's/FK's (these exist in Legacy but will not be present in test copy);
7) Create scripts to map and load data to normalised schema/datatypes in Test;
8) Load, and test data validity;
9) Create data cleanse package (SSIS), as some data possibly defunct, thus risking skewed reporting;
10) Cleanse Data;
11) Create Data Warehouse and export from OLTP to OLAP (SSAS);
12) Determine schedule to load from OLTP to OLAP
13) Create reporting platform (SSRS);
14) Create Web front-end;
15) Check connectivity (from all clients to DW/OLTP);
16) Create reports;
17) Test reports;
18) Load most recent data;
19) Run data cleanse package;
20) Run both legacy and new platform in parallel for 2 weeks, if all successful then decommission legacy;
21) Create further reports as desired.

In particular I'd like to know whether the community feels an off-the shelf data migration tool should be used, any tips for streamlining the schema familiarisation/normalisation process, which - let me be honest - is by far the most daunting aspect, and the one I foresee being a huge chunk of the project time.



  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000