2,187
edits
Difference between revisions of "MS SQL"
Jump to navigation
Jump to search
Added SQL 2008 install
m (Added source) |
(Added SQL 2008 install) |
||
| Line 1: | Line 1: | ||
== Installation == | |||
=== SQL 2008 Standard === | |||
Instructions below made from MS SQL 2008 Standard install ISO. These notes were created when installing a SQL server for use with [[:Category:vCentre|VMware vCentre]], therefore may contain some specifics for that usage, see the following if you're installing for vSphere 5, it has much greater detail.. | |||
* http://lonesysadmin.net/2011/10/06/how-to-install-microsoft-sql-server-2008-r2-for-vmware-vcenter-5/ | |||
If installing in a [[Acronyms#V|VM]], perform a snapshot 1st in case you make a mistake and want to reattempt from scratch. | |||
# Create a local user account for MS SQL<ref name="SQL-User" />, eg SQL-DB, with a nice long password with only the following options checked | |||
#* ''User not allowed to change password'' | |||
#* ''Password never expires'' | |||
# Run the setup.exe from the root of the ISO (or allow to autorun) | |||
# The installer may need to install .NET and upgrade Windows Installer | |||
## Click '''OK''' if so to allow | |||
## Accept the .NET Framework EULA | |||
## On completion on .NET Framework install, click '''Exit''' to proceed | |||
# Click on '''Installation''' to bring up the install options | |||
# Click on '''New SQL Server stand-alone...''' to launch the install wizard | |||
# Assuming the ''Setup Support Rules'' check succeeds, click '''OK''' to proceed | |||
#* Fix any issues found in order to be able to proceed further | |||
# If required, enter a valid product key, then click '''Next''' to proceed | |||
# Accept the EULA, then click '''Next''' to proceed | |||
# Click '''Install''' in order to install the Setup Support Files | |||
# Assuming the ''Setup Support Rules'' check succeeds, click '''Next''' to proceed | |||
#* Fix any issues found in order to be able to proceed further, warnings don't prevent installation, but should be read to understand why they have occurred | |||
# On the Feature Selection page, select the following features, and change the install paths if required, then click '''Next''' to continue | |||
#* ''Instance Features | Database Engine Services'' | |||
#* ''Shared Features | Client Tools Connectivity'' | |||
#* ''Shared Features | Integration Services'' | |||
#* ''Shared Features | Management Tools – Basic'' | |||
#* ''Shared Features | Management Tools – Complete'' | |||
# On the Instance Configuration page, change the Instance root directory if you want to store the database somewhere else, click '''Next''' to proceed | |||
# Click '''Next''' past the ''Disk Space Requirements'' page | |||
# On the ''Server Configuration'' page, change the following then click '''Next''' to proceed | |||
## Select ''Use the same account for all SQL Server services'' and enter the local account created earlier (eg for SQL-DB) | |||
## Change ''SQL Server Agent'' service startup type to ''Automatic'' | |||
# On the ''Database Engine Configuration'' page, change the following then click '''Next''' to proceed | |||
#* Change to ''Mixed Mode'', and enter a nice long password for the SQL root account | |||
#* Add SQL administrators (at minimum I'd suggest the machine's local admin and yourself) | |||
# Click '''Next''' to proceed past the next few pages, and then '''Install'' | |||
# Install should complete successfully, then click '''Next''', and then '''Close''' to complete. | |||
# Install the latest SQL service pack, then run Windows Update manually to ensure the install is fully patched | |||
# Even if not asked to, reboot the machine, then log back in and ensure all SQL services have started correctly | |||
# Limit memory available to SQL (optional) | |||
## Start ''SQL Server Management Studio'' and login as one of the SQL administrators you added earlier | |||
## Right-click over the SQL server and select '''Properties''' | |||
## Go to ''Memory'' and set ''Maximum server memory (in MB)'' to something sensible <ref name="SQL-Mem" /> | |||
##* SQL will grab as much physical RAM as possible in order to cache and improve performance, therefore you need to limit what it can grab so that the OS, and any other applications will have enough space to run without paging | |||
# Allow ''Local Launch'' (optional, but said to fix some scheduling and spurious event log error issues) | |||
## Go into ''Component Services'' (Start | Administrative Tools | Component Services) | |||
## Find, right-click and select ''Properties'' over ''Component Services'' | ''Computers'' | ''My Computer'' | ''DCOM Config'' | ''MsDtsServer100'' | |||
## Go to the ''Security'' tab and within the ''Launch and Activation Permission'' section, click '''Edit''' | |||
## '''Add''' the local SQL user (eg SQL-DB), and ensure ''Local Launch'' is checked | |||
## Click '''OK''' as required to apply and exit | |||
Additional notes... | |||
<references> | |||
<ref name="SQL-User">If you're creating a SQL server for a front-end service, then its usual to use domain service accounts to run SQL under. However, if you're creating a SQL server back-end infrastructure purposes (such as [[:Category:vCentre|vCentre]]) which should be able to run as stand-alone services without dependence on services such as [[Acronyms#D|DNS]] or Active Directory servers then you ''must'' use a local account. Back-end core infrastructure should be as isolated as possible from other systems, so that when failures or disasters occur, impact is localised and you don't end up with the chicken and egg or deadlock situation where you can't bring one service up, because it depends on another, which can't be brought up because...[etc].</ref> | |||
<ref name="SQL-Mem">If installing SQL on the same machine as [[:Category:vCentre|vCentre]], give SQL 25-50% of available RAM. For example, I'd use around 300 MB for a small lab/test environment of 1 or 2 ESX's, whereas if managing 40 ESX's with 1000 VM's you'd need around 2 GB.</ref> | |||
</references> | |||
== Standard Maintenance Plans == | == Standard Maintenance Plans == | ||
=== Daily Maintenance Plan === | === Daily Maintenance Plan === | ||