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.

SharePoint SPFastDeploy

SharePoint SPFastDeploy is a must have tool for developing solutions in SharePoint Online. This tool can be downloaded from Here and installed into Visual Studio. Without this tool it can take several minutes to deploy a SharePoint Add-In to SharePoint Online. 

Note: You will still have to initially deploy the solution to SharePoint Online using the traditional methods however once this tool is installed and the SharePoint Add-In has been deployed, you can simply right click a file after making changes and select Fast deploy to SP App. Using this method takes only seconds to save the new file.

Once this tool is installed it will add a new shortcut to the tools menu

There are a couple of way to configure this tool for simpler use

• Enable SPFastDeploy on Save - Select Tools | Options | SPFastDeploy, under Misc Select True for Deploy when saving. This will use SPFastDeploy each time a file is changed and saved.

• Add SPFastDeploy to Right Click Context Menu - Select Tools | Customize | Commands Tab | Select the Context Menu radio button and choose Project and Solution Context Menus | Item from the dropdown control.

•  Next select the Add Command Button and choose Tools under Categories and select SPFastDeploy from the list of commands.

• Ensure the right click functionality is now available by selecting a file in your SharePoint Add-In project and right clicking to view the context menu

ASPNET Core MVC display database connection string to page

ASPNET Core MVC display database connection string on page

I was recently asked how to display the database connection string for an ASPNET Core MVC application. This is not as straight forward as it was with previous version of ASPNET when you could just use the ConfigurationManager to read settings from the applications web.config file.

In ASPNET Core MVC there is no web.config, usually custom settings will be created in a appsettings.json file. To get access to the appsettings file, first there are some configuration settings that will need to be changed.

First this is how the current connection string is defined in the appsettings.json

Connection String

"database": {
"connection": "Data Source=.;Initial Catalog=OdeToFood;Integrated  Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"

}

Create a class to store the connection string property (AppSettings.cs)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace CoreTest
{
    public class AppSettings
    {
        public string connection { get; set; }
    }
}

Update the project Startup.cs file

Ensure the constructor is reading the appsettings.json file

public Startup(IHostingEnvironment env)
    {
        var builder = new ConfigurationBuilder()
            .SetBasePath(env.ContentRootPath)
            .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
            .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
            .AddEnvironmentVariables();
        Configuration = builder.Build();
    }

Add these lines to ConfigureServices

 services.AddOptions();
 services.Configure<AppSettings>(Configuration.GetSection("database"));

Update the controller (HomeController.cs)

Create the app settings variable and inject the AppSettings into the constructor

 public class HomeController : Controller
 {
    private AppSettings _AppSettings;

    public HomeController(IOptions<AppSettings> settings)
    {
        _AppSettings = settings.Value;
 }

Add the controller method that will send the connection string to the view

 public async Task ContentAction()
    {
        var constr = _AppSettings.connection;
        var jsonString = "{\"connectionString\""+ constr + "}";
        byte[] data = Encoding.UTF8.GetBytes(jsonString);
        Response.ContentType = "application/json";
        await Response.Body.WriteAsync(data, 0, data.Length);
    }

Call the controller method from the View (Index.cshtml)

 <div>
    <a asp-controller="Home" asp-action="ContentAction" class="btn btn-  success confirmCreate">
        Generate Response
        <i class="fa fa-angle-left"></i>
    </a>
</div>

View Output will display the connection string

{"connectionString"Data Source=.;Initial Catalog=OdeToFood;Integrated     Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False}

What SQL Server is my SharePoint machine pointed to?

I recently powered on a SharePoint 2016 box that I haven't used in a while, when I powered it on and tried to browse to the default SharePoint site at http://machinename I received the classic .net error:

Server Error in '/' Application.

Runtime Error

I also attempted to open the SharePoint Central Administration page and received the same error.

I opened the web.config for the default SharePoint site at  c:\inetpub\wwwroot\wss\VirtualDirectories\80 and searched for the word 'error' located the xml node...

<customErrors mode="On" /> and changed this setting to 'Off'

Refreshing the http://machinename page now displays the hidden error message...

'This operation can be performed only on a computer that is joined to a server farm by users who have permissions in SQL Server to read from the configuration database. To connect this server to a server farm, use the SharePoint Products Configuration Wizard, located on the Start menu in Microsoft Sharepoint 2016 Products.

Unfortunately, if I attempt to run the SharePoint Products Configuration Wizard it also displays an error message 'Failed to detect if this server is joined to a server farm,,,'

Turns out the SQL server was just turned off but in my environment I have many SharePoint Servers, SQL Servers, Web Servers, etc. I wasn't sure which SQL server this SharePoint machine was pointed to and I only wanted to power on this one SQL Server.

There are probably multiple ways to determine which SQL Server the SharePoint box is pointed to but the way I chose to find out was to just reference a registry key that defined the connection string to the configuration database. The key name is dsn and the Data value is populated with the connection string.

My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\16.O\Secure\ConfigDb

Connect SQL Server 2014 Management Studio to Azure SQL Database

To connect SQL Server Management Studio (SSMS) to an Azure SQL database log in to the Azure Management Portal

Create a new database or connect to an existing database

For this example I'll create a new SQL database, from Azure home page select SQL Databases and select  +Add

Select the database

After the database has been created (may take a few minutes and a refresh to appear), from the SQL databases menu select the database that was just created (MyDb).

Add a firewall rule

In order to connect to the Azure SQL database a firewall rule must be created with the IP address or range of IP addresses that will be allowed to access the database.

After the firewall rule has been created the database can now be connected to using SSMS.

Copy the server name

From the Azure Management Portal Select SQL Databases and select the MyDb database

Copy the server name listed below Essentials for your database

Determine Server Admin username for the Azure SQL Server

From the SQL Databases menu select the database (MyDb)

Select the server name link below Essentials - Note: you must have a username and password that has access to the database to connect using SSMS.

Launch the SSMS Application

Paste the server name that was copied above and enter the username and password