All posts by jimejamm

Installing SQL on core

For example ..

setup.exe /q /iacceptsqlserverlicenseterms /action=install /features=sqlengine,replication /instanceid=SQLEXPRESS /instancename=SQLEXPRESS /usemicrosoftupdate

OR you can force enable the UI if (really?) necessary

setup.exe /uimode=EnableUIonServerCore

Don’t forget your firewall allow rules

netsh advfirewall firewall add rule name=”Allow SQL TCP 54321″ protocol=TCP dir=in localport=54321 action=allow

netsh advfirewall firewall add rule name=”Allow SQL UDP 1434″ protocol=UDP dir=in localport=1434 action=allow

And here is the setup help for reference

Microsoft (R) SQL Server 2014 12.00.2000.08
Copyright (c) Microsoft Corporation.  All rights reserved.

Usage:
setup.exe /[option]={value} /[option]={value} …

Options:
ACTION                       Specifies a Setup work flow, like INSTALL,
UNINSTALL, or UPGRADE. This is a required
parameter.
ADDCURRENTUSERASSQLADMIN     Provision current user as a Database Engine
system administrator for %SQL_PRODUCT_SHORT_NAME%
Express.
AGTDOMAINGROUP               Either domain user name or system account
AGTSVCACCOUNT                Either domain user name or system account
AGTSVCPASSWORD               Password for domain user name. Not required for
system account
AGTSVCSTARTUPTYPE            Startup type for the SQL Server Agent service.
Supported values are Manual, Automatic or
Disabled.
ALLINSTANCES                 Specifies that all instances are to be included
in the Setup operation. This parameter is
supported only when applying a patch.
ALLOWUPGRADEFORSSRSSHAREPOIN
RSInputSettings_AllowUpgradeForSSRSSharePointMode_Description
ASBACKUPDIR                  The location for the Analysis Services backup
files.
ASCOLLATION                  The collation used by Analysis Services.
ASCONFIGDIR                  The location for the Analysis Services
configuration files.
ASDATADIR                    The location for the Analysis Services data
files.
ASLOGDIR                     The location for the Analysis Services log files.
ASPROVIDERMSOLAP             Specifies if the MSOLAP provider can run in
process.
ASSERVERMODE                 Specifies the server mode of the Analysis
Services instance. Valid values are
MULTIDIMENSIONAL and TABULAR. The default value
is MULTIDIMENSIONAL.
ASSVCACCOUNT                 The account used by the Analysis Services
service.
ASSVCPASSWORD                The password for the Analysis Services service
account.
ASSVCSTARTUPTYPE             Controls the service startup type setting for the
service.
ASSYSADMINACCOUNTS           Specifies the list of administrator accounts to
provision.
ASTEMPDIR                    The location for the Analysis Services temporary
files.
BROWSERSVCSTARTUPTYPE        Startup type for Browser Service.
CLTCTLRNAME                  The computer name that the client communicates
with for the Distributed Replay Controller
service.
CLTRESULTDIR                 The result directory for the Distributed Replay
Client service.
CLTSTARTUPTYPE               The startup type for the Distributed Replay
Client service.
CLTSVCACCOUNT                The account used by the Distributed Replay Client
service.
CLTSVCPASSWORD               The password for the Distributed Replay Client
service account.
CLTWORKINGDIR                The working directory for the Distributed Replay
Client service.
CLUSTERPASSIVE               Specifies that SQL Server Setup should not manage
the SQL Server services. This option should be
used only in a non-Microsoft cluster environment.
COMMFABRICENCRYPTION         MATRIXCOMMMESSAGEPROTECTION {0,1}
COMMFABRICNETWORKLEVEL       MATRIXCOMMNETWORKISOLATION {0,1}
COMMFABRICPORT               MATRIXCOMMPORT <port>
CONFIGURATIONFILE            Specifies the configuration file to be used for
Setup.
CONFIRMIPDEPENDENCYCHANGE    Indicates that the change in IP address resource
dependency type for the SQL Server multi-subnet
failover cluster is accepted.
CTLRSTARTUPTYPE              The startup type for the Distributed Replay
Controller service.
CTLRSVCACCOUNT               The account used by the Distributed Replay
Controller service.
CTLRSVCPASSWORD              The password for the Distributed Replay
Controller service account.
CTLRUSERS                    The Windows account(s) used to grant permission
to the Distributed Replay Controller service.
ENABLERANU                   Set to “1” to enable RANU for SQL Server Express.
ENU                          Use the /ENU parameter to install the English
version of SQL Server on your localized Windows
operating system.
ERRORREPORTING               Specify if errors can be reported to Microsoft to
improve future SQL Server releases. Specify 1 or
True to enable and 0 or False to disable this
feature.
FAILOVERCLUSTERDISKS         Specifies a cluster shared disk to associate with
the SQL Server failover cluster instance.
FAILOVERCLUSTERGROUP         Specifies the name of the cluster group for the
SQL Server failover cluster instance.
FAILOVERCLUSTERIPADDRESSES   Specifies an encoded IP address. The encodings
are semicolon-delimited (;), and follow the
format <IP Type>;<address>;<network name>;<subnet
mask>. Supported IP types include DHCP, IPV4, and
IPV6.
FAILOVERCLUSTERNETWORKNAME   Specifies the name of the SQ LServer failover
cluster instance.  This name is the network name
that is used to connect to SQL Server services.
FAILOVERCLUSTERROLLOWNERSHIP Specifies whether the upgraded nodes should take
ownership of the failover instance group or not.
Use 0 to retain ownership in the legacy nodes, 1
to make the upgraded nodes take ownership, or 2
to let SQL Server Setup decide when to move
ownership.
FEATURES                     Specifies features to install, uninstall, or
upgrade. The list of top-level features include
SQL, AS, RS, IS, MDS, and Tools. The SQL feature
will install the Database Engine, Replication,
Full-Text, and Data Quality Services (DQS)
server. The Tools feature will install Management
Tools, Books online components, SQL Server Data
Tools, and other shared components.
FILESTREAMLEVEL              Level to enable FILESTREAM feature at (0, 1, 2 or
3).
FILESTREAMSHARENAME          Name of Windows share to be created for
FILESTREAM File I/O.
FTSVCACCOUNT                 User account for Full-text Filter Daemon Host.
FTSVCPASSWORD                User password for Full-text Filter Daemon Host
account.
FTUPGRADEOPTION              Full-text catalog upgrade option.
HELP                         Displays the command line parameters usage
IACCEPTSQLSERVERLICENSETERMS By specifying this parameter and accepting the
SQL Server license terms, you acknowledge that
you have read and understood the terms of use.
INDICATEPROGRESS             Specifies that the detailed Setup log should be
piped to the console.
INSTALLSHAREDDIR             Specify the root installation directory for
shared components.  This directory remains
unchanged after shared components are already
installed.
INSTALLSHAREDWOWDIR          Specify the root installation directory for the
WOW64 shared components.  This directory remains
unchanged after WOW64 shared components are
already installed.
INSTALLSQLDATADIR            The Database Engine root data directory.
INSTANCEDIR                  Specify the instance root directory.
INSTANCEID                   Specify the Instance ID for the SQL Server
features you have specified. SQL Server directory
structure, registry structure, and service names
will incorporate the instance ID of the SQL
Server instance.
INSTANCENAME                 Specify a default or named instance. MSSQLSERVER
is the default instance for non-Express editions
and SQLExpress for Express editions. This
parameter is required when installing the SQL
Server Database Engine (SQL), Analysis Services
(AS), or Reporting Services (RS).
ISSVCACCOUNT                 Either domain user name or system account.
ISSVCPASSWORD                Password for domain user.
ISSVCSTARTUPTYPE             Automatic, Manual or Disabled.
MATRIXCMBRICKCOMMPORT        MATRIXCMBRICKCOMMPORT portNumber
MATRIXCMSERVERNAME           MATRIXCMSERVERNAME hostName\instanceName
MATRIXNAME                   MATRIXNAME=<name>
NPENABLED                    Specify 0 to disable or 1 to enable the Named
Pipes protocol.
PID                          Specify the SQL Server product key to configure
which edition you would like to use.
QUIET                        Setup will not display any user interface.
QUIETSIMPLE                  Setup will display progress only, without any
user interaction.
ROLE                         Detailed help for command line argument ROLE has
not been defined yet.
RSCATALOGSERVERINSTANCENAME  The SQL Server server for the report server
catalog database.
RSINSTALLMODE                RSInputSettings_RSInstallMode_Description
RSSHPINSTALLMODE             RSInputSettings_RSInstallMode_Description
RSSVCACCOUNT                 Specify the service account of the report server.
This value is required. If you omit this value,
Setup will use the default built-in account for
the current operating system (either
NetworkService or LocalSystem). If you specify a
domain user account, the domain must be under 254
characters and the user name must be under 20
characters. The account name cannot contain the
following characters:
” / \ [ ] : ; | = , + * ? < >
RSSVCPASSWORD                Specify a strong password for the account. A
strong password is at least 8 characters and
includes a combination of upper and lower case
alphanumeric characters and at least one symbol
character. Avoid spelling an actual word or name
that might be listed in a dictionary.
RSSVCSTARTUPTYPE             Specifies the startup mode for the Report Server
service. Valid values include Manual, Automatic,
and Disabled. The default value for StartupType
is Manual, where the server is started when a
request is received.
RSUPGRADEDATABASEACCOUNT     RSInputSettings_RSInstallMode_Description
RSUPGRADEPASSWORD            RSInputSettings_RSInstallMode_Description
RULES                        Specifies the list of rule IDs or rule group IDs
to run.
SAPWD                        Password for SQL Server sa account.
SECURITYMODE                 The default is Windows Authentication. Use “SQL”
for Mixed Mode Authentication.
SQLBACKUPDIR                 Default directory for the Database Engine backup
files.
SQLCOLLATION                 Specifies a Windows collation or an SQL collation
to use for the Database Engine.
SQLSVCACCOUNT                Account for SQL Server service: Domain\User or
system account.
SQLSVCPASSWORD               A SQL Server service password is required only
for a domain account.
SQLSVCSTARTUPTYPE            Startup type for the SQL Server service.
SQLSYSADMINACCOUNTS          Windows account(s) to provision as SQL Server
system administrators.
SQLTEMPDBDIR                 Directory for Database Engine TempDB files.
SQLTEMPDBLOGDIR              Directory for the Database Engine TempDB log
files.
SQLUSERDBDIR                 Default directory for the Database Engine user
databases.
SQLUSERDBLOGDIR              Default directory for the Database Engine user
database logs.
SQMREPORTING                 Specify that SQL Server feature usage data can be
collected and sent to Microsoft. Specify 1 or
True to enable and 0 or False to disable this
feature.
TCPENABLED                   Specify 0 to disable or 1 to enable the TCP/IP
protocol.
UIMODE                       Parameter that controls the user interface
behavior. Valid values are Normal for the full
UI,AutoAdvance for a simplied UI, and
EnableUIOnServerCore for bypassing Server Core
setup GUI block.
UpdateEnabled                Specify whether SQL Server Setup should discover
and include product updates. The valid values are
True and False or 1 and 0. By default SQL Server
Setup will include updates that are found.
UpdateSource                 Specify the location where SQL Server Setup will
obtain product updates. The valid values are “MU”
to search Microsoft Update, a valid folder path,
a relative path such as .\MyUpdates or a UNC
share. By default SQL Server Setup will search
Microsoft Update or a Windows Update service
through the Window Server Update Services.
USEMICROSOFTUPDATE           If this parameter is provided, then this computer
will use Microsoft Update to check for updates.
X86                          Specifies that Setup should install into WOW64.
This command line argument is not supported on an
IA64 or a 32-bit system.

Full unattended installation example, showing all required parameters:

setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install /PID=<validpid> /FEATURES=SQL,AS,RS,IS,Tools
/INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT=”MyDomain\MyAccount”
/SQLSVCPASSWORD=”************” /SQLSYSADMINACCOUNTS=”MyDomain\MyAccount ”
/AGTSVCACCOUNT=”MyDomain\MyAccount” /AGTSVCPASSWORD=”************”
/ASSVCACCOUNT=”MyDomain\MyAccount” /ASSVCPASSWORD=”************”
/RSSVCACCOUNT=”MyDomain\MyAccount” /RSSVCPASSWORD=”************”
/ISSVCAccount=”MyDomain\MyAccount” /ISSVCPASSWORD=”************”
/ASSYSADMINACCOUNTS=”MyDomain\MyAccount”

Core versus Full – Initial install quick stats

Created two VM’s with identical resources. On the first VM, Windows 2012 R2 Core Installation was chosen. On the second VM, Windows 2012 R2 Server with a GUI (Full) was chosen. After installation of both, I also ran updates on both until no more Windows updates were found.

Here are the quick stats:

    Core has more free disk space: 32,077,897,728 bytes to 26,787,229,696 bytes

Core uses less memory: 335 MB to 438 MB

    Core required less updates: 117 fixes to 153 fixes

    Full 2012 R2 initial installCore 2012 R2 initial install

    No surprises at this point. Core has no GUI, so it uses a sliver less memory, about 5GB less drive space, and there is less surface area to patch. Observed (not measured) that Server Core installed faster and completed patching faster too. But we’re only at step one in the life of a new server and saving a few minutes during an the install is probably not the reason Server Core is recommended.

    The fewer number of patches, though, does already start to show how Server Core is going to benefit in patch maintenance over the server lifecycle.

    So, do you reboot a server to apply some critical IE security fix? Hey, admins shouldn’t be browsing the web from servers anyway. But then why is it that IESC is so often found disabled on the servers? Besides that missing patch is going to keep showing up on vulnerability scans, and we do have to rescan until clean, right? Ever have these debates? By incurring less GUI related patches, it seems to me that Server Core also reduces the number of these time wasting debates over the administrator lifecycle.

    So where to go from here? I’m thinking SQL Server installation next then using HammerDB to benchmark a core SQL versus a full SQL. Another scenario I’d also like to try is perhaps creating an IIS farm behind a load balancer using least queue depth to see which server handles more traffic as another benchmark. Hmm, could create some How-to posts with either of those too … stay tuned …

Project Bookshelf

Project Bookshelf: The events depicted in this story are fictional. Any similarity to any person living or dead is merely coincidental.

(Moe) Team, let’s get these books off the floor by putting up a bookshelf on that nice sturdy wall over there.
(Kirk & Guy) Sounds great!

(Kirk) We found a bookshelf that should fit. It needs to be assembled with screws though.
(Moe) We have a screw driver.
(Kirk) Oh, it also needs to be mounted to the wall, so we have to drill some holes in the wall.
(Moe) Well, let’s use the Microvendor tool we have then, it’ll drill the holes then just change the bit and it’ll be a screw driver too.
(Kirk) Nice! That’ll work.
(Moe) Bunch of toilets clogged, tell you what, I’ll handle the toilets so you guys can focus on the bookshelf.
(Kirk & Guy) Ok!

Later …
(Moe) Hey how’s it looking … bookshelf up yet? Apparently not.
(Guy) Well, the Microvendor is okay, but I’m used to working with VMvendor drills and VMvendor just seems like a better tool.
(Moe) So?
(Kirk) So the VMvendor sales guy is coming in to show us their tools.
(Moe) ok, fine. We have a leaky roof, call me down when the sales guy is here.

(Kirk & Guy) Sales guy is here!
(sales guy) These VMvendor batteries last longer, and just look at all the VMvendor tools available. Microvendor sucks.
(Moe) Interesting, let’s compare
… test test test
(Moe) Well, seems like the batteries last about the same. And Microvendor has all the same tool selections.
(Kirk & Guy) We really, really just like the VMvendor tools better. Made in the USA, not China. USA! USA! USA!
(Moe) ok, well, how much are the VMvendor tools?
(sales guy) $160,000 the first year and $50,000 yearly maintenance … for the drill. But the other tools can be bundled in at …
(Moe) Stop right there buddy. The Microvendor tools that we already have should be just fine.
(Guy) No VMvendor? Moe, you’re so biased! Everyone uses VMvendor! How could you not choose VMvendor!?! … I quit! (Guy leaves)
(Moe) Uh, what the ..? Well, anyway, let’s get back to this bookshelf… using our Microvendor tools ok?
(Kirk) Yep, fine … although stupid Microvendor caused Guy to leave … open your mind Moe
(Moe) ok, then… Oops, got a call about a zombie rodent infestation. I’ll take care of it, so you can focus on the project

Later …
(Moe) Hey how’s it going? … Still no bookshelf? What happened now?
(Kirk) Well, Microvendor makes this caddy to charge up to 8 batteries at a time. So I had to wall mount it.
(Moe) I see. And what is this tool bench here for?
(Kirk) Well, we may have to trim a shelf one day, so the Microvendor table saw might be useful and needs to be mounted on something solid. So I setup the tool bench.
(Moe) Table saw? … uh, ok Kirk, let’s talk when I get back. I have to go, killer clowns are terrorizing the reception area. Should be able to get the bookshelf up quickly now, right!?
(Kirk) Absolutely!

Later …
(Moe) Damn, what’s up? The books are still all over the floor.
(Kirk) Well, we had an issue. When the table saw runs at the same time that 8 batteries are charging, the circuit breaker pops.
(Moe) Listen Kirk, maybe we should just use the simple Microvendor drill and just get the bookshelf up. Then worry about the tool bench later …
(Kirk) What!? I work to get all these advanced tools in place and you want me to go backwards now? You just don’t like that I’m ahead of you!
(Moe) wow ok. so, what do you suggest?
(Kirk) Let me just get this one issue worked out. I’ll call the electrician.
(Moe) Fine, call.

Later …
(Kirk) Electrician says we need to upgrade the circuit.
(Moe) How long is that going to take?
(Kirk) Not long, we should be ready soon.

Later …
(Moe) Hey, while you were coordinating with the electrician. I got the bookshelf put together and ..
(Kirk) How dare you! Are you competing with me or something?!
(Moe) Relax Kirk, I just want to get the books off the floor
(Kirk) Well, with my setup we’d be able to put up bookshelves way faster than YOU could manually.
(Moe) It took 12 minutes using the Microvendor drill
(Kirk) MY tool bench setup will make it take only 8 minutes, ha!
(Moe) Look, I’m going to start moving the books if you want to help …
(Kirk) Whatever! The electrician is here to change the circuit

Later …
(Moe) I’m almost done with moving the books to the bookshelf… umm, how come the electrician is still here?
(Kirk) Well, I want to make sure that the tool bench is always available. So the electrician is now helping me add a backup generator. This tool bench is a shining example of how we should work all projects!
(Moe) *sigh* but the project was the bookshelf