I have found that my sync was working fine until I selected “Synchronize BCS Connections” in the configuration and it broke everything. I had to delete the service application and reinstall the user profile application. How I fixed the issue:
User Profile Sync doesn’t work.
Forefront Identity Manager Synchronization Service Hangs
User Profile Synchronization Service and User Profile Service hangs on “Central Admin > Services on Server”
Lots and lots of errors in event log.. amongst others
Event 22. The Forefront Identity Manager Service cannot connect to the SQL Database Server. Event 6398. The description for Event ID 6398 from source Microsoft-SharePoint Products-SharePoint Foundation cannot be found. Event 3. .Net SqlClient Data Provider: System.Data.SqlClient.SqlException: Could not find stored procedure 'RegisterService'. Event 2. The Forefront Identity Manager Service could not bind to its endpoints. This failure prevents clients from communicating with the Web services. Event 6324. The server encountered an unexpected error and stopped. ERR: MMS(1956): sql.cpp(5580): Query (select count(*) from [mms_management_agent]) performed with error ERR: MMS(1956): sql.cpp(5633): Invalid object name 'mms_management_agent'. ERR: MMS(1956): sql.cpp(5641): hrError