.NET on Pivotal Cloud Foundry: Adding a MS SQL Service to an App
Deploying a service broker
In the previous post, we demonstrated how to deploy a .NET app to a Pivotal CF instance. However, the application is only partially operational—it still doesn’t have access to a database. In this tutorial, we will explain how to configure a MS SQL DB service and connect it to a .NET app running on PCF.
We will be using a private PaaS deployment, but the same commands will work for Pivotal Web Services (PWS). You can find the full versions of files used in the examples below in this GitHub repo.
MS SQL is an external service that can be added to the PaaS via a service broker, which is not available out-of-the-box. Fortunately, it is trivial to install one. The broker is a Java app, and we can simply push it to the Pivotal CF instance. There are other ways to add it, too. You can find a detailed guide in this GitHub repository.
Once the broker has been installed, we can go ahead and add the MS SQL service to the demo .NET app.
Creating a service
First, we need to check if MS SQL is available in the “marketplace” of the PCF instance and what plans it has. If you run the same commands on PWS, it will display publicly available services with corresponding pricing.
> cf marketplace
Getting services from marketplace in org Altoros / space dev as {UserName}...
OK
service plans description
mssql-dev default Microsoft SQL Server service for application development and testing
TIP: Use 'cf marketplace -s SERVICE' to view descriptions of individual plans of a given service.
> cf marketplace -s mssql-dev
Getting service plan information for service mssql-dev as {UserName}...
OK
service plan description free or paid
default Shared SQL Server free
>Judging by the output, mssql-dev is available with the default plan. There is no need to specify any additional parameters for this service at this moment. We only need the service name, the service plan, and a name for the instance (let’s use samplewebappSQL).
> cf create-service mssql-dev default samplewebappSQL
Creating service instance samplewebappSQL in org Altoros / space dev as {UserName}...
OK
>Now let’s check that the service has been enabled successfully.
> cf services
Getting services in org Altoros / space dev as {UserName}...
OK
name service plan bound apps last operation
samplewebappSQL mssql-dev default create succeeded
>Looks like it’s working.
Modifying the .NET app
After this step, we can bind the MS SQL service to the demo app, but we would like to do some modifications to the app first.
Parameters of bound services are available to apps via environment variables. Right now, we do not know which parameters will be provided, so we add an additional view and a related controller method (action) to the demo app. To do this, we need to modify the HomeController.cs and Environment.cshtml files. The examples below are the parts that need to be changed in:
HomeController.cs:
public ActionResult Environment()
{
ViewBag.Message = "Your environment page.";
return View();
}
- Environment.cshtml:
@using System.Collections
@{
ViewBag.Title = "title";
IDictionary environmentVariables = Environment.GetEnvironmentVariables();
}
<h2>Environment Variables</h2>
<div class="environment">
@foreach (DictionaryEntry entry in environmentVariables)
{
<div class="item">
<div class="key">@entry.Key</div>
<div class="value">@entry.Value</div>
</div>
}
</div>
Then, we add a new ActionLink to _Layout.cshtml:
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("Environment", "Environment", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
@Html.Partial("_LoginPartial")
</div>Now, we can publish the demo application to PCF, bind it to the MS SQL service, and view what environment variables are provided to us.
Publishing the .NET app
We push SampleWebApp to the Published folder (as described in the previous post), using the same set of commands:
> cf push SampleWebApp -p .\ -s windows2012R2 --no-start -b https://github.com/ryandotsmith/null-buildpack.git
Using stack windows2012R2...
OK
Creating app SampleWebApp in org Altoros / space dev as {UserName}...
OK
Using route samplewebapp.cf-dev.altoros.com
Binding samplewebapp.cf-dev.altoros.com to SampleWebApp...
OK
Uploading SampleWebApp...
Uploading app files from: .\
Uploading 1.1M, 76 files
Done uploading
OK
> cf enable-diego samplewebapp
Setting samplewebapp Deigo support to true
←[1;32mOk
←[0m
Verifying samplewebapp Deigo support is set to true
←[1;32mOk
←[0m
>
Binding the MS SQL service to the .NET app
Then, we bind the MS SQL service instance, samplewebappSQL, to the demo app:
> cf bind-service SampleWebApp samplewebappSQL
Binding service samplewebappSQL to app SampleWebApp in org Altoros / space dev as {UserName}...
OK
TIP: Use 'cf.exe restage SampleWebApp' to ensure your env variable changes take effect
> Please keep in mind that not all CF commands are case-insensitive. For example, application-related commands recognize samplewebapp and SampleWebApp as the same application, but the service-related set of commands is case-sensitive, so you cannot use SampleWebAppSQL instead of samplewebappSQL.
At this point, we can start our demo .NET app:
> cf start SampleWebApp
Starting app SampleWebApp in org Altoros / space dev as {UserName}...
Creating container
Successfully created container
Downloading app package...
Downloaded app package (7.8M)
Downloading buildpacks (https://github.com/ryandotsmith/null-buildpack.git)...
Downloaded buildpacks
Staging...
Exit status 0
Staging complete
Uploading droplet, build artifacts cache...
Uploading build artifacts cache...
Uploading droplet...
Uploaded droplet (7.8M)
Uploading complete
0 of 1 instances running, 1 starting
0 of 1 instances running, 1 starting
1 of 1 instances running
App started
OK
App SampleWebApp was started using this command `..\tmp\lifecycle\WebAppServer.exe`
Showing health and status for app SampleWebApp in org Altoros / space dev as {UserName}...
OK
requested state: started
instances: 1/1
usage: 1G x 1 instances
urls: samplewebapp.cf-dev.altoros.com
last uploaded: Fri Jan 15 06:23:23 UTC 2016
stack: windows2012R2
buildpack: https://github.com/ryandotsmith/null-buildpack.git
state since cpu memory disk details
#0 running 2016-01-15 09:33:04 AM 0.4% 296.4M of 1G 46.8M of 1G
>
Getting credentials
Open a new environment page and check the list of variables like it’s shown in the picture below.
We need the VCAP_SERVICES variable that contains all the information we need in the JSON format.
VCAP_SERVICES
{"mssql-dev":[{"name":"samplewebappSQL","label":"mssql-dev","tags":["mssql","relational"],"plan":"default","credentials":{"hostname":"10.92.0.109","host":"10.92.0.109","port":1433,"name":"cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8","username":"cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8-21e07dbd-a15a-4a81-a91b-eb756e3d46a7","password":"EOr2tI2bW4o39U8-T81dcvqk5JMJD_uFvjuP5uhsPxY=Aa_0","connectionString":"Address=10.92.0.109,1433;Database=cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8;UID=cf-0eb5ae1a-0e3c-467a-8ac3-4027ea96faf8-21e07dbd-a15a-4a81-a91b-eb756e3d46a7;PWD=EOr2tI2bW4o39U8-T81dcvqk5JMJD_uFvjuP5uhsPxY=Aa_0;"}}]}
The credentials in this quote were automatically generated during service binding.
Connecting the app to the MS SQL service
We could just replace the default connection string in the web.config file, but we prefer this connection string to change dynamically during application startup. The examples below are the parts that we have changed in:
Web.config:
<appSettings> … <add key="DBServiceName" value="mssql-dev"/> </appSettings>
- Startup.cs:
public void Configuration(IAppBuilder app)
{
ConfigureAuth(app);
ConfigureDB(app);
}
/// <summary>
/// Get connection string from environment variables and replace it in web.config
/// </summary>
/// <param name="app"></param>
private void ConfigureDB(IAppBuilder app)
{
string dbServiceName = ConfigurationManager.AppSettings["DBServiceName"];
string vcapServices = System.Environment.GetEnvironmentVariable("VCAP_SERVICES");
// if we are in the cloud and DB service was bound successfully...
if (vcapServices != null)
{
dynamic json = JsonConvert.DeserializeObject(vcapServices);
foreach (dynamic obj in json.Children())
{
if (((string)obj.Name).ToLowerInvariant().Contains(dbServiceName))
{
dynamic credentials = (((JProperty)obj).Value[0] as dynamic).credentials;
// replace connection string
var settings = ConfigurationManager.ConnectionStrings["DefaultConnection"];
var fi = typeof(ConfigurationElement).GetField(
"_bReadOnly",
BindingFlags.Instance | BindingFlags.NonPublic);
fi?.SetValue(settings, false);
settings.ConnectionString = credentials?.connectionString;
break;
}
}
}
}Repeat the publishing and deployment procedures, then start the application and test the connection to MS SQL by registering a new user. It works!
See this GitHub repo for the full versions of example files used in this tutorial.
We hope this post has helped you to get started with adding services to .NET apps on PCF. In the next tutorial, we will talk about scaling .NET applications to multiple instances.
Further reaading
- .NET on Pivotal CF: Connecting and Pushing an App
- How to Set Up a Cloud Foundry Infrastructure for .NET Apps in Minutes
- .NET on Pivotal Cloud Foundry: Scaling an App on Diego
