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
Enter_KratosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I have made many changes which are documented yet still a very time consuming process to manually change.
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.
1
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
From what you are saying, it appears that you are trying to update the database in-place rather than migrate to a new version.
If that is the case, I suggest to migrate to a totally new database.
Create an SSIS package or ETL script or SQL script that will pull out data from the existing database and populate the new database.

This can be achieved by pulling data into a staging table that is semi-finished and then using scripts to populate the finished db that you either create in the scripts or is already created (and documented) by you.

old db --> staging db --> finished db
shut down old db
point all applications to the new db

Hope that gives you some ideas
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Enter_KratosAuthor Commented:
Thank you both for your replies,

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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.