Post-it notes: Chapter 3 – Configuring SQL Server 2008

December 9, 2012

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

 

MAXDOP

Instances/Default instances

  • Up to SQL v7 – only 1 instance.
  • Each instance completely independent (own services, config and db’s)
  • InstanceID defines directory, registry and service names
  • Detail instance is not mandatory
  • Directories (80 – SQL 2000, 90 – SQL 2005 and 100 – SQL 2008)
  • SSIS not instance aware – 1 per server
  • Default instance listens on TCP port 1433 – others use dynamic port allocation
  • SQL browser service uses UDP 1434
  • Browser services assists in determining the port when connecting
  • If browser service not running, you need to specify port number. Good for extra security.
  • Each edition supports up to 50 instances. 25 supported on cluster.

 

Manage Services

  • Use SQL configuration manager to stop, start etc.
  • If changing accounts that the SQL Service is running under, definitely use Configuration Manager as it handles additional rights required. Using windows services panel will not do this correctly.
  • Service tab (right click a service in configuration manager) -worth having services set to automatic, however services on cluster are set to manual, as the failover process handles the stop/start.
  • Advanced tab (right click a service in configuration manager) – note the startup parameter – can append tracing flags here, -T1222 – capture detailed logs regarding deadlocks
  • Filestream tab (new feature in SQL 2008)
  • First you configure Filestream in windows (configuration manager, filestream tab) and then you configure Filestream in SQL Server (sp_configure). If enabled during install, then both are done.
  • sp_configure ‘filestream_access_level’  (0) disable (1) enable t-sql (3) t-sql and win32 api

 

Managing Connections and Protocols

  • 4 different protocols (shared memory, tcp/ip, named pipes and via)
  • Shared memory enabled on all editions. Fast, used for local connections. Always tried first.
  • VIA disabled on all editions. Virtual Interface Adapter. Requires specialized hardware.
  • TCP/IP enabled on Standard, Enterprise and Workgroup. Worth nothing when handing a dev server over to dev teams. Most common protocol. Port 1433.
  • Named Pipes (enabled locally, disabled for remote) on all editions. Port 445.
  • Note, Windows 2008 and SQL 2008 – has firewall enabled. Can create connectivity issues.

 

Configuration

  • Sp_configure has more options than SSMS.

 

sp_configure ‘show advanced options’, 1
Go
reconfigure

 

  • Select * from sys.configurations
  • AWE – on 32bit server, SQL limited to 2GB. Enable AWE to use more. Always set max memory when using this setting so it does not use all memory.
  • Max Memory – settings controls the max size of the buffer pool.
  • Min Memory – minimum that SQL will allocate to itself and not drop below – once it gets there. Service will start with least amount required and then grow. Useful when multiple instances installed.
  • Max Degree of Parallelism (MAXDOP) – controls number of CPU’s used to process a query
  • Best practise, set to half the number of CPU’s. By default, uses all, so could be in situation where CPU hits 100% when an intensive query runs.
  • Security certifications C2 Auditing (USA)  and Common Criteria Compliance (EU) aka EAL4+

 

Backup Compression

  • Enterprise feature however SQL 2008 R2 standard edition allows compressed backups.
  • Set the default (server wide) or/and at time of backup.

 

Database mail

  • No MAPI client required.
  • Cluster support, not supported in express version.
  • Can configure multiple smtp’s and will try each on in the list.
  • Public and Private profiles.
  • Sp_send_dbmail

 

Full text indexing

  • All databases have full text indexing enabled by default
  • First step is to: create fulltext catalog <name>
  • Table must have unique, single column, non-nullable index. Full text index will base its key on this.
  • Populating full text index – process of filling it – done when you create it.
  • By default, index will update automatically.
  • Modify the change tracking to control this update process.
  • Set to ‘manual’, changes tracked but not populated until ALTER FULLTEXT INDEX on <> START UPDATE POPULATION.
  • Set to ‘off’ changes not tracked or populated until you run full or incremental population.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: