Does SQL server provide comprehensive logging out of the box, or will I still need to create application logs to capture inserts, updates and deletes?
Nothing that you can just turn on, but here are the options for SQL server:
1. SQL server audit
2. SQL Server change data capture
3. SQL Server extended events
6. Custom auditing via stored procedure / application code
(at least these are the ones I can think of right now).
When deciding the correct approach it all depends on how complicated your application is, how much data changes and how many users there are.
It is probably worth considering whether you are using windows authentication or whether all users are logging on as 'admin', it will be impossible to audit data changes if everyone is using the same account.
Also be aware that moving to sql server linked tables will inevitably lead to some code changes and different ways of dealing with record sources for forms and reports.