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
  • Hardware Requirements
• Prerequisites
• Creating the SQL configuration file
• Creating the runbook
• Running the runbook
• Checking the SQL Server installation

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

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  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)
4. Once we have created the new runbook we will see it in a tab on the upper bar.
5. To start editing our runbook, we will have to check it out, click on Check Out to start edit mode of our runbook.
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.
   
7. We should now have named our runbook as follows:
   
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 , this runbook activity will be used to prompt us and ask us the server where the SQL Server 2016 will be installed on.
10. To add this a runbook activity simply drag and drop it to our new runbook.
11. Now we will add another runbook activity called Create Folder, this can be found under  .
12. Drag the Create Folder runbook activity beside our Initialize Data activity.
13. Next we will add another runbook activity from the , this one is called Copy File.
14. Now drag the Copy File runbook activity beside our Create Folder activity.
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.
    
16. Our Runbook should now look like this:
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.
    
18. The result should now look as follows:
    
19. Now drag a link between all the runbook activities.
20. We will not start configuring our runbook activities, we’ll start from the Initialize Data, double click to open the activity.
    
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.
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.
24. Our Initialize Data runbook activity should now look as follows:
25. We will now configure the Create Folder runbook activity, double click on it to modify it.
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.
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.
    
28. Select the Server Name in the Published Data and then click OK.
    
    
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.
    
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:
    
31. Let’s define a name for our folder to be created on the destination server, as follows:
32. Lastly we will also define which user account this runbook activity will run on by going to Security.
    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.
34. Lets continue by configuring the Copy File, double click the runbook activity to configure it.
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)
    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:
    Note: Since our folder contains sub-folders we will need to check the
    Include sub-folders checkbox.
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.
38. Now choose the Folder path from the Create Folder activity and click OK.
39. We should now have the source and destination paths filled in.
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.
41. Enter a domain account that has permissions to copy files to the destination server, once done, click Finish.
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.
43. Check the Flatten checkbox and choose Separate with line breaks, once done click Finish.
44. Finally we will configure our last runbook activity, Run Program, double click it to start configuring it.
    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.
46. In Activity field choose Initialize Data, we should now get all the Published Data from the Initialize Data activity
47. Now choose the Server Name under the Initialize Data activity and then click OK.
48. We should now have the following configuration:
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
    
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”
    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.
52. Enter a domain account that has permissions to run the setup.exe on the destination server, once done, click Finish.
53. Now that we are done, remember to check in our runbook.

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

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.
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.
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
4. Open the Summary text file to check the installation status of our SQL Server 2016.
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.
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!

509 total views, 4 views today