Udostępnij za pośrednictwem

Why should you install SQL 2008 from the command line

  • Artykuł

 

 

If a project needs several SQL Servers, I believe that command-line setup is the way to install SQL Server for the following reasons:

  • Speed. Once the process is established, it's quick to follow.
  • Lesser chance of making mistake while navigating the setup GUI.
  • Guarantees that instances in your project are installed exactly the same way.
  • If setup runs into problems, it's very easy to escalate, because you know exactly what you did

This is how typical SQL command-line setup command looks like:

 

setup.exe /q /PID="XXXXX-XXXXX-XXXXX-XXXXX-XXXXX" /ACTION=install /FEATURES=SQL,IS,Tools,BIDS /InstanceName=MSSQLSERVER /SQLSVCACCOUNT="DOMAIN\svcaccount" /AGTSVCACCOUNT="DOMAIN\svcaccount" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /ISSVCAccount="NT AUTHORITY\Network Service" /AGTSVCSTARTUPTYPE=2 /SQLSVCSTARTUPTYPE=2/ISSVCSTARTUPTYPE=2 /InstanceDir=D:\ /ERRORREPORTING=1 /SQMREPORTING=1 /SQLSVCPASSWORD="****" /AGTSVCPASSWORD="****" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" "DOMAIN\svcaccount"

A few comments:

  • The complete syntax and all the options can be found in Books Online (How to: Install SQL Server 2008 from the Command Prompt)
  • The edition is inferred from the /PID. If /PID is not specified, evaluation edition (180-day expiration) is installed.
  • There are some pre-requisites to SQL Setup (Windows Installer 4.5, Powershell). If they are not installed, the setup will install them first and may ask you to reboot and re-run the setup.
  • At least with SQL Server 2008 RC With Vista/Win2008 it's better to run the setup from elevated (Administrator) command prompt. If ran from non-elevated command prompt, the setup will not be able to print errors or messages - it will just silently end, and you will have to look at the setup logs to see what happened.
  • Even with successful setup, the logs should be inspected. The summary log is here: "C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt". On successful install, there's a "Final result: Passed" line at the top of the log.
  • For couple of reasons, I don't like SQL installed into C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\... - I prefer that SQL is installed in the root of a different drive. Hence I specified /InstanceDir=D:\ (SQL will be installed into D:\MSSQL10.MSSQLSERVER\MSSQL), but if you like default just don't specify this parameter.

Now, the fresh install of SQL will not allow connections from outside. Continuing command-line theme, here is how one can enable Named Pipes and TCP/IP (SQL needs to be cycled for changes to take effect):

 

WMIC /NAMESPACE:\\root\Microsoft\SqlServer\ComputerManagement10 PATH ServerNetworkProtocol WHERE "ProtocolName='Tcp' OR ProtocolName='Np'" CALL SetEnable

 

Open up port 1433 for TCP/IP connections:

 

netsh firewall add portopening TCP 1433 SQLServerDefaultInstance

 

You will need to tweak these commands for your environment, but I'm sure that small investment into getting your command-line install steps documented will speed up your installs and bring better predictability.

As a DBA, I found especially useful to provide a guide to Developers and Testers for the SQL setups. Now when I log into their server to troubleshoot something, I know how exactly SQL was installed.

Comments

  • Anonymous
    May 01, 2009
    Can you clarify this further? "For couple of reasons, I don't like SQL installed into C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQL" I know that the system drive is basic disk and is difficult to expand.  What other reasons are there to install SQL to another volume?

  • Anonymous
    January 29, 2010
    The comment has been removed