Samples/InstallSql.ps1

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
# Configuration to install Sql server database engine and management tools.
#
# A. Prepare a local self signed certificate with the following steps:
# 1. Get MakeCert.exe if you don't have. ( MakeCert.exe is shipped with windows SDK: http://msdn.microsoft.com/en-us/windows/desktop/bg162891.aspx)
# 2. Open console with Administrator elevation, run the following:
# makecert -r -pe -n "CN=DSCDemo" -sky exchange -ss my -sr localMachine

# B. Prepare software and run the configuration.
# 1. On the machine, create a folder as Software
# 2. On the machine, please copy Windows Server 2012 R2 source\sxs to C:\Software\sxs
# 3. copy sql software to C:\Software\sql
# 4. copy xSqlPs to $env:ProgramFiles\WindowsPowershell\Modules
# 5. Copy this file (InstallSql.ps1) to c:\demo
# 6. in powershell with administrator elevation, go to c:\demo, run .\InstallSql.ps1


$certSubject = "CN=DSCDemo"
$keysFolder = Join-Path $env:SystemDrive -ChildPath "Keys"
$cert = dir Cert:\LocalMachine\My | ? { $_.Subject -eq $certSubject }
if (! (Test-Path $keysFolder ))
{
    md $keysFolder | Out-Null
}
$certPath = Export-Certificate -Cert $cert -FilePath (Join-Path $keysFolder -ChildPath "Dscdemo.cer")


$ConfigData=
@{
    AllNodes = @(

       @{
           NodeName = "localhost"
           CertificateFile = $certPath
           Thumbprint = $cert.Thumbprint
        }
    )
 }

Configuration InstallSql
{
    param(
        [Parameter(Mandatory=$true)]
        [ValidateNotNullorEmpty()]
        [PsCredential] $credential
        )

    Import-DscResource -Module mlSqlPs

   Node $AllNodes.NodeName
   {
    
    # Install SQL Server
    WindowsFeature installdotNet35
    {            
        Ensure = "Present"
        Name = "Net-Framework-Core"
        Source = "c:\software\sxs"
    }
        
    xSqlServerInstall installSqlServer
    {
        InstanceName = "PowerPivot"

        SourcePath = "c:\software\sql"
            
        Features= "SQLEngine,SSMS"

        SqlAdministratorCredential = $credential
       
        SqlTempDBDir = "E:\mssql\MSSQL.PowerPivot\Data"
        
        SqlUserDBDir = "D:\mssql\MSSQL.PowerPivot\Data"
        
        SqlUserDBLogDir = "D:\mssql\MSSQL.PowerPivot\Data"
        
        SqlBackupDir = "F:\mssql\MSSQL.PowerPivot\Backup"
        
        DependsOn = "[WindowsFeature]installdotNet35"
    }

    LocalConfigurationManager 
    { 
        CertificateId = $node.Thumbprint 
    } 
 }    
}

InstallSql -ConfigurationData $ConfigData -OutputPath .\Mof -credential (Get-Credential -UserName "sa" -Message "Enter password for SqlAdministrator sa")

Set-DscLocalConfigurationManager .\Mof

Start-DscConfiguration -Path .\Mof -ComputerName localhost -Wait -Verbose