July 15, 2021 at 6:43 am
Hello Guys,
I'm having some issues using SSMA for Access and I can't figure out what is going on. The database in question is an Access 2019 database 64 bit. The database contains tables and queries of different sorts. I have successfully converted this database before without issue. Now when I try to perform the conversion the program seems to get hung up at the stage where it is attempting to read the database objects so they can be selected for conversion. I have no idea what the issue is and I am hoping that someone can help. Thanks in advance for any help you can provide.
July 15, 2021 at 2:53 pm
At first I was going to say "SQL Server Migration Assistant is deprecated and replaced with Data Migration Assistant", but that is only true for SQL to SQL migrations, not Access. So, my next thought - are you using the latest version of SSMA? Since you are using Access 2019, I am wondering if MAYBE you need to update SSMA to the latest version (8.21).
Now, looking at it a different way, are you sure that it is getting hung up and it isn't just doing some processing? Are you seeing the CPU usage by SSMA drop down to 0% and nothing happening OR is it using up nearly 100% of 1 core? It MAY just be that you need to be patient for it to complete. Depending on how large the database is, it may take a while to run. I know on one of my large systems when I ran Data Migration Assistant, it took a few hours to complete.
On top of that, if I remember right, Access doesn't like sharing. So if you or anyone has the Access database open when you run the tool, it may be getting some errors that it can't figure out.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 15, 2021 at 6:47 pm
Mr. Gale,
Thank you for the reply. Yes, I am using the most current version. I have used the tool with this database without any problems. I added some queries to the database that will be used in the upsized version as the basis for my DAL. I also made a copy of it and removed the queries. The result was the same. It is not being shared. As to the CPU usage issue I cannot say for sure as I haven't checked.
July 15, 2021 at 7:40 pm
If SSMA is a single threaded application (likely), then it may stop responding while it is gathering information and doing calculations. Also, all of the information is attempting to be stored in RAM. If the system you are running it on is low on resources, that RAM storage may get dumped to disk in the page file. If that happens, performance will TANK hard. I've seen 1 hour processes take over 24 hours to complete when you start paging to disk.
If you have a server with a lot of free memory, that is the best place to run it even though it is a a waste of resources. If you have a secondary workstation to run it on that can be dedicated to that tool short term, that would likely be better than your daily work machine.
On top of that, if you can copy the access database to the same physical machine that SSMA is running on, it will help with the performance. Otherwise it is pulling all of the data out of the database across the network and into memory (or page file). If the app isn't crashing (ie giving an error code and/or closing without you doing anything), I would think it is running but has a frozen GUI due to it being single threaded. If it says it is "Not Responding" in windows, there is a high chance it is processing stuff on the back end and not updating the user interface. I've coded some apps up like that before where the back-end processing explicitly requests to pause the draw operations while it is updating and calculating things, but I tend to put a "please wait..." type window in place so end users don't force-close it while waiting for it to finish working. A better practice is to have a separate thread for UI and processing, but that extra overhead on the coding side that some developers (myself included) don't like doing. Plus, multithreaded things can get messy if the thread crashes and the parent doesn't notice or the thread completes but the parent can't get a CPU to continue working. Plus it is extra overhead on the workstation (desktop or server) having something multithreaded. And debugging a multithreaded app can be tricky.
But, to make a long post longer, I would check system resources while it is running and appearing frozen. If SSMA is using CPU resources, it is likely still running and calculating things, just not updating the UI. Might not hurt to watch how much network I/O and disk I/O it is using as well as memory (which should be ever increasing as it grabs more and more data from access) BUT if CPU is near 0% AND memory is static (unchanging), and network and disk I/O are low, then it is likely frozen.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 15, 2021 at 7:57 pm
Mr. Gale,
Thank you for your reply. None of the issues that you bring up would likely be applicable here. Firstly, the program is not doing any real processing at the point that it gets hung up. It is step 2, the selection of database objects to be processed. The population of the treeview should happen very quickly. Instead it sits the like I just handed it a monster database to process. Secondly, my system is a Ryzen 9 16 Core processor (32 threads) with 64 MB of memory 8 GB video card 1 TB SSD primary and 8 GB HD secondary storage. Processing this little database should be child's play for my machine. I am happy to provide you with a copy of the database if you wish. It's a personal project. a media database. Nothing special. It's 156.748 MB currently.
July 15, 2021 at 9:26 pm
If it is a single threaded application, having a 16 core, 32 thread processor isn't going to help too much. If you have really good multi-core processing but poor single core processing, you may just need to be patient. If you think of the application like a garden hose, if the application is a single threaded application, the application has 1 garden hose. If you have 16 faucets (cores), each with 2 outputs on them (2 threads per core), you can hook up 32 garden hoses (32 threads). But your application only has 1 garden hose, so of those 32 outputs, you are using 1. Plus hyperthreading (2 threads on a single core which is how you get 32 threads on 16 cores) is not always the best way to go. If the core is rated at 2.2 GHz (for example), that is as fast as the core can process stuff. So 1 core that has 2 threads on it is now trying to process 2 things at once but still capped at a total speed of 2.2 GHz per core, not per thread. It is like putting a splitter on the end of your faucet. The water pressure didn't change, but you have 2 streams of water coming out now.
For some thing, single core processing power is what you want. SQL Server benefits a lot more (in most cases) from high single core performance than high multicore performance and as such you shouldn't buy a CPU that has more threads than cores IF you are planning on doing things that require high single core performance.
Now that being said, I wouldn't expect a Ryzen 9 to struggle with a 156 MB database UNLESS it has some external references that it is having troubles pulling data from. I have a feeling that the CPU isn't the problem, but I like to rule things out with proof rather than assumptions. I would still check task manager while the app is hung.
The 64 MB memory I am HOPING is a typo and you have 64 GB of memory, because if you REALLY have 64 MB of memory, that's highly likely to be where your problem is.
But even with a beefy system, if you have tons of stuff running in the background or tons of high-resource things running on it you could still be hitting that problem. For example, my work PC has 32 GB of RAM, if I don't reboot on an almost weekly basis, I find that my system struggles to find free memory by Friday. If I run the data migration assistant, I'm lucky if I can browse web pages at the same time without causing DMA to crash.
And just because the tool doesn't seem like it should be doing processing to you, it MAY be doing processing. It is a small database, but if you have a lot of relations in it or a lot of things for it to process while it is looking up information to present and order to you, the tool may take some time to figure those out. Or, as a worst case scenario, it could be due to database corruption. I can't even count the number of times I've corrupted an Access database.
Plus, I am hoping that the 8 GB HD you mentioned is actually an 8 TB HDD. If the access database, page file, or SSMA is being stored on the HDD and that is a 5400 RPM disk (and an eco friendly disk), it may be SLOW and that could be the problem. I would make sure that SSMA, page file, and access database are all on your SSD to ensure best performance.
At the moment, my work PC only has Access 2012 on it and I am not licensed for Access 2019, so sending me that database won't be much help.
Now as a completely different thought, do you have any log files you can review? Offhand, I don't remember at which point SSMA starts making logs. If there are no logs for SSMA, might not hurt to look at the windows event log.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 15, 2021 at 9:35 pm
I read through this - don't have any additional thoughts about the migration assistant issue. With that said - I am very curious why you are running through the assistant again.
If the system has already been upsized (migrated) to SQL Server - then what is the purpose of doing it again? Are you really maintaining a version in Access and a version in SQL Server? And doing development in Access to later migrate that to SQL Server?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2021 at 10:02 pm
Jeffery,
Thanks, for your reply. I am running the program again because I have made additions to the database. The first run was a test run done some time ago. Now the database is at a point where I want to do the process for real. Everybody seems to be missing the point though. The program is getting hung up BEFORE any real processing has been done. It gets hung up while trying to populate the treeview control BEFORE I've even selected anything. I have the program running right now on a vastly simplified version of the database that contains only the lookup tables, a dozen tables, and the program can't read the table names and display them. The program has been running for 10 minutes or more just trying to read the names of a dozen tables.
July 15, 2021 at 10:46 pm
Hi Guys,
I just tried to run the program against a completely different database and got the same result. The database was created from scratch. It has 3 tables with no data in them. The tables have an autonumber id field and a 255 character desc field. The result was the same the program gets hung up trying to retrieve the table names. There's something going on that is keeping the program from being able to read read the systems table, MSysObjects
July 16, 2021 at 1:55 am
Hi Guys,
I figured out the problem. It seems that there are settings linked to your Office Account that affect overall functionality. I had disabled all of mine and apparently it was blocking the program's ability to access the database objects. I am not a big fan of Windows 10's need to send this data and that data back to MS. In my opinion it is my data and none of MS's business. Windows 10 is MS's little data mining whore. So I tend to turn stuff off that looks like it serves that purpose. I was able to successfully convert my database to Server 2012 without any issues and did so in a matter of minutes not hours as some of you had suggested. I was rather disappointed that my data modification queries (inserts updates and deletes) did not convert. I know there are some differences in syntax but I don't think it would be that hard to translate an Access insert update or delete query into a compatible SQL Server query especially given the simplicity of my queries. Oh well, that was a bunch of wasted effort.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply