How do cscfg files and App.config files relate to one another for Azure Worker Roles?

Very Basic Explanation:

Conceptually they're the same. In a traditional application, you use app.config file to define various settings related to the application (in appSettings section). Similarly, you use cscfg file to define various settings related to your cloud application (in ConfigurationSettings section). Like app.config file, you get to define other things (e.g. number of instances of your cloud application) in the cscfg file.

If you want, you can still define some of the settings in app.config file but one thing to keep in mind is that app.config file gets "packaged" and deployed and in order to change the settings, you would have to repackage your application and deploy it. However you could change the settings in a cscfg file on the fly using either the portal or Service Management API without having to repackage and redeploy the application. For example consider the scenario where you're defining the database connection string in settings file. If you specify that in app.config, in order to change it you would need to make change in app.config file --> Build the application --> publish the application. Where as in case of a cscfg file, you would just change this value in the portal.

For Web/Worker Roles the traditional configuration files (app/web.config) will keep working like they do on an on-premises deployment. But it's important to know that this file is included in the Service Package, meaning it's part of the deployment.

This means you can't change the settings you have in your app/web.config without redeploying your application. The ServiceConfiguration.cscfg on the other hand is something which is defined at Cloud Service deployment slot level, next to the actual Service Package. This means you can change this configuration file without having to redeploy your application. These settings can also be accessed from your application by calling RoleEnvironment.GetConfigurationSettingValue (similar to ConfigurationManager.AppSettings).

If you consider building an application that works both on-premises and in Windows Azure, consider using the Microsoft.WindowsAzure.ConfigurationManager package. Which automatically chooses the cscfg or app/web.config based on where your application runs.

Tip: By subscribing to the RoleEnvironment.Changing/Changed event you can intercept changes to this configuration file. You can handle this to update the web.config in code for example (explained here).