Build your own SQL 2012 Demo Machine – Part 4 – Installing SQL Server twice

Step 5: Install SQL Server 2012 twice

We’ll have to run through this three times to install the different instances required, but two warm up ones are required before we get to SharePoint and PowerPivot for SharePoint. First of all we need to install everything for the default instance.

1. Run setup, choose installation and select New SQL Server stand alone installation.

Fig 5.1: New SQL Server Installation
Fig 5.1: New SQL Server Installation

2. This will run through Setup Support Rules, after which you should see this:

Fig 5.2: Setup Support Rules
Fig 5.2: Setup Support Rules

3. Click on OK. Next you get the Product Key screen:

Fig 5.3: Product Key Screen
Fig 5.3: Product Key Screen

4. Enter your Product Key (or choose Evaluation if appropriate) and click Next. The License Terms dialog comes up:

Fig 5.4: License Terms
Fig 5.4: License Terms

5. Accept the license terms, and Feature usage if you want to share. Then click Next. Some chugging will occur and you will then get the Setup Support Rules dialog pop up with a couple of warnings:

Fig 5.5: Setup Support Rules Warnings
Fig 5.5: Setup Support Rules Warnings

These warnings are

  • It doesn’t like the fact you are installing SQL on a Domain Controller – nothing we can do about that
  • It doesn’t like the fact Windows Firewall is on – as we don’t plan remote access, we don’t care

6. Click Next. Here we choose what we are going to install.

Fig 5.6: Setup Role
Fig 5.6: Setup Role

7. Choose a SQL Server Feature Installation and click Next. This brings up the Feature Selection.

Fig 5.7: Feature Selection
Fig 5.7: Feature Selection

Click on select all, leaving the feature directory paths untouched.

8. Click Next. This will lead to the Installation Rules dialog

Fig 5.8: Installation Rules
Fig 5.8: Installation Rules

Everything should have passed.

9. Click Next. This brings us to Instance Configuration:

Fig 5.9: Instance Configuration
Fig 5.9: Instance Configuration

We are going to install the Default instance, and leave all settings as is.

10. Click Next to bring up the Disk Space requirements dialog.

Fig 5.10: Disk Space Requirements
Fig 5.10: Disk Space Requirements

Everything should be OK.

11. Click Next. The Server Configuration dialog comes up:

Fig 5.11: Server Configuration
Fig 5.11: Server Configuration

This will advise (if you try and move on) that the SSAS Account is invalid.

12. Click the dropdown in Account Name to browse for a new user.

Fig 5.12: Select User
Fig 5.12: Select User

Type “admin” and click the “Check Names” button. This should validate the name and change it to “Administrator”. Then click OK. Enter the password in the dialog from Fig 5.11.

13. Click Next – we won’t be changing the default collation.

Fig 5.13: Database Engine Configuration
Fig 5.13: Database Engine Configuration

We will only be using Windows Authentication mode, so leave that as per the default. Click the “Add Current User” button to make the Administrator the SQL Administrator. Change the Data Directories if you want to customise them – though there isn’t much need for a demo box – and leave Filestream turned off.

14. Click Next to bring up the Analysis Services configuration dialog.

Fig 5.14: Analysis Services Configuration
Fig 5.14: Analysis Services Configuration

We are going to install Multidimensional SSAS (i.e. old school OLAP). As before, click Add Current user to make the Administrator the SSAS Administrator. Configure the Data Directories if you want.

15. Click Next to get to Reporting Services configuration.

Fig 5.15: Reporting Services Configuration
Fig 5.15: Reporting Services Configuration

Here we will make a change to Reporting Services Native Mode, and switch it to Install Only.

16. Click Next for Error Reporting.

Fig 5.16: Error Reporting
Fig 5.16: Error Reporting

17. Click Next for the installation configuration rules. Expand by clicking on “Show Details”.

Fig 5.17: Instance Configuration Rules
Fig 5.17: Instance Configuration Rules

Everything should have passed.

18. Click Next to get to the Installation Confirmation screen

Fig 5.18: Ready to Install
Fig 5.18: Ready to Install

And we’re good to go!

19. Click Install and watch the Installation Progress Bar. At this point I’d advise doing something else – this can take an hour or two.

Fig 5.19: Installation Complete
Fig 5.19: Installation Complete

And, all going to plan, everything is done.

20. Click OK, Close the Setup dialog, then reboot the machine. If necessary install updates and restart again.

21. Now, for our 2nd Install of SQL Server – this time to get in the Tabular Mode Analysis Server. Run Setup again, and repeat all of the steps above (on the way you will go through the Product Updates check, just go through this and click Next) until you get to the Installation Type Screen:

Fig 5.20: Installation Type
Fig 5.20: Installation Type

Here we are performing a new installation.

22. Click Next, Enter your product key again, accept licence terms, and then perform a SQL Server Feature installation (as in Fig 5.6).

23. Click Next to get to Feature Selection.

Fig 5.21: Feature Selection
Fig 5.21: Feature Selection

Check Analysis Services only.

24. Click Next. You’ll pass on to Instance Configuration:

Fig 5.22: Instance Configuration
Fig 5.22: Instance Configuration

Now we Install a Named Instance. I’ve opted to call it TABULAR. Call it anything you like except POWERPIVOT – that Instance will get created later for SharePoint integration. Then, click Next to get to Disk Space Requirements.

25. Click Next again to Server Configuration:

Fig 5.23: Server Configuration
Fig 5.23: Server Configuration

As in Figures 5.11 & 5.12 we need to set the Service Account to Administrator and enter the password.

26. Click Next to get to Analysis Services configuration:

Fig 5.24: Analysis Services Configuration
Fig 5.24: Analysis Services Configuration

This time we are installing the Server in Tabular Mode, so change the default Server Configuration.  As before, make the Administrator the SSAS Administrator by clicking the “Add Current User” button.

27. Click Next. Run through all subsequent screens until you get to the Ready to Install dialog, then click Install, and go make another cup of tea. When Installation is complete, restart and get ready for the SharePoint install.

Configuring Data Quality Services (This section was added on 01 May)

28: Next we need to configure Data Quality Services by doing the Post Installation tasks. As per the MSDN Article, locate DQSInstaller.exe under Start > All Programs > Microsoft SQL Server 2012 > Data Quality Services > Data Quality Server Installer:

Fig 5.25: Data Quality Server Installer
Fig 5.25: Data Quality Server Installer

29: Click it to start execution. You will be presented with a command Prompt window asking for a Database Master Key.

Fig 5.26: Data Quality Server Installer - Database Master Key
Fig 5.26: Data Quality Server Installer - Database Master Key

30. Enter a suitable password (note it down!) and hit enter. Re-enter it to confirm and hit enter again. The process will run for a while. Until you get the completion message in the command prompt:

Fig 5.27: Data Quality Server Installer - Completion
Fig 5.27: Data Quality Server Installer - Completion

31. Hit enter. The command window will close.

32. Launch SQL Server Management Studio and connect to the default Instance of SQL Server. Locate your Administrator Role under Security:

Fig 5.28: Server Security for Default Instance
Fig 5.28: Server Security for Default Instance

33. Double click to launch  the Login Properties.

Fig 5.29: Login Properties
Fig 5.29: Login Properties

34. As indicated above, select the “User Mapping” tab.

35. Map the DQS_MAIN database

36: Check the dqs_administrator role.

37: Click OK, then close SQL Server Management Studio. Data Quality Services is now configured.

Configuring Master Data Services (This section was added on 22 May)

This is as per this MSDN Arcticle

38: Run the Master Data Services Configuration Manager, located at Start > All Programs > Microsoft SQL Server 2012 > Master Data Services:

Fig 5.30: Master Data Services Configuration Manager
Fig 5.30: Master Data Services Configuration Manager

This will launch the Master Data Services Configuration Manager:

Fig 5.31: Master Data Services Configuration Manager
Fig 5.31: Master Data Services Configuration Manager

If, as shown above, you get the IIS error shown here about .svc handler mappings, follow steps 39-42 (as per this TechNet article):

39: Open up a command prompt and navigate to %windir%\Microsoft.NET\Framework64\v4.0.30319

40: Run the command aspnet_regiis -i to install ASP.Net

Fig 5.32: Install ASP.NET
Fig 5.32: Install ASP.NET

You should get the result above.

41. Click Exit on the Master Data Services Configuration Manager

42. Relaunch the Master Data Services Configuration Manager:

Fig 5.33: Master Data Services Configuration Manager
Fig 5.33: Master Data Services Configuration Manager

There should now be no warning for IIS.

43. In the Master Data Services Configuration Manager, click on Database Configuration to get the Database Configuration screen:

Fig 5.34: Master Data Services Configuration Manager - Database Configuration
Fig 5.34: Master Data Services Configuration Manager - Database Configuration

44. Click on “Create Database” to launch the Create database Wizard

Fig 5.35: Master Data Services Configuration Manager - Create Database Wizard
Fig 5.35: Master Data Services Configuration Manager - Create Database Wizard

45: Click  Next to select the Database Server

Fig 5.36: Master Data Services Configuration Manager - Create Database Wizard - Select Server
Fig 5.36: Master Data Services Configuration Manager - Create Database Wizard - Select Server

Accept the default settings.

46: Click Next to create the Database

Fig 5.37: Master Data Services Configuration Manager - Create Database Wizard - Create Database
Fig 5.37: Master Data Services Configuration Manager - Create Database Wizard - Create Database

Name your data base something suitable, such as MDS_Demo. Leave the Database collation as default.

47: Click Next to set the Administrator account

Fig 5.38: Master Data Services Configuration Manager - Create Database Wizard - Set Administrator Account
Fig 5.38: Master Data Services Configuration Manager - Create Database Wizard - Set Administrator Account

Accept the default (i.e. your Administrator account).

48: Click Next for the Summary screen

Fig 5.39: Master Data Services Configuration Manager - Create Database Wizard - Summary
Fig 5.39: Master Data Services Configuration Manager - Create Database Wizard - Summary

Review the summary and go back if you need to make changes.

49: Click Next to progress the Configuration

Fig 5.40: Master Data Services Configuration Manager - Create Database Wizard - Progress
Fig 5.40: Master Data Services Configuration Manager - Create Database Wizard - Progress

Everything should complete successfully.

50: Click Finish. The System Settings will now be editable. We won’t be making any changes.

Fig 5.41: Master Data Services Configuration Manager - Database Configuration
Fig 5.41: Master Data Services Configuration Manager - Database Configuration

51. Click on the Web Configuration option on the Master Data Services Configuration Manager.

Fig 5.42: Master Data Services Configuration Manager - Web Configuration
Fig 5.42: Master Data Services Configuration Manager - Web Configuration

52. In the drop down select “Default Web Site”

53. Click “Create Application…”. This will launch the Create Web Application dialig.

Fig 5.43: Master Data Services Configuration Manager - Create Web Application
Fig 5.43: Master Data Services Configuration Manager - Create Web Application

Accept all the default settings, and enter the Administrator username and passwords.

54. Click OK. This will close the dialog.

55. In the “Associate Application with Database” section, click on the “Select…” button to choose a database.

Fig 5.44: Master Data Services Configuration Manager - Associate Application with Database
Fig 5.44: Master Data Services Configuration Manager - Associate Application with Database

This will launch the dialog to choose your MDS database.

Fig 5.45: Master Data Services Configuration Manager - Connect Application to Database
Fig 5.45: Master Data Services Configuration Manager - Connect Application to Database

56: Click Connect (otherwise the Master Data Services database dropdown doesn’t populate)

57: Choose your newly created MDS database in the dropdown.

58. Click OK.

59. Click Apply in the Master Data Services Configuration Manager – Web Configuration dialog.

The Configuration complete dialog will appear.

Fig 5.46: Master Data Services Configuration Manager - Configuration Complete
Fig 5.46: Master Data Services Configuration Manager - Configuration Complete

60: Uncheck the “Launch web application in browser” checkbox.

61: Click OK to close the popup.

62. Next, click “Enable integration with Data Quality Services” in the Master Data Services Configuration Manager – Web Configuration dialog.

Fig 5.47: Master Data Services Configuration Manager - Enable Integration with Data Quality Services
Fig 5.47: Master Data Services Configuration Manager - Enable Integration with Data Quality Services

This will probably fail with the error: “Error while trying to enable integration with Data Quality Services. SQL Server returned the following error: Windows NT User or group ‘[Localmachine]\MDS_ServiceAccounts’ not found. Check the name again.”

Fig 5.48: Master Data Services Configuration Manager - Enable Integration with Data Quality Services Error
Fig 5.48: Master Data Services Configuration Manager - Enable Integration with Data Quality Services Error

This is a known problem with installing MDS on a Domain Controller, and the solution is as described here.

63. Click OK to close the error popup.

64. Launch SQL Server Management studio

65. Connect to the SQL Instance hosting DQS (should be your default instance).

66. Navigate to Security > Logins, right click and choose “New Login…”

Fig 5.49: Create a new Login for MDS_ServiceAccounts
Fig 5.49: Create a new Login for MDS_ServiceAccounts

67. In the dialog that launches, create a new login for [YourDomain]\MDS_ServiceAccounts. Leave everything else as default.

Fig 5.50: Create a new Login for MDS_ServiceAccounts
Fig 5.50: Create a new Login for MDS_ServiceAccounts

68: Next,navigate to Databases > DQS_MAIN > Security > Users, right click and select “New User…”

Fig 5.51: Create a new User for MDS_ServiceAccounts
Fig 5.51: Create a new User for MDS_ServiceAccounts

69: Create a new user for MDS_ServiceAccounts

Fig 5.52: Create a new User for MDS_ServiceAccounts
Fig 5.52: Create a new User for MDS_ServiceAccounts

70. Click OK.

71. In Master Data Services Configuration Manager – Web Configuration, click on Enable Integration with Data Quality Services as in Fig 5.47 again.

Fig 5.53: Master Data Services Configuration Manager - Enable Integration with Data Quality Services
Fig 5.53: Master Data Services Configuration Manager - Enable Integration with Data Quality Services

Master Data Services is now successfully configured!

72. Move on to Build your own SQL 2012 Demo Machine – Part 5 – SharePoint & PowerPivot

Read More