Hi guys, first post here. I hope someone can help. A little background info first: I'm actually running Windows 11 for the past couple of months but so far haven't really noticed much difference from Windows 10, everything seems to work just fine. The computer is a Dell laptop with a fast I7 processor, 16GB RAM, 1TB SSD with 327GB free.
I'm 60 years old and was an IT guy for over 20 years, Network Administrator, CNE (anyone remember Novell Netware? LOL) and I'm A+ certified, etc. so I'm pretty computer savvy but don't know squat about using SQL Server. My Access database was created from the Assets Manager sample database and has been fine for my purposes. I had a little help making some minor tweaks to it. I also passed a course in my local community college on basic VBA programming but I hate programming. So, all that being said....
I've been using Access 365 for a couple of years now to manage a collection of mine. I just recently hit the 2GB database size limit and realize I need to move this over to SQL Server so I can keep growing my database.
So yesterday I downloaded and installed SQL Server 2019 Express. Here are the results of my installation.
I've also downloaded and installed the SQL Server Migration Assistant. I've spent hours trying to figure out where to go from here. Should I move my Access data to SQL server using the Upsizing Wizard?
Seeing as how I'm a novice at all this stuff I'm thinking I should probably keep using Access as my front end and use SQL Server as the back end to store the actual data. Is this my best bet? I've read many pages on Microsoft website trying to figure out how to use the SSMA but I'm not sure about how to make the connection to my SQL Server.
When I try to connect to my SQL Server using the SSMA I get this:
Here's a shot of my SQL Server configuration manager status:
I've looked into modifying my Windows Defender Firewall settings. If everything is running on the same machine do I need to open any TCP ports? If so how? I've tried turning off Windows Defender and pausing Avast but it doesn't seem to help.
I've started the SSMA and can select the source database file of my Access database but can't select the target SQL Server database because it doesn't exist and I'm not sure the SQL Server is even visible to the SSMA because of blocked ports and I'm unable to get the Server Agents running. (Maybe that's my biggest clue?)
So a step by step procedure on how to go about doing what my next steps are would be very helpful.
Thanks a million in advance for any assistance.