Enter_Kratos
asked on
SQL Server 2012 (SSMS) Converting database to new format
Hi Guys,
I'm currently working on an on-going project which I've inherited. It's a poorly designed, barely relational database which I have been working to try and systematically upgrade. I have a live db and a test one which I implement all my changes on before rolling them out live.
Normally it is not too much trouble to make the changes necessary to the live db when it's time to deploy, however in the most recent version I have made many changes which are documented yet still a very time consuming process to manually change.
In many columns I have removed hard-coded, repeated values and replaced them with mapped values to other tables in an effort to make it more relational .
What is the normal method for migrating to new db versions? Presumably this will include creating a number of scripts to copy the values and perform any necessary conversions or potential lookups.
Are there any tools designed to aid in this sort of thing? The only ones I could find were designed to convert from different database formats e.g MySQL, Access etc.
Are there any tolls that can record manual changes and reapply them if a rollback is necessary?
Thanks in advance.
Dan
I'm currently working on an on-going project which I've inherited. It's a poorly designed, barely relational database which I have been working to try and systematically upgrade. I have a live db and a test one which I implement all my changes on before rolling them out live.
Normally it is not too much trouble to make the changes necessary to the live db when it's time to deploy, however in the most recent version I have made many changes which are documented yet still a very time consuming process to manually change.
In many columns I have removed hard-coded, repeated values and replaced them with mapped values to other tables in an effort to make it more relational .
What is the normal method for migrating to new db versions? Presumably this will include creating a number of scripts to copy the values and perform any necessary conversions or potential lookups.
Are there any tools designed to aid in this sort of thing? The only ones I could find were designed to convert from different database formats e.g MySQL, Access etc.
Are there any tolls that can record manual changes and reapply them if a rollback is necessary?
Thanks in advance.
Dan
Vitor Montalvão
How did you make those changes? The correct way is by script and not using SSMS GUI interface so you can run all scripts with all changes in the live db.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both for your replies,
Vitor -
I will bear this in mind going forward. As it stands, it looks like I have made quite a task for myself.
xDJR1875 - I have not looked into SSIS or ETL before but the process makes sense. I had not come across the term "staging table" before however having looked into it they seem to be in line with my thought process.
Thanks again for all of your help.
I may have some additional questions relating to SSIS or ETL but I'll create new topics to cover these if needed.
Dan
Vitor -
The correct way is by script and not using SSMS GUI interface so you can run all scripts with all changes in the live db
I will bear this in mind going forward. As it stands, it looks like I have made quite a task for myself.
xDJR1875 - I have not looked into SSIS or ETL before but the process makes sense. I had not come across the term "staging table" before however having looked into it they seem to be in line with my thought process.
Thanks again for all of your help.
I may have some additional questions relating to SSIS or ETL but I'll create new topics to cover these if needed.
Dan