Saturday
Oct062012

Tips for installing SQL 2012

 

I messed around with 2012 a while back and ran into a few snags getting it up and running.

For one it seemed to really want the .NET Framework 4.0 installed - along with a few other prerequisites I was apparently a little out-of-practice in configuring.

Here's my notes from installation - including getting past the "Microsoft .NET Framework 4.0 installation has failed with exit code 1." error. 

The prerequisites for SQL 2012 installation are available here:

  • .NET 4 install:  http://www.microsoft.com/download/en/confirmation.aspx?id=17851
  • KB2533523:  http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27014 – 
  • KB2544514:  http://www.microsoft.com/download/en/details.aspx?id=27756

 

Miscellaneous changes to the system you're installing SQL 2012 on

  • Alter Windows Firewall or, not recommended, disable entirely
  • Adjust UAC or disable entirely (Start > Run > msconfig)

 If you're making an unattended installation .ini for SQL 2012 you may find these tweaks helpful

  •  Toggle this to match up with your “/QS” command-line argument below:  QuiteSimple=“True”
  • Comment out “UIMODE” (with a semicolon)  like this:  ;UIMODE="AutoAdvance")
  • Add this:  IAcceptSQLServerLicenseTerms="True"
  • Specify an sa password if using Mixed Mode authentication and be sure it meets complexity:  SAPWD=”OpensezM3”

 

An example of a command line argument to run a SQL 2012 installation will look like this

 setup.exe /QS /ConfigurationFile=C:\ConfigurationFile.ini

More help 
  • From command-line:  setup.exe /?
  • http://msdn.microsoft.com/en-us/library/hh231669(v=sql.110).aspx
  • KB2544514 installs .NET 4 components.  Good luck with that.

  ...and finally an example of a command line to UNinstall SQL 2012 (for named instance "DENALI01") would look like this:

setup.exe /Q /Action=Uninstall /FEATURES=SQLENGINE,DQ,DQC,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,MDS /INSTANCENAME=DENALI01

As you're documenting steps for installation don't forget about the invaluable add-in included with Windows 7 - the problem steps recorder.  Just go to Start>Run and input psr to easily record the steps needed for installation.  So much easier then cutting/pasting screenshots into Microsoft Word.

 

I'm not affiliated with the links included, but do extend my thanks to the authors for making them available.

Saturday
Oct062012

SQL Server Installation Checklist

This will be a work in progress.  Other, smarter, better-looking people than I have done lists like this, yet I need a centralized location to reference and remind me of things I always seem to forget.  Cheers!

 

Recovery model* - Unless you absolutely need up-to-the-nanosecond restore points "Simple" will do just fine.
max/min memory settings - Set them.  Personal experience shows the min should not be less than ~75% of the max.  
Backups - schedule them.
Backup compression settings* - (SQL 2008 and higher.)  Don't forget about these.
Defrag - schedule it.
DBCC - schedule it.
Page Verify (Checksum - not torn page detection) - Especially when creating databases that have been upgraded
optimize for ad hoc workloads - Helps deter performance degradation caused by parameter sniffing and unnecessary plan cache bloat.
sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
sp_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO 
add a filegroup as an alternative to PRIMARY - (best practice dictates PRIMARY should be kept small)
TEMPDB minimum sizing (otherwise sizes default to 8MB 1MB for mdf and ldf as per http://bradmcgehee.com/wp-content/uploads/presentations/SSC06%20How%20to%20Optimize%20TEMPDB%20Performance.pdf)
* - Can bet set instance-wide by configuring the settings for the Model database
I'm not affiliated with the links presented in this article, but do wish to extend my thanks to the authors for making this material available to the public domain.

 

Further considerations:

Enable RCSI 

Enable Snapshot Isolation

Configure a non-standard TCP port to respond on

 

Analysis Services - best practices (for SQL 2008 R2) found here.

disable Flight Recorder 

enable "Shared Memory" protocol