In case you do not want to use dlt (and there are reasons not to), you can also check the docs for autoloader and merge notebooks

These 2 do basically the same as dlt but without the extra cost and more control.  You have to write more code though.
For ingesting the SQL server data I would use Data Factory, which lands the data onto your bronze layer (adls gen2).
Or use the Azure SQL connector of Databricks, but that will use DLT and is more expensive than ADF but has the ease of use (but less control/visibility).
So you see, many choices.