Anxiously you have waited for this day. The day you could finally get your hands on the latest version of the best database platform available. Today is that day. SQL Server 2017 is available and it runs on Windows, Linux and Docker. Find more info here: https://www.microsoft.com/en-us/sql-server/sql-server-2017. What changed? So many things it is hard to mention them all. Have a look at the Ignite sessions to get a feel for what happened.
Hi all,
A month ago at the Connect event in New York Microsoft did some anouncements on SQL Server vNext and Azure Data Services.
It’s good to see the traction it’s getting!
I work with a lot of partners on implementing Microsoft stuff and the partners are very keen in getting the latest info on new capabilities.
Also they enlist in new online and in classroom courses on the new stuff. So let’s take a quick look:
And last but not least: connectivity to all top programming languages!
A recurring theme with customers and partners is Power BI data refresh. More specifically, there is some confusion on what refresh scenario requires a Pro version and what can be done with the Free version of Power BI. I made the diagram below to help explain this. It shows the refresh scenarios against data storage solutions, such as SQL Azure, SQL in a virtual machine in Azure or SQL server on premises. I used these as examples, there are other options as well. I think the overall time carries over to other data storage solutions. The diagram shows the refresh that can be done using a Power BI Free account as orange and the refresh scenarios that need Power BI Pro as green lines. As shown in the diagram, if you want to refresh against on-premises sources or a database running in a VM in Azure you will need a gateway and Power BI Pro. This applies not only to the creator of the report and schedule but also to every consumer. If you use PAAS solutions for data storage in Azure such as SQL Azure, it becomes a bit more difficult and it is really dependent on the type of refresh required. If you need a refresh cycle higher than once a day (either max 8 times per 24 hours or live) you will need Power BI Pro. If you just want to refresh against such as SQL Azure and once a day is enough you can do that using Power BI Free. Again, the license requirement carries over from author to viewer; if the author of the report requires Pro, then the viewers also need Pro.
Hope this helps. If you have any questions or feedback, please comment below!
Als iemand een paar jaar terug tegen mij had gezegd dat Microsoft Multi platform zou gaan en dat ook SQL Server op Linux zou uitkomen, dan had ik hem voor gek verklaard. Echter sinds de komst van Satya Nadella als CEO en mensen als Scott Guthrie (CVP voor Cloud & Enterprise) waait er een frisse wind door Microsoft!
En als Microsoft employee voelde ik natuurlijk al wat aankomen, maar het is toch altijd weer top om het nieuws dan nu in de buitenwereld te zien landen: Gister heeft Microsoft de Linux versie van SQL Server geannonceerd in preview. Dit betreft voorlopig alleen het RDBMS en niet de overige SQL tools als SSRS en SSAS, maar een mooie start om nu ook op Linux de (volgens Gartner) de leading database te kunnen runnen!
Ik zou het leuk vinden als jullie na het testen van deze versie op het blog zouden willen reageren met de eerste indruk van de performance van deze preview versie.
De blogpost van Scott over de annoncering: http://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/
SQL groeten,
Harry
This post is #14 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.
In this series:
Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
Post #10 – Installing SQL Server
Post #11 – Installing SharePoint Server
Post #12 – Installing PowerPivot for SharePoint
Post #13 – Configuring PowerPivot for SharePoint
Wow. This has been a long and wild ride. But you and I made it together. We now have the full recipe to automatically configure a Microsoft BI demo machine with PowerShell. Of course there is more to be done, such as configuring other Service Accounts and deploying demo content; this script however saves me a lot of time every time I need to stand up a new demo machine.
You can download the script on Github. Please note (again) that the code is provided as-is and you should use it at your own risk. It is probably still buggy but should give you a good starting point to adapt it to your needs.
I enjoyed the ride with you; hope I made your life a bit easier of the course of this series. Enjoy!
This post is #13 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.
In this series so far:
Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
Post #10 – Installing SQL Server
Post #11 – Installing SharePoint Server
Post #12 – Installing PowerPivot for SharePoint
Now that PowerPivot for SharePoint has been installed, we need to configure it. I split the configuration into two parts since we need a reboot in between and used MSDN for reference: http://msdn.microsoft.com/en-us/library/hh230903.aspx.
Step A: configuring SharePoint and deploying PowerPivot features
In Post #11 we talked about installing SharePoint, but the actual SharePoint provisioning was not done then. We will do it here in one go with installing PowerPivot features.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
Function ConfigurePowerPivot { Param( [Parameter(Mandatory=$true,HelpMessage="Passphrase required")] [ValidateNotNullOrEmpty()] $passphrase, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] $Password ) Write-Host "Step 8: Configure PowerPivot" try { #Load Configure PowerPivot ps1 $scriptPath = Split-Path -parent $global:script . ('C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\ConfigurePowerPivot.ps1') #Create a user for SharePoint DB connection #if required, remove the ad user Get-ADUser -Filter {Identity -eq '$global:spAccount'} | Remove-ADUser CreateServiceAccount -AccountName $global:spAccount -DisplayName "SharePoint Farm account" -Description "Account for SharePoint Farm" -Path $global:path -Password $Password $spAccountFQ = $global:domainpart+"\"+$global:spAccount $pwd = convertto-securestring $Password -asplaintext -force & "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN\psconfig.exe" -cmd configdb -create -server $global:HostName -database 'SharePoint_Config' -user $spAccountFQ -password $Password -passphrase $passphrase -admincontentdatabase 'SharePoint_Admin' -cmd helpcollections -installall -cmd secureresources -cmd services -install -cmd installfeatures -cmd adminvs -provision -port 2000 -windowsauthprovider onlyusentlm -cmd applicationcontent -install -cmd quiet Add-PSSnapin Microsoft.SharePoint.PowerShell Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\powerpivotfarmsolution.wsp' Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\PowerPivotFarm14Solution.wsp' Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\powerpivotwebapplicationsolution.wsp' DeployFarmSolution $false DeployWebAppSolutionToCentralAdmin $false Install-SPFeature -path PowerPivotFarm -Force Install-SPFeature -path PowerPivotFarm -Force -CompatibilityLevel 14 Install-SPFeature -path PowerPivotCA -Force InstallSiteCollectionFeatures Write-Host "PowerPivot Part 1 Configured. Computer needs to be restarted before PowerPivot configuration can continue." -ForegroundColor Green if ($global:DoAllTasks) { Set-Restart-AndResume $global:script "9" } } catch { Write-Host "Failed to configure PowerPivot. Error: $_.Exception.Message" -ForegroundColor Red } } |
Step B: updating farm credentials and starting service applications
After the PowerPivot features have been deployed we need to configure Service Applications to get PowerPivot to work.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
Function ConfigurePowerPivotPart2 { Param( [Parameter(Mandatory=$true,HelpMessage="Passphrase required")] [ValidateNotNullOrEmpty()] $passphrase, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] $Password ) try { #Load Configure PowerPivot ps1 $scriptPath = Split-Path -parent $global:script . ('C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\ConfigurePowerPivot.ps1') Add-PSSnapin Microsoft.SharePoint.PowerShell Write-Host "DEBUG: updating Farm Credentials" $spAccountFQ = $global:domainpart+"\"+$global:spAccount stsadm.exe -o updatefarmcredentials -userlogin $spAccountFQ -password $Password Write-Host "DEBUG: New-PowerpivotSystemServiceInstance" New-PowerPivotSystemServiceInstance -Provision:$true Write-Host "DEBUG: New-PowerPivotServiceApplication" New-PowerPivotServiceApplication -ServiceApplicationName 'PowerPivot Service Application' -DatabaseServerName $global:HostName -DatabaseName 'PowerPivotServiceApplication' -AddToDefaultProxyGroup:$true Write-Host "DEBUG: Set-PowerPivotSystemService" Set-PowerPivotSystemService -Confirm:$false Write-Host "DEBUG: Creating user DefAppPool" $appAccountName = "DefAppPool" $appAccountNameFQ = $global:domainpart+"\"+$appAccountName CreateServiceAccount -AccountName $appAccountName -DisplayName "Default Application Pool" -Description "Service Account for Default Application Pool" -Path $global:path -Password $Password Write-Host "DEBUG: CreateWebApplication" CreateWebApplication 'SharePoint - 80' $global:HostName 'Default Application Pool' $appAccountNameFQ $pwd $global:HostName 'DefaultWebApplication' Write-Host "DEBUG: DeployWebAppSolution" DeployWebAppSolution $global:httpHostName 2047 $false Write-Host "DEBUG: New-SPSite" New-SPSite -Url $global:httpHostName -OwnerEmail 'me@example.com' -OwnerAlias $global:currentUserName -Template 'PowerPivot#0' -Name 'PowerPivot Site' Write-Host "DEBUG: EnableSiteFeatures" EnableSiteFeatures $global:httpHostName $true Write-Host "DEBUG: StartService SPWindowsTokenServiceInstance" StartService "Microsoft.SharePoint.Administration.Claims.SPWindowsTokenServiceInstance" Write-Host "DEBUG: StartSecureStoreService" StartSecureStoreService Write-Host "DEBUG: CreateSecureStoreApplicationService" CreateSecureStoreApplicationService $global:HostName 'Secure Store Service' Write-Host "DEBUG: CreateSecureStoreApplicationServiceProxy" CreateSecureStoreApplicationServiceProxy 'Secure Store Service' 'Secure Store Proxy' Write-Host "DEBUG: UpdateSecureStoreMasterKey" UpdateSecureStoreMasterKey 'Secure Store Proxy' $passphrase Write-Host "DEBUG: CreateUnattendedAccountForDataRefresh" CreateUnattendedAccountForDataRefresh $global:httpHostName 'PowerPivotUnattendedAccount' 'PowerPivot Unattended Account for Data Refresh' $spAccountFQ $pwd Write-Host "DEBUG: StartService ExcelServerWebServiceInstance" StartService "Microsoft.Office.Excel.Server.MossHost.ExcelServerWebServiceInstance" Write-Host "DEBUG: New-SPExcelServiceApplication" New-SPExcelServiceApplication -name 'ExcelServiceApp1' -Default -ApplicationPool 'SharePoint Web Services System' | Get-SPExcelServiceApplication | Set-SPExcelServiceApplication | iisreset Set-SPExcelFileLocation -ExternalDataAllowed 2 -WorkbookSizeMax 200 -WarnOnDataRefresh:$false -ExcelServiceApplication 'ExcelServiceApp1' -identity 'http://' Write-Host "DEBUG: AddExcelBIServer" AddExcelBIServer Write-Host "DEBUG: SetECSUsageTracker" SetECSUsageTracker 'ExcelServiceApp1' Write-Host "PowerPivot Configured" -ForegroundColor Green if ($global:DoAllTasks) { Set-Restart-AndResume $global:script "10" } } catch { Write-Host "Failed to configure PowerPivot. Error: $_.Exception.Message" -ForegroundColor Red } } |
Now we have seen all the steps required to build a Microsoft BI demo machine! The next post will serve as a wrap up and present a download for the full script.
This post is #12 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.
In this series so far:
Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
Post #10 – Installing SQL Server
Post #11 – Installing SharePoint Server
Ok, now that both SQL Server and SharePoint Server are installed, we just need to set up PowerPivot for SharePoint and configure it. Easy huh? Well, it turns out it is pretty difficult to get it right. Installation is not difficult (this post) but the configuration is harder (the next post). Here is how to install PowerPivot. I used MSDN for the info: http://msdn.microsoft.com/en-us/library/ee210645.aspx.
Installing PowerPivot involves mounting the SQL Server Installation Media and calling the setup with the right parameters.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Function InstallPowerPivot { Param( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] $Password ) Write-Log -Verbose "Step 7: Install PowerPivot" #MOUNT SQL ISO $mountresult = Mount-DiskImage -ImagePath $global:pathToSQLISO -PassThru $driveLetter = ($mountresult | Get-Volume).DriveLetter $setupFile = $driveLetter+":\setup.exe" #Remove Service Account if it already existed Get-ADServiceAccount -Filter {Name -eq 'PP'} | Remove-ADServiceAccount $ppAccountName = "PP" $ppAccountNameFQ = $global:domainpart+"\"+$ppAccountName CreateServiceAccount -AccountName $ppAccountName -DisplayName "PowerPivot" -Description "Service Account for PowerPivot for SharePoint" -Path $global:path -Password $Password #do PP installation #trying with plain text pwd in call $process = Start-Process -NoNewWindow -Wait $setupFile -ArgumentList "/ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS /Q /INSTANCENAME=POWERPIVOT /ERRORREPORTING=1 /SQMREPORTING=1 /ASSVCACCOUNT=$ppAccountNameFQ /ASSVCPASSWORD=$Password /ASSYSADMINACCOUNTS=$global:currentUserName /ROLE=SPI_AS_ExistingFarm" #SPI_AS_ExistingFarm #dismount Dismount-DiskImage -ImagePath $global:pathToSQLISO Write-Log -Verbose "If above an error is shown please check out C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\Summary.txt" Write-Log -Verbose "PowerPivot Installed" if ($global:DoAllTasks) { Set-Restart-AndResume $global:script "9" } } |
Next time: configuring PowerPivot.
This post is #11 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.
In this series so far:
Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
Post #10 – Installing SQL Server
Wow, so the last post was pretty intense, wasn’t it? I think we are ready for the next one: installing SharePoint. To build this script I used the following sources: http://social.technet.microsoft.com/wiki/contents/articles/14582.sharepoint-2013-install-prerequisites-offline-or-manually-on-windows-server-2012-a-comprehensive-guide.aspx#Installing_the_Roles_and_Features_for_SharePoint_2013_on_Windows_Server_2012_Offline_with_PowerShell and http://blogs.msdn.com/b/uksharepoint/archive/2013/03/18/scripted-installation-of-sharepoint-2013-and-office-web-apps-server-from-the-field-part-2.aspx.
Since this is again a quite lengthly script we will split it up in steps.
Step A: enabling IIS and other features
This step enables a whole load of features on Windows that are required by SharePoint, including IIS. If a restart is required, the script will reboot after the setup of the features. Some times your machine might reboot more than once to complete the setup of all these features.
|
1 2 3 4 5 6 7 8 9 10 |
Write-Log -Verbose "Step 6: Install SharePoint" Import-Module ServerManager #Add .Net 4.5 features Add-WindowsFeature NET-WCF-HTTP-Activation45,NET-WCF-TCP-Activation45,NET-WCF-Pipe-Activation45 #Add the rest of the needed features for IIS role $result = Add-WindowsFeature Net-Framework-Features,Web-Server,Web-WebServer,Web-Common-Http,Web-Static-Content,Web-Default-Doc,Web-Dir-Browsing,Web-Http-Errors,Web-App-Dev,Web-Asp-Net,Web-Net-Ext,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Health,Web-Http-Logging,Web-Log-Libraries,Web-Request-Monitor,Web-Http-Tracing,Web-Security,Web-Basic-Auth,Web-Windows-Auth,Web-Filtering,Web-Digest-Auth,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Mgmt-Tools,Web-Mgmt-Console,Web-Mgmt-Compat,Web-Metabase,Application-Server,AS-Web-Support,AS-TCP-Port-Sharing,AS-WAS-Support, AS-HTTP-Activation,AS-TCP-Activation,AS-Named-Pipes,AS-Net-Framework,WAS,WAS-Process-Model,WAS-NET-Environment,WAS-Config-APIs,Web-Lgcy-Scripting,Windows-Identity-Foundation,Server-Media-Foundation,Xps-Viewer if($result.RestartNeeded -eq "Yes") { Set-Restart-AndResume $global:script "7" } |
Step B: Installing SharePoint Prerequisites
SharePoint itself has a number of prerequisites; in this still we will install them all.
|
1 2 3 4 5 6 |
#Mount SharePoint iso $mountresult = Mount-DiskImage -ImagePath $global:pathToSharePointISO -PassThru $driveLetter = ($mountresult | Get-Volume).DriveLetter Write-Log -Verbose "Installing SharePoint PreReqs...." $setupFile = $driveLetter+":\prerequisiteinstaller.exe" $process = Start-Process $setupFile -PassThru -Wait -ArgumentList "/unattended /SQLNCli:$global:SharePoint2013Path\PrerequisiteInstallerFiles\sqlncli.msi /IDFX:$global:SharePoint2013Path\PrerequisiteInstallerFiles\Windows6.1-KB974405-x64.msu /IDFX11:$global:SharePoint2013Path\PrerequisiteInstallerFiles\MicrosoftIdentityExtensions-64.msi /Sync:$global:SharePoint2013Path\PrerequisiteInstallerFiles\Synchronization.msi /AppFabric:$global:SharePoint2013Path\PrerequisiteInstallerFiles\WindowsServerAppFabricSetup_x64.exe /KB2671763:$global:SharePoint2013Path\PrerequisiteInstallerFiles\AppFabric1.1-RTM-KB2671763-x64-ENU.exe /MSIPCClient:$global:SharePoint2013Path\PrerequisiteInstallerFiles\setup_msipc_x64.msi /WCFDataServices:$global:SharePoint2013Path\PrerequisiteInstallerFiles\WcfDataServices.exe" |
Step C: Installing SharePoint
SharePoint is installed in this step from the ISO that the script mounts.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
if($process.ExitCode -eq 0) { #install sharepoint Write-Log -Verbose "Installing SharePoint...." $path = $driveLetter+":\Setup.exe" $sharePointInstallProcess = Start-Process -Wait -PassThru $path -ArgumentList "/config $global:SharePoint2013Path\FarmSilentConfig.xml" switch($sharePointInstallProcess.ExitCode) { 0 { Write-Log -Verbose "SharePoint successfully installed" Write-Log -Verbose "SharePoint Installed" if ($global:DoAllTasks) { Set-Restart-AndResume $global:script "8" } } default{ Write-Log -Verbose "An error has occured in installing SharePoint. Code: " $sharePointInstallProcess.ExitCode } } } else { if($process.ExitCode -eq 3010) { Write-Log -Verbose "SharePoint prereqs install requires a reboot" } else { Write-Log -Verbose "SharePoint prereqs not succesfully installed, please investigate. Code: " $process.ExitCode } } |
Step D: Cleaning up
This step simply unmounts the SharePoint installation media.
|
1 |
Dismount-DiskImage -ImagePath $global:pathToSharePointISO |
Pff, are we done yet? No! Next up: Installing PowerPivot for SharePoint.
This post is #10 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.
In this series so far:
Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
In this tenth post we will get to the heart of it: installing SQL Server. After this script completes we will have SQL Agent, SQL Database, Analysis Services (multidimensional and tabular), Integration Services, Data Quality Services, Master Data Services, FullText search, Filestreaming, Development and Management tools and Reporting Services (both native and SharePoint integrated mode) installed. This script will be lengthier than earlier scripts simply because there is a lot more to do. Info I used to create this script: http://msdn.microsoft.com/en-us/library/ms144259.aspx. Here we go.
Step A: creating new service accounts
In this step we first remove any service account that starts with ‘SQL Server’ and then create new serviceaccounts using the configured password.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
#Remove Service Accounts for SQL in case they already exist Get-ADServiceAccount -Filter {DisplayName -like 'SQL Server*'} | Remove-ADServiceAccount #Create accounts $sqlagentAccountName = "SQLAgent" $ssasAccountName = "SSAS" $sqldbAccountName = "SQLDB" $ssisAccountName = "SSIS" $ssrsAccountName = "SSRS" $sqlagentAccountNameFQ = $global:domainpart+"\"+$sqlagentAccountName $ssasAccountNameFQ = $global:domainpart+"\"+$ssasAccountName $sqldbAccountNameFQ = $global:domainpart+"\"+$sqldbAccountName $ssisAccountNameFQ = $global:domainpart+"\"+$ssisAccountName $ssrsAccountNameFQ = $global:domainpart+"\"+$ssrsAccountName CreateServiceAccount -AccountName $sqlagentAccountName -DisplayName "SQL Server Agent" -Description "Service Account for SQL Server Agent" -Path $global:path -Password $password CreateServiceAccount -AccountName $ssasAccountName -DisplayName "SQL Server Analysis Services" -Description "Service Account for SQL Server Analysis Services" -Path $global:path -Password $password CreateServiceAccount -AccountName $sqldbAccountName -DisplayName "SQL Server Database Engine" -Description "Service Account for SQL Server Database Engine" -Path $global:path -Password $password CreateServiceAccount -AccountName $ssisAccountName -DisplayName "SQL Server Integration Services" -Description "Service Account for SQL Server Integration Services" -Path $global:path -Password $password CreateServiceAccount -AccountName $ssrsAccountName -DisplayName "SQL Server Reporting Services" -Description "Service Account for SQL Server Reporting Services" -Path $global:path -Password $password |
Step B: making sure required features are installed
In this step we make sure .NET 3.5 feature is enabled in Windows.
|
1 2 |
#Make sure the .Net 3.5 feature is enabled Install-WindowsFeature –name NET-Framework-Core |
Step C: Mounting the ISO and set up the parameters
We can now mount the SQL Server installation ISO and set up parameters for the setup to run with. We will do two phases (passes) since we cannot install both SSRS Native and SharePoint integrated mode and SSAS Multidimensional and Tabular mode in one go.
|
1 2 3 4 5 6 7 8 |
#Mount and Install SQL $mountresult = Mount-DiskImage -ImagePath $global:pathToSQLISO -PassThru $driveLetter = ($mountresult | Get-Volume).DriveLetter $setupFile = $driveLetter+":\setup.exe" #Run first pass of SQL Install: SQLDB,DQ,FullText,FileStreaming,AS,RSNative,DataQualityCLient,IS,MDS,Tools $featuresPass1 = "SQL,AS,RS,DQC,IS,MDS,TOOLS" $featuresPass2 = "AS,RS_SHP,RS_SHPWFE" |
Step D: do the actual installations
Now we execute SQL Server setup with the right argument list. This configures instance names, service accounts and passwords and the features to install. The install will be silent.
|
1 2 3 4 |
Start-Process $setupFile -NoNewWindow -Wait -ArgumentList "/ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS /Q /INSTANCENAME=MSSQLSERVER /ERRORREPORTING=1 /SQMREPORTING=1 /AGTSVCACCOUNT=$sqlagentAccountNameFQ /AGTSVCPASSWORD=$Password /ASSVCACCOUNT=$ssasAccountNameFQ /ASSVCPASSWORD=$Password /ASSERVERMODE=MULTIDIMENSIONAL /ASSYSADMINACCOUNTS=$global:currentUserName /SQLSVCACCOUNT=$sqldbAccountNameFQ /SQLSVCPASSWORD=$Password /SQLSYSADMINACCOUNTS=$global:currentUserName /FILESTREAMLEVEL=1 /ISSVCACCOUNT=$ssisAccountNameFQ /ISSVCPASSWORD=$Password /RSINSTALLMODE=DefaultNativeMode /RSSVCACCOUNT=$ssrsAccountNameFQ /RSSVCPASSWORD=$Password /FEATURES=$featuresPass1" Write-Log -Verbose "SQL Server Installation Pass 1 completed: SQL, AS Multidimensional, RS Native, Data QUality Client, DQS IS, MDS, TOOLS, FullText, FileStreaming" Start-Process $setupFile -NoNewWindow -Wait -ArgumentList "/ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS /Q /INSTANCENAME=TABULAR /ERRORREPORTING=1 /SQMREPORTING=1 /ASSVCACCOUNT=$ssasAccountNameFQ /ASSVCPASSWORD=$Password /ASSERVERMODE=TABULAR /ASSYSADMINACCOUNTS=$global:currentUserName /FEATURES=$featuresPass2" Write-Log -Verbose "SQL Server Installation Pass 2 completed: RS SharePoint, AS Tabular" |
Step E: wrapping up
In this step we unmount the SQL Server installation media and write to the log.
|
1 2 3 4 5 |
Dismount-DiskImage -ImagePath $global:pathToSQLISO Write-Log -Verbose "SQL Server Installed" if ($global:DoAllTasks) { Set-Restart-AndResume $global:script "7" } |
Next step: installing SharePoint
This post is #9 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.
In this series so far:
Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Although in the last step we configured a very permissive password policy we need a bit of security, so that is why I opted to install System Center Endpoint Protection. Now, in Azure you can also have extensions for security (both with Microsoft and 3rd party security products) so probably you will never install System Center Endpoint protection yourself, but for the sake of reference, here is how to install it using PowerShell.
|
1 2 3 4 5 6 7 8 9 |
Function InstallSystemCenterEndpointProtection { Write-Log -Verbose "Step 4: Install System Center Endpoint Protection" Start-Process .\Resources\SystemCenterEndpointProtection\scepinstall.exe -Wait -ArgumentList "/s /q" #-NoNewWindow Write-Log -Verbose "System Center Endpoint Protection Installed" if ($global:DoAllTasks) { Set-Restart-AndResume $global:script "6" } } |
Next step: installing SQL Server