The first item I’m going to cover is SQL Server itself; I have had problems before installing and configuring SSRS at the same time as SQL Server, so I now always separate these into three separate steps.
The first part is to install SQL Server; do so in SQL Server Installation Center by launching the setup.exe from the installation media:
Click Installation and then click on New SQL Server stand-alone installation or add features to an existing installation:
Enter the product key and click Next:
Accept the license terms and click Next:
Decide whether you want to use Microsoft Update and click Next:
A check will run through the install rules to validate prerequisites are present; the screenshot, below, shows a warning against Windows Firewall which is still enabled. The SQL Server port (typically 1433 for TCP/IP and 1434 for UDP) will need to be open.
If the install rules pass without error, click Next to proceed:
The next step is to decide what to install; I’m going for the SQL Server Feature Installation:
There are a wide variety of features available with SQL Server. I am installing those required for a Microsoft Dynamics GP test system. The features I selected are:
- Database Engine Services
- Client Tools Connectivity
- Client Tools Backwards Connectivity
- Management Tools – Basic
- Management Tools – Complete
For Dynamics GP it is recommended to install it on its own instance of SQL Server. To do this I have chosen to configure a named instance called GP:
On the Server Configuration step, you need to select the Account Name the service accounts should run under. I never leave it using a built-in service account; I prefer to create and use an AD account. I have changed both SQL Server Agent and SQL Server Database Engine to use this service account:
With the service accounts entered, we need to change the collation of SQL Server from the default Windows collation to the supported SQL collation. Do this by clicking the Collation tab.
The default collation of Latin1_General_CI_AS will be displayed in the Database Engine field. Click on Customize to change the collation:
In the Customize the SQL Server 2014 Database Engine Collation window mark the SQL collation, used for backwards compatibility radio button.
The supported collation for Dynamics GP is SQL_Latin1_General_CP1_CI_AS; once you’ve selected this collation in the list, click OK:
The collation will have been returned to the Server Configuration window, so click Next to continue:
On the next step, set the Authentication Mode to Mixed Mode (SQL Server authentication and Windows authentication), enter and confirm the sa password and enter the SQL Server administrators:
I always prefer to change the data directories from the defaults to a simpler path to make the database, logs and backuops easier to handle. If this was a live server the data and log folders would be on separate physical drives, but in this example I am setting up a test system so they are all just on the C drive:
The final step of the install is to check the selections you have made in the previos steps; if any changes are required, click Back, otherwise click Install to begin the installation:
Once the installation has finished a completion step will be displayed showing what has successfully been installed:
Click Close to end the installation process.