What are the Web.Debug.config and Web.Release.Config files for?
It's the new Web.config transformation feature of Visual Studio 2010. More information here.
Edit:
Are these files used to specify debug and release specific settings, so you don't clutter up the main web.config?
It isn't limited to three files, you could (in theory) have as many files as you have environments. The "top level" Web.config provides a template of your web config. The files under it provide replacement values specific to that environment (like if you have different connection strings for local/stage/test/whatever).
Does it even make sense to place a connection string in the root web.config file if I have have a local and remote one in the debug and release web.configs respectively.
It would only make sense if it wasn't going to change between environments. Sounds like in your case it does so, in your case no, it would not make sense to leave it in the Web.config.
https://forums.asp.net/t/1532038.aspx
The web.debug.config or web.release.config are only consumed by web deployment to transform web.config, say "Build deployment package" or "Publish". Regular build/debug doesn't invoke transform.
Web.Config Debug/Release
The web.config transforms that are part of Visual Studio 2010 use XSLT in order to "transform" the current web.config file into its .Debug or .Release version.
In your .Debug/.Release files, you need to add the following parameter in your connection string fields:
xdt:Transform="SetAttributes" xdt:Locator="Match(name)"
This will cause each connection string line to find the matching name and update the attributes accordingly.
Note: You won't have to worry about updating your providerName parameter in the transform files, since they don't change.
Here's an example from one of my apps. Here's the web.config file section:
<connectionStrings>
<add name="EAF" connectionString="[Test Connection String]" />
</connectionString>
And here's the web.config.release section doing the proper transform:
<connectionStrings>
<add name="EAF" connectionString="[Prod Connection String]"
xdt:Transform="SetAttributes"
xdt:Locator="Match(name)" />
</connectionStrings>
One added note: Transforms only occur when you publish the site, not when you simply run it with F5 or CTRL+F5. If you need to run an update against a given config locally, you will have to manually change your Web.config file for this.
For more details you can see the MSDN documentation
https://msdn.microsoft.com/en-us/library/dd465326(VS.100).aspx