Installing SQL Server Denali CTP3 on Windows 2008 R2 Core Edition

  • Article

Denali is packed with new features. A lot of features evolve around the engine, BI, Data Quality but there are also new features added to the installation process. In a previous post I already mentioned managed service accounts and in this post I will show how to install SQL Server Denali on Windows 2008 R2 Core Edition. As an added bonus I will be using managed service accounts again but instead of having the domain admin activate the managed service account I will delegate these permissions to Bob, our SQL administrator. I’m using the same setup as in my previous post.

I installed a new Windows Server 2008 R2 Core Edition and set a fixed ip (10.10.10.5/255.255.255.0). The computer was named SQL-02 and added to the domain CONTOSO. Again, Bob was added to the local administrators group using net localgroup Administrators CONTOSO\Bob /ADD.

Preparing the managed service account

First we will setup a new group for SQL Admins, add a new managed service account, sql2, and delegate permissions to the SQL Admins group. For Bob to be able to control the managed service account we must add him to the SQL Admins group.

  • On the domain controller as the domain admin create a new group in AD:
    dsadd group cn=SqlAdmins,cn=users,dc=contoso,dc=com
  • Now add Bob to that group:
    dsmod group cn=SqlAdmins,cn=users,dc=contoso,dc=com –addmbr cn=Bob,cn=Users,dc=contoso,dc=com
  • Create a managed service account. First start PowerShell and import the AD cmdlets using Import-Module ActiveDirectory. Then create the account:
    New-ADServiceAccount sql2
  • To verify the creation you can use Get-ADServiceAccount sql2.
  • To delegate permissions to the SQL Admins group we must use dcals. Again, the command and procedure is documented in the Step by Step guide. Exit PowerShell and then run:
    dsacls "CN=sql2,CN=Managed Service Accounts,DC=contoso,DC=com" /G "CONTOSO\SqlAdmins:SDRCLCRPLOCA" "CONTOSO\SqlAdmins:WP;Logon Information" "CONTOSO\SqlAdmins:WP;Description" "CONTOSO\SqlAdmins:WP;DisplayName" "CONTOSO\SqlAdmins:WP;Account Restrictions" "CONTOSO\SqlAdmins:WS;Validated write to DNS host name" "CONTOSO\SqlAdmins:WS;Validated write to service principal name"
  • Now Bob can log into SQL-02.
  • First enable PowerShell using sconfig.cmd. Choose 4. Configure Remote Management followed by 2. Enable Windows PowerShell.
  • Next we need to install a few pre-requirements before we can install SQL Server Denali. First install/enable .NET 3.5.1 using:
    dism.exe /online /enable-feature /featurename:NetFx3-ServerCore
  • Next up we need the WOW64 .NET 2.0 bits. Run these two commands to get these:
    dism.exe /online /Enable-Feature /FeatureName:ServerCore-WOW64
    dism.exe /online /Enable-Feature /FeatureName:NetFx2-ServerCore-WOW64
  • Now Bob needs to associate the managed service account with this machine. For that we need the Active Directory cmdlets. Run the following command to install the AD PowerShell cmdlets:
    dism /online /enable-feature /featurename:ActiveDirectory-PowerShell
  • Next, start up PowerShell and import the AD cmdlets using Import-Module ActiveDirectory.
  • Now we need to activate the managed service account at this machine. For this we run Get-ADServiceAccount sql2 to verify that the account is enabled, not associated with another computer and what the SamAccountName is.
    image
  • Now we install the managed service account on the computer. We need the SamAccountName here, sql2$ in this case.
    Install-ADServiceAccount -identity sql2$
  • If everything is correctly configured you will succeed and when you run Get-ADServiceAccount sql2 you will see that HostComputers is filled now. And while the name HostComputers suggest otherwise, a managed service account can only be associated with one computer.
  • Finally the time has come to install SQL Server “Denali” CTP3. There is no GUI on core so we need to resort to the command prompt interface. This is documented in Books Online; https://msdn.microsoft.com/en-us/library/ms144259(v=sql.110).aspx.
  • Put in the installation media and run the following command (on multiple lines for readability):
    setup.exe /q /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=DENALICTP3A /SQLSVCACCOUNT=CONTOSO\sql2$ /AGTSVCACCOUNT=CONTOSO\sql2$ /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS /AGTSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS=CONTOSO\SqlAdmins /TCPENABLED=1
  • Afther the installation is finished you can check the summary.txt file in the Bootstrap folder, C:\Program Files\Microsoft SQL Server\110\Bootstrap\Log\Summary.txt.
  • As with the machine in my previous post I wanted this instance to run on port 6000 as well. Start PowerShell and run this command:
    (gwmi –namespace root\Microsoft\SqlServer\ComputerManagement11 –class ServerNetworkProtocolProperty –filter “InstanceName=’DENALICTP3A’ AND ProtocolName=’Tcp’ AND IPAddressName=’IPALL’ AND PropertyName=’TcpPort’”).SetStringValue(6000)
  • This will use WMI to set the tcp port for IPALL. For SQL pro’s the filter should look familiar. After that we need to restart the instance. We use Stop-Service MSSQL`$DENALICTP3A –force to stop the service. We need to escape the dollar sign and we need the force parameter because there are dependent services (SQL Agent). Now start the service with Start-Service MSSQL`$DENALICTP3A.
  • To verify SQL is listening on we can search the errorlog for the text “Server is listening on [ ‘any’ <ipv4> 6000].” or you can use netstat –aon | findstr :6000. This will give you the process id that is listening on that port. Using tasklist /FI “PID eq <pid>” where replacing <pid> with the earlier found process id should give sqlserver.exe in return.
  • Now SQL is listening on port 6000. We need to open up the appropriate ports in the firewall. use these two commands to open port 6000 and allow UDP traffic for the Browser service.
    netsh advfirewall firewall add rule name="SQL Server DENALICTP3A Instance" dir=in action=allow protocol=tcp localport=6000 profile=domain
    netsh advfirewall firewall add rule name="SQL Server Browser Service" dir=in action=allow protocol=udp localport=1434 profile=domain
  • Now returning to the Windows 7 client and SSMS let’s see how we are connected to these instances.
    image

With Windows Server 2008 R2 Core you get a smaller install base; 33 processes running on core vs 41 on full installation, 351MB vs 452MB memory in use after start and 4GB vs 9GB disk space. Of course your mileage may vary but you get the general idea. Not only did I show how easy it is to install SQL Server Denali CTP3 on a Windows Server Core edition but also how you permissions to control managed service accounts can be delegated to other administrators.

Give SQL Server Denali CTP3 a run and remember to provide your feedback. If you’re fast you might win something as well, https://blogs.technet.com/b/dataplatforminsider/archive/2011/08/19/announcing-the-sql-server-code-name-denali-feedback-challenge.aspx.

Comments

  • Anonymous
    December 18, 2012
    helped me alot really a good post thanks Mr.Robert Hartskeerl

  • Anonymous
    December 19, 2012
    Hi Robert Hartskeerl Can u help me out in delegation of managed service account and how to set a managed service account as a service administrator?