2/16/17

Responsive SharePoint Online Hosted Add-In using Bootstrap, Bootstrap Dialog, JQuery Datatables and Toastr


In this blog post I'll demonstrate how to create a SharePoint Online Hosted Add-In that uses the SharePoint REST API to interact with a custom SharePoint list to Create, Read, Update and Delete records from this list in a responsive fashion. 

The project contains a custom SharePoint List called Files that will be populated using sample data, this list will provide the data to the JQuery datatables grid.


Prerequisites

  1. Office 365 Tenant
  2. Visual Studio 2015 or Visual Studio Code
  3. SPFastDeploy (optional) - if you haven't used this tool before please take a look at my blog post on this Here

Javascript Libraries

Install the following Javascript libraries via NuGet
  1. Bootstrap v3.3.7 (Don't install v4+ for this demo)
  2. Bootstrap.dialog
  3. JQuery (installed by default)
  4. Jquery Datatables
  5. Jquery Datatables.select (Installed with JQuery Datatables NuGet package)
  6. Toastr
Download Visual Studio Solution - Here

Terminology

The words Apps and Add-Ins can be a little confusing, Microsoft has officially renamed SharePoint Apps to SharePoint Add-Ins. In Visual Studio and SharePoint Online the dialogs still use the old term Apps.

Original name
New name
apps for SharePoint
SharePoint Add-ins
app part
add-in part
app web
add-in web

Instructions 

This demonstration will be done using Visual Studio 2015 Update 3.

Open Visual Studio 2015 and Select File | New Project

Under the New Project | Installed | Templates |Office/SharePoint | Select App for SharePoint



Enter a SharePoint site where this application will be published

Select SharePoint Hosted and Select Next



Select SharePoint Online for the target version and Select Finish

Installing NuGet packages

Right click on the project name and Select Manage NuGet Packages. Select the Browse link and search for Bootstrap. Select Bootstrap from the list and choose version 3.7 in the dropdown. Select the Install Button.
Repeat this process for the remaining Javascript libraries listed above.


Create the SharePoint Files List to host the data for the datatables grid

Right Click on the project name and select Add | New Item



Under the Office/SharePoint Items | Select List and type 'Files' as the name




Enter 'Files' for the name to display and Select Create a customizable list template and a list instance of it Radio Button. Select Finish




The Files | Schema.xml file should automatically open (if not simply double click on the Files List from the solution explorer. On the Columns tab, create columns for FileName, FileType and Team, each column will have a Type of Single Line of Text. Save and Close the Schema.xml File



Note: At any point you may be prompted to sign in to Office 365, enter your credentials and select Sign In



Populate Sample Data into Files List

Open the Elements.xml file beneath Files | FilesInstance. Delete everything in this file, copy and paste the following code into this file:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <ListInstance Title="Files" OnQuickLaunch="TRUE" TemplateType="100" Url="Lists/Files" Description="Files List Instance">
    <Data>
      <Rows>
        <Row>
          <Field Name="Title">Autoexec</Field>
          <Field Name="FileName">Autoexec</Field>
          <Field Name="FileType">bat</Field>
          <Field Name="Team">Dell</Field>
        </Row>
        <Row>
          <Field Name="Title">Config</Field>
          <Field Name="FileName">Config</Field>
          <Field Name="FileType">sys</Field>
          <Field Name="Team">EMC</Field>
        </Row>
        <Row>
          <Field Name="Title">Hosts</Field>
          <Field Name="FileName">Hosts</Field>
          <Field Name="FileType">exe</Field>
          <Field Name="Team">Dell</Field>
        </Row>
        <Row>
          <Field Name="Title">Bootstrap</Field>
          <Field Name="FileName">Bootstrap</Field>
          <Field Name="FileType">js</Field>
          <Field Name="Team">EMC</Field>
        </Row>
        <Row>
          <Field Name="Title">Startup</Field>
          <Field Name="FileName">Startup</Field>
          <Field Name="FileType">css</Field>
          <Field Name="Team">Dell</Field>
        </Row>
      </Rows>
    </Data>
  </ListInstance>
</Elements>

Note: After saving the list and deploying to SharePoint I noticed that the filetype
column was displaying 'undefined'. I looked at the schema.xml file for the Files list 
and noticed the filetype had been changed to filetype1 in the <fields><field></field></fields>
node. A simple search in this file for filetype helped identify 2 places where I needed to 
change the name back to filetype. Not sure why this occurred.

Add Javascript to App.js file

The following javascript contains functions to populate the datatables grid, provide
All of the Create, Read, Update and Delete (CRUD) methods, creating the Bootstrap
model dialog for creating and editing files and delete file confirmation dialog.


'use strict';
var RestUrl = "../_api/lists/getbytitle('Files')/items";
$(document).ready(function () {
    PopulateGrid();
    $('#fileFormSubmit').click(function (e) {
        //Check for edit or new and call update or add function
        if ($('#myModalLabel').html() == 'Add New File') {
            addFile($('#fileTitle').val(), $('#fileName').val(), $('#fileType').val(), $('#team').val());
        } else {
            UpdateFiles($('#fileId').val());
        }
    });
});
function PopulateGrid() {
    //Clear datatables
    $('#FilesGrid').empty();
    //Get File list items
    $.ajax({
        url: RestUrl,
        method: "GET",
        headers: {
            "accept": "application/json;odata=verbose"
        },
        success: function (data) {
            if (data.d.results.length > 0) {
                //construct HTML Table from the JSON Data
                $('#FilesGrid').append(GenerateTableFromJson(data.d.results));
                //Bind the HTML data with Jquery DataTable
                var oTable = $('#FilesTable').dataTable({
                    //control which datatable options available
                    dom: 'Bfrltip',
                    //add select functionality to datatable
                    select: true,
                    //adjust column widths
                    "columns": [
                    null,
                    null,
                    null,
                    null,
                    null,
                    { "width": "8%" }
                    ],
                    //remove sort icon from actions column
                    "aoColumnDefs": [
                    { "bSortable": false, "aTargets": [5] }
                    ]
                });
            } else {
                $('#FilesGrid').append("<span>No Files Found.</span>");
            }
        },
        error: function (data) {
            $('#FilesGrid').append("<span>Error Retreiving Files. Error : " + JSON.stringify(data) + "</span>");
        }
    });
};
//Generate html table values
function GenerateTableFromJson(objArray) {
    var tableContent =
        '<table id="FilesTable" class="table table-striped table-bordered" cellspacing="0" width="100%">' +
            '<thead><tr>' + '<th>ID</th>' + '<th>Title</th>' + '<th>FileName</th>' + '<th>FileType</th>' +
            '<th>Team</th>' + '<th>Actions</th>' + '</tr></thead>';
    for (var i = 0; i < objArray.length; i++) {
        tableContent += '<tr>';
        tableContent += '<td>' + objArray[i].Id + '</td>';
        tableContent += '<td>' + objArray[i].Title + '</td>';
        tableContent += '<td>' + objArray[i].FileName + '</td>';
        tableContent += '<td>' + objArray[i].FileType + '</td>';
        tableContent += '<td>' + objArray[i].Team + '</td>';
        tableContent += "<td><a id='" + objArray[i].Id + "' href='#' style='color: orange' class='confirmEditFileLink'>" +
            "<i class='glyphicon glyphicon-pencil' title='Edit File'></i></a>&nbsp&nbsp";
        tableContent += "<a id='" + objArray[i].Id + "' href='#' style='color: red' class='confirmDeleteFileLink'>" +
            "<i class='glyphicon glyphicon-remove' title='Delete File'></i></a>&nbsp&nbsp";
        tableContent += "<a id='" + objArray[i].Id + "' href='#' class='confirmListItemDetailsLink'>" +
            "<i class='glyphicon glyphicon-cog' title='Link to List Item'></i></a></td>";
        tableContent += '</tr>';
    }
    return tableContent;
};
// Edit button click event
$(document).on('click', '.confirmEditFileLink', function (e) {
    e.preventDefault();
    var id = this.id;
    var requestUri = "../_api/web/lists/getByTitle('Files')/items(" + id + ")";
    $.ajax({
        url: requestUri,
        method: "GET",
        contentType: "application/json;odata=verbose",
        headers: { "accept": "application/json;odata=verbose" },
        success: function (data) {
            $('#fileTitle').val(data.d.Title);
            $('#fileName').val(data.d.FileName);
            $('#fileType').val(data.d.FileType);
            $('#team').val(data.d.Team);
            $('#fileId').val(data.d.Id);
            $('#myModalLabel').html('Edit File');
            $('#myModalNorm').modal('show');
            $("#etag").val(data.d.__metadata.etag);
        }
    });
});
//Link to files list item
$(document).on('click', '.confirmListItemDetailsLink', function (e) {
    e.preventDefault();
    var id = this.id;
    var requestUri = "../Lists/Files/DispForm.aspx?ID=" + id;
    window.location.href = requestUri;
});
// Delete button click event
$(document).on('click', '.confirmDeleteFileLink', function (e) {
    e.preventDefault();
    var id = this.id;
    BootstrapDialog.show({
        size: BootstrapDialog.SIZE_SMALL,
        type: BootstrapDialog.TYPE_DANGER,
        title: "Delete Files Confirmation",
        message: "Are you sure you wish to Delete this File?",
        buttons: [
            {
                label: "Confirm",
                cssClass: 'btn-primary',
                action: function (dialog) {
                    dialog.close();
                    var restUrl = "../_api/web/lists/GetByTitle('Files')/items(" + id + ")";
                    jQuery.ajax({
                        url: restUrl,
                        type: "DELETE",
                        headers: {
                            Accept: "application/json;odata=verbose",
                            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
                            "IF-MATCH": "*"
                        }
                    });
                    toastr.success("File Successfully Deleted.", "Success");
                    PopulateGrid();
                }
            },
            {
                label: "Cancel",
                action: function (dialog) {
                    dialog.close();
                }
            }
        ]
    });
});
//Update Model Label
function updateFormLabel() {
    $('#myModalLabel').html('Add New File');
};
//Populate then display model dialog for add file button clicked
function addNewFile() {
    $('#myModalLabel').html('Add New File');
    $('#fileTitle').val('');
    $('#fileName').val('');
    $('#fileType').val('');
    $('#team').val('');
    $('#fileId').val('');
    $('#myModalNorm').modal('show');
};
//Edit file function
function UpdateFiles(id) {
    var fileTitle = $("#fileTitle").val();
    var fileName = $("#fileName").val();
    var fileType = $("#fileType").val();
    var team = $("#team").val();
    var eTag = $("#etag").val();
    var requestUri = "../_api/web/lists/getByTitle('Files')/items(" + id + ")";
    var requestHeaders = {
        "accept": "application/json;odata=verbose",
        "X-HTTP-Method": "MERGE",
        "X-RequestDigest": $('#__REQUESTDIGEST').val(),
        "If-Match": eTag
    }
    var fileData = {
        __metadata: { "type": "SP.Data.FilesListItem" },
        Title: fileTitle,
        FileName: fileName,
        FileType: fileType,
        Team: team
    };
    var requestBody = JSON.stringify(fileData);

    return $.ajax({
        url: requestUri,
        type: "POST",
        contentType: "application/json;odata=verbose",
        headers: requestHeaders,
        data: requestBody
    });
}
//Add File function
var addFile = function (fileTitle, fileName, fileType, team) {
    var requestUri = "../_api/web/lists/getByTitle('Files')/items";
    var requestHeaders = {
        "accept": "application/json;odata=verbose",
        "content-type": "application/json;odata=verbose",
        "X-RequestDigest": $('#__REQUESTDIGEST').val()
    }
    var fileData = {
        __metadata: { "type": "SP.Data.FilesListItem" },
        Title: fileTitle,
        FileName: fileName,
        FileType: fileType,
        Team: team
    };
    var requestBody = JSON.stringify(fileData);
    return $.ajax({
        url: requestUri,
        type: "POST",
        headers: requestHeaders,
        data: requestBody
    });

};

Modify the Default.aspx page with the following code

In the <asp:content> section all of the css and script references are added, ensure these match the
file locations from the downloaded NuGet packages, if they don't they will be underlined indicating
there is an issue.

<%-- The following 4 lines are ASP.NET directives needed when using SharePoint components --%>

<%@ Page Inherits="Microsoft.SharePoint.WebPartPages.WebPartPage, Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" MasterPageFile="~masterurl/default.master" Language="C#" %>

<%@ Register TagPrefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%-- The markup and script in the following Content element will be placed in the <head> of the page --%>
<asp:Content ContentPlaceHolderID="PlaceHolderAdditionalPageHead" runat="server">
        <!-- CSS styles added to the following file -->
    <link type="text/css" href="../Content/App.css" rel="Stylesheet"/>
    <link type="text/css" href="../Content/toastr.css" rel="stylesheet" />
    <link type="text/css" href="../Content/bootstrap.css" rel="stylesheet" />
    <link type="text/css" href="../Content/bootstrap-dialog.css" rel="stylesheet" />
    <link type="text/css" href="../Content/DataTables/css/select.bootstrap.min.css" rel="stylesheet" />
    <link type="text/css" rel="stylesheet" href="https://cdn.datatables.net/1.10.8/css/jquery.dataTables.css">
     <!-- javascript references added to the following file -->
    <script type="text/javascript" src="../Scripts/jquery-1.9.1.min.js"></script>
    <script type="text/javascript" src="../Scripts/bootstrap.js"></script>
    <script type="text/javascript" src="../Scripts/bootstrap-dialog.js"></script>
    <script type="text/javascript" src="../Scripts/toastr.min.js"></script>
    <script type="text/javascript" src="../Scripts/App.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.8/js/jquery.dataTables.min.js"></script>
    <script type="text/javascript" src="../Scripts/DataTables/dataTables.select.min.js"></script>
</asp:Content>

<%-- The markup and script in the following Content element will be placed in the <body> of the page --%>
<asp:Content ContentPlaceHolderID="PlaceHolderMain" runat="server">
    <div class="container">
        <h1><span class="label label-primary">Grid Data Consumed via  SharePoint Rest API</span></h1>
        <div id="toolbar">
            <button type="button" value="Files" class="btn btn-info" onclick="Javascript: location.href = '../Lists/Files'"><span class='glyphicon glyphicon-upload'></span> File List</button>
            <button type="button" class="btn btn-success" onclick='addNewFile();'><span class='glyphicon glyphicon-plus'></span> Add New File</button>
        </div>
   <p></p>
 <div id="FilesPanel">
 <table style="width: 100%;">
 <tr>
     <td>
        <div id="FilesGrid" style="width: 100%"></div>
     </td>
 </tr>
 </table>
 </div>
 <!-- Bootstrap Modal Dialog-->
<div class="modal fade" id="myModalNorm" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <!-- Modal Header -->
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal">
                       <span aria-hidden="true">&times;</span>
                       <span class="sr-only">Close</span>
                </button>
                <h4 class="modal-title" id="myModalLabel">
                    Add New File
                </h4>
            </div>
            <!-- Modal Body -->
            <div class="modal-body" id="modalBody">
                <form role="form" id="fileForm">
                  <div class="form-group">
                    <label>File Title</label>
                      <input class="form-control" id="fileTitle"/>
                  </div>
                  <div class="form-group">
                    <label>File Name</label>
                      <input class="form-control" id="fileName"/>
                  </div>
                  <div class="form-group">
                    <label>File Type</label>
                      <input class="form-control" id="fileType"/>
                  </div>
                     <div class="form-group">
                    <label>Team</label>
                      <input class="form-control" id="team"/>
                  </div>
                      <!-- hidden controls -->
                  <div style="display: none">
                      <input id="etag" />
                      <input id="fileId" />
                  </div>
                  </form>
                  <div class="modal-footer">
                      <button type="button" class="btn btn-danger" data-dismiss="modal" onclick='updateFormLabel();'>
                        Cancel
                      </button>
                      <button type="submit" class="btn btn-primary" id="fileFormSubmit">
                        Submit
                      </button>
                  </div>
            </div>
        </div>
    </div>
</div>
</div>
</asp:Content>
Verify Solution - a list of items in the Visual Studio Solution

Deploy SharePoint Add-In to SharePoint Online

Right click on the project name and Select Deploy

In the Output Tab ensure that the SharePoint Add-in deploys successfully. During deployment there will be a message: Installation is in progress (00:00:00) with a timer to show the amount of time it takes for deployment.


Note: If you have installed and configured SPFastDeploy you will no longer have to wait for this lengthy deployment process after the initial deployment. If you make a simple change to a single file you can just right click on the file and select SPFastDeploy.

View the SharePoint Add-In in SharePoint Online

Navigate to the SharePoint site configured in your SharePoint Add-In properties

Select the ResponsiveSharePointHostedAddin link from the left navigation


Note: In the action column if you hover over the icons you will see a description for each icon, from left to right there is edit, delete and link to list item icons. Bootstrap is used here to render these icons using a regular <a href> tag with a class='glyphicon glyphicon-pencil' for the edit icon for example.


Testing SharePoint Add-In Functionality

Select the Add New File Button, this will present the Bootstrap Model Dialog prompting the user to create a new file. Select Cancel.



Select the Pencil icon from one of the items in the grid. Notice the row selected changes colors (JQuery.Datatables.Select library) indicating this row is selected and then Bootstrap dialog opens allowing the user to edit the selected file. Select Cancel.



Select the Delete icon from one of the items in the grid. A Bootstap dialog will be displayed asking for confirmation of the file deletion. Select Cancel.


Select the order by column headers for some of the columns in the grid, notice how there are no post-backs for any of this functionality and its very fast. Type something in the search box that appears in the grid, notice how it quickly filters items to the text you type again with no post-backs.

Select Delete on an item in the grid, confirm the deletion. A Toast will fire indicating the file was successfully deleted.



This completes this demonstration.

Download Visual Studio Solution - Here











2/15/17

Microsoft SQL Server 2016 Installation

This post will provide the necessary prequisites, steps and configuration settings for a Stand-Alone SQL Server 2016 Environment.  The Stand-Alone tier of service consists of a single SQL Server with a baseline configuration.


   Prerequisites

  • Windows Server installation 
  •  Storage provisioning allocated with disk partitions outlined
  •  Instance and Shared Features required for this implementation of SQL Server, i.e. Reporting, Analysis, Integration
  •  Domain Service accounts (recommended)
  • Instance Type identified, Default.
  • SA account password
  • Specific SQL Server administrator accounts.
  • Default Collation identified for instance type and purpose.
  • .Net 3.5 framework feature enabled
  • Setup file for SQL Server 2016 
  •  Licensing keys for SQL Server 2016
  • Adequate user account permissions to complete the installation

Note: The SQL installation is based on this drive configuration, please configure these drives on the Windows Server before continuing

SQL Server Hard Drive Configuration

Drive Letter
Functions
Size (GB)
Notes
C
System
80GB
80
D
Binaries
200GB
G
Data
2TB
L
Logs
400GB
(data * 20%)
T
TempDB Data & Logs
100GB
R
Backups
2.5TB

SQL Server Account Configuration

Component
SQL Server 2014/2014
Notes
Database Engine
sqlsvc01
SQL Server Agent
sqlsvc02
SSAS
Local Service
SSIS
Local Service
SSRS
Local Service
Distributed Replay Controller
Local Service
FD Launcher (Full-text Search)
Local Service
SQL Server Browser
Local Service
SQL Server VSS Writer
Local Service













Installation

Logon to server, locate the installation media and ensure the media is presented to the operating system. 

 Double-Click the setup.exe file to launch the SQL Server 2016 Installation Center


Select Installation from the left menu items and the click on the New Server stand-alone installation or add features to an existing installation link.




Enter the product key for the SQL edition that is being installed.



Accept the product license terms.





Select Use Microsoft Update checkbox.


Select the Include SQL Server product updates checkbox and select the Next button.



Ensure no failures are identified on the Install Rules check and select the Next button.
Note: the correct firewall rules may need to be created for the SQL Server, refer to the following Microsoft table for the list of ports and protocols used by SQL Server.


Service / Purpose
Protocol
Port
Analysis Services connections via HTTP (default)
TCP
80, 2383 Default Instance
Analysis Services connections via HTTPS (default)
TCP
443
Microsoft Distributed Transaction Coordinator (MS DTC)
TCP
135
Reporting services Web Services
TCP
80
Reporting Services configured for use through HTTPS
TCP
1433
Service Broker
TCP
4022
SQL Server 2014 Analysis Services
TCP
2382 (SQL Server Browser Services for SSAS port)
2383 (Clusters will listen only on this port)
SQL Server Browser Service (Database Engine)
UDP
1434
SQL Server Browser Service
TCP
2382 (Only Needed for named instance)
SQL Server default instance running over an HTTPS endpoint.
TCP
443
SQL Server Instance (Database Engine) running over an HTTP endpoint.
TCP
80,443 (SSL)
SQL Server Integration Services
TCP
135
SQL over TCP (default instance)
TCP
1433




The Feature Selection dialog will be displayed. Select All SQL Instance and Shared Features except PolyBase Query Service for External Data, R Services (In-Database) and R Server (Standalone). Adjust the Instance Root Directory, Shared Feature Directory and Shared Feature Directory (x86) to the configured data drive D:\ then select the Next button. 
Note: If you have a requirement for R Services, select the appropriate items.


The Feature Rules dialog will be displayed. Click the Show details button and ensure all operations complete successfully then select the Next button. 



The Instance Configuration dialog will be displayed. Select the instance type, Default is the most common.  If Default, leave the instance name as MSSQLSERVER and verify the SQL Server, Analysis Services and Reporting Services directories are set to the D: volume then click the Next button.



The Server Configuration dialog will be displayed.  Enter the appropriate service accounts and passwords in the Service Accounts section. Set the SQL Server Agent startup type to Automatic. Select the Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but does not work for log file allocations. This is enabled for each instance via the "Perform volume maintenance tasks" local security policy. In a cluster you have to grant the right on all nodes. If there are multiple instances on a server or cluster, you should grant this right to each instance’s security group.



Select the Collation tab. Verify collation setting and set appropriately for installation by clicking the Customize… button. When satisfied with setting click the Next button to continue.



NOTE: Most installations use the default setting of Latin1_General_CI_AS. If a collation setting has not been provided, leave the default setting. 

The Database Engine Configuration dialog will be displayed. Under the Server Configuration tab set the Authentication Mode to Mixed, enter the (sa) account password and add Specific SQL Server administrators then click the Data Directories tab.

Note: See the Service Accounts section in the table above for account specifications.



Set the Data, Log and Backup directories as depicted in the following figure then select the TempDB tab. These directories will align directly with the configuration specified in the drive table above .


NOTE: If this is a named instance installation, <MSSQLSERVER> will be replaced with the provided instance name. MSSQL12 equates to SQL Server 2014, MSSQL13 equates to SQL Server 2016.



Set the TempDB directories as depicted in the following figure.
 Note: To set the TempDB data directory, select the remove button to clear the default directory, select the add button and select new folder to create the following directory structure. 
The structure should be T:\MSSQL13.MSSQLSERVER\MSSQL\TEMP as depicted in the following figure. Select Next.


The Analysis Services Configuration dialog will be displayed. Accept the default Server Mode and specify which users have administrative permissions for analysis services. 


Select the Data Directories tab and Set the Data, Log, TempDB and Backup directories as depicted in the following figure.



The Reporting Services Configuration dialog will be displayed. Accept the default settings and select Next.



The Distributed Replay Controller dialog will be displayed. Use the Distributed Replay Controller Configuration page to specify the users you want to grant administrative permissions to for the Distributed Replay controller service. Users that have administrative permissions will have unlimited access to the Distributed Replay controller service. Select Next.


The Distributed Replay Client dialog will be displayed. Use the Distributed Replay Client Configuration page to specify the users you want to grant administrative permissions to for the Distributed Replay client service. Users that have administrative permissions will have unlimited access to the Distributed Replay client service. Accept the default settings including leaving the optional controller name blank. If there is a later requirement for using the distributed replay client and server, the controller name can be updated in the client configuration file. Select Next.


The Feature Configuration Rules dialog will be displayed. Ensure that every rule shows a status of passed. Select Next.


The Ready to Install dialog will be displayed. Select Next.


After the installation has completed the Complete dialog will be displayed. Ensure there are no errors or issues with the features installed.  If there are any issues the log file link can be selected to view detailed information about the installation.
Select the Close button to complete the installation.



INSTALL SQL SERVER MANAGEMENT TOOLS
The next step will require downloading the latest version of the SQL Server Management Studio. The link from the Installation Center will launch a browser to download the executable. Before this link is selected disable the IE Enhanced Security Configuration. From the Windows Start Menu select Server Manager.


The Server Manager – Local Server dialog will be displayed. Select the On link next to IE Enhanced Security Configuration. Under Administrators select Off and select Ok. 


With the SQL Server Installation Center still open select Install SQL Server Management Tools. Close the Server Manager Applet.
Note: Unlike previous versions of SQL Server, the SQL Server Management Studio (SSMS) is not part of the SQL Server Installation and must be downloaded and installed manually.



Select the Download SQL Server Management Studio link.


After the download is complete, launch the executable to install SSMS. Select Install. Restart after the installation.



SQL SERVER CONFIGURATION MANAGER SETTINGS.
Launch the SQL Server Configuration Manager


Expand the SQL Server Network Configuration Node
Select the Protocols for the newly installed Instance <MSSQLSERVER>
Ensure TCP/IP is enabled



Highlight the SQL Server Services Node

Ensure the SQL Server and SQL Server Agent services are Running and the Start Mode is set to Automatic. Close the SQL Server Configuration Manager.


Open SQL Server Management Studio


Connect to the SQL instance just installed. 

Highlight the Root Node, right click and select Properties


Select the Memory page and set the Maximum memory appropriate to server memory. The default memory setting of 2147483647 allows the SQL Server to consume all of the memory on the machine, the operating system should be allocated 10% of total memory at a minimum.

NOTE: For 32GB Machines set to 25600MB, for 64GB machines set to 54272MB.


Select the Advanced page and set the Cost Threshold for Parallelism to 20. By setting this to a larger setting you ensure the SQL Server only tries to parallelize larger queries. Parallelism only makes sense when you deal with larger queries – like in a reporting or Data Warehousing scenario.


Set the Max Degree of Parallelism to half the number of cpu cores (8) on the current virtual machine would be set to (4). The Max Degree of Parallelism defines how many worker threads each parallel operator in the execution plan can use.

This completes the SQL Server 2016 installation.