March 12, 2015 at 6:10 am
Good morning,
The e-commerce business I work for has been using Access until now to store all their data - and the main project I have been working on is migrating everything to use SQL Server instead. Right now I have set it up so that the data from new orders is being added to both the SQL Server and the Access database. So right now, the Access database consists of all of the actual orders, and the SQL Server database is a combination of test data and recent orders.
I plan on using SSMA to overwrite all of the data in the SQL Server to have a current copy of the Access database when we go live. The main issue is I wonder what will happen if somebody places an order in the 20 minute window that SSMA will be running - I imagine it will at least get added to the Access database - but will it also get added to the SQL Server? Will the request go into a sort of queue and get processed when the SSMA operation is complete?
I can manually input any orders that were placed during that short window into the SQL Server - but there are approximately 100 columns/fields in one of the tables so I'd really rather not have to manually type in a copy of the orders. Any insight into this issue would be deeply appreciated.
March 13, 2015 at 10:29 am
Just an opinion, but thats seems to be asking SSMA to be some sort of replication / transaction manager and I'd be surprised if this would go off without a hitch. As it is, the folks blogging about their work mention "multiple passes" are possible.
That 20 minutes of uptime that you don't want to let go of is probably going to be a project in itself, so given that, your best bet is to test it with some worst case web traffic and see what sort of mess you might end up with 🙂
March 13, 2015 at 4:41 pm
It's not so much that I expect ssma to be a transaction manager.
What I'm wondering is - if the orders table is currently being written to by the migration assistant, maybe SQL Server will hold off on adding an order placed from the website until it is done being written to. As in, hold that transaction into a queue. But I wouldn't expect the migration assistant to do that - it would be more likely that the SQL Server itself might do that. It makes sense that SQL Server might be able to handle it, since if two users were both doing insert statements involving several thousand rows at the same exact time it could cause problems if it doesn't have some sort of queue. But maybe that would cause a problem.
I'm going to do a test run next week and see what happens.
March 14, 2015 at 9:49 am
ccoscina (3/13/2015)
It's not so much that I expect ssma to be a transaction manager.What I'm wondering is - if the orders table is currently being written to by the migration assistant, maybe SQL Server will hold off on adding an order placed from the website until it is done being written to. As in, hold that transaction into a queue. But I wouldn't expect the migration assistant to do that - it would be more likely that the SQL Server itself might do that. It makes sense that SQL Server might be able to handle it, since if two users were both doing insert statements involving several thousand rows at the same exact time it could cause problems if it doesn't have some sort of queue. But maybe that would cause a problem.
I see. SQL server handles multiple users with transactions and such, to an extent where we expect multiple users to access and update tables in an orderly fashion. You can google "sql server isolation levels" to get the specifics and as a more general topic its sometimes referred to as concurrency control.
On the other hand, SSMA does (from my limited reading) offer some transaction options like how many rows to update per transaction, but that's probably per individual table, and application designs typically might update few rows over one ore more tables per individual web update (although obviously we do batch updates also right?)
I'm going to do a test run next week and see what happens.
I think that's some good thinking, look for what might break, do plenty of updates while migrating, because that's what trying to keep that 20 minutes uptime seems to look like. Good luck with it!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply