Automating WSS v3/MOSS 2007 development environment setup: part V – AD prep + SQL Server 2005 SP2 unattended setup

  • Article

Hello,

Now we have our development server up and running, being a Domain Controller.

We can now focus on SharePoint itself.

The steps from here are now:

  1. Prepare Active Directory (create one OU and the Service Accounts)
  2. Setup SQL Server 2005 with SP2
  3. Set parameters and MOSS pre-requisites to SQL Server
  4. Setup MOSS 2007 binaries
  5. Create MOSS 2007 farm
  6. Create standard logical components (1 SSP, start search, Excel services, etc.)
  7. Setup Office 2007 Enterprise with SP1
  8. Setup SharePoint Designer 2007 with SP1
  9. Setup Visual Studio 2008
  10. Setup SPDevmod

I’ll describe steps 1 and 2 in this post:

Step 1: Create one OU for SAs and the Service Accounts

I decided to create an Organizational Unit (OU) to store the Service Accounts used on the development platform. This OU will be at the root of the domain/LDAP tree, and called “Service Accounts”

Doing this is easy, just type in a cmd window (being Domain Admin, at minimum):

Dsadd ou OU="Service Accounts",DC=ad,DC=moss -desc "Org Unit to store Service Accounts"

You now have a place to store your Service Accounts

To create them, simply run:

Dsadd user "CN=MOSS_Farm_Svc,OU=Service Accounts,DC=ad,DC=moss"

-pwd password

-desc "Service Account to run the MOSS farm (pwd = password)"

-mustchpwd no

-canchpwd no

-pwdneverexpires yes

Note: be careful with your password policy applied. If password doesn’t meet the requirements, creation will be rejected. To avoid these issues, I often decrease the security level of passwords for my development environment (as it’s development environment and main focus will be on impersonation, not passwords strength)

Do that for all the Service Accounts you planned to use, or the production environment use.

Result for me is:

Step2a: Install SQL Server 2005

SQL_Svc account will be used right now!

Setting up SQL Server 2005 an unattended way has been dramatically eased on this release. You simply need to launch a command line with the few parameters you need, and you’re done!

In general, and for MOSS use, I use the following settings:

  • One (or 3) domain service accounts to run
    • SQL Services,
    • SQL Agent
    • SQL Browser
  • Server Collation to be : Latin1_General_CI_AS_KS_WS, which is the native one used by WSS/MOSS
  • Default Instance
  • Workstation components
  • Windows authentication (no mixed)

For convenience, I use a second VHD file to store scripts and binaries. Its Drive letter is E:, so sources will be on this drive (as D: letter is used by the default DVD drive during the setup).

To launch the SQL Server DB Engine setup an automated way, you have 2 options:

  • Create a “settings” file (or INI)
  • Type directly a long instruction (which is what I’ll do, as it’s easy and efficient)

In my case, the I use direct instruction which is:

Start /wait E:\Sources\SQLSvr2005\Servers\setup.exe /qb INSTANCENAME=MSSQLSERVER ADDLOCAL=SQL_Engine SQLACCOUNT=ad\SQL_Svc SQLPASSWORD=sql AGTACCOUNT=ad\SQL_Svc AGTPASSWORD=sql SQLBROWSERACCOUNT=ad\SQL_Svc SQLBROWSERPASSWORD=sql SQLCOLLATION=Latin1_General_CI_AS_KS_WS

Here it is:

Then, I setup the workstation components (I love Management Studio and SQL Analyzer to develop and watch what happens in the SQL Server while using SharePoint):

Start /wait E:\Sources\SQLSvr2005\Tools\setup.exe /qb ADDLOCAL=Client_Components,Connectivity,SQL_Documentation,SQL_Tools90

And here you are:

Notes:

  • To launch successfully these scripts, beware that: no “CR LF” must be in the command line and put only 1 space between each blocks.
  • Looks like the “ to reference the setup.exe full path is not welcome either.

Step 2b: Apply the Service Pack 2 for SQL Server 2005

You’ll need the SP2 package.

It can be downloaded from here: Microsoft SQL Server 2005 Service Pack 2 (https://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&displaylang=en)

The different options and command line arguments are displayed, typing:

SQLServer2005SP2-KB921896-x86-ENU.exe /?

From this, you can deduct the right instruction for environment:

SQLServer2005SP2-KB921896-x86-ENU.exe /quiet /allinstances

Here it is:

Note: the /quiet switch turn off any screens or progress bars. Once launched, you need to wait for a dozen of minutes for the setup to complete (you can monitor hotfix.exe and SQLServer2005SP2-KB921896-x86-ENU.exe processes in the task manager). If you want to follow what happens, remove the /quiet switch, but doing so will force you to click and proceed manually… which is not my objective here.

And here is the result :

Note: I know, I changed the server name (from SVR2003 to MSDN)!

For any help:

  • SQL server 2005 RTM version = 9.0.1399
  • SQL server 2005 with SP1 = 9.0.2047
  • SQL server 2005 with SP2 = 9.0.3042

Step 2c: Set minimal settings to SQL Server DB Engine instance

Most of the time, I change few settings on my SQL Instances, to run the way I like.

Here are the settings and the way to apply them:

  • Change Maximum Memory allowed for the instance:

    • Set it to half the physical memory of your server (in my case 256 MB which is half the 512 MB physical memory of the VM):

      USE master
      EXEC sp_configure 'show advanced options', 1
      RECONFIGURE WITH OVERRIDE
      EXEC sp_configure 'max server memory', 256
      RECONFIGURE WITH OVERRIDE
      EXEC sp_configure 'show advanced options', 0
      RECONFIGURE WITH OVERRIDE

  • Change Data and Log default directory for new databases:

    • Create the directories you’ll use (in my case C:\SQL_Data\DB\ and C:\SQL_Data\Logs\ – script here is obvious), then:

    USE master
    GO
    --Data file
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'C:\SQL_Data\DB'
    GO
    -- Log file
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'C:\SQL_Data\Logs'
    GO

These T-SQL instructions can be put in 1 single *.sql file (I called it SQLSettings.sql), then launch:

cd /d "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
SQLCMD -i "SQLSettings.sql"

Settings before the script :

Run the script :

And settings after :

NOW, SQL Server 2005 SP2 is setup, and fully installed without any hand operations ! We can focus on SharePoint, at last !

<Emmanuel/>

Post Scriptum:

  • Don’t forget to activate and update your virtual machine any time, before 30 days !  

References: