FIM User Profile Synchronization Service Issue Resolved

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: Symptoms: 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: 0x80040e37, dwMinor: 208
ERR: MMS(1956): sql.cpp(5796): SQL error: 42S02, native: 208
BAIL: MMS(1956): sql.cpp(2897): 0x80040e37 
BAIL: MMS(1956): sql.cpp(4114): 0x80040e37 
ERR: MMS(1956): sql.cpp(5580): Query (select fixed_schema_version_number from [mms_server_configuration] ) performed with error
ERR: MMS(1956): sql.cpp(5633): Invalid object name 'mms_server_configuration'.
ERR: MMS(1956): sql.cpp(5641): hrError: 0x80040e37, dwMinor: 208
ERR: MMS(1956): sql.cpp(5796): SQL error: 42S02, native: 208
BAIL: MMS(1956): sql.cpp(2897): 0x80040e37 
BAIL: MMS(1956): sql.cpp(2738): 0x80040e37 
BAIL: MMS(1956): storeimp.cpp(2485): 0x80040e37 
BAIL: MMS(1956): storeimp.cpp(293): 0x80040e37 
ERR: MMS(1956): server.cpp(294): Failed to connect to the database Sync DB on ****\SHAREPOINT
BAIL: MMS(1956): server.cpp(295): 0x80040e37 
BAIL: MMS(1956): server.cpp(3518): 0x80040e37 
BAIL: MMS(1956): service.cpp(1528): 0x80040e37 
ERR: MMS(1956): service.cpp(977): Error creating com objects. Error code: -2147217865. This is retry number 3.
BAIL: MMS(1956): service.cpp(991): 0x80040e37 
Forefront Identity Manager 4.0.2450.5"

Looks familiar?
Diagnosis:
Um.. something is wrong with FIM?
Solution:
I have tried to compile the list of things I did to sort this out.
Rule of thumb, shut down the offending services and then make the changes. I assume you have the correct SPadmin accounts and all the services have the right accounts assigned.
Edit: 2010-10-28. If you do this and it still doesn't work, try deleting the User Profile Service Application and recreating it, as described in the following post http://www.harbar.net/articles/sp2010ups.aspx Look under the "Create the UPS service application" section.
I also had to stop most of the user profile services and do a restart after deleting and reinstalling the UPS service, also the new application pool that was created was set to 32 bit, which I had to change. Fair enough, took me 3 hours, but I did not have to reinstall SP.. again..


0. Reset IIS.. (just in case something is running around in memory)

PS D:\> iisreset

1. A wild FIM Service Account appears.. I choose you SPadmin account.
2. Now I assign “Full control” for my SPadmin account to the following folder “C:\Program Files\Microsoft Office Servers\14.0”
3. Open IIS manager and make sure that for all the application pools the “Advanced Settings>General>Enable 32-Bit Applications” = False.
4. Open SP CA > “Services on Server” and make sure that “User Profile Synchronization Service” and “User Profile Service” is stopped. If they are just hanging, do this:
Open PowerShell on SP server.
a. Type:

get-spserviceinstance
Name                             Status   Id
--------                         ------   --
Managed Metadata Web Service     Online   82880b37-2cff-4a3c-94ca-922bb739ff27
User Profile Synchronization ... Provi... 03f49dd6-658e-44cd-a6f0-21c7560242ab
Business Data Connectivity Se... Online   901d17ff-471a-4557-80eb-befac3ffb396

b. Next, locate the GUID for the user profile synchronization service (it'll probably show a status of 'provisioning' in PowerShell) and use the following command:
c.

stop-spserviceinstance [userprofilesynchronizationservice GUID]

This will eventually stop the service and the status of the service on the SharePoint Manage services on server page will show a status of 'stopped'.
Also, the two Forefront services in the Windows Services console should be set back to a status of disabled.
5. Login as farm account
6. Backup the User Profile DB and the User Profile Sync DB
7. Stop the SharePoint 2010 Timer service:

PS D:\> net stop sptimerv4

8. Delete the data in the Sync DB using the following PowerShell script:

PS D:\> Get-SPDatabase

9. Copy the GUID associated with the User Profile Sync DB in the command line below

PS D:\> $syncdb=Get-SPDatabase -Id <GUID of User Profile Sync DB>

10. Execute these commands, in exactly the following order. This is not a script. So please cut and paste each of these commands one by one.

PS D:\> $syncdb.Unprovision()
PS D:\> $syncdb.Status='Offline'
PS D:\> Get-SPServiceApplication

#Copy the GUID associated with the User Profile Service and paste it after "Id" in the next command:

PS D:\> $upa=Get-SPServiceApplication -Id <GUID of User Profile Service
PS D:\> $upa.ResetSynchronizationMachine()
PS D:\> $upa.ResetSynchronizationDatabase()

11. Provision the Sync DB:

PS D:\> $syncdb.Provision()

12. Add the User Profile Synchronization service account (farm account) as the dbowner on the Sync DB (using SQL Server Management Studio).
13. Start the SharePoint 2010 Timer service

PS D:\> net start sptimerv4

14. Go to SP CA > Application Management > Manage Service Applications > Click on the right next to “User Profile Service” (Do not open it, just select it) > Administrators (on the ribbon) > Make sure your chosen account has full control
15. Start the User Profile Synchronization Service in the Central Administration UI.
16. After the User Profile Synchronization Service is started, reset IIS.

PS D:\> iisreset

17. Create connections to data sources in the Central Administration UI.
18. Run full user profile synchronization.
19. Open C:\Program Files\Microsoft Office Servers\14.0\Synchronization Service\UIShell\ miisclient.exe
I wish I could tell you that “Do this and it will work again” but I cannot guarantee this will solve your problem, it solved mine however.(this solution is pasted together from about 5 forums)
*Edit: see what happens if you talk to the right people*
Thank you Donald Farmer and Peter Willmot for pointing me in the right direction.. thank you
http://www.harbar.net/articles/sp2010ups.aspx
And of course, thank you Spencer Harbar for all the awesomeness