{"id":1811,"date":"2014-02-13T13:27:00","date_gmt":"2014-02-13T13:27:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/powershell\/2014\/02\/13\/configuring-a-sql-high-availability-group-with-dsc\/"},"modified":"2024-02-22T12:50:24","modified_gmt":"2024-02-22T20:50:24","slug":"configuring-a-sql-high-availability-group-with-dsc","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/powershell\/configuring-a-sql-high-availability-group-with-dsc\/","title":{"rendered":"Configuring a SQL High Availability Group with DSC"},"content":{"rendered":"
\n

Let’s use DSC to configure something complicated!\u00a0 In past blogs, we\u2019ve shown you how to use Windows PowerShell Desired State Configuration (DSC<\/strong>) to configure relatively simple systems.\u00a0 However, the technologies you deal with on a day to day basis can sometimes become complicated.\u00a0 Don\u2019t worry, DSC can still help simplify configuration.\u00a0 Let\u2019s use SQL\u00a0AlwaysOn\u00a0Availability Group (AG<\/strong>)<\/a> as a example. SQL AG is a new SQL feature that enables replication on top of Windows Server Failover Clustering. While the feature is cool, configuring the environment is quite complex. It involves many steps across multiple machines. Some steps in one machine might depend on progress or status of others.<\/p>\n

\n

In this blog post, we will demonstrate<\/strong> using DSC to configure a SQL AG<\/strong>. When using the provided example, one PowerShell command will deploy a SQL AG on Virtual Machines (VMs).<\/p>\n

Environment<\/h2>\n

Using the DSC configuration scripts described in this blog you can fully deploy and configure the following environment:<\/p>\n

\"\"<\/a><\/p>\n

Configuration Overview<\/h2>\n

To deploy the environment described above in a virtual environment using DSC a configuration is generated for each guest server described above and the VM host machine. All of these configurations are coordinated by a single PowerShell script (Deploy-Demo.ps1). A description of what each of the configuration scripts does is below. A zip (Dsc-SqlDemo.zip) containing all of the configuration files is attached to this blog (see the bottom of the blog) and should be downloaded before you read on so that you can follow along while looking at the associated scripts.<\/p>\n

\n

Configuring the Host and VMs<\/h2>\n

\n

First, Deploy-Demo.ps1 runs Dsc-SqlDemo\\ConfigSqlDemo.ps1. <\/strong>\u00a0This configures the host machine by doing the following:<\/p>\n

\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that a VM Switch for an internal network is present (in the demo, subnet of 192.168.100.*)<\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that a local user called vmuser is present, so that VMs can access data in host<\/p>\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that a net share (c:\\SqlDemo\\Sql12Sp1) is present.<\/p>\n

4.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that three VMs are created in the correct state by:<\/p>\n

o\u00a0\u00a0 <\/span><\/span>Ensuring that a DSC Configuration, DSC Resources, and other files are copied to the VHD image.<\/p>\n

o\u00a0\u00a0 <\/span><\/span>Ensuring that the VMs are started from the VHDs.<\/p>\n

\n

Once the host machine is configured, we have three VMs running.\u00a0 Each of these VMs has a configuration that has been bootstrapped into it.\u00a0 Because of the way we bootstrap the VMs, they will configure themselves after startup, using the .mof we have injected into them.<\/p>\n

\n

Stay tuned for a blog post about the bootstrapping procedure.<\/p>\n

\n

Configuring the Primary Domain Controller – pdc<\/h2>\n

\n

The .mof file injected into the Primary Domain Controller (pdc) VM was generated from the configuration in Dsc-SqlDemo\\Scenarios\\nodes.ps1 <\/strong>from the node statement \u00e8<\/span> Node $AllNodes.Where{$_.Role -eq “PrimaryDomainController<\/strong>” }.NodeName<\/p>\n

\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure the VM has a static IPAddress<\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure necessary WindowsFeatures are present<\/p>\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that a Domain Forest is created<\/p>\n

4.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Sets\u00a0 up a network share folder that will be used in the SQL replication process<\/p>\n

\n

Setting up the first SQL Server- Sql01<\/h2>\n

\n

The .mof file injected into the first SQL Server (Sql01) VM was generated from the configuration in Dsc-SqlDemo\\Scenarios\\nodes.ps1 <\/strong>from the node statement \u00e8<\/span> Node $AllNodes.Where{$_.Role -eq “PrimarySqlClusterNode<\/strong>” }.NodeName<\/p>\n

\u00a0<\/strong><\/p>\n

\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensures that the machines IPAddress is correctly set<\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensures that necessary WindowsFeatures are present<\/p>\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/strong>WaitFor Primary Domain Controller to have created the AD Domain <\/strong><\/p>\n

4.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that the machine is joined to the Domain<\/p>\n

5.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that .Net 3.5 and SQL Server 2012 SP1 are installed<\/p>\n

6.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensures that Firewalls are configured such that Sqlbrowser.exe and SqlServr.exe are accessible in the private network.<\/p>\n

7.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that a Windows Cluster is created and that Sql01 is added to the cluster<\/p>\n

8.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that the SQL Server for High Availability (HA) service is enabled<\/p>\n

9.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that there is an Endpoint for the HA<\/p>\n

10.\u00a0\u00a0 <\/span>Ensure that the SQL HA group for databases is created (in the demo, TestDB)<\/p>\n

\n

\n

Setting up the second SQL Server – Sql02<\/h2>\n

\n

The .mof file injected into the second SQL Server (Sql02) VM was generated from the configuration in Dsc-SqlDemo\\Scenarios\\nodes.ps1 <\/strong>from the node statement \u00e8<\/span> Node $AllNodes.Where{$_.Role -eq “ReplicaSqlClusterNode<\/strong>” }.NodeName<\/p>\n

\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensures that the machines IPAddress is correctly set<\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensures that necessary WindowsFeatures are present<\/p>\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/strong>WaitFor Primary Domain Controller to have created the AD Domain <\/strong><\/p>\n

4.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that the machine is joined to the Domain<\/p>\n

5.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that .Net 3.5 and SQL Server 2012 SP1 are installed<\/p>\n

6.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensures that Firewalls are configured such that Sqlbrowser.exe and SqlServr.exe are accessible in the private network.<\/p>\n

7.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/strong>WaitFor the first SQL node to have created the windows cluster <\/strong><\/p>\n

8.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that Sql02 is added to the cluster<\/p>\n

9.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Ensure that the SQL Server for High Availability (HA) service is enabled<\/p>\n

10.\u00a0\u00a0 <\/span>Ensure that there is an Endpoint for the HA<\/p>\n

11.\u00a0\u00a0 <\/span><\/strong>WaitFor the first SQL node to have created the HA group<\/strong><\/p>\n

12.\u00a0\u00a0 <\/span>Ensure that sql02 is joined to the HA group.<\/p>\n

\n

Deploy the environment<\/h2>\n

Now that you have an understanding of the environment and what the DSC scripts do, let\u2019s go ahead and deploy the environment using the scripts. Note there is quite a bit of preparation to complete before the scripts can be executed so please be patient.<\/p>\n

Requirements<\/h2>\n

Hardware<\/h3>\n

\n

To simulate a SQL AG, we need a decent machine that is capable of running Windows Server 2012 R2 and Hyper-V (64-bit) with at least 16GB of RAM and around 100GB of free disk space. Because this is a demo, we also recommend that you not store important items on the machine, in case it is cleaned up.<\/p>\n

\n

Software<\/h3>\n

\n

The following software are needed to perform the steps in the demo.<\/p>\n

\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>An evaluation version of Windows Server 2012 R2 Datacenter (both ISO and VHD). A download can be found here<\/a>.\u00a0 Note: We need both the VHD and the ISO because SQL Server requires .Net 3.5, which is not available in the VHD. Fortunately, in the expanded ISO image, there is a folder named Sources\\sxs, that includes all .Net 3.5 files.<\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>An evaluation version of SQL Server 2012 SP1 (ISO).\u00a0 A download can be found here<\/a>.<\/p>\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>The following DSC resources:<\/p>\n

a.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>User (Ships in Windows Server 2012)<\/p>\n

b.\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Windows Feature (Ships in Windows Server 2012)<\/p>\n

c.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>xComputerManagement (Download here<\/a>)<\/p>\n

d.\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>xNetworking (Download here<\/a>)<\/p>\n

e.\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>xHyper-V (Download here<\/a>)<\/p>\n

f.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>xActiveDirectory (Download here<\/a>)<\/p>\n

g.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>xFailOverCluster (Download here<\/a>)<\/p>\n

h.\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>xSqlps (Download here<\/a>)<\/p>\n

i.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>xSmbShare (Download here<\/a>)<\/p>\n

\u00a0<\/span><\/p>\n

\u00a0<\/span><\/p>\n

Certificate<\/h3>\n

\u00a0<\/span><\/p>\n

Setting up domain controllers or SQL servers requires a few credentials. \u00a0To keep these credentials secure, DSC encrypts them before placing them into the plain text of the .mof files.\u00a0 For details on this process, check out this <\/span>blog<\/span><\/a>. To secure credentials, DSC uses a certificate\u2019s public key to encrypt the credentials and the private key to decrypt the credentials on the target machine that is being configured. To ensure that this demo works correctly, we need to ensure that the host and the target machines have the appropriate certificates. <\/span><\/p>\n

\u00a0<\/span><\/p>\n

To do this, we first create a self-signed certificate on the host machine, then copy it with private key<\/strong> to the target machines.\u00a0 We then install the certificate to the target\u2019s local machine certificate store. Since private key should be kept secret, it is important to clean them up as soon as possible <\/strong>(instructions can be found below).\u00a0 \u00a0Again, with this, please ensure you do NOT run the demo in production or on machines that require security by default.\u00a0 <\/span><\/p>\n

\u00a0<\/span><\/p>\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Steps to setup certificate on the host machine:<\/span><\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Get MakeCert.exe if you don\u2019t have. (It is shipped with Windows SDK, a download can be found <\/span>here<\/span><\/a>). <\/span><\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Create a certificate with CN=DSCDemo<\/strong>. To do this, open a PowerShell console with Administrator elevation, cd to place that can see MakeCert.exe, and run the following command (notice, for security reasons, I make the cert expire as soon as possible, please adjust the highlighted date<\/span> as needed).<\/span><\/p>\n

\u00a0<\/span><\/p>\n

makecert -r -pe -n “CN=DSCDemo” -sky exchange -ss my -sr localMachine<\/span> \u2013e 02\/15\/2014<\/span><\/span><\/p>\n

\u00a0<\/span><\/p>\n

The command line above will create a self-signed certificate on localhost certificate store (cert:\\localMachine\\My, with Subject = \u201cCN=DSCDemo<\/strong>\u201d). \u00a0Remember the subject, we will need it very soon. In my example, the UI in the certificate store looks like the following in (Certificates(Local Computer)\\Personal\\Certificates<\/strong>)<\/span><\/p>\n

\n

\"\"<\/a><\/p>\n

\u00a0<\/span><\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Create a folder to hold the keys for the demo. In my example, I created C:\\keys<\/span><\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Public key.<\/span><\/strong> Export the public key of the certificate. \u00a0You can do this manually, or do it with the following PS script. In my example, I saved the public key as: C:\\keys\\Dscdemo.cer<\/strong>\u201d<\/span><\/p>\n

\u00a0<\/span><\/p>\n

$certSubject<\/span> = <\/span>“CN=DSCDemo”<\/span><\/p>\n

$keysFolder<\/span> = Join-Path <\/span>$env<\/span>:SystemDrive -ChildPath <\/span>“Keys”<\/span><\/p>\n

$cert<\/span> = dir Cert:\\LocalMachine\\My | ? { <\/span>$_<\/span>.Subject -eq <\/span>$certSubject<\/span> }<\/span><\/p>\n

if<\/span> (! (Test-Path <\/span>$keysFolder<\/span> ))<\/span><\/p>\n

{<\/span><\/p>\n

\u00a0\u00a0\u00a0 md <\/span>$keysFolder<\/span> | Out-Null<\/span><\/p>\n

}<\/span><\/p>\n

$certPath<\/span> = Export-Certificate -Cert <\/span>$cert<\/span> -FilePath (Join-Path <\/span>$keysFolder<\/span> -ChildPath <\/span>“Dscdemo.cer”<\/span>)<\/span><\/p>\n

\u00a0<\/span><\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Private key<\/span><\/strong> and protection-Password<\/strong>. For security reason, export the private key certificate as the following:<\/span><\/p>\n

o\u00a0\u00a0 <\/span><\/span>In Personal\\Certificates, find the certificate Issued to \u201c<\/span>DSCDemo<\/span><\/strong>\u201d as shown above. Right click and select the \u201cExport\u2026\u201d option.<\/span><\/p>\n

o\u00a0\u00a0 <\/span><\/span>Take the option of \u201cexport private key\u201d<\/span><\/p>\n

o\u00a0\u00a0 <\/span><\/span>UI will ask you for a<\/span>password for the protection.<\/span>\u00a0Enter and remember your password<\/span>, <\/span>you<\/span>need it very soon. For this demo<\/span>, <\/span>we used <\/span>P@ssword<\/span><\/strong><\/p>\n

o\u00a0\u00a0 <\/span><\/span>Export the certificate to the appropriate folder.\u00a0 In my example, it is C:\\keys\\Dscdemo.pfx<\/strong><\/span><\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Certificate Thumbprint<\/span><\/strong>. Run the following PS script to get certificate\u2019s thumbprint, we need it very soon.<\/span><\/p>\n

\u00a0<\/span><\/p>\n

dir Cert:\\LocalMachine\\My | ? { <\/span>$_<\/span>.Subject -eq <\/span>“CN=DSCDemo”<\/span> }<\/span><\/p>\n

\u00a0<\/span><\/p>\n

In my example, it is E513EEFCB763E6954C52BA66A1A81231BF3F551E<\/span><\/p>\n

\u00a0<\/span><\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Update the deployment scripts:<\/span><\/p>\n

With above steps, we need update deployment scripts to point to the correct certificate values. <\/span><\/p>\n

\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Public key location<\/span>: <\/span>in my example, it is C:\\keys\\Dscdemo.cert<\/span><\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Thrumbprint<\/span>: <\/span>in my example, it is <\/span>E513EEFCB763E6954C52BA66A1A81231BF3F551E<\/span><\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Private key location<\/span>: <\/span>in my example, it is C:\\keys\\Dscdemo.pfx<\/span><\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Private key protection password: in my example, it is P@ssword<\/span><\/p>\n

\n

Update the following places in the deployment scripts:<\/span><\/p>\n

\n

2.1 ConfigSqlDemoData.psd1<\/strong> <\/span><\/p>\n

\n

At line 56, modify the file to point to your private key location.<\/span><\/p>\n

\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>SourcePath =<\/span> “<\/span>C:<\/span>\\Keys\\Dscdemo.pfx”<\/span>;<\/span><\/p>\n

\u00a0<\/span><\/p>\n

\n

\n

At line 145-146, modify the file to point to your certificate file and Thumbprint:<\/span><\/p>\n

\u00a0<\/span><\/p>\n

\u00a0<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @{<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NodeName=<\/span> “*”<\/span><\/span><\/p>\n

\u00a0<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0CertificateFile =<\/span> “C:\\keys\\Dscdemo.cer”<\/span><\/strong><\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Thumbprint =<\/span> “E513EEFCB763E6954C52BA66A1A81231BF3F551E”<\/span><\/span><\/strong><\/p>\n

\u00a0<\/span><\/strong><\/p>\n

\u00a0<\/span><\/p>\n

2.2 deployment\\installcert.ps1<\/span><\/strong><\/p>\n

\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -Password<\/span> $(ConvertTo-SecureString<\/span> -String<\/span> “P@ssword<\/span>” <\/span><\/span><\/p>\n

\u00a0<\/span><\/p>\n

This corresponds private key protection password. Change it to the value you just entered.<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/p>\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Install the certificate to the VMs.\u00a0 Now that we\u2019ve done steps 1 and 2, the deployment script will do the following automatically<\/em>:<\/span><\/p>\n

\u00a0<\/span><\/p>\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Encrypt credentials for the environment that is going to set up. <\/span><\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Copy the private key and the script (installcert.ps1) that holds the private key protection password to each VM\u2019s VHD file (into the VHD\u2019s c:\\deployment folder). Once the VM is started, it will install the certificate with the private key. <\/span><\/p>\n

\u00a0<\/span><\/p>\n

4.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Clean up the certificate.<\/span>\u00a0 <\/span>After you are done with the demo, please remove certificate and keys as soon as possible with the following steps:<\/span><\/p>\n

\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Delete the certificate files. In my case, I delete all files under C:\\keys<\/span><\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Remove the self-signed certificate we just created. In my case, I used the UI to go to Certificates(Local Computer)\\Personal\\Certificates, <\/strong>and deleted certificate issued to DSCDemo<\/strong><\/span><\/p>\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Remove the password in the deployment\\installcert.ps1 file.<\/span><\/p>\n

4.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Delete the xml files under deployment (pdc.xml, sql01.xml, sql02.xml) because they have passwords for VMs bootstrap. <\/span><\/p>\n

5.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>In each VM, delete the files under C:\\deployment<\/span><\/p>\n

6.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Shred the recycle bin of the host machine. <\/span><\/p>\n

\u00a0<\/span><\/p>\n

\n

Prepare the host<\/h2>\n

\n

Before we can run the demo, we need to make sure that we have all of the necessary files in the appropriate places.<\/p>\n

\u00a0<\/span><\/strong><\/p>\n

Copying Files<\/h3>\n

\u00a0<\/span><\/p>\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>\u00a0Confirm that the host machine is running Windows Server 2012 R2. If that is not the case, you can expand the ISO downloaded above to DVD, and install Windows Server 2012 R2 from there. The host is also required to have Hyper-V<\/a>. Please see the Hyper-V Start Guide in the reference section for more details on Hyper-V.\u00a0 It is recommend to upgrade the OS with latest patches by running Windows Update.<\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Create a folder named SqlDemo. In my case, I created the folder here: C:\\SqlDemo<\/strong><\/p>\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Copy the Windows Server 2012 R2 VHD file to C:\\SqlDemo. For me, this looks like: \u201c<\/span>c:\\SqlDemo\\9600.16415.amd64fre.winblue_refresh.130928-2229_server_serverdatacentereval_en-us.vhd\u201d <\/span><\/strong><\/p>\n

4.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Copy the Windows Server ISO to C:\\SqlDemo. To make things simple, you can rename the file to a short name. In my case, this looks like: C:\\SqlDemo\\WS12R2.ISO<\/p>\n

5.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Similarly, copy the SQL ISO \u00a0to C:\\SqlDemo. Again, rename the file to a short name like this: C:\\SqlDemo\\SqlSP1.iso<\/p>\n

6.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Unzip Dsc-SqlDemo.zip. In my case, it is like C:\\Dsc-SqlDemo, the entire folder like the following:<\/p>\n

\"Image<\/a><\/p>\n

7.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>download xActiveDirectory, xComputerManagement, xFailOverCluster, xHyper-V, xNetworking, xSmbShare, xSqlPs modules if not. Copy them to root of unzipped folder. It looks like the following in the end:<\/p>\n

\n

\"\"<\/a><\/p>\n

\n

Extracting Content<\/h3>\n

\n

Now that we\u2019ve copied the ISOs into the necessary locations, we need to extract some of their content.\u00a0 Specifically, we need to get the sxs files (which include .Net 3.5), and the SQL content.\u00a0 While<\/strong> there are many ways to do this, the simplist way in this situation is to run the \u201cGetFilesFromImage.ps1\u201d<\/strong> script in DSC-SqlDemo folder.<\/p>\n

\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Open a Windows PowerShell console (with Administrator privileges<\/strong>), and cd to the Dsc-SqlDemo folder.<\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Run the following script to get sxs files including .Net 3.5<\/strong><\/p>\n

\n

.\\GetFilesFromImage.ps1 -ImagePath c:\\SqlDemo\\WS12R2.ISO -SrcPath \u201csources\\sxs\u201d -DstPath c:\\SqlDemo\\Srv12R2\\sxs<\/strong><\/p>\n

\n

\"\"<\/a><\/p>\n

Figure 1: Note: -SrcPath has no driver letter because we don\u2019t know which driver letter the ISO image will mount to until runtime.<\/p>\n

\n

\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Similarly, get the entire Sql ISO content<\/strong> by running the following script:<\/p>\n

\n

.\\GetFilesFromImage.ps1 -ImagePath c:\\SqlDemo\\Sql12SP1.ISO -SrcPath \u201c*\u201d \u2013DstPath c:\\SqlDemo\\Sql12SP1<\/strong><\/p>\n

\n

\"\"<\/a><\/p>\n

\n

Remember folder c:\\SqlDemo\\Srv12R2\\sxs and c:\\SqlDemo\\Sql12SP1, we need them later on.<\/p>\n

<\/h2>\n

Checking the Configuration Data File<\/h3>\n

\u00a0<\/strong><\/p>\n

It\u2019s important to ensure the configuration data file (c:\\dsc-SqlDemo\\ConfigSqlDemoData.psd1<\/strong>) has the correct information. If you used the same paths as above for SqlDemo<\/strong>, and are okay with using the default credentials, the demo should work without any change. However, if SqlDemo<\/strong> and underneath files is in different path, driver, or name, their locations need to be updated in the data file.<\/p>\n

\n

Checking Credentials<\/h4>\n

\n

By default, \u201cP@ssword\u201d is the password for every credential<\/strong>. You can change to your own if you would like, but please remember them. And don\u2019t forget to do clean up after the demo.<\/p>\n

\n

Also, notice, those three VMs are created in private network of the host. In another word, they are only visible to each other and the host. To make vm access software\u2019s on the host, we create a local user: vmuser, which could read access to SqlDemo folder (in my case: c:\\SqlDemo).<\/p>\n

\n

Checking Paths<\/h4>\n

\n

Confirm that the following paths in the ConfigSqlDemoData.psd1<\/strong> file are correct:<\/p>\n

\n

# Windows Server 2012 R2 vhd <\/span><\/p>\n

VhdSrcPath =<\/span> “c:\\SqlDemo\\9600.16415.amd64fre.winblue_refresh.130928-2229_server_serverdatacentereval_en-us.vhd”<\/span><\/span><\/p>\n

\u00a0<\/span><\/p>\n

# .Net 3.5 source files\u00a0\u00a0 <\/span><\/p>\n

@{ Source =<\/span> “C:\\SqlDemo\\Svr12R2\\sxs”<\/span>;\u00a0\u00a0\u00a0 Destination =<\/span> “sxs”<\/span> } <\/span><\/p>\n

\n

# Sql software folder on Host<\/span><\/p>\n

SqlSrcHostPath =<\/span> “C:\\SqlDemo\\Sql12SP1”<\/span>\u00a0 <\/span><\/p>\n

\n

Running the demo<\/h2>\n

\n

Once everything is ready, running the demo is as simple as this:<\/p>\n

\"\"<\/a><\/p>\n

\n

The script will ask you to enter password for private domain administrator, sql administrator, user to access host file share, and user on the host for the file share access. The last two should have the same password. In my example, I entered “P@ssword” four times for the sake of simply.<\/p>\n

\n

After about 30-60 minutes, the SQL AG will be set up across three VMs running on the host machine:<\/p>\n

\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>SqlDemo-pdc \u2013 the primary domain controller, which ensures the private domain for two SQL cluster nodes.<\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>SqlDemo-Sql01 \u2013 the primary node in SQL\u00a0AlwaysOn Availability Group<\/p>\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>SqlDemo-Sql02 \u2013 the secondary node in SQL\u00a0AlwaysOn Availability Group<\/p>\n

\n

\"\"<\/a><\/p>\n

\n

Verification (How do you know it worked)<\/span><\/span><\/h3>\n

\n

It’s worth noting that when the configuration returns success on the host machine, that only indicates that the VMs have been created, NOT that SQL\u00a0AG deployment on VMs is completed.\u00a0 The deployment takes about 30-60 minutes, so be patient with the installation script.<\/p>\n

\n

To check for complete status:<\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Monitor the size of the vhds being created on the host machine under Vm\\. pdc vhd should be about 2.4 GB, sql vhds should be about 8GB.<\/p>\n

\n

To debug a failure:<\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span>Check the ETW events on each VM under Applications and Services Logs\\Microsoft\\Windows\\Desired State Configuration\/Operational<\/p>\n

\n

To confirm success:<\/p>\n

1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Login to one of SQLs nodes<\/p>\n

2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Start \u201cMicrosoft SQL Server Management Studio\u201d<\/p>\n

3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Connect to one of SQL instances (like sql01, or 192.168.100.11 in IP)<\/p>\n

4.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Under \u201cAlwaysOn High Availability\u201d, you should see something like the following snapshot:<\/p>\n

\n

\"\"<\/a><\/p>\n

\n

5.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Expand the Databases folder<\/p>\n

6.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Open TestDB<\/p>\n

7.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Populate some data<\/p>\n

8.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>Check that it is replicated on the second node shortly thereafter.<\/p>\n

Key Takeaways<\/h2>\n

This example is far more complex than most others that have been shown or created.\u00a0 As such, it demonstrates many characteristics of configurations that may be lost in the simpler scenarios.\u00a0 Here are a few things we think are worth noting.<\/p>\n

\n

    \n
  1. \u00a0<\/span>Each configuration uses a Configuration Data File<\/em> to separate the structural configuration data from the environmental configuration.\u00a0 This allows the example to easily scale up.<\/li>\n
  2. The \u201cWaitFor\u201d pattern<\/em> is used many times to coordinate across machines.\u00a0 This pattern is used in scenarios where a machine needs to wait for another machines to do something.\u00a0 For example, Sql02 needed to wait for the Primary Domain Controller to create the domain before ensuring that it was joined to the domain.<\/li>\n
  3. The configurations that ran in pdc, sql01, and sql02 were bootstrapped into the VHDs as .mof files.\u00a0 This technique improves scalability and performance when configuring VMs at startup.\u00a0 Stay tuned for a blog post on this later.<\/li>\n<\/ol>\n

    \n

    That\u2019s it!\u00a0 Let us know what you think in the comments.<\/p>\n

    \n

    Enjoy the fun!<\/p>\n

    \n

    Chen Shang, Mark Gray, John Slack, Narine Mossikyan<\/p>\n

    Windows DSC Team<\/p>\n<\/div>\n

    Dsc-SqlDemo.zip<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

    Let’s use DSC to configure something complicated!\u00a0 In past blogs, we\u2019ve shown you how to use Windows PowerShell Desired State Configuration (DSC) to configure relatively simple systems.\u00a0 However, the technologies you deal with on a day to day basis can sometimes become complicated.\u00a0 Don\u2019t worry, DSC can still help simplify configuration.\u00a0 Let\u2019s use SQL\u00a0AlwaysOn\u00a0Availability Group […]<\/p>\n","protected":false},"author":600,"featured_media":13641,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[142,150,251,345,348],"class_list":["post-1811","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-powershell","tag-desired-state-configuration","tag-dsc","tag-powershell-4-0","tag-windows-powershell-4-0","tag-windows-powershell-desired-state-configuration"],"acf":[],"blog_post_summary":"

    Let’s use DSC to configure something complicated!\u00a0 In past blogs, we\u2019ve shown you how to use Windows PowerShell Desired State Configuration (DSC) to configure relatively simple systems.\u00a0 However, the technologies you deal with on a day to day basis can sometimes become complicated.\u00a0 Don\u2019t worry, DSC can still help simplify configuration.\u00a0 Let\u2019s use SQL\u00a0AlwaysOn\u00a0Availability Group […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts\/1811","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/users\/600"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/comments?post=1811"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/posts\/1811\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/media\/13641"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/media?parent=1811"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/categories?post=1811"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/powershell\/wp-json\/wp\/v2\/tags?post=1811"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}