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.
| 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 OLAP13) 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.CheersJB |
|
|
|
|
|
|
|
Recreating entire schema + reporting from scratch