>SQL server is obviously the better option with very large tables. Is there a general rule of thumb that says that the time to switch is when the largest table in the database exceeds X records? What is X?

SQL Server is better option not only for large, but for middle size databases too. It is more secure than VFP native DBF's. I think that everything depend on user and application.
If you have company with turnover more than $10mln, it could afford to buy SQLServer.
If you have more than 10 000 trnsaction per month you obviously will need MSDE(cutted SQLServer) or SQLServer.
X is different for different user and application of course.