Microsoft Server 2012 R2, SQL Server 2016 and DreamFactory

Part 1: Running Microsoft Server 2012 and SQL Server on AWS, on my MacBook Pro

How do we get from here, hosting an AWS Microsoft SQL Server instance on my MacBook Pro?

AWS Microsoft Server 2012 R2 Desktop

To here using Microsoft Server, SQL Server, and DreamFactory, still on my MacBook Pro.

SQL Server Get Schema

Some Background:

Let's get to the nuts and bolts of this.  In the past, it was very difficult to create cross platform solutions.  With the advent of cloud computing, this has become increasingly easier to do.

When you have a robust piece of middleware software, such as DreamFactory which is for most intents and purposes language and platform agnostic, you really do have your choice of platforms to install it on.  Each has its advantages and disadvantages, which I am not going to go into detail in this article, but suffice it to say, there are a lot of enterprises that choose the Microsoft platform(s), and some of those advantages became apparent as I worked on this post.

First things first, make sure to grab all of the pre-requisites you need to make the install easy:

Required Software and Extensions

At a minimum, you will need the following software and extensions installed and enabled on your system in order to successfully clone and install DreamFactory 2.12.0+.

• PHP 7+ - check and install the requirements below for your particular environment.
  • PHP required extensions: Curl, MBString, MongoDB, SQLite, and Zip. You may need to install other extensions depending upon DreamFactory usage requirements. If you don't plan on using MongoDB, please remove the df-mongodb requirement from,composer.json or include the --ignore-platform-req option when running composer install.
• Git
•  Windows Git Client - Git Bash lets you run "Linux style" commands
• A web server such as NGINX, Apache, or IIS. You may use PHP's built-in server for development purposes.
• One of four databases for storing configuration data: MS SQL Server, MySQL (MariaDB or Percona are also supported), PostgreSQL, or SQLite.
• Composer - may require cURL to be installed from particular environment below.

Microsoft Server can be spun up almost anywhere now, as is evidenced by the photos above, and since DreamFactory is platform agnostic, we can install it on the Microsoft Server 2012 R2 instance with just a few bits of software installed to get up and running. There are multiple ways to grab and install PHP on a Microsoft platform, but an easy way is to utilize the Web Platform Installer (version 5.0 as of this post).

The Install:

You can download the Web Platform Installer for IIS here. Select a PHP version (7.0.x is required to run the current 2.13.0 version of DreamFactory), and different pieces of IIS, should you decide to utilize that as your production web server.  This post will not dive into the nitty-gritty of IIS, but you can see our documentation here.  We will be using PHP's built-in development web server to just illustrate the connections.

Web Platform Installer 5.0 Showing PHP installed

Once you have installed PHP and double checked your pre-requisites are installed, you can begin the install:

• Perform a Git clone into this directory for Dreamfactory:

<span class="kw2">git clone</span> https:<span class="sy0">//</span>github.com<span class="sy0">/</span>dreamfactorysoftware<span class="sy0">/</span>dreamfactory

Clone down the latest version

This will pull down the master branch of DreamFactory into a directory called ./dreamfactory.

• Navigate to the dreamfactory directory and install dependencies using composer. For production environment, use --no-dev, otherwise discard that option for a development environment. If you are not running or plan to run MongoDB, add —ignore-platform-reqs:

composer update <span class="re5">--ignore-platform-reqs</span> <span class="re5">--no-dev</span>

composer update --ignore-platform-reqs --no-dev

Otherwise, run the following command to install the dependencies:

composer <span class="kw2">install</span> <span class="re5">--no-dev</span>

• Run DreamFactory setup command-line wizard. This will set up your configuration and prompt you for things like database settings, first admin user account, etc. It will also allow you to change environment settings midway and then run it again to complete the setup.

php artisan df:setup

php artisan df:setup

Follow the on-screen prompts to complete the setup.

Follow the prompts

You can then run php artisan serve and migrate over to the address and port you have set up. In this example, we are running off of https://127.0.0.1:8000

php artisan serve

Part 2:  The SQL Server Reckoning

With our instance running now, we can finally delve into the "fun" part of this install.  The ease with which you can add a SQL Server instance is awesome.  It is the fastest install I have ever done from the driver install to DreamFactory connection, it was less than 5 minutes¹.

Using the Web Platform Installer, you can download a SQL Server driver package that is compatible with your PHP version and your O/S version.

SQL Server Driver Package, version 5.2

Now you can head back over to your instance and create a SQL Server service.  Just select the service type, add in your credentials and then test it.  That's it.  No muss, no fuss.  Take a look at the screenshots below to see the results.

Create your service

Add your credentials

SQL Server Get Schema

We have now connected our SQL Server instance to our Microsoft Server 2012 R2 (both hosted on AWS) on my MacBook Pro.  Sometimes, it all falls into place.  Don't forget to check out our wiki and community forums for more topics, information, and examples.

¹ I had my credentials on hand in a notepad text file for copy/paste quickness, but still, very fast :)

Related reading: MySQL vs MS SQL Server – Which Reigns Supreme?