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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

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

JaybeeSQL
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.

Cheers

JB

  Previous Topic Topic Next 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.06 seconds. Powered By: Snitz Forums 2000