Part 1: Storage Configuration
Skip this section if the installation will not be putting user data on a shared storage device (i.e., a local install where there’s only a single magnetic drive).
1. Provision LUNs and configure the storage device according to the manufacturer’s recommendations for SQL Server.
2. Provision a drive or mount point for each of the following groups of data. To design for snapshot backups, if there will be over ~1 TB of user data on this server (or the potential for such given data growth estimates), follow the manufacturer requirements for where to land the user data and log volumes (i.e., they may be required to be on the same LUN, or on separate LUNs).
(a) TempDB data files
(b) TempDB log file
(c) User database data files
(d) User database log files
(e) Backup target (note: consider targeting a shared file server)
3. If running a version of Windows Server prior to 2008, or are planning to reuse volumes formatted under a version of Windows Server prior to 2008, ensure the volumes are partition-aligned. More information about partition alignment can be found in this whitepaper: http://msdn.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx.
4. If this is a new storage device, test storage throughput using a tool such as SQLIO, which can be downloaded here: http://www.microsoft.com/download/en/details.aspx?id=20163.
(Note: SQLIO performs extremely intensive tests and should only be run when no other systems are dependent on the shared storage device.) If this server will host many databases, make careful note of the IOPS when the storage device is under heavy concurrent load – ensure the results meet the business requirements. Ensure correct multipathing and failover configuration by physically unplugging cables while running the tests.
Part 2: Preparation for Install
1. Select the appropriate SQL Server edition to install. You may need to consult the business requirements for the project to determine the specific edition that’s needed out of the possibilities given below:
* For development purposes, consider deploying multiple instances, one for each possible server edition, collation, and configuration.
2. Create a new Active Directory domain account to be used as the SQL Server service account. This account should be added as a member of the SQL_Service_Accounts group. (Note: consider provisioning two accounts, one for the database engine service, and one for SQL Agent, and assign them to different AD groups.)
3. Enable instant file initialization. (Note: in rare circumstances, this feature violates security requirements or protocols – verify the business requirements before making this change.)
(a) Open the Local Security Policy through Administrative Tools
(b) Expand the Local Policies node
(c) Open the User Rights Assignment node
(d) Add the applicable database engine service accounts created in the previous step to the “Perform volume maintenance tasks” policy
(e) (Optional) Restart the applicable SQL Server service(s) for the setting to take effect
4. Enable jumbo frames for the network adapter(s), in particular for network adapters dedicated to storage using either iSCSI or FCoE. (Note: ensure all other servers and devices that connect to or from this server also have jumbo frames configured correctly. If they cannot be configured for jumbo frames, skip this step.)
(a) Open Device Manager
(b) For each of the applicable network adapters:
i. Open the Properties window
ii. Find an option that says either Jumbo Packets or Jumbo Frames (varies by network card). This is usually in an Advanced or Settings tab.
iii. Select the largest packet size from the options available (usually around 9000 bytes)
iv. Click OK to accept changes (note: this will drop the network for 5-10 seconds, but does not require a reboot)
(c) Work with your network administrator to ensure jumbo frames are enabled throughout the supporting network infrastructure. Most infrastructure supports this by default, but you want to make sure it’s working correctly.
5. If the server is, or will be, running anti-virus software, exclude SQL Server-related files according to the following Microsoft Knowledge Base article: http://support.microsoft.com/kb/309422.
6. Configure the system page file:
(a) Open the System Properties window
(b) Open the Advanced System Settings window
(c) Open the Performance settings interface
(d) Switch to the Advanced tab
(e) Open the Virtual Memory settings window
(f) Disable automatic management of the page file size
(g) For the C drive, configure a custom size of 2048MB initial, 2048MB maximum
(h) Click the Set button to commit the change
(i) Ensure no other volumes have page files configured
(j) Click OK all the way out
(k) (Optional) Reboot the system for the setting to take effect
Part 3: Installation
1. Install SQL Server from the installation media, selecting to install at least the following features:
(a) All Database Engine Services
(b) Client Tools Backwards Compatibility
(c) Management Tools - Complete
Make sure all directories are changed to the E drive (32-bit install shown).
2. Select an appropriate instance name:
3. For the SQL Server Agent and SQL Server Database Engine, enter the user name(s) and password(s) for the account(s) created in Part 2, Step 2.
4. If the business requirements state that an instance collation other than the default is required, configure that in the Collation tab.
5. Add the following Windows groups to the administrators list:
(a) <domain>\Domain Admins
(b) <domain>\SQL_Administrators
6. If this server requires access via SQL Server authentication, select Mixed Mode authentication, and choose a strong password for the system administrator (sa) account. Otherwise, select Windows authentication mode. (Note: the authentication mode can be changed after SQL Server is installed and configured, if necessary.)
7. Enter the paths for data storage, based on the volumes and/or mount points provisioned in Part 1, Step 2:
8. Accepts the defaults for the remainder of the wizard.
Part 4: SQL Server Configuration
1. If you installed SQL Server Developer or Express edition and the instance is intended for internal/”public” use, the instance must be configured to allow outside TCP/IP connections, because it’s disabled by default:
(a) Open SQL Server Configuration Manager from the Start Menu
(b) Expand the SQL Server Network Configuration node on the left
(c) Select the “Protocols for <instance name>” node on the left
(d) Right-click on TCP/IP in the list on the right, and click Enable
(e) Click OK through
(f) (Optional) Restart the SQL Server database engine service for the change to take effect
2. If security requirements dictate this instance must not respond to network broadcasts, disable this behaviour:
(a) Open SQL Server Configuration Manager from the Start Menu
(b) Expand the SQL Server Network Configuration node on the left
(c) In the tree, right-click the “Protocols for <instance name>” node, and click Properties
(d) Set the Hide Instance property to Yes
(e) Click OK through
(f) (Optional) Restart the SQL Server database engine service for the change to take effect
In preparation for the next steps, open SQL Server Management Studio from the Start Menu, and connect Object Explorer to the instance using Windows Authentication. If the SQL Server service is not already running, it can be started using the Services applet in Administrative Tools, or through SQL Server Configuration Manager. (Make sure the database engine service is set to start automatically, if appropriate.)
3. Configure instance security:
(a) Expand the Security node under the server node
(b) Expand the Logins node
(c) If it exists, remove the BUILTIN\Administrators and/or BUILTIN\Administrator login(s)
(d) Disable the sa login:
i. Right-click the sa login node and click Properties
ii. Select Status on the left side
iii. Select Disable in the “Login” section
iv. Click OK to accept the changes
4. Configure Database Mail:
(a) Expand the Management node
(b) Right-click the Database Mail node, and click Configure Database Mail
(c) Click Next on the introduction screen
Click Next; say Yes to the prompt asking to enable Database Mail.
Type the profile name:
Click the Add account button
Enter the profile information:
Click OK, and click Next in the wizard.
Set the profile as Public and as the Default Profile:
Accept the default values in the rest of the wizard
5. Send a test e-mail to ensure Database Mail is configured correctly:
(a) Right-click the Database Mail node
(b) Click Send Test Email
(c) Enter your personal e-mail address in the To field
(d) Click the Send Test Email button
(e) You should receive the test e-mail. If it doesn’t arrive within a couple of minutes, Database Mail is probably not configured correctly (assuming good network connectivity).
6. Configure instance memory settings:
(a) Open Server Properties by right-clicking the server name in Object Explorer, and clicking Properties
(b) Select Memory on the left side
(c) If this is a production server, set the Minimum server memory field to 512 MB – 2048 MB, depending on expected workload and database size
(d) For Maximum server memory, from the amount of memory allocated to Windows, subtract 2 GB, and divide the remainder among the one or more SQL Server instances in this Windows server
(e) Click OK to accept changes (takes effect immediately)
7. Run the following Server Setup scripts (note: Office means internal; it has nothing to do with server location):
(a) Create Operators and Alerts.sql
(b) Create Cycle Log File Job.sql
(c) sp_configure Settings.sql
(d) System Database Files.sql
(e) Rename System Jobs.sql
(f) tr_NotifyCreateDropDatabase.sql (production only)
8. Configure tempdb files according to anticipated workload:
(a) The number of data files should not exceed the number of logical processor cores assigned to the SQL instance, with a maximum of 8. A good starting point is 1/4 to 1/2 the number of logical processor cores. Adjust based on expected workload.
(b) Data file size should add up to be approximately the size of the active working sets for all databases in the instance. If this is not known in advance, a safe setting is to allocate 1/8 to 1/4 of the assigned system memory, and configure aggressive auto-growth. Ideally, these files (and the log file) should never have to grow beyond their initial sizes (so the files themselves are contiguous on disk).
(c) Set the log file to an initial size of 1/4 the total size of the data files, and configure aggressive auto-growth. Do not add additional log files.
9. Install the database maintenance scripts:
(a) CommandExecute.sql
(c) DatabaseSelect.sql
(d) IndexOptimize.sql
(e) DatabaseBackup.sql
(f) DatabaseIntegrityCheck.sql
(g) (Production only, or if required) Run the “Database Integrity Checks Job” script to create the job. Note: you must set up your own schedule for this job to run.
(h) (Production only, or if required) Run the “Index Maintenance Job” script to create the job. Note: you must set up your own schedule for this job to run.
(i) (Production only, or if required) Run the “Semimonthly Snapshot Backup Job” script. Important: verify the backup target in the script before you run it.
10. (For production, or for critical data) Create a maintenance plan to perform database backup-related operations. Note: the following instructions may not provide the optimal backup solution for every situation.
(a) In Object Explorer, expand the Management folder
(b) Right-click the Maintenance Plans folder and click New Maintenance Plan
(c) Enter the name “Database Backups” and click OK
(d) Create subplans for the following backup tasks:
i. Full: add a Back Up Database Task with the following parameters:
1. Full backup type
2. All databases
3. Ignore databases where the state is not online (in databases dropdown)
4. Create a backup file for every database
5. (Optional) Create a sub-directory for each database
6. Backup target (from Part 1, Step 2)
7. Verify backup integrity
8. Use the server default compression setting
ii. Differential: add a Back Up Database Task with the following parameters:
1. Differential backup type
2. (Same steps 2-8 as for full backups)
iii. Transaction Log: add a Back Up Database Task with the following parameters:
1. Transaction Log backup type
2. (Same steps 2-8 as for full backups)
iv. History Cleanup: add a History Cleanup Task with the following parameters:
1. Delete backup and restore history
2. Delete SQL Server Agent job history
3. Delete maintenance plan history
4. 4-week retention period
(e) Close the maintenance plan, saving changes
(f) Edit the jobs created for the maintenance plan to set the schedules according to the business requirements. For a production server, generally run full backups once/week, differentials daily on the 6 other days, and transaction log backups periodically according to the acceptable amount of data loss.
11. Run the “Change Job Owners to sa.sql” script. (Note: if you have to go back and change any maintenance plan, run this script again afterwards – saving a SSIS package changes the owner of the jobs to the currently logged in Windows account, even if you’re connected using a SQL login.)
12. (Optional) Run “sp_TakeSnapshot.sql”. If this instance is NOT a production server, run “sp_RestoreFromSnapshot.sql“ as well.
13. Manually run the Full database backup job to back up the system databases (master, model, msdb).
14. Restart the Windows server for all changed settings to take effect and to make sure the environment starts up correctly automatically.