Installing MSSQL

Here are some notes I made on installing MS SQL server.

Before Beginning:

• Remove all previous Express versions esp those of the same type e.g. 2008 express.
• Create an account and password for the service to use e.g. "SQL".

Run Setup:
Install server as named instance e.g. MSSQL2008 so can install other versions on same machine:

• Servers\Setup.exe
• On the "Planning" tab run the pre-requisites tools to check system requirements are met.
• Select 'Installation' tab and 'New SQL installation"
• Be sure to select "Database Engine" (and Management Tools if required) in features to install
• "Instance Configuration" page of wizard allows you to define a name for this instance of SQL server to avoid conflict (i.e. do not use default instance but call them MSSQL2005 MSSQL2008 etc)
• After specifying the username created above for the service select the authentication and administrators for the engine: select mixed mode and enter the sa password. add your admin username to "SQL server administrators" (not the one created for the service)
• Select the Data Directories tab and change to required separate drive eg. change root to E:\MSSQL

After setup:

• Set SQL Server Browser Service to Disabled. You do not need it to utilise normal database operations (e.g. accessing via MSSQL Management Studio or from code.

Named MS SQL instances will use dynamic TCP ports for remote connections by default, which is not suitable for connecting to them from scripted settings. To alter this follow the directions below (http://msdn.microsoft.com/en-us/library/ms345327.aspx)

• Open SQL Server Configuration Manager
• Expand SQL Server Network Configuration
• Select Protocols for [instance name]
• Double click TCP/IP
• Ensure Enabled
• Select IP Addresses tab, Scroll down to IP All
• Delete value in "TCP Dynamic Ports" ie. Null
• Set TCP Port (e.g. to 49172)

Configure the firewall to enable the specific port http://msdn.microsoft.com/en-us/library/ms175043.aspx

• Open Control Panel/Windwos Firewall with Advanced Security
• Enter New Rule Name: SQL Server [instance name e.g. MSSQL2008] In Programs and Services
• In Protocols and Ports assign TCP, Local port: Specific Port, [port number e.g. 49172]
• In Scope/Remote IP Address select 'These IP Addresses"
  • Add "Predefined set of computers/Local subnet"
  • Ensure Advanced/Profiles is set to All Profiles (Domain is for use with Domain controller only, and does NOT mean local subnet)
• SQL Browser service if required should be configured in the same way but on the next port up and as UDP (but probably not needed: see note above).

Test your connection using [IP],[port number]
e.g. 291.168.0.101,49172

To Copy Databases across from another server:

• Stop the SQL services on both machines
• Copy the .mdf and .log files across to the new machine
• Right click the 'Databases' folder in Management Studio and select 'Attach Database'. Browse to the file copied in previous step