October 28, 2009 at 10:37 am
I'm trying to compare two different ways of migrating data from one set of tables to another. By migrating, I mean if I set up a table structure, but then based on how the system is used, I realize the structure isn't as efficient as is should be, so I redesign it and want to get all the data moved over and all of the DB code switched to the new tables, meanwhile keeping the data synced between the two sets of tables so the change can be gradual.
In the past, what I've done is to create the new tables, migrate the existing data, and then set up triggers on the initial tables to update, insert, and delete the records in the new tables. Then, I go through every function and stored procedure that is selecting data, and update those to select from the new tables. Once I've tested (and had other people tested) that, then I go through and start updating any code that is updating, inserting, or deleting data. That way, there is no downtime and everything can be fully tested and not rushed.
The main problem with that approach is that there's always the chance that I missed something when creating the triggers, so the data gets out of sync.
My other thought is to create a view that will just pull the data into a structure that matches the new tables. This should keep everything synced between the original tables and the view at all times. Then, it also wouldn't matter if I switched all of the selecting code before the updating code, because the view could update the original tables as well and still be in sync.
I've heard people before saying that this is a good approach when migrating data, but I'm not clear how to go from the view to the actual new tables without essentially taking the system down to update the code to go from table_structure_view to table_structure and load all the data. In most cases, it shouldn't take more than 10 minutes or so I wouldn't think, but our database is accessed 24/7, so downtime is avoided at all cost.
I guess my question is there a good way to "swap" a table and a view in this type of situation?
October 28, 2009 at 12:03 pm
lucidspoon (10/28/2009)
I redesign it and want to get all the data moved over and all of the DB code switched to the new tables, meanwhile keeping the data synced between the two sets of tables so the change can be gradual.
:blink: kind of an on-the-go migration? really?
- How big is your database?
- How large is the migration window you can get?
Answering your question, neither triggers nor views... "insert into select" would do it nicely.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 28, 2009 at 12:46 pm
Yeah. For example, the developer before me had a table to store multiple ratings for each record in a long string like "|1234:5|1235:6|1236:3|" where the number before each ":" was an ID and number after was a rating. I made a table to store it the correct way so that each rating was its own record. There was so much code referencing the string format that I set up triggers so that if the string was updated, it would parse it and update the corresponding values in the new table.
As for the size of the database, there's about 150 tables. It's about 16 GB. The particular tables I am concerned about right now contain about 2000 records in one and about 10000 records used to link users to those 2000 records.
I'm not sure what you mean by "insert into select", unless you just mean to get the records from one table to another. That's what I have done initially, "INSERT INTO new_table (columns) SELECT columns FROM old_table", to get the current records. Then, in a trigger on insert of old_table, would be something like "INSERT INTO new_table (colums) SELECT columns FROM INSERTED". Same with update and delete.
Is there a better way to do this?
October 28, 2009 at 12:51 pm
16 Gig is a pretty manageable database.
Do it in one shot - why would you like to get into the myriad of troubles an on-the-go migration would cause?
Friday night users logout from old database.
Migrate.
Saturday morning users can login into the new one.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 28, 2009 at 1:07 pm
I'm not wanting to actually migrate from one database to another. Although this time, I kind of am...:-P
I didn't want to post exactly what I was trying to migrate at first, because everybody will probably laugh at me for the way I set things up at first... So, when I first started at this company (and first started really learning about databases), we were designing rights and roles for the site so that an outside organization could create custom roles and assign rights to those from a set list of rights. Then, they could assign their employees to the roles. At the time, I had just learned about ASP.Net profiles and roles, so I thought it would be a good idea to use that to manage the roles. Now that I see how it's being used and how it's rather hard for me to manage the roles like that, since I also had to use custom tables to manage the rights, I wanted to get all of that data out of those tables and into custom tables that I could more easily manage.
Maybe "migration" isn't really the correct word to use, since I'm not going from one database to another, but I didn't know what else to call it.
There really isn't a time where users log out at the end of the day, and log in the next morning. People use our site in different timezones and on 2nd and 3rd shifts. So taking things down isn't really an option, especially since there's only two of us here, so we can't really assign certain tasks for everyone to do and test at once. We also don't have enough people to do all the necessary testing in even a day. That's why I like the idea of basically having the 2 structures running in parallel essentially, so I can have scripts that I can run to make sure the data is getting updated correctly in the new one.
EDIT: I almost forgot the worst part of using the aspnet_Roles table the way I did was that I also tried to use it to allow an organization to store a workgroup tree structure so they could assign users to workgroups. So there's a tree structure being stored in a table that doesn't have a parent_id column. I had to get creative with that one, and not in a good way.
October 28, 2009 at 1:36 pm
I got is not a migration from database "A" to database "B" but a structural migration in the same database.
That's why I asked how large is the maintenance window you can get.
For a 16 Gig database a one to two hours window would sufice to move the 16 Gig around three or four times.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 28, 2009 at 1:51 pm
Oh, ok. Yeah, I'm not worried about actually moving the data so much as just having the data stored both ways in parallel for say a week or two while all the code is changed and tested. We have testing servers, but like I said, with only a few of us able to make the changes and do the testing, it's a process I'd rather draw out longer than just a couple of hours.
That might not be a regular way of doing things for a DBA, but I'm not claiming to be a good DBA. 😛 I've been doing this for 3 years, and I'm still learning the ropes.
October 29, 2009 at 6:49 am
With all due respect I strongly suggest not to proceed that way - the number of unknows will grew exponentially as time goes on.
Your job as a DBA is to protect the data - described process puts data in harms way.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 29, 2009 at 11:19 am
That makes sense. Right now for the thing that I need to do, I think I'll just use either a view or triggers into new tables to get the data in the right format for what I'm creating and testing now, but leave all the other code the way it is, so it won't really matter which I use. Then, when it comes time to try to actually implement the new thing, I'll copy stuff one time when we have a low volume of users.
There were 3 other developers and a DBA at my last job, so the DBA usually took care of all of the getting the data into new tables or into a new database. When I started this job, I was THE only designer / developer / DBA / EVERYTHING ELSE! 😛 So I know I don't use the best approaches! Hopefully things take off soon, so we can afford to get some more people on board...
Thanks for the advice!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply