GUIDE, SCORCH, SQL

Installing SQL Server 2016 remotely with Orchestrator 1801

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!

Contents

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.

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.scorch_sql
  2. Select the SQL Server image, right click it and then choose mount.scorch_sql2
  3. We will automatically be redirected inside the SQL Server 2016 image.scorch_sql3
  4. Double click setup.exe to start the SQL Server installation.scorch_sql4
  5. Navigate to the Installation pane, on the menu on the left side.scorch_sql5
  6. Now click New SQL Server stand-alone installation or add features to an existing installation to start the SQL Server installation wizard.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.scorch_sql7
  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.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.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.scorch_sql10
  12. Next up we will read through the license agreement (or just skip).scorch_sql11
  13. Check box I accept the license terms, click Next to continue.scorch_sql12
  14. Now we will be able to choose what features we want to install, and were the instance will be installed.scorch_sql13
  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.scorch_sql14
  16. Next up is the instance configuration, we will go with the Default instance which is the MSSQLSERVER, click Next to continue.scorch_sql15
  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.scorch_sql16
  18. In the next step we can specify the SQL Server administrators and the data directories of our SQL Server.scorch_sql17
  19. We will add the builtin administrators group (aka local administrators) who will become the SQL Server administrators, click Add…scorch_sql18
  20. Now click on Locations and in the search field enter Localhost\Administrators, this will add the BUILTIN\Administrators group, then click OK.scorch_sql19
  21. Now we should have the following:
    scorch_sql20
  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:scorch_sql211
  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.scorch_sql22
  26. Now let’s copy the ConfigurationFile.ini to our media location that we will use for our unattended SQL Server installation.scorch_sql23

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

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 scorch_icon 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)scorch_sql24
  4. Once we have created the new runbook we will see it in a tab on the upper bar.scorch_sql25
  5. To start editing our runbook, we will have to check it out, click on Check Out to start edit mode of our runbook.scorch_sql26
  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.
    sql_scorch_33
  7. We should now have named our runbook as follows:
    sql_scorch_34
  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 scorch_sql212, this runbook activity will be used to prompt us and ask us the server where the SQL Server 2016 will be installed on.scorch_sql27
  10. To add this a runbook activity simply drag and drop it to our new runbook.scorch_sql28.1
  11. Now we will add another runbook activity called Create Folder, this can be found under  scorch_sql213.scorch_sql29
  12. Drag the Create Folder runbook activity beside our Initialize Data activity.scorch_sql30.1
  13. Next we will add another runbook activity from the scorch_sql213, this one is called Copy File.scorch_sql31
  14. Now drag the Copy File runbook activity beside our Create Folder activity.scorch_sql32.1
  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.
    sql_scorch_35
  16. Our Runbook should now look like this:sql_scorch_36
  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.
    sql_scorch_37
  18. The result should now look as follows:
    sql_scorch_38
  19. Now drag a link between all the runbook activities.sql_scorch_39
  20. We will not start configuring our runbook activities, we’ll start from the Initialize Data, double click to open the activity.
    sql_scorch_40.1
  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.sql_scorch_41
  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.sql_scorch_43
  24. Our Initialize Data runbook activity should now look as follows:sql_scorch_44
  25. We will now configure the Create Folder runbook activity, double click on it to modify it.sql_scorch_45
  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.sql_scorch_46
  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.
    sql_scorch_47
  28. Select the Server Name in the Published Data and then click OK.
    sql_scorch_48
  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.
    sql_scorch_49.3
  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:
    sql_scorch_50
  31. Let’s define a name for our folder to be created on the destination server, as follows:sql_scorch_51
  32. Lastly we will also define which user account this runbook activity will run on by going to Security.sql_scorch_52
    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.sql_scorch_53
  34. Lets continue by configuring the Copy File, double click the runbook activity to configure it.sql_scorch_54
  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)sql_scorch_55
    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:sql_scorch_56
    Note: Since our folder contains sub-folders we will need to check the
    Include sub-folders checkbox.sql_scorch_57
  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.sql_scorch_58
  38. Now choose the Folder path from the Create Folder activity and click OK.sql_scorch_59.png
  39. We should now have the source and destination paths filled in.sql_scorch_60.1
  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.sql_scorch_62
  41. Enter a domain account that has permissions to copy files to the destination server, once done, click Finish.sql_scorch_61
  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.sql_scorch_81
  43. Check the Flatten checkbox and choose Separate with line breaks, once done click Finish.sql_scorch_82
  44. Finally we will configure our last runbook activity, Run Program, double click it to start configuring it.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.sql_scorch_64
  46. In Activity field choose Initialize Data, we should now get all the Published Data from the Initialize Data activitysql_scorch_65.png
  47. Now choose the Server Name under the Initialize Data activity and then click OK.sql_scorch_66
  48. We should now have the following configuration:sql_scorch_67
  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
    sql_scorch_87
  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”sql_scorch_89
    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.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.sql_scorch_75
  53. Now that we are done, remember to check in our runbook.sql_scorch_76.png

Our runbook is now finally ready!

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 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.
    sql_scorch_77
  3. To run our runbook, click start_runbook.png on the right under Actions.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.sql_scorch_79
  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 pil and to start the runbook click Start.sql_scorch_80
  6. Let’s go back to our Runbook Designer, we should now see that our runbook has started.sql_scorch_83
  7. If we head to our destination server we should now be seeing that the SQL installation folder has been created.sql_scorch_84
  8. We should also see that the SQL installation files are being copied to the folder that we’ve created.sql_scorch_86

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.sql_scorch_93
  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.sql_scorch_94
  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\Logsql_scorch_90.png
  4. Open the Summary text file to check the installation status of our SQL Server 2016.sql_scorch_91
  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.sql_scorch_92
  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!

1,083 total views, 8 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.