The fourth time is a charm!

I’ve documented my experients with SQL Server 2005 silent / command line install in 3 previous posts. They all failed. To be fair, I tested it using September CTP, not the finalized RTM release.

After I got the RTM last November, I loaded it on my laptop, so I could test all my code and screen shots for the book we wrote. I did command line install and it was successful. However, to load things quickly, I used ADDLOCAL=All. So every SQL Server 2005 component was installed.

The other day I had a chance to test it one more time using RTM on a server. And it was successful. I am really happy about that.

For this Server, I just want to load the database engine, Integration Service, and AdventureWorks sample database. I do not want the extra stuff, such as Analysis Services, Replication, full-text, Notification Services, Reporting Services, etc.. So, for ADDLOCAL, which defines what you want to install, I just have SQL_Data_Files,SQL_DTS,SQL_AdventureWorksSamples. SQL_Data_Files is a child component of Sql Server Engine. The rule of silent install is that if any of the child component is installed, then the parent component is automatically installed also. Note that you need to separate each component with comma, and do not put spaces between them. They are case-sensitive.

Here is the full content of my SqlInstall.ini file:

[Options]
;--------------------------------------------------------------------
;--------------------------------------------------------------------
; PIDKEY specifies the Product Identification Key.
; Usage: PIDKEY=ABCDE12345FGHIJ67890KLMNO

; NOTE: PIDKEY is not required for SQL Server Express Edition.
; NOTE: Do not include "-" in the PIDKEY.

;PIDKEY=

ADDLOCAL=SQL_Data_Files,SQL_DTS,SQL_AdventureWorksSamples

INSTANCENAME=MSSQLSERVER

SQLBROWSERACCOUNT="NT AUTHORITY\SYSTEM"
SQLBROWSERPASSWORD="NT AUTHORITY\SYSTEM"

SQLACCOUNT="NT AUTHORITY\SYSTEM"
SQLPASSWORD="NT AUTHORITY\SYSTEM"

AGTACCOUNT="NT AUTHORITY\SYSTEM"
AGTPASSWORD="NT AUTHORITY\SYSTEM"

SQLBROWSERAUTOSTART=0
SQLAUTOSTART=1
AGTAUTOSTART=1

SECURITYMODE=SQL
SAPWD=SomeStrongSaPassword

SAMPLEDATABASESERVER=.

Note also that sqlcmd is installed with this minimal installation of Sql Server engine, which I like.

I then went to the command line. I went to the directory where the setup.exe is located. I then typed:

setup.exe /settings c:\LocationOfIniFile\SqlInstall.ini /qn

I will write an article on this and hopefully it will be published at SqlServerCentral.com.

Update: Reader Johan Bijnens from Belgium sent me the following comments, which I agree 100%. He also shared his ini files. Since there are so many, I will not post them here. Thanks so much Johan for sharing:

Installing a default instance works fine, however one will have to know before install if IIS is activates with .net selected if one wants to install ADDLOCAL=All

When installing instances, it also works fine, but performing an uninstall is tricky because you’ll have to specify exactly which components you want to uninstall.

Aparently it does not take into account that there still are other instances on the server so if you don’t restrict an uninstall, it also uninstalls the tools, ..

Update 2: My stylesheet does not display some characters correctly, which will cause you probelms if you use my ini code sample for your silent install. There should be a backward slash between NT AUTHORITY and SYSTEM in the startup account.