In ConfigMgr the SMS Provider is installed during installation. The SMS Provider is the interface between the ConfigMgr console and the Site database. In ConfigMgr 2007 there can be only one SMS Provider installed per ConfigMgr Site. In ConfigMgr 2012 however multiple installations of the SMS Provider are supported. Just remember: It cannot be installed on a clustered SQL server database server or on the same computer as the SMS Provider for another site.
For example: ConfigMgr 2012 is installed already and the SMS Provider is installed on your Primary Site server. Now you want to install another one on your SQL server, which isn't installed on a clustered SQL server database server. Here are the steps which must be followed:
1) Open the Start Menu on the ConfigMgr server and select Microsoft System Center 2012 > Configuration Manager > Configuration Manager Setup
2) Getting Started > Select "Perform site maintenance or reset this Site" and click Next
3) Site Maintenance > Select "Modify SMS Provider configuration" and click Next
4) Manage SMS Providers > Select "Add a new SMS provider" and "Enter the FQDN of the server where the SMS Provider is to be installed" and click Next
5) Configuration > After a few minutes the SMS Provider will be installed on another server
Good news that the SMS Provider can be installed multiple times now, and isn't a Single-point-of-failure anymore! With above steps multiple SMS Providers can be installed or uninstalled. Just choose to divide ConfigMgr roles also to have a High Available ConfigMgr environment.
Good luck with your installation!