70-457 Reviewer #03, Planning an Installation of SQL Server 2012

Planning an installation on SQL Server 2012 is under the Install and Configure SQL Server objective which is 19% of the total examination for 70-457.

I will be writing all the important things to consider planning an installation for SQL Server 2012 in a nugget form to serve as a handy review notes for the certification exam. This topic can also be used as part of your review if you are going to take 70-462, Administering SQL Server 2012 Databases. As I have explained before 70-457 is an upgrade exam for all DBA with MCITP certification for SQL Server 2008.

Topics under Planning an Installation

  • Evaluate installation requirements
  • Designing an installation
  • Scale up versus scale out
  • Capacity planning
  • Designing new database
  • Identify standby database for reporting
  • Windows & service level security
  • Core mode installation
  • Benchmarking

My nuggets on each topic:

On evaluating your installation requirements

  • Identify the necessary components needed. Database Engine only? SSIS? SSRS? SSAS?
  • Which SQL Edition is needed to be installed
    • Standard Edition
    • Business Intelligence Edition
    • Enterprise Edition
    • Web/Development/Express Editions
  • Minimum hardware requirements. How much CPU? How much RAM? Disk requirements?
    • CPU requirement : 1.4Ghz or higher
    • Memory: 1G (512 for express edition)
    • Storage: atleast 1G for the system and data files
  • Consider virtualization requirements
  • Choosing which design? Instance components or shared components?
  • Operating system prerequisites;
    • .NET 3.5 SP1
    • .NET 4.0
    • Windows Powershell
    • IE7 or higher

Scale up vs Scale out

  • Scale up means increasing the current resources of your server.
  • Scale out means adding more servers to your current one.
  • In scale up, has larger impact in event of failure. In time of failure or downtime all services are down.
  • In scale out, there is lesser impact on failure or downtime.

Capacity planning

  • Small database that grow on demand can increase fragmentation
  • Fixed database size can reduce fragmentation
  • Auto-grow can cause file fragmentation
  • Auto-shrink can cause index fragmentation (I really think they should get rid of this.)

Designing new databases

  • Use RAID 1+0/5 for better performance and fault tolerance benefits.
  • Isolate the location of the log file to another physical disk. It is best to use RAID 10 for better write performance
  • Use filegroups. Place tables with most heavy writes on a RAID 10. Place tables with most heavy reads on RAID 5. The primary filegroup can be placed on RAID10 or RAID 5.
  • Place your tempDB on a RAID10 disk for better write performance.
  • Consider creating multiple tempDB files proportional to each logical CPU on your server to enable the SQL Server scheduler workers to loosely align to a file.

Identify standby database for reporting

  • Log shipping – this is just a warm standby, no automatic failover
  • Mirroring – this is considered a hot standby, will be deprecated in futures editions and will be replaced by the AlwaysOn feature
  • Replication
  • AlwaysOn

Server Core Mode Installation

  • Can be performed only on Windows Server 2008 R2 SP 1
  • All editions of SQL Server 2012 are supported
  • No GUI, only command line
  • Most secure way of installing SQL Server 2012

Service Security

  • Isolate services with separate service account. Advantage is that if a service account is compromised, only that specific service is compromised.
  • You can also use virtual accounts or managed service

Benchmarking

  • Use SQLIO to determine your hardware I/O capabilities. This is a separate tool and can be downloaded here.
  • Use SQLIOSim utility to validate disk integrity. To download this utility and a deep dive, click here.
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

My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with thee Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: