Share via

Orchestrator 1801: Installing SQL Server 2016 remotely

  • Article

Introduction

Microsoft SQL Server is one of the most used database platforms, in this guide I wanted to share with you how you can easily install SQL Server 2016 remotely by using a System Center Orchestrator 1801.

This guide will have some prerequisites, but I will go through every single one of them to make life easy for you!

SQL Server 2016 Requirements

Software Requirements

  • SQL Server 2016 (13.x) RC1 and later require .NET Framework 4.6 for the Database Engine, Master Data Services, or Replication. SQL Server 2016 setup automatically installs .NET Framework. You can also manually install .NET Framework from Microsoft .NET Framework 4.6 (Web Installer) for Windows.
  • Windows 8.1, and Windows Server 2012 R2 require KB2919355 before installing .NET Framework 4.6.

Hardware Requirements

  • Memory
    • Minimum:
      • Express Editions: 512 MB.
      • All other editions: 1 GB.
    • Recommended:
      • Express Editions: 1 GB.
      • All other editions: At least 4 GB and should be increased as database size increases to ensure optimal performance.
  • Processor Speed
    • Minimum: x64 Processor: 1.4 GHz.
    • Recommended: 2.0 GHz or faster.
  • Processor Type
    • Processor Type x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support.

Prerequisites

  • Windows 8 and above.
  • Windows Server 2012 and above.

↑ Back to top

Creating the SQL configuration file

We will start by creating the SQL configuration file which will be needed for our unattended SQL server installation.

The easiest way to do this is to mount up an SQL Server image and run the GUI installation till the end of the installation, note we will not finish the installation, before pressing Install the SQL server setup wizard will save the setup settings we've chosen into a ConfigurationFile.ini file.

  1. Let's start by mounting up our SQL Server 2016 image in Windows, first locate your SQL Server image.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql.png

  2. Select the SQL Server image, right click it and then choose mount.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql2.png

  3. We will automatically be redirected inside the SQL Server 2016 image.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql3.png

  4. Double click setup.exe to start the SQL Server installation.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql4.png

  5. Navigate to the Installation pane, on the menu on the left side.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql5.png

  6. Now click New SQL Server stand-alone installation or add features to an existing installation to start the SQL Server installation wizard.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql6.png

  7. Click **Next **to continue.

  8. In the next step, we can choose whether we want to include SQL Server product updates or not.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql7.png
    **
    **

  9. In this guide will go with a very basic SQL Server installation, so we will uncheck the box Include SQL Server product updates, click Next to continue.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql8.png

  10. The next step we will choose the installation type, in this case we will perform a completely new installation of SQL Server 2016, click Next to continue.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql9.png

  11. In the following step we will insert our product key for our SQL Server 2016, once we have entered our product key, click Next to continue.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql10.png

  12. Next up we will read through the license agreement (or just skip).

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql11.png

  13. Check box I accept the license terms, click Next to continue.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql12.png

  14. Now we will be able to choose what features we want to install, and were the instance will be installed.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql13.png

  15. As I mentioned earlier we will go with a very basic SQL Server installation, so we will only choose the Database Engine Services, we will also go with the default instance root directory C:\Program Files\Microsoft SQL Server click Next to continue.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql14.png

  16. Next up is the instance configuration, we will go with the Default instance which is the MSSQLSERVER, click Next to continue.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql15.png

  17. Now we will be able to change the service accounts and the collation for our SQL Server, in this guide we will go with the default settings, so we can continue by clicking Next.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql16.png

  18. In the next step we can specify the SQL Server administrators and the data directories of our SQL Server.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql17.png

  19. We will add the builtin administrators group (aka local administrators) who will become the SQL Server administrators, click Add...
    **
    **https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql18.png

  20. Now click on **Locations **and in the search field enter Localhost\Administrators, this will add the BUILTIN\Administrators group, then click OK.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql19.png

  21. Now we should have the following:

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql20.png

  22. We should now have created our ConfigurationFile.ini file, you will see the path to where it was created at the bottom of the next window:

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql211.png

  23. We can now click Cancel to cancel the SQL Server installation, as we have now successfully created our ConfigurationFile.ini.

  24. Now let's locate our ConfigurationFile.ini file at *C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\

  25. Open up a Windows Explorer and head to the location where the SQL Server ConfigurationFile.ini was created.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql22.png

  26. Now let's copy the ConfigurationFile.ini to our media location that we will use for our unattended SQL Server installation.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql23.png

The SQL Server 2016 ConfigurationFile.ini can be downloaded from here.

↑ Back to top

Creating the runbook

Now let's create the runbook which will automate the whole installation process of our SQL Server 2016 installation.

  1. We'll begin by logging on to our Orchestrator Runbook Server.

  2. Open up the Runbook Designer https://thesystemcenterblog.com/wp-content/uploads/2018/05/scorch_icon.png console.

  3. Let's create a new runbook, right click on Runbooks and then choose
    New > Runbook. (If you have already created a folder hierarchy right click the folder where you want the new runbook to be placed, then choose New > Runbook.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql24.png

  4. Once we have created the new runbook we will see it in a tab on the upper bar.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql25.png

  5. To start editing our runbook, we will have to check it out, click on Check Out to start edit mode of our runbook.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql26.png

  6. We will begin by renaming our runbook to something more convenient, like "Install_SQL_Server_2016" right click on your runbook and click Rename and then type in a new name for the runbook.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_33.png

  7. We should now have named our runbook as follows:

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_34.png

  8. Let's continue, on the right side of the Runbook Designer console we can find all the available runbook activities, in this guide we will use basic runbook activities that come with Orchestrator by default.

  9. We will start by using a Initialize Data runbook activity, it can be found under https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql212.png, this runbook activity will be used to prompt us and ask us the server where the SQL Server 2016 will be installed on.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql27.png

  10. To add this a runbook activity simply drag and drop it to our new runbook.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql28.1.png

  11. Now we will add another runbook activity called Create Folder, this can be found under  https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql213.png.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql29.png

  12. Drag the Create Folder runbook activity beside our Initialize Data activity.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql30.1.png

  13. Next we will add another runbook activity from the https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql213.png, this one is called Copy File.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql31.png

  14. Now drag the Copy File runbook activity beside our Create Folder activity.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_sql32.1.png

  15. We will now add the runbook activity that will run the SQL Server setup itself, go to the right under System and drag a runbook activity called Run Program.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_35.png

  16. Our Runbook should now look like this:

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_36.png

  17. We will now need to link these runbook activities to each other to make the process flow.
    You can do this by mouse hovering over one activity, for example mouse hover over the Initialize Data runbook activity and you will see an arrow on the right side of the runbook activity, click on it and drag a line to the next runbook activity Create Folder.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_37.png

  18. The result should now look as follows:

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_38.png

  19. Now drag a link between all the runbook activities.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_39.png

  20. We will not start configuring our runbook activities, we'll start from the Initialize Data, double click to open the activity.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_40.1.png

  21. In this activity we will make a prompt for the user to enter the server where the SQL Server 2016 will be installed on.

  22. To do this we will first click on Add, this will add a parameter within the Initialize Data runbook activity.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_41.png

  23. Change the parameter name by clicking on "Parameter 1", a new window will open where we can enter a new name for the parameter, "Server Name" and then click OK.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_43.png

  24. Our Initialize Data runbook activity should now look as follows:

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_44.png

  25. We will now configure the Create Folder runbook activity, double click on it to modify it.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_45.png

  26. In this runbook activity we will create a folder, we will use this folder to copy the SQL Server 2016 installation files to. We will take the server name from the previous activity Initialize Data, to do this we right click on the Path field and choose **Subscribe **and then click Published Data.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_46.png

  27. We should now see all available data that can be fetched from the Initialize Data activity. This means that we can get the *Server Name *and use it in all the other runbook activities in our runbook.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_47.png

  28. Select the Server Name in the Published Data and then click OK.
    **
    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_48.png

    **

  29. Now since this will be an UNC (Uniform Naming Convention) path, for example \server\share\ we will need to insert back slashes in front and behind of our Published Data so that the path becomes correct.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_49.3.png

  30. Now at the end of our current path we will insert a share name where we will create our folder.
    If you are an Domain Administrator you can use hidden shares, "drive letter + $" (example: D$). In this guide we will use a hidden shares on the D-drive as follows:

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_50.png

  31. Let's define a name for our folder to be created on the destination server, as follows:

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_51.png

  32. Lastly we will also define which user account this runbook activity will run on by going to Security.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_52.png
    **
    Note:** If you go with unchanged settings, in other terms, default settings it will use the Orchestrator's service account.In this guide we will be using a Domain Administrator account, to avoid any permission problems, what we need to make sure is that the account we are using has enough permissions to create a folder where we will copy the SQL installation files and also run the SQL Server setup.

  33. Enter a domain account that has permissions to perform the tasks mentioned above, once we are done, click Finish.https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_53.png

  34. Lets continue by configuring the Copy File, double click the runbook activity to configure it.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_54.png

  35. First we'll enter the local source where we have stored our SQL server installation files, in this guide we have it stored on the Orchestrator runbook server's C drive (C:\Install)

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_55.png

    **Note: **The SQL setup files will have to be copied from an SQL Server installation image to a local or remote folder. Also make sure that it's in the same folder as the SQL ConfigurationFile.ini that we created previously.

  36. Since we want to copy all the files in the folder we will enter the path and a star (*) as follows:

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_56.png

    Note: Since our folder contains sub-folders we will need to check the
    Include sub-folders checkbox.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_57.png

  37. Let's continue by entering our destination path, since we will create a folder in our 2nd step in our runbook we will use that as our destination. We can do this by using Published Data, right click the Destination Folder field and choose **Subscribe **and then click Published Data.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_58.png

  38. Now choose the Folder path from the Create Folder activity and click OK.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_59.png

  39. We should now have the source and destination paths filled in.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_60.1.png

  40. We will also configure a different domain account to run this runbook activity, go to Security on the left of the *Copy File Properties.

    *https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_62.png

  41. Enter a domain account that has permissions to copy files to the destination server, once done, click Finish.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_61.png

  42. Now we will configure the Run Behavior of this runbook activity, as there are many files we will have to flatten the data, go to the Run Behavior on the left of the Copy File Properties.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_81.png

  43. Check the Flatten checkbox and choose Separate with line breaks, once done click Finish.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_82.png

  44. Finally we will configure our last runbook activity, Run Program, double click it to start configuring it.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_63.png

    We will need to configure on which computer it will run on, the program path and the program parameters.

  45. We can use the Published Data from our 1st runbook activity "Initialize Data" to get the server name where we will install the SQL Server, right click on the Computer field and choose **Subscribe **and then click Published Data.
    **
    **https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_64.png

  46. In Activity field choose Initialize Data, we should now get all the Published Data from the Initialize Data activity

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_65.png

  47. Now choose the Server Name under the Initialize Data activity and then click OK.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_66.png

  48. We should now have the following configuration:

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_67.png

  49. Now we will configure the program path, since we're creating a folder named SQL_media on the D drive of the destination server, our path will be D:\SQL_media\setup.exe
    **
    **https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_87.png

  50. Since we will run an unattended SQL Server installation we will need to run the SQL Server setup with our ConfigurationFile as a parameter, to use the configurationFile parameter we will need to enter the following
    /ConfigurationFile = "D:\SQL_media\ConfigurationFile.ini"
    **
    **https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_89.png

    Note: To get a better view over the fields within runbook activities you can right click the field and then choose Expand...

  51. Finally we will also configure this runbook activity, to run on a different domain account, go to Security on the left of the *Run Program Properties.

    *https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_74.png

  52. Enter a domain account that has permissions to run the setup.exe on the destination server, once done, click Finish.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_75.png

  53. Now that we are done, remember to check in our runbook.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_76.png

Our runbook is now finally ready!

↑ Back to top

Running the runbook

Now let's test our runbook to see if it works, we will use the Orchestrator Web Console to run the runbook.

  1. In your Runbook Designer you can open the Orchestrator Web Console by clicking on https://thesystemcenterblog.com/wp-content/uploads/2018/06/scorch_console.png, if you haven't configured that you can open the a browser that supports Silverlight (For example Internet Explorer) and enter your Orchestrator Web Console URL (Default is: http://localhost:82/)

  2. Once that you have your Web Console open, expand the runbook hierarchy and locate your SQL Server runbook.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_77.png

  3. To run our runbook, click https://thesystemcenterblog.com/wp-content/uploads/2018/06/start_runbook.png on the right under Actions.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_78.png

  4. A new window will appear and we will be prompted to enter the Server Name, this is the destination server where we want our SQL Server to be installed.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_79.png

  5. Lastly we will need to choose on which Runbook Server this runbook will run on, pick any runbook server available and then click on https://thesystemcenterblog.com/wp-content/uploads/2018/06/pil.png and to start the runbook click Start.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_80.png

  6. Let's go back to our Runbook Designer, we should now see that our runbook has started.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_83.png

  7. If we head to our destination server we should now be seeing that the SQL installation folder has been created.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_84.png

  8. We should also see that the SQL installation files are being copied to the folder that we've created.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_86.png

↑ Back to top

Checking the Server 2016 installation

We will now check and confirm if our SQL Server 2016 was successful by checking both Orchestrator and SQL Server installation logs.

  1. Once the runbook is completed we should have see at the bottom of the Runbook Designer, in the Log History tab whether it was successful or not.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_93.png

  2. Our runbook has run successfully, let's also make sure that all runbook steps were successful by double clicking the log event under *Log History.

    *https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_94.png

  3. Lastly we will also check the SQL server installation log on the destination server if the installation was successful. You can find the setup log of SQL Server 2016 here: C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log
    **
    **https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_90.png

  4. Open the Summary text file to check the installation status of our SQL Server 2016.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_91.png

  5. Everything looks OK so far, now finally we will check if the SQL services are running. Go to Start and then type services.msc and hit enter. Now let's look for the SQL Server services.

    https://thesystemcenterblog.com/wp-content/uploads/2018/06/sql_scorch_92.png

  6. We can now confirm that the services are there and they are running!

We have now successfully installed SQL Server 2016 with System Center Orchestrator 1801!