Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Recreating entire schema + reporting from scratch

Author  Topic 

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2013-12-26 : 15:03:08
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.

Cheers

JB

   

- Advertisement -